use Cwd 'abs_path';
use Pod::Usage;
use Data::Dumper;
+use DateTime;
pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
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 @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($MIGSCHEMA,$w,$dbh);
+ 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;
- #print Dumper($pull_ws->cells);
- #print Dumper(@content);
my @tab_headers = shift @content;
my $tab_headers_length = $#{ $tab_headers[0] };
my @pg_headers;
}
shift @content;
#todo: check for clean headers at some point ...
- truncate_table($MIGSCHEMA,$push_tb,$dbh);
+ 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($MIGSCHEMA,$push_tb,$dbh,'pull');
+ 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 tables.\n";
- $sql = 'SELECT table_name FROM ' . $MIGSCHEMA . '.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 $t (@table_names) {
- my $pull_tb = $MIGSCHEMA . "." . $t;;
- my @table_headers = get_pg_column_headers($t,$MIGSCHEMA);
- my $push_ws_name = get_worksheet_name($MIGSCHEMA,$t,$dbh);
+ 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;
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 ' . $MIGSCHEMA . '.gsheet_tracked_column WHERE table_id = (SELECT id FROM ' . $MIGSCHEMA . '.gsheet_tracked_table WHERE table_name = \'' . $t . '\')';
+ $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();
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);
+ print "Writing to $push_ws_name\n";
foreach my $fillsheet (@content) {
my $new_row = $push_ws->add_row (
$fillsheet
);
}
- timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
+ 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 $MIGSCHEMA.gsheet_tracked_table does not exist. Bailing...\n";
+ die "Table gsheet_tracked_table does not exist. Bailing...\n";
}
}
print "Erasing $ws_name.\n";
my @rows = $ws->rows;
- my $i;
- my $j = @rows;
- $j = int(($j / 2))-1;
- if ($j < 2) { $j = 2; }
- while ($j > 0) {
- #bodge until I figure out why google sheets is only deleting even numbered rows
- $i = 1;
- foreach my $row (@rows) {
- if ($i != 1) { $row->delete; }
- $i++;
- };
- $j--;
- };
+ splice @rows, 0, 1;
+ my $i = @rows;
+ while ($i > 0) {
+ my $row = pop @rows;
+ $row->delete;
+ $i--;
+ }
return;
}
}
sub get_table_name {
- my $migs = shift;
my $worksheet = shift;
my $dbh = shift;
- my $sql = 'SELECT table_name FROM ' . $migs . '.gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
+ 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;
return $table_name;
}
-sub get_worksheet_name {
- my $migs = shift;
- my $table = shift;
- my $dbh = shift;
-
- my $sql = 'SELECT tab_name FROM ' . $migs . '.gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
- 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 {
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 {
- my ($schema, $table, $dbh, $action) = @_;
+ my ($table, $dbh, $action) = @_;
my $column;
if ($action eq 'pull') { $column = 'last_pulled' }
else { $column = 'last_pushed' };
$dbh->do(qq/
- UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
+ UPDATE gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
/);
}
sub truncate_table {
- my $schema = shift;
my $table = shift;
my $dbh = shift;
$dbh->do(qq/
- TRUNCATE TABLE $schema.$table;;
+ TRUNCATE TABLE $table;;
/);
- print "Table $schema.$table truncated.\n";
+ print "Table $table truncated.\n";
}
sub abort {
projects / migration-tools.git / blobdiff