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/";
18 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
19 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
26 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
28 Mig::die_if_no_env_migschema();
29 die_if_gsheet_tracked_table_does_not_exist();
30 die_if_gsheet_tracked_column_does_not_exist();
44 foreach my $arg (@ARGV) {
45 if ($arg eq '--push') {
46 $next_arg_is_push = 1;
49 if ($next_arg_is_push) {
51 $next_arg_is_push = 0;
54 if ($arg eq '--pull') {
55 $next_arg_is_pull = 1;
58 if ($next_arg_is_pull) {
60 $next_arg_is_pull = 0;
63 if ($arg eq '--export') {
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";
362 if (!defined $ENV{'CLIENTID'}) {
363 exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
364 print "Open Authentication settings were not loaded, please re-run.\n";
366 my $session_filename = $ENV{SESSIONFILE};
367 my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
368 client_id => $ENV{CLIENTID},
369 client_secret => $ENV{CLIENTSECRET},
370 scope => ['http://spreadsheets.google.com/feeds/'],
371 redirect_uri => 'https://developers.google.com/oauthplayground',
373 my $session = retrieve($session_filename);
374 my $restored_token = Net::OAuth2::AccessToken->session_thaw(
377 profile => $oauth2->oauth2_webserver,
379 $oauth2->access_token($restored_token);
380 my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
382 my $spreadsheet = $service->spreadsheet(