-- SQL to create the initial tables for the GNUEDU database.
--
drop table if exists contributor;
drop table if exists keyword;
drop table if exists item;

-- main tables
CREATE TABLE contributor (
  id smallint unsigned NOT NULL auto_increment,
  fullname varchar(255) binary NOT NULL default '',
  email tinytext NOT NULL default '',
  lang char(2) NOT NULL default '',
  id_country char(3) NOT NULL default '',
  login varchar(63),
  creation_time datetime NOT NULL default 0,
  modification_time timestamp NOT NULL,
  UNIQUE KEY id (id),
  UNIQUE KEY email (email(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
CREATE TABLE keyword (
  id mediumint unsigned NOT NULL auto_increment,
  lang char(2) NOT NULL default '', 
  function enum('action','concept') NOT NULL default 'concept',
  label varchar(127) NOT NULL default '',
  untranslatable boolean default 0, -- finally I don't like 'xx' ..
  weight smallint NOT NULL default 0,
  status boolean NOT NULL default '1',
  timestamp timestamp,
  UNIQUE KEY id (id),
  UNIQUE KEY label (lang,label(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE item (
  id mediumint unsigned NOT NULL auto_increment,
  lang char(2) NOT NULL default '',
  id_subject char(3) NOT NULL default '',
  label_teacher tinytext NOT NULL,
  label_student tinytext,
  description text,
  timestamp timestamp,
  UNIQUE KEY id (id),
  UNIQUE KEY label (id_subject,label_teacher (200)),
  KEY lang (lang)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE resource (
  id int unsigned NOT NULL auto_increment,
  lang char(2) NOT NULL,
  title tinytext NOT NULL default '',
  audience char(3),
  subject char(3),
  description text,
  format varchar(15) NOT NULL,
  filesize mediumint unsigned, -- in kilobytes
  software varchar(15),
  interactivity enum('active', 'expositive', 'mixed') NOT NULL default 'mixed',
  lrestype varchar(15) NOT NULL default '',
  difficulty enum('1','2','3','4','5'),
  annotation text,
  localpath tinytext,  -- for internal reference
  url_document tinytext NOT NULL default '',  -- external link
  url_source tinytext,  -- external link for source file if necessary
  version varchar(15),
  status enum('draft', 'final', 'revised', 'unavailable'),
  license varchar(15) NOT NULL,
  id_author smallint unsigned,
  id_last_editor smallint unsigned,
  authors tinytext,
  email varchar(63),
  creation_time datetime NOT NULL default 0,
  modification_time timestamp NOT NULL,
  published boolean NOT NULL default '1',
  UNIQUE KEY id (id),
  UNIQUE KEY url (url_document(200)),
  KEY format (format),
  KEY id_author (id_author),
  KEY id_last_editor (id_last_editor),
  KEY license (license),
  KEY modification_time (modification_time),
  FOREIGN KEY fk_id_author (id_author) REFERENCES contributor(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- contributor profile 
CREATE TABLE profile (
  id mediumint unsigned NOT NULL auto_increment,
  id_contributor smallint unsigned NOT NULL,
  language varchar(15),
  format varchar(15),
  software varchar(15),
  country varchar(15),
  subject varchar(15),
  audience varchar(15),
  level varchar(15),
  license varchar(15),
  PRIMARY KEY id (id),
  FOREIGN KEY fk_id_contributor (id_contributor) REFERENCES contributor(id)
) ENGINE=InnoDB CHARACTER SET ascii PACK_KEYS=1;

-- case more than one author
CREATE TABLE author_resource (
  id_author smallint unsigned NOT NULL default 0,
  id_resource int unsigned NOT NULL default 0,
  PRIMARY KEY ids (id_author, id_resource),
  FOREIGN KEY fk_id_author (id_author) REFERENCES author(id),
  FOREIGN KEY fk_id_resource (id_resource) REFERENCES resource(id)
) DEFAULT CHARSET=ascii;

-- for keywords and items
CREATE TABLE keyword_item (
  id_keyword mediumint unsigned NOT NULL default 0,
  id_item mediumint unsigned NOT NULL default 0,
  PRIMARY KEY ids (id_keyword, id_item),
  FOREIGN KEY fk_id_keyword (id_keyword) REFERENCES keyword(id),
  FOREIGN KEY fk_id_item (id_item) REFERENCES item(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE synonym (
  id_master mediumint unsigned NOT NULL, -- lang & function determined here
  id_synonym mediumint unsigned NOT NULL,
  subject char(3) default NULL, 
  timestamp timestamp NOT NULL,
  PRIMARY KEY ids (id_master, id_synonym),
  FOREIGN KEY fk_id_master (id_master) REFERENCES keyword(id),
  FOREIGN KEY fk_id_synonym (id_synonym) REFERENCES keyword(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
 
CREATE TABLE keyword_translation (
  langs char(4) NOT NULL default '',
  id_from mediumint unsigned NOT NULL default '0',
  id_to mediumint unsigned NOT NULL default '0',
  fuzzy boolean NOT NULL default '0',
  context char(3),
  id_last_translator smallint(5) unsigned NOT NULL default '0',
  timestamp timestamp NOT NULL,
  PRIMARY KEY from_to (id_from, id_to),
  KEY id_to (id_to),
  KEY langs (langs),
  FOREIGN KEY fk_id_from (id_from) REFERENCES keyword(id),
  FOREIGN KEY fk_id_to (id_to) REFERENCES keyword(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

-- for resources
CREATE TABLE keyword_resource (
  id_keyword mediumint unsigned NOT NULL,
  id_resource int unsigned NOT NULL,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY ids (id_keyword, id_resource),
  FOREIGN KEY fk_id_keyword (id_keyword) REFERENCES keyword(id),
  FOREIGN KEY fk_id_resource (id_resource) REFERENCES resource(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE item_resource (
  id_item mediumint unsigned NOT NULL default 0,
  id_resource int unsigned NOT NULL default 0,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY ids (id_item, id_resource),
  FOREIGN KEY fk_id_item (id_item) REFERENCES item(id),
  FOREIGN KEY fk_id_resource (id_resource) REFERENCES resource(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

-- national curricula
CREATE TABLE level (
  id smallint unsigned NOT NULL auto_increment,
  id_country char(3) NOT NULL default '',
  audience char(3),
  label varchar(63) NOT NULL default '', 
  UNIQUE KEY id (id),
  UNIQUE KEY label (id_country,label)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE curriculum (
  id smallint unsigned NOT NULL auto_increment,
  id_subject char(3) NOT NULL default '',
  id_country char(3) NOT NULL default '',
  label tinytext NOT NULL, 
  date varchar(63) NOT NULL default 0,
  UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE level_curriculum (
  id smallint(5) unsigned NOT NULL auto_increment,
  id_level smallint unsigned NOT NULL default 0,
  id_curriculum smallint unsigned NOT NULL default 0,
  PRIMARY KEY id (id),
  UNIQUE KEY ids (id_level, id_curriculum),
  FOREIGN KEY fk_id_level (id_level) REFERENCES level(id),
  FOREIGN KEY fk_id_curriculum (id_curriculum) REFERENCES curriculum(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE chapterset (
  id mediumint unsigned NOT NULL auto_increment,
  id_levelcur smallint unsigned NOT NULL default 0,
  label tinytext NOT NULL, 
  description text,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY id (id),
  UNIQUE KEY label (id_levelcur,label(200)),
  FOREIGN KEY fk_id_levelcur (id_levelcur) REFERENCES level_curriculum(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB;

CREATE TABLE chapter (
  id mediumint unsigned NOT NULL auto_increment,
  id_chapterset smallint unsigned NOT NULL default 0,
  id_parent mediumint unsigned NOT NULL default 0,
  label tinytext NOT NULL, 
  description text,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY id (id),
  UNIQUE KEY label (id_chapterset,label(200)),
  FOREIGN KEY fk_id_chapterset (id_chapterset) REFERENCES chapterset(id),
  FOREIGN KEY fk_id_parent (id_parent) REFERENCES chapter(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB;

CREATE TABLE chapter_keyword (
  id_chapter mediumint unsigned NOT NULL default 0,
  id_keyword mediumint unsigned NOT NULL default 0,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY ids (id_chapter, id_keyword),
  FOREIGN KEY fk_id_chapter (id_chapter) REFERENCES chapter(id),
  FOREIGN KEY fk_id_keyword (id_keyword) REFERENCES keyword(id)
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE chapter_item (
  id_chapter mediumint unsigned NOT NULL default 0,
  id_item mediumint unsigned NOT NULL default 0,
  id_last_editor smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY ids (id_chapter, id_item),
  KEY id_chapter (id_chapter),
  KEY id_item (id_item),
  FOREIGN KEY fk_id_chapter (id_chapter) REFERENCES chapter(id),
  FOREIGN KEY fk_id_item (id_item) REFERENCES item(id),
  FOREIGN KEY fk_id_last_editor (id_last_editor) REFERENCES contributor(id)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

-- LOM
CREATE TABLE lom (
  id_resource int unsigned NOT NULL,
  lom text NOT NULL,  
  id_user smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  PRIMARY KEY id_resource (id_resource),
  KEY id_user (id_user),
  KEY timestamp (timestamp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

CREATE TABLE lom_version (
  id_resource int unsigned NOT NULL default 0,
  lom text NOT NULL default '',  
  id_user smallint unsigned NOT NULL default 0,
  timestamp timestamp NOT NULL,
  KEY id_resource (id_resource),
  KEY id_user (id_user),
  KEY timestamp (timestamp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

-- indexes
ALTER TABLE contributor ADD UNIQUE INDEX email (email(100));
ALTER TABLE author_resource ADD INDEX ids (id_author, id_resource);
ALTER TABLE chapter ADD UNIQUE INDEX label (id_level,id_curriculum,label(200));
ALTER TABLE chapter ADD index id_level(id_level);
ALTER TABLE chapter ADD index id_curriculum(id_curriculum);
ALTER TABLE chapter_item ADD INDEX id_chapter (id_chapter);
ALTER TABLE chapter_item ADD INDEX id_item (id_item);
ALTER TABLE item ADD UNIQUE INDEX label (label_teacher(200));
ALTER TABLE item ADD INDEX id_subject (id_subject);
ALTER TABLE item_resource ADD INDEX id_item (id_item);
ALTER TABLE item_resource ADD INDEX id_resource (id_resource);
ALTER TABLE keyword ADD UNIQUE INDEX label (lang,label(100));
ALTER TABLE keyword_item ADD INDEX id_keyword (id_keyword);
ALTER TABLE keyword_item ADD INDEX id_item (id_item);
ALTER TABLE keyword_translation ADD INDEX id_from (id_from);
ALTER TABLE keyword_translation ADD INDEX id_to (id_to);
ALTER TABLE keyword_translation ADD INDEX langs (langs);
ALTER TABLE level ADD UNIQUE INDEX label (id_country,label);
ALTER TABLE resource ADD INDEX format (format);
ALTER TABLE resource ADD INDEX id_author (id_author);
ALTER TABLE resource ADD INDEX id_last_editor (id_last_editor);
ALTER TABLE resource ADD INDEX license (license);
ALTER TABLE resource ADD INDEX modification_time (modification_time);
ALTER TABLE resource ADD UNIQUE INDEX url (url_document(200));
ALTER TABLE lom ADD INDEX id_user (id_user);
ALTER TABLE lom ADD INDEX timestamp (timestamp);
ALTER TABLE lom_version ADD INDEX id_resource (id_resource);
ALTER TABLE lom_version ADD INDEX id_user (id_user);
ALTER TABLE lom_version ADD INDEX timestamp (timestamp);

-- the end