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