zeitgeist team mailing list archive

[Siegfried Gevatter <rainct@xxxxxxxxxx>]

Siegfried Gevatter has proposed merging lp:~zeitgeist/zeitgeist/dbschema4 into lp:zeitgeist with lp:~zeitgeist/zeitgeist/storagemonitor2 as a prerequisite.

Requested reviews:
  Zeitgeist Framework Team (zeitgeist)

For more details, see:
https://code.launchpad.net/~zeitgeist/zeitgeist/dbschema4/+merge/52265

This branch includes the two remaining schema changes. It should be merged at the same time as the storage monitor so people will get all updates at once.
-- 
https://code.launchpad.net/~zeitgeist/zeitgeist/dbschema4/+merge/52265
Your team Zeitgeist Framework Team is requested to review the proposed merge of lp:~zeitgeist/zeitgeist/dbschema4 into lp:zeitgeist.

=== modified file '_zeitgeist/engine/main.py'
--- _zeitgeist/engine/main.py	2011-02-10 10:55:44 +0000
+++ _zeitgeist/engine/main.py	2011-03-04 22:36:33 +0000
@@ -613,7 +613,8 @@
 			for subject in event.subjects:	
 				self._cursor.execute("""
 					INSERT INTO event VALUES (
-						?, ?, ?, ?, ?, ?,
+						?, ?, ?, ?, ?, ?, ?,
+						(SELECT id FROM uri WHERE value=?),
 						(SELECT id FROM uri WHERE value=?),
 						?, ?,
 						(SELECT id FROM uri WHERE value=?),
@@ -626,8 +627,10 @@
 						self._interpretation[event.interpretation],
 						self._manifestation[event.manifestation],
 						self._actor[event.actor],
+						None, # event origin
 						payload_id,
 						subject.uri,
+						subject.uri,
 						self._interpretation[subject.interpretation],
 						self._manifestation[subject.manifestation],
 						subject.origin,

=== modified file '_zeitgeist/engine/sql.py'
--- _zeitgeist/engine/sql.py	2011-03-04 22:36:33 +0000
+++ _zeitgeist/engine/sql.py	2011-03-04 22:36:33 +0000
@@ -260,10 +260,12 @@
 			id INTEGER,
 			timestamp INTEGER,
 			interpretation INTEGER,
-			manifestation INTEGER,			 
-			actor INTEGER,			 
+			manifestation INTEGER,
+			actor INTEGER,
+			origin INTEGER,
 			payload INTEGER,
 			subj_id INTEGER,
+			subj_id_current INTEGER,
 			subj_interpretation INTEGER,
 			subj_manifestation INTEGER,
 			subj_origin INTEGER,
@@ -276,10 +278,14 @@
 				REFERENCES manifestation(id) ON DELETE CASCADE,
 			CONSTRAINT actor_fk FOREIGN KEY(actor)
 				REFERENCES actor(id) ON DELETE CASCADE,
+			CONSTRAINT actor_fk FOREIGN KEY(origin)
+				REFERENCES uri(id) ON DELETE CASCADE,
 			CONSTRAINT payload_fk FOREIGN KEY(payload)
 				REFERENCES payload(id) ON DELETE CASCADE,
 			CONSTRAINT subj_id_fk FOREIGN KEY(subj_id)
 				REFERENCES uri(id) ON DELETE CASCADE,
+			CONSTRAINT subj_id_current_fk FOREIGN KEY(subj_id_current)
+				REFERENCES uri(id) ON DELETE CASCADE,
 			CONSTRAINT subj_interpretation_fk FOREIGN KEY(subj_interpretation)
 				REFERENCES interpretation(id) ON DELETE CASCADE,
 			CONSTRAINT subj_manifestation_fk FOREIGN KEY(subj_manifestation)
@@ -311,9 +317,15 @@
 		CREATE INDEX IF NOT EXISTS event_actor
 			ON event(actor)""")
 	cursor.execute("""
+		CREATE INDEX IF NOT EXISTS event_origin
+			ON event(origin)""")
+	cursor.execute("""
 		CREATE INDEX IF NOT EXISTS event_subj_id
 			ON event(subj_id)""")
 	cursor.execute("""
+		CREATE INDEX IF NOT EXISTS event_subj_id_current
+			ON event(subj_id_current)""")
+	cursor.execute("""
 		CREATE INDEX IF NOT EXISTS event_subj_interpretation
 			ON event(subj_interpretation)""")
 	cursor.execute("""
@@ -353,24 +365,25 @@
 				""" % {'column': column, 'table': table})
 
 	# ... special cases
-	cursor.execute("""
-		CREATE TRIGGER IF NOT EXISTS fkdc_event_uri_1
-		BEFORE DELETE ON event
-		WHEN ((SELECT COUNT(*) FROM event WHERE subj_id=OLD.subj_id OR subj_origin=OLD.subj_id) < 2)
-		BEGIN
-			DELETE FROM uri WHERE id=OLD.subj_id;
-		END;
-		""" % {'column': column, 'table': table})
-	cursor.execute("""
-		CREATE TRIGGER IF NOT EXISTS fkdc_event_uri_2
-		BEFORE DELETE ON event
-		WHEN ((SELECT COUNT(*) FROM event WHERE subj_id=OLD.subj_origin OR subj_origin=OLD.subj_origin) < 2)
-		BEGIN
-			DELETE FROM uri WHERE id=OLD.subj_origin;
-		END;
-		""" % {'column': column, 'table': table})
+	for num, column in enumerate(('subj_id', 'subj_origin',
+	'subj_id_current', 'origin')):
+		cursor.execute("""
+			CREATE TRIGGER IF NOT EXISTS fkdc_event_uri_%(num)d
+			BEFORE DELETE ON event
+			WHEN ((
+				SELECT COUNT(*)
+				FROM event
+				WHERE
+					origin=OLD.%(column)s
+					OR subj_id=OLD.%(column)s
+					OR subj_id_current=OLD.%(column)s
+					OR subj_origin=OLD.%(column)s
+				) < 2)
+			BEGIN
+				DELETE FROM uri WHERE id=OLD.%(column)s;
+			END;
+			""" % {'num': num+1, 'column': column})
 
