[svn:p5ee] r15607 - p5ee/trunk/App-Repository/bin

Author: spadkins
Date: Mon Apr  1 14:45:44 2013
New Revision: 15607

Added:
   p5ee/trunk/App-Repository/bin/dbexport   (contents, props changed)
   p5ee/trunk/App-Repository/bin/dbimport   (contents, props changed)

Log:
new

Added: p5ee/trunk/App-Repository/bin/dbexport
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/bin/dbexport	Mon Apr  1 14:45:44 2013
@@ -0,0 +1,117 @@
+#!/usr/bin/perl -w
+
+use strict;
+
+use App::Options (
+    options => [qw(repository table where exportdir csv)],
+    option => {
+        repository => {
+            description => "the name of the repository to use",
+            default => "default",
+        },
+        table => {
+            description => "the name of the table to export",
+        },
+        where => {
+            description => "additional where clause criteria",
+        },
+        exportdir => {
+            description => "directory to export data files to",
+            default => '{dbname}',
+        },
+        csv => {
+            description => "set options according to CSV files",
+        },
+        dump => {
+            description => "use mysqldump instead of portable App-Repository approach",
+        },
+    },
+);
+
+my ($dbname, $table);
+
+if (($#ARGV > -1 && $ARGV[0] =~ /^-\?/) ||
+    ($#ARGV <= 0 || $#ARGV >= 3)) {
+    print STDERR "Usage: $0 <db> <table> [<where>]\n";
+    print STDERR "       $0 -?     (this message)\n";
+    exit 1;
+}
+
+&export_table(@ARGV);
+
+exit 0;
+
+sub export_table {
+    my ($dbname, $table, $where) = @_;
+    print "Exporting $dbname.$table ...\n";
+
+    my ($exportdir, $txtfile, $cmd, $rc);
+
+    if ($App::options{exportdir}) {
+        $exportdir = $App::options{exportdir};
+        $exportdir =~ s/\{dbname\}/$dbname/g;
+        $exportdir =~ s/\{table\}/$table/g;
+        mkdir($exportdir) if (! -d $exportdir);
+    }
+    else {
+        $exportdir = ".";
+    }
+
+    my $csv = $App::options{csv};
+    if ($App::options{dump}) {
+        if ($where) {
+            $where = "--where=\"$where\"";
+        }
+        else {
+            $where = "";  # export all rows
+        }
+
+        if ($csv) { $cmd = "mysqldump -t $where --tab=$exportdir --fields-terminated-by=',' --fields-enclosed-by='\"' --fields-escaped-by='\"' $dbname $table"; }
+        else      { $cmd = "mysqldump -n -t $where --tab=$exportdir --fields-terminated-by='|' --fields-escaped-by='\\\\' $dbname $table"; }
+
+        #print "CMD: $cmd\n";
+        $rc = 0xffff & system($cmd);
+        if ($rc) {
+            print STDERR "CMD FAILED ($rc): [$cmd]\n";
+            return;
+        }
+    }
+    else {
+        use App;
+        my $context = App->context;
+        my $repository_name = $dbname;
+        my $rep = $context->repository($repository_name);
+        my $table_def = $rep->get_table_def($table);
+        my $phys_columns = $table_def->{phys_columns};
+        my $params = {};
+        $params->{"where.verbatim"} = $where if ($where);
+        my $rows = $rep->get_rows($table, $params, $phys_columns);
+        my $sep = $csv ? "," : "|";
+        open(my $fh, ">", "$exportdir/$table.csv") || die "Unable to open $exportdir/$table.csv: $!";
+        print $fh join($sep, @$phys_columns), "\n";
+        foreach my $row (@$rows) {
+            for (my $i = 0; $i <= $#$phys_columns; $i++) {
+                print $fh $sep if ($i > 0);
+                my $value = $row->[$i];
+                if (!defined $value || $value eq "") {
+                    # do nothing
+                }
+                elsif ($value =~ s/\"/""/g) {
+                    print $fh "\"$value\"";
+                }
+                elsif ($value =~ /\'/) {
+                    print $fh "\"$value\"";
+                }
+                elsif ($value =~ /[$sep]/) {
+                    print $fh "\"$value\"";
+                }
+                else {
+                    print $fh $value;
+                }
+            }
+            print $fh "\n";
+        }
+        close($fh);
+    }
+}
+

Added: p5ee/trunk/App-Repository/bin/dbimport
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/bin/dbimport	Mon Apr  1 14:45:44 2013
@@ -0,0 +1,53 @@
+#!/usr/bin/perl -w
+
+use strict;
+
+use App::Options (
+    options => [qw(repository table)],
+    option => {
+        repository => {
+            description => "the name of the repository to use",
+            default => "default",
+        },
+        table => {
+            description => "the name of the table to export",
+        },
+    },
+);
+
+my ($dbname, $table);
+
+if (($#ARGV > -1 && $ARGV[0] =~ /^-\?/) ||
+    ($#ARGV <= 0)) {
+    print STDERR "Usage: $0 <db> <table> [<table> ...]\n";
+    print STDERR "       $0 -?     (this message)\n";
+    exit 1;
+}
+
+$dbname = shift(@ARGV);
+
+foreach $table (@ARGV) {
+    &import_table($dbname, $table);
+}
+
+exit 0;
+
+sub import_table {
+    my ($dbname, $table) = @_;
+    print "Importing $dbname.$table ...\n";
+
+    my ($exportdir, $txtfile, $where, $cmd, $rc);
+
+    $exportdir = $dbname;
+    $txtfile = "$exportdir/$table.txt";
+
+    if ($App::options{csv}) { $cmd = "mysqlimport --fields-terminated-by=',' --fields-escaped-by='\"' $dbname $txtfile"; }
+    else                    { $cmd = "mysqlimport --fields-terminated-by='|' --fields-escaped-by='\\' $dbname $txtfile"; }
+
+    $rc = 0xffff & system($cmd);
+    if ($rc) {
+        print STDERR "CMD FAILED ($rc): [$cmd]\n";
+        return;
+    }
+}
+

• [svn:p5ee] r15607 - p5ee/trunk/App-Repository/bin by spadkins