source: trunk/greenstone3-extensions/gsdl-as/create-tables.sql@ 12191

create table users (
    username varchar(128) primary key, 
    password text not null
);

create table subscriptions (
    id int primary key auto_increment, 
    name varchar(255), 
    email text, 
    rss bool not null default 0, 
    page bool not null default 0, 
    user varchar(128) not null references users.username, 
    
    unique(user,name)
);

create table predicates (
    id int primary key auto_increment, 
    field varchar(128) not null, 
    value varchar(255), 
    type enum("org.greenstone.gsdlas.profiles.EqualsPredicate", "org.greenstone.gsdlas.profiles.SubstringMatchPredicate", "org.greenstone.gsdlas.profiles.QueryPredicate") not null,
    
    unique(field,type,value)
);

create table subs_to_predicates (
    subscription int not null references subscriptions.id, 
    predicate int not null references predicates.id, 
    
    unique (subscription,predicate)
);

create table events (
    id int primary key auto_increment,
    timestamp datetime not null,
    content text not null
);

create table events_to_subs (
    event int not null references events.id,
    subscription int not null references subscriptions.id,

    unique (event,subscription)
);