script to update DB object comments

New script, update-db-comments.sh, to be run as part of upgrades
to refresh comments on database schema objects.  Usage is:

  update-db-comments.sh db-host db-port db-name db-user db-pass

The helper script grab-db-comment.pl is what actually parses out
the comment statements.

To avoid repetition, the list of default SQL scripts to use when
initializing an Evergreen database has been moved to a new file
called sql_file_manifest.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

git-svn-id: svn://svn.open-ils.org/ILS/trunk@20161 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh
index ef9b7b9..6ed4588 100755 (executable)
--- a/Open-ILS/src/sql/Pg/build-db.sh
+++ b/Open-ILS/src/sql/Pg/build-db.sh
@@ -76,70 +76,18 @@ EOM
 fi
 
 # ---------------------------------------------------------------------------
-# This describes the order in which the SQL files will be eval'd by psql.
+# Import files via psql, warn user on error, suggest abort.  SQL scripts
+# are processed in the ordered listed in sql_file_manifest.
 # ---------------------------------------------------------------------------
-ordered_file_list="
-  000.functions.general.sql
-
-  $fts_config_file
-
-  001.schema.offline.sql
-
-  002.schema.config.sql
-  002.functions.aggregate.sql
-  002.functions.config.sql
-
-  005.schema.actors.sql
-  006.schema.permissions.sql
-  008.schema.query.sql
-  010.schema.biblio.sql
-  011.schema.authority.sql
-  012.schema.vandelay.sql
-  015.schema.staging.sql
-  020.schema.functions.sql
-  030.schema.metabib.sql
-  040.schema.asset.sql
-  070.schema.container.sql
-  080.schema.money.sql
-  090.schema.action.sql
-  095.schema.booking.sql
- 
-  099.matrix_weights.sql 
-  100.circ_matrix.sql
-  110.hold_matrix.sql
-
-  210.schema.serials.sql
-  200.schema.acq.sql
-  201.acq.audit-functions.sql
-
-  300.schema.staged_search.sql
-  400.schema.action_trigger.sql
-  
-  500.view.cross-schema.sql
-  
-  800.fkeys.sql
-  
-  900.audit-functions.sql
-  901.audit-tables.sql
-  950.data.seed-values.sql
-  951.data.MODS-xsl.sql
-  952.data.MODS3-xsl.sql
-  953.data.MODS32-xsl.sql
-  954.data.MODS33-xsl.sql
-  954.data.marc21expand880.sql
-
-  990.schema.unapi.sql
-  
-  reporter-schema.sql
-  extend-reporter.sql
+cat sql_file_manifest | while read sql_file; do
+  if [ `expr "$sql_file" : "^#"` = 1 ] || [ "$sql_file" = '' ]; then
+    continue;
+  fi
 
-  999.functions.global.sql
-"
+  if [ $sql_file = 'FTS_CONFIG_FILE' ]; then
+    sql_file=$fts_config_file
+  fi
 
-# ---------------------------------------------------------------------------
-# Import files via psql, warn user on error, suggest abort.
-# ---------------------------------------------------------------------------
-for sql_file in $ordered_file_list; do
   # It would be wise to turn this on only if confidence is high that errors in
   # scripts will result in terminal failures.  Currently, there are a couple
   # that seem benign.  --asjoyner

