zeitgeist team mailing list archive

Thread
Date

[Merge] lp:~kamstrup/zeitgeist/schema_versions into lp:zeitgeist

To: mp+26231@xxxxxxxxxxxxxxxxxx
From: Mikkel Kamstrup Erlandsen <mikkel.kamstrup@xxxxxxxxx>
Date: Thu, 27 May 2010 19:21:32 -0000
Reply-to: mp+26231@xxxxxxxxxxxxxxxxxx
Sender: bounces@xxxxxxxxxxxxx

Mikkel Kamstrup Erlandsen has proposed merging lp:~kamstrup/zeitgeist/schema_versions into lp:zeitgeist.

Requested reviews:
  Zeitgeist Framework Team (zeitgeist)
Related bugs:
  #566898 Log DB file should be versioned
  https://bugs.launchpad.net/bugs/566898
  #580643 conversion script for the database and database versioning
  https://bugs.launchpad.net/bugs/580643


It's not very thoroughly tested yet, but upgrades <= 0.3.3 to 0.3.4 seem to work.

But what is this?
Versioning of the core DB schema (and also adds the possibility to version other schema if we ever have that).

On startup we check if the schema version for the 'core' schema is what we expect and if that is case we assume the schema is good and no further setup is needed.

If the schema version is not what we want we look for a module called _zeitgeist.engine.upgrades.core_$oldversion_$newversion and execute its run() method if it's there.
In our case we are talking upgrading from core schema 0 to 1, so that would be _zeitgeist.engine.upgrades.core_0_1.py.

Note that I did it this way in order to minimize the number of .py files we need to stat and/or parse at startup. If no upgrades are necessary, none of the upgrade .py files are parsed let alone read from disk.
-- 
https://code.launchpad.net/~kamstrup/zeitgeist/schema_versions/+merge/26231
Your team Zeitgeist Framework Team is requested to review the proposed merge of lp:~kamstrup/zeitgeist/schema_versions into lp:zeitgeist.

=== modified file '_zeitgeist/engine/__init__.py'
--- _zeitgeist/engine/__init__.py	2010-05-25 20:14:11 +0000
+++ _zeitgeist/engine/__init__.py	2010-05-27 19:21:32 +0000
@@ -80,6 +80,17 @@
 	SIG_EVENT = "asaasay"
 	
 	# Extensions
+<<<<<<< TREE
 	DEFAULT_EXTENSIONS = _get_extensions()
+=======
+	DEFAULT_EXTENSIONS = [
+		"_zeitgeist.engine.extensions.blacklist.Blacklist",
+		"_zeitgeist.engine.extensions.datasource_registry.DataSourceRegistry",
+		]
+	
+	# Required version of DB schema
+	CORE_SCHEMA="core"
+	CORE_SCHEMA_VERSION = 1
+>>>>>>> MERGE-SOURCE
 
 constants = _Constants()

=== modified file '_zeitgeist/engine/sql.py'
--- _zeitgeist/engine/sql.py	2010-05-14 16:59:04 +0000
+++ _zeitgeist/engine/sql.py	2010-05-27 19:21:32 +0000
@@ -22,6 +22,7 @@
 
 import sqlite3
 import logging
+import time
 
 from _zeitgeist.engine import constants
 
@@ -49,14 +50,88 @@
 		else:
 			return super(UnicodeCursor, self).execute(statement)
 
