use DBI;
use POSIX;
use Time::HiRes;
+use IO::Select;
+use Unicode::Collate;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
require '../include/config.pm';
-my $dbh;
my @log = ();
+my $uca = Unicode::Collate->new(level => 1);
my %rgb = (
yellow => {
printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
}
+sub sort_key {
+ my $m = shift;
+ return $uca->getSortKey($m);
+}
+
sub get_oauth_bearer_token {
my ($dbh, $ua) = @_;
my $now = time();
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) {
# 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, '/v4/spreadsheets/properties');
+ 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;
}
}
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->{lc $real_name}} > 1) {
+ } elsif (scalar @{$seen_names->{sort_key($real_name)}} > 1) {
# Don't touch them.
} else {
skv_log("Fjerner at $real_name skal på trening.");
$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($dbh, $ua);
# 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.
$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);
$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 $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{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);
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