zeitgeist team mailing list archive

[Siegfried Gevatter <rainct@xxxxxxxxxx>]

Siegfried Gevatter has proposed merging lp:~rainct/zeitgeist/schema6 into lp:zeitgeist.

Requested reviews:
  Zeitgeist Framework Team (zeitgeist)

For more details, see:
https://code.launchpad.net/~rainct/zeitgeist/schema6/+merge/92519
-- 
https://code.launchpad.net/~rainct/zeitgeist/schema6/+merge/92519
Your team Zeitgeist Framework Team is requested to review the proposed merge of lp:~rainct/zeitgeist/schema6 into lp:zeitgeist.

=== modified file 'NEWS'
--- NEWS	2012-02-10 14:42:54 +0000
+++ NEWS	2012-02-10 17:02:09 +0000
@@ -6,6 +6,8 @@
  - The FTS module in Python has been replaced with a C/C++ implementation.
  - Pre-process events *before* they are send to extensions (LP: #628804).
  - Minor fixes.
+ - Introduced new DB schema (version 6); cached tables may no longer
+   re-use row IDs.
 
 Python API:
  - Fixed signal/monitor reconnection to avoid duplicated notifications.
@@ -42,9 +44,10 @@
 
 Engine:
 
- - The language for extensions has changes from Python to Vala.
+ - Changed the language for extensions from Python to Vala.
  - The post_get_events hook is no longer supported.
  - The FTS and Histogram extensions are now shipped together with Zeitgeist.
+ - Introduced new DB schema (version 5).
 
 ---------------------------------------------------------------------
 ---------------------------------------------------------------------

=== modified file 'src/sql-schema.vala'
--- src/sql-schema.vala	2012-02-06 16:11:53 +0000
+++ src/sql-schema.vala	2012-02-10 17:02:09 +0000
@@ -35,7 +35,7 @@
     {
 
         public const string CORE_SCHEMA = "core";
-        public const int CORE_SCHEMA_VERSION = 5;
+        public const int CORE_SCHEMA_VERSION = 6;
 
         public static void ensure_schema (Sqlite.Database database)
             throws EngineError
@@ -48,19 +48,55 @@
                 // most likely a new DB
                 create_schema (database);
             }
-            else if (schema_version == 4)
+            else if (schema_version == 4 || schema_version == 5)
             {
-                // DB from latest Python Zeitgeist, which we can "upgrade"
-                try
-                {
-                  Utils.backup_database ();
-                }
-                catch (Error backup_error)
-                {
-                    var msg = "Database backup failed: " + backup_error.message;
-                    throw new EngineError.BACKUP_FAILED (msg);
-                }
+                backup_database ();
+
+                string[] tables = { "interpretation", "manifestation",
+                    "mimetype", "actor" };
+
+                // Rename old tables that need to be replaced
+                foreach (unowned string table in tables)
+                {
+                    exec_query (database,
+                        "ALTER TABLE %s RENAME TO %s_old".printf (table, table));
+                }
+
+                // Create any missing tables and indices
                 create_schema (database);
+
+                // Migrate data to the new tables and delete the old ones
+                foreach (unowned string table in tables)
+                {
+                    exec_query (database,
+                        "INSERT INTO %s SELECT id, value FROM %s_old".printf (
+                        table, table));
+
+                    exec_query (database, "DROP TABLE %s_old".printf (table));
+                }
+
+                // Ontology update
+                exec_query (database,
+                    "INSERT OR IGNORE INTO manifestation (value) VALUES ('%s')"
+                    .printf (NFO.WEB_DATA_OBJECT));
+                exec_query (database, """
+                    UPDATE event
+                    SET subj_manifestation=(
+                        SELECT id FROM manifestation WHERE value='""" +
+                            NFO.WEB_DATA_OBJECT + """')
+                    WHERE
+                        subj_manifestation=(
+                            SELECT id FROM manifestation WHERE value='""" +
+                                NFO.WEB_DATA_OBJECT + """')
+                        AND subj_id IN (
+                            SELECT id FROM uri
+                            WHERE
+                                value LIKE "http://%";
+                                OR value LIKE "https://%";
+                        )
+                    """);
+
+                message ("Upgraded database to schema version 6.");
             }
             else if (schema_version < CORE_SCHEMA_VERSION)
             {
@@ -70,6 +106,19 @@
             }
         }
 
+        private static void backup_database () throws EngineError
+        {
+            try
+            {
+              Utils.backup_database ();
+            }
+            catch (Error backup_error)
+            {
+                var msg = "Database backup failed: " + backup_error.message;
+                throw new EngineError.BACKUP_FAILED (msg);
+            }
+        }
+
         public static int get_schema_version (Sqlite.Database database)
         {
           var sql = "SELECT version FROM schema_version WHERE schema='core'";
@@ -116,7 +165,7 @@
             // Interpretation
             exec_query (database, """
                 CREATE TABLE IF NOT EXISTS interpretation (
-                    id INTEGER PRIMARY KEY,
+                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                     value VARCHAR UNIQUE
                 )
                 """);
@@ -128,7 +177,7 @@
             // Manifestation
             exec_query (database, """
                 CREATE TABLE IF NOT EXISTS manifestation (
-                    id INTEGER PRIMARY KEY,
+                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                     value VARCHAR UNIQUE
                 )
                 """);
@@ -140,7 +189,7 @@
             // Mime-Type
             exec_query (database, """
                 CREATE TABLE IF NOT EXISTS mimetype (
-                    id INTEGER PRIMARY KEY,
+                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                     value VARCHAR UNIQUE
                 )
                 """);
@@ -152,7 +201,7 @@
             // Actor
             exec_query (database, """
                 CREATE TABLE IF NOT EXISTS actor (
-                    id INTEGER PRIMARY KEY,
+                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                     value VARCHAR UNIQUE
                 )
                 """);

=== modified file 'src/utils.vala'
--- src/utils.vala	2012-02-06 16:19:27 +0000
+++ src/utils.vala	2012-02-10 17:02:09 +0000
@@ -124,6 +124,8 @@
             original = File.new_for_path (get_database_file_path ());
             destination = File.new_for_path (get_database_file_backup_path ());
 
+            message ("Backing up database to \"%s\" for schema upgrade...",
+                get_database_file_backup_path ());
             original.copy (destination, FileCopyFlags.OVERWRITE, null, null);
         }