Merge lp:~kalikiana/midori/dbupdate into lp:midori

[Cris Dywan]

Refactor excuting schema from file into a function

[lp:~kalikiana/midori/dbupdate]

6406. By André Stösel on 2013-09-18

fix Create.sql to avoid errors if the db already exists

[André Stösel (ivaldi)]

review: Approve

=== added directory 'data/forms'
=== added file 'data/forms/Create.sql'
--- data/forms/Create.sql	1970-01-01 00:00:00 +0000
+++ data/forms/Create.sql	2013-09-18 16:11:48 +0000
@@ -0,0 +1,6 @@
+CREATE TABLE IF NOT EXISTS forms
+(
+    domain text,
+    field text,
+    value text
+)

=== added file 'data/history/Day.sql'
--- data/history/Day.sql	1970-01-01 00:00:00 +0000
+++ data/history/Day.sql	2013-09-18 16:11:48 +0000
@@ -0,0 +1,14 @@
+CREATE TEMPORARY TABLE backup
+(
+    uri text,
+    title text,
+    date integer
+);
+INSERT INTO backup SELECT uri, title, date FROM history;
+DROP TABLE history;
+CREATE TABLE history (uri text, title text, date integer, day integer);
+INSERT INTO history SELECT uri, title, date,
+    julianday(date(date,'unixepoch','start of day','+1 day'))
+    - julianday('0001-01-01','start of day')
+    FROM backup;
+DROP TABLE backup;

