libervia-pubsub: db/pubsub.sql annotate

annotate db/pubsub.sql @ 294:df1edebb0466

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

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 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	4 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	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'
70c8bb90d75f added access_model to config, default to 'open' Goffi <goffi@goffi.org> parents: 206 diff changeset	13 CHECK (access_model IN ('open', 'roster')),
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,
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	16 send_last_published_item text NOT NULL DEFAULT 'on_sub'
260 f0cd02c032b3 publish model management Goffi <goffi@goffi.org> parents: 248 diff changeset	17 CHECK (send_last_published_item IN ('never', 'on_sub')),
f0cd02c032b3 publish model management Goffi <goffi@goffi.org> parents: 248 diff changeset	18 publish_model text NOT NULL DEFAULT 'publishers'
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	19 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	20 UNIQUE (node, pep) WHERE pep IS NOT NULL,
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	21 UNIQUE (node) WHERE pep IS NULL
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	22 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	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
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	35 CHECK (affiliation IN ('outcast', 'publisher', 'owner')),
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 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	37 );
f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	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 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	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 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	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,
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	64 data text,
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'
50f6ee966da8 item are gotten according to item's access model in getItems Goffi <goffi@goffi.org> parents: 244 diff changeset	66 CHECK (access_model IN ('open', 'roster')),
206 274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	67 date timestamp with time zone NOT NULL DEFAULT now(),
274a45d2a5ab Implement root collection that includes all leaf nodes. Ralph Meijer <ralphm@ik.nu> parents: 188 diff changeset	68 UNIQUE (node_id, item)
87 f766e46dce0f Initial revision Ralph Meijer <ralphm@ik.nu> parents: diff changeset	69 );
244 3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	70
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	71 CREATE TABLE item_groups_authorized (
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	72 item_groups_authorized_id serial PRIMARY KEY,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	73 item_id integer NOT NULL references items ON DELETE CASCADE,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	74 groupname text NOT NULL,
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	75 UNIQUE (item_id,groupname)
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	76 );
3ecc94407e36 item access_model (not finished) Goffi <goffi@goffi.org> parents: 243 diff changeset	77
294 df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	78 CREATE TABLE metadata (
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	79 key text PRIMARY KEY,
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	80 value text
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	81 );
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	82
df1edebb0466 PEP implementation, draft (huge patch sorry): Goffi <goffi@goffi.org> parents: 260 diff changeset	83 INSERT INTO metadata VALUES ('version', '1');

Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 294:df1edebb0466