Add database migrations

* README.rst: explain how to upgrade db
 * mandaye/migration: sqlalchemy-migration folder
 * mandaye/migration/versions: migration files
 * mandaye_admin.py: add db versioning during db creation
 * mandaye_migrate.py: new script to manage migration
 * setup.py: add sqlalchemy-migration support

README.rst

@@ -92,7 +92,7 @@ First create your database::
 
   $ mandaye_admin.py --createdb
 
-Launch mandaye server:::
+Launch mandaye server::
 
   $ mandaye_server.py
 
@@ -106,6 +106,14 @@ or::
 
  $ mandaye_server.py -c PATH_TO_THE_FILE/gunicorn.conf.py -b 0.0.0.0:4242
 
+Upgrade Mandaye
+---------------
+
+You need to upgrade the update the repository and upgrade database::
+
+ ~/mandaye $ git pull
+ ~/mandaye $ ./mandaye_migrate.py upgrade
+
 Configuration
 =============
 TODO

mandaye/migration/README

@@ -0,0 +1,4 @@
+This is a database migration repository.
+
+More information at
+http://code.google.com/p/sqlalchemy-migrate/

mandaye/migration/__init__.py

@@ -0,0 +1 @@
+# template repository default module

mandaye/migration/manage.py

@@ -0,0 +1,5 @@
+#!/usr/bin/env python
+from migrate.versioning.shell import main
+
+if __name__ == '__main__':
+    main(debug='False')

mandaye/migration/migrate.cfg

@@ -0,0 +1,25 @@
+[db_settings]
+# Used to identify which repository this database is versioned under.
+# You can use the name of your project.
+repository_id=Mandaye migrations
+
+# The name of the database table used to track the schema version.
+# This name shouldn't already be used by your project.
+# If this is changed once a database is under version control, you'll need to 
+# change the table name in each database too. 
+version_table=migrate_version
+
+# When committing a change script, Migrate will attempt to generate the 
+# sql for all supported databases; normally, if one of them fails - probably
+# because you don't have that database installed - it is ignored and the 
+# commit continues, perhaps ending successfully. 
+# Databases in this list MUST compile successfully during a commit, or the 
+# entire commit will fail. List the databases your application will actually 
+# be using to ensure your updates to that database work properly.
+# This must be a list; example: ['postgres','sqlite']
+required_dbs=[]
+
+# When creating new change scripts, Migrate will stamp the new script with
+# a version number. By default this is latest_version + 1. You can set this
+# to 'true' to tell Migrate to use the UTC timestamp instead.
+use_timestamp_numbering=False

mandaye/migration/versions/001_initial_schema.py

@@ -0,0 +1,18 @@
+
+from datetime import datetime
+
+from sqlalchemy import *
+from migrate import *
+
+
+def upgrade(migrate_engine):
+    meta = MetaData(bind=migrate_engine)
+    ext_users = Table('ext_users', meta, autoload=True)
+    creation_date = Column("creation_date", DateTime, default=datetime.now())
+    creation_date.create(ext_users)
+
+def downgrade(migrate_engine):
+    meta = MetaData(bind=migrate_engine)
+    ext_users = Table('ext_users', meta, autoload=True)
+    ext_users.c.creation_date.drop()
+

mandaye/migration/versions/__init__.py

@@ -0,0 +1 @@
+# template repository default versions module

mandaye_admin.py

@@ -54,10 +54,16 @@ def main():
     if options.createdb:
         logger.info("Creating database...")
         if config.db_url:
-            from mandaye.models import Base
+            import migrate.versioning.api
+            import mandaye.migration
             from sqlalchemy import create_engine
+            from mandaye.models import Base
             engine = create_engine(config.db_url)
             Base.metadata.create_all(engine)
+            migrate.versioning.api.version_control(url=config.db_url,
+                    repository=mandaye.migration.__path__[0])
+            migrate.versioning.api.upgrade(url=config.db_url,
+                    repository=mandaye.migration.__path__[0])
             logger.info("Database created")
     if options.cryptpwd:
         for user in sql_session().query(ExtUser).all():

mandaye_migrate.py

@@ -0,0 +1,10 @@
+#!/usr/bin/env python
+
+import os
+
+import mandaye.migration
+from mandaye import config
+
+if config.db_url:
+    from migrate.versioning.shell import main
+    main(url=config.db_url, debug='False', repository=mandaye.migration.__path__[0])

setup.py

@@ -17,7 +17,7 @@ setup(name="mandaye",
       author_email="info@entrouvert.org",
       maintainer="Jerome Schneider",
       maintainer_email="jschneider@entrouvert.com",
-      scripts=['mandaye_server.py', 'mandaye_admin.py'],
+      scripts=['mandaye_server.py', 'mandaye_admin.py', 'mandaye_migrate.py'],
       packages=find_packages(),
       package_data={},
       install_requires=[
@@ -27,6 +27,7 @@ setup(name="mandaye",
           'poster>=0.8',
           'pycrypto>=2.0',
           'sqlalchemy>=0.6',
+          'sqlalchemy-migrate>=0.7.2',
           'lxml>=2.0',
           'static',
       ],