public void DatabaseStoreConstructor() { // Tests to ensure that the database has been created correctly. using (var dbConn = new SqliteConnection("Data Source=" + testFilename + ";Version=3")) { dbConn.Open(); using (var dbcmd = dbConn.CreateCommand()) { dbcmd.CommandText = "" + "SELECT name FROM sqlite_master " + "WHERE type='table' " + "ORDER BY name"; using (var dbrdr = dbcmd.ExecuteReader()) { dbrdr.Read(); StringAssert.IsMatch("fieldType", dbrdr.GetString(0), "DatabaseStore contains fieldType table."); dbrdr.Read(); StringAssert.IsMatch("fields", dbrdr.GetString(0), "DatabaseStore contains fields table."); dbrdr.Read(); StringAssert.IsMatch("fileRecord", dbrdr.GetString(0), "DatabaseStore contains fileRecord table."); dbrdr.Read(); StringAssert.IsMatch("recordType", dbrdr.GetString(0), "DatabaseStore contains recordType table."); dbrdr.Read(); StringAssert.IsMatch("records", dbrdr.GetString(0), "DatabaseStore contains records table."); Assert.False(dbrdr.Read(), "DatabaseStore contains additional tables."); } } using (var dbcmd = dbConn.CreateCommand()) { dbcmd.CommandText = "" + "SELECT recordTypeName FROM recordType"; using (var dbrdr = dbcmd.ExecuteReader()) { for (int i = 0; i < BibtexRecordTypeLibrary.Count(); i++) { dbrdr.Read(); StringAssert.IsMatch(BibtexRecordTypeLibrary.GetWithIndex(i).name, dbrdr.GetString(0)); } } } using (var dbcmd = dbConn.CreateCommand()) { dbcmd.CommandText = "" + "SELECT fieldTypeName FROM fieldType"; using (var dbrdr = dbcmd.ExecuteReader()) { for (int i = 0; i < BibtexRecordFieldTypeLibrary.Count(); i++) { dbrdr.Read(); StringAssert.IsMatch(BibtexRecordFieldTypeLibrary.GetWithIndex(i).name, dbrdr.GetString(0)); } } } dbConn.Close(); } }
public static void Initialize(string filename) { Environment.SetEnvironmentVariable("BIBLIOGRAPHER_DATABASE", filename); if (!File.Exists(filename)) { // Create database and create tables SqliteConnection.CreateFile(filename); using (SqliteConnection dbConn = SqlConn()) { dbConn.OpenAsync(); using (SqliteTransaction tr = dbConn.BeginTransaction()) { using (SqliteCommand dbcmd = dbConn.CreateCommand()) { dbcmd.Transaction = tr; dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS records (" + "id INTEGER PRIMARY KEY, " + "key TEXT, " + "recordTypeId INTEGER" + ")"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS fields (" + "id INTEGER PRIMARY KEY, " + "recordId INTEGER, " + "fieldTypeId INTEGER," + "fieldText TEXT" + ")"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE UNIQUE INDEX IF NOT EXISTS field_idx ON fields(recordId, fieldTypeId)"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS recordType (" + "id INTEGER PRIMARY KEY, " + "recordTypeName TEXT NOT NULL UNIQUE" + ")"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS fieldType (" + "id INTEGER PRIMARY KEY, " + "fieldTypeName TEXT NOT NULL UNIQUE" + ")"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS fileRecord (" + "id INTEGER PRIMARY KEY, " + "recordId INTEGER UNIQUE, " + "filename TEXT UNIQUE," + "size INTEGER," + "mtime INTEGER," + "md5sum TEXT" + ")"; dbcmd.ExecuteNonQueryAsync(); dbcmd.CommandText = "" + "CREATE TABLE IF NOT EXISTS searchData (" + "id INTEGER PRIMARY KEY, " + "recordId INTEGER UNIQUE, " + "data TEXT" + ")"; dbcmd.ExecuteNonQueryAsync(); } tr.Commit(); } dbConn.Close(); } } // Update recordType if new types have been implemented using (SqliteConnection dbConn = SqlConn()) { dbConn.OpenAsync(); using (SqliteTransaction tr = dbConn.BeginTransaction()) { using (SqliteCommand dbcmd = dbConn.CreateCommand()) { dbcmd.Transaction = tr; for (int i = 0; i < BibtexRecordTypeLibrary.Count(); i++) { dbcmd.CommandText = "" + "INSERT OR IGNORE INTO recordType (recordTypeName) " + "VALUES ('" + BibtexRecordTypeLibrary.GetWithIndex(i).name.ToLower() + "')"; dbcmd.ExecuteNonQueryAsync(); } for (int i = 0; i < BibtexRecordFieldTypeLibrary.Count(); i++) { dbcmd.CommandText = "" + "INSERT OR IGNORE INTO fieldType (fieldTypeName) " + "VALUES ('" + BibtexRecordFieldTypeLibrary.GetWithIndex(i).name.ToLower() + "')"; dbcmd.ExecuteNonQueryAsync(); } } tr.Commit(); } dbConn.Close(); } }