use DBI;
use POSIX;
use Time::HiRes;
+use IO::Select;
+use Unicode::Collate;
+use IO::Socket::SSL;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
require '../include/config.pm';
-my $dbh;
+my $global_ctx = IO::Socket::SSL::SSL_Context->new(
+ SSL_session_cache_size => 100, # Probably overkill.
+);
+IO::Socket::SSL::set_default_context($global_ctx);
+
my @log = ();
+my $uca = Unicode::Collate->new(level => 1);
my %rgb = (
yellow => {
blue => 0,
alpha => 1
},
+ orange => {
+ red => 0xf9 / 255.0,
+ green => 0xcb / 255.0,
+ blue => 0x9c / 255.0,
+ alpha => 1
+ },
blue => {
red => 0,
green => 1,
}
);
+sub log_timing {
+ my ($start, $msg) = @_;
+ my $elapsed = Time::HiRes::tv_interval($start);
+ printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
+}
+
+# Unicode::Collate is seemingly slow, so add a cache for each name part
+# (which, of course, only works for equality). Helps especially in
+# --daemon mode, where even the first request gets a warm cache.
+my %sort_key_cache = ();
+my $sort_key_sp = $uca->getSortKey(' ');
+
+sub sort_key {
+ my $m = shift;
+ my $sk;
+ for my $part (split /\s+/, $m) {
+ my $psk = \$sort_key_cache{$part};
+ if (!defined($$psk)) {
+ $$psk = $uca->getSortKey($part);
+ }
+ if (defined($sk)) {
+ $sk .= $sort_key_sp;
+ $sk .= $$psk;
+ } else {
+ $sk = $$psk;
+ }
+ }
+ return $sk;
+}
+
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);
my $name = $cell->{'userEnteredValue'}{'stringValue'};
return undef if (!defined($name));
return undef if ($name =~ /^G[1-4]\.[1-5]/);
+ return undef if ($name =~ /^1r/);
$name =~ s/đ//;
$name =~ s/\(.*\)//g;
$name =~ s/\[.*\]//g;
}
sub matches_name {
- my ($slack_name, $spreadsheet_name) = @_;
- if (lc($slack_name) eq lc($spreadsheet_name)) {
- return 1;
- }
+ my ($slack_name, $spreadsheet_name, $ap) = @_;
+
+ # No need to check for an exact match; we already did that through $seen_names.
+ # 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])) {
+ # @ap is precalculated by the caller.
+ # my @ap = map { sort_key($_) } split /\s+/, $slack_name;
+ my @bp = map { sort_key($_) } split /\s+/, $spreadsheet_name;
+ if (scalar @$ap >= 2 && scalar @bp >= 2 && $ap->[0] eq $bp[0]) {
# First name matches, try to match some surname
my $found = 0;
- for my $ai (1..$#ap) {
+ for my $ai (1..(scalar @$ap - 1)) {
for my $bi (1..$#bp) {
- $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
+ $found = 1 if ($ap->[$ai] eq $bp[$bi]);
}
}
if ($found) {
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;
}
my $json = shift;
my %assignments = ();
- my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $rows = $json->{'data'}[0]{'rowData'};
my @curr_groups = ();
for my $row (@$rows) {
my $col = 0;
next if $contents =~ /^LT\b/;
next if $contents =~ /^400m/;
next if $contents =~ /^546m/;
+ next if $contents =~ /^1r/;
if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye lĂžpere.*)/) {
$curr_groups[$col] = $1;
} else {
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);
$q->execute($channel, $ts, $name, undef);
}
}
- $dbh->commit;
}
sub get_spreadsheet_with_title {
- my ($ua, $token, $wanted_sheet_title) = @_;
+ my ($dbh, $ua, $token, $invitation_ts, $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'}}) {
my $sheet_id = $sheet->{'properties'}{'sheetId'};
if ($title =~ /\Q$wanted_sheet_title\E/) {
# skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket â$titleâ (fane-ID $sheet_id).");
+ $dbh->do('UPDATE message_sheet_link SET tab_name=?, tab_id=? WHERE channel=? AND ts=?',
+ undef, $title, $sheet_id, $config::invitation_channel, $invitation_ts);
return ($title, $sheet_id);
}
}
my $json = shift;
my %seen_names = ();
- my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $rows = $json->{'data'}[0]{'rowData'};
my $rowno = 3;
for my $row (@$rows) {
my $colno = 0;
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;
}
# for each user; earlier ones are irrelevant and don't count. But it
# doesn't deduplicate across reactions. Meh.)
sub create_reaction_log {
- my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
+ my ($dbh, $channel, $invitation_ts, $interval, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
- my $q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\',\'blue_heart\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - interval \'8 hours\' order by event_ts desc limit 50');
- $q->execute($config::invitation_channel, $invitation_ts);
+ my $q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\',\'blue_heart\',\'orange_heart\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - ?::interval order by event_ts desc limit 50');
+ $q->execute($channel, $invitation_ts, $interval);
my @recent_changes = ();
while (my $ref = $q->fetchrow_hashref) {
my $msg = $ref->{'event_ts'};
$msg .= 'đź';
} elsif ($ref->{'reaction'} eq 'blue_heart') {
$msg .= 'đ';
+ } elsif ($ref->{'reaction'} eq 'orange_heart') {
+ $msg .= 'đ§Ą';
} else {
$msg .= 'â€ïž';
}
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)
+FROM ( SELECT * FROM current_group_membership_history WHERE channel=? AND ts=? ) g_old
+ FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE channel=? AND 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);
+ $q->execute($config::invitation_channel, $prev_invitation_ts, $config::invitation_channel, $invitation_ts);
my @recent_moves = ();
while (my $ref = $q->fetchrow_hashref) {
my $name = $ref->{'name'};
}
for my $real_name (keys %$have_colors) {
next if (exists($want_colors->{$real_name}));
- if (!exists($seen_names->{lc $real_name})) {
+ my $sk = sort_key($real_name);
+ if (!exists($seen_names->{$sk})) {
# 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->{lc $real_name}} > 1) {
+ } elsif (scalar @{$seen_names->{$sk}} > 1) {
# Don't touch them.
} else {
skv_log("Fjerner at $real_name skal pÄ trening.");
}
sub possibly_nag_user {
- my ($dbh, $ua, $userid) = @_;
+ my ($dbh, $ua, $userid, $invitation_ts, $group, $slack_userid_to_slack_name) = @_;
+
+ my $slack_name = $slack_userid_to_slack_name->{$userid};
# See if we've nagged this user before.
- my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
- $q->execute($userid);
+ my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=? AND ts=?');
+ $q->execute($userid, $invitation_ts);
if (defined($q->fetchrow_hashref)) {
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!";
+ my $msg;
+ if (!defined($group)) {
+ $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 Slack-melding til $slack_name ($userid) for Ă„ spĂžrre om gruppe.");
+ } elsif ($group eq '(flere grupper)') {
+ $msg = "Hei! Du meldte deg akkurat pÄ trening, men du ser ut til Ä stÄ i flere forskjellige grupper 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 retter der. Om du er usikker pÄ hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen pÄ trening!";
+ skv_log("Sender Slack-melding til $slack_name ($userid) for Ă„ spĂžrre om gruppe.");
+ } else {
+ $msg = "Hei! Du er pÄmeldt gruppe *$group*. Om dette er feil, gÄ gjerne inn og endre pÄ https://regneark.skvidar.run/. Vi gleder oss til Ä se deg pÄ trening!";
+ skv_log("Sender Slack-melding om at $slack_name ($userid) er i gruppe $group.");
+ }
- 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/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 $msg_json = JSON::XS::decode_json($response->decoded_content);
die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
# Mark that we've sent the message, so it won't happen again.
- $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
+ $dbh->do('INSERT INTO users_nagged (userid, ts, last_nag) VALUES (?, ?, CURRENT_TIMESTAMP)', undef, $userid, $invitation_ts);
+}
+
+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->{AutoCommit} = 0;
+ $dbh->do('LISTEN skvupdate') or return undef;
+ return $dbh;
}
sub run {
- my $start = [Time::HiRes::gettimeofday];
+ my ($dbh, $ua) = @_;
+ 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);
+ # For the logic on the âappliedâ table below.
+ $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
+
+ my $token = get_oauth_bearer_token($dbh, $ua);
# 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 $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;
- }
+ my $tab_name = $linkref->{'tab_name'};
+ my $tab_id = $linkref->{'tab_id'};
# Store away the second-newest ID.
my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
+ if (!defined($tab_name) || !defined($tab_id)) {
+ ($tab_name, $tab_id) = get_spreadsheet_with_title($dbh, $ua, $token, $invitation_ts, $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,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
+ $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\', \'orange_heart\')');
$q->execute($config::invitation_channel, $invitation_ts);
my @attending_userids = ();
my %colors = ();
my $userid = $ref->{'userid'};
push @attending_userids, $userid;
if ($ref->{'reaction'} eq 'blue_heart') {
- if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
+ if (exists($colors{$userid}) && $colors{$userid} ne 'blue') {
$double{$userid} = 1;
}
$colors{$userid} = 'blue';
+ } elsif ($ref->{'reaction'} eq 'orange_heart') {
+ if (exists($colors{$userid}) && $colors{$userid} ne 'orange') {
+ $double{$userid} = 1;
+ }
+ $colors{$userid} = 'orange';
} else {
- if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
+ if (exists($colors{$userid}) && $colors{$userid} ne 'yellow') {
$double{$userid} = 1;
}
$colors{$userid} = 'yellow';
delete $colors{$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
+ # Get the list of all people in the sheet (we're going to need them soon).
+ # Also get the Slack mapping when we're doing an API request 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&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
+ Authorization => 'Bearer ' . $token,
+ Accept_Encoding => HTTP::Message::decodable
);
- my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ log_timing($start, "/spreadsheets/");
+
+ my $sheets_json = JSON::XS::decode_json($response->decoded_content);
+ if (!exists($sheets_json->{'sheets'})) {
+ die "Missing sheets (error response?): " . $response->decoded_content;
+ }
+ my $main_sheet_json = $sheets_json->{'sheets'}[0];
+ my $mapping_sheet_json = $sheets_json->{'sheets'}[1];
# Update the list of groups we've seen people in.
+ $start = [Time::HiRes::gettimeofday];
my %assignments = get_group_assignments($main_sheet_json);
+ log_timing($start, "Parsing group assignments");
+ $start = [Time::HiRes::gettimeofday];
update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
+ log_timing($start, "Updating assignments in database");
+ $start = [Time::HiRes::gettimeofday];
my %seen_names = find_where_each_name_is($main_sheet_json);
+ log_timing($start, "Making sort key reverse mapping");
# Find duplicates.
for my $name (sort keys %seen_names) {
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 $mapping_sheet_rows = $mapping_sheet_json->{'data'}[0]{'rowData'};
my $cur_row = 5;
for my $row (@$mapping_sheet_rows) {
my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
$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, {
} 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'};
+ my $main_sheet_rows = $main_sheet_json->{'data'}[0]{'rowData'};
+ $start = [Time::HiRes::gettimeofday];
+ my @ap = map { sort_key($_) } split /\s+/, $slack_name; # Precalc for matches_name().
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)) {
+ if (defined($name) && matches_name($slack_name, $name, \@ap)) {
push @candidates, $name;
}
}
}
+ log_timing($start, "Fuzzy-searching for Slack name $slack_name");
if ($#candidates == -1) {
skv_log("$slack_name ($userid) er pÄmeldt pÄ Slack, men fant ikke et regneark-navn for dem.");
- possibly_nag_user($dbh, $ua, $userid);
+ possibly_nag_user($dbh, $ua, $userid, $invitation_ts, undef, \%slack_userid_to_slack_name);
} elsif ($#candidates == 0) {
my $name = $candidates[0];
$slack_userid_to_real_name{$userid} = $name;
}
}
}
- 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) {
skv_log("$name er pÄmeldt flere steder pÄ Slack; vet ikke hvilken som skal brukes.");
}
+ # ...and possibly send welcome messages to remind them of groups.
+ for my $userid (@attending_userids) {
+ my $real_name = $slack_userid_to_real_name{$userid};
+ next if (!defined($real_name));
+ my $group = $assignments{$real_name};
+ next if (!defined($group));
+ possibly_nag_user($dbh, $ua, $userid, $invitation_ts, $group, \%slack_userid_to_slack_name);
+ }
+
# Find the list of names to mark yellow.
my %want_colors = ();
- my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $main_sheet_rows = $main_sheet_json->{'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})) {
+ my $sk = sort_key($real_name);
+ if (!exists($seen_names{$sk})) {
# 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{$sk};
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 {
# Find the list of names we already marked yellow.
my %have_colors = ();
- $dbh->{AutoCommit} = 0;
- $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
$q->execute($config::invitation_channel, $invitation_ts);
while (my $ref = $q->fetchrow_hashref) {
for my $diff (@diffs) {
my $real_name = $diff->[0];
- my $seen = $seen_names{lc $real_name};
+ 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);
}
}
- my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
+ my @recent_changes = create_reaction_log($dbh, $config::invitation_channel, $invitation_ts, '8 hours', \%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,
+ startRowIndex => 3,
+ endRowIndex => 3 + scalar @recent_changes,
startColumnIndex => 0,
endColumnIndex => 1
}
sheet_batch_update($ua, $token, \@yellow_updates);
$dbh->commit;
- my $elapsed = Time::HiRes::tv_interval($start);
+ my $elapsed = Time::HiRes::tv_interval($total_start);
printf "Tok %.0f ms.\n", 1e3 * $elapsed;
+ print "\n";
}
+# Initialize the handles we need for communication.
+my $dbh = db_connect() or die;
+my $ua = LWP::UserAgent->new(agent => 'SKVidarLang/1.0', keep_alive => 50);
if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
# Start with a single, forced run.
- unlink("/srv/skvidar-slack.sesse.net/marker");
- run();
+ run($dbh, $ua);
while (1) {
- if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
- unless ($!{ENOENT}) {
- warn "/srv/skvidar-slack.sesse.net/marker: $!";
- }
+ while (!defined($dbh) || !$dbh->ping) {
+ print STDERR "Database connection lost, reconnecting...\n";
sleep 1;
+ eval {
+ $dbh = db_connect();
+ };
+ }
+ my $s = IO::Select->new($dbh->{pg_socket});
+ my @ready = $s->can_read(150.0); # slack.com HTTP timeout is ~3 minutes, sheets.googleapis.com is ~4 minutes.
+ 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 {
+ run($dbh, $ua);
+ };
+ if ($@) {
+ warn "Died with: $@";
+ $dbh = undef;
+ }
+ } else {
+ # Keep the connections alive and the token in the database fresh.
+ # (The two URLs we use don't really exist. Note that the first time,
+ # we might be making the initial connection to slack.com, since it's
+ # not a given that run() needed to talk to them.)
+ get_oauth_bearer_token($dbh, $ua);
+ $dbh->commit;
+ #my $start = [Time::HiRes::gettimeofday];
+ $ua->get('https://sheets.googleapis.com/ping');
+ #log_timing($start, 'sheets.googleapis.com (keepalive)');
+ #$start = [Time::HiRes::gettimeofday];
+ $ua->get('https://slack.com/api/ping');
+ #log_timing($start, 'slack.com (keepalive)');
+ #print STDERR "\n";
}
- $dbh->disconnect;
+ }
+} elsif ($#ARGV >= 0 && $ARGV[0] eq '--benchmark') {
+ for my $i (0..9) {
+ run($dbh, $ua);
}
} else {
- run();
+ run($dbh, $ua);
}