Mercurial > libervia-pubsub

comparison db/pubsub.sql @ 329:98409ef42c94

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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');