+def _get_schema_version (cursor, schema_name):
+	"""
+	Returns the schema version for schema_name or returns 0 in case
+	the schema doesn't exist.
+	"""
+	try:
+		schema_version_result = cursor.execute("""
+			SELECT version FROM schema_version WHERE schema='core'
+		""")
+		result = schema_version_result.fetchone()
+		return result[0] if result else 0
+	except sqlite3.OperationalError, e:
+		# The schema isn't there...
+		log.debug ("Schema '%s' not found: %s" % (schema_name, e))
+		return 0
+
+def _set_schema_version (cursor, schema_name, version):
+	"""
+	Sets the version of `schema_name` to `version`
+	"""
+	cursor.execute("""
+		CREATE TABLE IF NOT EXISTS schema_version
+			(schema VARCHAR PRIMARY KEY ON CONFLICT REPLACE, version INT)
+	""")
+	
+	# The 'ON CONFLICT REPLACE' on the PK converts INSERT to UPDATE
+	# when appriopriate
+	cursor.execute("""
+		INSERT INTO schema_version VALUES (?, ?)
+	""", (schema_name, version))
+	cursor.connection.commit()
+
+def _do_schema_upgrade (cursor, schema_name, old_version, new_version):
+	"""
+	Try and upgrade schema `schema_name` from version `old_version` to
+	`new_version`. This is done by checking for an upgrade module named
+	'_zeitgeist.engine.upgrades.$schema_name_$old_version_$new_version'
+	and executing the run(cursor) method of that module
+	"""
+	# Fire of the right upgrade module
+	log.info("Upgrading database '%s' from version %s to %s. This may take a while" %
+	         (schema_name, old_version, new_version))
+	upgrader_name = "%s_%s_%s" % (schema_name, old_version, new_version)
+	module = __import__ ("_zeitgeist.engine.upgrades.%s" % upgrader_name)
+	eval("module.engine.upgrades.%s.run(cursor)" % upgrader_name)
+	
+	# Update the schema version
+	_set_schema_version(cursor, schema_name, new_version)
+	
+	log.info("Upgrade succesful")
+
 def create_db(file_path):
 	"""Create the database and return a default cursor for it"""
-	
+	start = time.time()
 	log.info("Using database: %s" % file_path)
 	conn = sqlite3.connect(file_path)
 	conn.row_factory = sqlite3.Row
 	cursor = conn.cursor(UnicodeCursor)
 	
+	# See if we have the right schema version, and try an upgrade if needed
+	core_schema_version = _get_schema_version(cursor, constants.CORE_SCHEMA)
+	if core_schema_version is not None:
+		if core_schema_version == constants.CORE_SCHEMA_VERSION:
+			_time = (time.time() - start)*1000
+			log.debug("Core schema is good. DB loaded in %sms" % _time)
+			return cursor
+		else:
+			try:
+				_do_schema_upgrade (cursor,
+				                    constants.CORE_SCHEMA,
+				                    core_schema_version,
+				                    constants.CORE_SCHEMA_VERSION)
+				# Don't return here. The upgrade process might depend on the
+				# tables, indexes, and views being set up (to avoid code dup)
+				log.info("Running post upgrade setup")
+			except Exception, e:
+				log.fatal("Failed to upgrade database '%s' from version %s to %s: %s" %
+				          (constants.CORE_SCHEMA, core_schema_version, constants.CORE_SCHEMA_VERSION, e))
+	else:
+		log.info("Setting up initial database")
+		
+	
 	# uri
 	cursor.execute("""
 		CREATE TABLE IF NOT EXISTS uri
@@ -277,6 +352,13 @@
 			FROM event
 		""")
 	
+	# All good. Set the schema version, so we don't have to do all this
+	# sql the next time around
+	_set_schema_version (cursor, constants.CORE_SCHEMA, constants.CORE_SCHEMA_VERSION)
+	_time = (time.time() - start)*1000
+	log.info("DB set up in %sms" % _time)
+	cursor.connection.commit()
+	
 	return cursor
 
 _cursor = None