-	# TODO: Make the DROP conditional to version upgrades. How?
 	cursor.execute("DROP VIEW IF EXISTS event_view")
 	cursor.execute("""
 		CREATE VIEW IF NOT EXISTS event_view AS

=== modified file '_zeitgeist/engine/upgrades/core_3_4.py'
--- _zeitgeist/engine/upgrades/core_3_4.py	2011-03-04 22:36:33 +0000
+++ _zeitgeist/engine/upgrades/core_3_4.py	2011-03-04 22:36:33 +0000
@@ -22,6 +22,80 @@
 	# That way they will always be marked as available. We don't have a chance
 	# of properly backtracking all items, so we use this as a clutch
 	cursor.execute("UPDATE event SET subj_storage=? WHERE subj_storage IS NULL", (unknown_storage_rowid, ))
+
+	# Add new colums to the events table: subj_current_id and (event) origin
+	# Since SQLite doesn't support "ALTER TABLE ... ADD CONSTRAINT" we have
+	# to create the table anew. See: http://www.sqlite.org/faq.html#q11
+	cursor.execute("ALTER TABLE event RENAME TO event_old");
+	cursor.execute("""
+		CREATE TABLE IF NOT EXISTS event (
+			id INTEGER,
+			timestamp INTEGER,
+			interpretation INTEGER,
+			manifestation INTEGER,
+			actor INTEGER,
+			origin INTEGER,
+			payload INTEGER,
+			subj_id INTEGER,
+			subj_id_current INTEGER,
+			subj_interpretation INTEGER,
+			subj_manifestation INTEGER,
+			subj_origin INTEGER,
+			subj_mimetype INTEGER,
+			subj_text INTEGER,
+			subj_storage INTEGER,
+			CONSTRAINT interpretation_fk FOREIGN KEY(interpretation)
+				REFERENCES interpretation(id) ON DELETE CASCADE,
+			CONSTRAINT manifestation_fk FOREIGN KEY(manifestation)
+				REFERENCES manifestation(id) ON DELETE CASCADE,
+			CONSTRAINT actor_fk FOREIGN KEY(actor)
+				REFERENCES actor(id) ON DELETE CASCADE,
+			CONSTRAINT actor_fk FOREIGN KEY(origin)
+				REFERENCES uri(id) ON DELETE CASCADE,
+			CONSTRAINT payload_fk FOREIGN KEY(payload)
+				REFERENCES payload(id) ON DELETE CASCADE,
+			CONSTRAINT subj_id_fk FOREIGN KEY(subj_id)
+				REFERENCES uri(id) ON DELETE CASCADE,
+			CONSTRAINT subj_id_current_fk FOREIGN KEY(subj_id_current)
+				REFERENCES uri(id) ON DELETE CASCADE,
+			CONSTRAINT subj_interpretation_fk FOREIGN KEY(subj_interpretation)
+				REFERENCES interpretation(id) ON DELETE CASCADE,
+			CONSTRAINT subj_manifestation_fk FOREIGN KEY(subj_manifestation)
+				REFERENCES manifestation(id) ON DELETE CASCADE,
+			CONSTRAINT subj_origin_fk FOREIGN KEY(subj_origin)
+				REFERENCES uri(id) ON DELETE CASCADE,
+			CONSTRAINT subj_mimetype_fk FOREIGN KEY(subj_mimetype)
+				REFERENCES mimetype(id) ON DELETE CASCADE,
+			CONSTRAINT subj_text_fk FOREIGN KEY(subj_text)
+				REFERENCES text(id) ON DELETE CASCADE,
+			CONSTRAINT subj_storage_fk FOREIGN KEY(subj_storage)
+				REFERENCES storage(id) ON DELETE CASCADE,
+			CONSTRAINT unique_event UNIQUE (timestamp, interpretation, manifestation, actor, subj_id)
+		)
+		""")
+	cursor.execute("""
+		CREATE INDEX IF NOT EXISTS event_origin
+			ON event(origin)""")
+	cursor.execute("""
+		CREATE INDEX IF NOT EXISTS event_subj_id_current
+			ON event(subj_id_current)""")
+	# Copy the old data over. Additionally, we initialize subj_id_current to
+	# the same value as in subj_id.
+	cursor.execute("""
+		INSERT INTO event
+		SELECT
+			id, timestamp, interpretation, manifestation, actor, NULL,
+			payload, subj_id, subj_id AS subj_id_current, subj_interpretation,
+			subj_manifestation, subj_origin, subj_mimetype, subj_text,
+			subj_storage
+		FROM event_old
+		""")
+	# Finally, delete the old table
+	cursor.execute("DROP TABLE event_old")
+
+	# Delete triggers that have changed (they'll be created anew
+	# in sql.create_db()
+	cursor.execute("DROP TRIGGER IF EXISTS fkdc_event_uri_1")
+	cursor.execute("DROP TRIGGER IF EXISTS fkdc_event_uri_2")
 	
 	cursor.connection.commit()
-