Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 319:a51947371625

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pgsql storage: use item_id instead of date to order and select items
author Goffi <goffi@goffi.org>
date Sun, 03 Jan 2016 18:33:22 +0100
parents 05c875a13a62
children 98409ef42c94
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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'
295
bed30cef11a8 fixed bad queries in pubsub.sql file
Goffi <goffi@goffi.org>
parents: 294
diff changeset
19 CHECK (publish_model IN ('publishers', 'subscribers', 'open'))
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
20 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
21
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
22 /* 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
23 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
24 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
25
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
26 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
27
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
28 CREATE TABLE affiliations (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
29 affiliation_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
30 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
31 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
32 affiliation text NOT NULL
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
33 CHECK (affiliation IN ('outcast', 'publisher', 'owner')),
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
34 UNIQUE (entity_id, node_id)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
35 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
36
243
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
37 CREATE TABLE node_groups_authorized (
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
38 node_groups_authorized_id serial PRIMARY KEY,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
39 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
40 groupname text NOT NULL,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
41 UNIQUE (node_id,groupname)
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
42 );
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
43
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
44 CREATE TABLE subscriptions (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
45 subscription_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
46 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
47 resource text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
48 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
49 state text NOT NULL DEFAULT 'subscribed'
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
50 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
51 subscription_type text
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
52 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
53 subscription_depth text
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
54 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
55 UNIQUE (entity_id, resource, node_id));
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
56
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
57 CREATE TABLE items (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
58 item_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
59 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
60 item text NOT NULL,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
61 publisher text NOT NULL,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
62 data text,
248
50f6ee966da8 item are gotten according to item's access model in getItems
Goffi <goffi@goffi.org>
parents: 244
diff changeset
63 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
64 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
65 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
66 UNIQUE (node_id, item)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
67 );
244
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
68
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
69 CREATE TABLE item_groups_authorized (
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
70 item_groups_authorized_id serial PRIMARY KEY,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
71 item_id integer NOT NULL references items ON DELETE CASCADE,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
72 groupname text NOT NULL,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
73 UNIQUE (item_id,groupname)
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
74 );
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
75
301
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
76 CREATE TABLE item_categories (
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
77 item_categories_id serial PRIMARY KEY,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
78 item_id integer NOT NULL references items ON DELETE CASCADE,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
79 category text NOT NULL,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
80 UNIQUE (item_id,category)
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
81 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
82
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
83 CREATE TABLE metadata (
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
84 key text PRIMARY KEY,
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
85 value text
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
86 );
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
87
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
88 INSERT INTO metadata VALUES ('version', '1');