# $Id: create_tables.mysql,v 1.9 2008-02-07 14:32:46 mike Exp $

# Please read "../doc/data-model" before this file.
#
# Create the tables as follows:
#	mysql -u kr_admin -p'kr-adm_3636' kr < create_tables.mysql
#
#   ###	I have not even though about indexes and suchlike.
#
# In all these tables, fields called "tag" are short strings to be
# compared with other strings by computer, while fields called "name"
# are longer, mixed-case, may include spaces and are intended to be
# read by humans.

drop table if exists genre;
create table genre (
	id		int not null auto_increment,
	tag		text,
	name		text,
	primary key(id)
);

# "mformat" is short for "metadata format"
drop table if exists mformat;
create table mformat (
	id		int not null auto_increment,
	genre_id	int,	# default genre for this mformat
	name		text,
	uri		text,
	primary key(id),
	foreign key(genre_id) references genre(id)
);

drop table if exists service_type;
create table service_type (
	id		int not null auto_increment,
	tag		text,
	name		text,
	plugin		text,
	priority	int,
	# Each service-type also need special-case code in the resolver
	primary key(id)
);

# Many-to-many link table between genre and service_type
drop table if exists genre_service_type;
create table genre_service_type (
	genre_id	int,
	service_type_id	int,
	foreign key(genre_id) references genre(id),
	foreign key(service_type_id) references service_type(id)
);

drop table if exists provider;
create table provider (
	id		int not null auto_increment,
	name		text,
	priority	int,
	contact		text,
	primary key(id)
);

drop table if exists service;
create table service (
	id		int not null auto_increment,
	service_type_id	int,
	provider_id	int,
	tag		text, # misnamed: really plugin_name
	name		text,
	priority	int,
	# These recipes will probably not be general enough
	url_recipe	text,
	need_auth	bool,
	auth_recipe	text,
	disabled	bool,
	primary key(id),
	foreign key(service_type_id) references service_type(id),
	foreign key(provider_id) references provider(id)
);

drop table if exists serial;
create table serial (
	id		int not null auto_increment,
	name		text,
	issn		text,
	primary key(id)
);

# Implements the repeating "alias" field for serial objects
drop table if exists serial_alias;
create table serial_alias (
	id		int not null auto_increment,
	serial_id	int,
	alias		text,
	primary key(id),
	foreign key(serial_id) references serial(id)
);

# Many-to-many link table between service and serial
drop table if exists service_serial;
create table service_serial (
	service_id	int,
	serial_id	int,
	foreign key(service_id) references service(id),
	foreign key(serial_id) references serial(id)
);

drop table if exists identity;
create table identity (
	id		int not null auto_increment,
	name		text,
	level		text,
	parent_id	int,	# NULL for no parent, otherwise an identity.id
	primary key(id),
	foreign key(parent_id) references identity(id)
);

# Many-to-many link table between service and identity
drop table if exists credentials;
create table credentials (
	id		int not null auto_increment,
	service_id	int,
	identity_id	int,
	primary key(id),
	foreign key(service_id) references service(id),
	foreign key(identity_id) references identity(id)
);

# Provides multiple name=value elements for the credentials table
drop table if exists credentials_details;
create table credentials_details (
	id		int not null auto_increment,
	credentials_id	int,
	name		text,
	value		text,
	primary key(id),
	foreign key(credentials_id) references credentials(id)
);

drop table if exists sid;
create table sid (
	id		int not null auto_increment,
	tag		text,
	# A single recipe string will probably not be general enough
	recipe		text,
	primary key(id)
);

drop table if exists source;
create table source (
	id		int not null auto_increment,
	sid_id		int,
	name		text,
	url		text,
	encoding	text,
	primary key(id),
	foreign key(sid_id) references sid(id)
);

drop table if exists config;
create table config (
	id		int not null auto_increment,
	name		varchar(64) not null,
	value		text not null,
	primary key(id)
);

drop table if exists domain;
create table domain (
	id		int not null auto_increment,
	domain		text,
	status		int,
	primary key(id)
);

# The next two tables allow the values of fields in an OpenURL to
# affect the selection of service-types and services that are used to
# resolve them.  Each such rule applies only when the specified field
# has the specified value.  When a rule files, it either allows only
# the service-types or services whose tags listed in the
# space-separated "tags" field (the default) or denies those
# service-types or services (if the "deny" field is true).
#
drop table if exists service_type_rule;
create table service_type_rule (
	id		int not null auto_increment,
	fieldname	varchar(255),
	value		varchar(255),
	deny		bool,
	tags		text,
	primary key(id),
	key(fieldname, value)
);

drop table if exists service_rule;
create table service_rule (
	id		int not null auto_increment,
	fieldname	varchar(255),
	value		varchar(255),
	deny		bool,
	tags		text,
	primary key(id),
	key(fieldname, value)
);


# ----------------------------------------------------------------------------
# Everything below the cut is used by the Admin UI rather than the resolver.

# Rows in the "site" table represent a sub-site of the Admin UI,
# e.g. dbc.resolver.indexdata.com or talis.resolver.indexdata.com.
# This table will have many more fields by the time we're done, by
# which the per-site customisations are achieved.
#
drop table if exists site;
create table site (
	id		int not null auto_increment,
	tag		varchar(255), # not null
	name		text, # not null
	bg_colour	text,
	email_address	varchar(255), # not null

	primary key(id),
	key(tag)
);

# Sessions are site-specific
drop table if exists session;
create table session (
	id		int not null auto_increment,
	site_id		int, # not null
	cookie		varchar(255), # not null,
	user_id		int,
	dest		text, # Destination URL to go to after login
	query		text, # Last query executed, for subsequent pages

	primary key(id),
	key(cookie),
	unique(cookie)
);

# Users are site-specific.  The "admin" field may be:
#	0 - normal user
#	1 - administrator
#	2 - wizard
#
drop table if exists user;
create table user (
	id		int not null auto_increment,
	site_id		int, # not null
	admin		int default 0, # not null
	name		text, # not null
	email_address	varchar(255), # not null
	password	varchar(255), # not null

	primary key(id),
	foreign key(site_id) references site(id),
	key(email_address),
	key(password),
	unique(site_id, email_address)
);

commit;