Add SQL schema.

diff --git a/skvidarlang.sql b/skvidarlang.sql
new file mode 100644 (file)
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;
+