# TODO:
# - detect moves between groups
-# - better daemon behavior
require '../include/config.pm';
+my $dbh;
my @log = ();
sub get_oauth_bearer_token {
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();
+}
projects / skvidarsync / commitdiff