X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-bin%2Fmig-gsheet;h=dbf15998b1602deb7468e6ab9f3bef8571fc6687;hp=86654f8e86c39a2a24f34fcacca043504c3a5034;hb=b735c4228a8f6315d4da35e10d3826cf4044bd04;hpb=abd558500e6bc82e02c9617b9627799ae16e7a4f

diff --git a/mig-bin/mig-gsheet b/mig-bin/mig-gsheet
index 86654f8..dbf1599 100755
--- a/mig-bin/mig-gsheet
+++ b/mig-bin/mig-gsheet
@@ -11,6 +11,7 @@ use Storable;
 use DBI;
 use FindBin;
 use lib "$FindBin::Bin/";
+my $mig_bin = "$FindBin::Bin/";
 use Mig;
 use strict;
 use Env qw(
@@ -21,8 +22,9 @@ use Switch;
 use Cwd 'abs_path';
 use Pod::Usage;
 use Data::Dumper;
+use DateTime;
 
-my $mig_bin = "$FindBin::Bin/";
+pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
 
 Mig::die_if_no_env_migschema();
 die_if_gsheet_tracked_table_does_not_exist();
@@ -65,15 +67,14 @@ foreach my $arg (@ARGV) {
     }
 }
 
-abort('invalid value for --export') unless ($cmd_export eq 'on' or $cmd_export eq 'off');
-abort('must specify --push or --pull') unless (defined $cmd_push or defined $cmd_pull);
+abort('must specify --push (db->worksheets) or --pull (worksheets->db)') unless (defined $cmd_push or defined $cmd_pull);
 if (defined $cmd_push and defined $cmd_pull) { abort('you can not specify both a --push and --pull on the same command'); }
 
 my $dbh = Mig::db_connect();
 my $spreadsheet = connect_gsheet();
 abort('could not connect to google sheet') unless (defined $spreadsheet);
 
-$sql = 'SELECT tab_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table;';
+$sql = 'SELECT tab_name FROM gsheet_tracked_table;';
 $sth = $dbh->prepare($sql);
 my $ra = $sth->execute();
 while (my @row = $sth->fetchrow_array) {
@@ -98,8 +99,6 @@ if (defined $cmd_pull) {
         my @rows = $pull_ws->rows;
         my @content;
         map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
-        #print Dumper($pull_ws->cells);
-        #print Dumper(@content);
         my @tab_headers = shift @content;
         my $tab_headers_length = $#{ $tab_headers[0] };
         my @pg_headers;
@@ -114,6 +113,7 @@ if (defined $cmd_pull) {
             insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
         }
         timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
+        if ($cmd_export == 1) { export_table($dbh,$push_tb); }
     }
 }
 
@@ -158,11 +158,10 @@ if (defined $cmd_push) {
         foreach my $row ( @{$grabhash} ) {
             my $record = {};
             for my $column ( sort keys %{ $row } ) {
-                print Dumper(@$sheet_headers);
-                print "column: $column\n";
+                #print Dumper(@$sheet_headers);
+                #print "column: $column\n";
                 my $clean_column = $column;
                 $clean_column =~ s/_//g;
-                print "clean column: $clean_column\n";
                 if ( $column ~~ @$sheet_headers ) {
                     $record->{$clean_column} = $row->{$column}; 
                 }
@@ -170,16 +169,38 @@ if (defined $cmd_push) {
             push @content, $record;
         }
 
-        #print Dumper(@content); 
         foreach my $fillsheet (@content) {
             my $new_row = $push_ws->add_row (
                 $fillsheet
             );
         }
         timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
+        if ($cmd_export == 1) { export_table($dbh,$pull_tb); }
     }
 }   
 
+sub export_table {
+    my $dbh = shift;
+    my $table = shift;
+
+    my $dt = DateTime->now;
+    my $date = $dt->ymd;
+    my $hms = $dt->hms;
+    my $efile = $MIGGITDIR . $table . '_' . $date . '_' . $hms . '.tsv';
+    my @data;
+    my $record_count = 0;
+    $dbh->do("COPY $table TO STDOUT CSV HEADER;");
+    1 while $dbh->pg_getcopydata(\$data[$record_count++]) >= 0;
+    open (my $eout, '>', $efile) or abort("Could NOT open $efile.");
+    foreach my $d (@data) {
+        print $eout $d;
+    }
+    print "$efile written.\n";
+    close $eout;
+
+}
+
+
 sub die_if_gsheet_tracked_table_does_not_exist {
     if (!check_for_gsheet_tracked_table()) {
         die "Table $MIGSCHEMA.gsheet_tracked_table does not exist.  Bailing...\n";
@@ -319,12 +340,10 @@ sub insert_row {
     my $header_string = '(' . join(",", @headers) . ')';
     map {s/\'/\'\'/g; } @row_data;
     my $row_string = '(' . join(",", map {qq/'$_'/} @row_data) . ')';
-    print "INSERT INTO $schema.$table $header_string VALUES $row_string\n"; 
+    #print "INSERT INTO $schema.$table $header_string VALUES $row_string\n"; 
     $dbh->do(qq/
         INSERT INTO $schema.$table $header_string VALUES $row_string ;
     /);
-    
-    print "Row pushed to $table in schema $schema.\n";
 }
 
 sub timestamp {
@@ -355,7 +374,6 @@ sub truncate_table {
 sub abort {
     my $msg = shift;
     print STDERR "$0: $msg", "\n";
-    print_usage();
     exit 1;
 }
 
@@ -388,23 +406,4 @@ sub connect_gsheet {
     return $spreadsheet;
 }
 
-sub print_usage {
-    print <<_USAGE_;
-
-Mig gsheet is for synchronizing data between migration postgresql tables 
-and Google Sheets.  It accepts the following command line arguments:
-
---push table_name or all
-
---pull worksheet_name or all
 
-Either --push or --pull must be present and it must be one or the other.
-
---export on or off  
-
-Export will default to off if not declared.  If on it will export to tab 
-separated tables the tables being pushed or worksheets being pulled with 
-timestamps.
-
-_USAGE_
-}