my $mig_bin = "$FindBin::Bin/";
use Mig;
use strict;
-use Env qw(
- HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
- MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
- );
use Switch;
use Cwd 'abs_path';
use Pod::Usage;
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_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();
+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;';
if (defined $cmd_push) {
print "Pushing ";
+ my @tab_names;
if ($cmd_push eq 'all') {
- print "all tables.\n";
- $sql = 'SELECT table_name FROM gsheet_tracked_table;';
+ 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 @table_names, $row[0];
+ push @tab_names, $row[0];
}
} else {
- print "only table $cmd_push.\n";
+ print "only worksheet $cmd_push.\n";
if (!defined $cmd_push) { abort('command incomplete'); }
- push @table_names, $cmd_push;
+ push @tab_names, $cmd_push;
}
- foreach my $pull_tb (@table_names) {
+ 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);
- my $push_ws_name = get_worksheet_name($pull_tb,$dbh);
print "worksheetname: $push_ws_name\n";
my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
if (!defined $push_ws) { next; }
my $efile = $MIGGITDIR . $table . '_' . $date . '_' . $hms . '.tsv';
my @data;
my $record_count = 0;
- $dbh->do("COPY $table TO STDOUT CSV HEADER;");
+ $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) {
sub die_if_gsheet_tracked_table_does_not_exist {
if (!check_for_gsheet_tracked_table()) {
- die "Table $MIGSCHEMA.gsheet_tracked_table does not exist. Bailing...\n";
+ die "Table gsheet_tracked_table does not exist. Bailing...\n";
}
}
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 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 {
}
sub connect_gsheet {
- if (!defined $ENV{'CLIENTID'}) {
- exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
- print "Open Authentication settings were not loaded, please re-run.\n";
- }
- my $session_filename = $ENV{SESSIONFILE};
+
+ my ($clientid,$clientsecret,$sessionfile) = @_;
+
my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
- client_id => $ENV{CLIENTID},
- client_secret => $ENV{CLIENTSECRET},
+ client_id => $clientid,
+ client_secret => $clientsecret,
scope => ['http://spreadsheets.google.com/feeds/'],
redirect_uri => 'https://developers.google.com/oauthplayground',
);
- my $session = retrieve($session_filename);
+ if ($sessionfile =~ m/~/) {$sessionfile =~ s/~/$ENV{HOME}/; }
+ my $session = retrieve($sessionfile);
my $restored_token = Net::OAuth2::AccessToken->session_thaw(
$session,
auto_refresh => 1,