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(); } }