From 60124b44b148a69e9a51d4333c1a4fe110b95a4e Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Sat, 28 Oct 2023 19:47:15 +0200 Subject: [PATCH] Better daemon behavior. --- bin/sync.pl | 438 +++++++++++++++++++++++++++------------------------- 1 file changed, 231 insertions(+), 207 deletions(-) diff --git a/bin/sync.pl b/bin/sync.pl index 46df547..bcba96a 100644 --- a/bin/sync.pl +++ b/bin/sync.pl @@ -13,10 +13,10 @@ use utf8; # TODO: # - detect moves between groups -# - better daemon behavior require '../include/config.pm'; +my $dbh; my @log = (); sub get_oauth_bearer_token { @@ -263,236 +263,260 @@ sub find_diff { return @diffs; } -skv_log("Siste sync startet: " . POSIX::ctime(time)); - -# Initialize the handles we need for communication. -my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1}) - or die "Could not connect to Postgres: " . DBI->errstr; -my $ua = LWP::UserAgent->new('SKVidarLang/1.0'); -my $token = get_oauth_bearer_token($ua); - -# Find the newest message, and what it is linked to. -# TODO: Support more than one, and test better for errors here. -my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1'); -$q->execute($config::invitation_channel); -my $linkref = $q->fetchrow_hashref; -my $invitation_ts = $linkref->{'ts'}; -my $wanted_sheet_title = $linkref->{'sheet_title'}; -die "Could not get newest sheet title" if (!defined($wanted_sheet_title)); - -my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title); -if (!defined($tab_name)) { - skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n"); - sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]); - die; -} +sub run { + @log = (); + skv_log("Siste sync startet: " . POSIX::ctime(time)); + + # Initialize the handles we need for communication. + $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1}) + or die "Could not connect to Postgres: " . DBI->errstr; + my $ua = LWP::UserAgent->new('SKVidarLang/1.0'); + my $token = get_oauth_bearer_token($ua); + + # Find the newest message, and what it is linked to. + # TODO: Support more than one, and test better for errors here. + my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1'); + $q->execute($config::invitation_channel); + my $linkref = $q->fetchrow_hashref; + my $invitation_ts = $linkref->{'ts'}; + my $wanted_sheet_title = $linkref->{'sheet_title'}; + die "Could not get newest sheet title" if (!defined($wanted_sheet_title)); + + my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title); + if (!defined($tab_name)) { + skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n"); + sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]); + die; + } -# Find everyone who are marked as attending on Slack (via reactions). -$q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')'); -$q->execute($config::invitation_channel, $invitation_ts); -my @attending_userids = (); -while (my $ref = $q->fetchrow_hashref) { - push @attending_userids, $ref->{'userid'}; -} + # Find everyone who are marked as attending on Slack (via reactions). + $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')'); + $q->execute($config::invitation_channel, $invitation_ts); + my @attending_userids = (); + while (my $ref = $q->fetchrow_hashref) { + push @attending_userids, $ref->{'userid'}; + } -# Get the list of all people in the sheet (we're going to need them soon anyway). -my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&fields=sheets/data/rowData/values/userEnteredValue', - Authorization => 'Bearer ' . $token -); -my $main_sheet_json = JSON::XS::decode_json($response->decoded_content); + # Get the list of all people in the sheet (we're going to need them soon anyway). + my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&fields=sheets/data/rowData/values/userEnteredValue', + Authorization => 'Bearer ' . $token + ); + my $main_sheet_json = JSON::XS::decode_json($response->decoded_content); -my %seen_names = find_where_each_name_is($main_sheet_json); + my %seen_names = find_where_each_name_is($main_sheet_json); -# Find duplicates. -for my $name (sort keys %seen_names) { - my $seen = $seen_names{$name}; - if (scalar @$seen >= 2) { - my $exemplar = $seen->[0][0]; - skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")"); + # Find duplicates. + for my $name (sort keys %seen_names) { + my $seen = $seen_names{$name}; + if (scalar @$seen >= 2) { + my $exemplar = $seen->[0][0]; + skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")"); + } } -} - -# Get our existing Slack->name mapping, from the sheets. -my %slack_userid_to_real_name = (); -my %slack_userid_to_slack_name = (); -my %slack_userid_to_row = (); -$response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue', - Authorization => 'Bearer ' . $token -); -my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content); -my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; -my $cur_row = 5; -for my $row (@$mapping_sheet_rows) { - my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'}; - my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'}; - my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more - $slack_userid_to_row{$slack_id} = $cur_row++; - next if (!defined($slack_name)); - $slack_userid_to_slack_name{$slack_id} = $slack_name; - next if (!defined($real_name)); - $slack_userid_to_real_name{$slack_id} = $real_name; -} -# See which ones we don't have a mapping for, and look them up in Slack. -# TODO: Use an append call instead of $cur_row? -my @slack_mapping_updates = (); -for my $userid (@attending_userids) { - next if (exists($slack_userid_to_real_name{$userid})); - - # Make sure they have a row in the spreadsheet. - my $write_row; - if (exists($slack_userid_to_row{$userid})) { - $write_row = $slack_userid_to_row{$userid}; - } else { - $write_row = $cur_row++; - $slack_userid_to_row{$userid} = $write_row; - push @slack_mapping_updates, { - range => "Slack-mapping!A$write_row:A$write_row", - values => [ [ $userid ]] - }; + # Get our existing Slack->name mapping, from the sheets. + my %slack_userid_to_real_name = (); + my %slack_userid_to_slack_name = (); + my %slack_userid_to_row = (); + $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue', + Authorization => 'Bearer ' . $token + ); + my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content); + my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; + my $cur_row = 5; + for my $row (@$mapping_sheet_rows) { + my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'}; + my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'}; + my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more + $slack_userid_to_row{$slack_id} = $cur_row++; + next if (!defined($slack_name)); + $slack_userid_to_slack_name{$slack_id} = $slack_name; + next if (!defined($real_name)); + $slack_userid_to_real_name{$slack_id} = $real_name; } - # Fetch their Slack name if we don't already have it. - my $slack_name; - if (exists($slack_userid_to_slack_name{$userid})) { - $slack_name = $slack_userid_to_slack_name{$userid}; - } else { - $slack_userid_to_slack_name{$userid} = $slack_name; - $slack_name = get_slack_name($ua, $userid); - push @slack_mapping_updates, { - range => "Slack-mapping!B$write_row:B$write_row", - values => [ [ $slack_name ]] - }; - $slack_userid_to_slack_name{$userid} = $slack_name; - } + # See which ones we don't have a mapping for, and look them up in Slack. + # TODO: Use an append call instead of $cur_row? + my @slack_mapping_updates = (); + for my $userid (@attending_userids) { + next if (exists($slack_userid_to_real_name{$userid})); - if (exists($seen_names{lc $slack_name})) { - # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz. - $slack_userid_to_real_name{$userid} = $slack_name; - push @slack_mapping_updates, { - range => "Slack-mapping!C$write_row:C$write_row", - values => [ [ $slack_name ]] - }; - } else { - # Do a search through all the available names in the sheet to find an obvious(ish) match. - my @candidates = (); - my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; - for my $row (@$main_sheet_rows) { - for my $val (@{$row->{'values'}}) { - my $name = get_spreadsheet_name($val); - if (defined($name) && matches_name($slack_name, $name)) { - push @candidates, $name; - } - } + # Make sure they have a row in the spreadsheet. + my $write_row; + if (exists($slack_userid_to_row{$userid})) { + $write_row = $slack_userid_to_row{$userid}; + } else { + $write_row = $cur_row++; + $slack_userid_to_row{$userid} = $write_row; + push @slack_mapping_updates, { + range => "Slack-mapping!A$write_row:A$write_row", + values => [ [ $userid ]] + }; } - if ($#candidates == -1) { - skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem."); - } elsif ($#candidates == 0) { - my $name = $candidates[0]; - $slack_userid_to_real_name{$userid} = $name; + + # Fetch their Slack name if we don't already have it. + my $slack_name; + if (exists($slack_userid_to_slack_name{$userid})) { + $slack_name = $slack_userid_to_slack_name{$userid}; + } else { + $slack_userid_to_slack_name{$userid} = $slack_name; + $slack_name = get_slack_name($ua, $userid); + push @slack_mapping_updates, { + range => "Slack-mapping!B$write_row:B$write_row", + values => [ [ $slack_name ]] + }; + $slack_userid_to_slack_name{$userid} = $slack_name; + } + + if (exists($seen_names{lc $slack_name})) { + # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz. + $slack_userid_to_real_name{$userid} = $slack_name; push @slack_mapping_updates, { range => "Slack-mapping!C$write_row:C$write_row", - values => [ [ $name ]] + values => [ [ $slack_name ]] }; } else { - skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes."); + # Do a search through all the available names in the sheet to find an obvious(ish) match. + my @candidates = (); + my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; + for my $row (@$main_sheet_rows) { + for my $val (@{$row->{'values'}}) { + my $name = get_spreadsheet_name($val); + if (defined($name) && matches_name($slack_name, $name)) { + push @candidates, $name; + } + } + } + if ($#candidates == -1) { + skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem."); + } elsif ($#candidates == 0) { + my $name = $candidates[0]; + $slack_userid_to_real_name{$userid} = $name; + push @slack_mapping_updates, { + range => "Slack-mapping!C$write_row:C$write_row", + values => [ [ $name ]] + }; + } else { + skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes."); + } } } -} -my $update = { - valueInputOption => 'USER_ENTERED', - data => \@slack_mapping_updates -}; -$response = $ua->post( - 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key, - Content => JSON::XS::encode_json($update), - Content_type => 'application/json;charset=UTF-8', - Authorization => 'Bearer ' . $token -); -die $response->decoded_content if (!$response->is_success); - -# Find the list of names to mark yellow. -my %want_names = (); -my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; -for my $userid (@attending_userids) { - next if (!exists($slack_userid_to_real_name{$userid})); - my $slack_name = $slack_userid_to_slack_name{$userid}; - my $real_name = $slack_userid_to_real_name{$userid}; - - # See if we can find them in the spreadsheet. - if (!exists($seen_names{lc $real_name})) { - # TODO: Perhaps move this logic further down, for consistency? - skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe."); - } else { - my $seen = $seen_names{lc $real_name}; - if (scalar @$seen >= 2) { - skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes."); + my $update = { + valueInputOption => 'USER_ENTERED', + data => \@slack_mapping_updates + }; + $response = $ua->post( + 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key, + Content => JSON::XS::encode_json($update), + Content_type => 'application/json;charset=UTF-8', + Authorization => 'Bearer ' . $token + ); + die $response->decoded_content if (!$response->is_success); + + # Find the list of names to mark yellow. + my %want_names = (); + my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'}; + for my $userid (@attending_userids) { + next if (!exists($slack_userid_to_real_name{$userid})); + my $slack_name = $slack_userid_to_slack_name{$userid}; + my $real_name = $slack_userid_to_real_name{$userid}; + + # See if we can find them in the spreadsheet. + if (!exists($seen_names{lc $real_name})) { + # TODO: Perhaps move this logic further down, for consistency? + skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe."); } else { - $want_names{$seen->[0][0]} = 1; + my $seen = $seen_names{lc $real_name}; + if (scalar @$seen >= 2) { + skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes."); + } else { + $want_names{$seen->[0][0]} = 1; + } } } -} -# Find the list of names we already marked yellow. -my %have_names = (); -$dbh->{AutoCommit} = 0; -$dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); -$q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?'); -$q->execute($config::invitation_channel, $invitation_ts); -while (my $ref = $q->fetchrow_hashref) { - $have_names{$ref->{'name'}} = 1; -} - -my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names); - -my @yellow_updates = (); -if (scalar @diffs > 0) { - # Now fill in the actual stuff. - for my $diff (@diffs) { - my $real_name = $diff->[0]; + # Find the list of names we already marked yellow. + my %have_names = (); + $dbh->{AutoCommit} = 0; + $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); + $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?'); + $q->execute($config::invitation_channel, $invitation_ts); + while (my $ref = $q->fetchrow_hashref) { + $have_names{$ref->{'name'}} = 1; + } - # See if we can find them in the spreadsheet. - my $seen = $seen_names{lc $real_name}; - die if (!defined($seen) || scalar @$seen > 1); - my $rowno = $seen->[0][1]; - my $colno = $seen->[0][2]; - push @yellow_updates, { - updateCells => { - rows => [{ - values => [{ - userEnteredFormat => $diff->[1] - }] - }], - fields => 'userEnteredFormat.backgroundColor', - range => { - sheetId => $tab_id, - startRowIndex => $rowno, - endRowIndex => $rowno + 1, - startColumnIndex => $colno, - endColumnIndex => $colno + 1 + my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names); + + my @yellow_updates = (); + if (scalar @diffs > 0) { + # Now fill in the actual stuff. + for my $diff (@diffs) { + my $real_name = $diff->[0]; + + # See if we can find them in the spreadsheet. + my $seen = $seen_names{lc $real_name}; + die if (!defined($seen) || scalar @$seen > 1); + my $rowno = $seen->[0][1]; + my $colno = $seen->[0][2]; + push @yellow_updates, { + updateCells => { + rows => [{ + values => [{ + userEnteredFormat => $diff->[1] + }] + }], + fields => 'userEnteredFormat.backgroundColor', + range => { + sheetId => $tab_id, + startRowIndex => $rowno, + endRowIndex => $rowno + 1, + startColumnIndex => $colno, + endColumnIndex => $colno + 1 + } } - } - }; + }; + } } + + my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name); + push @yellow_updates, { + updateCells => { + rows => \@recent_changes, + fields => 'userEnteredValue.stringValue', + range => { + sheetId => $config::log_tab_id, + startRowIndex => 4, + endRowIndex => 4 + scalar @recent_changes, + startColumnIndex => 0, + endColumnIndex => 1 + } + } + }; + + # Push the final set of updates (including the log). + skv_log("Ferdig."); + push @yellow_updates, serialize_skv_log_to_sheet(); + sheet_batch_update($ua, $token, \@yellow_updates); + $dbh->commit; } -my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name); -push @yellow_updates, { - updateCells => { - rows => \@recent_changes, - fields => 'userEnteredValue.stringValue', - range => { - sheetId => $config::log_tab_id, - startRowIndex => 4, - endRowIndex => 4 + scalar @recent_changes, - startColumnIndex => 0, - endColumnIndex => 1 +if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') { + while (1) { + if (!unlink("/srv/skvidar-slack.sesse.net/marker")) { + unless ($!{ENOENT}) { + warn "/srv/skvidar-slack.sesse.net/marker: $!"; + } + sleep 1; + next; } + eval { + run(); + }; + if ($@) { + warn "Died with: $@"; + } + $dbh->disconnect; } -}; - -# Push the final set of updates (including the log). -skv_log("Ferdig."); -push @yellow_updates, serialize_skv_log_to_sheet(); -sheet_batch_update($ua, $token, \@yellow_updates); -$dbh->commit; +} else { + run(); +} -- 2.39.2