Mercurial > libervia-pubsub
annotate db/pubsub.sql @ 204:b4bf0a5ce50d
Implement storage facilities for the HTTP gateway.
Author: ralphm.
Fixes #12.
One of the storage facilities is PostgreSQL based, providing persistence.
| author | Ralph Meijer <ralphm@ik.nu> |
|---|---|
| date | Wed, 16 Jul 2008 06:38:32 +0000 |
| parents | a5d267289e92 |
| children | 274a45d2a5ab |
| rev | line source |
|---|---|
| 87 | 1 create table entities ( |
| 2 id serial primary key, | |
| 3 jid text not null unique | |
| 4 ); | |
| 5 | |
| 6 create table nodes ( | |
| 7 id serial primary key, | |
| 8 node text not null unique, | |
| 9 persistent boolean not null default true, | |
|
188
a5d267289e92
Fix syntax errors in database schema.
Ralph Meijer <ralphm@ik.nu>
parents:
182
diff
changeset
|
10 deliver_payload boolean not null default true, |
|
182
4aa29b1a8c67
Add send_last_published_item configuration item to nodes table definition.
Ralph Meijer <ralphm@ik.nu>
parents:
87
diff
changeset
|
11 send_last_published_item text not null default 'on_sub' |
|
188
a5d267289e92
Fix syntax errors in database schema.
Ralph Meijer <ralphm@ik.nu>
parents:
182
diff
changeset
|
12 check (send_last_published_item in ('never', 'on_sub')) |
| 87 | 13 ); |
| 14 | |
| 15 create table affiliations ( | |
| 16 id serial primary key, | |
| 17 entity_id integer not null references entities on delete cascade, | |
| 18 node_id integer not null references nodes on delete cascade, | |
| 19 affiliation text not null | |
| 20 check (affiliation in ('outcast', 'publisher', 'owner')), | |
| 21 unique (entity_id, node_id) | |
| 22 ); | |
| 23 | |
| 24 create table subscriptions ( | |
| 25 id serial primary key, | |
| 26 entity_id integer not null references entities on delete cascade, | |
| 27 resource text, | |
| 28 node_id integer not null references nodes on delete cascade, | |
| 29 subscription text not null default 'subscribed' check | |
| 30 (subscription in ('subscribed', 'pending', 'unconfigured')), | |
| 31 unique (entity_id, resource, node_id) | |
| 32 ); | |
| 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 ); |