------------------------------------------------------------
revno: 271
fixes bug(s): https://launchpad.net/bugs/844877
committer: Siegfried-Angel Gevatter Pujals <siegfr...@gevatter.com>
branch nick: bluebird
timestamp: Mon 2011-09-19 16:12:03 +0200
message:
  Avoid using event_view's subqueries in WHERE clauses.
modified:
  src/engine.vala
  src/sql-schema.vala
  src/where-clause.vala


--
lp:~zeitgeist/zeitgeist/bluebird
https://code.launchpad.net/~zeitgeist/zeitgeist/bluebird

Your team Zeitgeist Framework Team is subscribed to branch 
lp:~zeitgeist/zeitgeist/bluebird.
To unsubscribe from this branch go to 
https://code.launchpad.net/~zeitgeist/zeitgeist/bluebird/+edit-subscription
=== modified file 'src/engine.vala'
--- src/engine.vala	2011-09-18 14:13:07 +0000
+++ src/engine.vala	2011-09-19 14:12:03 +0000
@@ -865,7 +865,7 @@
                 if (like)
                     where.add_wildcard_condition ("origin", val, negated);
                 else
-                    where.add_text_condition ("event_origin_uri", val, negated);
+                    where.add_text_condition_subquery ("origin", val, negated);
             }
 
             // Subject templates within the same event template are AND'd
@@ -923,7 +923,7 @@
                     if (like)
                         where.add_wildcard_condition ("subj_id", val, negated);
                     else
-                        where.add_text_condition ("subj_uri", val, negated);
+                        where.add_text_condition_subquery ("subj_id", val, negated);
                 }
 
                 // Origin
@@ -937,8 +937,8 @@
                         where.add_wildcard_condition (
                             "subj_origin", val, negated);
                     else
-                        where.add_text_condition (
-                            "subj_origin_uri", val, negated);
+                        where.add_text_condition_subquery (
+                            "subj_origin", val, negated);
                 }
 
                 // Text
@@ -947,7 +947,7 @@
                     // Negation and prefix search isn't supported for
                     // subject texts, but "!" and "*" are valid as
                     // plain text characters.
-                    where.add_text_condition ("subj_text",
+                    where.add_text_condition_subquery ("subj_text_id",
                         subject_template.text, false);
                 }
 
@@ -962,8 +962,8 @@
                         where.add_wildcard_condition (
                             "subj_id_current", val, negated);
                     else
-                        where.add_text_condition (
-                            "subj_current_uri", val, negated);
+                        where.add_text_condition_subquery (
+                            "subj_id_current", val, negated);
                 }
 
                 // Subject storage
@@ -972,7 +972,7 @@
                     string val = subject_template.storage;
                     assert_no_negation ("subject storage", val);
                     assert_no_wildcard ("subject storage", val);
-                    where.add_text_condition ("subj_storage", val);
+                    where.add_text_condition_subquery ("subj_storage_id", val);
                 }
             }
 

=== modified file 'src/sql-schema.vala'
--- src/sql-schema.vala	2011-09-18 13:29:05 +0000
+++ src/sql-schema.vala	2011-09-19 14:12:03 +0000
@@ -286,7 +286,7 @@
                 CREATE TABLE IF NOT EXISTS extensions_conf (
                     extension VARCHAR,
                     key VARCHAR,
-                    value BLOB, 
+                    value BLOB,
                     CONSTRAINT unique_extension UNIQUE (extension, key)
                 )
                 """);
@@ -295,6 +295,9 @@
                     ON extensions_conf (extension, key)
                 """);
 
+            // Performance note: the subqueries here are provided for lookup
+            // only. For querying, use explicit "WHERE x IN (SELECT id ...)"
+            // subqueries.
             exec_query (database, "DROP VIEW IF EXISTS event_view");
             exec_query (database, """
                 CREATE VIEW IF NOT EXISTS event_view AS
@@ -333,7 +336,9 @@
                         (SELECT value FROM uri
                             WHERE uri.id=event.subj_id_current)
                             AS subj_current_uri,
-                        event.subj_id_current
+                        event.subj_id_current,
+                        event.subj_text AS subj_text_id,
+                        event.subj_storage AS subj_storage_id
                     FROM event
                 """);
         }
@@ -354,6 +359,8 @@
                 string error_message = "Can't create database: %d, %s".printf(
                     rc, database.errmsg ());
                 critical ("%s\n", error_message);
+                // FIXME: Propagate exceptions so the engine will stop if
+                // this happens
             }
         }
 

=== modified file 'src/where-clause.vala'
--- src/where-clause.vala	2011-09-15 22:15:58 +0000
+++ src/where-clause.vala	2011-09-19 14:12:03 +0000
@@ -79,46 +79,71 @@
                 arguments.add (args[i]);
         }
 
-        public void add_match_condition (string column, int val,
-            bool negation=false)
-            throws EngineError.INVALID_ARGUMENT
-        {
-            string sql = "%s %s= %d".printf (column, (negation) ? "!" : "", val);
-            add (sql);
-        }
-
-        public void add_text_condition (string column, string val,
-            bool negation=false)
-            throws EngineError.INVALID_ARGUMENT
-        {
-            string sql = "%s %s= ?".printf (column, (negation) ? "!" : "");
-            add (sql, val);
-        }
-
-        public void add_wildcard_condition (string column, string needle,
-            bool negation=false)
-        {
-            string search_column;
+        private static string get_search_table_for_column (string column)
+        {
+            string search_table;
             switch (column)
             {
+                // For use in add_text_condition_subquery and
+                // add_wildcard_condition:
                 case "origin":
                 case "subj_origin":
                 case "subj_id":
                 case "subj_id_current":
-                    search_column = "uri";
+                    search_table = "uri";
                     break;
                 case "subj_mimetype":
-                    search_column = "mimetype";
-                    break;
+                    search_table = "mimetype";
+                    break;
+
+                // For use only in add_text_condition_subquery:
+                case "subj_text_id":
+                    search_table = "text";
+                    break;
+                case "subj_storage_id":
+                    search_table = "storage";
+                    break;
+
+                // --
                 default:
-                    search_column = column;
+                    search_table = column;
                     break;
             }
+            return search_table;
+        }
+
+        public void add_match_condition (string column, int val,
+            bool negation=false)
+        {
+            string sql = "%s %s= %d".printf (column, (negation) ? "!" : "", val);
+            add (sql);
+        }
+
+        public void add_text_condition_subquery (string column, string val,
+            bool negation=false)
+        {
+            string search_table = get_search_table_for_column (column);
+            string sql = "%s IN (SELECT id FROM %s WHERE value %s= ?)".printf (
+                column, search_table, (negation) ? "!" : "");
+            add (sql, val);
+        }
+
+        public void add_text_condition (string column, string val,
+            bool negation=false)
+        {
+            string sql = "%s %s= ?".printf (column, (negation) ? "!" : "");
+            add (sql, val);
+        }
+
+        public void add_wildcard_condition (string column, string needle,
+            bool negation=false)
+        {
+            string search_table = get_search_table_for_column (column);
 
             var values = new GenericArray<string> ();
             values.add(needle);
             string optimized_glob = optimize_glob (
-                "id", search_column, ref values);
+                "id", search_table, ref values);
 
             string sql;
             if (!negation)

_______________________________________________
Mailing list: https://launchpad.net/~zeitgeist
Post to     : zeitgeist@lists.launchpad.net
Unsubscribe : https://launchpad.net/~zeitgeist
More help   : https://help.launchpad.net/ListHelp

Reply via email to