- change i2 to i2_

[myplc.git] / plc.d / db

diff --git a/plc.d/db b/plc.d/db
index b249ca9..d018893 100755 (executable)
--- a/plc.d/db
+++ b/plc.d/db
@@ -20,15 +20,58 @@ set -x
 # Export so that we do not have to specify -p to psql invocations
 export PGPORT=$PLC_DB_PORT
 
-case "$1" in
-    start)
-       if [ "$PLC_DB_ENABLED" != "1" ] ; then
-           exit 0
+# Updates the database by applying all migration scripts in
+# /usr/share/plc_api/migrations/N-up-*, where N is greater than the
+# current subversion. At least one of the migration scripts with the
+# same N must update plc_db_version.subversion.
+function migrate_db()
+{
+    subversion=$(psql -U $PLC_DB_USER --quiet --tuples-only --no-align -c \
+                "SELECT subversion FROM plc_db_version LIMIT 1" \
+                $PLC_DB_NAME 2>/dev/null || echo 0)
+    shopt -s nullglob
+    for file in /usr/share/plc_api/migrations/[0-9]*-up-* ; do
+       script=$(basename $file)
+       index=${script%-up*}
+       extension=${script##*.}
+       if [ $index -gt $subversion ] ; then
+           if [ "$extension" = "sql" ] ; then
+               psql -U $PLC_DB_USER -f $file $PLC_DB_NAME
+           elif [ -x $file ] ; then
+               $file
+           fi
+           check
        fi
+    done
+}
+
+# Dumps the database
+function dump_db()
+{
+    dump=/var/lib/pgsql/backups/$(date +"$PLC_DB_NAME.%Y-%m-%d-%H-%M.sql")
+    pg_dump -U $PLC_DB_USER $PLC_DB_NAME > $dump
+    check
+}
+
+# Clean up old backups
+function clean_dumps()
+{
+    find /var/lib/pgsql/backups -name "$PLC_DB_NAME.*.sql" -atime +15 | xargs rm -f
+    check
+}
 
+if [ "$PLC_DB_ENABLED" != "1" ] ; then
+    exit 0
+fi
+
+case "$1" in
+    start)
        MESSAGE=$"Bootstrapping the database"
        dialog "$MESSAGE"
 
+       # Apply schema updates
+       migrate_db
+
        # Update the maintenance account username. This can't be
        # done through the api-config script since it uses the
        # maintenance account to access the API. The maintenance
@@ -48,6 +91,35 @@ EOF
 
        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