From 744ec84d40c4d36bde6af7064df35f0e7e62e22f Mon Sep 17 00:00:00 2001
From: Thierry Parmentelat <thierry.parmentelat@sophia.inria.fr>
Date: Tue, 30 Jan 2007 11:29:36 +0000
Subject: [PATCH] adds minimal support for db migrations

also supports new usage
Usage: /etc/plc.d/db [start|migrate|dump|clean-dump]
for convenience
---
 plc.d/db | 94 +++++++++++++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 90 insertions(+), 4 deletions(-)

diff --git a/plc.d/db b/plc.d/db
index b249ca9..8492e85 100755
--- a/plc.d/db
+++ b/plc.d/db
@@ -7,7 +7,7 @@
 # Mark Huang <mlhuang@cs.princeton.edu>
 # Copyright (C) 2006 The Trustees of Princeton University
 #
-# $Id$
+# $Id: db,v 1.4 2007/01/24 21:05:30 mlhuang Exp $
 #
 
 # Source function library and configuration
@@ -20,11 +20,60 @@ set -x
 # Export so that we do not have to specify -p to psql invocations
 export PGPORT=$PLC_DB_PORT
 
+### updates the subversion column in plc_db_version by applying
+### all migrations scripts in /usr/share/plc_api/migrations/nnn-up-*.sql
+### between the current_subversion+1 and the last one
+### migration indices must be contiguous
+function migrate_db () {
+
+    ### check for (or create) the subversion column in plc_db_version
+    psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME > /dev/null
+    [ "$?" = 0 ] || psql -U $PLC_DB_USER -c 'ALTER TABLE plc_db_version ADD subversion integer DEFAULT 0' $PLC_DB_NAME
+
+    # get current subversion - OUCH, THIS IS UGLY
+    current_subversion=$(psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME | \
+			 grep '^  *[0-9][0-9]*$')
+    current_subversion=$(($current_subversion))
+    upgrade_index=$current_subversion
+			 
+    while true; do
+        upgrade_index=$(printf "%03d" $(($upgrade_index+1)))
+	upgrade_scripts=$(ls -1 /usr/share/plc_api/migrations/${upgrade_index}-up-*.sql 2> /dev/null)
+	if [ -z "$upgrade_scripts" ] ; then
+	    echo "DB current migration is $current_subversion"
+	    psql -U $PLC_DB_USER -c "UPDATE plc_db_version SET subversion=$current_subversion" $PLC_DB_NAME
+	    break
+	else
+	    for script in $upgrade_scripts; do
+	        echo "Applying migration script $script"
+		psql -U $PLC_DB_USER -f $script $PLC_DB_NAME 
+	    done
+	    current_subversion=$(($upgrade_index))
+	fi
+    done
+}
+
+#### dumps the database for future restoration
+# uses /root/dumps since /root is mounted on /data/root
+function dump_db () {
+
+    dump=/root/dumps/$(date +'%Y-%m-%d-%H-%M.sql')
+    mkdir -p $(dirname $dump)
+    pg_dump --user=$PLC_DB_USER $PLC_DB_NAME > $dump
+    
+}
+
+function clean_dumps () {
+    find /root/dumps -name '[0-9]*.sql' -atime +15 | xargs rm
+}
+
+##########
+if [ "$PLC_DB_ENABLED" != "1" ] ; then
+  exit 0
+fi
+
 case "$1" in
     start)
-	if [ "$PLC_DB_ENABLED" != "1" ] ; then
-	    exit 0
-	fi
 
 	MESSAGE=$"Bootstrapping the database"
 	dialog "$MESSAGE"
@@ -46,8 +95,45 @@ EOF
 	db-config
 	check
 
+	# Handle migrations
+	migrate_db
+
+	result "$MESSAGE"
+	;;
+
+    migrate)
+
+	MESSAGE=$"Migrating the database"
+	dialog "$MESSAGE"
+
+	migrate_db
 	result "$MESSAGE"
 	;;
+
+    dump)
+
+      	MESSAGE=$"Dumping the database"
+	dialog "$MESSAGE"
+
+	dump_db
+	result "$MESSAGE"
+	;;
+
+    clean-dump)
+
+      	MESSAGE=$"Cleaning old database dumps"
+	dialog "$MESSAGE"
+
+	clean_dumps
+	result "$MESSAGE"
+	;;
+
+    *)
+        echo "Usage: $0 [start|migrate|dump|clean-dump]"
+	exit 1
+	;;
+
+      
 esac
 
 exit $ERRORS
-- 
2.43.0