use DBI;
use POSIX;
use Time::HiRes;
+use IO::Select;
binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';
use utf8;
require '../include/config.pm';
-my $dbh;
my @log = ();
my %rgb = (
}
);
+sub log_timing {
+ my ($start, $msg) = @_;
+ my $elapsed = Time::HiRes::tv_interval($start);
+ printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
+}
+
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",
"iat" => $now,
});
my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
'assertion' => $jws_token ]);
- return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+ log_timing($start, '/oauth2/v4/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 {
my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
'Authorization' => 'Bearer ' . $config::slack_oauth_token
]);
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->request($req);
+ log_timing($start, '/users.info');
die $response->status_line if !$response->is_success;
my $user_json = JSON::XS::decode_json($response->decoded_content);
my $update = {
requests => \@requests
};
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post(
'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
Content => JSON::XS::encode_json($update),
Content_type => 'application/json;charset=UTF-8',
Authorization => 'Bearer ' . $token
);
+ log_timing($start, '/spreadsheets/values:batchUpdate');
die $response->decoded_content if !$response->is_success;
}
my ($ua, $token, $wanted_sheet_title) = @_;
# 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, '/spreadsheets/properties');
my $sheets_json = JSON::XS::decode_json($response->decoded_content);
my ($tab_name, $tab_id);
for my $sheet (@{$sheets_json->{'sheets'}}) {
user => $userid,
text => $msg
};
+ my $start = [Time::HiRes::gettimeofday];
my $response = $ua->post(
'https://slack.com/api/chat.postEphemeral',
Content => JSON::XS::encode_json($content),
Content_type => 'application/json;charset=UTF-8',
Authorization => 'Bearer ' . $config::slack_oauth_token
);
+ log_timing($start, 'chat.postEphemeral');
die $response->status_line if !$response->is_success;
my $msg_json = JSON::XS::decode_json($response->decoded_content);
die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
$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 $start = [Time::HiRes::gettimeofday];
+ 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($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.
}
# 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.
my %slack_userid_to_real_name = ();
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
+ 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 $cur_row = 5;
}
}
}
- my $update = {
- valueInputOption => 'USER_ENTERED',
- data => \@slack_mapping_updates
- };
- $response = $ua->post(
- 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
- Content => JSON::XS::encode_json($update),
- Content_type => 'application/json;charset=UTF-8',
- Authorization => 'Bearer ' . $token
- );
- die $response->decoded_content if (!$response->is_success);
+ if (scalar @slack_mapping_updates > 0) {
+ my $update = {
+ valueInputOption => 'USER_ENTERED',
+ data => \@slack_mapping_updates
+ };
+ $start = [Time::HiRes::gettimeofday];
+ $response = $ua->post(
+ 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
+ Content => JSON::XS::encode_json($update),
+ Content_type => 'application/json;charset=UTF-8',
+ Authorization => 'Bearer ' . $token
+ );
+ log_timing($start, "/spreadsheets/values:batchUpdate");
+ die $response->decoded_content if (!$response->is_success);
+ }
# Now that we have Slack names, we can log double-reacters.
for my $userid (keys %double) {
sheet_batch_update($ua, $token, \@yellow_updates);
$dbh->commit;
- my $elapsed = Time::HiRes::tv_interval($start);
+ my $elapsed = Time::HiRes::tv_interval($total_start);
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