);
CREATE INDEX lookup_idx ON reaction_log USING btree (channel, ts, userid, reaction, event_ts DESC);
+CREATE TABLE message_sheet_link (
+ channel VARCHAR NOT NULL,
+ ts VARCHAR NOT NULL,
+ sheet_title VARCHAR NOT NULL,
+ PRIMARY KEY (channel, ts)
+);
+
CREATE VIEW current_reactions AS
SELECT userid, channel, ts, reaction
FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
GRANT SELECT,INSERT,DELETE ON TABLE applied TO skvidarlang;
GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
GRANT SELECT ON TABLE current_reactions TO skvidarlang;
+GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
+
if (exists($json->{'event'}) && exists($json->{'event'}{'type'})) {
my $type = $json->{'event'}{'type'};
+ my $user = $json->{'event'}{'user'};
+
+ if ($type eq 'message') {
+ if ($json->{'event'}{'message'}{'text'} =~ /(20\d{2}-\d{2}-\d{2})/) {
+ # TODO: What if edits happen out-of-order?
+ my $date = $1;
+ my $channel = $json->{'event'}{'channel'};
+ my $ts = $json->{'event'}{'ts'};
+ print "Matching message {$channel, $ts} to date $date\n";
+ $dbh->do('INSERT INTO message_sheet_link (channel, ts, sheet_title) VALUES (?,?,?)', undef,
+ $channel, $ts, $date);
+ } else {
+ print STDERR "No date found in message, ignoring\n";
+ }
+ exit;
+ }
+
my $reaction = $json->{'event'}{'reaction'};
my $channel = $json->{'event'}{'item'}{'channel'};
my $ts = $json->{'event'}{'item'}{'ts'};
my $event_ts = $json->{'event'}{'event_ts'};
- my $user = $json->{'event'}{'user'};
if (!defined($channel) || !defined($ts)) {
print STDERR "Not reacting to a message; ignoring.\n";