-- The following database schema has been tested on PostgreSQL 7.2.1.
-- It should only use SQL 92 features plus the text BLOB type.

create table users (
	name 		varchar(32) 	primary key,
	description	text		not null,
	password	varchar(32),
	login           bool            not null,
	admin	        bool            not null
);

create table instance (
	name		varchar(32)	primary key,
	description	text		not null
);

create table permission (
	instance 	varchar(32)	not null references instance(name)
					on delete cascade,
	username	varchar(32) 	not null references users(name)
					on delete cascade,
	type		char(1)		not null,

	-- constraints on the whole table:
	check ( type = 'W' or type = 'R' or type = 'O' )
);

-- permission types:
-- 'W': write permission
-- 'R': read permission
-- 'O': ownership permission

create table entry (
	id		serial		primary key,
	instance	varchar(32)	not null references instance(name)
					on delete cascade,
	day		date		not null,
	row_index	integer		not null,
	period_start	time,
	period_end	time,
	duration	interval,
	project		varchar(32)	not null,
	description	text		not null,

	-- constraints on the whole table:
	unique (instance, day, row_index)
);

create table wd_session (
	id		serial		primary key,
	skey		varchar(32)     unique,   -- may be null
	svalue		text		not null,
	schecksum       varchar(32)	not null,
	last_used       date            not null
);



grant all on users to public;
grant all on instance to public;
grant all on permission to public;
grant all on entry to public;
grant all on entry_id_seq to public;   -- PostgreSQL-specific
grant all on wd_session to public;
grant all on wd_session_id_seq to public;    -- PostgreSQL-specific