public static bool IgnoreCustomFact(string factType)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
bool result = false;
using (SqliteCommand cmd = new SqliteCommand("SELECT EXISTS(SELECT ignore FROM CustomFacts where FactType=?)", InstanceConnection))
{
SqliteParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
cmd.Prepare();
cmd.Parameters[0].Value = factType;
using (SqliteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.Read())
{
result = reader[0].ToString() == "1";
}
}
}
return(result);
}
public static void StoreLostCousinsFact(CensusIndividual ind, IProgress <string> outputText)
{
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
SqliteParameter param;
using (SqliteCommand cmd = new SqliteCommand("insert into LostCousins (CensusYear, CensusCountry, CensusRef, IndID, FullName) values(?,?,?,?,?)", InstanceConnection))
{
param = cmd.CreateParameter();
param.DbType = DbType.Int32;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
if (ind.CensusReference != null)
{
cmd.Parameters[0].Value = ind.CensusDate.BestYear;
cmd.Parameters[1].Value = ind.CensusCountry;
cmd.Parameters[2].Value = ind.CensusReference;
cmd.Parameters[3].Value = ind.IndividualID;
cmd.Parameters[4].Value = ind.Name;
int rowsaffected = cmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
outputText.Report($"\nProblem updating record in database update affected {rowsaffected} records.");
}
else
{
FactLocation location = FactLocation.GetLocation(ind.CensusCountry);
Fact f = new Fact(ind.CensusRef, Fact.LC_FTA, ind.CensusDate, location, string.Empty, true, true);
Individual person = FamilyTree.Instance.GetIndividual(ind.IndividualID); // get the individual not the census indvidual
person?.AddFact(f);
}
}
}
}
catch (Exception e)
{
outputText.Report($"\nFailed to save Lost Cousins record in database error was: {e.Message}");
}
}
public static void GetLocationDetails(FactLocation location)
{
if (location.ToString().Length == 0)
{
return;
}
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
ReadLocationIntoFact(location, InstanceConnection);
}
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();
}
public static void StoreLostCousinsFact(CensusIndividual ind, IProgress <string> outputText)
{
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
SQLiteParameter param;
using (SQLiteCommand cmd = new SQLiteCommand("insert into LostCousins (CensusYear, CensusCountry, CensusRef, IndID, FullName) values(?,?,?,?,?)", InstanceConnection))
{
param = cmd.CreateParameter();
param.DbType = DbType.Int32;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
if (ind.CensusReference != null)
{
cmd.Parameters[0].Value = ind.CensusDate.BestYear;
cmd.Parameters[1].Value = ind.CensusCountry;
cmd.Parameters[2].Value = ind.CensusReference;
cmd.Parameters[3].Value = ind.IndividualID;
cmd.Parameters[4].Value = ind.Name;
int rowsaffected = cmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
outputText.Report($"\nProblem updating record in database update affected {rowsaffected} records.");
}
}
}
}
catch (Exception e)
{
outputText.Report($"\nFailed to save Lost Cousins record in database error was: {e.Message}");
}
}
public static int AddLostCousinsFacts()
{
int count = 0;
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SqliteCommand cmd = new SqliteCommand("select CensusYear, CensusCountry, CensusRef, IndID, FullName from LostCousins", InstanceConnection))
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string indID = reader["IndID"].ToString();
string fullName = reader["FullName"].ToString();
Individual ind = FamilyTree.Instance.GetIndividual(indID);
if (ind?.Name == fullName) // only load if individual exists in this tree.
{
string CensusYear = reader["CensusYear"].ToString();
string CensusCountry = reader["CensusCountry"].ToString();
string CensusRef = reader["CensusRef"].ToString();
if (!ind.IsLostCousinsEntered(CensusDate.GetLostCousinsCensusYear(new FactDate(CensusYear), true)))
{
FactLocation location = FactLocation.GetLocation(CensusCountry);
Fact f = new Fact(CensusRef, Fact.LOSTCOUSINS, new FactDate(CensusYear), location, string.Empty, true, true);
ind?.AddFact(f);
}
count++;
}
else
{
Console.Write("name wrong");
// UpdateFullName(reader, ind.Name); needed during testing
}
}
}
}
return(count);
}
public static bool LostCousinsExists(CensusIndividual ind)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
bool result = false;
using (SqliteCommand cmd = new SqliteCommand("SELECT EXISTS(SELECT 1 FROM LostCousins where CensusYear=? and CensusCountry=? and CensusRef=? and IndID=?)", InstanceConnection))
{
SqliteParameter param = cmd.CreateParameter();
param.DbType = DbType.Int32;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
cmd.Parameters[0].Value = ind.CensusDate.BestYear;
cmd.Parameters[1].Value = ind.CensusCountry;
cmd.Parameters[2].Value = ind.CensusReference;
cmd.Parameters[3].Value = ind.IndividualID;
using (SqliteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.Read())
{
result = reader[0].ToString() == "1";
}
}
}
return(result);
}
public static bool IsLocationInDatabase(string location)
{
bool inDatabase;
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand cmd = new SQLiteCommand("select location from geocode where location = ?", InstanceConnection))
{
SQLiteParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
cmd.Parameters[0].Value = location;
using (SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
inDatabase = reader.Read();
}
}
return(inDatabase);
}
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);
}
public static void InsertGeocode(FactLocation loc)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
SQLiteParameter param;
using (SQLiteCommand insertCmd = new SQLiteCommand("insert into geocode (location, level, latitude, longitude, founddate, foundlocation, foundlevel, viewport_x_ne, viewport_y_ne, viewport_x_sw, viewport_y_sw, geocodestatus, foundresulttype, latm, longm) values(?,?,?,?,date('now'),?,?,?,?,?,?,?,?,?,?)", InstanceConnection))
{
param = insertCmd.CreateParameter();
param.DbType = DbType.String;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Int32;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.String;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Int32;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Int32;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.String;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
param = insertCmd.CreateParameter();
param.DbType = DbType.Double;
insertCmd.Parameters.Add(param);
insertCmd.Prepare();
insertCmd.Parameters[0].Value = loc.ToString();
insertCmd.Parameters[1].Value = loc.Level;
insertCmd.Parameters[2].Value = loc.Latitude;
insertCmd.Parameters[3].Value = loc.Longitude;
insertCmd.Parameters[4].Value = loc.FoundLocation;
insertCmd.Parameters[5].Value = loc.FoundLevel;
insertCmd.Parameters[6].Value = loc.ViewPort.NorthEast.Long;
insertCmd.Parameters[7].Value = loc.ViewPort.NorthEast.Lat;
insertCmd.Parameters[8].Value = loc.ViewPort.SouthWest.Long;
insertCmd.Parameters[9].Value = loc.ViewPort.SouthWest.Lat;
insertCmd.Parameters[10].Value = loc.GeocodeStatus;
insertCmd.Parameters[11].Value = loc.FoundResultType;
insertCmd.Parameters[12].Value = loc.LatitudeM;
insertCmd.Parameters[13].Value = loc.LongitudeM;
int rowsaffected = insertCmd.ExecuteNonQuery();
}
}
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");
}
}
void ConvertLatLongs()
{
Coordinate Point, NorthEast, SouthWest;
Coordinate mPoint, mNorthEast, mSouthWest;
double latitude, longitude, viewport_x_ne, viewport_y_ne, viewport_x_sw, viewport_y_sw;
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
int rowcount = 0;
using (SQLiteCommand cmd = new SQLiteCommand("select count(*) from geocode where latitude <> 0 and longitude <> 0", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
reader.Read();
int.TryParse(reader[0].ToString(), out rowcount);
}
}
#region update cmd
using (SQLiteCommand updateCmd = new SQLiteCommand("update geocode set latm=?, longm=?, viewport_x_ne=?, viewport_y_ne=?, viewport_x_sw=?, viewport_y_sw=? where location = ?", InstanceConnection))
{
SQLiteParameter param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
updateCmd.Prepare();
Progress p = new Progress(rowcount);
p.Show();
int row = 0;
using (SQLiteCommand cmd = new SQLiteCommand("select location, latitude, longitude, viewport_x_ne, viewport_y_ne, viewport_x_sw, viewport_y_sw from geocode where latitude <> 0 and longitude <> 0", InstanceConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
latitude = longitude = viewport_x_ne = viewport_x_sw = viewport_y_ne = viewport_y_sw = 0;
string location = reader["location"].ToString();
double.TryParse(reader["latitude"].ToString(), out latitude);
double.TryParse(reader["longitude"].ToString(), out longitude);
double.TryParse(reader["viewport_x_ne"].ToString(), out viewport_x_ne);
double.TryParse(reader["viewport_y_ne"].ToString(), out viewport_y_ne);
double.TryParse(reader["viewport_x_sw"].ToString(), out viewport_x_sw);
double.TryParse(reader["viewport_y_sw"].ToString(), out viewport_y_sw);
Point = new Coordinate(longitude, latitude);
NorthEast = new Coordinate(viewport_y_ne, viewport_x_ne); // old viewports had x & y wrong way round
SouthWest = new Coordinate(viewport_y_sw, viewport_x_sw); // x is stored as lat y as long
mPoint = MapTransforms.TransformCoordinate(Point);
mNorthEast = MapTransforms.TransformCoordinate(NorthEast);
mSouthWest = MapTransforms.TransformCoordinate(SouthWest);
// now write back the m versions
updateCmd.Parameters[0].Value = mPoint.Y;
updateCmd.Parameters[1].Value = mPoint.X;
updateCmd.Parameters[2].Value = mNorthEast.X;
updateCmd.Parameters[3].Value = mNorthEast.Y;
updateCmd.Parameters[4].Value = mSouthWest.X;
updateCmd.Parameters[5].Value = mSouthWest.Y;
updateCmd.Parameters[6].Value = location;
updateCmd.ExecuteNonQuery();
p.Update(++row);
}
}
}
p.Dispose();
}
#endregion
}
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");
}
}
public static void UpdateGeocode(FactLocation loc)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
using (SQLiteCommand updateCmd = new SQLiteCommand("update geocode set founddate=date('now'), level = ?, latitude = ?, longitude = ?, foundlocation = ?, foundlevel = ?, viewport_x_ne = ?, viewport_y_ne = ?, viewport_x_sw = ?, viewport_y_sw = ?, geocodestatus = ?, foundresulttype = ?, latm = ?, longm = ? where location = ?", InstanceConnection))
{
SQLiteParameter param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Double;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
updateCmd.Prepare();
updateCmd.Parameters[0].Value = loc.Level;
updateCmd.Parameters[1].Value = loc.Latitude;
updateCmd.Parameters[2].Value = loc.Longitude;
updateCmd.Parameters[3].Value = loc.FoundLocation;
updateCmd.Parameters[4].Value = loc.FoundLevel;
updateCmd.Parameters[5].Value = loc.ViewPort.NorthEast.Long;
updateCmd.Parameters[6].Value = loc.ViewPort.NorthEast.Lat;
updateCmd.Parameters[7].Value = loc.ViewPort.SouthWest.Long;
updateCmd.Parameters[8].Value = loc.ViewPort.SouthWest.Lat;
updateCmd.Parameters[9].Value = loc.GeocodeStatus;
updateCmd.Parameters[10].Value = loc.FoundResultType;
updateCmd.Parameters[11].Value = loc.LatitudeM;
updateCmd.Parameters[12].Value = loc.LongitudeM;
updateCmd.Parameters[13].Value = loc.ToString();
int rowsaffected = updateCmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
Console.WriteLine("Problem updating");
}
OnGeoLocationUpdated(loc);
}
}
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");
}
}