head 1.9; access; symbols AS_AFTER_RESTRUCTURING:1.5; locks; strict; comment @# @; 1.9 date 2003.04.23.09.04.07; author rse; state Exp; branches; next 1.8; 1.8 date 2003.03.07.20.30.45; author rse; state Exp; branches; next 1.7; 1.7 date 2003.03.07.16.50.53; author rse; state Exp; branches; next 1.6; 1.6 date 2003.03.07.16.50.31; author rse; state Exp; branches; next 1.5; 1.5 date 2002.11.04.15.03.41; author rse; state Exp; branches; next 1.4; 1.4 date 2002.10.02.17.28.32; author rse; state Exp; branches; next 1.3; 1.3 date 2002.10.01.16.06.52; author rse; state Exp; branches; next 1.2; 1.2 date 2002.09.29.10.11.04; author rse; state Exp; branches; next 1.1; 1.1 date 2002.09.29.09.31.18; author rse; state Exp; branches; next ; desc @@ 1.9 log @flush pending changes @ text @#!/bin/sh -- # -*- perl -*- eval 'exec perl -S $0 ${1+"$@@"}' if $running_under_some_shell; ## ## AS -- Accounting System ## Copyright (c) 2002-2003 Cable & Wireless Deutschland ## Copyright (c) 2002-2003 Ralf S. Engelschall ## ## This file is part of AS, an accounting system which can be ## found at http://as.is.eu.cw.com/ ## ## 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.0 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. ## ## You should have received a copy of the GNU General Public License ## along with this program; if not, write to the Free Software ## Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 ## USA, or contact The OSSP Project . ## ## as_db.pl: AS Database Setup Utility ## require 5.006; use strict; use Getopt::Long; use lib "."; require "as_db.pm"; import AS::DB; # program information my $progname = "as_db"; my $progvers = "0.0.1"; # option defaults my $cfg = { "su_username" => "", "su_password" => "", "su_database" => "", "as_username" => "", "as_password" => "", "as_database" => "", "as_expiring" => "", "db_schema" => "", "db_host" => "", "db_port" => "", }; my $opt = { "verbose" => 0, "version" => 0, "help" => 0, }; # fill in defaults from module my $as = new AS::DB; foreach my $attr (keys(%{$cfg})) { $cfg->{$attr} = $as->attr($attr); } # exception handling support $SIG{__DIE__} = sub { my ($err) = @@_; # $err =~ s|\s+at\s+.*||s if (not $opt->{verbose}); print STDERR "$progname:ERROR: $err ". ($! ? "($!)" : "") . "\n"; exit(1); }; # print usage message sub usage { print "Usage: $progname [options] create|destroy\n" . "Available options:\n" . " -U,--su-username=STR set super-user username (default: \"".$cfg->{'su_username'}."\")\n" . " -P,--su-password=STR set super-user password (default: \"".$cfg->{'su_password'}."\")\n" . " -D,--su-database=STR set super-user database (default: \"".$cfg->{'su_database'}."\")\n" . " -u,--as-username=STR set AS user username (default: \"".$cfg->{'as_username'}."\")\n" . " -p,--as-password=STR set AS user password (default: \"".$cfg->{'as_password'}."\")\n" . " -d,--as-database=STR set AS user database (default: \"".$cfg->{'as_database'}."\")\n" . " -e,--as-expiring=STR set AS user expiring (default: \"".$cfg->{'as_expiring'}."\")\n" . " -s,--db-schema=FILE set file name of AS schema definition (default: \"".$cfg->{'db_schema'}."\")\n" . " -h,--db-host=HOST set host name of PostgreSQL RBMS (default: \"".$cfg->{'db_host'}."\")\n" . " -l,--db-port=PORT set port number of PostgreSQL RBMS (default: \"".$cfg->{'db_port'}."\")\n" . " -v,--verbose enable verbose run-time mode\n" . " -V,--version print program version\n" . " -h,--help print this usage page\n"; } # command line parsing Getopt::Long::Configure("bundling"); my $result = GetOptions( 'U|su-username=s' => \$cfg->{su_username}, 'P|su-password=s' => \$cfg->{su_password}, 'D|su-database=s' => \$cfg->{su_database}, 'u|as-username=s' => \$cfg->{as_username}, 'p|as-password=s' => \$cfg->{as_password}, 'd|as-database=s' => \$cfg->{as_database}, 'e|as-expiring=s' => \$cfg->{as_expiring}, 's|db-schema=s' => \$cfg->{db_schema}, 'h|db-host=s' => \$cfg->{db_host}, 'l|db-port=s' => \$cfg->{db_port}, 'v|verbose' => \$opt->{verbose}, 'V|version' => \$opt->{version}, 'h|help' => \$opt->{help}, ) || die "option parsing failed"; if ($opt->{help}) { &usage(); exit(0); } if ($opt->{version}) { print "$progname $progvers\n"; exit(0); } if (@@ARGV != 1) { die "invalid number of argument (use --help for help)"; } if ($ARGV[0] !~ m/^(create|destroy)$/) { die "invalid command \"$ARGV[0]\""; } # create AS DB object my $as = new AS::DB or die "unable to create AS DB object"; # configure AS DB object foreach my $attr (keys(%{$cfg})) { $as->attr($attr, $cfg->{$attr}); } if ($ARGV[0] eq 'create') { # create AS database $as->schema_create(); } elsif ($ARGV[0] eq 'destroy') { # destroy AS database $as->schema_destroy(); } # destroy AS DB object $as->destroy; exit(0); @ 1.8 log @fully work off the AS DB API architecture by introducing a Perl management API for the AS DB which currently at least performs all the schema creation/destruction the old executable as_db.pl did. The new as_db.pl now just calls this Perl API. @ text @d34 2 a35 1 use as_db; d61 1 a61 1 my $as = new as_db; d126 1 a126 1 my $as = new as_db @ 1.7 log @fix extension on comment @ text @d33 2 a34 3 use IO; use DBI; use DBD::Pg; d36 5 a40 1 # default configuration d42 6 a47 6 "su_username" => "postgresql", "su_password" => "postgresql", "su_database" => "template1", "as_username" => "as", "as_password" => "as", "as_database" => "as", d49 8 d59 33 d102 6 d109 23 d133 7 a139 84 # determine expire time of account if ($cfg->{as_expiring} eq '') { my $now = time(); my $expires = $now + (60*60*24*356*5); my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($expires); $cfg->{as_expiring} = sprintf("%04d-%02d-%02d 00:00:00", $year+1900, $mon+1, $mday); } # read data model SQL definition my $sql = ''; my $io = new IO::File "); $io->close; # replace variables in SQL statements foreach my $var (keys(%{$cfg})) { $sql =~ s|\@@${var}\@@|$cfg->{$var}|sg; } # extract comments and annotate SQL CREATE TABLE statements # with the corresponding P/SQL COMMENT ON TABLE|COLUMN statements my $do = $sql; $do =~ s|\n--\s+([^\n]+)\nCREATE\s+SEQUENCE\s+(\S+)\s+(.+?);|&do_seq($1, $2, $3), ''|sge; $do =~ s|\n--\s+([^\n]+)\nCREATE\s+TABLE\s+(\S+)\s+\((.+?\n)\);|&do1($1, $2, $3), ''|sge; sub do1 { my ($comment, $table, $do) = @@_; $comment =~ s|^\s*(.+?)\s*$|$1|s; my $C = {}; #$do =~ s|\n\s+(\S+)\s+(.+?)\n\s+--\s+(([^\n\\]+\\\n)*[^\n\\]+)|&do2($C, $1, $2, $3), ''|sge; $do =~ s|\n\s+(\S+)\s+(.+?)\n\s+--\s+([^\n]+(\n\s+--\s+[^\n]+)*)|&do2($C, $1, $2, $3), ''|sge; sub do2 { my ($C, $field, $type, $comment) = @@_; #$comment =~ s|\s*\\\n\s*(--\s*)?| |sg; $comment =~ s|\s*\n\s*(--\s*)?| |sg; $comment =~ s|\s+| |sg; $comment =~ s|^\s*(.+?)\s*$|$1|s; $C->{$field} = $comment; } my $com = "COMMENT ON TABLE $table\n" . " IS '$comment';\n"; foreach my $field (sort(keys(%{$C}))) { my $comment = $C->{$field}; $com .= "COMMENT ON COLUMN $table.$field\n" . " IS '$comment';\n"; } $sql =~ s|(CREATE\s+TABLE\s+$table\s+\(.+?\n\);)|$1\n$com|s; } sub do_seq { my ($comment, $sequence, $do) = @@_; $comment =~ s|^\s*(.+?)\s*$|$1|s; my $com = "COMMENT ON SEQUENCE $sequence\n" . " IS '$comment';\n"; $sql =~ s|(CREATE\s+SEQUENCE\s+$sequence\s+.+?;)|$1\n$com|s; } # remove the comments from the original SQL $sql =~ s|^\s*--\s+.+$||mg; $sql =~ s|\n+|\n|sg; # execute SQL statements my $db = undef; while ($sql =~ s|^\s*(.+?;)||s) { my $stmt = $1; $stmt =~ s|\s+| |sg; $stmt =~ s|^\s+||s; $stmt =~ s|\s+$||s; if ($stmt =~ m|^\\connect\s+(\S+)\s+(\S+)\s*;$|) { my ($database, $username) = ($1, $2); my $password = ($username eq $cfg->{as_username} ? $cfg->{as_password} : $cfg->{su_password}); $db->disconnect if (defined($db)); print "DBI: connect(\"dbi:Pg:dbname=$database\", \"$username\", \"$password\")\n"; ($db = DBI->connect("dbi:Pg:dbname=$database", $username, $password, { RaiseError => 1, AutoCommit => 1 })) || die "unable to connect to database \"$database\" " . "as user \"$username\" and password \"$password\""; $db->{RaiseError} = 1; } else { print "SQL: $stmt\n"; eval { $db->do($stmt) }; if ($@@) { die "unable to perform SQL command \"$stmt\": ".$db->errstr; } } d141 5 a145 1 $db->disconnect if (defined($db)); @ 1.6 log @bump copyright year @ text @d27 1 a27 1 ## as_db.mk: AS Database Setup Utility @ 1.5 log @give PostgreSQL error back, too. @ text @d6 2 a7 2 ## Copyright (c) 2002 Cable & Wireless Deutschland ## Copyright (c) 2002 Ralf S. Engelschall @ 1.4 log @support also CREATE SEQUENCE commenting @ text @d135 1 d139 4 a142 2 $db->do($stmt) || die "unable to perform SQL command \"$stmt\""; @ 1.3 log @allow multi-line comments in SQL @ text @d82 1 d106 7 @ 1.2 log @cleanup the data model stuff @ text @d87 2 a88 1 $do =~ s|\n\s+(\S+)\s+(.+?)\n\s+--\s+([^\n]+)|&do2($C, $1, $2, $3), ''|sge; d91 3 @ 1.1 log @add DBI-based database creation scripts @ text @d79 4 a82 4 # extract comments and create P/SQL COMMENT statements my $O = ''; my $x = $sql; $x =~ s|\n--\s+([^\n]+)\nCREATE\s+TABLE\s+(\S+)\s+\((.+?\n)\);|&do1($1, $2, $3), ''|sge; d84 1 a84 1 my ($comment, $table, $sql) = @@_; d87 1 a87 1 $sql =~ s|\n\s+(\S+)\s+(.+?)\n\s+--\s+([^\n]+)|&do2($C, $1, $2, $3), ''|sge; d93 2 a94 2 $O .= "COMMENT ON TABLE $table IS\n" . " '$comment';\n"; d97 2 a98 2 $O .= "COMMENT ON COLUMN $table.$field IS\n" . " '$comment';\n"; d100 1 d103 1 a103 2 # remove the comments from the original SQL and # add generated COMMENT statements a105 1 $sql .= $O; a127 2 #$db->commit # || die "unable to commit SQL command \"$stmt\""; @