From f3bf25a720835c905dd3f323aac3566fceb6d77a Mon Sep 17 00:00:00 2001 From: gmc Date: Mon, 18 Apr 2011 15:41:18 +0000 Subject: [PATCH] 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 git-svn-id: svn://svn.open-ils.org/ILS/trunk@20161 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/build-db.sh | 70 ++++--------------------------- Open-ILS/src/sql/Pg/grab-db-comments.pl | 45 ++++++++++++++++++++ Open-ILS/src/sql/Pg/sql_file_manifest | 60 ++++++++++++++++++++++++++ Open-ILS/src/sql/Pg/update-db-comments.sh | 24 +++++++++++ 4 files changed, 138 insertions(+), 61 deletions(-) create mode 100755 Open-ILS/src/sql/Pg/grab-db-comments.pl create mode 100644 Open-ILS/src/sql/Pg/sql_file_manifest create mode 100755 Open-ILS/src/sql/Pg/update-db-comments.sh diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh index ef9b7b93f..6ed458819 100755 --- 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 index 000000000..6da505abb --- /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 +# +# 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 () { + 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('', ); + 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 index 000000000..70d7dcda9 --- /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 index 000000000..22135c0e3 --- /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 -- 2.11.0