bric_pgimport

#!/usr/bin/perl -w

=head1 NAME

bric_pgimport - Builds a Bricolage Database

=head1 VERSION

$LastChangedRevision$

=head1 DATE

$LastChangedDate$

=head1 SYNOPSIS

  bric_pgimport [options]

=head1 DESCRIPTION

This is a developer program. Users should C<make> and C<make install> to build
their Bricolage installations and database. Note that it only works with PostgreSQL
7.3 and later.

=head1 OPTIONS

=over 4

=item -P

The location of the psql program. Defaults to simply 'psql' if undefined. This
should work if psql is in your path.

=item -w

The directory with the Subversion SQL files. Defaults to lib subdirectory of
BRICOLAGE_ROOT environment variable, which itself defaults to
F</usr/local/bricolage>.

=item -u

Database user login. Defaults to C<PGUSER> environment variable.

=item -p

Database user password. Defaults to C<PGPASSWORD> environment variable.

=item -d

Database name. Defaults to C<PGDATABASE> environment variable.

=item -H

PostgreSQL server host name. Will use C<PGHOST> environment variable or else
PostgreSQL assumes that it's connecting via local Unix sockets.

=item -o

PostgreSQL server port number. Will use C<PGPORT> environment variable or else
PostgreSQL assumes that it's connecting via local Unix sockets.

=item -c

Create the database first.

=item -r

Drop and recreate the exiting database (assumes -c).

=item -m

Make a new user. Pass in the user name and password separated by a
colon. Permissions will be granted to this user to access the new database
(assumes -g for this user).

=item -g

User name to which permissions should be granted on the new database.

=item -t

Deprecated.

=item -h

Print this help message.

=item -q

Quiet mode.

=back

=head1 AUTHORS

Garth Web <garth@perijove.com>

David Wheeler <david@kineticode.net>

=head1 SEE ALSO

L<Bric::Admin>

=cut

#==============================================================================#
# Dependencies                         #
#======================================#

use strict;
use DBI;
use Getopt::Std;
use File::Spec::Functions;

#==============================================================================#
# Constants                            #
#======================================#

# The database type used.
use constant DBD  => 'Pg';

# Any specific DB attributes for connecting.
use constant ATTR => {'RaiseError'  => 1,
		      'AutoCommit'  => 1,
		      'PrintError'  => 0,
		      'LongReadLen' => 32768,
		      'LongTruncOk' => 0,
		     };

#==============================================================================#
# Global Variables                     #
#======================================#

our ($opt_u, $opt_p, $opt_d, $opt_w, $opt_H, $opt_t, $opt_h, $opt_r, $opt_o,
     $opt_c, $opt_m, $opt_q, $opt_g, $opt_P);

getopts('u:p:d:w:H:o:m:htrcqg:P:');

use vars qw( @SQL @CON @VAL $STOP );

our $PSQL = $opt_P || 'psql';

#==============================================================================#
# Main Program                         #
#======================================#

# Tell STDERR to ignore PostgreSQL NOTICE messages by forking another Perl to
# filter them out.
open STDERR, "| perl -ne 'print unless /: NOTICE:  /'"
  or die "Cannot pipe STDERR: $!\n";

# Setup some initial values.
initialize();

# Insert the different file sets.
print "\nAdding table definitions...\n" unless $opt_q;
insert_file_set(\@SQL);

print "\nPrepopulating tables with default values...\n" unless $opt_q;
insert_file_set(\@VAL);

print "\nAdding constraints...\n" unless $opt_q;
insert_file_set(\@CON);

grant_permissions($opt_g, create_user());
exit;
# Do any cleanup work before exiting.
#clean_up();



#==============================================================================#
# Subroutines                          #
#======================================#

#------------------------------------------------------------------------------#

sub initialize {
    $opt_H ||= $ENV{PGHOST}; # || 'localhost';
    $opt_o ||= $ENV{PGPORT}; # || 5432;
    $opt_d ||= $ENV{PGDATABASE};
    $opt_u ||= $ENV{PGUSER};
    $opt_p ||= $ENV{PGPASSWORD};

    if ($opt_w) {
        # Add on the location of the SQL.
        $opt_w = catdir($opt_w, 'sql', DBD);
    } else {
	$ENV{BRICOLAGE_ROOT} ||= '/usr/local/bricolage';
	$opt_w = catdir($ENV{BRICOLAGE_ROOT}, 'sql', DBD);
    }

    # Print a usage message unless all required args are included or if -h has
    # been passed.
    usage() if $opt_h;
    unless ($opt_u && $opt_p && $opt_d && -d $opt_w) {
	print "\n";
	print "  -u <database login> or PGUSER environment variable required.\n"
	  unless $opt_u;
	print "  -p <database password> or PGPASSWORD environment variable"
	  . " required.\n" unless $opt_p;
	print "  -d <database name> required.\n" unless $opt_d;
	print "  No such directory '$opt_w'\n" unless -d $opt_w;
	usage();
    }

    # Set up the environment variables for psql.
    $ENV{PGHOST} ||= $opt_H if $opt_H;
    $ENV{PGPORT} ||= $opt_o if $opt_o;
    $ENV{PGDATABASE} ||= $opt_d;
    $ENV{PGUSER} ||= $opt_u;
    $ENV{PGPASSWORD} ||= $opt_p;

    # Set some vars.
    $STOP = $opt_q ? 0 : 1;

    if ($opt_c || $opt_r) {
	# Drop the current database, if necessary.
        if ($opt_r) {
            print "\nDropping database '$opt_d'.\n" unless $opt_q;
            exec_sql(qq{DROP DATABASE "$opt_d"}, 0, 'template1');
        }

	# Create a new database.
	print "\nCreating database '$opt_d'.\n" unless $opt_q;
        exec_sql(qq{CREATE DATABASE "$opt_d" WITH ENCODING = 'UNICODE'}
                   . " TEMPLATE template0", 0, 'template1');
    }

    # Find all the files of each type.
    @SQL = reverse `find $opt_w -name '*.sql'`;
    @CON = reverse `find $opt_w -name '*.con'`;
    @VAL = reverse `find $opt_w -name '*.val'`;
}

