mig-bin/mig-gsheet

   1 #!/usr/bin/perl
   2
   3 use Env qw(
   4     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
   5     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
   6 );
   7 use Net::Google::Spreadsheets;
   8 use Net::Google::DataAPI::Auth::OAuth2;
   9 use Net::OAuth2::AccessToken;
  10 use Storable;
  11 use DBI;
  12 use FindBin;
  13 use lib "$FindBin::Bin/";
  14 my $mig_bin = "$FindBin::Bin/";
  15 use Mig;
  16 use strict;
  17 use Env qw(
  18     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
  19     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
  20     );
  21 use Switch;
  22 use Cwd 'abs_path';
  23 use Pod::Usage;
  24 use Data::Dumper;
  25 use DateTime;
  26
  27 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
  28
  29 Mig::die_if_no_env_migschema();
  30 die_if_gsheet_tracked_table_does_not_exist();
  31 die_if_gsheet_tracked_column_does_not_exist();
  32
  33 my $cmd_push;
  34 my $next_arg_is_push;
  35 my $cmd_pull;
  36 my $next_arg_is_pull;
  37 my @worksheet_names;
  38 my $cmd_export = 0;
  39 my @table_names;
  40 my $sql;
  41 my $sth;
  42 my @ws;
  43 my @tracked_ws_names;
  44
  45 foreach my $arg (@ARGV) {
  46     if ($arg eq '--push') {
  47         $next_arg_is_push = 1;
  48         next;
  49     }
  50     if ($next_arg_is_push) {
  51         $cmd_push = $arg;
  52         $next_arg_is_push = 0;
  53         next;
  54     }
  55     if ($arg eq '--pull') {
  56         $next_arg_is_pull = 1;
  57         next;
  58     }
  59     if ($next_arg_is_pull) {
  60         $cmd_pull = $arg;
  61         $next_arg_is_pull = 0;
  62         next;
  63     }
  64     if ($arg eq '--export') {
  65         $cmd_export = 1;
  66         next;
  67     }
  68 }
  69
  70 abort('must specify --push (db->worksheets) or --pull (worksheets->db)') unless (defined $cmd_push or defined $cmd_pull);
  71 if (defined $cmd_push and defined $cmd_pull) { abort('you can not specify both a --push and --pull on the same command'); }
  72
  73 my $dbh = Mig::db_connect();
  74 my $spreadsheet = connect_gsheet();
  75 abort('could not connect to google sheet') unless (defined $spreadsheet);
  76
  77 $sql = 'SELECT tab_name FROM gsheet_tracked_table;';
  78 $sth = $dbh->prepare($sql);
  79 my $ra = $sth->execute();
  80 while (my @row = $sth->fetchrow_array) {
  81     push @tracked_ws_names, $row[0];
  82 }
  83
  84 if (defined $cmd_pull) {
  85     print "Pulling ";
  86     if ($cmd_pull eq 'all') {
  87         print "all worksheets.\n";
  88         @ws = $spreadsheet->worksheets;
  89         foreach my $wsn (@ws) { push @worksheet_names, $wsn->title; }
  90     } else {
  91         print "only worksheet $cmd_pull.\n";
  92         if (!defined $cmd_pull) { abort('command incomplete'); }
  93         push @worksheet_names, $cmd_pull;
  94     }
  95     my @m = array_match(\@worksheet_names,\@tracked_ws_names);
  96     foreach my $w (@m) {
  97         my $pull_ws = $spreadsheet->worksheet( {title => $w} );
  98         my $push_tb = get_table_name($MIGSCHEMA,$w,$dbh);
  99         my @rows = $pull_ws->rows;
 100         my @content;
 101         map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
 102         my @tab_headers = shift @content;
 103         my $tab_headers_length = $#{ $tab_headers[0] };
 104         my @pg_headers;
 105         for my $i ( 0 .. $tab_headers_length ) {
 106             push @pg_headers, $tab_headers[0][$i];
 107         }
 108         shift @content;
 109         #todo: check for clean headers at some point ...
 110         truncate_table($MIGSCHEMA,$push_tb,$dbh);
 111         print "Inserting from $w to $push_tb.\n";
 112         for my $j (@content) {
 113             insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
 114         }
 115         timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
 116         if ($cmd_export == 1) { export_table($dbh,$push_tb); }
 117     }
 118 }
 119
 120 if (defined $cmd_push) {
 121     print "Pushing ";
 122     if ($cmd_push eq 'all') {
 123         print "all tables.\n";
 124         $sql = 'SELECT table_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table';
 125         $sth = $dbh->prepare($sql);
 126         $ra = $sth->execute();
 127         while (my @row = $sth->fetchrow_array) {
 128             push @table_names, $row[0];
 129         }
 130     } else {
 131         print "only table $cmd_push.\n";
 132         if (!defined $cmd_push) { abort('command incomplete'); }
 133         push @table_names, $cmd_push;
 134     }
 135     foreach my $t (@table_names) {
 136         my $pull_tb = $MIGSCHEMA . "." . $t;;
 137         my @table_headers = get_pg_column_headers($t,$MIGSCHEMA);
 138         my $push_ws_name = get_worksheet_name($MIGSCHEMA,$t,$dbh);
 139         my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
 140         if (!defined $push_ws) { next; }
 141         my @rows;
 142         my $i = 0;
 143         foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }
 144         $sql = "SELECT * FROM $pull_tb;";
 145         $sth = $dbh->prepare($sql);
 146         $sth->execute();
 147         my $grabhash = $sth->fetchall_arrayref({});
 148         erase_sheet($push_ws,$push_ws_name);
 149
 150         #get from postgres the headers to use in the sheet from tracked columns
 151         $sql = 'SELECT column_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_column WHERE table_id = (SELECT id FROM ' . $MIGSCHEMA . '.gsheet_tracked_table WHERE table_name = \'' . $t . '\')';
 152         $sth = $dbh->prepare($sql);
 153         $sth->execute();
 154         my $sheet_headers = $sth->fetchall_arrayref();
 155         my $sheet_headers_length = @$sheet_headers;
 156         #now I need to do new rows using those headers
 157         my @content;
 158         foreach my $row ( @{$grabhash} ) {
 159             my $record = {};
 160             for my $column ( sort keys %{ $row } ) {
 161                 #print Dumper(@$sheet_headers);
 162                 #print "column: $column\n";
 163                 my $clean_column = $column;
 164                 $clean_column =~ s/_//g;
 165                 if ( $column ~~ @$sheet_headers ) {
 166                     $record->{$clean_column} = $row->{$column};
 167                 }
 168             }
 169             push @content, $record;
 170         }
 171
 172         foreach my $fillsheet (@content) {
 173             my $new_row = $push_ws->add_row (
 174                 $fillsheet
 175             );
 176         }
 177         timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
 178         if ($cmd_export == 1) { export_table($dbh,$pull_tb); }
 179     }
 180 }
 181
 182 sub export_table {
 183     my $dbh = shift;
 184     my $table = shift;
 185
 186     my $dt = DateTime->now;
 187     my $date = $dt->ymd;
 188     my $hms = $dt->hms;
 189     my $efile = $MIGGITDIR . $table . '_' . $date . '_' . $hms . '.tsv';
 190     my @data;
 191     my $record_count = 0;
 192     $dbh->do("COPY $table TO STDOUT CSV HEADER;");
 193     1 while $dbh->pg_getcopydata(\$data[$record_count++]) >= 0;
 194     open (my $eout, '>', $efile) or abort("Could NOT open $efile.");
 195     foreach my $d (@data) {
 196         print $eout $d;
 197     }
 198     print "$efile written.\n";
 199     close $eout;
 200
 201 }
 202
 203
 204 sub die_if_gsheet_tracked_table_does_not_exist {
 205     if (!check_for_gsheet_tracked_table()) {
 206         die "Table $MIGSCHEMA.gsheet_tracked_table does not exist.  Bailing...\n";
 207     }
 208 }
 209
 210 sub array_match {
 211     my ($xa,$xb) = @_;
 212     my @a = @{ $xa };
 213     my @b = @{ $xb };
 214     my @r;
 215
 216     foreach my $av (@a) {
 217         foreach my $bv (@b) {
 218             if ($av eq $bv) { push @r, $bv; }
 219         }
 220     }
 221     return @r;
 222 }
 223
 224 sub get_pg_column_headers {
 225     my $table_name = shift;
 226     my $schema_name = shift;
 227     my @headers;
 228     my $dbh = Mig::db_connect();
 229     $sql = 'SELECT column_name FROM information_schema.columns WHERE table_schema = ' . $dbh->quote( $schema_name ) . ' AND table_name = ' . $dbh->quote( $table_name ) . ';';
 230     $sth = $dbh->prepare($sql);
 231     $ra = $sth->execute();
 232     while (my @row = $sth->fetchrow_array) {
 233         push @headers, $row[0];
 234     }
 235     return @headers;
 236 }
 237
 238 sub erase_sheet {
 239     my $ws = shift;
 240     my $ws_name = shift;
 241
 242     print "Erasing $ws_name.\n";
 243     my @rows = $ws->rows;
 244     my $i;
 245     my $j = @rows;
 246     $j = int(($j / 2))-1;
 247     if ($j < 2) { $j = 2; }
 248     while ($j > 0) {
 249         #bodge until I figure out why google sheets is only deleting even numbered rows
 250         $i = 1;
 251         foreach my $row (@rows) {
 252             if ($i != 1) { $row->delete; }
 253             $i++;
 254         };
 255         $j--;
 256     };
 257     return;
 258 }
 259
 260 sub check_for_gsheet_tracked_table {
 261     my $dbh = Mig::db_connect();
 262     my $sth = $dbh->prepare("
 263         SELECT EXISTS(
 264             SELECT 1
 265             FROM information_schema.tables
 266             WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
 267             AND table_name = 'gsheet_tracked_table'
 268         );"
 269     );
 270     my $rv = $sth->execute()
 271         || die "Error checking for table (tracked_gsheet_table): $!";
 272     my @cols = $sth->fetchrow_array;
 273     $sth->finish;
 274     Mig::db_disconnect($dbh);
 275     return $cols[0];
 276 }
 277
 278 sub die_if_gsheet_tracked_column_does_not_exist {
 279     if (!check_for_gsheet_tracked_column()) {
 280         die "Table $MIGSCHEMA.gsheet_tracked_column does not exist.  Bailing...\n";
 281     }
 282 }
 283
 284 sub get_table_name {
 285     my $migs = shift;
 286     my $worksheet = shift;
 287     my $dbh = shift;
 288
 289     my $sql = 'SELECT table_name FROM ' . $migs . '.gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
 290     my $sth = $dbh->prepare($sql);
 291     my $xs = $sth->execute();
 292     my $table_name;
 293     while (my @row = $sth->fetchrow_array) {
 294         $table_name = $row[0];
 295     }
 296
 297     return $table_name;
 298 }
 299
 300 sub get_worksheet_name {
 301     my $migs = shift;
 302     my $table = shift;
 303     my $dbh = shift;
 304
 305     my $sql = 'SELECT tab_name FROM ' . $migs . '.gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
 306     my $sth = $dbh->prepare($sql);
 307     my $xs = $sth->execute();
 308     my $worksheet_name;
 309     while (my @row = $sth->fetchrow_array) {
 310         $worksheet_name = $row[0];
 311     }
 312
 313     return $worksheet_name;
 314 }
 315
 316
 317 sub check_for_gsheet_tracked_column {
 318     my $dbh = Mig::db_connect();
 319     my $sth = $dbh->prepare("
 320         SELECT EXISTS(
 321             SELECT 1
 322             FROM information_schema.tables
 323             WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
 324             AND table_name = 'gsheet_tracked_column'
 325         );"
 326     );
 327     my $rv = $sth->execute()
 328         || die "Error checking for table (gsheet_tracked_column): $!";
 329     my @cols = $sth->fetchrow_array;
 330     $sth->finish;
 331     Mig::db_disconnect($dbh);
 332     return $cols[0];
 333 }
 334
 335 sub insert_row {
 336     my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
 337     my @headers = @{ $headers_ref };
 338     my @row_data = @{ $row_ref };
 339
 340     my $header_string = '(' . join(",", @headers) . ')';
 341     map {s/\'/\'\'/g; } @row_data;
 342     my $row_string = '(' . join(",", map {qq/'$_'/} @row_data) . ')';
 343     #print "INSERT INTO $schema.$table $header_string VALUES $row_string\n";
 344     $dbh->do(qq/
 345         INSERT INTO $schema.$table $header_string VALUES $row_string ;
 346     /);
 347 }
 348
 349 sub timestamp {
 350     my ($schema, $table, $dbh, $action) = @_;
 351
 352     my $column;
 353     if ($action eq 'pull') { $column = 'last_pulled' }
 354         else { $column = 'last_pushed' };
 355
 356     $dbh->do(qq/
 357         UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
 358     /);
 359
 360 }
 361
 362
 363 sub truncate_table {
 364     my $schema = shift;
 365     my $table = shift;
 366     my $dbh = shift;
 367
 368     $dbh->do(qq/
 369         TRUNCATE TABLE $schema.$table;;
 370     /);
 371     print "Table $schema.$table truncated.\n";
 372 }
 373
 374 sub abort {
 375     my $msg = shift;
 376     print STDERR "$0: $msg", "\n";
 377     exit 1;
 378 }
 379
 380 sub connect_gsheet {
 381     if (!defined $ENV{'CLIENTID'}) {
 382         exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
 383         print "Open Authentication settings were not loaded, please re-run.\n";
 384     }
 385     my $session_filename = $ENV{SESSIONFILE};
 386     my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
 387         client_id => $ENV{CLIENTID},
 388         client_secret => $ENV{CLIENTSECRET},
 389         scope => ['http://spreadsheets.google.com/feeds/'],
 390         redirect_uri => 'https://developers.google.com/oauthplayground',
 391     );
 392     my $session = retrieve($session_filename);
 393     my $restored_token = Net::OAuth2::AccessToken->session_thaw(
 394         $session,
 395         auto_refresh => 1,
 396         profile => $oauth2->oauth2_webserver,
 397     );
 398     $oauth2->access_token($restored_token);
 399     my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
 400
 401     my $spreadsheet = $service->spreadsheet(
 402         {
 403             title => $MIGSCHEMA
 404         }
 405     );
 406     return $spreadsheet;
 407 }
 408
 409