diff db/pubsub.sql @ 87:f766e46dce0f

Initial revision
author Ralph Meijer <ralphm@ik.nu>
date Tue, 16 Nov 2004 10:37:01 +0000
parents
children 4aa29b1a8c67
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/pubsub.sql	Tue Nov 16 10:37:01 2004 +0000
@@ -0,0 +1,40 @@
+create table entities (
+    id serial primary key,
+    jid text not null unique
+);
+
+create table nodes (
+    id serial primary key,
+    node text not null unique,
+    persistent boolean not null default true,
+    deliver_payload boolean not null default true
+);
+
+create table affiliations (
+    id serial primary key,
+    entity_id integer not null references entities on delete cascade,
+    node_id integer not null references nodes on delete cascade,
+    affiliation text not null
+        check (affiliation in ('outcast', 'publisher', 'owner')),
+    unique (entity_id, node_id)
+);
+
+create table subscriptions (
+    id serial primary key,
+    entity_id integer not null references entities on delete cascade,
+    resource text,
+    node_id integer not null references nodes on delete cascade,
+    subscription text not null default 'subscribed' check
+        (subscription in ('subscribed', 'pending', 'unconfigured')),
+    unique (entity_id, resource, node_id)
+);
+
+create table items (
+    id serial primary key,
+    node_id integer not null references nodes on delete cascade,
+    item text not null,
+    publisher text not null,
+    data text,
+    date timestamp with time zone not null default now(),
+    unique (node_id, item)
+);