void CheckDatabaseConnection()
{
try
{
DatabaseFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), @"Family Tree Analyzer\Geocodes.s3db");
if (!File.Exists(DatabaseFile))
{
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), @"Family Tree Analyzer");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
File.Copy(Path.Combine(Application.StartupPath, @"Resources\Geocodes-Empty.s3db"), DatabaseFile);
}
connectionString = $"Data Source={DatabaseFile};Version=3;";
}
catch (Exception ex)
{
UIHelpers.ShowMessage($"Error opening database. Error is :{ex.Message}", "FTAnalyzer");
}
}
public bool BackupDatabase(string saveDatabase, string comment)
{
//string directory = Application.UserAppDataRegistry.GetValue("Geocode Backup Directory", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).ToString();
string FileName = $"FTAnalyzer-Geocodes-{DateTime.Now.ToString("yyyy-MM-dd")}-{FamilyTree.Instance.Version}.zip";
//saveDatabase.InitialDirectory = directory;
//DialogResult result = saveDatabase.ShowDialog();
//if (result == DialogResult.OK)
{
StartBackupRestoreDatabase();
if (File.Exists(FileName))
{
File.Delete(FileName);
}
ZipFile zip = new ZipFile(FileName);
zip.AddFile(DatabaseFile, string.Empty);
zip.Comment = comment + " on " + DateTime.Now.ToString("dd MMM yyyy HH:mm");
zip.Save();
//EndBackupDatabase();
//Application.UserAppDataRegistry.SetValue("Geocode Backup Directory", Path.GetDirectoryName(saveDatabase.FileName));
UIHelpers.ShowMessage($"Database exported to {FileName}", "FTAnalyzer Database Export Complete");
return(true);
}
//return false;
}
void UpgradeDatabase(Version dbVersion)
{
try
{
Version v7_3_3_2 = new Version("7.3.3.2");
Version v7_4_0_0 = new Version("7.4.0.0");
Version v8_0_0_0 = new Version("8.0.0.0");
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
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 IF NOT EXISTS 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 IF NOT EXISTS 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();
}
}
if (dbVersion < v8_0_0_0)
{
using (SqliteCommand cmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS CustomFacts (FactType STRING(60) PRIMARY KEY, [Ignore] BOOLEAN)", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
using (SqliteCommand cmd = new SqliteCommand("update Versions set database = '8.0.0.0' where platform = 'PC'", InstanceConnection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
UIHelpers.ShowMessage($"Error upgrading database. Error is :{ex.Message}", "FTAnalyzer");
}
}
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");
}
}