From: Thierry Parmentelat <thierry.parmentelat@sophia.inria.fr>
Date: Wed, 25 Jan 2012 19:42:50 +0000 (+0100)
Subject: add support for migrations with sqlalchemy-migrate
X-Git-Tag: sfa-2.1-1~16
X-Git-Url: http://git.onelab.eu/?p=sfa.git;a=commitdiff_plain;h=b9ae62cc0463f168771bf3d4ebf348bb920acb4f;hp=2d94bbfb87d2458bba9da28a7c7a237cc1537004

add support for migrations with sqlalchemy-migrate
(no actual script for now)
---

diff --git a/Makefile b/Makefile
index 0bacbae2..79b71c5a 100644
--- a/Makefile
+++ b/Makefile
@@ -37,6 +37,7 @@ python-install:
 	python setup.py install --root=$(DESTDIR)	
 	chmod 444 $(DESTDIR)/etc/sfa/default_config.xml
 	rm -rf $(DESTDIR)/usr/lib*/python*/site-packages/*egg-info
+	rm -rf $(DESTDIR)/usr/lib*/python*/site-packages/sfa/storage/migrations
 	(cd $(DESTDIR)/usr/bin ; ln -s sfi.py sfi; ln -s sfascan.py sfascan)
 
 python-clean: version-clean
@@ -159,6 +160,10 @@ synctest: synccheck
 syncrestart: synccheck
 	$(SSHCOMMAND) exec service sfa restart
 
+syncmig:
+	+$(RSYNC) ./sfa/storage/migrations $(SSHURL)/usr/share/sfa/
+
+
 # full-fledged
 sync: synclib syncbin syncinit syncconfig syncrestart
 # 99% of the time this is enough
diff --git a/init.d/sfa b/init.d/sfa
index 885865a5..7cb510c3 100755
--- a/init.d/sfa
+++ b/init.d/sfa
@@ -59,6 +59,48 @@ function postgresql_check () {
     return 1
 }
 
+# use a single date of this script invocation for the dump_*_db functions.
+DATE=$(date +"%Y-%m-%d-%H-%M-%S")
+
+# Dumps the database - optional argument to specify filename suffix
+function dump_sfa_db() {
+    if [ -n "$1" ] ; then suffix="-$1" ; else suffix="" ; fi
+    dumpfile=/var/lib/pgsql/backups/$(date +"${SFA_DB_NAME}.${DATE}${suffix}.sql")
+    pg_dump -U $SFA_DB_USER $SFA_DB_NAME > $dumpfile
+    check
+}
+
+# Updates the database by applying migration scripts
+# using sqlalchemy-migrate for this 
+# migrate repo is /usr/share/sfa/migrations/
+# and version scripts are thus in /usr/share/sfa/migrations/versions/
+function db_migrate() {
+
+    # only if enabled
+    [ "$SFA_DB_ENABLED" == 1 ] || return
+
+    alchemy_url="postgresql+psycopg2://${SFA_DB_USER}:${SFA_DB_PASSWORD}@:${SFA_DB_PORT}/${SFA_DB_NAME}"
+    migrate_repo="/usr/share/sfa/migrations"
+    # check if under version control, and initialize it otherwise
+    if ! sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo >& /dev/null ; then
+	# at this point it would make sense to
+	# check that the 'users' table is in the db
+	# if not, backup/dump, and run a nuke/import
+	# and then only init the db version to 0
+	echo -n $"version init in db ${SFA_DB_NAME}" 
+	sqlalchemy-migrate version_control --url=$alchemy_url $migrate_repo 
+    fi
+    version_before=$(sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo)
+    check
+    sqlalchemy-migrate upgrade --url=$alchemy_url $migrate_repo 2> /dev/null
+    check
+    version_after=$(sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo)
+    if [ "$version_before" != "$version_after" -o "$ERRORS" != 0 ] ; then
+	MESSAGE=$"DB version: $version_before -> $version_after"
+	echo -n "$MESSAGE"
+	[ "$ERRORS" == 0 ] && success "$MESSAGE" || failure "$MESSAGE" ; echo
+    fi
+}
 
 # Regenerate configuration files - almost verbatim from plc.init
 function reload () {
@@ -246,6 +288,7 @@ function start() {
     reload
 
     db_start
+    db_migrate
 
     # install peer certs
     action $"SFA installing peer certs" daemon /usr/bin/sfa-start.py -t -d $OPTIONS 
diff --git a/setup.py b/setup.py
index 6f302570..d98a1ed0 100755
--- a/setup.py
+++ b/setup.py
@@ -64,6 +64,8 @@ data_files = [ ('/etc/sfa/', [ 'config/aggregates.xml',
                ('/etc/sfatables/matches/', glob('sfatables/matches/*.xml')),
                ('/etc/sfatables/targets/', glob('sfatables/targets/*.xml')),
                ('/etc/init.d/', [ "init.d/%s"%x for x in initscripts ]),
+               ('/usr/share/sfa/migrations', [ 'sfa/storage/migrations/*' ] ),
+               ('/usr/share/sfa/migrations/versions', [ 'sfa/storage/migrations/versions/*' ] ),
                ('/usr/share/sfa/examples/', glob('sfa/examples/*' ) + [ 'cron.d/sfa.cron' ] ),
               ]
 
diff --git a/sfa/storage/migrations/README b/sfa/storage/migrations/README
new file mode 100644
index 00000000..6218f8ca
--- /dev/null
+++ b/sfa/storage/migrations/README
@@ -0,0 +1,4 @@
+This is a database migration repository.
+
+More information at
+http://code.google.com/p/sqlalchemy-migrate/
diff --git a/sfa/storage/migrations/__init__.py b/sfa/storage/migrations/__init__.py
new file mode 100644
index 00000000..e69de29b
diff --git a/sfa/storage/migrations/migrate.cfg b/sfa/storage/migrations/migrate.cfg
new file mode 100644
index 00000000..eb55261e
--- /dev/null
+++ b/sfa/storage/migrations/migrate.cfg
@@ -0,0 +1,20 @@
+[db_settings]
+# Used to identify which repository this database is versioned under.
+# You can use the name of your project.
+repository_id=future migrations in the SFA registry database
+
+# 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=sfa_db_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=['postgres']
diff --git a/sfa/storage/migrations/versions/__init__.py b/sfa/storage/migrations/versions/__init__.py
new file mode 100644
index 00000000..e69de29b