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