Merge lp:~kamstrup/zeitgeist/schema_versions into lp:zeitgeist/0.1

[Mikkel Kamstrup Erlandsen]

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.

[lp:~kamstrup/zeitgeist/schema_versions]

1470. By Mikkel Kamstrup Erlandsen <kamstrup@hardback> on 2010-05-27

Describe the fixmes is core_0_1.py upgrade

1471. By Mikkel Kamstrup Erlandsen <kamstrup@hardback> on 2010-05-27

Merge with trunk

[Siegfried Gevatter (rainct)]

Go, go, go!

review: Approve (diff review)

[lp:~kamstrup/zeitgeist/schema_versions]

1472. By Mikkel Kamstrup Erlandsen <kamstrup@hardback> on 2010-05-27

Fix hard coded 'core' schema in a SQL statement for the upgrade logic

1473. By Mikkel Kamstrup Erlandsen <kamstrup@hardback> on 2010-05-27

exit if the upgrade fails... running on a undefined schema version is very dangerous

=== modified file '_zeitgeist/engine/__init__.py'
--- _zeitgeist/engine/__init__.py	2010-05-25 20:14:11 +0000
+++ _zeitgeist/engine/__init__.py	2010-05-27 20:20:41 +0000
@@ -81,5 +81,9 @@
 	
 	# Extensions
 	DEFAULT_EXTENSIONS = _get_extensions()
+	
+	# Required version of DB schema
+	CORE_SCHEMA="core"
+	CORE_SCHEMA_VERSION = 1
 
 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 20:20:41 +0000
@@ -22,6 +22,7 @@
 
 import sqlite3
 import logging
+import time
 
 from _zeitgeist.engine import constants
 
@@ -49,14 +50,89 @@
 		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=?
+		""", (schema_name,))
+		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))
+				raise SystemExit(27)
+	else:
+		log.info("Setting up initial database")
+		
+	
 	# uri
 	cursor.execute("""
 		CREATE TABLE IF NOT EXISTS uri
@@ -277,6 +353,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 20:20:41 +0000
@@ -0,0 +1,143 @@
+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"),
+	
+	("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"),
+]
+
+# The following interpretations does not have a good candidate for replacement
+# in the Nepomuk ontology. Now with schema versions in place we can consider
+# adding our own hacky URIs for these:
+# FIXME: FEED_MESSAGE
+# FIXME: BROADCAST_MESSAGE
+# FIXME: http://freedesktop.org/standards/xesam/1.0/core#SystemRessource
+# FIXME: Note - like from Tomboy and what have we
+
+# We should reevaluate the usefulness of the following event interpretations
+# FIXME: FOCUS_EVENT - We don't have a concrete use case except so hand wavy ideas
+# FIXME: WARN_EVENT - wtf?
+# FIXME: ERROR_EVENT - wtf?
+
+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
+