From ecb26f1f6d44c9ff3ad2b3c5bf1dfac6a94000d7 Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Fri, 27 Oct 2023 15:09:36 +0200 Subject: [PATCH] Add SQL schema. --- skvidarlang.sql | 37 +++++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 skvidarlang.sql diff --git a/skvidarlang.sql b/skvidarlang.sql new file mode 100644 index 0000000..78e39e8 --- /dev/null +++ b/skvidarlang.sql @@ -0,0 +1,37 @@ +CREATE TABLE applied ( + channel VARCHAR NOT NULL, + ts VARCHAR NOT NULL, + name VARCHAR NOT NULL, + PRIMARY KEY (channel, ts, name) +); + +CREATE TABLE reaction_log ( + userid VARCHAR NOT NULL, + channel VARCHAR NOT NULL, + ts VARCHAR NOT NULL, + event_type VARCHAR NOT NULL, + event_ts VARCHAR NOT NULL, + reaction VARCHAR NOT NULL +); +CREATE INDEX lookup_idx ON reaction_log USING btree (channel, ts, userid, reaction, event_ts DESC); + +CREATE VIEW current_reactions AS + SELECT userid, channel, ts, reaction + FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction) + userid, channel, ts, event_type, event_ts, reaction + FROM reaction_log ORDER BY channel, ts, userid, reaction, event_ts DESC ) t1 + WHERE event_type = 'reaction_added'; + +CREATE TABLE reactions ( + userid VARCHAR NOT NULL, + channel VARCHAR NOT NULL, + ts VARCHAR NOT NULL, + reaction VARCHAR NOT NULL, + PRIMARY KEY (userid, channel, ts, 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,INSERT,DELETE ON TABLE reactions TO skvidarlang; + -- 2.39.2