Menu
▾
▴
[Treebase-guts] SF.net SVN: treebase:[37] trunk/treebase-core/src/main/perl/dump/sqldump
|
From: <rv...@us...> - 2009-06-11 07:33:42
|
Revision: 37
http://treebase.svn.sourceforge.net/treebase/?rev=37&view=rev
Author: rvos
Date: 2009-06-11 07:33:41 +0000 (Thu, 11 Jun 2009)
Log Message:
-----------
Changed SQL for fetching all tables
Modified Paths:
--------------
trunk/treebase-core/src/main/perl/dump/sqldump
Modified: trunk/treebase-core/src/main/perl/dump/sqldump
===================================================================
--- trunk/treebase-core/src/main/perl/dump/sqldump 2009-06-11 07:02:58 UTC (rev 36)
+++ trunk/treebase-core/src/main/perl/dump/sqldump 2009-06-11 07:33:41 UTC (rev 37)
@@ -12,6 +12,7 @@
my $with_inserts = 1; # write insert statements
my $with_creates = 0; # write create statements
my $name_file_after_table = 0; # name output file after table
+my $schema = 'TBASE2'; # name of schema to analyze
# get command line options, see Getopt::Long
GetOptions(
@@ -23,9 +24,10 @@
'pass=s' => \$ENV{'TREEBASE_DB_PASS'},
'dsn=s' => \$ENV{'TREEBASE_DB_DSN'},
'table=s' => \@tables,
- 'help|?' => sub { pod2usage( '-verbose' => 0 ) }, # see Pod::Usage
- 'man' => sub { pod2usage( '-verbose' => 1 ) }, # see Pod::Usage
'all' => sub { @tables = get_all_tables() },
+ 'help|?' => sub { pod2usage( '-verbose' => 0 ) }, # see Pod::Usage
+ 'man' => sub { pod2usage( '-verbose' => 1 ) }, # see Pod::Usage
+ 'schema=s' => sub { @tables = get_all_tables(pop) },
'file=s' => sub {
my $file = pop;
open my $fh, '>', $file
@@ -52,82 +54,89 @@
'-msg' => 'Need at least one operation to write out (--inserts and/or --creates), aborting'
) unless $with_creates or $with_inserts;
-# create database handle
-my $dbh = get_handle();
-
-for my $table ( @tables ) {
- # only using these two functions once, so for clarity as to where they
- # originate let's use the fully qualified names.
- my @names = CIPRES::TreeBase::DBIUtil::get_colnames($dbh, $table);
- my @types = CIPRES::TreeBase::DBIUtil::get_coltypes($dbh, $table);
-
- # instantiate a RecDumper object, which will format a row-as-array
- # into an insert statement
- my $dumper = CIPRES::TreeBase::RecDumper->new(
- 'FIELDS' => \@names,
- 'TYPES' => \@types,
- 'TABLE' => $table
- ) or die "Couldn't instantiate CIPRES::TreeBase::RecDumper";
+{
+ # create database handle
+ my $dbh = get_handle();
- # give dumper the output handle to write to, i.e. either STDOUT,
- # or a file specified on the command line, or the table name
- if ( $name_file_after_table ) {
- close $outhandle;
- open my $fh, '>', "${table}.sql" or die $!;
- $outhandle = $fh;
- }
- $dumper->set_output($outhandle);
-
- if ( $with_creates ) {
- print $outhandle "CREATE TABLE $table;\n";
- }
-
- if ( $with_inserts ) {
- # get all rows from $table
- my $q = qq{select * from $table};
- my $sth = $dbh->prepare($q);
- $sth->execute();
+ for my $table ( @tables ) {
+ # only using these two functions once, so for clarity as to where they
+ # originate let's use the fully qualified names.
+ my @names = CIPRES::TreeBase::DBIUtil::get_colnames($dbh, $table);
+ my @types = CIPRES::TreeBase::DBIUtil::get_coltypes($dbh, $table);
+
+ # instantiate a RecDumper object, which will format a row-as-array
+ # into an insert statement
+ my $dumper = CIPRES::TreeBase::RecDumper->new(
+ 'FIELDS' => \@names,
+ 'TYPES' => \@types,
+ 'TABLE' => $table
+ ) or die "Couldn't instantiate CIPRES::TreeBase::RecDumper";
- my $row;
-
- # if --progress is provided, print out progress counter
- if ( $with_progress_meter ) {
- my $count = 0;
- while ( $row = $sth->fetchrow_arrayref ) {
- ++$count;
- $dumper->rec(@$row);
- print STDERR "\r$count" if $count % 1000 == 0;
- }
- }
-
- # if --noprogress or default, just dump the records
- else {
- while ( $row = $sth->fetchrow_arrayref ) {
- $dumper->rec(@$row);
+ # give dumper the output handle to write to, i.e. either STDOUT,
+ # or a file specified on the command line, or the table name
+ if ( $name_file_after_table ) {
+ close $outhandle;
+ open my $fh, '>', "${table}.sql" or die $!;
+ $outhandle = $fh;
+ }
+ $dumper->set_output($outhandle);
+
+ if ( $with_creates ) {
+ print $outhandle "CREATE TABLE $table;\n";
+ }
+
+ if ( $with_inserts ) {
+ # get all rows from $table
+ my $q = qq{select * from $table};
+ my $sth = $dbh->prepare($q);
+ $sth->execute();
+
+ my $row;
+
+ # if --progress is provided, print out progress counter
+ if ( $with_progress_meter ) {
+ my $count = 0;
+ while ( $row = $sth->fetchrow_arrayref ) {
+ ++$count;
+ $dumper->rec(@$row);
+ print STDERR "\r$count" if $count % 1000 == 0;
+ }
+ }
+
+ # if --noprogress or default, just dump the records
+ else {
+ while ( $row = $sth->fetchrow_arrayref ) {
+ $dumper->rec(@$row);
+ }
}
- }
-
- # finish the statement handler
- $sth->finish();
-
- # add line break if we're printing a progress counter
- print STDERR "\n" if $with_progress_meter;
- }
+
+ # finish the statement handler
+ $sth->finish();
+
+ # add line break if we're printing a progress counter
+ print STDERR "\n" if $with_progress_meter;
+ }
+ }
+
+ # disconnect from database
+ $dbh->disconnect;
+ exit 0;
}
-# disconnect from database
-$dbh->disconnect;
-exit 0;
-
sub get_all_tables {
+ my $schema_to_analyze = shift || $schema;
my $dbh = get_handle();
- my $sth = $dbh->prepare('SELECT * FROM SYSCAT.TABLES');
+ my $sth = $dbh->prepare(sprintf('list tables for schema %s', $schema_to_analyze));
my @fetched_tables;
$sth->execute();
while ( my $row = $sth->fetchrow_hashref ) {
- if ( $row->{'DEFINER'} =~ /TBASE2/ ) {
- push @fetched_tables, $row->{'TABNAME'};
+ use Data::Dumper;
+ if ( $row->{'DEFINER'} =~ /\Q$schema\E/ ) {
+ push @fetched_tables, $row->{'TABNAME'};
}
+ else {
+ print Dumper($row);
+ }
}
return @fetched_tables;
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