diff --git a/Open-ILS/src/sql/Pg/grab-db-comments.pl b/Open-ILS/src/sql/Pg/grab-db-comments.pl
new file mode 100755 (executable)
index 0000000..6da505a
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/grab-db-comments.pl
@@ -0,0 +1,45 @@
+#!/usr/bin/perl
+
+# Copyright (C) 2011 Equinox Software, Inc.
+# Galen Charlton <gmc@esilibrary.com>
+#
+# Extract 'comment on' statements from Evergreen's SQL initialization
+# scripts.  Useful for updating the comments after an upgrade.
+#
+# This program is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+# 
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+
+use strict;
+use warnings;
+
+unless ($#ARGV == 0) {
+    print "usage: $0 sql_file_manifest\n";
+    print "output is a set of SQL statements to be run\n";
+    print "in an Evergreen database to update schema comments.\n";
+    exit(1);
+}
+
+open MANIFEST, '<', $ARGV[0];
+while (<MANIFEST>) {
+    chomp;
+    my $file = $_;
+    $file =~ s/\s+$//;
+    $file =~ s/^\s+//;
+    next unless $file ne '' and $file !~ /^#/ and $file ne 'FTS_CONFIG_FILE';
+    open IN, '<', $file or next; # errors blithely ignored
+    my $contents = join('', <IN>);
+    print "$_\n\n" foreach $contents =~ /(comment on .*? is \$\$.*?\$\$;)/sig;
+    #my @comments =  $contents =~ /(comment on .*? is \$\$.*?\$\$;)/sig;
+    #foreach my $comment (@comments) {
+        #print $comment, "\n\n";
+    #}
+    close IN;
+}
+close MANIFEST;

diff --git a/Open-ILS/src/sql/Pg/sql_file_manifest b/Open-ILS/src/sql/Pg/sql_file_manifest
new file mode 100644 (file)
index 0000000..70d7dcd
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/sql_file_manifest
@@ -0,0 +1,60 @@
+# Manifest of SQL scripts to be run when initializing an Evergreen
+# database.  Files should be listed in the order to be loaded.  The
+# name "FTS_CONFIG_FILE" is a placeholder for the Postgres-version-specific
+# fulltext search configuration SQL file selected by build-db.sh.
+
+000.functions.general.sql
+
+FTS_CONFIG_FILE
+
+001.schema.offline.sql
+
+002.schema.config.sql
+002.functions.aggregate.sql
+002.functions.config.sql
+
+005.schema.actors.sql
+006.schema.permissions.sql
+008.schema.query.sql
+010.schema.biblio.sql
+011.schema.authority.sql
+012.schema.vandelay.sql
+015.schema.staging.sql
+020.schema.functions.sql
+030.schema.metabib.sql
+040.schema.asset.sql
+070.schema.container.sql
+080.schema.money.sql
+090.schema.action.sql
+095.schema.booking.sql
+ 
+099.matrix_weights.sql 
+100.circ_matrix.sql
+110.hold_matrix.sql
+
+210.schema.serials.sql
+200.schema.acq.sql
+201.acq.audit-functions.sql
+
+300.schema.staged_search.sql
+400.schema.action_trigger.sql
+
+500.view.cross-schema.sql
+
+800.fkeys.sql
+
+900.audit-functions.sql
+901.audit-tables.sql
+950.data.seed-values.sql
+951.data.MODS-xsl.sql
+952.data.MODS3-xsl.sql
+953.data.MODS32-xsl.sql
+954.data.MODS33-xsl.sql
+954.data.marc21expand880.sql
+
+990.schema.unapi.sql
+
+reporter-schema.sql
+extend-reporter.sql
+
+999.functions.global.sql

diff --git a/Open-ILS/src/sql/Pg/update-db-comments.sh b/Open-ILS/src/sql/Pg/update-db-comments.sh
new file mode 100755 (executable)
index 0000000..22135c0
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/update-db-comments.sh
@@ -0,0 +1,24 @@
+#!/bin/sh
+
+if [ $# = 0 ]; then
+    echo Update database schema comments for an Evegreen database
+    echo
+    echo usage: $0 db-host db-port db-name db-user db-password
+    exit 0;
+fi
+
+# ---------------------------------------------------------------------------
+# Store command line args for later use
+# args: {db-host} {db-port} {db-name} {db-user} {db-password}
+# ---------------------------------------------------------------------------
+PGHOST=$1
+PGPORT=$2
+PGDATABASE=$3
+PGUSER=$4
+PGPASSWORD=$5
+export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD
+
+cd `dirname $0`
+export PATH=.:$PATH
+
+grab-db-comments.pl sql_file_manifest | psql