#------------------------------------------------------------------------------#

sub usage {
    my $prog = substr($0, rindex($0, '/')+1);

    print qq{
Usage: $prog [options]

Supported Options:
  -P The location of the psql program. Defaults to simply 'psql' if undefined.
     This should work if psql is in your path.
  -w The directory with the Subversion SQL files. Defaults to lib subdirectory
     of BRICOLAGE_ROOT environment variable, which itself defaults to
     /usr/local/bricolage.
  -u Database user login. Defaults to PGUSER environment variable.
  -p Database user password. Defaults to PGPASSWORD environment variable.
  -d Database name. Defaults to PGDATABASE environment variable.
  -H PostgreSQL server host name. Will use PGHOST environment variable and
     defaults to localhost
  -o PostgreSQL server port number. Will use PGPORT environment variable
     and defaults to 5432.
  -c Create the database first.
  -r Drop and recreate the exiting database (assumes -c).
  -m Make a new user. Pass in the user name and password separated by a colon.
     Permissions will be granted to this user to access the new database
     (assumes -g for this user).
  -g User name to which permissions should be granted on the new database.
  -h Print this help message.
  -q Quiet mode.
};
    exit;
}

#------------------------------------------------------------------------------#

sub insert_file_set {
    foreach my $file (@{$_[0]}) {
	chomp $file;
	print "\tImporting '$file'\n" unless $opt_q;
        exec_sql(0, $file);
    }
}

#------------------------------------------------------------------------------#

sub exec_sql {
    my ($sql, $file, $db) = @_;
    $db ||= $opt_d;
    my @args = $sql ? ('-c', $sql) : ('-f', $file);
    # System returns 0 on success, so just return if it succeeds.
    system($PSQL, '-q', @args, $db) or return;

    # We encountered a problem.
    if ($STOP) {
        print "Continue (c), Go non-interactive (g), Quit (q): ";
        my $ans = <STDIN>;
        $STOP = 0  if $ans =~ /^g/i;
        exit if $ans =~ /^q/i;
    }
}

#------------------------------------------------------------------------------#

sub create_user {
    return unless $opt_m;
    # Create the new user.
    my ($user, $pass) = split /:/, $opt_m;
    print "Creating user '$user'.\n" unless $opt_q;
    $pass =~ s/'/''/g;
    $user =~ s/'/''/g;
    my $sql = qq{
        CREATE USER "$user"
        WITH PASSWORD '$pass' NOCREATEDB NOCREATEUSER
    };
    system($PSQL, '-q', '-c', $sql, $opt_d);
    return $user;
}

#------------------------------------------------------------------------------#

sub grant_permissions {
    return unless @_;
    print "\nGranting permissions...\n" unless $opt_q;

    # This requires that we use DBI. We could probably have psql dump
    # the results of the below query to a file and then parse the file,
    # but there's not much point in investing the time to do that at this
    # point. So create the DSN.
    my $dsn = "dbname=$opt_d;";
    $dsn .= "host=$opt_H;" if $opt_H;
    $dsn .= "port=$opt_o;" if $opt_o;

    # Establish a database connection.
    my $dbh = DBI->connect(join(':','dbi',DBD,$dsn), $opt_u, $opt_p, ATTR);

    # Get a list of objects to grant permissions on.
    my $sql = qq{
        SELECT n.nspname || '.' || c.relname
        FROM   pg_catalog.pg_class c
               LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE  c.relkind IN ('r', 'S')
               AND u.usename = ?
               AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
               AND pg_catalog.pg_table_is_visible(c.oid)
    };

    my $objects;
    eval {
	my $sel = $dbh->prepare($sql);
	$objects = $dbh->selectcol_arrayref($sel, undef, lc $opt_u);
        $dbh->disconnect;
	return 1 unless @$objects;
    };

    if ($@) {
	warn "\nProblems executing sql:\n\n" unless $opt_q;
	# Log this error;

	if ($STOP) {
	    warn "$sql\n\n$@\n\n";
	    print "Continue (c), Go non-interactive (g), Quit (q): ";
	    my $ans = <STDIN>;
	    $STOP = 0  if $ans =~ /^g/i;
	    exit if $ans =~ /^q/i;
	}
    }

    $objects = join ', ', @$objects;

    foreach my $user (@_) {
	next unless $user;
	$sql = qq{
            GRANT SELECT, UPDATE, INSERT, DELETE
            ON    $objects
            TO    "$user"
        };
        exec_sql($sql);
    }
}

1;
__END__