[Qualipso-a4] Duda thread
Javier Pueyo
jpueyo en gsyc.es
Jue Feb 26 14:23:30 CET 2009
Muchas gracias José Antonio. Me parece perfecto, aunque si la columna
secuencia obligara a modificar mucho el extractor lo cierto es que no me
resulta fundamental. Para analizar mejor la temática de un thread me
basta con saber qué mensaje origina el thread y cuáles pertenecen al
mismo.
Saludos
El jue, 26-02-2009 a las 10:27 +0100, José Antonio Quiles escribió:
> Hola Javier,
> te adjunto el nuevo diseño de la BD con los cambios que pediste, y
> además un cambio en la parte de documentación.
> Como puedes ver, he metido una nueva tabla que se llama mail_replies
> que contiene los mail que son reply de otro y además almacena el
> número de secuencia.
> Adjunto también los scripts de creación, y los scripts de modificación
> (a partir de la versión 1.1).
> Espero vuestros comentarios...
> Vai
>
>
> Javier Pueyo escribió:
> > El mar, 24-02-2009 a las 16:20 +0100, Jose Gato Luis escribió:
> >
> > > Javier Pueyo escribió:
> > >
> > > > Hola a todos,
> > > >
> > > > Tengo una duda. ¿Según quedó la BBDD de mails es posible extraer
> > > > directamente qué mensajes pertenecen a un "thread" y el orden de cada
> > > > mensaje dentro del mismo?
> > > >
> > > No es algo que estemos almacenando (in-reply-to), ¿lo necesitas?
> > >
> >
> > No inmediatamente, pero más adelante me será bastante útil para
> > determinar la temática de los mensajes.
> >
> >
> > > > Saludos,
> > > >
> > > > Javier
> > > >
> > > > _______________________________________________
> > > > Qualipso-a4 mailing list
> > > > Qualipso-a4 en gsyc.escet.urjc.es
> > > > http://gsyc.escet.urjc.es/cgi-bin/mailman/listinfo/qualipso-a4
> > > >
> >
> >
> documento de texto sencillo adjunto (alter_qualipso_database_1.2.sql)
> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_replies` (
> `replyid` INT(11) NOT NULL AUTO_INCREMENT ,
> `mailid` INT(11) NOT NULL ,
> `sequence` INT(11) NOT NULL ,
> PRIMARY KEY (`replyid`) ,
> INDEX `mail_replies_mail_mails` (`mailid` ASC) ,
> CONSTRAINT `mail_replies_mail_mails`
> FOREIGN KEY (`mailid` )
> REFERENCES `qualipso`.`mail_mails` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB
> DEFAULT CHARACTER SET = latin1
> COLLATE = latin1_swedish_ci;
>
> ALTER TABLE `qualipso`.`floss_sources` ADD COLUMN `topicid` INT(10) NOT NULL AFTER `itemid` , CHANGE COLUMN `itemid` `itemid` INT(10) NOT NULL ,
> ADD CONSTRAINT `floss_sources_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> ADD CONSTRAINT `floss_sources_info_items`
> FOREIGN KEY ()
> REFERENCES `qualipso`.`info_items` ()
> ON DELETE NO ACTION
> ON UPDATE NO ACTION
> , ADD INDEX `floss_sources_floss_topics` (`topicid` ASC)
> , ADD INDEX `floss_sources_info_items` ()
> , ADD PRIMARY KEY (`itemid`) , ENGINE = MyISAM ;
>
> ALTER TABLE `qualipso`.`doc_documents` ADD COLUMN `filename` VARCHAR(250) NULL DEFAULT NULL AFTER `itemid` , ADD COLUMN `lastModification` NULL AFTER `filename` , ADD COLUMN `summary` LONGTEXT NULL DEFAULT NULL AFTER `filename` , ADD COLUMN `text` LONGTEXT NULL DEFAULT NULL AFTER `summary` , CHANGE COLUMN `itemid` `itemid` INT(11) NOT NULL ,
> ADD CONSTRAINT `doc_documents_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION
> , ADD INDEX `doc_documents_info_items` (`itemid` ASC)
> , ADD PRIMARY KEY (`itemid`) ;
>
> ALTER TABLE `qualipso`.`doc_chapters` DROP COLUMN `parentChapter` , DROP COLUMN `text` , ADD COLUMN `endText` INT(11) NULL DEFAULT NULL AFTER `title` , ADD COLUMN `startText` INT(11) NULL DEFAULT NULL AFTER `title` , CHANGE COLUMN `number` `number` VARCHAR(60) NULL DEFAULT NULL , DROP FOREIGN KEY `doc_chapters_doc_chapters`
> , DROP INDEX `doc_chapters_doc_chapters` ;
>
>
> SET SQL_MODE=@OLD_SQL_MODE;
> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
> documento de texto sencillo adjunto (create_qualipso_database_1.2.sql)
> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
>
> CREATE SCHEMA IF NOT EXISTS `qualipso` ;
> USE `qualipso`;
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`item_types`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`item_types` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`item_types` (
> `typeid` INT NOT NULL AUTO_INCREMENT ,
> `description` VARCHAR(120) NULL ,
> `label_en` VARCHAR(50) NULL ,
> `class` VARCHAR(250) NULL ,
> PRIMARY KEY (`typeid`) )
> ENGINE = InnoDB;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`info_items`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`info_items` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`info_items` (
> `itemid` INT NOT NULL AUTO_INCREMENT ,
> `uri` VARCHAR(250) NOT NULL ,
> `url` VARCHAR(250) NULL ,
> `language` VARCHAR(20) NULL ,
> `title` VARCHAR(2000) NULL ,
> `creation` DATETIME NOT NULL ,
> `typeid` INT NOT NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `fk_info_items_item_types`
> FOREIGN KEY (`typeid` )
> REFERENCES `qualipso`.`item_types` (`typeid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `fk_info_items_item_types` ON `qualipso`.`info_items` (`typeid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`item_metadata`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`item_metadata` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`item_metadata` (
> `metadataid` INT NOT NULL AUTO_INCREMENT ,
> `name` VARCHAR(100) NOT NULL ,
> `value` VARCHAR(1000) NULL ,
> `itemid` INT NOT NULL ,
> PRIMARY KEY (`metadataid`) ,
> CONSTRAINT `fk_item_metadata_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `fk_item_metadata_info_items` ON `qualipso`.`item_metadata` (`itemid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`persons`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`persons` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`persons` (
> `personid` INT NOT NULL AUTO_INCREMENT ,
> `uri` VARCHAR(250) NULL ,
> `firstname` VARCHAR(100) NULL ,
> `surename` VARCHAR(100) NULL ,
> PRIMARY KEY (`personid`) )
> ENGINE = InnoDB;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`item_authors`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`item_authors` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`item_authors` (
> `personid` INT NOT NULL ,
> `itemid` INT NOT NULL ,
> CONSTRAINT `fk_item_authors_persons`
> FOREIGN KEY (`personid` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `fk_item_authors_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `fk_item_authors_persons` ON `qualipso`.`item_authors` (`personid` ASC) ;
>
> CREATE INDEX `fk_item_authors_info_items` ON `qualipso`.`item_authors` (`itemid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_topics`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_topics` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_topics` (
> `topicid` INT(10) NOT NULL ,
> `description` MEDIUMTEXT NOT NULL ,
> `label` VARCHAR(80) NOT NULL ,
> PRIMARY KEY (`topicid`) )
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_sources`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_sources` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_sources` (
> `itemid` INT(10) NOT NULL ,
> `topicid` INT(10) NOT NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `floss_sources_info_items`
> FOREIGN KEY ()
> REFERENCES `qualipso`.`info_items` ()
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `floss_sources_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `floss_sources_info_items` ON `qualipso`.`floss_sources` () ;
>
> CREATE INDEX `floss_sources_floss_topics` ON `qualipso`.`floss_sources` (`topicid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_entities`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_entities` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_entities` (
> `entityid` INT(10) NOT NULL ,
> `description` MEDIUMTEXT NOT NULL ,
> `label` VARCHAR(80) NOT NULL ,
> PRIMARY KEY (`entityid`) )
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`corpus_entities`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`corpus_entities` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`corpus_entities` (
> `entityid` INT(10) NOT NULL ,
> `itemid` INT(10) NOT NULL ,
> PRIMARY KEY (`entityid`) ,
> CONSTRAINT `corpus_entities_floss_sources`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`floss_sources` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `corpus_entities_floss_entities`
> FOREIGN KEY (`entityid` )
> REFERENCES `qualipso`.`floss_entities` (`entityid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `corpus_entities_floss_sources` ON `qualipso`.`corpus_entities` (`itemid` ASC) ;
>
> CREATE INDEX `corpus_entities_floss_entities` ON `qualipso`.`corpus_entities` (`entityid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`entity_param`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`entity_param` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`entity_param` (
> `entityid` INT(10) NOT NULL ,
> `name` VARCHAR(80) NOT NULL ,
> `value` VARCHAR(80) NOT NULL ,
> PRIMARY KEY (`entityid`) ,
> CONSTRAINT `entity_param_corpus_entities`
> FOREIGN KEY (`entityid` )
> REFERENCES `qualipso`.`corpus_entities` (`entityid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `entity_param_corpus_entities` ON `qualipso`.`entity_param` (`entityid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_bigrams`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_bigrams` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_bigrams` (
> `topicid` INT(10) NOT NULL ,
> `bigram` VARCHAR(80) NOT NULL ,
> CONSTRAINT `floss_bigrams_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `floss_bigrams_floss_topics` ON `qualipso`.`floss_bigrams` (`topicid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_collocates`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_collocates` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_collocates` (
> `topicid` INT(10) NOT NULL ,
> `collocate` VARCHAR(80) NOT NULL ,
> `t_score` DECIMAL(10,0) NOT NULL ,
> `MI_score` DECIMAL(10,0) NOT NULL ,
> CONSTRAINT `floss_collocates_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `floss_collocates_floss_topics` ON `qualipso`.`floss_collocates` (`topicid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_trigrams`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_trigrams` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_trigrams` (
> `topicid` INT(10) NOT NULL ,
> `trigram` VARCHAR(80) NOT NULL ,
> CONSTRAINT `floss_trigrams_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `floss_trigrams_floss_topics` ON `qualipso`.`floss_trigrams` (`topicid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`floss_unigrams`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`floss_unigrams` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`floss_unigrams` (
> `topicid` INT(10) NOT NULL ,
> `unigram` VARCHAR(80) NOT NULL ,
> CONSTRAINT `floss_unigrams_floss_topics`
> FOREIGN KEY (`topicid` )
> REFERENCES `qualipso`.`floss_topics` (`topicid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1;
>
> CREATE INDEX `floss_unigrams_floss_topics` ON `qualipso`.`floss_unigrams` (`topicid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`sequential_ngram`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`sequential_ngram` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`sequential_ngram` (
> `wordid` INT(10) NOT NULL ,
> `itemid` INT(10) NOT NULL ,
> `w1` VARCHAR(80) NOT NULL ,
> `l1` VARCHAR(80) NOT NULL ,
> `pos1` VARCHAR(10) NOT NULL ,
> `w2` VARCHAR(80) NOT NULL ,
> `l2` VARCHAR(80) NOT NULL ,
> `pos2` VARCHAR(10) NOT NULL ,
> `w3` VARCHAR(80) NOT NULL ,
> `l3` VARCHAR(80) NOT NULL ,
> `pos3` VARCHAR(10) NOT NULL ,
> `w4` VARCHAR(80) NOT NULL ,
> `l4` VARCHAR(80) NOT NULL ,
> `pos4` VARCHAR(10) NOT NULL ,
> `w5` VARCHAR(80) NOT NULL ,
> `l5` VARCHAR(80) NOT NULL ,
> `pos5` VARCHAR(10) NOT NULL ,
> `w6` VARCHAR(80) NOT NULL ,
> `l6` VARCHAR(80) NOT NULL ,
> `pos6` VARCHAR(10) NOT NULL ,
> `w7` VARCHAR(80) NOT NULL ,
> `l7` VARCHAR(80) NOT NULL ,
> `pos7` VARCHAR(10) NOT NULL ,
> `w8` VARCHAR(80) NOT NULL ,
> `l8` VARCHAR(80) NOT NULL ,
> `pos8` VARCHAR(10) NOT NULL ,
> `w9` VARCHAR(80) NOT NULL ,
> `l9` VARCHAR(80) NOT NULL ,
> `pos9` VARCHAR(10) NOT NULL ,
> PRIMARY KEY (`w5`) ,
> CONSTRAINT `sequential_ngram_floss_sources`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`floss_sources` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = MyISAM
> DEFAULT CHARACTER SET = latin1
> COMMENT = 'Contains grammatical information (pos5) and lemma (l5) about';
>
> CREATE INDEX `sequential_ngram_floss_sources` ON `qualipso`.`sequential_ngram` (`itemid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`mail_mails`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`mail_mails` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_mails` (
> `itemid` INT NOT NULL ,
> `messageid` VARCHAR(100) NULL ,
> `body` LONGTEXT NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `mail_mails_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `mail_mails_info_items` ON `qualipso`.`mail_mails` (`itemid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`mail_cc`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`mail_cc` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_cc` (
> `mailid` INT NOT NULL ,
> `personid` INT NOT NULL ,
> CONSTRAINT `mail_cc_persons`
> FOREIGN KEY (`personid` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `mail_cc_mails`
> FOREIGN KEY (`mailid` )
> REFERENCES `qualipso`.`mail_mails` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `mail_cc_persons` ON `qualipso`.`mail_cc` (`personid` ASC) ;
>
> CREATE INDEX `mail_cc_mails` ON `qualipso`.`mail_cc` (`mailid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`mail_to`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`mail_to` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_to` (
> `mailid` INT NOT NULL ,
> `personid` INT NOT NULL ,
> CONSTRAINT `mail_to_persons`
> FOREIGN KEY (`personid` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `mail_to_mail_mails`
> FOREIGN KEY (`mailid` )
> REFERENCES `qualipso`.`mail_mails` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `mail_to_persons` ON `qualipso`.`mail_to` (`personid` ASC) ;
>
> CREATE INDEX `mail_to_mail_mails` ON `qualipso`.`mail_to` (`mailid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`refers_to`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`refers_to` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`refers_to` (
> `itemid` INT NOT NULL ,
> `strong_refers_to` INT NULL ,
> `entity_name` VARCHAR(250) NULL ,
> CONSTRAINT `from_refers_to_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `to_refers_to_info_items`
> FOREIGN KEY (`strong_refers_to` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `from_refers_to_info_items` ON `qualipso`.`refers_to` (`itemid` ASC) ;
>
> CREATE INDEX `to_refers_to_info_items` ON `qualipso`.`refers_to` (`strong_refers_to` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`bts_bugs`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`bts_bugs` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`bts_bugs` (
> `itemid` INT NOT NULL ,
> `submitDate` DATETIME NULL ,
> `updateDate` DATETIME NULL ,
> `summary` LONGTEXT NULL ,
> `state` VARCHAR(80) NULL ,
> `reporter` INT NULL ,
> `assignedTo` INT NULL ,
> `bugId` VARCHAR(80) NULL ,
> `description` LONGTEXT NULL ,
> `notes` LONGTEXT NULL ,
> `addInfo` VARCHAR(250) NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `bts_bugs_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `bts_bugs_reporter_persons`
> FOREIGN KEY (`reporter` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `bts_bugs_assigned_persons`
> FOREIGN KEY (`assignedTo` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `bts_bugs_info_items` ON `qualipso`.`bts_bugs` (`itemid` ASC) ;
>
> CREATE INDEX `bts_bugs_reporter_persons` ON `qualipso`.`bts_bugs` (`reporter` ASC) ;
>
> CREATE INDEX `bts_bugs_assigned_persons` ON `qualipso`.`bts_bugs` (`assignedTo` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`mail_attachments`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`mail_attachments` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_attachments` (
> `attachid` INT NOT NULL AUTO_INCREMENT ,
> `mailid` INT NOT NULL ,
> `filename` VARCHAR(250) NOT NULL ,
> `contentType` VARCHAR(100) NULL ,
> PRIMARY KEY (`attachid`) ,
> CONSTRAINT `mail_attachments_mail_mails`
> FOREIGN KEY (`mailid` )
> REFERENCES `qualipso`.`mail_mails` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `mail_attachments_mail_mails` ON `qualipso`.`mail_attachments` (`mailid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`doc_documents`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`doc_documents` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`doc_documents` (
> `itemid` INT NOT NULL ,
> `filename` VARCHAR(250) NULL ,
> `summary` LONGTEXT NULL ,
> `text` LONGTEXT NULL ,
> `lastModification` NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `doc_documents_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `doc_documents_info_items` ON `qualipso`.`doc_documents` (`itemid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`bts_attachments`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`bts_attachments` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`bts_attachments` (
> `attachid` INT NOT NULL AUTO_INCREMENT ,
> `bugid` INT NOT NULL ,
> `filename` VARCHAR(250) NOT NULL ,
> `contentType` VARCHAR(100) NULL ,
> PRIMARY KEY (`attachid`) ,
> CONSTRAINT `bts_attachments_bug_bugs`
> FOREIGN KEY (`bugid` )
> REFERENCES `qualipso`.`bts_bugs` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `bts_attachments_bug_bugs` ON `qualipso`.`bts_attachments` (`bugid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`doc_chapters`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`doc_chapters` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`doc_chapters` (
> `chapterid` INT NOT NULL AUTO_INCREMENT ,
> `docid` INT NOT NULL ,
> `level` INT NOT NULL ,
> `number` VARCHAR(60) NULL ,
> `title` VARCHAR(300) NULL ,
> `startText` INT NULL ,
> `endText` INT NULL ,
> PRIMARY KEY (`chapterid`) ,
> CONSTRAINT `doc_chapters_doc_documents`
> FOREIGN KEY (`docid` )
> REFERENCES `qualipso`.`doc_documents` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `doc_chapters_doc_documents` ON `qualipso`.`doc_chapters` (`docid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`doc_keywords`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`doc_keywords` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`doc_keywords` (
> `keywordid` INT NOT NULL AUTO_INCREMENT ,
> `docid` INT NOT NULL ,
> `keyword` VARCHAR(300) NOT NULL ,
> PRIMARY KEY (`keywordid`) ,
> CONSTRAINT `doc_keywords_doc_documents`
> FOREIGN KEY (`docid` )
> REFERENCES `qualipso`.`doc_documents` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `doc_keywords_doc_documents` ON `qualipso`.`doc_keywords` (`docid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`scm_commits`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`scm_commits` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`scm_commits` (
> `itemid` INT NOT NULL ,
> `committerid` INT NULL ,
> `message` LONGTEXT NULL ,
> `revision` INT NULL ,
> PRIMARY KEY (`itemid`) ,
> CONSTRAINT `scm_commits_info_items`
> FOREIGN KEY (`itemid` )
> REFERENCES `qualipso`.`info_items` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `scm_commits_persons`
> FOREIGN KEY (`committerid` )
> REFERENCES `qualipso`.`persons` (`personid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `scm_commits_info_items` ON `qualipso`.`scm_commits` (`itemid` ASC) ;
>
> CREATE INDEX `scm_commits_persons` ON `qualipso`.`scm_commits` (`committerid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`scm_files`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`scm_files` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`scm_files` (
> `fileid` INT NOT NULL AUTO_INCREMENT ,
> `filename` VARCHAR(1024) NOT NULL ,
> PRIMARY KEY (`fileid`) )
> ENGINE = InnoDB;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`scm_actions`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`scm_actions` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`scm_actions` (
> `actionid` INT NOT NULL AUTO_INCREMENT ,
> `commitid` INT NOT NULL ,
> `fileid` INT NULL ,
> `type` VARCHAR(100) NULL ,
> PRIMARY KEY (`actionid`) ,
> CONSTRAINT `scm_actions_scm_commits`
> FOREIGN KEY (`commitid` )
> REFERENCES `qualipso`.`scm_commits` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `scm_actions_scm_files`
> FOREIGN KEY (`fileid` )
> REFERENCES `qualipso`.`scm_files` (`fileid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `scm_actions_scm_commits` ON `qualipso`.`scm_actions` (`commitid` ASC) ;
>
> CREATE INDEX `scm_actions_scm_files` ON `qualipso`.`scm_actions` (`fileid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`scm_error_lines`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`scm_error_lines` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`scm_error_lines` (
> `lineid` INT NOT NULL AUTO_INCREMENT ,
> `actionid` INT NOT NULL ,
> `fileid` INT NOT NULL ,
> PRIMARY KEY (`lineid`) ,
> CONSTRAINT `scm_error_lines_scm_actions`
> FOREIGN KEY (`actionid` )
> REFERENCES `qualipso`.`scm_actions` (`actionid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION,
> CONSTRAINT `scm_error_lines_scm_files`
> FOREIGN KEY (`fileid` )
> REFERENCES `qualipso`.`scm_files` (`fileid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `scm_error_lines_scm_actions` ON `qualipso`.`scm_error_lines` (`actionid` ASC) ;
>
> CREATE INDEX `scm_error_lines_scm_files` ON `qualipso`.`scm_error_lines` (`fileid` ASC) ;
>
>
> -- -----------------------------------------------------
> -- Table `qualipso`.`mail_replies`
> -- -----------------------------------------------------
> DROP TABLE IF EXISTS `qualipso`.`mail_replies` ;
>
> CREATE TABLE IF NOT EXISTS `qualipso`.`mail_replies` (
> `replyid` INT NOT NULL AUTO_INCREMENT ,
> `mailid` INT NOT NULL ,
> `sequence` INT NOT NULL ,
> PRIMARY KEY (`replyid`) ,
> CONSTRAINT `mail_replies_mail_mails`
> FOREIGN KEY (`mailid` )
> REFERENCES `qualipso`.`mail_mails` (`itemid` )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
> CREATE INDEX `mail_replies_mail_mails` ON `qualipso`.`mail_replies` (`mailid` ASC) ;
>
>
>
> SET SQL_MODE=@OLD_SQL_MODE;
> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
More information about the Qualipso-a4
mailing list