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 $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);
}
sub matches_name {
- my ($slack_name, $spreadsheet_name) = @_;
- if (sort_key($slack_name) eq sort_key($spreadsheet_name)) {
- return 1;
- }
+ my ($slack_name, $spreadsheet_name, $ap) = @_;
- my @ap = split /\s+/, $slack_name;
- my @bp = split /\s+/, $spreadsheet_name;
- if (scalar @ap >= 2 && scalar @bp >= 2 && sort_key($ap[0]) eq sort_key($bp[0])) {
+ # 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;
+ # }
+
+ # @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)) {
for my $bi (1..$#bp) {
- $found = 1 if (sort_key($ap[$ai]) eq sort_key($bp[$bi]));
+ $found = 1 if ($ap->[$ai] eq $bp[$bi]);
}
}
if ($found) {
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;
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 $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;
if (!defined($dbh)) {
return undef;
}
+ $dbh->{AutoCommit} = 0;
$dbh->do('LISTEN skvupdate') or return undef;
return $dbh;
}
sub run {
- my $dbh = shift;
+ 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.
- my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
+ # 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).
delete $colors{$userid};
}
- # Get the list of all people in the sheet (we're going to need them soon anyway).
+ # 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&fields=sheets/data/rowData/values/userEnteredValue',
+ 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
);
- log_timing($start, "/spreadsheets/$tab_name");
+ log_timing($start, "/spreadsheets/");
- my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ my $sheets_json = JSON::XS::decode_json($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.
my %assignments = get_group_assignments($main_sheet_json);
update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
+ $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_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,
- 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 $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'};
} 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, $invitation_ts, undef, \%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};
# 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) {
printf "Tok %.0f ms.\n", 1e3 * $elapsed;
}
+# 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.
- run($dbh);
+ run($dbh, $ua);
while (1) {
while (!defined($dbh)) {
}
if (scalar @ready > 0) {
eval {
- $dbh->{AutoCommit} = 1;
- run($dbh);
- $dbh->commit;
+ run($dbh, $ua);
};
if ($@) {
warn "Died with: $@";
}
}
}
+} elsif ($#ARGV >= 0 && $ARGV[0] eq '--benchmark') {
+ for my $i (0..9) {
+ run($dbh, $ua);
+ }
} else {
- run($dbh);
+ run($dbh, $ua);
}