95 lines
3.6 KiB
C#
95 lines
3.6 KiB
C#
using System.Data.SQLite;
|
|
|
|
namespace Screenie;
|
|
|
|
internal class Database : IDisposable {
|
|
private Plugin Plugin { get; }
|
|
private SQLiteConnection Connection { get; }
|
|
|
|
internal Database(Plugin plugin) {
|
|
this.Plugin = plugin;
|
|
|
|
var dir = this.Plugin.Interface.GetPluginConfigDirectory();
|
|
Directory.CreateDirectory(dir);
|
|
var dbPath = Path.Join(dir, "database.sqlite");
|
|
|
|
this.Connection = new SQLiteConnection($"Data Source={dbPath}");
|
|
this.Connection.Open();
|
|
this.RunMigrations();
|
|
}
|
|
|
|
public void Dispose() {
|
|
this.Connection.Close();
|
|
this.Connection.Dispose();
|
|
}
|
|
|
|
private void RunMigrations() {
|
|
using var t = this.Connection.BeginTransaction();
|
|
this.Execute("create table if not exists _migrations (id int not null primary key, query text not null)");
|
|
|
|
// get latest migration
|
|
long max;
|
|
{
|
|
using var reader = this.Query("select coalesce(max(id), 0) from _migrations");
|
|
if (!reader.Read()) {
|
|
throw new Exception("could not get max migration");
|
|
}
|
|
|
|
max = reader.GetFieldValue<long>(0);
|
|
}
|
|
|
|
if (max < 1) {
|
|
const string migrationOne = """
|
|
create table screenshots (
|
|
hash text not null primary key,
|
|
path text not null,
|
|
active_character jsonb not null,
|
|
location text,
|
|
location_sub text,
|
|
area text,
|
|
area_sub text,
|
|
territory_type int not null,
|
|
world text,
|
|
world_id int not null,
|
|
captured_at_local timestamp not null,
|
|
captured_at_utc timestamp not null,
|
|
eorzea_time text not null,
|
|
weather text,
|
|
ward int,
|
|
plot int,
|
|
visible_characters jsonb not null,
|
|
mods_in_use jsonb not null
|
|
)
|
|
""";
|
|
this.Execute(migrationOne);
|
|
this.Execute("insert into _migrations (id, query) values (1, $query)", new Dictionary<string, object?> {
|
|
["$query"] = migrationOne,
|
|
});
|
|
}
|
|
|
|
t.Commit();
|
|
}
|
|
|
|
private SQLiteCommand GetCommand(string query, Dictionary<string, object?>? parameters = null) {
|
|
var command = this.Connection.CreateCommand();
|
|
command.CommandText = query;
|
|
if (parameters != null) {
|
|
foreach (var (key, value) in parameters) {
|
|
command.Parameters.AddWithValue(key, value);
|
|
}
|
|
}
|
|
|
|
return command;
|
|
}
|
|
|
|
internal SQLiteDataReader Query(string query, Dictionary<string, object?>? parameters = null) {
|
|
using var command = this.GetCommand(query, parameters);
|
|
return command.ExecuteReader();
|
|
}
|
|
|
|
internal int Execute(string query, Dictionary<string, object?>? parameters = null) {
|
|
using var command = this.GetCommand(query, parameters);
|
|
return command.ExecuteNonQuery();
|
|
}
|
|
}
|