[prev in list] [next in list] [prev in thread] [next in thread]
List: bricolage-commits
Subject: [6621] Initial revision of a utility similar to bric_pgimport.
From: tamas () bricolage ! cc
Date: 2005-08-30 12:58:51
Message-ID: 20050830125851.32746.qmail () x1 ! develooper ! com
[Download RAW message or body]
Revision: 6621
Author: tamas
Date: 2005-08-30 05:58:49 -0700 (Tue, 30 Aug 2005)
ViewCVS: http://viewsvn.bricolage.cc/?rev=6621&view=rev
Log Message:
-----------
Initial revision of a utility similar to bric_pgimport. The grant_permissions() \
function seems to be ugly and broken now, fixes are on the move.
Added Paths:
-----------
bricolage/branches/dev_mysql/bin/bric_myimport
["r6621-tamas.diff" (r6621-tamas.diff)]
Added: bricolage/branches/dev_mysql/bin/bric_myimport
===================================================================
--- bricolage/branches/dev_mysql/bin/bric_myimport 2005-08-30 12:40:56 UTC (rev 6620)
+++ bricolage/branches/dev_mysql/bin/bric_myimport 2005-08-30 12:58:49 UTC (rev 6621)
@@ -0,0 +1,397 @@
+#!/usr/bin/perl -w
+
+=head1 NAME
+
+bric_pgimport - Builds a Bricolage Database
+
+=head1 VERSION
+
+$LastChangedRevision$
+
+=head1 DATE
+
+$LastChangedDate: 2005-07-13 05:19:39 +0200 (Wed, 09 Jun 2004) $
+
+=head1 SYNOPSIS
+
+ bric_myimport [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 MySQL
+5 and later.
+
+=head1 OPTIONS
+
+=over 4
+
+=item -P
+
+The location of the mysql program. Defaults to simply 'mysq' if undefined. This
+should work if mysql 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<MYUSER> environment variable.
+
+=item -p
+
+Database user password. Defaults to C<MYPASSWORD> environment variable.
+
+=item -d
+
+Database name. Defaults to C<MYDATABASE> environment variable.
+
+=item -H
+
+MySQL server host name. Will use C<MYHOST> environment variable or else
+MySQL assumes that it's connecting via local Unix sockets.
+
+=item -o
+
+MySQL server port number. Will use C<MYPORT> environment variable or else
+MySQL 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
+
+If true, insert database test values.
+
+=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 => 'mysql';
+
+# 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 @TST $STOP );
+
+our $MYSQL = $opt_P || 'mysql';
+
+#==============================================================================#
+# Main Program #
+#======================================#
+
+
+# Tell STDERR to ignore MySQL 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);
+
+if ($opt_t) {
+ print "\nPrepopulating tables with test values...\n" unless $opt_q;
+ insert_file_set(\@TST);
+}
+
+print "\nAdding constraints...\n" unless $opt_q;
+insert_file_set(\@CON);
+
+# XXX check grant_permissions syntax
+grant_permissions($opt_g, create_user());
+exit;
+
+# Do any cleanup work before exiting.
+#clean_up();
+
+
+
+#==============================================================================#
+# Subroutines #
+#======================================#
+
+#----------------------------- -------------------------------------------------#
+
+sub initialize {
+ $opt_H ||= $ENV{MYHOST}; # || 'localhost';
+ $opt_o ||= $ENV{MYPORT}; # || 3306;
+ $opt_d ||= $ENV{MYDATABASE};
+ $opt_u ||= $ENV{MYUSER};
+ $opt_p ||= $ENV{MYPASSWORD};
+
+ 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 MYUSER environment variable required.\n"
+ unless $opt_u;
+ print " -p <database password> or MYPASSWORD 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{MYHOST} ||= $opt_H if $opt_H;
+ $ENV{MYPORT} ||= $opt_o if $opt_o;
+ $ENV{MYDATABASE} ||= $opt_d;
+ $ENV{MYUSER} ||= $opt_u;
+ $ENV{MYPASSWORD} ||= $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;
+ # XXX MySQL hasn't got the same templating as PgSQL, so using 'template1' should be \
revised. + exec_sql(qq{CREATE DATABASE "$opt_d" DEFAULT CHARACTER SET 'utf8' \
DEFAULT COLLATE = 'utf8_bin' }, 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'`;
+ @TST = reverse `find $opt_w -name '*.tst'` if $opt_t;
+}
+
+#------------------------------------------------------------------------------#
+
+# XXX change all PostgreSQL-related usage info to MySQL
+sub usage {
+ my $prog = substr($0, rindex($0, '/')+1);
+
+ print qq{
+Usage: $prog [options]
+
+Supported Options:
+ -P The location of the mysql program. Defaults to simply 'mysql' if undefined.
+ This should work mysql 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 MYUSER environment variable.
+ -p Database user password. Defaults to MYPASSWORD environment variable.
+ -d Database name. Defaults to MYDATABASE environment variable.
+ -H MySQL server host name. Will use MYHOST environment variable and
+ defaults to localhost
+ -o MySQL server port number. Will use MYPORT environment variable
+ and defaults to 3306.
+ -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.
+ -t If true, insert database test values.
+ -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.
+ # MySQL has a --no-pager switch to disable output instead of Pg's -q
+ system($MYSQL, '--no-pager', @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;
+ }
+}
+
+#------------------------------------------------------------------------------#
+
+# XXX check MySQL default user permissions, revoke (nearly) all permissions if
+# XXX necessary (defaults should be okay)
+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;
+ # By default, MySQL doesn't allow a simple user
+ # to create a database
+ my $sql = qq{
+ CREATE USER "$user"
+ IDENTIFIED BY '$pass'
+ };
+ system($MYSQL, '--no-pager', '-c', $sql, $opt_d);
+ return $user;
+}
+
+#------------------------------------------------------------------------------#
+
+# XXX grant_permissions: create a similar permission granter function in MySQL.
+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.
+ # XXX this is buggy
+ #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__
Property changes on: bricolage/branches/dev_mysql/bin/bric_myimport
___________________________________________________________________
Name: svn:executable
+ *
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic