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