CREATE DATABASE IF NOT EXISTS `ekktt` CHARACTER SET utf8 COLLATE utf8_bin;
USE `ekktt`;

DROP TABLE IF EXISTS `ekktt_article`;
CREATE TABLE `ekktt_article` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_local_id` int(11) DEFAULT NULL, /* holds the article id as given in the news portal*/

  `category_name` longtext CHARACTER SET utf8, /*at the moment this is optional, but should be the category of the item*/
  `channel_id` enum('POSTIMEES','ARIPAEV','PAEVALEHT','EESTIEKSPRESS','DELFI') NOT NULL, /*the id of the channel from which the stories are downloaded*/
  `lang_id` int(11) NOT NULL DEFAULT '1', /*language id (optional)*/

  `title` mediumtext CHARACTER SET utf8 NOT NULL, /*the title of news story in plain text without html tags*/
  `lead` mediumtext CHARACTER SET utf8 NOT NULL, /*the lead of news story in plain text without html tags*/
  `content` mediumtext CHARACTER SET utf8 NOT NULL,/*the body of the news story in plain text without html tags*/

  `author` varchar(255) CHARACTER SET utf8 DEFAULT NULL, /*the author of the news story in plain text without html tags*/
  `publish_date` datetime NOT NULL, /*publish date*/
  `state` tinyint(3) unsigned NOT NULL,
  `html` longtext, /*the full html content found on given url*/
  `url` varchar(256) DEFAULT NULL, /*the url of current news story*/
  
  `title_morph` mediumtext CHARACTER SET utf8,
  `lead_morph` mediumtext CHARACTER SET utf8,
  `content_morph` mediumtext CHARACTER SET utf8,
  
  `is_morph_analyzed` boolean DEFAULT false,
  `is_indexed` boolean DEFAULT false,
  `is_ner_analyzed` boolean DEFAULT false,
  
  PRIMARY KEY (`article_id`),
  KEY `channel_id` (`channel_id`),
  KEY `publish_date` (`publish_date`),
  KEY `state` (`state`),
  
  KEY `key_is_morph_analyzed` (`is_morph_analyzed`),
  KEY `key_is_indexed` (`is_indexed`),
  KEY `key_is_ner_analyzed` (`is_ner_analyzed`)
  
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `ekktt_named_entity`;
CREATE TABLE `ekktt_named_entity` (
	/* Columns value, text, start_pos, end_pos can be NULL in case of Timex entities */
  `id` int(11) NOT NULL auto_increment,
  `article_id` int(11) NOT NULL,
  `article_section` enum('TITLE','LEAD','CONTENT') NOT NULL,
  `value` text NOT NULL,
  `text` text,
  `type` enum('ORG', 'PER', 'LOC', 'GPE', 'FAC', 'ADR', 'STR', 'TMX') NOT NULL,
  `start_pos` int(11),
  `end_pos` int(11),
  PRIMARY KEY  (`id`),
  KEY `key_article_id` (`article_id`)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `ekktt_visit_url`;
CREATE TABLE `ekktt_visit_url` (
  `id` int(11) NOT NULL AUTO_INCREMENT, /*the id of the url in this table*/
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL, /*the url of the news story*/
  `visited` tinyint(1) DEFAULT NULL, /*boolean, whether this news story is downloaded*/
  `channel_id` enum('POSTIMEES','ARIPAEV','PAEVALEHT','EESTIEKSPRESS','DELFI') COLLATE utf8_bin DEFAULT NULL, /*the id of the channel*/
  `last_visit_date` datetime DEFAULT NULL, /*the last (usually also first) visit date*/
  `article_local_id` int(11) DEFAULT NULL,/*the id of the url, taken from the story id in news portal*/
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


ALTER TABLE ekktt_article ADD INDEX idx3 (article_local_id);
ALTER TABLE ekktt_article ADD INDEX idx6 (url);
ALTER TABLE ekktt_article ADD INDEX idx4 (article_local_id, channel_id);
ALTER TABLE ekktt_article ADD INDEX idx_is_morph_analyzed_publish_date (is_morph_analyzed, publish_date DESC);

ALTER TABLE ekktt_article ADD COLUMN is_article_full BOOLEAN DEFAULT TRUE;


ALTER TABLE ekktt_visit_url ADD INDEX idx1 (article_local_id);
ALTER TABLE ekktt_visit_url ADD INDEX idx2 (visited);
ALTER TABLE ekktt_visit_url ADD INDEX idx5 (url);

ALTER TABLE ekktt_named_entity ADD FOREIGN KEY `fk_ekktt_named_entity_article_id` (article_id) REFERENCES ekktt_article (article_id);

ALTER TABLE `ekktt_named_entity` ADD FULLTEXT (`value`);

ALTER TABLE `ekktt_named_entity` CHANGE `value` `value` VARCHAR(255);

ALTER TABLE `ekktt_article` CHANGE `title` `title` VARCHAR(255);

ALTER TABLE ekktt_article ADD CONSTRAINT uc_ctp UNIQUE (channel_id,title,publish_date);

%3.25sek min koos idx8 ja idx7-ga, 2.08 min vaid idx7, min 3.22 ilma idx7 ja idx8-ta

%ALTER TABLE `ekktt_named_entity` ADD INDEX idx8 (article_id, value, type);

ALTER TABLE `ekktt_named_entity` ADD INDEX idx7 (value, type);

%ALTER TABLE `ekktt_named_entity` ADD INDEX idx9 (article_id, type);

%DROP INDEX idx8 ON `ekktt_named_entity`;



