fixing doc
[migration-tools.git] / mig-bin / mig-gsheet
1 #!/usr/bin/perl
2
3 use Env qw(
4     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
5     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
6 );
7 use Net::Google::Spreadsheets;
8 use Net::Google::DataAPI::Auth::OAuth2;
9 use Net::OAuth2::AccessToken;
10 use Storable;
11 use DBI;
12 use FindBin;
13 use lib "$FindBin::Bin/";
14 my $mig_bin = "$FindBin::Bin/";
15 use Mig;
16 use strict;
17 use Env qw(
18     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
19     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
20     );
21 use Switch;
22 use Cwd 'abs_path';
23 use Pod::Usage;
24 use Data::Dumper;
25
26 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
27
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();
31
32 my $cmd_push;
33 my $next_arg_is_push;
34 my $cmd_pull;
35 my $next_arg_is_pull;
36 my @worksheet_names;
37 my $cmd_export = 0;
38 my @table_names;
39 my $sql;
40 my $sth;
41 my @ws;
42 my @tracked_ws_names;
43
44 foreach my $arg (@ARGV) {
45     if ($arg eq '--push') {
46         $next_arg_is_push = 1;
47         next;
48     }
49     if ($next_arg_is_push) {
50         $cmd_push = $arg;
51         $next_arg_is_push = 0;
52         next;
53     }
54     if ($arg eq '--pull') {
55         $next_arg_is_pull = 1;
56         next;
57     }
58     if ($next_arg_is_pull) {
59         $cmd_pull = $arg;
60         $next_arg_is_pull = 0;
61         next;
62     }
63     if ($arg eq '--export') {
64         $cmd_export = 1;
65         next;
66     }
67 }
68
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'); }
71
72 my $dbh = Mig::db_connect();
73 my $spreadsheet = connect_gsheet();
74 abort('could not connect to google sheet') unless (defined $spreadsheet);
75
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];
81 }
82
83 if (defined $cmd_pull) {
84     print "Pulling ";
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; }
89     } else {
90         print "only worksheet $cmd_pull.\n";
91         if (!defined $cmd_pull) { abort('command incomplete'); } 
92         push @worksheet_names, $cmd_pull;
93     }
94     my @m = array_match(\@worksheet_names,\@tracked_ws_names);
95     foreach my $w (@m) { 
96         my $pull_ws = $spreadsheet->worksheet( {title => $w} ); 
97         my $push_tb = get_table_name($MIGSCHEMA,$w,$dbh);
98         my @rows = $pull_ws->rows;
99         my @content;
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] };
105         my @pg_headers;
106         for my $i ( 0 .. $tab_headers_length ) {
107             push @pg_headers, $tab_headers[0][$i];
108         }
109         shift @content;
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);
115         }
116         timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
117     }
118 }
119
120 if (defined $cmd_push) {
121     print "Pushing ";
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];
129         }
130     } else {
131         print "only table $cmd_push.\n";
132         if (!defined $cmd_push) { abort('command incomplete'); }
133         push @table_names, $cmd_push;
134     }
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; }
141         my @rows;
142         my $i = 0;
143         foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }         
144         $sql = "SELECT * FROM $pull_tb;";
145         $sth = $dbh->prepare($sql);
146         $sth->execute();
147         my $grabhash = $sth->fetchall_arrayref({});
148         erase_sheet($push_ws,$push_ws_name);
149
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);
153         $sth->execute();
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
157         my @content;
158         foreach my $row ( @{$grabhash} ) {
159             my $record = {};
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}; 
168                 }
169             }
170             push @content, $record;
171         }
172
173         #print Dumper(@content); 
174         foreach my $fillsheet (@content) {
175             my $new_row = $push_ws->add_row (
176                 $fillsheet
177             );
178         }
179         timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
180     }
181 }   
182
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";
186     }
187 }
188
189 sub array_match {
190     my ($xa,$xb) = @_;
191     my @a = @{ $xa };
192     my @b = @{ $xb };
193     my @r;
194
195     foreach my $av (@a) {
196         foreach my $bv (@b) {
197             if ($av eq $bv) { push @r, $bv; }
198         }
199     }    
200     return @r;
201 }
202
203 sub get_pg_column_headers {
204     my $table_name = shift;
205     my $schema_name = shift;
206     my @headers;
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];
213     }
214     return @headers;
215 }
216
217 sub erase_sheet {
218     my $ws = shift;
219     my $ws_name = shift;
220
221     print "Erasing $ws_name.\n";
222     my @rows = $ws->rows;
223     my $i;
224     my $j = @rows;
225     $j = int(($j / 2))-1;
226     if ($j < 2) { $j = 2; }
227     while ($j > 0) {
228         #bodge until I figure out why google sheets is only deleting even numbered rows
229         $i = 1;
230         foreach my $row (@rows) {
231             if ($i != 1) { $row->delete; }
232             $i++;
233         };
234         $j--;
235     };
236     return;
237 }
238
239 sub check_for_gsheet_tracked_table {
240     my $dbh = Mig::db_connect();
241     my $sth = $dbh->prepare("
242         SELECT EXISTS(
243             SELECT 1
244             FROM information_schema.tables
245             WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
246             AND table_name = 'gsheet_tracked_table'
247         );"
248     );
249     my $rv = $sth->execute()
250         || die "Error checking for table (tracked_gsheet_table): $!";
251     my @cols = $sth->fetchrow_array;
252     $sth->finish;
253     Mig::db_disconnect($dbh);
254     return $cols[0];
255 }
256
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";
260     }
261 }
262
263 sub get_table_name {
264     my $migs = shift;
265     my $worksheet = shift;
266     my $dbh = shift;
267
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();
271     my $table_name;
272     while (my @row = $sth->fetchrow_array) {
273         $table_name = $row[0];
274     }
275
276     return $table_name;
277 }
278
279 sub get_worksheet_name {
280     my $migs = shift;
281     my $table = shift;
282     my $dbh = shift;
283
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();
287     my $worksheet_name;
288     while (my @row = $sth->fetchrow_array) {
289         $worksheet_name = $row[0];
290     }
291
292     return $worksheet_name;
293 }
294
295
296 sub check_for_gsheet_tracked_column {
297     my $dbh = Mig::db_connect();
298     my $sth = $dbh->prepare("
299         SELECT EXISTS(
300             SELECT 1
301             FROM information_schema.tables
302             WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
303             AND table_name = 'gsheet_tracked_column'
304         );"
305     );
306     my $rv = $sth->execute()
307         || die "Error checking for table (gsheet_tracked_column): $!";
308     my @cols = $sth->fetchrow_array;
309     $sth->finish;
310     Mig::db_disconnect($dbh);
311     return $cols[0];
312 }
313
314 sub insert_row {
315     my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
316     my @headers = @{ $headers_ref };
317     my @row_data = @{ $row_ref };
318
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"; 
323     $dbh->do(qq/
324         INSERT INTO $schema.$table $header_string VALUES $row_string ;
325     /);
326     
327     print "Row pushed to $table in schema $schema.\n";
328 }
329
330 sub timestamp {
331     my ($schema, $table, $dbh, $action) = @_;
332
333     my $column;
334     if ($action eq 'pull') { $column = 'last_pulled' }
335         else { $column = 'last_pushed' }; 
336
337     $dbh->do(qq/
338         UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
339     /);
340
341 }
342
343
344 sub truncate_table {
345     my $schema = shift;
346     my $table = shift;
347     my $dbh = shift;
348
349     $dbh->do(qq/
350         TRUNCATE TABLE $schema.$table;;
351     /);
352     print "Table $schema.$table truncated.\n";
353 }
354
355 sub abort {
356     my $msg = shift;
357     print STDERR "$0: $msg", "\n";
358     exit 1;
359 }
360
361 sub connect_gsheet {
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";
365     }
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',
372     );
373     my $session = retrieve($session_filename);
374     my $restored_token = Net::OAuth2::AccessToken->session_thaw(
375         $session,
376         auto_refresh => 1,
377         profile => $oauth2->oauth2_webserver,
378     );
379     $oauth2->access_token($restored_token);
380     my $service = Net::Google::Spreadsheets->new(auth => $oauth2);
381
382     my $spreadsheet = $service->spreadsheet(
383         {  
384             title => $MIGSCHEMA
385         }
386     );
387     return $spreadsheet;
388 }
389
390