Mercurial > libervia-pubsub

comparison db/pubsub.sql @ 350:4d4575911060

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
sql (PostgreSQL): schema update: - added version check on top of update files, SQL will not be applied if the version is not the one expected - new db schema version (3) - added "presence" access_model in nodes - added "schema" column in nodes - converted items data to xml, this will allow the use of all XML functions PostgreSQL now needs to have been compiled with --with-libxml .
author Goffi <goffi@goffi.org>
date Fri, 08 Sep 2017 08:02:04 +0200
parents 98409ef42c94
children 1d93f94feee3
comparison
equal deleted inserted replaced
349:20b82fb8de02 350:4d4575911060
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', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')), 13 CHECK (access_model IN ('open', 'presence', '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'
19 CHECK (publish_model IN ('publishers', 'subscribers', 'open')) 19 CHECK (publish_model IN ('publishers', 'subscribers', 'open'))
20 schema xml;
20 ); 21 );
21 22
22 /* we need 2 partial indexes to manage NULL value for PEP */ 23 /* we need 2 partial indexes to manage NULL value for PEP */
23 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL; 24 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL;
24 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL; 25 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL;
57 CREATE TABLE items ( 58 CREATE TABLE items (
58 item_id serial PRIMARY KEY, 59 item_id serial PRIMARY KEY,
59 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, 60 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
60 item text NOT NULL, 61 item text NOT NULL,
61 publisher text NOT NULL, 62 publisher text NOT NULL,
62 data text, 63 data xml,
63 access_model text NOT NULL DEFAULT 'open' 64 access_model text NOT NULL DEFAULT 'open'
64 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), 65 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
65 date timestamp with time zone NOT NULL DEFAULT now(), 66 date timestamp with time zone NOT NULL DEFAULT now(),
66 UNIQUE (node_id, item) 67 UNIQUE (node_id, item)
67 ); 68 );
97 CREATE TABLE metadata ( 98 CREATE TABLE metadata (
98 key text PRIMARY KEY, 99 key text PRIMARY KEY,
99 value text 100 value text
100 ); 101 );
101 102
102 INSERT INTO metadata VALUES ('version', '2'); 103 INSERT INTO metadata VALUES ('version', '3');