diff options
author | Vika <vika@fireburn.ru> | 2023-07-29 21:59:56 +0300 |
---|---|---|
committer | Vika <vika@fireburn.ru> | 2023-07-29 21:59:56 +0300 |
commit | 0617663b249f9ca488e5de652108b17d67fbaf45 (patch) | |
tree | 11564b6c8fa37bf9203a0a4cc1c4e9cc088cb1a5 /kittybox-rs/migrations | |
parent | 26c2b79f6a6380ae3224e9309b9f3352f5717bd7 (diff) | |
download | kittybox-0617663b249f9ca488e5de652108b17d67fbaf45.tar.zst |
Moved the entire Kittybox tree into the root
Diffstat (limited to 'kittybox-rs/migrations')
-rw-r--r-- | kittybox-rs/migrations/0001_init.sql | 62 | ||||
-rw-r--r-- | kittybox-rs/migrations/webmention/0001_init.sql | 15 |
2 files changed, 0 insertions, 77 deletions
diff --git a/kittybox-rs/migrations/0001_init.sql b/kittybox-rs/migrations/0001_init.sql deleted file mode 100644 index c9915eb..0000000 --- a/kittybox-rs/migrations/0001_init.sql +++ /dev/null @@ -1,62 +0,0 @@ -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; diff --git a/kittybox-rs/migrations/webmention/0001_init.sql b/kittybox-rs/migrations/webmention/0001_init.sql deleted file mode 100644 index 9e7a192..0000000 --- a/kittybox-rs/migrations/webmention/0001_init.sql +++ /dev/null @@ -1,15 +0,0 @@ -CREATE SCHEMA IF NOT EXISTS kittybox_webmention; - -CREATE TABLE kittybox_webmention.incoming_webmention_queue ( - id UUID PRIMARY KEY DEFAULT gen_random_uuid(), - source TEXT NOT NULL, - target TEXT NOT NULL, - recv_timestamp TIMESTAMPTZ NOT NULL DEFAULT now(), - attempts INTEGER NOT NULL DEFAULT 0 -); - -CREATE INDEX webmention_jobs_by_attempts ON kittybox_webmention.incoming_webmention_queue (attempts); - -CREATE RULE notify_incoming_webmention AS -ON INSERT TO kittybox_webmention.incoming_webmention_queue -DO ALSO NOTIFY incoming_webmention; |