zeitgeist team mailing list archive

[Seif Lotfy <seif@xxxxxxxxx>]

Seif Lotfy has proposed merging lp:~zeitgeist/zeitgeist/sqlite-insert-improvements into lp:zeitgeist.

Requested reviews:
  Zeitgeist Framework Team (zeitgeist)


Basically all i do is set the journal_mode for SQLite to OFF since we don't use rollback...
more to this topic can be found here: http://www.sqlite.org/pragma.html
-- 
https://code.launchpad.net/~zeitgeist/zeitgeist/sqlite-insert-improvements/+merge/35701
Your team Zeitgeist Framework Team is requested to review the proposed merge of lp:~zeitgeist/zeitgeist/sqlite-insert-improvements into lp:zeitgeist.

=== modified file '_zeitgeist/engine/main.py'
--- _zeitgeist/engine/main.py	2010-09-16 07:14:37 +0000
+++ _zeitgeist/engine/main.py	2010-09-16 17:37:44 +0000
@@ -97,7 +97,18 @@
 	value, wildcard = parse_wildcard(kind, field, value)
 	return value, negation, wildcard
 
-
+def explain_query(statement, arguments):
+	
+	cursor = get_default_cursor()
+	print "**********************************************"
+	print "QUERY:"
+	print statement,"(",arguments,")"
+	print "PLAN:"
+	for r in cursor.execute("EXPLAIN QUERY PLAN "+statement, arguments).fetchall():
+		print r
+	print "**********************************************"
+	
+			
 class ZeitgeistEngine:
 	
 	def __init__ (self):
@@ -165,13 +176,17 @@
 		"""
 		Look up a list of events.
 		"""
-		
 		t = time.time()
 		
 		if not ids and not rows:
 			return []
 		
 		if ids:
+			query = """
+				SELECT * FROM event_view
+				WHERE id IN (%s)
+				""" % ",".join("%d" % id for id in ids)
+			explain_query(query, ())
 			rows = self._cursor.execute("""
 				SELECT * FROM event_view
 				WHERE id IN (%s)
@@ -212,6 +227,10 @@
 				yield Event((event_data, [], None)), Subject(subject)
 	
 	def _build_sql_from_event_templates(self, templates):
+		
+		event_groups = {}
+		#for template in templates:
+		#	print "*********", template	
 	
 		where_or = WhereClause(WhereClause.OR)
 		
@@ -371,6 +390,7 @@
 		if max_events > 0:
 			sql += " LIMIT %d" % max_events
 		
+		explain_query(sql, where.arguments)
 		result = self._cursor.execute(sql, where.arguments).fetchall()
 		
 		if return_mode == 0:
@@ -580,6 +600,15 @@
 			# The event was already registered.
 			# Rollback _last_event_id and return the ID of the original event
 			self._last_event_id -= 1
+			explain_query("""
+				SELECT id FROM event
+				WHERE timestamp=? AND interpretation=? AND manifestation=?
+					AND actor=?
+				""",
+				(event.timestamp,
+					self._interpretation[event.interpretation],
+					self._manifestation[event.manifestation],
+					self._actor[event.actor]))
 			self._cursor.execute("""
 				SELECT id FROM event
 				WHERE timestamp=? AND interpretation=? AND manifestation=?

=== modified file '_zeitgeist/engine/sql.py'
--- _zeitgeist/engine/sql.py	2010-05-28 07:38:51 +0000
+++ _zeitgeist/engine/sql.py	2010-09-16 17:37:44 +0000
@@ -133,7 +133,8 @@
 	conn = sqlite3.connect(file_path)
 	conn.row_factory = sqlite3.Row
 	cursor = conn.cursor(UnicodeCursor)
-	
+	cursor.execute("PRAGMA journal_mode=OFF;")
+
 	# Always assume that temporary memory backed DBs have good schemas
 	if constants.DATABASE_FILE != ":memory:":
 		if _check_core_schema_upgrade (cursor):