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
  26 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
  27
  28 Mig::die_if_no_env_migschema();
  29 die_if_gsheet_tracked_table_does_not_exist();
  30 die_if_gsheet_tracked_column_does_not_exist();
  31
  32 my $cmd_push;
  33 my $next_arg_is_push;
  34 my $cmd_pull;
  35 my $next_arg_is_pull;
  36 my @worksheet_names;
  37 my $cmd_export = 0;
  38 my @table_names;
  39 my $sql;
  40 my $sth;
  41 my @ws;
  42 my @tracked_ws_names;
  43
  44 foreach my $arg (@ARGV) {
  45     if ($arg eq '--push') {
  46         $next_arg_is_push = 1;
  47         next;
  48     }
  49     if ($next_arg_is_push) {
  50         $cmd_push = $arg;
  51         $next_arg_is_push = 0;
  52         next;
  53     }
  54     if ($arg eq '--pull') {
  55         $next_arg_is_pull = 1;
  56         next;
  57     }
  58     if ($next_arg_is_pull) {
  59         $cmd_pull = $arg;
  60         $next_arg_is_pull = 0;
  61         next;
  62     }
  63     if ($arg eq '--export') {
  64         $cmd_export = 1;
  65         next;
  66     }
  67 }
  68
  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     exit 1;
 359 }
 360
 361 sub connect_gsheet {
 362     if (!defined $ENV{'CLIENTID'}) {
 363         exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
 364         print "Open Authentication settings were not loaded, please re-run.\n";
 365     }
 366     my $session_filename = $ENV{SESSIONFILE};
 367     my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
 368         client_id => $ENV{CLIENTID},
 369         client_secret => $ENV{CLIENTSECRET},
 370         scope => ['http://spreadsheets.google.com/feeds/'],
 371         redirect_uri => 'https://developers.google.com/oauthplayground',
 372     );
 373     my $session = retrieve($session_filename);
 374     my $restored_token = Net::OAuth2::AccessToken->session_thaw(
 375         $session,
 376         auto_refresh => 1,
 377         profile => $oauth2->oauth2_webserver,
 378     );
 379     $oauth2->access_token($restored_token);
 380     my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
 381
 382     my $spreadsheet = $service->spreadsheet(
 383         {
 384             title => $MIGSCHEMA
 385         }
 386     );
 387     return $spreadsheet;
 388 }
 389
 390