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/";
17 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
18 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
25 my $mig_bin = "$FindBin::Bin/";
27 Mig::die_if_no_env_migschema();
28 die_if_gsheet_tracked_table_does_not_exist();
29 die_if_gsheet_tracked_column_does_not_exist();
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 '--export') {
68 abort('invalid value for --export') unless ($cmd_export eq 'on' or $cmd_export eq 'off');
69 abort('must specify --push or --pull') unless (defined $cmd_push or defined $cmd_pull);
70 if (defined $cmd_push and defined $cmd_pull) { abort('you can not specify both a --push and --pull on the same command'); }
72 my $dbh = Mig::db_connect();
73 my $spreadsheet = connect_gsheet();
74 abort('could not connect to google sheet') unless (defined $spreadsheet);
76 $sql = 'SELECT tab_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table;';
77 $sth = $dbh->prepare($sql);
78 my $ra = $sth->execute();
79 while (my @row = $sth->fetchrow_array) {
80 push @tracked_ws_names, $row[0];
83 if (defined $cmd_pull) {
85 if ($cmd_pull eq 'all') {
86 print "all worksheets.\n";
87 @ws = $spreadsheet->worksheets;
88 foreach my $wsn (@ws) { push @worksheet_names, $wsn->title; }
90 print "only worksheet $cmd_pull.\n";
91 if (!defined $cmd_pull) { abort('command incomplete'); }
92 push @worksheet_names, $cmd_pull;
94 my @m = array_match(\@worksheet_names,\@tracked_ws_names);
96 my $pull_ws = $spreadsheet->worksheet( {title => $w} );
97 my $push_tb = get_table_name($MIGSCHEMA,$w,$dbh);
98 my @rows = $pull_ws->rows;
100 map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
101 #print Dumper($pull_ws->cells);
102 #print Dumper(@content);
103 my @tab_headers = shift @content;
104 my $tab_headers_length = $#{ $tab_headers[0] };
106 for my $i ( 0 .. $tab_headers_length ) {
107 push @pg_headers, $tab_headers[0][$i];
110 #todo: check for clean headers at some point ...
111 truncate_table($MIGSCHEMA,$push_tb,$dbh);
112 print "Inserting from $w to $push_tb.\n";
113 for my $j (@content) {
114 insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
116 timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
120 if (defined $cmd_push) {
122 if ($cmd_push eq 'all') {
123 print "all tables.\n";
124 $sql = 'SELECT table_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table';
125 $sth = $dbh->prepare($sql);
126 $ra = $sth->execute();
127 while (my @row = $sth->fetchrow_array) {
128 push @table_names, $row[0];
131 print "only table $cmd_push.\n";
132 if (!defined $cmd_push) { abort('command incomplete'); }
133 push @table_names, $cmd_push;
135 foreach my $t (@table_names) {
136 my $pull_tb = $MIGSCHEMA . "." . $t;;
137 my @table_headers = get_pg_column_headers($t,$MIGSCHEMA);
138 my $push_ws_name = get_worksheet_name($MIGSCHEMA,$t,$dbh);
139 my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
140 if (!defined $push_ws) { next; }
143 foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }
144 $sql = "SELECT * FROM $pull_tb;";
145 $sth = $dbh->prepare($sql);
147 my $grabhash = $sth->fetchall_arrayref({});
148 erase_sheet($push_ws,$push_ws_name);
150 #get from postgres the headers to use in the sheet from tracked columns
151 $sql = 'SELECT column_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_column WHERE table_id = (SELECT id FROM ' . $MIGSCHEMA . '.gsheet_tracked_table WHERE table_name = \'' . $t . '\')';
152 $sth = $dbh->prepare($sql);
154 my $sheet_headers = $sth->fetchall_arrayref();
155 my $sheet_headers_length = @$sheet_headers;
156 #now I need to do new rows using those headers
158 foreach my $row ( @{$grabhash} ) {
160 for my $column ( sort keys %{ $row } ) {
161 print Dumper(@$sheet_headers);
162 print "column: $column\n";
163 my $clean_column = $column;
164 $clean_column =~ s/_//g;
165 print "clean column: $clean_column\n";
166 if ( $column ~~ @$sheet_headers ) {
167 $record->{$clean_column} = $row->{$column};
170 push @content, $record;
173 #print Dumper(@content);
174 foreach my $fillsheet (@content) {
175 my $new_row = $push_ws->add_row (
179 timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
183 sub die_if_gsheet_tracked_table_does_not_exist {
184 if (!check_for_gsheet_tracked_table()) {
185 die "Table $MIGSCHEMA.gsheet_tracked_table does not exist. Bailing...\n";
195 foreach my $av (@a) {
196 foreach my $bv (@b) {
197 if ($av eq $bv) { push @r, $bv; }
203 sub get_pg_column_headers {
204 my $table_name = shift;
205 my $schema_name = shift;
207 my $dbh = Mig::db_connect();
208 $sql = 'SELECT column_name FROM information_schema.columns WHERE table_schema = ' . $dbh->quote( $schema_name ) . ' AND table_name = ' . $dbh->quote( $table_name ) . ';';
209 $sth = $dbh->prepare($sql);
210 $ra = $sth->execute();
211 while (my @row = $sth->fetchrow_array) {
212 push @headers, $row[0];
221 print "Erasing $ws_name.\n";
222 my @rows = $ws->rows;
225 $j = int(($j / 2))-1;
226 if ($j < 2) { $j = 2; }
228 #bodge until I figure out why google sheets is only deleting even numbered rows
230 foreach my $row (@rows) {
231 if ($i != 1) { $row->delete; }
239 sub check_for_gsheet_tracked_table {
240 my $dbh = Mig::db_connect();
241 my $sth = $dbh->prepare("
244 FROM information_schema.tables
245 WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
246 AND table_name = 'gsheet_tracked_table'
249 my $rv = $sth->execute()
250 || die "Error checking for table (tracked_gsheet_table): $!";
251 my @cols = $sth->fetchrow_array;
253 Mig::db_disconnect($dbh);
257 sub die_if_gsheet_tracked_column_does_not_exist {
258 if (!check_for_gsheet_tracked_column()) {
259 die "Table $MIGSCHEMA.gsheet_tracked_column does not exist. Bailing...\n";
265 my $worksheet = shift;
268 my $sql = 'SELECT table_name FROM ' . $migs . '.gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
269 my $sth = $dbh->prepare($sql);
270 my $xs = $sth->execute();
272 while (my @row = $sth->fetchrow_array) {
273 $table_name = $row[0];
279 sub get_worksheet_name {
284 my $sql = 'SELECT tab_name FROM ' . $migs . '.gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
285 my $sth = $dbh->prepare($sql);
286 my $xs = $sth->execute();
288 while (my @row = $sth->fetchrow_array) {
289 $worksheet_name = $row[0];
292 return $worksheet_name;
296 sub check_for_gsheet_tracked_column {
297 my $dbh = Mig::db_connect();
298 my $sth = $dbh->prepare("
301 FROM information_schema.tables
302 WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
303 AND table_name = 'gsheet_tracked_column'
306 my $rv = $sth->execute()
307 || die "Error checking for table (gsheet_tracked_column): $!";
308 my @cols = $sth->fetchrow_array;
310 Mig::db_disconnect($dbh);
315 my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
316 my @headers = @{ $headers_ref };
317 my @row_data = @{ $row_ref };
319 my $header_string = '(' . join(",", @headers) . ')';
320 map {s/\'/\'\'/g; } @row_data;
321 my $row_string = '(' . join(",", map {qq/'$_'/} @row_data) . ')';
322 print "INSERT INTO $schema.$table $header_string VALUES $row_string\n";
324 INSERT INTO $schema.$table $header_string VALUES $row_string ;
327 print "Row pushed to $table in schema $schema.\n";
331 my ($schema, $table, $dbh, $action) = @_;
334 if ($action eq 'pull') { $column = 'last_pulled' }
335 else { $column = 'last_pushed' };
338 UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
350 TRUNCATE TABLE $schema.$table;;
352 print "Table $schema.$table truncated.\n";
357 print STDERR "$0: $msg", "\n";
363 if (!defined $ENV{'CLIENTID'}) {
364 exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
365 print "Open Authentication settings were not loaded, please re-run.\n";
367 my $session_filename = $ENV{SESSIONFILE};
368 my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
369 client_id => $ENV{CLIENTID},
370 client_secret => $ENV{CLIENTSECRET},
371 scope => ['http://spreadsheets.google.com/feeds/'],
372 redirect_uri => 'https://developers.google.com/oauthplayground',
374 my $session = retrieve($session_filename);
375 my $restored_token = Net::OAuth2::AccessToken->session_thaw(
378 profile => $oauth2->oauth2_webserver,
380 $oauth2->access_token($restored_token);
381 my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
383 my $spreadsheet = $service->spreadsheet(
394 Mig gsheet is for synchronizing data between migration postgresql tables
395 and Google Sheets. It accepts the following command line arguments:
397 --push table_name or all
399 --pull worksheet_name or all
401 Either --push or --pull must be present and it must be one or the other.
405 Export will default to off if not declared. If on it will export to tab
406 separated tables the tables being pushed or worksheets being pulled with