Mercurial > libervia-pubsub
annotate db/pubsub.sql @ 367:a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
/!\ this patch updates pgqsl schema /!\
Had to set 2 features in the same patch, to avoid updating 2 times the schema.
1) creation/last modification date:
column keeping the date of creation of items is renamed from "date" to "created"
the date of last modification of items is saved in the new "updated" column
2) serial ids:
this experimental feature allows to have ids in series (i.e. 1, 2, 3, etc.) instead of UUID.
This is a convenience feature and there are some drawbacks:
- PostgreSQL sequences are used, so gaps can happen (see PostgreSQL documentation for more details)
- if somebody create an item with a future id in the series, the series will adapt, which can have undesired effect, and may lead to item fail if several items are created at the same time. For instance if next id in series is "8", and somebody hads already created item "8" and "256", the item will be created with biggest value in items +1 (i.e. 257). if 2 people want to create item in this situation, the second will fail with a conflict error.
author | Goffi <goffi@goffi.org> |
---|---|
date | Sat, 04 Nov 2017 21:31:32 +0100 |
parents | 6350622d9eb9 |
children | 1dc606612405 |
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, |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
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, |
367
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
360
diff
changeset
|
16 serial_ids boolean NOT NULL DEFAULT FALSE, |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
17 send_last_published_item text NOT NULL DEFAULT 'on_sub' |
260 | 18 CHECK (send_last_published_item IN ('never', 'on_sub')), |
19 publish_model text NOT NULL DEFAULT 'publishers' | |
359
1d93f94feee3
psql (schema): fixed forgotten comma
Goffi <goffi@goffi.org>
parents:
350
diff
changeset
|
20 CHECK (publish_model IN ('publishers', 'subscribers', 'open')), |
360
6350622d9eb9
psql (schema): fixed bad semi-colon
Goffi <goffi@goffi.org>
parents:
359
diff
changeset
|
21 schema xml |
87 | 22 ); |
23 | |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
24 /* 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
|
25 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
|
26 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
|
27 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
28 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
|
29 |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
30 CREATE TABLE affiliations ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
31 affiliation_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
32 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
|
33 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
|
34 affiliation text NOT NULL |
329 | 35 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
|
36 UNIQUE (entity_id, node_id) |
87 | 37 ); |
38 | |
243
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
39 CREATE TABLE node_groups_authorized ( |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
40 node_groups_authorized_id serial PRIMARY KEY, |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
41 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
|
42 groupname text NOT NULL, |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
43 UNIQUE (node_id,groupname) |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
44 ); |
42048e37699e
added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents:
240
diff
changeset
|
45 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
46 CREATE TABLE subscriptions ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
47 subscription_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
48 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, |
87 | 49 resource text, |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
50 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
|
51 state text NOT NULL DEFAULT 'subscribed' |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
52 CHECK (state IN ('subscribed', 'pending', 'unconfigured')), |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
53 subscription_type text |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
54 CHECK (subscription_type IN (NULL, 'items', 'nodes')), |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
55 subscription_depth text |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
56 CHECK (subscription_depth IN (NULL, '1', 'all')), |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
57 UNIQUE (entity_id, resource, node_id)); |
87 | 58 |
206
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
59 CREATE TABLE items ( |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
60 item_id serial PRIMARY KEY, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
61 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
|
62 item text NOT NULL, |
274a45d2a5ab
Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents:
188
diff
changeset
|
63 publisher text NOT NULL, |
350 | 64 data xml, |
248
50f6ee966da8
item are gotten according to item's access model in getItems
Goffi <goffi@goffi.org>
parents:
244
diff
changeset
|
65 access_model text NOT NULL DEFAULT 'open' |
329 | 66 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
|
67 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
|
68 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
|
69 UNIQUE (node_id, item) |
87 | 70 ); |
244 | 71 |
72 CREATE TABLE item_groups_authorized ( | |
73 item_groups_authorized_id serial PRIMARY KEY, | |
74 item_id integer NOT NULL references items ON DELETE CASCADE, | |
75 groupname text NOT NULL, | |
76 UNIQUE (item_id,groupname) | |
77 ); | |
78 | |
329 | 79 CREATE TABLE item_jids_authorized ( |
80 item_jids_authorized_id serial PRIMARY KEY, | |
81 item_id integer NOT NULL references items ON DELETE CASCADE, | |
82 jid text NOT NULL, | |
83 UNIQUE (item_id,jid) | |
84 ); | |
85 | |
86 CREATE TABLE item_languages ( | |
87 item_languages_id serial PRIMARY KEY, | |
88 item_id integer NOT NULL references items ON DELETE CASCADE, | |
89 language text NOT NULL, | |
90 UNIQUE (item_id,language) | |
91 ); | |
92 | |
301
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
93 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
|
94 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
|
95 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
|
96 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
|
97 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
|
98 ); |
05c875a13a62
categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents:
295
diff
changeset
|
99 |
294
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
100 CREATE TABLE metadata ( |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
101 key text PRIMARY KEY, |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
102 value text |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
103 ); |
df1edebb0466
PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
260
diff
changeset
|
104 |
367
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
360
diff
changeset
|
105 INSERT INTO metadata VALUES ('version', '4'); |