view db/pubsub.sql @ 142:812300cdbc22

Changed behaviour of retraction of items so that only the actually deleted item ids are returned, and no exception is raised for items that didn't actually exists.
author Ralph Meijer <ralphm@ik.nu>
date Tue, 12 Jul 2005 09:23:00 +0000
parents f766e46dce0f
children 4aa29b1a8c67
line wrap: on
line source

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)
);