From c1c1fc3a1cc0a7d6a8ebb81db5fe8ddb6ba7b9fd Mon Sep 17 00:00:00 2001
From: erickson <erickson@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 23 Nov 2009 20:50:40 +0000
Subject: [PATCH] DB update patch/script from Joe Atzberger.

===
Attachment one is a database update script based on work Bill sent to the list.  The previous mechanism was to call psql -f once per file, but that also required the psql password to be entered once per file and didn't check to see if the call failed to try again or at least bail out on subsequent files (that might require the earlier ones to succeed.  Given that typing (or even pasting) 20+ consecutive blind password entires is error-prone, I replaced that with a pipe to single psql call.

Pending acceptance, the foreseeable goal will be to integrate this script with the update.sh script and have a single call to apply both code and data changes from an updated repo.
===



git-svn-id: svn://svn.open-ils.org/ILS/trunk@15016 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 build/tools/update_db.sh | 88 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 88 insertions(+)
 create mode 100644 build/tools/update_db.sh

diff --git a/build/tools/update_db.sh b/build/tools/update_db.sh
new file mode 100644
index 0000000000..d0c1f37441
--- /dev/null
+++ b/build/tools/update_db.sh
@@ -0,0 +1,88 @@
+#!/bin/bash
+#
+# Based on a script by Bill Erickson.
+#
+# TODO:
+#   ADD OPTIONS:
+#     ~ single-step mode that calls psql -f once per file
+#          (but also prompts for password once per file).
+#     ~ help/usage option
+
+DB_HOST=$1
+DB_USER=$2
+DB_NAME=$3
+
+function usage() {
+    cat <<END_OF_USAGE
+usage: $0  db_host  db_user  db_name
+
+Automtically update the DB with all numbered updates beyond the last installed one.
+
+ALL parameters are required to access the postgres database.
+
+PARAMETERS:
+  db_host - database host system (e.g. "localhost" or "10.121.99.6")
+  db_user - database username
+  db_name - database name
+    
+Run from your source repository Open-ILS/src/sql/Pg directory.
+
+You will be prompted for the postgres password if necessary.
+
+END_OF_USAGE
+}
+
+function die() {
+    echo "ERROR: $1" >&2;
+    exit 1;
+}
+
+function usage_die() {
+    exec >&2;
+    echo;
+    echo "ERROR: $1";
+    echo;
+    usage;
+    exit 1;
+}
+
+function feedback() {
+    #TODO: add a test and verbose mode that use this.
+    echo "Updating database $DB_NAME on $DB_HOST as user $DB_USER";
+}
+
+[ -z "$DB_HOST" -o -z "$DB_USER" -o -z "$DB_NAME" ] && usage_die "Need all DB parameters";
+
+PSQL_ACCESS="-h $DB_HOST -U $DB_USER $DB_NAME";
+
+VERSION=$(psql -c "select max(version) from config.upgrade_log" -t $PSQL_ACCESS);
+[  $? -gt 0  ] && die "Database access failed.";
+# [ $VERBOSE ] && echo RAW VERSION: $VERSION     # TODO: for verbose mode
+VERSION=$(echo $VERSION | sed -e 's/^ *0*//');    # This is a separate step so we can check $? above.
+[ -z "$VERSION" ] && usage_die "config.upgrade_log missing ANY installed version data!";
+echo "* Last installed version -> $VERSION";
+
+[ -d ./upgrade ] || usage_die "No ./upgrade directory found.  Please run from Open-ILS/src/sql/Pg";
+
+declare -a FILES;
+while true; do
+    VERSION=$(($VERSION + 1));
+    PREFIX=$(printf "%0.4d" $VERSION);
+    FILE=$(ls upgrade/$PREFIX* 2>/dev/null);
+    [ ! -f "$FILE" ] && break;
+    FILES[${#FILES[@]}]=$FILE;      # "push" onto FILES array
+    echo "* Pending $FILE";
+done;
+
+COUNT=${#FILES[@]};
+
+if [ $COUNT -gt 0 ] ; then
+    echo "* $COUNT update scripts to apply."
+    exec 3>&1;  # our copy of STDOUT
+    for (( i=0; i<$COUNT; i++ )) ; do
+        echo "* Applying ${FILES[$i]}" >&3;   # to the main script STDOUT
+        cat ${FILES[$i]};                     # to the psql pipe
+    done | psql $PSQL_ACCESS ;
+else
+    echo "* Nothing to update";
+fi
-- 
2.43.2