Mercurial > libervia-pubsub

comparison db/pubsub.sql @ 206:274a45d2a5ab

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Implement root collection that includes all leaf nodes.
author Ralph Meijer <ralphm@ik.nu>
date Mon, 04 Aug 2008 13:47:10 +0000
parents a5d267289e92
children 70c8bb90d75f
comparison
equal deleted inserted replaced
205:e6b710bf2b24 206:274a45d2a5ab
1 create table entities ( 1 CREATE TABLE entities (
2 id serial primary key, 2 entity_id serial PRIMARY KEY,
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 id serial primary key, 7 node_id serial PRIMARY KEY,
8 node text not null unique, 8 node text NOT NULL UNIQUE,
9 persistent boolean not null default true, 9 node_type text NOT NULL DEFAULT 'leaf'
10 deliver_payload boolean not null default true, 10 CHECK (node_type IN ('leaf', 'collection')),
11 send_last_published_item text not null default 'on_sub' 11 persist_items boolean,
12 check (send_last_published_item in ('never', 'on_sub')) 12 deliver_payloads boolean NOT NULL DEFAULT TRUE,
13 send_last_published_item text NOT NULL DEFAULT 'on_sub'
14 CHECK (send_last_published_item IN ('never', 'on_sub'))
13 ); 15 );
14 16
15 create table affiliations ( 17 INSERT INTO nodes (node, node_type) values ('', 'collection');
16 id serial primary key, 18
17 entity_id integer not null references entities on delete cascade, 19 CREATE TABLE affiliations (
18 node_id integer not null references nodes on delete cascade, 20 affiliation_id serial PRIMARY KEY,
19 affiliation text not null 21 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
20 check (affiliation in ('outcast', 'publisher', 'owner')), 22 node_id integer NOT NULL references nodes ON DELETE CASCADE,
21 unique (entity_id, node_id) 23 affiliation text NOT NULL
24 CHECK (affiliation IN ('outcast', 'publisher', 'owner')),
25 UNIQUE (entity_id, node_id)
22 ); 26 );
23 27
24 create table subscriptions ( 28 CREATE TABLE subscriptions (
25 id serial primary key, 29 subscription_id serial PRIMARY KEY,
26 entity_id integer not null references entities on delete cascade, 30 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
27 resource text, 31 resource text,
28 node_id integer not null references nodes on delete cascade, 32 node_id integer NOT NULL REFERENCES nodes ON delete CASCADE,
29 subscription text not null default 'subscribed' check 33 state text NOT NULL DEFAULT 'subscribed'
30 (subscription in ('subscribed', 'pending', 'unconfigured')), 34 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
31 unique (entity_id, resource, node_id) 35 subscription_type text
36 CHECK (subscription_type IN (NULL, 'items', 'nodes')),
37 subscription_depth text
38 CHECK (subscription_depth IN (NULL, '1', 'all')),
39 UNIQUE (entity_id, resource, node_id));
40
41 CREATE TABLE items (
42 item_id serial PRIMARY KEY,
43 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
44 item text NOT NULL,
45 publisher text NOT NULL,
46 data text,
47 date timestamp with time zone NOT NULL DEFAULT now(),
48 UNIQUE (node_id, item)
32 ); 49 );
33
34 create table items (
35 id serial primary key,
36 node_id integer not null references nodes on delete cascade,
37 item text not null,
38 publisher text not null,
39 data text,
40 date timestamp with time zone not null default now(),
41 unique (node_id, item)
42 );