# $Id: create_tables2.mysql,v 1.2 2007-07-12 13:12:41 mike Exp $

# Please read "../doc/data-model" before this file.
#
# Create the tables as follows:
#	mysql -u kr_admin -p'kr-adm_3636' < 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.

use kr2;

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

commit;