Mercurial > libervia-pubsub

comparison db/pubsub.sql @ 294:df1edebb0466

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
PEP implementation, draft (huge patch sorry): /!\ database schema has changed ! /!\ - whole PEP behaviour is not managed yet - if the stanza is delegated, PEP is assumed - fixed potential SQL injection in pgsql_storage - publish notifications manage PEP - added retract notifications (if "notify" attribute is present), with PEP handling - a publisher can't replace an item he didn't publised anymore - /!\ schema has changed, sat_pubsub_update_0_1.sql update it - sat_pubsub_update_0_1.sql also fixes bad items coming from former version of SàT
author Goffi <goffi@goffi.org>
date Sun, 16 Aug 2015 01:32:42 +0200
parents f0cd02c032b3
children bed30cef11a8
comparison
equal deleted inserted replaced
293:b96a4ac25f8b 294:df1edebb0466
3 jid text NOT NULL UNIQUE 3 jid text NOT NULL UNIQUE
4 ); 4 );
5 5
6 CREATE TABLE nodes ( 6 CREATE TABLE nodes (
7 node_id serial PRIMARY KEY, 7 node_id serial PRIMARY KEY,
8 node text NOT NULL UNIQUE, 8 node text NOT NULL,
9 pep text,
9 node_type text NOT NULL DEFAULT 'leaf' 10 node_type text NOT NULL DEFAULT 'leaf'
10 CHECK (node_type IN ('leaf', 'collection')), 11 CHECK (node_type IN ('leaf', 'collection')),
11 access_model text NOT NULL DEFAULT 'open' 12 access_model text NOT NULL DEFAULT 'open'
12 CHECK (access_model IN ('open', 'roster')), 13 CHECK (access_model IN ('open', 'roster')),
13 persist_items boolean, 14 persist_items boolean,
14 deliver_payloads boolean NOT NULL DEFAULT TRUE, 15 deliver_payloads boolean NOT NULL DEFAULT TRUE,
15 send_last_published_item text NOT NULL DEFAULT 'on_sub' 16 send_last_published_item text NOT NULL DEFAULT 'on_sub'
16 CHECK (send_last_published_item IN ('never', 'on_sub')), 17 CHECK (send_last_published_item IN ('never', 'on_sub')),
17 publish_model text NOT NULL DEFAULT 'publishers' 18 publish_model text NOT NULL DEFAULT 'publishers'
18 CHECK (publish_model IN ('publishers', 'subscribers', 'open')) 19 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
20 UNIQUE (node, pep) WHERE pep IS NOT NULL,
21 UNIQUE (node) WHERE pep IS NULL
19 ); 22 );
23
24 /* we need 2 partial indexes to manage NULL value for PEP */
25 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL;
26 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL;
20 27
21 INSERT INTO nodes (node, node_type) values ('', 'collection'); 28 INSERT INTO nodes (node, node_type) values ('', 'collection');
22 29
23 CREATE TABLE affiliations ( 30 CREATE TABLE affiliations (
24 affiliation_id serial PRIMARY KEY, 31 affiliation_id serial PRIMARY KEY,
66 item_id integer NOT NULL references items ON DELETE CASCADE, 73 item_id integer NOT NULL references items ON DELETE CASCADE,
67 groupname text NOT NULL, 74 groupname text NOT NULL,
68 UNIQUE (item_id,groupname) 75 UNIQUE (item_id,groupname)
69 ); 76 );
70 77
78 CREATE TABLE metadata (
79 key text PRIMARY KEY,
80 value text
81 );
82
83 INSERT INTO metadata VALUES ('version', '1');