--- 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):

--- 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()

--- 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,

--- 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])

--- 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)