1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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;
|
