From a863a2b27902d2d8b87dae07c03f94e96d06d92b Mon Sep 17 00:00:00 2001
From: Vika <vika@fireburn.ru>
Date: Sun, 9 Jul 2023 01:39:58 +0300
Subject: Implement Postgres backend
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

A single giga-commit that took me weeks to produce. I know, this is
not exactly the best thing ever — but I wanted to experiment first
before "committing" to the implementation, so that I would produce the
best solution.
---
 kittybox-rs/migrations/0001_init.sql | 62 ++++++++++++++++++++++++++++++++++++
 1 file changed, 62 insertions(+)
 create mode 100644 kittybox-rs/migrations/0001_init.sql

(limited to 'kittybox-rs/migrations/0001_init.sql')

diff --git a/kittybox-rs/migrations/0001_init.sql b/kittybox-rs/migrations/0001_init.sql
new file mode 100644
index 0000000..c9915eb
--- /dev/null
+++ b/kittybox-rs/migrations/0001_init.sql
@@ -0,0 +1,62 @@
+CREATE SCHEMA IF NOT EXISTS kittybox;
+
+CREATE TABLE kittybox.users (
+    user_domain TEXT NOT NULL PRIMARY KEY,
+    site_name   JSONB NOT NULL DEFAULT '"Kittybox"'::jsonb,
+    webring     JSONB NOT NULL DEFAULT 'false'::jsonb
+);
+
+CREATE TABLE kittybox.mf2_json (
+    uid TEXT NOT NULL PRIMARY KEY,
+    mf2 JSONB NOT NULL,
+    owner TEXT NOT NULL -- REFERENCES kittybox.users(user_domain)
+);
+
+CREATE INDEX mf2props ON kittybox.mf2_json USING GIN (mf2);
+CREATE INDEX published_date ON kittybox.mf2_json ((mf2 #>> '{properties,published,0}'));
+
+CREATE TABLE kittybox.children (
+    parent TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
+    child  TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
+    UNIQUE(parent, child)
+);
+
+CREATE INDEX fulltext ON kittybox.mf2_json USING GIN (
+    to_tsvector('english', mf2['properties']['content'])
+);
+
+CREATE FUNCTION kittybox.set_setting(user_domain text, setting text, val anyelement) RETURNS void AS $$
+BEGIN
+EXECUTE format('INSERT INTO kittybox.users (user_domain, %I) VALUES ($1, $2) ON CONFLICT (user_domain) DO UPDATE SET %I = $2', setting, setting)
+    USING user_domain, val;
+    RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION kittybox.get_setting(user_domain text, setting text) RETURNS jsonb AS $$
+DECLARE
+  val jsonb;
+BEGIN
+EXECUTE format('SELECT %I FROM kittybox.users WHERE user_domain = $1', setting) USING user_domain INTO val;
+
+RETURN val;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION kittybox.hydrate_author(mf2 jsonb) RETURNS jsonb AS $$
+DECLARE
+  author jsonb;
+  author_uid text;
+BEGIN
+
+author_uid := mf2 #>> '{properties,author,0}';
+IF NOT (author_uid IS NULL) THEN
+   SELECT mf2_json.mf2 INTO author FROM kittybox.mf2_json WHERE uid = author_uid;
+END IF;
+IF NOT FOUND THEN
+    RETURN mf2;
+ELSE
+    RETURN jsonb_set(mf2, '{properties,author,0}', author);
+END IF;
+END;
+$$ LANGUAGE plpgsql;
-- 
cgit 1.4.1