void UpgradeDatabase(Version dbVersion)
{
try
{
Version v7_3_3_2 = new Version("7.3.3.2");
InstanceConnection.Close();
}
catch (Exception ex)
{
UIHelpers.ShowMessage($"Error upgrading database. Error is :{ex.Message}", "FTAnalyzer");
}
}
public static void LoadGeoLocations()
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
foreach (FactLocation loc in FactLocation.AllLocations)
{
ReadLocationIntoFact(loc, InstanceConnection);
}
InstanceConnection.Close();
}
public static void ResetPartials()
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set latitude = 0, longitude = 0, founddate = date('now'), foundlocation = '', foundlevel = -2, viewport_x_ne = 0, viewport_y_ne = 0, viewport_x_sw = 0, viewport_y_sw = 0, geocodestatus = 0, foundresulttype = '' where geocodestatus in (2,7,9)", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
InstanceConnection.Close();
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
try
{
if (InstanceConnection?.State == ConnectionState.Open)
{
InstanceConnection.Close();
}
InstanceConnection?.Dispose();
// dispose of things here
}
catch (Exception) { }
}
}
public void AddEmptyLocationsToQueue(ConcurrentQueue <FactLocation> queue)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SqliteCommand cmd = new SqliteCommand("select location from geocode where foundlocation='' and geocodestatus in (3, 8, 9)", InstanceConnection))
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FactLocation loc = FactLocation.LookupLocation(reader[0].ToString());
if (!queue.Contains(loc))
{
queue.Enqueue(loc);
}
}
}
}
InstanceConnection.Close();
}
static Version GetDatabaseVersion()
{
string db = null;
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand cmd = new SQLiteCommand("select Database from versions where platform='PC'", InstanceConnection))
{
db = (string)cmd.ExecuteScalar();
}
}
catch (Exception)
{ // use old method if current method fails
try
{
using (SQLiteCommand cmd = new SQLiteCommand("select Database from versions", InstanceConnection))
{
db = (string)cmd.ExecuteScalar();
}
}
catch { }
}
finally
{
InstanceConnection.Close();
}
Version dbVersion = db == null ? new Version("0.0.0.0") : new Version(db);
if (dbVersion == new Version("7.3.0.0"))
{
return(new Version("7.0.0.0")); // force old version so it updates after beta fix on v7.3.0.0
}
return(dbVersion);
}
static Version GetDatabaseVersion()
{
string db = null;
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SqliteCommand cmd = new SqliteCommand("select Database from versions where platform='Mac'", InstanceConnection))
{
db = (string)cmd.ExecuteScalar();
}
InstanceConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("Error in GetDatabaseVersion " + e.Message);
}
Version dbVersion = db == null ? new Version("0.0.0.0") : new Version(db);
return(dbVersion);
}
private static Version GetDatabaseVersion()
{
string db = null;
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand cmd = new SQLiteCommand("select Database from versions", InstanceConnection))
{
db = (string)cmd.ExecuteScalar();
}
InstanceConnection.Close();
}
catch (Exception)
{
//log.Error("Error in GetDatabaseVersion " + e.Message);
}
Version dbVersion = db == null ? new Version("0.0.0.0") : new Version(db);
return(dbVersion);
}
void UpgradeDatabase(Version dbVersion)
{
try
{
Version v3_0_0_0 = new Version("3.0.0.0");
Version v3_0_2_0 = new Version("3.0.2.0");
Version v3_1_2_0 = new Version("3.1.2.0");
Version v3_3_2_5 = new Version("3.3.2.5");
Version v7_0_0_0 = new Version("7.0.0.0");
Version v7_3_0_0 = new Version("7.3.0.0");
Version v7_3_0_1 = new Version("7.3.0.1");
Version v7_3_3_2 = new Version("7.3.3.2");
Version v7_4_0_0 = new Version("7.4.0.0");
if (dbVersion < v3_0_0_0)
{
// Version is less than 3.0.0.0 or none existent so copy latest database from empty database
GC.Collect(); // needed to force a cleanup of connections prior to replacing the file.
if (File.Exists(DatabaseFile))
{
File.Delete(DatabaseFile);
}
File.Copy(Path.Combine(Application.StartupPath, @"Resources\Geocodes-Empty.s3db"), DatabaseFile);
}
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
if (dbVersion < v3_0_2_0)
{
// Version v3.0.2.0 needs to reset Google Matches to not searched and set partials to level
//SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column GeocodeStatus integer default 0", conn);
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set geocodestatus=0 where geocodestatus=1", InstanceConnection))
{
cmd.ExecuteNonQuery(); // reset Google Match to Not Searched
}
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set geocodestatus=7 where geocodestatus=2", InstanceConnection))
{
cmd.ExecuteNonQuery(); // set to level mismatch if partial
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.0.2.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
MessageBox.Show("Please note that due to fixes in the way Google reports\nlocations your 'Google Matched' geocodes have been reset.", "FTAnalyzer");
}
if (dbVersion < v3_1_2_0)
{
bool proceed = false;
if (restoring)
{
proceed = true;
}
else
{
DialogResult result = MessageBox.Show("In order to improve speed of the maps a database upgrade is needed.\nThis may take several minutes and must be allowed to complete.\nYou must backup your database first. Ok to proceed?", "Database upgrading", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
Application.UseWaitCursor = true;
if (result == DialogResult.Yes)
{
SaveFileDialog sfd = new SaveFileDialog();
proceed = BackupDatabase(sfd, "FTAnalyzer zip file created by Database upgrade for v3.2.1.0");
sfd.Dispose();
}
Application.UseWaitCursor = false;
}
if (proceed)
{
bool latm = false;
bool longm = false;
using (SQLiteCommand cmd = new SQLiteCommand("PRAGMA table_info('geocode')", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string column = reader[1].ToString();
if (column.Equals("Latm"))
{
latm = true;
}
if (column.Equals("Longm"))
{
longm = true;
}
}
}
}
if (!latm)
{
using (SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column Latm real default 0.0", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (!longm)
{
using (SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column Longm real default 0.0", InstanceConnection))
{
cmd.ExecuteNonQuery();
ConvertLatLongs();
}
}
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set foundlocation='', foundlevel=-2 where geocodestatus=3", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.2.1.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
MessageBox.Show("Database lat/long upgrade complete", "FTAnalyzer");
}
else
{
MessageBox.Show("Database not backed up we cannot proceed to update maps without a safe database backup.\nMapping features will not work correctly.", "Database backup Required");
}
}
if (dbVersion < v3_3_2_5)
{
// mark all bad viewports as not searched
using (SQLiteCommand cmd = new SQLiteCommand("update Geocode set latitude = 0, longitude = 0, founddate = date('now'), foundlocation = '', foundlevel = -2, viewport_x_ne = 0, viewport_y_ne = 0, viewport_x_sw = 0, viewport_y_sw = 0, geocodestatus = 0, foundresulttype = '' where latitude<>0 and longitude<>0 and abs(viewport_x_ne) <= 180", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.3.2.5'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (dbVersion < v7_0_0_0)
{
using (SQLiteCommand cmd = new SQLiteCommand("update Geocode set geocodestatus = 0 where latitude=0 and longitude=0 and geocodestatus=3", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '7.0.0.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (dbVersion < v7_3_0_0)
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand("create table LostCousins (CensusYear INTEGER(4), CensusCountry STRING (20), CensusRef STRING(25), IndID STRING(10), FullName String(80), constraint pkLostCousins primary key (CensusYear, CensusCountry, CensusRef, IndID))", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '7.3.0.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException) { } // skip if table already exists.
}
if (dbVersion < v7_3_0_1)
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand("update table LostCousins add column FullName String(80)", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException) { } // don't complain if adding field already exists due to beta testing.
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '7.3.0.1'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (dbVersion < v7_3_3_2)
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand("SELECT count(*) FROM LostCousins", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException)
{
using (SQLiteCommand cmd = new SQLiteCommand("create table LostCousins (CensusYear INTEGER(4), CensusCountry STRING (20), CensusRef STRING(25), IndID STRING(10), FullName String(80), constraint pkLostCousins primary key (CensusYear, CensusCountry, CensusRef, IndID))", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '7.3.3.2'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (dbVersion < v7_4_0_0)
{
using (SQLiteCommand cmd = new SQLiteCommand("drop table versions", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("CREATE TABLE Versions(Platform VARCHAR(10) PRIMARY KEY, [Database] VARCHAR(10));", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("insert into Versions(platform, database) values('PC', '7.4.0.0')", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("insert into Versions(platform, database) values('Mac', '1.2.0.42')", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
InstanceConnection.Close();
}
catch (Exception ex)
{
UIHelpers.ShowMessage($"Error upgrading database. Error is :{ex.Message}", "FTAnalyzer");
}
}
private void UpgradeDatabase(Version dbVersion)
{
try
{
Version v3_0_0_0 = new Version("3.0.0.0");
Version v3_0_2_0 = new Version("3.0.2.0");
Version v3_1_2_0 = new Version("3.1.2.0");
Version v3_3_2_5 = new Version("3.3.2.5");
Version v7_0_0_0 = new Version("7.0.0.0");
if (dbVersion < v3_0_0_0)
{
// Version is less than 3.0.0.0 or none existent so copy latest database from empty database
GC.Collect(); // needed to force a cleanup of connections prior to replacing the file.
if (File.Exists(Filename))
{
File.Delete(Filename);
}
File.Copy(Path.Combine(Application.StartupPath, @"Resources\Geocodes-Empty.s3db"), Filename);
}
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
if (dbVersion < v3_0_2_0)
{
// Version v3.0.2.0 needs to reset Google Matches to not searched and set partials to level
//SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column GeocodeStatus integer default 0", conn);
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set geocodestatus=0 where geocodestatus=1", InstanceConnection))
{
cmd.ExecuteNonQuery(); // reset Google Match to Not Searched
}
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set geocodestatus=7 where geocodestatus=2", InstanceConnection))
{
cmd.ExecuteNonQuery(); // set to level mismatch if partial
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.0.2.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
MessageBox.Show("Please note that due to fixes in the way Google reports\nlocations your 'Google Matched' geocodes have been reset.", "FTAnalyzer");
}
if (dbVersion < v3_1_2_0)
{
bool proceed = false;
if (restoring)
{
proceed = true;
}
else
{
DialogResult result = MessageBox.Show("In order to improve speed of the maps a database upgrade is needed.\nThis may take several minutes and must be allowed to complete.\nYou must backup your database first. Ok to proceed?", "Database upgrading", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
Application.UseWaitCursor = true;
if (result == DialogResult.Yes)
{
proceed = BackupDatabase(new SaveFileDialog(), "FTAnalyzer zip file created by Database upgrade for v3.2.1.0");
}
Application.UseWaitCursor = false;
}
if (proceed)
{
bool latm = false;
bool longm = false;
using (SQLiteCommand cmd = new SQLiteCommand("PRAGMA table_info('geocode')", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string column = reader[1].ToString();
if (column.Equals("Latm"))
{
latm = true;
}
if (column.Equals("Longm"))
{
longm = true;
}
}
}
}
if (!latm)
{
using (SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column Latm real default 0.0", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (!longm)
{
using (SQLiteCommand cmd = new SQLiteCommand("alter table geocode add column Longm real default 0.0", InstanceConnection))
{
cmd.ExecuteNonQuery();
ConvertLatLongs();
}
}
using (SQLiteCommand cmd = new SQLiteCommand("update geocode set foundlocation='', foundlevel=-2 where geocodestatus=3", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.2.1.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
MessageBox.Show("Database lat/long upgrade complete", "FTAnalyzer");
}
else
{
MessageBox.Show("Database not backed up we cannot proceed to update maps without a safe database backup.\nMapping features will not work correctly.", "Database backup Required");
}
}
if (dbVersion < v3_3_2_5)
{
// mark all bad viewports as not searched
using (SQLiteCommand cmd = new SQLiteCommand("update Geocode set latitude = 0, longitude = 0, founddate = date('now'), foundlocation = '', foundlevel = -2, viewport_x_ne = 0, viewport_y_ne = 0, viewport_x_sw = 0, viewport_y_sw = 0, geocodestatus = 0, foundresulttype = '' where latitude<>0 and longitude<>0 and abs(viewport_x_ne) <= 180", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '3.3.2.5'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
if (dbVersion < v7_0_0_0)
{
using (SQLiteCommand cmd = new SQLiteCommand("update Geocode set geocodestatus = 0 where latitude=0 and longitude=0 and geocodestatus=3", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = new SQLiteCommand("update versions set Database = '7.0.0.0'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
InstanceConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show($"Error upgrading database. Error is :{ex.Message}", "FTAnalyzer");
}
}