Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 242:a6170637690d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
remote roster partial support
author Goffi <goffi@goffi.org>
date Sun, 27 May 2012 15:36:06 +0200
parents 70c8bb90d75f
children 42048e37699e
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,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
8 node text NOT NULL UNIQUE,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
9 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
10 CHECK (node_type IN ('leaf', 'collection')),
240
70c8bb90d75f added access_model to config, default to 'open'
Goffi <goffi@goffi.org>
parents: 206
diff changeset
11 access_model text NOT NULL DEFAULT 'open'
70c8bb90d75f added access_model to config, default to 'open'
Goffi <goffi@goffi.org>
parents: 206
diff changeset
12 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
13 persist_items boolean,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
14 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
15 send_last_published_item text NOT NULL DEFAULT 'on_sub'
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
16 CHECK (send_last_published_item IN ('never', 'on_sub'))
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
17 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
18
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
19 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
20
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
21 CREATE TABLE affiliations (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
22 affiliation_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
23 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
24 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
25 affiliation text NOT NULL
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
26 CHECK (affiliation IN ('outcast', 'publisher', 'owner')),
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
27 UNIQUE (entity_id, node_id)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
28 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
29
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
30 CREATE TABLE subscriptions (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
31 subscription_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,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
33 resource text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
34 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
35 state text NOT NULL DEFAULT 'subscribed'
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
36 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
37 subscription_type text
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
38 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
39 subscription_depth text
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
40 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
41 UNIQUE (entity_id, resource, node_id));
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
42
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
43 CREATE TABLE items (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
44 item_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
45 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
46 item text NOT NULL,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
47 publisher text NOT NULL,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
48 data text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
49 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
50 UNIQUE (node_id, item)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
51 );