use DBI;
use FindBin;
use lib "$FindBin::Bin/";
+my $mig_bin = "$FindBin::Bin/";
use Mig;
use strict;
use Env qw(
use Cwd 'abs_path';
use Pod::Usage;
use Data::Dumper;
+use DateTime;
-my $mig_bin = "$FindBin::Bin/";
+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();
}
}
-abort('invalid value for --export') unless ($cmd_export eq 'on' or $cmd_export eq 'off');
-abort('must specify --push or --pull') unless (defined $cmd_push or defined $cmd_pull);
+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 $dbh = Mig::db_connect();
my $spreadsheet = connect_gsheet();
abort('could not connect to google sheet') unless (defined $spreadsheet);
-$sql = 'SELECT tab_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table;';
+$sql = 'SELECT tab_name FROM gsheet_tracked_table;';
$sth = $dbh->prepare($sql);
my $ra = $sth->execute();
while (my @row = $sth->fetchrow_array) {
my @rows = $pull_ws->rows;
my @content;
map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
- #print Dumper($pull_ws->cells);
- #print Dumper(@content);
my @tab_headers = shift @content;
my $tab_headers_length = $#{ $tab_headers[0] };
my @pg_headers;
insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
}
timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
+ if ($cmd_export == 1) { export_table($dbh,$push_tb); }
}
}
foreach my $row ( @{$grabhash} ) {
my $record = {};
for my $column ( sort keys %{ $row } ) {
- print Dumper(@$sheet_headers);
- print "column: $column\n";
+ #print Dumper(@$sheet_headers);
+ #print "column: $column\n";
my $clean_column = $column;
$clean_column =~ s/_//g;
- print "clean column: $clean_column\n";
if ( $column ~~ @$sheet_headers ) {
$record->{$clean_column} = $row->{$column};
}
push @content, $record;
}
- #print Dumper(@content);
foreach my $fillsheet (@content) {
my $new_row = $push_ws->add_row (
$fillsheet
);
}
timestamp($MIGSCHEMA,$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 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;
+
+}
+
+
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";
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";
+ #print "INSERT INTO $schema.$table $header_string VALUES $row_string\n";
$dbh->do(qq/
INSERT INTO $schema.$table $header_string VALUES $row_string ;
/);
-
- print "Row pushed to $table in schema $schema.\n";
}
sub timestamp {
sub abort {
my $msg = shift;
print STDERR "$0: $msg", "\n";
- print_usage();
exit 1;
}
return $spreadsheet;
}
-sub print_usage {
- print <<_USAGE_;
-
-Mig gsheet is for synchronizing data between migration postgresql tables
-and Google Sheets. It accepts the following command line arguments:
-
---push table_name or all
-
---pull worksheet_name or all
-Either --push or --pull must be present and it must be one or the other.
-
---export on or off
-
-Export will default to off if not declared. If on it will export to tab
-separated tables the tables being pushed or worksheets being pulled with
-timestamps.
-
-_USAGE_
-}