# -----------------------------------------------------------------
# WebLib metadata tables
#
# The tables must be present in all WebLib applications that utilize the
# metadata sub-system.
#
# Last modified: 		29 November 2006
# -----------------------------------------------------------------
# Metadata data elements
# -----------------------------------------------------------------
create table if not exists weblib_md_elements (
	element_id				smallint unsigned			not null auto_increment,
	name					varchar(255)				not null,
	name_short				varchar(15)					not null,
	description				text						not null,
	datatype				tinyint unsigned			not null,
	rules					text						not null,
	rank					smallint unsigned			not null,
	is_legacy				tinyint	unsigned			not null,
	is_internal_only		tinyint unsigned			not null,
	standard_id				smallint unsigned			not null references weblib_md_standards(standard_id),
	documentation_url		text						not null,
	
	index					i_standard_id				(standard_id),
	unique index			u_name						(name,standard_id),
	unique index			u_name_short				(name_short,standard_id),
	primary key 										(element_id)
) type=innodb character set utf8 collate utf8_general_ci;


# -----------------------------------------------------------------
# Metadata attribute
# -----------------------------------------------------------------
create table if not exists weblib_md_attributes (
	attribute_id			int unsigned				not null auto_increment,
	element_id				smallint unsigned			null,
	
	table_num				tinyint unsigned			not null,
	row_id					integer unsigned			not null,
	
	value_text				longtext					null,
	value_integer			int							null,
	value_decimal			decimal(20,4)				null,
	value_sdatetime			decimal(30,20)				null,
	value_edatetime			decimal(30,20)				null,
	language				varchar(255)				null,
	text_encoding			varchar(255)				null,
	source_notes			text						null,
	sdatetime				decimal(30,20)				null,
	edatetime				decimal(30,20)				null,
	
	index					i_element_id				(element_id),
	index					i_value_text				(value_text(255)),
	index					i_value_integer				(value_integer),
	index					i_value_decimal				(value_decimal),
	index					i_value_datetime			(value_sdatetime, value_edatetime),
	index					i_language					(language),
	
	index					i_row						(row_id, table_num),
	primary key (attribute_id)
) type=innodb character set utf8 collate utf8_general_ci;

# -----------------------------------------------------------------
# Metadata template (ordered collection of elements)
# -----------------------------------------------------------------
create table if not exists weblib_md_template_elements (
	template_element_id		smallint unsigned			not null auto_increment,
	
	table_num				tinyint unsigned			not null, /* number of table metadata is attached to, not the type table itself */
	template_id				smallint unsigned			null, /* may be id of type in a table or a constant (defined in the table config the metadata is attached to */
	
	element_id				smallint unsigned			not null,
	usage_notes				text						not null,
	rank					smallint unsigned			not null,
	
	
	index					i_element_id				(element_id),
	unique index			u_row						(template_id, element_id, table_num),
	
	primary key 										(template_element_id)
) type=innodb character set utf8 collate utf8_general_ci;

# -----------------------------------------------------------------
# Metadata standards
# -----------------------------------------------------------------
create table if not exists weblib_md_standards (
	standard_id				smallint unsigned			not null auto_increment,
	
	name					varchar(255)				not null, 
	name_short				varchar(15)					not null,
	
	description				text						not null,
	documentation_url		varchar(255)				not null,
	
	rank					smallint unsigned			not null,
	
	unique index			u_name						(name),
	unique index			u_name_short				(name_short),
	primary key 										(standard_id)
) type=innodb character set utf8 collate utf8_general_ci;

# -----------------------------------------------------------------