public void BackupDatabase(string filename)
{
//backups a database to file
// destination database must be open
if (DBConnection == null)
{
throw new InvalidOperationException("Database must be open for backup.");
}
try
{
// return on empty filename
if (String.IsNullOrEmpty(filename))
{
return;
}
// check for existing directory first --> create on if not
string dir = Path.GetDirectoryName(filename);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
// simply delete destination db if already exists
if (File.Exists(filename))
{
File.Delete(filename);
}
// get sql for creating structures
string sql = "select sql from sqlite_master where name not like 'sqlite_%'";
DataTable structure = this.Select(sql);
// get user tables
sql = "select name from sqlite_master where type = 'table' and name not like 'sqlite_%'";
DataTable usertables = this.Select(sql);
// crate identically structures in backup db
SQLiteDatabase backupdb = new SQLiteDatabase(filename);
backupdb.Open();
foreach (DataRow row in structure.Rows)
{
backupdb.DBCommand.CommandText = row[0].ToString();
backupdb.DBCommand.ExecuteNonQuery();
}
// close the backup db
backupdb.Close();
// attach backup db to source db
this.AttachDatabase(filename, "dest");
this.BeginTransaction();
// copy all user tables
foreach (DataRow row in usertables.Rows)
{
this.DBCommand.CommandText = "insert into dest." + row[0].ToString() + " select * from main." + row[0].ToString();
this.DBCommand.ExecuteNonQuery();
}
this.Commit();
this.DetachDatabase("dest");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public ScoutBaseDatabase()
{
try
{
// create dictionaries
versioninfo = new VersionInfo();
callsigns = new CallsignDictionary();
horizons = new HorizonDictionary();
// check if database path exists --> create if not
if (!Directory.Exists(DefaultDatabaseDirectory()))
{
Directory.CreateDirectory(DefaultDatabaseDirectory());
}
// check if database is already on disk
DBPath = Path.Combine(DefaultDatabaseDirectory(), "ScoutBase.db3");
if (!File.Exists(DBPath))
{
// create one on disk
System.Data.SQLite.SQLiteDatabase dbn = new System.Data.SQLite.SQLiteDatabase(DBPath);
dbn.Open();
dbn.CreateTable(new DataTableVersionInfo());
dbn.CreateTable(new DataTableCallsigns());
dbn.CreateTable(new DataTableHorizons());
dbn.CreateTable(new DataTableGLOBE());
dbn.CreateTable(new DataTableSRTM3());
dbn.CreateTable(new DataTableSRTM1());
if (String.IsNullOrEmpty(versioninfo.Version))
{
// no version info
// initally create one and store in database
versioninfo.Version = System.Reflection.Assembly.GetAssembly(typeof(ScoutBaseDatabase)).GetName().Version.ToString();
versioninfo.LastUpdated = DateTime.UtcNow;
dbn.InsertOrReplaceTable(versioninfo.WriteToTable());
}
dbn.Close();
}
if (Properties.Settings.Default.Database_InMemory)
{
db = System.Data.SQLite.SQLiteDatabase.CreateInMemoryDB(DBPath);
}
else
{
db = new System.Data.SQLite.SQLiteDatabase(DBPath);
db.Open();
}
// check version
if (String.Compare(System.Reflection.Assembly.GetAssembly(typeof(ScoutBaseDatabase)).GetName().Version.ToString(), versioninfo.Version) > 0)
{
// do any upgrade stuff here if necessary
}
// read data tables and initialize dictionaries
ReadDataTables();
}
catch (Exception ex)
{
Console.WriteLine("Error initilalizing database: " + ex.Message);
throw new TypeInitializationException(this.GetType().Name, ex);
}
}
public PcdbFileInfo(string path)
{
var db = new SQLiteDatabase(path);
db.Open();
Revision = PcdbUtil.GetRevision(db);
GameVersion = PcdbUtil.GetGameVersion(db);
db.Close();
}
public static SQLiteDatabase CreateInMemoryDB(string filename)
{
// returns a copy of a database in memory
// create an empty memory db first
SQLiteDatabase memdb = new SQLiteDatabase(":memory:");
try
{
// open an existing on disk db
SQLiteDatabase ondiskdb = new SQLiteDatabase(filename);
ondiskdb.Open();
// get user & schema version
int user_version = ondiskdb.GetUserVersion();
int schema_version = ondiskdb.GetSchemaVersion();
// get text encoding
string text_encoding = ondiskdb.GetTextEncoding();
// get sql for creating structures
string sql = "select sql from sqlite_master where name not like 'sqlite_%'";
DataTable structure = ondiskdb.Select(sql);
// get user tables
sql = "select name from sqlite_master where type = 'table' and name not like 'sqlite_%'";
DataTable usertables = ondiskdb.Select(sql);
// crate identically structures in memmory db
memdb.Open();
foreach (DataRow row in structure.Rows)
{
memdb.DBCommand.CommandText = row[0].ToString();
memdb.DBCommand.ExecuteNonQuery();
}
// set user & schema version
memdb.SetUserVerion(user_version);
memdb.SetSchemaVerion(schema_version);
// set text encoding
memdb.SetTextEncoding(text_encoding);
user_version = memdb.GetUserVersion();
text_encoding = memdb.GetTextEncoding();
// close on disk db
ondiskdb.Close();
// attach on disk db to memory db
memdb.AttachDatabase(filename, "ondisk");
memdb.BeginTransaction();
foreach (DataRow row in usertables.Rows)
{
memdb.DBCommand.CommandText = "insert into main." + row[0].ToString() + " select * from ondisk." + row[0].ToString();
memdb.DBCommand.ExecuteNonQuery();
}
memdb.Commit();
memdb.DetachDatabase("ondisk");
memdb.DiskFileName = filename;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(memdb);
}
protected System.Data.SQLite.SQLiteDatabase OpenDatabase(string name, string defaultdatabasedirectory, bool inmemory)
{
System.Data.SQLite.SQLiteDatabase db = null;
try
{
// check if database path exists --> create if not
if (!Directory.Exists(defaultdatabasedirectory))
{
Directory.CreateDirectory(defaultdatabasedirectory);
}
// check if database is already on disk
DBPath = defaultdatabasedirectory;
if (!File.Exists(Path.Combine(DBPath, name)))
{
// create one on disk
System.Data.SQLite.SQLiteDatabase dbn = new System.Data.SQLite.SQLiteDatabase(Path.Combine(DBPath, name));
// open database
dbn.Open();
// set user version
dbn.SetUserVerion(UserVersion);
// set auto vacuum mode to full
dbn.SetAutoVacuum(AUTOVACUUMMODE.FULL);
dbn.Close();
}
// check for in-memory database --> open from disk, if not
if (inmemory)
{
db = System.Data.SQLite.SQLiteDatabase.CreateInMemoryDB(Path.Combine(DBPath, name));
}
else
{
db = new System.Data.SQLite.SQLiteDatabase(Path.Combine(DBPath, name));
db.Open();
}
// get version info
int v = db.GetUserVersion();
// do upgrade stuff here
}
catch (Exception ex)
{
Console.WriteLine("Error initilalizing database: " + ex.Message);
throw new TypeInitializationException(this.GetType().Name, ex);
}
return(db);
}
public static PcdbGameVersion GetGameVersion(string filename)
{
var db = new SQLiteDatabase(filename);
db.Open();
try
{
return PcdbUtil.GetGameVersion(db);
}
finally
{
db.Close();
}
}
