Cache /properties; it was getting untenably slow for something that nearly never...

diff --git a/bin/sync.pl b/bin/sync.pl
index 9e00dc491eb658ed42e79cd00f84ed70722e37ce..e913c16d8613f07625c05d9cf79483b66428c99a 100644 (file)
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -251,7 +251,7 @@ sub update_assignment_db {
 }
 
 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];
@@ -267,6 +267,8 @@ sub get_spreadsheet_with_title {
                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);
                }
        }
@@ -495,17 +497,21 @@ sub run {
        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->execute($config::invitation_channel, $invitation_ts);

diff --git a/bin/ws.pl b/bin/ws.pl
index fd01cb551bf35ebab43c4327bf65f4db5584b25a..79394f3f5a8b16ca36ece86f0d41e28c977f303a 100644 (file)
--- a/bin/ws.pl
+++ b/bin/ws.pl
@@ -119,6 +119,8 @@ sub handle_event {
                        print STDERR "Matching message {$channel, $ts} to date $date\n";
                        $dbh->do('INSERT INTO message_sheet_link (channel, ts, sheet_title) VALUES (?,?,?)', undef,
                                $channel, $ts, $date);
+                       # Blow the cache.
+                       $dbh->do('UPDATE message_sheet_link SET tab_name=NULL, tab_id=NULL WHERE channel=?', undef, $channel);
                } else {
                        print STDERR "No date found in message, ignoring\n";
                }

diff --git a/skvidarlang.sql b/skvidarlang.sql
index 5f0db3b8ec73e6a450f2b6bcda5222ebaa2b8bfc..020d066908931475f44699316ed51375a55d51cf 100644 (file)
--- a/skvidarlang.sql
+++ b/skvidarlang.sql
@@ -20,6 +20,8 @@ CREATE TABLE message_sheet_link (
     channel VARCHAR NOT NULL,
     ts VARCHAR NOT NULL,
     sheet_title VARCHAR NOT NULL,
+    tab_name VARCHAR,  -- Cache; blown away every time we see a new message.
+    tab_id VARCHAR,   -- Ditto.
     PRIMARY KEY (channel, ts)
 );