public void DataStoreSetUp()
{
if (Environment.OSVersion.Platform == PlatformID.Win32NT)
{
Environment.SetEnvironmentVariable("BIBTEX_TYPE_LIB", Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\bibliographer\\bibtex_records");
Environment.SetEnvironmentVariable("BIBTEX_FIELDTYPE_LIB", Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\bibliographer\\bibtex_fields");
testFilename = Path.GetTempPath() + "\\datastoretest.sqlite";
}
else
{
Environment.SetEnvironmentVariable("BIBTEX_TYPE_LIB", Environment.GetEnvironmentVariable("HOME") + "/.config/bibliographer/bibtex_records");
Environment.SetEnvironmentVariable("BIBTEX_FIELDTYPE_LIB", Environment.GetEnvironmentVariable("HOME") + "/.config/bibliographer/bibtex_fields");
testFilename = "/tmp/datastoretest.sqlite";
}
BibtexRecordTypeLibrary.Load();
BibtexRecordFieldTypeLibrary.Load();
// Delete testfilename if it exists so that we can check correct creation
if (File.Exists(testFilename))
{
File.Delete(testFilename);
}
Assert.IsFalse(File.Exists(testFilename), "File: " + testFilename + " exists prior to databaseStore constructor.");
DatabaseStoreStatic.Initialize(testFilename);
Assert.IsTrue(File.Exists(testFilename), "File: " + testFilename + " has been created by the databaseStore constructor.");
}
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();
}
}
