private static void UpgradeSystemsDB15(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN versiondate DATETIME";
string query2 = "UPDATE Systems SET versiondate = datetime('now')";
conn.PerformUpgrade(15, true, false, new[] { query1, query2 });
}
private static void UpgradeSystemsDB11(SQLExtConnection conn)
{
//Default is Color.Red.ToARGB()
string query1 = "ALTER TABLE Systems ADD COLUMN FirstDiscovery BOOL";
conn.PerformUpgrade(11, true, false, new[] { query1 });
}
private static void UpgradeUserDB12(SQLExtConnection conn)
{
string query1 = "CREATE TABLE routes_expeditions (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL, start DATETIME, end DATETIME)";
string query2 = "CREATE TABLE route_systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, routeid INTEGER NOT NULL, systemname TEXT NOT NULL)";
conn.PerformUpgrade(12, true, false, new[] { query1, query2 });
}
// either give a fully formed cmd to it, or give cmdexplain=null and it will create one for you using cmdtextoptional (but with no variable variables allowed)
public static List <string> ExplainQueryPlan(this SQLExtConnection r, DbCommand cmdexplain = null, string cmdtextoptional = null)
{
if (cmdexplain == null)
{
System.Diagnostics.Debug.Assert(cmdtextoptional != null);
cmdexplain = r.CreateCommand(cmdtextoptional);
}
List <string> ret = new List <string>();
using (DbCommand cmd = r.CreateCommand("Explain Query Plan " + cmdexplain.CommandText))
{
foreach (System.Data.SQLite.SQLiteParameter p in cmdexplain.Parameters)
{
cmd.Parameters.Add(p);
}
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string detail = (string)reader[3];
int order = (int)(long)reader[1];
int from = (int)(long)reader[2];
int selectid = (int)(long)reader[0];
ret.Add("Select ID " + selectid + " Order " + order + " From " + from + ": " + detail);
}
}
}
return(ret);
}
private static void UpgradeUserDB11(SQLExtConnection conn)
{
string query2 = "ALTER TABLE Objects ADD COLUMN Landed BOOL";
string query3 = "ALTER TABLE Objects ADD COLUMN terraform Integer";
conn.PerformUpgrade(11, true, false, new[] { query2, query3 });
}
private static void UpgradeSystemsDB102(SQLExtConnection conn)
{
string query1 = "CREATE TABLE SystemNames (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL COLLATE NOCASE, " +
"EdsmId INTEGER NOT NULL)";
string query2 = "CREATE TABLE EdsmSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER, " +
"X INTEGER NOT NULL, " +
"Y INTEGER NOT NULL, " +
"Z INTEGER NOT NULL, " +
"CreateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"UpdateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"VersionTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"GridId INTEGER NOT NULL DEFAULT -1, " +
"RandomId INTEGER NOT NULL DEFAULT -1)";
string query3 = "CREATE TABLE EddbSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER NOT NULL, " +
"Population INTEGER , " +
"Faction TEXT, " +
"GovernmentId Integer, " +
"AllegianceId Integer, " +
"PrimaryEconomyId Integer, " +
"Security Integer, " +
"EddbUpdatedAt Integer, " + // Seconds since 1970-01-01 00:00:00 UTC
"State Integer, " +
"NeedsPermit Integer)";
conn.PerformUpgrade(102, true, false, new[] { query1, query2, query3 });
}
private static void UpgradeSystemsDB6(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_eddb Integer";
string query2 = "ALTER TABLE Systems ADD COLUMN faction TEXT";
//string query3 = "ALTER TABLE Systems ADD COLUMN population Integer";
string query4 = "ALTER TABLE Systems ADD COLUMN government_id Integer";
string query5 = "ALTER TABLE Systems ADD COLUMN allegiance_id Integer";
string query6 = "ALTER TABLE Systems ADD COLUMN primary_economy_id Integer";
string query7 = "ALTER TABLE Systems ADD COLUMN security Integer";
string query8 = "ALTER TABLE Systems ADD COLUMN eddb_updated_at Integer";
string query9 = "ALTER TABLE Systems ADD COLUMN state Integer";
string query10 = "ALTER TABLE Systems ADD COLUMN needs_permit Integer";
string query11 = "DROP TABLE IF EXISTS Stations";
string query12 = "CREATE TABLE Stations (id INTEGER PRIMARY KEY NOT NULL ,system_id INTEGER, name TEXT NOT NULL , " +
" max_landing_pad_size INTEGER, distance_to_star INTEGER, faction Text, government_id INTEGER, allegiance_id Integer, state_id INTEGER, type_id Integer, " +
"has_commodities BOOL DEFAULT (null), has_refuel BOOL DEFAULT (null), has_repair BOOL DEFAULT (null), has_rearm BOOL DEFAULT (null), " +
"has_outfitting BOOL DEFAULT (null), has_shipyard BOOL DEFAULT (null), has_blackmarket BOOL DEFAULT (null), eddb_updated_at Integer )";
string query13 = "CREATE TABLE station_commodities (station_id INTEGER PRIMARY KEY NOT NULL, commodity_id INTEGER, type INTEGER)";
string query14 = "CREATE INDEX station_commodities_index ON station_commodities (station_id ASC, commodity_id ASC, type ASC)";
string query15 = "CREATE INDEX StationsIndex_ID ON Stations (id ASC)";
string query16 = "CREATE INDEX StationsIndex_system_ID ON Stations (system_id ASC)";
string query17 = "CREATE INDEX StationsIndex_system_Name ON Stations (Name ASC)";
conn.PerformUpgrade(6, true, false, new[] {
query1, query2, query4, query5, query6, query7, query8, query9, query10,
query11, query12, query13, query14, query15, query16, query17
});
}
public static void Vacuum(this SQLExtConnection r)
{
using (DbCommand cmd = r.CreateCommand("VACUUM"))
{
cmd.ExecuteNonQuery();
}
}
// immediate paras, called p1,p2,p3 etc.
public static DbCommand CreateSelect(this SQLExtConnection r, string table, string outparas, string where, Object[] paras,
string orderby = "", string[] joinlist = null, object limit = null, DbTransaction tx = null)
{
string lmt = "";
if (limit != null)
{
lmt = " LIMIT " + (limit is string?(string)limit : ((int)limit).ToStringInvariant());
}
string cmdtext = "SELECT " + outparas + " FROM " + table + " " + (joinlist != null ? string.Join(" ", joinlist) : "") +
(where.HasChars() ? (" WHERE " + where) : "") + (orderby.HasChars() ? (" ORDER BY " + orderby) : "") + lmt;
DbCommand cmd = r.CreateCommand(cmdtext, tx);
int pname = 1;
foreach (var o in paras)
{
var par = cmd.CreateParameter();
par.ParameterName = "@p" + (pname++).ToStringInvariant();
par.Value = o;
cmd.Parameters.Add(par);
}
return(cmd);
}
private static void UpgradeUserDB107(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Commanders ADD COLUMN SyncToEdsm INTEGER NOT NULL DEFAULT 1";
string query2 = "ALTER TABLE Commanders ADD COLUMN SyncFromEdsm INTEGER NOT NULL DEFAULT 0";
string query3 = "ALTER TABLE Commanders ADD COLUMN SyncToEddn INTEGER NOT NULL DEFAULT 1";
conn.PerformUpgrade(107, true, false, new[] { query1, query2, query3 });
}
private static void UpgradeSystemsDB2(SQLExtConnection conn)
{
string query = "CREATE TABLE Systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , name TEXT NOT NULL COLLATE NOCASE , x FLOAT, y FLOAT, z FLOAT, cr INTEGER, commandercreate TEXT, createdate DATETIME, commanderupdate TEXT, updatedate DATETIME, status INTEGER, population INTEGER )";
string query3 = "CREATE TABLE Distances (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , NameA TEXT NOT NULL , NameB TEXT NOT NULL , Dist FLOAT NOT NULL , CommanderCreate TEXT NOT NULL , CreateTime DATETIME NOT NULL , Status INTEGER NOT NULL )";
string query5 = "CREATE INDEX DistanceName ON Distances (NameA ASC, NameB ASC)";
conn.PerformUpgrade(2, false, false, new[] { query, query3, query5 });
}
private static void UpgradeSystemsDB17(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_edsm Integer";
string query4 = "ALTER TABLE Distances ADD COLUMN id_edsm Integer";
string query5 = "CREATE INDEX Distances_EDSM_ID_Index ON Distances (id_edsm ASC)";
conn.PerformUpgrade(17, true, false, new[] { query1, query4, query5 });
}
public static DbCommand CreateDelete(this SQLExtConnection r, string table, string where, string[] paras = null, DbType[] types = null, DbTransaction tx = null)
{
string cmdtext = "DELETE FROM " + table + " WHERE " + where;
DbCommand cmd = r.CreateCommand(cmdtext, tx);
cmd.CreateParams(paras, types);
return(cmd);
}
private static void RenameStarTables(SQLExtConnection conn, string frompostfix, string topostfix)
{
conn.ExecuteNonQueries(new string[]
{
"ALTER TABLE Sectors" + frompostfix + " RENAME TO Sectors" + topostfix,
"ALTER TABLE Systems" + frompostfix + " RENAME TO Systems" + topostfix,
"ALTER TABLE Names" + frompostfix + " RENAME TO Names" + topostfix,
});
}
private static void DropStarTables(SQLExtConnection conn, string postfix = "")
{
conn.ExecuteNonQueries(new string[]
{
"DROP TABLE IF EXISTS Sectors" + postfix, // dropping the tables kills the indexes
"DROP TABLE IF EXISTS Systems" + postfix,
"DROP TABLE IF EXISTS Names" + postfix,
});
}
private static void UpgradeSystemsDB19(SQLExtConnection conn)
{
string query1 = "CREATE TABLE SystemAliases (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, id_edsm INTEGER, id_edsm_mergedto INTEGER)";
string query2 = "CREATE INDEX SystemAliases_name ON SystemAliases (name)";
string query3 = "CREATE UNIQUE INDEX SystemAliases_id_edsm ON SystemAliases (id_edsm)";
string query4 = "CREATE INDEX SystemAliases_id_edsm_mergedto ON SystemAliases (id_edsm_mergedto)";
conn.PerformUpgrade(19, true, false, new[] { query1, query2, query3, query4 });
}
private static void UpgradeUserDB101(SQLExtConnection conn)
{
string query1 = "DROP TABLE IF EXISTS Systems";
string query2 = "DROP TABLE IF EXISTS SystemAliases";
string query3 = "DROP TABLE IF EXISTS Distances";
string query4 = "VACUUM";
conn.PerformUpgrade(101, true, false, new[] { query1, query2, query3, query4 });
}
private static void UpgradeSystemsDB20(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN gridid Integer NOT NULL DEFAULT -1";
string query2 = "ALTER TABLE Systems ADD COLUMN randomid Integer NOT NULL DEFAULT -1";
conn.PerformUpgrade(20, true, false, new[] { query1, query2 }, () =>
{
SQLExtRegister reg = new SQLExtRegister(conn);
reg.PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00"); // force EDSM sync.. MUST do this manually, can't use main function as it needs internal one
});
}
private static void CreateStarTables(SQLExtConnection conn, string postfix = "")
{
conn.ExecuteNonQueries(new string[]
{
// purposely not using autoincrement or unique on primary keys - this slows it down.
"CREATE TABLE IF NOT EXISTS Sectors" + postfix + " (id INTEGER PRIMARY KEY NOT NULL, gridid INTEGER, name TEXT NOT NULL COLLATE NOCASE)",
"CREATE TABLE IF NOT EXISTS Systems" + postfix + " (edsmid INTEGER PRIMARY KEY NOT NULL , sectorid INTEGER, nameid INTEGER, x INTEGER, y INTEGER, z INTEGER)",
"CREATE TABLE IF NOT EXISTS Names" + postfix + " (id INTEGER PRIMARY KEY NOT NULL , Name TEXT NOT NULL COLLATE NOCASE )",
});
}
private static void UpgradeUserDB108(SQLExtConnection conn)
{
string query1 = "ALTER TABLE Commanders ADD COLUMN JournalDir TEXT";
conn.PerformUpgrade(108, true, false, new[] { query1 }, () =>
{
try
{
List <int> commandersToMigrate = new List <int>();
using (DbCommand cmd = conn.CreateCommand("SELECT Id, NetLogDir, JournalDir FROM Commanders"))
{
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int nr = Convert.ToInt32(rdr["Id"]);
object netlogdir = rdr["NetLogDir"];
object journaldir = rdr["JournalDir"];
if (netlogdir != DBNull.Value && journaldir == DBNull.Value)
{
string logdir = Convert.ToString(netlogdir);
if (logdir != null && System.IO.Directory.Exists(logdir) && System.IO.Directory.EnumerateFiles(logdir, "journal*.log").Any())
{
commandersToMigrate.Add(nr);
}
}
}
}
}
using (DbCommand cmd2 = conn.CreateCommand("UPDATE Commanders SET JournalDir=NetLogDir WHERE Id=@Nr"))
{
cmd2.AddParameter("@Nr", System.Data.DbType.Int32);
foreach (int nr in commandersToMigrate)
{
cmd2.Parameters["@Nr"].Value = nr;
cmd2.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("UpgradeUser108 exception: " + ex.Message);
}
});
}
private static void UpgradeSystemsDB101(SQLExtConnection conn)
{
string query1 = "DROP TABLE IF EXISTS Bookmarks";
string query2 = "DROP TABLE IF EXISTS SystemNote";
string query3 = "DROP TABLE IF EXISTS TravelLogUnit";
string query4 = "DROP TABLE IF EXISTS VisitedSystems";
string query5 = "DROP TABLE IF EXISTS Route_Systems";
string query6 = "DROP TABLE IF EXISTS Routes_expedition";
string query7 = "VACUUM";
conn.PerformUpgrade(101, true, false, new[] { query1, query2, query3, query4, query5, query6, query7 }, () =>
{
});
}
static public object SQLScalar(this SQLExtConnection cn, DbCommand cmd)
{
object ret = null;
try
{
ret = cmd.ExecuteScalar();
return(ret);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQuery Exception: " + ex.Message);
throw;
}
}
// Dodgy old ones - check this on re-integration
public static DataSet SQLQueryText(this SQLExtConnection cn, DbCommand cmd)
{
try
{
DataSet ds = new DataSet();
DbDataAdapter da = cn.CreateDataAdapter(cmd);
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlQuery Exception: " + ex.Message);
throw;
}
}
static public int SQLNonQueryText(this SQLExtConnection cn, DbCommand cmd)
{
int rows = 0;
try
{
rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQueryText Exception: " + ex.Message);
throw;
}
}
public static DbCommand CreateUpdate(this SQLExtConnection r, string table, string where, string[] paras, DbType[] types, DbTransaction tx = null)
{
string plist = "";
foreach (string s in paras)
{
plist = plist.AppendPrePad(s.Split(':')[0], ",");
}
string cmdtext = "UPDATE " + table + " SET " + plist + " " + where;
DbCommand cmd = r.CreateCommand(cmdtext, tx);
cmd.CreateParams(paras, types);
return(cmd);
}
static public List <string> Tables(this SQLExtConnection r)
{
List <string> tables = new List <string>();
using (DbCommand cmd = r.CreateCommand("select name From sqlite_master Where type='table'"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tables.Add((string)reader[0]);
}
}
}
return(tables);
}
public static string ExplainQueryPlanString(this SQLExtConnection r, DbCommand cmdexplain, bool listparas = true)
{
var ret = ExplainQueryPlan(r, cmdexplain);
string s = "SQL Query:" + Environment.NewLine + cmdexplain.CommandText + Environment.NewLine;
if (listparas && cmdexplain.Parameters.Count > 0)
{
foreach (System.Data.SQLite.SQLiteParameter p in cmdexplain.Parameters)
{
s += p.Value + " ";
}
s += Environment.NewLine;
}
return(s + "Plan:" + Environment.NewLine + string.Join(Environment.NewLine, ret));
}
// no paras, or delayed paras
public static DbCommand CreateSelect(this SQLExtConnection r, string table, string outparas, string where = null, string orderby = "",
string[] inparas = null, DbType[] intypes = null,
string[] joinlist = null, object limit = null, DbTransaction tx = null)
{
string lmt = "";
if (limit != null)
{
lmt = " LIMIT " + (limit is string?(string)limit : ((int)limit).ToStringInvariant());
}
string cmdtext = "SELECT " + outparas + " FROM " + table + " " + (joinlist != null ? string.Join(" ", joinlist) : "") +
(where.HasChars() ? (" WHERE " + where) : "") + (orderby.HasChars() ? (" ORDER BY " + orderby) : "") + lmt;
DbCommand cmd = r.CreateCommand(cmdtext, tx);
cmd.CreateParams(inparas, intypes);
return(cmd);
}
static public string SQLIntegrity(this SQLExtConnection r)
{
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
using (DbCommand cmd = r.CreateCommand("pragma Integrity_Check"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string ret = (string)reader[0];
System.Diagnostics.Debug.WriteLine($"Integrity check {r.ToString()} {ret} in {sw.ElapsedMilliseconds}ms");
return(ret);
}
}
}
return(null);
}
// for these, types are optional if paras given as name:type strings
public static DbCommand CreateInsert(this SQLExtConnection r, string table, string[] paras, DbType[] types = null, DbTransaction tx = null, bool insertorreplace = false, bool insertorignore = false)
{
string plist = "";
string atlist = "";
foreach (string s in paras)
{
string n = s.Split(':')[0];
plist = plist.AppendPrePad(n, ",");
atlist = atlist.AppendPrePad("@" + n, ",");
}
string cmdtext = "INSERT " + (insertorreplace ? "OR REPLACE " : "") + (insertorignore ? "OR IGNORE " : "") + "INTO " + table + " (" + plist + ") VALUES (" + atlist + ")";
DbCommand cmd = r.CreateCommand(cmdtext, tx);
cmd.CreateParams(paras, types);
return(cmd);
}