CREATE TABLE mrec_tbl 
( 
  mrec_id 	int auto_increment, 
  mrec_type 	SET("collection", "item", "annotation"), 
  create_date 	DATE, 
  mod_date 	DATE, 
  oai_set 	varchar(100),
  cache_flag	char(1) default 'N',
  batch_id 	int, 
  authority	varchar(50),
  docname	varchar(20),
  primary key (mrec_id) 
); 

CREATE TABLE element_tbl 
( 
  element_id 	int auto_increment, 
  mrec_id 	int,
  element_name 	varchar(50), 
  element_type 	varchar(20), 
  element_scheme 	varchar(50), 
  element_value 	text, 
  sequence 	int default 0,
  primary key (element_id),
  index element_idx1 (mrec_id, element_name)
); 


CREATE TABLE acl_tbl 
( 
  acl_id	int auto_increment,
  mrec_id 	int not null, 
  privilege 	SET("read","edit"),
  primary key 	(acl_id),
  unique index acl_idx1 (mrec_id, privilege)
); 

CREATE TABLE acl_user_tbl
(
   acl_id	int not null,
   user_id	varchar(20) not null,
   primary key  (acl_id, user_id)
);

CREATE TABLE link_tbl 
( 
  parent_id	int not null, 
  child_id 	int not null, 
  annotation_id int default NULL,
  sequence 	int not null default 0,
  primary key (parent_id, child_id, sequence) 
); 

CREATE TABLE cache_tbl
(
  mrec_id 	int not null,
  mime_type_id 	int, 
  cache_date	timestamp not null, 
  cache_value 	blob,
  primary key (mrec_id, cache_date)
);


CREATE TABLE mime_type_tbl
(
  mime_type_id		int auto_increment,
  mime_type_name 	varchar(20),
  primary key		(mime_type_id)
);

CREATE TABLE batch_info_tbl 
( 
  batch_id 		int auto_increment,
  batch_date		DATETIME,
  new_record_added	int,
  record_updated	int, 
  batch_name		varchar(50),
  source_desc           varchar(30),
  native_format	        varchar(10),
  crosswalk_method	varchar(20),
  number_of_records	int,
  destination_file	varchar(50),
  nsdl_category		varchar(50),
  collection_record_number varchar(10),
  batch_comments	text,
  primary key 		(batch_id)
); 

CREATE TABLE manage_info_tbl 
( 
  batch_name 		varchar(50) not null, 
  contact_person1 	varchar(50), 
  contact_email1 	varchar(50), 
  contact_phone_number1 varchar(20), 
  contact_fax_number1 varchar(20),
  contact_person2 	varchar(50), 
  contact_email2 	varchar(50), 
  contact_phone_number2 varchar(20), 
  contact_fax_number2 varchar(20), 
  primary key (batch_name)
); 

CREATE TABLE update_log_tbl
(
  mrec_id 	int not null,
  user_id	varchar(20),
  update_date 	timestamp,
  data 		text,
  primary key (mrec_id, update_date)
);

CREATE TABLE mdblob_tbl
(
  mrec_id 	int not null,
  format 	ENUM('marc', 'gem'),
  blob_value 	text,
  primary key   (mrec_id)
);