Mercurial > libervia-pubsub
annotate db/pubsub.sql @ 494:468b7cd6c344 default tip
bookmark compat: handle mapped errors:
This convert error on request (e.g. missing node) to appropriate stanza error.
author | Goffi <goffi@goffi.org> |
---|---|
date | Fri, 13 Dec 2024 12:23:47 +0100 (2 days ago) |
parents | cfa40fa108a4 |
children |
rev | line source |
---|---|
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
1 CREATE TABLE entities ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
2 entity_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
3 jid text NOT NULL UNIQUE |
87 | 4 ); |
5 | |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
6 CREATE TABLE nodes ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
7 node_id serial PRIMARY KEY, |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
8 node text NOT NULL, |
430 | 9 pep text, |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
10 node_type text NOT NULL DEFAULT 'leaf' |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
11 CHECK (node_type IN ('leaf', 'collection')), |
240
70c8bb90d75f
added access_model to config, default to 'open'
Goffi <goffi@goffi.org>
parents:
206
diff
changeset
|
12 access_model text NOT NULL DEFAULT 'open' |
350 | 13 CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')), |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
14 persist_items boolean, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
15 deliver_payloads boolean NOT NULL DEFAULT TRUE, |
403
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
367
diff
changeset
|
16 max_items integer NOT NULL DEFAULT 0 |
430 | 17 CHECK (max_items >= 0), |
478
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
18 overwrite_policy text NOT NULL DEFAULT 'original_publisher' |
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
19 CHECK (overwrite_policy IN ('original_publisher', 'any_publisher')), |
367
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
360
diff
changeset
|
20 serial_ids boolean NOT NULL DEFAULT FALSE, |
403
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
367
diff
changeset
|
21 consistent_publisher boolean NOT NULL DEFAULT FALSE, |
430 | 22 fts_language text NOT NULL DEFAULT 'generic', |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
23 send_last_published_item text NOT NULL DEFAULT 'on_sub' |
260 | 24 CHECK (send_last_published_item IN ('never', 'on_sub')), |
25 publish_model text NOT NULL DEFAULT 'publishers' | |
359
1d93f94feee3
psql (schema): fixed forgotten comma
Goffi <goffi@goffi.org>
parents:
350
diff
changeset
|
26 CHECK (publish_model IN ('publishers', 'subscribers', 'open')), |
360
6350622d9eb9
psql (schema): fixed bad semi-colon
Goffi <goffi@goffi.org>
parents:
359
diff
changeset
|
27 schema xml |
87 | 28 ); |
29 | |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
30 /* we need 2 partial indexes to manage NULL value for PEP */ |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
31 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL; |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
32 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL; |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
33 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
34 INSERT INTO nodes (node, node_type) values ('', 'collection'); |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
35 |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
36 CREATE TABLE affiliations ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
37 affiliation_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
38 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
39 node_id integer NOT NULL references nodes ON DELETE CASCADE, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
40 affiliation text NOT NULL |
329 | 41 CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')), |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
42 UNIQUE (entity_id, node_id) |
87 | 43 ); |
44 | |
243
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
45 CREATE TABLE node_groups_authorized ( |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
46 node_groups_authorized_id serial PRIMARY KEY, |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
47 node_id integer NOT NULL references nodes ON DELETE CASCADE, |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
48 groupname text NOT NULL, |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
49 UNIQUE (node_id,groupname) |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
50 ); |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
51 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
52 CREATE TABLE subscriptions ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
53 subscription_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
54 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, |
87 | 55 resource text, |
478
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
56 node_id integer REFERENCES nodes ON DELETE CASCADE, |
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
57 /* when we reference an external node (with PAM), node_id is NULL and service and node |
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
58 * are set */ |
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
59 ext_service text, |
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
60 ext_node text, |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
61 state text NOT NULL DEFAULT 'subscribed' |
430 | 62 CHECK (state IN ('subscribed', 'pending', 'unconfigured')), |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
63 subscription_type text |
430 | 64 CHECK (subscription_type IN (NULL, 'items', 'nodes')), |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
65 subscription_depth text |
430 | 66 CHECK (subscription_depth IN (NULL, '1', 'all')), |
478
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
67 public boolean NOT NULL DEFAULT FALSE, |
479 | 68 UNIQUE (entity_id, resource, node_id), |
69 UNIQUE (entity_id, ext_service, ext_node) | |
70 ); | |
87 | 71 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
72 CREATE TABLE items ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
73 item_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
74 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
75 item text NOT NULL, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
76 publisher text NOT NULL, |
430 | 77 data xml NOT NULL, |
433
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
78 data_fts tsvector, |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
79 data_fts_cfg text NOT NULL DEFAULT 'simple', |
248
50f6ee966da8
item are gotten according to item's access model in getItems
Goffi <goffi@goffi.org>
parents:
244
diff
changeset
|
80 access_model text NOT NULL DEFAULT 'open' |
329 | 81 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), |
367
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
360
diff
changeset
|
82 created timestamp with time zone NOT NULL DEFAULT now(), |
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
360
diff
changeset
|
83 updated timestamp with time zone NOT NULL DEFAULT now(), |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
84 UNIQUE (node_id, item) |
87 | 85 ); |
244 | 86 |
87 CREATE TABLE item_groups_authorized ( | |
88 item_groups_authorized_id serial PRIMARY KEY, | |
89 item_id integer NOT NULL references items ON DELETE CASCADE, | |
90 groupname text NOT NULL, | |
91 UNIQUE (item_id,groupname) | |
92 ); | |
93 | |
329 | 94 CREATE TABLE item_jids_authorized ( |
95 item_jids_authorized_id serial PRIMARY KEY, | |
96 item_id integer NOT NULL references items ON DELETE CASCADE, | |
97 jid text NOT NULL, | |
98 UNIQUE (item_id,jid) | |
99 ); | |
100 | |
101 CREATE TABLE item_languages ( | |
102 item_languages_id serial PRIMARY KEY, | |
103 item_id integer NOT NULL references items ON DELETE CASCADE, | |
104 language text NOT NULL, | |
105 UNIQUE (item_id,language) | |
106 ); | |
107 | |
301
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
108 CREATE TABLE item_categories ( |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
109 item_categories_id serial PRIMARY KEY, |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
110 item_id integer NOT NULL references items ON DELETE CASCADE, |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
111 category text NOT NULL, |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
112 UNIQUE (item_id,category) |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
113 ); |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
114 |
430 | 115 /* Full Text Search */ |
116 CREATE INDEX items_data_fts ON items USING GIN (data_fts); | |
117 | |
433
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
118 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS |
430 | 119 $$ |
120 BEGIN | |
433
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
121 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') |
430 | 122 WHERE items.node_id=new.node_id |
123 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); | |
124 RETURN new; | |
125 END; | |
126 $$ | |
127 language plpgsql; | |
128 | |
129 CREATE TRIGGER nodes_fts_language_update | |
130 AFTER UPDATE OF fts_language ON nodes | |
131 FOR EACH ROW | |
433
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
132 EXECUTE PROCEDURE update_data_fts_cfg(); |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
133 |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
134 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
135 $$ |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
136 BEGIN |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
137 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
138 RETURN new; |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
139 END |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
140 $$ |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
141 language plpgsql; |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
142 |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
143 CREATE TRIGGER items_fts_tsvector_update |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
144 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items |
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
145 FOR EACH ROW |
430 | 146 EXECUTE PROCEDURE update_data_fts(); |
147 | |
466
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
148 /* Roster */ |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
149 |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
150 CREATE TABLE roster ( |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
151 roster_id serial PRIMARY KEY, |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
152 jid text NOT NULL UNIQUE, |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
153 version text, |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
154 updated timestamp with time zone NOT NULL, |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
155 roster xml NOT NULL |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
156 ); |
0d38c3529972
psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
448
diff
changeset
|
157 |
433
920440200570
PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
430
diff
changeset
|
158 |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
159 CREATE TABLE metadata ( |
430 | 160 key text PRIMARY KEY, |
161 value text | |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
162 ); |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
163 |
478
b544109ab4c4
Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents:
466
diff
changeset
|
164 INSERT INTO metadata VALUES ('version', '10'); |