=== modified file 'extensions/formhistory/formhistory-frontend.h'
--- extensions/formhistory/formhistory-frontend.h	2013-06-17 19:15:01 +0000
+++ extensions/formhistory/formhistory-frontend.h	2013-09-18 16:11:48 +0000
@@ -22,6 +22,7 @@
 
 typedef struct
 {
+    MidoriDatabase* database;
     sqlite3* db;
     WebKitDOMElement* element;
     int completion_timeout;

=== modified file 'extensions/formhistory/formhistory-gdom-frontend.c'
--- extensions/formhistory/formhistory-gdom-frontend.c	2013-06-17 19:15:01 +0000
+++ extensions/formhistory/formhistory-gdom-frontend.c	2013-09-18 16:11:48 +0000
@@ -457,11 +457,7 @@
 void
 formhistory_private_destroy (FormHistoryPriv *priv)
 {
-    if (priv->db)
-    {
-        sqlite3_close (priv->db);
-        priv->db = NULL;
-    }
+    katze_object_assign (priv->database, NULL);
     katze_assign (priv->oldkeyword, NULL);
     gtk_widget_destroy (priv->popup);
     priv->popup = NULL;

=== modified file 'extensions/formhistory/formhistory.c'
--- extensions/formhistory/formhistory.c	2013-08-12 19:21:06 +0000
+++ extensions/formhistory/formhistory.c	2013-09-18 16:11:48 +0000
@@ -489,44 +489,25 @@
 formhistory_new (const gchar* config_dir)
 {
     gchar* filename;
-    sqlite3* db;
-    char* errmsg = NULL, *errmsg2 = NULL;
+    GError* error = NULL;
     FormHistoryPriv* priv = formhistory_private_new ();
     priv->master_password = NULL;
     priv->master_password_canceled = 0;
     formhistory_construct_popup_gui (priv);
 
     filename = g_build_filename (config_dir, "forms.db", NULL);
-    if (sqlite3_open (filename, &db) != SQLITE_OK)
-    {
-        g_warning (_("Failed to open database: %s\n"), sqlite3_errmsg (db));
-        sqlite3_close (db);
-    }
+    priv->database = midori_database_new (filename, &error);
     g_free (filename);
-    if ((sqlite3_exec (db, "CREATE TABLE IF NOT EXISTS "
-                           "forms (domain text, field text, value text)",
-                           NULL, NULL, &errmsg) == SQLITE_OK))
-    {
-        sqlite3_exec (db,
-            /* "PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY" */
-            "PRAGMA count_changes = OFF; PRAGMA journal_mode = TRUNCATE;",
-            NULL, NULL, &errmsg);
-        priv->db = db;
-    }
-    else
-    {
-        if (errmsg)
-        {
-            g_critical (_("Failed to execute database statement: %s\n"), errmsg);
-            sqlite3_free (errmsg);
-            if (errmsg2)
-            {
-                g_critical (_("Failed to execute database statement: %s\n"), errmsg2);
-                sqlite3_free (errmsg2);
-            }
-        }
-        sqlite3_close (db);
-    }
+    if (error != NULL)
+    {
+        g_critical ("%s", error->message);
+        g_error_free (error);
+        priv->db = NULL;
+        return priv;
+    }
+
+    priv->db = midori_database_get_db (MIDORI_DATABASE (priv->database));
+    g_warn_if_fail (priv->db != NULL);
     return priv;
 }
 

=== modified file 'midori/midori-database.vala'
--- midori/midori-database.vala	2013-09-13 21:24:57 +0000
+++ midori/midori-database.vala	2013-09-18 16:11:48 +0000
@@ -12,7 +12,8 @@
 namespace Midori {
     public errordomain DatabaseError {
         OPEN,
-        SCHEMA,
+        NAMING,
+        FILENAME,
         EXECUTE,
     }
 
@@ -48,22 +49,54 @@
 
             if (db.exec ("PRAGMA journal_mode = WAL; PRAGMA cache_size = 32100;") != Sqlite.OK)
                 db.exec ("PRAGMA synchronous = NORMAL; PRAGMA temp_store = MEMORY;");
-
+            db.exec ("PRAGMA count_changes = OFF;");
+
+            int64 version, user_version;
+            Sqlite.Statement stmt;
+            if (db.prepare_v2 ("PRAGMA user_version;", -1, out stmt, null) != Sqlite.OK)
+                throw new DatabaseError.EXECUTE ("Failed to compile statement %s".printf (db.errmsg ()));
+            if (stmt.step () != Sqlite.ROW)
+                throw new DatabaseError.EXECUTE ("Failed to get row %s".printf (db.errmsg ()));
+            version = user_version = stmt.column_int64 (0);
+
+            if (version == 0) {
+                exec_script ("Create");
+                user_version = version = 1;
+                exec ("PRAGMA user_version = " + user_version.to_string ());
+            }
+
+            while (true) {
+                int64 new_version = version + 1;
+                try {
+                    exec_script ("Update" + new_version.to_string ());
+                } catch (DatabaseError error) {
+                    if (error is DatabaseError.FILENAME)
+                        break;
+                    throw error;
+                }
+                user_version = new_version;
+                exec ("PRAGMA user_version = " + user_version.to_string ());
+            }
+
+            first_use = !exists;
+            return true;
+        }
+
+        public bool exec_script (string filename) throws DatabaseError {
             string basename = Path.get_basename (path);
             string[] parts = basename.split (".");
             if (!(parts != null && parts[0] != null && parts[1] != null))
-                throw new DatabaseError.SCHEMA ("Failed to deduce schema filename from %s".printf (path));
-            string schema_filename = Midori.Paths.get_res_filename (parts[0] + "/Create.sql");
+                throw new DatabaseError.NAMING ("Failed to deduce schema filename from %s".printf (path));
+            string schema_filename = Midori.Paths.get_res_filename (parts[0] + "/" + filename + ".sql");
             string schema;
             try {
                 FileUtils.get_contents (schema_filename, out schema, null);
             } catch (Error error) {
-                throw new DatabaseError.SCHEMA ("Failed to open schema: %s".printf (schema_filename));
+                throw new DatabaseError.FILENAME ("Failed to open schema: %s".printf (schema_filename));
             }
+            schema = "BEGIN TRANSACTION; %s; COMMIT;".printf (schema);
             if (db.exec (schema) != Sqlite.OK)
                 throw new DatabaseError.EXECUTE ("Failed to execute schema: %s".printf (schema));
-
-            first_use = !exists;
             return true;
         }
 

=== modified file 'midori/midori-history.c'
--- midori/midori-history.c	2013-09-13 21:24:57 +0000
+++ midori/midori-history.c	2013-09-18 16:11:48 +0000
@@ -33,11 +33,6 @@
     MidoriHistoryDatabase* database;
     GError* error = NULL;
     sqlite3* db;
-    gboolean has_day = FALSE;
-    sqlite3_stmt* stmt;
-    gint result;
-    gchar* sql;
-    gchar* bookmarks_filename;
     KatzeArray* array;
 
     g_return_val_if_fail (errmsg != NULL, NULL);
@@ -53,27 +48,6 @@
     db = midori_database_get_db (MIDORI_DATABASE (database));
     g_return_val_if_fail (db != NULL, NULL);
 
-    sqlite3_prepare_v2 (db, "SELECT day FROM history LIMIT 1", -1, &stmt, NULL);
-    result = sqlite3_step (stmt);
-    if (result == SQLITE_ROW)
-        has_day = TRUE;
-    sqlite3_finalize (stmt);
-
-    if (!has_day)
-        sqlite3_exec (db,
-                      "BEGIN TRANSACTION;"
-                      "CREATE TEMPORARY TABLE backup (uri text, title text, date integer);"
-                      "INSERT INTO backup SELECT uri,title,date FROM history;"
-                      "DROP TABLE history;"
-                      "CREATE TABLE history (uri text, title text, date integer, day integer);"
-                      "INSERT INTO history SELECT uri,title,date,"
-                      "julianday(date(date,'unixepoch','start of day','+1 day'))"
-                      " - julianday('0001-01-01','start of day')"
-                      "FROM backup;"
-                      "DROP TABLE backup;"
-                      "COMMIT;",
-                      NULL, NULL, errmsg);
-
     array = katze_array_new (KATZE_TYPE_ARRAY);
     g_object_set_data (G_OBJECT (array), "db", db);
     g_signal_connect (array, "clear",

=== modified file 'midori/midori-historydatabase.vala'
--- midori/midori-historydatabase.vala	2013-09-13 21:24:57 +0000
+++ midori/midori-historydatabase.vala	2013-09-18 16:11:48 +0000
@@ -41,6 +41,12 @@
             init ();
             string bookmarks_filename = Midori.Paths.get_config_filename_for_writing ("bookmarks_v2.db");
             exec ("ATTACH DATABASE '%s' AS bookmarks".printf (bookmarks_filename));
+
+            try {
+                exec ("SELECT day FROM history LIMIT 1");
+            } catch (Error error) {
+                exec_script ("Day");
+            }
         }
 
         public async List<HistoryItem>? query (string sqlcmd, string? filter, int day, int max_items, Cancellable cancellable) {