--- /dev/null
+#!/usr/bin/perl
+
+use Env qw(
+ HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
+ MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
+);
+use Net::Google::Spreadsheets;
+use Net::Google::DataAPI::Auth::OAuth2;
+use Net::OAuth2::AccessToken;
+use Storable;
+use DBI;
+use FindBin;
+use lib "$FindBin::Bin/";
+my $mig_bin = "$FindBin::Bin/";
+use Mig;
+use strict;
+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';
+
+Mig::die_if_no_env_migschema();
+die_if_gsheet_tracked_table_does_not_exist();
+die_if_gsheet_tracked_column_does_not_exist();
+
+my $cmd_push;
+my $next_arg_is_push;
+my $cmd_pull;
+my $next_arg_is_pull;
+my @worksheet_names;
+my $cmd_export = 0;
+my @table_names;
+my $sql;
+my $sth;
+my @ws;
+my @tracked_ws_names;
+my $authfile = $ENV{HOME} . '/.mig/oauth.env';
+my $next_arg_is_authfile;
+
+foreach my $arg (@ARGV) {
+ if ($arg eq '--push') {
+ $next_arg_is_push = 1;
+ next;
+ }
+ if ($next_arg_is_push) {
+ $cmd_push = $arg;
+ $next_arg_is_push = 0;
+ next;
+ }
+ if ($arg eq '--pull') {
+ $next_arg_is_pull = 1;
+ next;
+ }
+ if ($next_arg_is_pull) {
+ $cmd_pull = $arg;
+ $next_arg_is_pull = 0;
+ next;
+ }
+ if ($arg eq '--authfile') {
+ $next_arg_is_authfile = 1;
+ next;
+ }
+ if ($next_arg_is_authfile) {
+ $authfile = $arg;
+ $next_arg_is_authfile = 0;
+ next;
+ }
+ if ($arg eq '--export') {
+ $cmd_export = 1;
+ next;
+ }
+}
+
+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 $clientid;
+my $clientsecret;
+my $sessionfile;
+
+open (my $fh, '<', $authfile) or abort("Could not open $authfile");
+while (my $var = <$fh>) {
+ chomp $var;
+ my ($var1, $var2) = split /=/,$var;
+ if ($var1 eq 'CLIENTID') { $clientid = $var2; }
+ if ($var1 eq 'CLIENTSECRET') { $clientsecret = $var2; }
+ if ($var1 eq 'SESSIONFILE') { $sessionfile = $var2; }
+}
+my $dbh = Mig::db_connect();
+my $spreadsheet = connect_gsheet($clientid,$clientsecret,$sessionfile);
+abort('could not connect to google sheet') unless (defined $spreadsheet);
+
+$sql = 'SELECT tab_name FROM gsheet_tracked_table;';
+$sth = $dbh->prepare($sql);
+my $ra = $sth->execute();
+while (my @row = $sth->fetchrow_array) {
+ push @tracked_ws_names, $row[0];
+}
+
+if (defined $cmd_pull) {
+ print "Pulling ";
+ if ($cmd_pull eq 'all') {
+ print "all worksheets.\n";
+ @ws = $spreadsheet->worksheets;
+ foreach my $wsn (@ws) { push @worksheet_names, $wsn->title; }
+ } else {
+ print "only worksheet $cmd_pull.\n";
+ if (!defined $cmd_pull) { abort('command incomplete'); }
+ push @worksheet_names, $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($w,$dbh);
+ my @rows = $pull_ws->rows;
+ my @content;
+ map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
+ my @tab_headers = shift @content;
+ my $tab_headers_length = $#{ $tab_headers[0] };
+ my @pg_headers;
+ for my $i ( 0 .. $tab_headers_length ) {
+ push @pg_headers, $tab_headers[0][$i];
+ }
+ shift @content;
+ #todo: check for clean headers at some point ...
+ 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($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 worksheets.\n";
+ $sql = 'SELECT tab_name FROM gsheet_tracked_table;';
+ $sth = $dbh->prepare($sql);
+ $ra = $sth->execute();
+ while (my @row = $sth->fetchrow_array) {
+ push @tab_names, $row[0];
+ }
+ } else {
+ print "only worksheet $cmd_push.\n";
+ if (!defined $cmd_push) { abort('command incomplete'); }
+ push @tab_names, $cmd_push;
+ }
+ 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;
+ my $i = 0;
+ foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }
+ $sql = "SELECT * FROM $pull_tb;";
+ $sth = $dbh->prepare($sql);
+ $sth->execute();
+ my $grabhash = $sth->fetchall_arrayref({});
+ 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 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();
+ my $sheet_headers_length = @$sheet_headers;
+ #now I need to do new rows using those headers
+ my @content;
+ foreach my $row ( @{$grabhash} ) {
+ my $record = {};
+ for my $column ( sort keys %{ $row } ) {
+ #print Dumper(@$sheet_headers);
+ #print "column: $column\n";
+ my $clean_column = $column;
+ $clean_column =~ s/_//g;
+ if ( $column ~~ @$sheet_headers ) {
+ $record->{$clean_column} = $row->{$column};
+ }
+ }
+ push @content, $record;
+ }
+ print "Writing to $push_ws_name\n";
+ foreach my $fillsheet (@content) {
+ my $new_row = $push_ws->add_row (
+ $fillsheet
+ );
+ }
+ 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 DELIMITER E'\t' 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 gsheet_tracked_table does not exist. Bailing...\n";
+ }
+}
+
+sub array_match {
+ my ($xa,$xb) = @_;
+ my @a = @{ $xa };
+ my @b = @{ $xb };
+ my @r;
+
+ foreach my $av (@a) {
+ foreach my $bv (@b) {
+ if ($av eq $bv) { push @r, $bv; }
+ }
+ }
+ return @r;
+}
+
+sub get_pg_column_headers {
+ my $table_name = shift;
+ my $schema_name = shift;
+ my @headers;
+ my $dbh = Mig::db_connect();
+ $sql = 'SELECT column_name FROM information_schema.columns WHERE table_schema = ' . $dbh->quote( $schema_name ) . ' AND table_name = ' . $dbh->quote( $table_name ) . ';';
+ $sth = $dbh->prepare($sql);
+ $ra = $sth->execute();
+ while (my @row = $sth->fetchrow_array) {
+ push @headers, $row[0];
+ }
+ return @headers;
+}
+
+sub erase_sheet {
+ my $ws = shift;
+ my $ws_name = shift;
+
+ print "Erasing $ws_name.\n";
+ my @rows = $ws->rows;
+ splice @rows, 0, 1;
+ my $i = @rows;
+ while ($i > 0) {
+ my $row = pop @rows;
+ $row->delete;
+ $i--;
+ }
+ return;
+}
+
+sub check_for_gsheet_tracked_table {
+ my $dbh = Mig::db_connect();
+ my $sth = $dbh->prepare("
+ SELECT EXISTS(
+ SELECT 1
+ FROM information_schema.tables
+ WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
+ AND table_name = 'gsheet_tracked_table'
+ );"
+ );
+ my $rv = $sth->execute()
+ || die "Error checking for table (tracked_gsheet_table): $!";
+ my @cols = $sth->fetchrow_array;
+ $sth->finish;
+ Mig::db_disconnect($dbh);
+ return $cols[0];
+}
+
+sub die_if_gsheet_tracked_column_does_not_exist {
+ if (!check_for_gsheet_tracked_column()) {
+ die "Table $MIGSCHEMA.gsheet_tracked_column does not exist. Bailing...\n";
+ }
+}
+
+sub get_table_name {
+ my $worksheet = shift;
+ my $dbh = shift;
+
+ 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;
+ while (my @row = $sth->fetchrow_array) {
+ $table_name = $row[0];
+ }
+
+ return $table_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 {
+ my $dbh = Mig::db_connect();
+ my $sth = $dbh->prepare("
+ SELECT EXISTS(
+ SELECT 1
+ FROM information_schema.tables
+ WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
+ AND table_name = 'gsheet_tracked_column'
+ );"
+ );
+ my $rv = $sth->execute()
+ || die "Error checking for table (gsheet_tracked_column): $!";
+ my @cols = $sth->fetchrow_array;
+ $sth->finish;
+ Mig::db_disconnect($dbh);
+ return $cols[0];
+}
+
+sub insert_row {
+ my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
+ my @headers = @{ $headers_ref };
+ my @row_data = @{ $row_ref };
+
+ 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";
+ $dbh->do(qq/
+ INSERT INTO $schema.$table $header_string VALUES $row_string ;
+ /);
+}
+
+sub timestamp {
+ my ($table, $dbh, $action) = @_;
+
+ my $column;
+ if ($action eq 'pull') { $column = 'last_pulled' }
+ else { $column = 'last_pushed' };
+
+ $dbh->do(qq/
+ UPDATE gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
+ /);
+
+}
+
+
+sub truncate_table {
+ my $table = shift;
+ my $dbh = shift;
+
+ $dbh->do(qq/
+ TRUNCATE TABLE $table;;
+ /);
+ print "Table $table truncated.\n";
+}
+
+sub abort {
+ my $msg = shift;
+ print STDERR "$0: $msg", "\n";
+ exit 1;
+}
+
+sub connect_gsheet {
+
+ my ($clientid,$clientsecret,$sessionfile) = @_;
+
+ my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
+ client_id => $clientid,
+ client_secret => $clientsecret,
+ scope => ['http://spreadsheets.google.com/feeds/'],
+ redirect_uri => 'https://developers.google.com/oauthplayground',
+ );
+ if ($sessionfile =~ m/~/) {$sessionfile =~ s/~/$ENV{HOME}/; }
+ my $session = retrieve($sessionfile);
+ my $restored_token = Net::OAuth2::AccessToken->session_thaw(
+ $session,
+ auto_refresh => 1,
+ profile => $oauth2->oauth2_webserver,
+ );
+ $oauth2->access_token($restored_token);
+ my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
+
+ my $spreadsheet = $service->spreadsheet(
+ {
+ title => $MIGSCHEMA
+ }
+ );
+ return $spreadsheet;
+}
+
+
projects / migration-tools.git / blobdiff