From 5310688934eb634ac0a6c3c2f5a9e616dcd1899e Mon Sep 17 00:00:00 2001
From: Thierry Parmentelat <thierry.parmentelat@sophia.inria.fr>
Date: Thu, 5 Jan 2012 12:37:09 +0100
Subject: [PATCH] first draft of an added support for migrations in the db
 schema

---
 Makefile            |  3 ++-
 init.d/sfa          | 46 ++++++++++++++++++++++++++++++++++++++++++++-
 setup.py            |  1 +
 sfa/storage/sfa.sql |  7 +------
 4 files changed, 49 insertions(+), 8 deletions(-)

diff --git a/Makefile b/Makefile
index a21ce8ff..9df5ce98 100644
--- a/Makefile
+++ b/Makefile
@@ -38,6 +38,7 @@ python-install:
 	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/sfa.sql
+	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
@@ -152,7 +153,7 @@ sync: synccheck
 	+$(RSYNC)  $(BINS) $(SSHURL)/usr/bin/
 	+$(RSYNC) ./init.d/sfa  $(SSHURL)/etc/init.d/
 	+$(RSYNC) ./config/default_config.xml $(SSHURL)/etc/sfa/
-	+$(RSYNC) ./sfa/storage/sfa.sql $(SSHURL)/usr/share/sfa/
+	+$(RSYNC) ./sfa/storage/{sfa.sql,migrations} $(SSHURL)/usr/share/sfa/
 	$(SSHCOMMAND) exec service sfa restart
 
 # 99% of the time this is enough
diff --git a/init.d/sfa b/init.d/sfa
index 3e733eed..c6b76f2f 100755
--- a/init.d/sfa
+++ b/init.d/sfa
@@ -59,6 +59,50 @@ 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 all migration scripts in
+# /usr/share/sfa/migrations/N-up-*, where N is greater than the
+# current subversion. At least one of the migration scripts with the
+# same N must update sfa_db_version.subversion.
+function migrate_db()
+{
+    subversion=$(psql -U $SFA_DB_USER --quiet --tuples-only --no-align -c \
+	         "SELECT subversion FROM sfa_db_version LIMIT 1" \
+	         $SFA_DB_NAME 2>/dev/null || echo 0)
+    shopt -s nullglob
+    for file in /usr/share/sfa/migrations/[0-9]*-up-* ; do
+	script=$(basename $file)
+	index=${script%-up*}
+	extension=${script##*.}
+	if [ $index -gt $subversion ] ; then
+	    if [ "$extension" = "sql" ] ; then
+	        dialog " - $script (dbdumped)"
+	        dump_sfa_db "before-$script"
+		psql -U $SFA_DB_USER -f $file $SFA_DB_NAME
+	    elif [ -x $file ] ; then
+	        dialog " - $script (dbdumped)"
+	        dump_sfa_db "before-$script"
+		$file
+	    else
+	        dialog "\nWarning: migration $file not executable"
+	    fi
+	    check
+	fi
+    done
+}
+
+
 
 # Regenerate configuration files - almost verbatim from plc.init
 function reload () {
@@ -126,7 +170,7 @@ function db_start () {
 	( egrep -v '^(PGDATA=|PGLOG=|PGPORT=)' $postgresql_sysconfig 
 	    echo "PGDATA=$PGDATA"
 	    echo "PGLOG=/var/log/pgsql"
-	    echo "PGPORT=$PLC_DB_PORT"
+	    echo "PGPORT=$SFA_DB_PORT"
 	) >> $tmp ; mv -f $tmp $postgresql_sysconfig
 
 	######## /var/lib/pgsql/data 
diff --git a/setup.py b/setup.py
index f48cbaf4..fbab6aff 100755
--- a/setup.py
+++ b/setup.py
@@ -64,6 +64,7 @@ data_files = [ ('/etc/sfa/', [ 'config/aggregates.xml',
                ('/etc/sfatables/targets/', glob('sfatables/targets/*.xml')),
                ('/etc/init.d/', [ "init.d/%s"%x for x in initscripts ]),
                ('/usr/share/sfa/', [ 'sfa/storage/sfa.sql' ] ),
+               ('/usr/share/sfa/migrations', [ 'sfa/storage/migrations/*' ] ),
                ('/usr/share/sfa/examples/', glob('sfa/examples/*' ) + [ 'cron.d/sfa.cron' ] ),
               ]
 
diff --git a/sfa/storage/sfa.sql b/sfa/storage/sfa.sql
index 9a2792c8..cc26c1c8 100644
--- a/sfa/storage/sfa.sql
+++ b/sfa/storage/sfa.sql
@@ -14,12 +14,7 @@ CREATE TABLE sfa_db_version (
     subversion integer NOT NULL DEFAULT 0
 ) WITH OIDS;
 
--- the migration scripts do not use the major 'version' number
--- so 5.0 sets subversion at 100
--- in case your database misses the site and persons tags feature, 
--- you might wish to first upgrade to 4.3-rc16 before moving to some 5.0
--- or run the up script here
--- http://svn.planet-lab.org/svn/PLCAPI/branches/4.3/migrations/
+-- for upgrades/migrations
 
 INSERT INTO sfa_db_version (version, subversion) VALUES (1, 1);
 
-- 
2.43.0