=== added directory '_zeitgeist/engine/upgrades'
=== added file '_zeitgeist/engine/upgrades/__init__.py'
=== added file '_zeitgeist/engine/upgrades/core_0_1.py'
--- _zeitgeist/engine/upgrades/core_0_1.py	1970-01-01 00:00:00 +0000
+++ _zeitgeist/engine/upgrades/core_0_1.py	2010-05-27 19:21:32 +0000
@@ -0,0 +1,137 @@
+import os
+import sys
+
+INTERPRETATION_RENAMES = \
+[
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo/#ManifestationCode";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#SourceCode";),
+	
+	("http://www.semanticdesktop.org/ontologies/nfo/#Bookmark";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Bookmark";),
+	
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo/#Document";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Document";),
+	 
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo/#Image";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Image";),
+	
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo/#Video";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Video";),
+	
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo/#Audio";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Audio";),
+	
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nmo/#Email";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nmo#Email";),
+	
+	("http://www.semanticdesktop.org/ontologies/2007/03/22/nmo/#IMMessage";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nmo#IMMessage";),
+	
+	# FIXME: FEED_MESSAGE
+	# FIXME: BROADCAST_MESSAGE
+	# FIXME: FOCUS_EVENT
+	# FIXME: WARN_EVENT
+	# FIXME: ERROR_EVENT
+	# FIXME: http://freedesktop.org/standards/xesam/1.0/core#SystemRessource
+	
+	("http://zeitgeist-project.com/schema/1.0/core#CreateEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#CreateEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#ModifyEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#ModifyEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#VisitEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#AccessEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#OpenEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#AccessEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#SaveEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#ModifyEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#CloseEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#LeaveEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#SendEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#SendEvent";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#ReceiveEvent";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#ReceiveEvent";),
+]
+
+MANIFESTATION_RENAMES = \
+[
+	("http://zeitgeist-project.com/schema/1.0/core#UserActivity";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#UserActivity";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#HeuristicActivity";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#HeuristicActivity";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#ScheduledActivity";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#ScheduledActivity";),
+	
+	("http://zeitgeist-project.com/schema/1.0/core#UserNotification";,
+	 "http://www.zeitgeist-project.com/ontologies/2010/01/27/zg#WorldActivity";),
+	
+	("http://www.semanticdesktop.org/ontologies/nfo/#FileDataObject";,
+	 "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#FileDataObject";),
+]
+
+# These are left alone, but are listed here for completeness
+INTERPRETATION_DELETIONS = \
+[
+	"http://www.semanticdesktop.org/ontologies/2007/01/19/nie/#comment";,
+	"http://zeitgeist-project.com/schema/1.0/core#UnknownInterpretation";,
+]
+
+# These are left alone, but are listed here for completeness
+MANIFESTATION_DELETIONS = \
+[
+	"http://zeitgeist-project.com/schema/1.0/core#UnknownManifestation";,
+]
+
+#
+# This module upgrades the 'core' schema from version 0 (or unversioned
+# pre 0.3.3 DBs) to DB core schema version 1
+#
+def run(cursor):
+	for r in INTERPRETATION_RENAMES:
+		cursor.execute("""
+			UPDATE interpretation SET value=? WHERE value=?
+		""", r)
+	
+	for r in MANIFESTATION_RENAMES:
+		cursor.execute("""
+			UPDATE manifestation SET value=? WHERE value=?
+		""", r)
+	
+	# START WEB HISTORY UPGRADE
+	# The case of Manifestation.WEB_HISTORY it's a little more tricky.
+	# We must set the subject interpretation to Interpretation.WEBSITE
+	# and set the subject manifestation to Manifestation.REMOTE_DATA_OBJECT.
+	#
+	# We accomplish this by renaming nfo#WebHistory to nfo#RemoteDataObject
+	# and after that set the interpretation of all events with manifestation
+	# nfo#RemoteDataObjects to nfo#Website.
+	
+	cursor.execute("""
+		UPDATE manifestation SET value=? WHERE value=?
+	""", ("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#WebHistory";,
+	      "http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#RemoteDataObject";))
+	
+	try:
+		cursor.execute("""
+			INSERT INTO interpretation (value) VALUES (?)
+		""", ("http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Website";,))
+	except:
+		# Unique key constraint violation - it's already there...
+		pass
+	
+	website_id = cursor.execute("SELECT id FROM interpretation WHERE value='http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Website'").fetchone()[0]
+	remotes = cursor.execute("SELECT id FROM event WHERE subj_manifestation='http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#RemoteDataObject'").fetchall()
+	for event_id in remotes:
+		cursor.execute("""
+			UPDATE event SET subj_interpretation=%s WHERE id=?
+		""" % website_id, (event_id,))
+	# END WEB HISTORY UPGRADE
+