Merge lp:~mvo/pkgme-devportal/schemas-via-storm into lp:~mvo/pkgme-devportal/more-architectures

[Michael Vogt]

This branch uses storm to manage the db schema and adds a migration patch from the previous schema without the architecture column to the new schema that adds it.

Please note that this needs running:
"""
CREATE TABLE patch (version INTEGER NOT NULL PRIMARY KEY)
"""
on the database first to ensure that storm does not assumes the DB is totally empty.

This will allow us to do schema updates using devportalbinary/migrations/patch_N.py based files that can alter the DB via a apply() function to do the required schema changes. It internally keeps track of what is applied and what is missing etc.

[lp:~mvo/pkgme-devportal/schemas-via-storm]

70. By Michael Vogt on 2012-07-25

devportalbinary/database.py: add comment, remove adding the patches table if its missing and leave that to the webops via the MP

[Michael Vogt (mvo)]

This branch is on hold for now as discussed on irc:

<jml> mvo: in any case, we won't be using it for this branch though. Maybe best to shelve it until we have another schema change?

But it should be re-considered for the next schema update.

Unmerged revisions

70. By Michael Vogt on 2012-07-25

devportalbinary/database.py: add comment, remove adding the patches table if its missing and leave that to the webops via the MP

69. By Michael Vogt on 2012-07-25

use storm.schema to manage the db and add migrate script

=== modified file 'devportalbinary/database.py'
--- devportalbinary/database.py	2012-07-24 14:45:01 +0000
+++ devportalbinary/database.py	2012-07-25 14:10:24 +0000
@@ -18,7 +18,10 @@
     )
 from launchpadlib.launchpad import Launchpad
 from pkgme.run_script import run_subprocess
-from storm.locals import create_database, Store
+from storm.locals import create_database, Store, StormError
+from storm.schema import Schema
+
+import devportalbinary.migrations
 
 from .configuration import (
     CONF_FILE_ENV_VAR,
@@ -318,32 +321,20 @@
         if store is None:
             options = load_configuration().options
             store, store_type = cls.get_store_from_config(options)
-        if store_type == 'sqlite':
-            store.execute(
-                "CREATE TABLE IF NOT EXISTS libdep ("
-                "source_package_name TEXT, "
-                "library TEXT, "
-                "dependency TEXT, "
-                "architecture TEXT, "
-                "CONSTRAINT libdep_uniq UNIQUE (source_package_name, "
-                "library, dependency))")
-        elif store_type == 'postgres':
-            # Postgres only supports IF NOT EXISTS starting from 9.1
-            exists = store.execute(
-                "SELECT COUNT(relname) FROM pg_class WHERE "
-                "relname = 'libdep'").get_one()[0]
-            if not exists:
-                store.execute(
-                    "CREATE TABLE libdep ("
+        # use storm to manage the db schema, this allows us to do migrations
+        # using devportalbinary.migrations.patch_N.py files
+        creates = [ "CREATE TABLE libdep ("
                     "source_package_name TEXT, "
                     "library TEXT, "
                     "dependency TEXT, "
                     "architecture TEXT, "
                     "CONSTRAINT libdep_uniq UNIQUE (source_package_name, "
-                    "library, dependency))")
-        else:
-            raise AssertionError("Unsupported database type: %s" % store_type)
-        store.commit()
+                    "library, dependency))"
+                   ]
+        drops = [ "DROP TABLE libdep" ]
+        deletes = [ "DELETE FROM libdep" ]
+        schema = Schema(creates, drops, deletes, devportalbinary.migrations)
+        schema.upgrade(store)
         return cls(store)
 
     def get_dependencies(self, library_name, arch='i386'):

=== added directory 'devportalbinary/migrations'
=== added file 'devportalbinary/migrations/patch_0.py'
--- devportalbinary/migrations/patch_0.py	1970-01-01 00:00:00 +0000
+++ devportalbinary/migrations/patch_0.py	2012-07-25 14:10:24 +0000
@@ -0,0 +1,4 @@
+def apply(store):
+    store.execute('ALTER TABLE libdep ADD architecture TEXT;')
+    store.execute('UPDATE libdep SET architecture = "i386";')
+