From c8f8370de099501cdc14dd86158643ef86c95181 Mon Sep 17 00:00:00 2001
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
Date: Sun, 5 Nov 2023 15:19:17 +0100
Subject: [PATCH] Store OAuth tokens in the database instead of asking for them
 for every single sync.

---
 bin/sync.pl     | 16 +++++++++++++---
 skvidarlang.sql |  8 ++++++++
 2 files changed, 21 insertions(+), 3 deletions(-)

diff --git a/bin/sync.pl b/bin/sync.pl
index 649cfec..4d49816 100644
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -45,8 +45,15 @@ sub log_timing {
 }
 
 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",
@@ -60,7 +67,10 @@ sub get_oauth_bearer_token {
 		'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
 		'assertion' => $jws_token ]);
 	log_timing($start, '/oauth2/v4/token');
-	return JSON::XS::decode_json($response->decoded_content)->{'access_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 {
@@ -447,7 +457,7 @@ sub run {
 	$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);
+	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.
diff --git a/skvidarlang.sql b/skvidarlang.sql
index e317b3e..e1249c1 100644
--- a/skvidarlang.sql
+++ b/skvidarlang.sql
@@ -39,6 +39,13 @@ CREATE TABLE group_membership_history (
 );
 CREATE INDEX gmh_lookup_idx ON group_membership_history (channel, ts, name, change_seen DESC);
 
+CREATE TABLE oauth_tokens (
+    token VARCHAR NOT NULL,
+    acquired TIMESTAMP WITH TIME ZONE NOT NULL,
+    expiry TIMESTAMP WITH TIME ZONE NOT NULL
+);
+CREATE INDEX oauth_tokens_exp_idx ON oauth_tokens ( expiry );
+
 CREATE VIEW current_reactions AS
   SELECT userid, channel, ts, reaction
     FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
@@ -59,3 +66,4 @@ GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
 GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;
 GRANT SELECT,INSERT ON TABLE group_membership_history TO skvidarlang;
 GRANT SELECT ON TABLE current_group_membership_history TO skvidarlang;
+GRANT SELECT,INSERT ON TABLE oauth_tokens TO skvidarlang;
-- 
2.39.2