use LWP::UserAgent;
use DBI;
use POSIX;
+use Time::HiRes;
+use IO::Select;
+use Unicode::Collate;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
-# TODO:
-# - detect moves between groups
-
require '../include/config.pm';
-my $dbh;
my @log = ();
+my $uca = Unicode::Collate->new(level => 1);
my %rgb = (
yellow => {
}
);
+sub log_timing {
+ my ($start, $msg) = @_;
+ my $elapsed = Time::HiRes::tv_interval($start);
+ printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
+}
+
+sub sort_key {
+ my $m = shift;
+ return $uca->getSortKey($m);
+}
+
sub get_oauth_bearer_token {
- my $ua = shift;
+ my ($dbh, $ua) = @_;
my $now = time();
+
+ # See if the database already has a token we could use, that doesn't expire in a while.
+ my $ref = $dbh->selectrow_hashref('SELECT token FROM oauth_tokens WHERE expiry - (TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\') > INTERVAL \'1 minute\' ORDER BY expiry DESC LIMIT 1', undef, $now);
+ if (defined($ref->{'token'})) {
+ return $ref->{'token'};
+ }
+
my $jwt = JSON::XS::encode_json({
"iss" => $config::jwt_key->{'client_email'},
"scope" => "https://www.googleapis.com/auth/spreadsheets",
"iat" => $now,
});
my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
'assertion' => $jws_token ]);
- return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+ log_timing($start, '/oauth2/v4/token');
+ my $token = JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+ $dbh->do('INSERT INTO oauth_tokens (token, acquired, expiry) VALUES (?, TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\', TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\')',
+ undef, $token, $now, $now + 1800);
+ return $token;
}
sub get_slack_name {
my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
'Authorization' => 'Bearer ' . $config::slack_oauth_token
]);
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->request($req);
+ log_timing($start, '/users.info');
die $response->status_line if !$response->is_success;
my $user_json = JSON::XS::decode_json($response->decoded_content);
sub matches_name {
my ($slack_name, $spreadsheet_name) = @_;
- if (lc($slack_name) eq lc($spreadsheet_name)) {
+ if (sort_key($slack_name) eq sort_key($spreadsheet_name)) {
return 1;
}
my @ap = split /\s+/, $slack_name;
my @bp = split /\s+/, $spreadsheet_name;
- if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
+ if (scalar @ap >= 2 && scalar @bp >= 2 && sort_key($ap[0]) eq sort_key($bp[0])) {
# First name matches, try to match some surname
my $found = 0;
for my $ai (1..$#ap) {
for my $bi (1..$#bp) {
- $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
+ $found = 1 if (sort_key($ap[$ai]) eq sort_key($bp[$bi]));
}
}
if ($found) {
sub format_cell_names_for_seen {
my $seen = shift;
- my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
+ my @cells = map { chr(ord('A') + $_->[2]) . ($_->[1] + 1) } @$seen;
return join(', ', @cells);
}
my $update = {
requests => \@requests
};
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post(
'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
Content => JSON::XS::encode_json($update),
Content_type => 'application/json;charset=UTF-8',
Authorization => 'Bearer ' . $token
);
+ log_timing($start, '/spreadsheets/values:batchUpdate');
die $response->decoded_content if !$response->is_success;
}
+sub get_group_assignments {
+ my $json = shift;
+
+ my %assignments = ();
+ my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my @curr_groups = ();
+ for my $row (@$rows) {
+ my $col = 0;
+ for my $val (@{$row->{'values'}}) {
+ ++$col;
+ my $contents = $val->{'userEnteredValue'}{'stringValue'};
+ next if !defined($contents);
+ if ($contents =~ /Gruppe /) {
+ @curr_groups = ();
+ last;
+ }
+ next if $contents =~ /^VL:/;
+ next if $contents =~ /^LT\b/;
+ next if $contents =~ /^400m/;
+ next if $contents =~ /^546m/;
+ if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye løpere.*)/) {
+ $curr_groups[$col] = $1;
+ } else {
+ my $name = get_spreadsheet_name($val);
+ next if (!defined($name));
+ my $group = $curr_groups[$col] // $curr_groups[$col - 1];
+ # print $group, " ", $name, "\n";
+ if (exists($assignments{$name})) {
+ $assignments{$name} = "(flere grupper)";
+ } else {
+ $assignments{$name} = $group;
+ }
+ }
+ }
+ }
+ return %assignments;
+}
+
+sub update_assignment_db {
+ my ($dbh, $channel, $ts, $assignments) = @_;
+
+ local $dbh->{AutoCommit} = 0;
+ my %db_assignments = ();
+ my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
+ $q->execute($channel, $ts);
+ while (my $ref = $q->fetchrow_hashref) {
+ if (defined($ref->{'group_name'})) {
+ $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
+ }
+ }
+
+ $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
+ for my $name (keys %$assignments) {
+ if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
+ $q->execute($channel, $ts, $name, $assignments->{$name});
+ }
+ }
+ for my $name (keys %db_assignments) {
+ if (!exists($assignments->{$name})) {
+ $q->execute($channel, $ts, $name, undef);
+ }
+ }
+ $dbh->commit;
+}
+
sub get_spreadsheet_with_title {
my ($ua, $token, $wanted_sheet_title) = @_;
# See if we have any spreadsheets that match this title.
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
- Authorization => 'Bearer ' . $token
+ Authorization => 'Bearer ' . $token,
+ Accept_Encoding => HTTP::Message::decodable
);
+ log_timing($start, '/spreadsheets/properties');
my $sheets_json = JSON::XS::decode_json($response->decoded_content);
my ($tab_name, $tab_id);
for my $sheet (@{$sheets_json->{'sheets'}}) {
for my $val (@{$row->{'values'}}) {
my $name = get_spreadsheet_name($val);
if (defined($name)) {
- push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
+ push @{$seen_names{sort_key($name)}}, [$name, $rowno, $colno];
}
++$colno;
}
return @recent_changes;
}
+sub create_move_log {
+ my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
+ my $q = $dbh->prepare(<<"EOF");
+SELECT
+ name, g_old.group_name as old_group, g_new.group_name as new_group, TO_CHAR(g_new.change_seen, \'YYYY-mm-dd HH24:MI\') AS change_seen
+FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
+ FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
+WHERE
+ g_new.group_name IS DISTINCT FROM g_old.group_name
+ AND g_new.group_name IS NOT NULL
+ORDER BY g_new.change_seen DESC, name
+LIMIT 50
+EOF
+ $q->execute($prev_invitation_ts, $invitation_ts);
+ my @recent_moves = ();
+ while (my $ref = $q->fetchrow_hashref) {
+ my $name = $ref->{'name'};
+ my $old_group = $ref->{'old_group'};
+ my $new_group = $ref->{'new_group'};
+
+ my $msg = $ref->{'change_seen'} . " ";
+ if (!defined($old_group)) {
+ $msg .= "$name, (ny løper) → $new_group";
+ } else {
+ $msg .= "$name, $old_group → $new_group";
+ }
+ push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
+ }
+ while (scalar @recent_moves < 50) {
+ push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
+ }
+ return @recent_moves;
+}
+
# Also applies the diff to the database (a bit ugly).
sub find_diff {
my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
}
for my $real_name (keys %$have_colors) {
next if (exists($want_colors->{$real_name}));
- if (!exists($seen_names->{lc $real_name})) {
+ if (!exists($seen_names->{sort_key($real_name)})) {
# TODO: This can somehow come if we try to add someone who's not in the sheet, too?
skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
+ } elsif (scalar @{$seen_names->{sort_key($real_name)}} > 1) {
+ # Don't touch them.
} else {
skv_log("Fjerner at $real_name skal på trening.");
push @diffs, [
return;
}
+ my $msg = "Hei! Du meldte deg akkurat på trening, men vi klarer ikke å finne deg i en gruppe i regnearket. For at det skal være enklere for trenerne, ønsker vi gjerne at du går inn på https://regneark.skvidar.run/ og skriver deg inn der med samme navn som du bruker på Slack. Om du er usikker på hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen på trening og til klubben!";
+
skv_log("Sender melding til $userid for å spørre om gruppe.");
+ my $content = {
+ channel => $config::invitation_channel,
+ user => $userid,
+ text => $msg
+ };
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post(
- 'https://slack.com/api/conversations.open',
- Content => JSON::XS::encode_json({ users => [ $userid ] }),
+ 'https://slack.com/api/chat.postEphemeral',
+ Content => JSON::XS::encode_json($content),
Content_type => 'application/json;charset=UTF-8',
Authorization => 'Bearer ' . $config::slack_oauth_token
);
+ log_timing($start, 'chat.postEphemeral');
die $response->status_line if !$response->is_success;
-
- my $im_json = JSON::XS::decode_json($response->decoded_content);
- die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
- my $channel_id = $im_json->{'channel'}{'id'};
-
- my $msg = "Hei! Du meldte deg akkurat på trening, men vi klarer ikke å finne deg i en gruppe i regnearket. For at det skal være enklere for trenerne, ønsker vi gjerne at du går inn på https://regneark.skvidar.run/ og skriver deg inn der med samme navn som du bruker på Slack. Om du er usikker på hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen på trening og til klubben!";
-
- $response = $ua->post(
- 'https://slack.com/api/chat.postMessage',
- Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
- Content_type => 'application/json;charset=UTF-8',
- Authorization => 'Bearer ' . $config::slack_oauth_token
- );
my $msg_json = JSON::XS::decode_json($response->decoded_content);
die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
$dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
}
+sub db_connect {
+ my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
+ or warn "Could not connect to Postgres: " . DBI->errstr;
+ if (!defined($dbh)) {
+ return undef;
+ }
+ $dbh->do('LISTEN skvupdate') or return undef;
+ return $dbh;
+}
+
sub run {
+ my $dbh = shift;
+ my $total_start = [Time::HiRes::gettimeofday];
+
@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);
+ my $token = get_oauth_bearer_token($dbh, $ua);
- # Find the newest message, and what it is linked to.
+ # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
# 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');
+ my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
$q->execute($config::invitation_channel);
my $linkref = $q->fetchrow_hashref;
my $invitation_ts = $linkref->{'ts'};
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");
+ 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;
}
+ # Store away the second-newest ID.
+ my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
+
# Find everyone who are marked as attending on Slack (via reactions).
$q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
$q->execute($config::invitation_channel, $invitation_ts);
}
# Get the list of all people in the sheet (we're going to need them soon anyway).
+ my $start = [Time::HiRes::gettimeofday];
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
+ Authorization => 'Bearer ' . $token,
+ Accept_Encoding => HTTP::Message::decodable
);
+ log_timing($start, "/spreadsheets/$tab_name");
+
my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ # Update the list of groups we've seen people in.
+ my %assignments = get_group_assignments($main_sheet_json);
+ update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
+
my %seen_names = find_where_each_name_is($main_sheet_json);
# Find duplicates.
my %slack_userid_to_real_name = ();
my %slack_userid_to_slack_name = ();
my %slack_userid_to_row = ();
+
+ $start = [Time::HiRes::gettimeofday];
$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
+ Authorization => 'Bearer ' . $token,
+ Accept_Encoding => HTTP::Message::decodable
);
+ log_timing($start, "/spreadsheets/Slack-mapping");
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;
$slack_userid_to_slack_name{$userid} = $slack_name;
}
- if (exists($seen_names{lc $slack_name})) {
+ if (exists($seen_names{sort_key($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, {
}
}
}
- 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);
+ if (scalar @slack_mapping_updates > 0) {
+ my $update = {
+ valueInputOption => 'USER_ENTERED',
+ data => \@slack_mapping_updates
+ };
+ $start = [Time::HiRes::gettimeofday];
+ $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
+ );
+ log_timing($start, "/spreadsheets/values:batchUpdate");
+ die $response->decoded_content if (!$response->is_success);
+ }
# Now that we have Slack names, we can log double-reacters.
for my $userid (keys %double) {
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})) {
+ if (!exists($seen_names{sort_key($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};
+ my $seen = $seen_names{sort_key($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 {
for my $diff (@diffs) {
my $real_name = $diff->[0];
+ my $seen = $seen_names{sort_key($real_name)};
+
+ # We've already complained about these earlier, so just skip them silently.
+ next if (scalar @$seen > 1);
+
# See if we can find them in the spreadsheet.
- my $seen = $seen_names{lc $real_name};
- die if (!defined($seen) || scalar @$seen > 1);
+ die "Could not find $real_name" if (!defined($seen));
my $rowno = $seen->[0][1];
my $colno = $seen->[0][2];
push @yellow_updates, {
}
};
+ my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
+ push @yellow_updates, {
+ updateCells => {
+ rows => \@recent_moves,
+ fields => 'userEnteredValue.stringValue',
+ range => {
+ sheetId => $config::log_tab_id,
+ startRowIndex => 4,
+ endRowIndex => 4 + scalar @recent_moves,
+ startColumnIndex => 1,
+ endColumnIndex => 2
+ }
+ }
+ };
+
# 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 $elapsed = Time::HiRes::tv_interval($total_start);
+ printf "Tok %.0f ms.\n", 1e3 * $elapsed;
}
+my $dbh = db_connect() or die;
if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
# Start with a single, forced run.
- unlink("/srv/skvidar-slack.sesse.net/marker");
- run();
+ run($dbh);
while (1) {
- if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
- unless ($!{ENOENT}) {
- warn "/srv/skvidar-slack.sesse.net/marker: $!";
- }
+ while (!defined($dbh)) {
+ print STDERR "Database connection lost, reconnecting...\n";
sleep 1;
+ $dbh = db_connect();
+ }
+ my $s = IO::Select->new($dbh->{pg_socket});
+ my @ready = $s->can_read(10.0);
+ my @exceptions = $s->has_exception(0.0);
+
+ if (scalar @exceptions > 0) {
+ $dbh->disconnect;
+ $dbh = undef;
next;
}
- eval {
- run();
- };
- if ($@) {
- warn "Died with: $@";
+ if (scalar @ready > 0) {
+ eval {
+ $dbh->{AutoCommit} = 1;
+ run($dbh);
+ $dbh->commit;
+ };
+ if ($@) {
+ warn "Died with: $@";
+ $dbh = undef;
+ }
}
- $dbh->disconnect;
}
} else {
- run();
+ run($dbh);
}
projects / skvidarsync / blobdiff