# HG changeset patch # User Daniele Nicolodi # Date 1323699028 -3600 # Node ID 39b50b763f11e89d97ceae129eba0f13ad664f47 # Parent e1800a0170ae61a4b188cd8b141b50c59c25f9de New collections database representation. diff -r e1800a0170ae -r 39b50b763f11 src/ltpdarepo/__init__.py --- a/src/ltpdarepo/__init__.py Thu Dec 01 17:24:31 2011 +0100 +++ b/src/ltpdarepo/__init__.py Mon Dec 12 15:10:28 2011 +0100 @@ -24,7 +24,7 @@ from .views.users import module as users -SCHEMA = 30 +SCHEMA = 31 class datetimeutc(datetime): diff -r e1800a0170ae -r 39b50b763f11 src/ltpdarepo/install.py --- a/src/ltpdarepo/install.py Thu Dec 01 17:24:31 2011 +0100 +++ b/src/ltpdarepo/install.py Mon Dec 12 15:10:28 2011 +0100 @@ -110,7 +110,7 @@ end repeat; END;""") - curs.execute("""INSERT INTO `options` VALUES ('version', '30')""") + curs.execute("""INSERT INTO `options` VALUES ('version', '31')""") conn.commit() conn.close() diff -r e1800a0170ae -r 39b50b763f11 src/ltpdarepo/sql/database.sql --- a/src/ltpdarepo/sql/database.sql Thu Dec 01 17:24:31 2011 +0100 +++ b/src/ltpdarepo/sql/database.sql Mon Dec 12 15:10:28 2011 +0100 @@ -42,11 +42,19 @@ CREATE TABLE `collections` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `nobjs` int(11) DEFAULT NULL, - `obj_ids` text CHARACTER SET utf8, PRIMARY KEY (`id`) ) ENGINE=InnoDB; +CREATE TABLE `collections2objs` ( + `id` int unsigned NOT NULL, + `obj_id` int unsigned NOT NULL, + PRIMARY KEY (`id`, `obj_id`), + FOREIGN KEY (`id`) REFERENCES `collections` (`id`) ON DELETE CASCADE, + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE, + INDEX (`id`), + INDEX (`obj_id`) +) ENGINE=InnoDB; + CREATE TABLE `transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `obj_id` int(11) DEFAULT NULL, diff -r e1800a0170ae -r 39b50b763f11 src/ltpdarepo/tests/test_schema_upgrade.py --- a/src/ltpdarepo/tests/test_schema_upgrade.py Thu Dec 01 17:24:31 2011 +0100 +++ b/src/ltpdarepo/tests/test_schema_upgrade.py Mon Dec 12 15:10:28 2011 +0100 @@ -1,4 +1,5 @@ import unittest2 as unittest +import logging import os import warnings import MySQLdb as mysql @@ -9,15 +10,14 @@ class TestCase(unittest.TestCase): + @classmethod + def setUpClass(self): + # setup logging + logger = logging.getLogger('ltpdarepo').setLevel(logging.ERROR) + def setUp(self): self.app = admin.Application() - - def tearDown(self): - self.app.wipe() - - def test_schema_upgrade(self): - warnings.simplefilter('ignore', category=mysql.Warning) - + # load dump obtained with the `dump.sh` script conn = self.app.connect(db='mysql') curs = conn.cursor() @@ -30,6 +30,12 @@ curs.execute("FLUSH PRIVILEGES") conn.close() + def tearDown(self): + self.app.wipe() + + def test_schema_upgrade(self): + warnings.simplefilter('ignore', category=mysql.Warning) + # proper configuration to match dump app = admin.Application(USERNAME='ltpda', PASSWORD='ltpda') # connect as root @@ -38,6 +44,7 @@ # upgrade app.upgrade() + # database connection conn = self.app.connect() curs = conn.cursor() @@ -106,3 +113,50 @@ for line in diff: print line, self.assertTrue(upgraded.getvalue() == new.getvalue()) + + + def test_collections_upgrade(self): + warnings.simplefilter('ignore', category=mysql.Warning) + + # proper configuration to match dump + app = admin.Application(USERNAME='ltpda', PASSWORD='ltpda') + # connect as root + app.username = 'root' + app.password = '' + # upgrade to schema version 30 + app.upgrade(_to=30) + + # database connection + conn = self.app.connect(db='db1') + curs = conn.cursor() + + # add objects + self.app.populate('db1', 30) + # add collections + for ids in ((1, 2), (2, 3), (4, 5, 6, 7), (7, 8, 9, 10)): + curs.execute("INSERT INTO collections (nobjs, obj_ids) " + "VALUES (%s, %s)", (len(ids), ",".join("%d" % i for i in ids))) + # add single object collections + for ids in ((11, ), (12, ), (13, ), (14, ), (15, )): + curs.execute("INSERT INTO collections (nobjs, obj_ids) " + "VALUES (%s, %s)", (len(ids), ",".join("%d" % i for i in ids))) + # add broken collections + for ids in ((16, 17, 18), (19, )): + curs.execute("INSERT INTO collections (nobjs, obj_ids) " + "VALUES (%s, %s)", (len(ids) + 1, ",".join("%d" % i for i in ids))) + conn.commit() + + # upgrade to schema version 31 + app.upgrade(_to=31) + + # check collections + curs.execute("SELECT COUNT(*) FROM collections") + self.assertEqual(curs.fetchone()[0], 5) + curs.execute("SELECT COUNT(DISTINCT id) FROM collections2objs") + self.assertEqual(curs.fetchone()[0], 5) + curs.execute("SELECT obj_id FROM collections2objs WHERE id = 1") + self.assertEqual([i for i, in curs.fetchall()], [1, 2]) + curs.execute("SELECT obj_id FROM collections2objs WHERE id = 4") + self.assertEqual([i for i, in curs.fetchall()], [7, 8, 9, 10]) + curs.execute("SELECT obj_id FROM collections2objs WHERE id = 10") + self.assertEqual([i for i, in curs.fetchall()], [16, 17, 18]) diff -r e1800a0170ae -r 39b50b763f11 src/ltpdarepo/upgrade.py --- a/src/ltpdarepo/upgrade.py Thu Dec 01 17:24:31 2011 +0100 +++ b/src/ltpdarepo/upgrade.py Mon Dec 12 15:10:28 2011 +0100 @@ -469,3 +469,55 @@ # user has privileges on the transactions table but not on the database curs.execute("""REVOKE INSERT ON `%s`.transactions FROM %%s@%%s""" % database, (row[0], '%')) + + +@register(30, 31) +def upgrade_30_31(conn, **kwargs): + logger = logging.getLogger(__name__) + curs = conn.cursor() + + # store current database name + curs.execute("SELECT DATABASE()") + current = curs.fetchone()[0] + + # for each database + curs.execute("SELECT db_name FROM available_dbs") + databases = [row[0] for row in curs.fetchall()] + for db in databases: + + logger.info(" upgrading collections rapresentation on %s", db) + curs.execute("""USE `%s`""" % db) + + # create collections mapping table + curs.execute("""CREATE TABLE IF NOT EXISTS collections2objs ( + id INT UNSIGNED NOT NULL, + obj_id INT UNSIGNED NOT NULL, + PRIMARY KEY (id, obj_id), + FOREIGN KEY (id) REFERENCES collections (id) ON DELETE CASCADE, + FOREIGN KEY (obj_id) REFERENCES objs (id) ON DELETE CASCADE, + INDEX (id), + INDEX (obj_id) + ) ENGINE=InnoDB""") + + # upgrade representation + curs.execute("""SELECT id, nobjs, obj_ids FROM collections""") + for id, nobjs, obj_ids in curs.fetchall(): + ids = map(int, obj_ids.split(',')) + if len(ids) != nobjs: + logger.warning(" bad collection description cid=%d", id) + # discard collections composed by a single object + if len(ids) == 1: + logger.info(" deleting single object collection cid=%d", id) + curs.execute("""DELETE FROM collections WHERE id=%s""", id) + continue + for obj_id in ids: + curs.execute("""INSERT INTO collections2objs (id, obj_id) + VALUES (%s, %s)""", (id, obj_id)) + + # drop old columns + curs.execute("""ALTER TABLE collections DROP COLUMN nobjs""") + curs.execute("""ALTER TABLE collections DROP COLUMN obj_ids""") + conn.commit() + + # switch back to default database + curs.execute("USE `%s`" % current)