X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-bin%2Fmig-gsheet;h=a4e089c24ad68c57ae6fc0b1d0c698208d11a965;hp=0bf3804b37dd8d5a5bf8b59b9b5529fe62d3a237;hb=2f34c8427944b6572c9e0c695b2f241b4f10cb3a;hpb=10de3f4ac4bda540279413d818b15740ade85788

diff --git a/mig-bin/mig-gsheet b/mig-bin/mig-gsheet
index 0bf3804..a4e089c 100755
--- a/mig-bin/mig-gsheet
+++ b/mig-bin/mig-gsheet
@@ -22,6 +22,7 @@ use Switch;
 use Cwd 'abs_path';
 use Pod::Usage;
 use Data::Dumper;
+use DateTime;
 
 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
 
@@ -66,14 +67,14 @@ foreach my $arg (@ARGV) {
     }
 }
 
-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) {
@@ -94,12 +95,10 @@ if (defined $cmd_pull) {
     my @m = array_match(\@worksheet_names,\@tracked_ws_names);
     foreach my $w (@m) { 
         my $pull_ws = $spreadsheet->worksheet( {title => $w} ); 
-        my $push_tb = get_table_name($MIGSCHEMA,$w,$dbh);
+        my $push_tb = get_table_name($w,$dbh);
         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;
@@ -108,34 +107,36 @@ if (defined $cmd_pull) {
         }
         shift @content;
         #todo: check for clean headers at some point ...
-        truncate_table($MIGSCHEMA,$push_tb,$dbh);
+        truncate_table($push_tb,$dbh);
         print "Inserting from $w to $push_tb.\n";
         for my $j (@content) {
             insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
         }
-        timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
+        timestamp($push_tb,$dbh,'pull');
+        if ($cmd_export == 1) { export_table($dbh,$push_tb); }
     }
 }
 
 if (defined $cmd_push) {
     print "Pushing ";
+    my @tab_names;
     if ($cmd_push eq 'all') {
-        print "all tables.\n";
-        $sql = 'SELECT table_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table';
+        print "all worksheets.\n";
+        $sql = 'SELECT tab_name FROM gsheet_tracked_table;';
         $sth = $dbh->prepare($sql);
         $ra = $sth->execute();
         while (my @row = $sth->fetchrow_array) {
-            push @table_names, $row[0];
+            push @tab_names, $row[0];
         }
     } else {
-        print "only table $cmd_push.\n";
+        print "only worksheet $cmd_push.\n";
         if (!defined $cmd_push) { abort('command incomplete'); }
-        push @table_names, $cmd_push;
+        push @tab_names, $cmd_push;
     }
-    foreach my $t (@table_names) {
-        my $pull_tb = $MIGSCHEMA . "." . $t;;
-        my @table_headers = get_pg_column_headers($t,$MIGSCHEMA);
-        my $push_ws_name = get_worksheet_name($MIGSCHEMA,$t,$dbh);
+    foreach my $push_ws_name (@tab_names) {
+        my $pull_tb = get_table_name($push_ws_name,$dbh);
+        my @table_headers = get_pg_column_headers($pull_tb,$MIGSCHEMA);
+        print "worksheetname: $push_ws_name\n";
         my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
         if (!defined $push_ws) { next; }
         my @rows;
@@ -148,7 +149,7 @@ if (defined $cmd_push) {
         erase_sheet($push_ws,$push_ws_name);
 
         #get from postgres the headers to use in the sheet from tracked columns
-        $sql = 'SELECT column_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_column WHERE table_id = (SELECT id FROM ' . $MIGSCHEMA . '.gsheet_tracked_table WHERE table_name = \'' . $t . '\')';
+        $sql = 'SELECT column_name FROM gsheet_tracked_column WHERE table_id = (SELECT id FROM gsheet_tracked_table WHERE table_name = \'' . $pull_tb . '\')';
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my $sheet_headers = $sth->fetchall_arrayref();
@@ -158,28 +159,48 @@ 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}; 
                 }
             }
             push @content, $record;
         }
-
-        #print Dumper(@content); 
+        print "Writing to $push_ws_name\n";
         foreach my $fillsheet (@content) {
             my $new_row = $push_ws->add_row (
                 $fillsheet
             );
         }
-        timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
+        timestamp($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;
+    return;
+}
+
 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";
@@ -220,19 +241,13 @@ sub erase_sheet {
 
     print "Erasing $ws_name.\n";
     my @rows = $ws->rows;
-    my $i;
-    my $j = @rows;
-    $j = int(($j / 2))-1;
-    if ($j < 2) { $j = 2; }
-    while ($j > 0) {
-        #bodge until I figure out why google sheets is only deleting even numbered rows
-        $i = 1;
-        foreach my $row (@rows) {
-            if ($i != 1) { $row->delete; }
-            $i++;
-        };
-        $j--;
-    };
+    splice @rows, 0, 1;
+    my $i = @rows;
+    while ($i > 0) {
+        my $row = pop @rows;
+        $row->delete;
+        $i--;
+    }
     return;
 }
 
@@ -261,11 +276,10 @@ sub die_if_gsheet_tracked_column_does_not_exist {
 }
 
 sub get_table_name {
-    my $migs = shift;
     my $worksheet = shift;
     my $dbh = shift;
 
-    my $sql = 'SELECT table_name FROM ' . $migs . '.gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
+    my $sql = 'SELECT table_name FROM gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
     my $sth = $dbh->prepare($sql);
     my $xs = $sth->execute();
     my $table_name;
@@ -276,21 +290,21 @@ sub get_table_name {
     return $table_name;
 }
 
-sub get_worksheet_name {
-    my $migs = shift;
-    my $table = shift;
-    my $dbh = shift;
-
-    my $sql = 'SELECT tab_name FROM ' . $migs . '.gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
-    my $sth = $dbh->prepare($sql);
-    my $xs = $sth->execute();
-    my $worksheet_name;
-    while (my @row = $sth->fetchrow_array) {
-        $worksheet_name = $row[0];
-    }
-
-    return $worksheet_name;
-}
+#sub get_worksheet_name {
+#    my $table = shift;
+#    my $dbh = shift;
+#
+#    my $sql = 'SELECT tab_name FROM gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
+#    print "$sql \n";
+#    my $sth = $dbh->prepare($sql);
+#    my $xs = $sth->execute();
+#    my $worksheet_name;
+#    while (my @row = $sth->fetchrow_array) {
+#        $worksheet_name = $row[0];
+#    }
+#
+#    return $worksheet_name;
+#}
 
 
 sub check_for_gsheet_tracked_column {
@@ -319,37 +333,34 @@ 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 {
-    my ($schema, $table, $dbh, $action) = @_;
+    my ($table, $dbh, $action) = @_;
 
     my $column;
     if ($action eq 'pull') { $column = 'last_pulled' }
         else { $column = 'last_pushed' }; 
 
     $dbh->do(qq/
-        UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
+        UPDATE gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
     /);
 
 }
 
 
 sub truncate_table {
-    my $schema = shift;
     my $table = shift;
     my $dbh = shift;
 
     $dbh->do(qq/
-        TRUNCATE TABLE $schema.$table;;
+        TRUNCATE TABLE $table;;
     /);
-    print "Table $schema.$table truncated.\n";
+    print "Table $table truncated.\n";
 }
 
 sub abort {