zeitgeist team mailing list archive
-
zeitgeist team
-
Mailing list archive
-
Message #04113
[Branch ~zeitgeist/zeitgeist/bluebird] Rev 271: Avoid using event_view's subqueries in WHERE clauses.
------------------------------------------------------------
revno: 271
fixes bug(s): https://launchpad.net/bugs/844877
committer: Siegfried-Angel Gevatter Pujals <siegfried@xxxxxxxxxxxx>
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)
