-- The following database schema has been tested on MySQL 3.23

-- Note: MySQL does not support foreign keys.

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

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

create table permission (
	instance 	varchar(32)	not null,
	username	varchar(32) 	not null,
	type		char(1)		not null

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

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

create table entry (
	id		integer auto_increment	primary key,
	instance	varchar(32)	not null,
	day		date		not null,
	row_index	integer		not null,
	period_start	time,
	period_end	time,
	duration	varchar(32),    -- rem: no interval type
	project		varchar(32)	not null,
	description	text		not null,

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

create table wd_session (
        id              integer auto_increment  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 wd_session to public;
