Ignore more headings.

[skvidarsync] / bin / sync.pl

diff --git a/bin/sync.pl b/bin/sync.pl
index d8866a6c493f1fb3a65b2eab06fb731d39ce6854..740d38f117e9cd1487be44278a6425222bdc37ab 100644 (file)
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -126,6 +126,7 @@ sub get_spreadsheet_name {
        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;
@@ -151,7 +152,7 @@ sub matches_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..(scalar @$ap)) {
+               for my $ai (1..(scalar @$ap - 1)) {
                        for my $bi (1..$#bp) {
                                $found = 1 if ($ap->[$ai] eq $bp[$bi]);
                        }
@@ -233,6 +234,7 @@ sub get_group_assignments {
                        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 {
@@ -374,15 +376,15 @@ sub create_move_log {
         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'};
@@ -579,6 +581,9 @@ sub run {
        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];
 
@@ -675,7 +680,7 @@ sub run {
                                        }
                                }
                        }
-                       log_timing($start, "Fuzzy-searching for Slack name “$slack_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);
@@ -828,6 +833,7 @@ sub run {
 
        my $elapsed = Time::HiRes::tv_interval($total_start);
        printf "Tok %.0f ms.\n", 1e3 * $elapsed;
+       print "\n";
 }
 
 # Initialize the handles we need for communication.
@@ -838,13 +844,15 @@ if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
        run($dbh, $ua);
 
        while (1) {
-               while (!defined($dbh)) {
+               while (!defined($dbh) || !$dbh->ping) {
                        print STDERR "Database connection lost, reconnecting...\n";
                        sleep 1;
-                       $dbh = db_connect();
+                       eval {
+                               $dbh = db_connect();
+                       };
                }
                my $s = IO::Select->new($dbh->{pg_socket});
-               my @ready = $s->can_read(10.0);
+               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) {
@@ -860,6 +868,20 @@ if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
                                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";
                }
        }
 } elsif ($#ARGV >= 0 && $ARGV[0] eq '--benchmark') {