4 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
5 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
7 use Net::Google::Spreadsheets;
8 use Net::Google::DataAPI::Auth::OAuth2;
9 use Net::OAuth2::AccessToken;
13 use lib "$FindBin::Bin/";
14 my $mig_bin = "$FindBin::Bin/";
23 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
25 EMig::die_if_no_env_migschema();
26 die_if_gsheet_tracked_table_does_not_exist();
27 die_if_gsheet_tracked_column_does_not_exist();
40 my $authfile = $ENV{HOME} . '/.emig/oauth.env';
41 my $next_arg_is_authfile;
43 foreach my $arg (@ARGV) {
44 if ($arg eq '--push') {
45 $next_arg_is_push = 1;
48 if ($next_arg_is_push) {
50 $next_arg_is_push = 0;
53 if ($arg eq '--pull') {
54 $next_arg_is_pull = 1;
57 if ($next_arg_is_pull) {
59 $next_arg_is_pull = 0;
62 if ($arg eq '--authfile') {
63 $next_arg_is_authfile = 1;
66 if ($next_arg_is_authfile) {
68 $next_arg_is_authfile = 0;
71 if ($arg eq '--export') {
77 abort('must specify --push (db->worksheets) or --pull (worksheets->db)') unless (defined $cmd_push or defined $cmd_pull);
78 if (defined $cmd_push and defined $cmd_pull) { abort('you can not specify both a --push and --pull on the same command'); }
84 open (my $fh, '<', $authfile) or abort("Could not open $authfile");
85 while (my $var = <$fh>) {
87 my ($var1, $var2) = split /=/,$var;
88 if ($var1 eq 'CLIENTID') { $clientid = $var2; }
89 if ($var1 eq 'CLIENTSECRET') { $clientsecret = $var2; }
90 if ($var1 eq 'SESSIONFILE') { $sessionfile = $var2; }
92 my $dbh = EMig::db_connect();
93 my $spreadsheet = connect_gsheet($clientid,$clientsecret,$sessionfile);
94 abort('could not connect to google sheet') unless (defined $spreadsheet);
96 $sql = 'SELECT tab_name FROM gsheet_tracked_table;';
97 $sth = $dbh->prepare($sql);
98 my $ra = $sth->execute();
99 while (my @row = $sth->fetchrow_array) {
100 push @tracked_ws_names, $row[0];
103 if (defined $cmd_pull) {
105 if ($cmd_pull eq 'all') {
106 print "all worksheets.\n";
107 @ws = $spreadsheet->worksheets;
108 foreach my $wsn (@ws) { push @worksheet_names, $wsn->title; }
110 print "only worksheet $cmd_pull.\n";
111 if (!defined $cmd_pull) { abort('command incomplete'); }
112 push @worksheet_names, $cmd_pull;
114 my @m = array_match(\@worksheet_names,\@tracked_ws_names);
116 my $pull_ws = $spreadsheet->worksheet( {title => $w} );
117 my $push_tb = get_table_name($w,$dbh);
118 my @rows = $pull_ws->rows;
120 map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
121 my @tab_headers = shift @content;
122 my $tab_headers_length = $#{ $tab_headers[0] };
124 for my $i ( 0 .. $tab_headers_length ) {
125 push @pg_headers, $tab_headers[0][$i];
128 #todo: check for clean headers at some point ...
129 truncate_table($push_tb,$dbh);
130 print "Inserting from $w to $push_tb.\n";
131 for my $j (@content) {
132 insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
134 timestamp($push_tb,$dbh,'pull');
135 if ($cmd_export == 1) { export_table($dbh,$push_tb); }
139 if (defined $cmd_push) {
142 if ($cmd_push eq 'all') {
143 print "all worksheets.\n";
144 $sql = 'SELECT tab_name FROM gsheet_tracked_table;';
145 $sth = $dbh->prepare($sql);
146 $ra = $sth->execute();
147 while (my @row = $sth->fetchrow_array) {
148 push @tab_names, $row[0];
151 print "only worksheet $cmd_push.\n";
152 if (!defined $cmd_push) { abort('command incomplete'); }
153 push @tab_names, $cmd_push;
155 foreach my $push_ws_name (@tab_names) {
156 my $pull_tb = get_table_name($push_ws_name,$dbh);
157 my @table_headers = get_pg_column_headers($pull_tb,$MIGSCHEMA);
158 print "worksheetname: $push_ws_name\n";
159 my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
160 if (!defined $push_ws) { next; }
163 foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }
164 $sql = "SELECT * FROM $pull_tb;";
165 $sth = $dbh->prepare($sql);
167 my $grabhash = $sth->fetchall_arrayref({});
168 erase_sheet($push_ws,$push_ws_name);
170 #get from postgres the headers to use in the sheet from tracked columns
171 $sql = 'SELECT column_name FROM gsheet_tracked_column WHERE table_id = (SELECT id FROM gsheet_tracked_table WHERE table_name = \'' . $pull_tb . '\')';
172 $sth = $dbh->prepare($sql);
174 my $sheet_headers = $sth->fetchall_arrayref();
175 my $sheet_headers_length = @$sheet_headers;
176 #now I need to do new rows using those headers
178 foreach my $row ( @{$grabhash} ) {
180 for my $column ( sort keys %{ $row } ) {
181 #print Dumper(@$sheet_headers);
182 #print "column: $column\n";
183 my $clean_column = $column;
184 $clean_column =~ s/_//g;
185 if ( $column ~~ @$sheet_headers ) {
186 $record->{$clean_column} = $row->{$column};
189 push @content, $record;
191 print "Writing to $push_ws_name\n";
192 foreach my $fillsheet (@content) {
193 my $new_row = $push_ws->add_row (
197 timestamp($pull_tb,$dbh,'push');
198 if ($cmd_export == 1) { export_table($dbh,$pull_tb); }
206 my $dt = DateTime->now;
209 my $efile = $MIGGITDIR . $table . '_' . $date . '_' . $hms . '.tsv';
211 my $record_count = 0;
212 $dbh->do("COPY $table TO STDOUT CSV DELIMITER E'\t' HEADER;");
213 1 while $dbh->pg_getcopydata(\$data[$record_count++]) >= 0;
214 open (my $eout, '>', $efile) or abort("Could NOT open $efile.");
215 foreach my $d (@data) {
218 print "$efile written.\n";
223 sub die_if_gsheet_tracked_table_does_not_exist {
224 if (!check_for_gsheet_tracked_table()) {
225 die "Table gsheet_tracked_table does not exist. Bailing...\n";
235 foreach my $av (@a) {
236 foreach my $bv (@b) {
237 if ($av eq $bv) { push @r, $bv; }
243 sub get_pg_column_headers {
244 my $table_name = shift;
245 my $schema_name = shift;
247 my $dbh = EMig::db_connect();
248 $sql = 'SELECT column_name FROM information_schema.columns WHERE table_schema = ' . $dbh->quote( $schema_name ) . ' AND table_name = ' . $dbh->quote( $table_name ) . ';';
249 $sth = $dbh->prepare($sql);
250 $ra = $sth->execute();
251 while (my @row = $sth->fetchrow_array) {
252 push @headers, $row[0];
261 print "Erasing $ws_name.\n";
262 my @rows = $ws->rows;
273 sub check_for_gsheet_tracked_table {
274 my $dbh = EMig::db_connect();
275 my $sth = $dbh->prepare("
278 FROM information_schema.tables
279 WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
280 AND table_name = 'gsheet_tracked_table'
283 my $rv = $sth->execute()
284 || die "Error checking for table (tracked_gsheet_table): $!";
285 my @cols = $sth->fetchrow_array;
287 EMig::db_disconnect($dbh);
291 sub die_if_gsheet_tracked_column_does_not_exist {
292 if (!check_for_gsheet_tracked_column()) {
293 die "Table $MIGSCHEMA.gsheet_tracked_column does not exist. Bailing...\n";
298 my $worksheet = shift;
301 my $sql = 'SELECT table_name FROM gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
302 my $sth = $dbh->prepare($sql);
303 my $xs = $sth->execute();
305 while (my @row = $sth->fetchrow_array) {
306 $table_name = $row[0];
312 #sub get_worksheet_name {
316 # my $sql = 'SELECT tab_name FROM gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
318 # my $sth = $dbh->prepare($sql);
319 # my $xs = $sth->execute();
320 # my $worksheet_name;
321 # while (my @row = $sth->fetchrow_array) {
322 # $worksheet_name = $row[0];
325 # return $worksheet_name;
329 sub check_for_gsheet_tracked_column {
330 my $dbh = EMig::db_connect();
331 my $sth = $dbh->prepare("
334 FROM information_schema.tables
335 WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
336 AND table_name = 'gsheet_tracked_column'
339 my $rv = $sth->execute()
340 || die "Error checking for table (gsheet_tracked_column): $!";
341 my @cols = $sth->fetchrow_array;
343 EMig::db_disconnect($dbh);
348 my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
349 my @headers = @{ $headers_ref };
350 my @row_data = @{ $row_ref };
352 my $header_string = '(' . join(",", @headers) . ')';
353 map {s/\'/\'\'/g; } @row_data;
354 my $row_string = '(' . join(",", map {qq/'$_'/} @row_data) . ')';
355 #print "INSERT INTO $schema.$table $header_string VALUES $row_string\n";
357 INSERT INTO $schema.$table $header_string VALUES $row_string ;
362 my ($table, $dbh, $action) = @_;
365 if ($action eq 'pull') { $column = 'last_pulled' }
366 else { $column = 'last_pushed' };
369 UPDATE gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
380 TRUNCATE TABLE $table;;
382 print "Table $table truncated.\n";
387 print STDERR "$0: $msg", "\n";
393 my ($clientid,$clientsecret,$sessionfile) = @_;
395 my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
396 client_id => $clientid,
397 client_secret => $clientsecret,
398 scope => ['http://spreadsheets.google.com/feeds/'],
399 redirect_uri => 'https://developers.google.com/oauthplayground',
401 if ($sessionfile =~ m/~/) {$sessionfile =~ s/~/$ENV{HOME}/; }
402 my $session = retrieve($sessionfile);
403 my $restored_token = Net::OAuth2::AccessToken->session_thaw(
406 profile => $oauth2->oauth2_webserver,
408 $oauth2->access_token($restored_token);
409 my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
411 my $spreadsheet = $service->spreadsheet(