Mercurial > libervia-pubsub
comparison db/pubsub.sql @ 329:98409ef42c94
PostGreSQL: schema update:
access models changes:
- renamed roster to publisher-roster, as roster is in fact the pubsub service's roster, so it was badly used
- added whitelist, publish-only
- new non standard (yet?) self-publisher. It is planed to allow publisher to publish, update and delete only its own items
- roster => publisher-roster renaming, and whitelist has been added to item access model too. publish-only and self-publisher don't make sense for items
added "member" to affiliations (will be used for whitelist)
news tables item_jids_authorized and item_languages.
updated to version 2
author | Goffi <goffi@goffi.org> |
---|---|
date | Sun, 26 Mar 2017 20:33:18 +0200 |
parents | 05c875a13a62 |
children | 4d4575911060 |
comparison
equal
deleted
inserted
replaced
328:ae37289007c3 | 329:98409ef42c94 |
---|---|
8 node text NOT NULL, | 8 node text NOT NULL, |
9 pep text, | 9 pep text, |
10 node_type text NOT NULL DEFAULT 'leaf' | 10 node_type text NOT NULL DEFAULT 'leaf' |
11 CHECK (node_type IN ('leaf', 'collection')), | 11 CHECK (node_type IN ('leaf', 'collection')), |
12 access_model text NOT NULL DEFAULT 'open' | 12 access_model text NOT NULL DEFAULT 'open' |
13 CHECK (access_model IN ('open', 'roster')), | 13 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')), |
14 persist_items boolean, | 14 persist_items boolean, |
15 deliver_payloads boolean NOT NULL DEFAULT TRUE, | 15 deliver_payloads boolean NOT NULL DEFAULT TRUE, |
16 send_last_published_item text NOT NULL DEFAULT 'on_sub' | 16 send_last_published_item text NOT NULL DEFAULT 'on_sub' |
17 CHECK (send_last_published_item IN ('never', 'on_sub')), | 17 CHECK (send_last_published_item IN ('never', 'on_sub')), |
18 publish_model text NOT NULL DEFAULT 'publishers' | 18 publish_model text NOT NULL DEFAULT 'publishers' |
28 CREATE TABLE affiliations ( | 28 CREATE TABLE affiliations ( |
29 affiliation_id serial PRIMARY KEY, | 29 affiliation_id serial PRIMARY KEY, |
30 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, | 30 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, |
31 node_id integer NOT NULL references nodes ON DELETE CASCADE, | 31 node_id integer NOT NULL references nodes ON DELETE CASCADE, |
32 affiliation text NOT NULL | 32 affiliation text NOT NULL |
33 CHECK (affiliation IN ('outcast', 'publisher', 'owner')), | 33 CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')), |
34 UNIQUE (entity_id, node_id) | 34 UNIQUE (entity_id, node_id) |
35 ); | 35 ); |
36 | 36 |
37 CREATE TABLE node_groups_authorized ( | 37 CREATE TABLE node_groups_authorized ( |
38 node_groups_authorized_id serial PRIMARY KEY, | 38 node_groups_authorized_id serial PRIMARY KEY, |
59 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, | 59 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, |
60 item text NOT NULL, | 60 item text NOT NULL, |
61 publisher text NOT NULL, | 61 publisher text NOT NULL, |
62 data text, | 62 data text, |
63 access_model text NOT NULL DEFAULT 'open' | 63 access_model text NOT NULL DEFAULT 'open' |
64 CHECK (access_model IN ('open', 'roster')), | 64 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), |
65 date timestamp with time zone NOT NULL DEFAULT now(), | 65 date timestamp with time zone NOT NULL DEFAULT now(), |
66 UNIQUE (node_id, item) | 66 UNIQUE (node_id, item) |
67 ); | 67 ); |
68 | 68 |
69 CREATE TABLE item_groups_authorized ( | 69 CREATE TABLE item_groups_authorized ( |
70 item_groups_authorized_id serial PRIMARY KEY, | 70 item_groups_authorized_id serial PRIMARY KEY, |
71 item_id integer NOT NULL references items ON DELETE CASCADE, | 71 item_id integer NOT NULL references items ON DELETE CASCADE, |
72 groupname text NOT NULL, | 72 groupname text NOT NULL, |
73 UNIQUE (item_id,groupname) | 73 UNIQUE (item_id,groupname) |
74 ); | |
75 | |
76 CREATE TABLE item_jids_authorized ( | |
77 item_jids_authorized_id serial PRIMARY KEY, | |
78 item_id integer NOT NULL references items ON DELETE CASCADE, | |
79 jid text NOT NULL, | |
80 UNIQUE (item_id,jid) | |
81 ); | |
82 | |
83 CREATE TABLE item_languages ( | |
84 item_languages_id serial PRIMARY KEY, | |
85 item_id integer NOT NULL references items ON DELETE CASCADE, | |
86 language text NOT NULL, | |
87 UNIQUE (item_id,language) | |
74 ); | 88 ); |
75 | 89 |
76 CREATE TABLE item_categories ( | 90 CREATE TABLE item_categories ( |
77 item_categories_id serial PRIMARY KEY, | 91 item_categories_id serial PRIMARY KEY, |
78 item_id integer NOT NULL references items ON DELETE CASCADE, | 92 item_id integer NOT NULL references items ON DELETE CASCADE, |
83 CREATE TABLE metadata ( | 97 CREATE TABLE metadata ( |
84 key text PRIMARY KEY, | 98 key text PRIMARY KEY, |
85 value text | 99 value text |
86 ); | 100 ); |
87 | 101 |
88 INSERT INTO metadata VALUES ('version', '1'); | 102 INSERT INTO metadata VALUES ('version', '2'); |