private static void InitDb() { if (!File.Exists(DbFilePath)) { SqliteConnection.CreateFile(DbFilePath); using (var conn = GetConnection()) { RunScript(Path.Combine(Directory.GetCurrentDirectory(), "DAL", "Scripts", "CreateDb.sql"), conn); RunScript(Path.Combine(Directory.GetCurrentDirectory(), "DAL", "Scripts", "InitDb.sql"), conn); } } }
/// <summary> /// This is run each time the app runs, but won't do anything after the first run. /// </summary> public void CreateDatabase() { try { if (!File.Exists(Settings.DatabaseFile)) { string categoriesSql = "CREATE TABLE \"categories\" (" + "\"id\" INTEGER PRIMARY KEY AUTOINCREMENT," + "\"name\" TEXT NOT NULL," + "\"active\" INTEGER DEFAULT 0," + "\"inbuilt\" INTEGER DEFAULT 0)"; string questionsSql = "CREATE TABLE questions (" + "\"id\" INTEGER PRIMARY KEY AUTOINCREMENT," + "\"categoryid\" INTEGER," + "\"title\" TEXT," + "\"answer\" TEXT," + "\"order\" INTEGER DEFAULT 0," + "\"lastasked\" INTEGER DEFAULT 0," + "\"nextaskon\" INTEGER DEFAULT 0," + "\"previousinterval\" INTEGER DEFAULT 0," + "\"interval\" INTEGER DEFAULT 0," + "\"askcount\" INTEGER DEFAULT 0," + "\"responsequality\" INTEGER DEFAULT 0," + "\"easinessfactor\" REAL DEFAULT 0)"; // Create the file SqliteConnection.CreateFile(Settings.DatabaseFile); // And the schema using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection)) { connection.Open(); using (SqliteCommand command = new SqliteCommand(connection)) { command.CommandText = categoriesSql; command.ExecuteNonQuery(); command.CommandText = questionsSql; command.ExecuteNonQuery(); // Default category command.CommandText = Default.Sql(); command.ExecuteNonQuery(); #if GERMAN command.CommandText = German.Sql(); command.ExecuteNonQuery(); #elif SPANISH command.CommandText = Spanish.Sql(); command.ExecuteNonQuery(); #elif FRENCH command.CommandText = French.Sql(); command.ExecuteNonQuery(); #endif } } } } catch (IOException ex) { Logger.Fatal("Unable to delete the database file {0}: \n{1}", Settings.DatabaseFile, ex); throw; } catch (SqliteException ex) { Logger.Fatal("Unable to create the database: \n{0}", ex); throw; } }