createDB.sql

-- MySQL Script generated by MySQL Workbench
-- 17-12-07 16:03:03 CET
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema talaiaDB
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `talaiaDB` ;

-- -----------------------------------------------------
-- Schema talaiaDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `talaiaDB` DEFAULT CHARACTER SET utf8mb4 ;
USE `talaiaDB` ;

-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_source`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_source` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_source` (
  `source_id` BIGINT(20) NOT NULL DEFAULT '0',
  `type` VARCHAR(10) CHARACTER SET 'utf8' NOT NULL,
  `influence` DOUBLE NULL DEFAULT NULL,
  `source_name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL,
  `last_fetch` LONGTEXT CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `user_id` INT(11) NULL DEFAULT NULL,
  `domain` LONGTEXT CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `geoinfo` VARCHAR(80) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `followers` INT(11) NULL DEFAULT NULL,
  `friends` INT(11) NULL DEFAULT NULL,
  `is_local_area` TINYINT(1) NULL DEFAULT '0',
  `avatar` VARCHAR(250) CHARACTER SET 'utf8' NULL DEFAULT '',
  PRIMARY KEY (`source_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE INDEX `source_index_2type` ON `talaiaDB`.`behagunea_app_source` (`type` ASC, `influence` ASC);

CREATE INDEX `source_index_1inf` ON `talaiaDB`.`behagunea_app_source` (`influence` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_feed`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_feed` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_feed` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `url` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `lang` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `last_fetch` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `description` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `source_id` BIGINT(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `behagunea_app_feed_source_id_27100ef35f5168dc_fk`
    FOREIGN KEY (`source_id`)
    REFERENCES `talaiaDB`.`behagunea_app_source` (`source_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 57
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE INDEX `behagunea_a_source_id_2c8c8e24_fk_behagunea_app_source_source_id` ON `talaiaDB`.`behagunea_app_feed` (`source_id` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_keyword`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_keyword` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_keyword` (
  `keyword_id` INT(11) NOT NULL,
  `type` VARCHAR(10) CHARACTER SET 'utf8' NOT NULL,
  `lang` VARCHAR(5) CHARACTER SET 'utf8' NOT NULL,
  `category` VARCHAR(40) CHARACTER SET 'utf8' NOT NULL,
  `subCat` VARCHAR(40) CHARACTER SET 'utf8' NOT NULL,
  `term` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `anchor` TINYINT(1) NOT NULL,
  `is_anchor` TINYINT(1) NOT NULL,
  `screen_tag` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `is_kword` TINYINT(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`keyword_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE INDEX `k_type` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`type` ASC);

CREATE INDEX `k_lang` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`lang` ASC);

CREATE INDEX `k_cat` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`category` ASC);

CREATE INDEX `k_scrtag` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`screen_tag`(12) ASC);

CREATE INDEX `k_subcat` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`subCat` ASC);

CREATE INDEX `k_term` USING BTREE ON `talaiaDB`.`behagunea_app_keyword` (`term`(12) ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_mention`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_mention` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_mention` (
  `mention_id` INT(11) NOT NULL,
  `date` DATETIME NOT NULL,
  `url` LONGTEXT NOT NULL,
  `text` LONGTEXT NOT NULL,
  `lang` VARCHAR(5) NOT NULL,
  `polarity` VARCHAR(10) NOT NULL,
  `favourites` INT(11) NOT NULL,
  `retweets` INT(11) NOT NULL,
  `source_id` BIGINT(20) NULL DEFAULT NULL,
  `corrected` TINYINT(1) NOT NULL DEFAULT '0',
  `manual_polarity` VARCHAR(10) NULL DEFAULT NULL,
  `geoinfo` VARCHAR(80) NULL DEFAULT NULL,
  `cluster_id` INT(11) NULL DEFAULT NULL,
  `native_id` BIGINT(30) NULL DEFAULT NULL,
  `retweet_id` BIGINT(30) NULL DEFAULT NULL,
  `quote_id` BIGINT(30) NULL DEFAULT NULL,
  `is_local_area` TINYINT(1) NULL DEFAULT '0',
  `offset` VARCHAR(30) NOT NULL DEFAULT 'NULL',
  `media_url` LONGTEXT NOT NULL,
  `feed_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`mention_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX `orig_tweet_id_UNIQUE` ON `talaiaDB`.`behagunea_app_mention` (`native_id` ASC);

CREATE INDEX `behagunea_app_mention_0afd9202` ON `talaiaDB`.`behagunea_app_mention` (`source_id` ASC);

CREATE INDEX `fk_behagunea_app_mention_cluster1_idx` ON `talaiaDB`.`behagunea_app_mention` (`cluster_id` ASC);

CREATE INDEX `mention_index1` ON `talaiaDB`.`behagunea_app_mention` (`is_local_area` ASC, `retweet_id` ASC, `date` ASC);

CREATE INDEX `mention_index2` ON `talaiaDB`.`behagunea_app_mention` (`is_local_area` ASC, `retweet_id` ASC, `date` ASC, `manual_polarity` ASC);

CREATE INDEX `m_pol` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`polarity` ASC);

CREATE INDEX `m_manualpol` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`manual_polarity` ASC);

CREATE INDEX `m_date` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`date` ASC);

CREATE INDEX `m_local` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`is_local_area` ASC);

CREATE INDEX `m_rtid` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`retweet_id` ASC);

CREATE INDEX `m_lang` USING BTREE ON `talaiaDB`.`behagunea_app_mention` (`lang` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_keyword_mention`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_keyword_mention` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_keyword_mention` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `keyword_id` INT(11) NOT NULL,
  `mention_id` INT(11) NOT NULL,
  `polarity` VARCHAR(10) CHARACTER SET 'utf8' NOT NULL DEFAULT 'NULL',
  `manual_polarity` VARCHAR(10) CHARACTER SET 'utf8' NOT NULL DEFAULT 'NULL',
  `corrected` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  CONSTRAINT `behagune_keyword_id_e1d999f2_fk_behagunea_app_keyword_keyword_id`
    FOREIGN KEY (`keyword_id`)
    REFERENCES `talaiaDB`.`behagunea_app_keyword` (`keyword_id`),
  CONSTRAINT `mention_id_4844d90a89de1133_fk_behagunea_app_mention_mention_id`
    FOREIGN KEY (`mention_id`)
    REFERENCES `talaiaDB`.`behagunea_app_mention` (`mention_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2823995
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE INDEX `behagune_keyword_id_e1d999f2_fk_behagunea_app_keyword_keyword_id` ON `talaiaDB`.`behagunea_app_keyword_mention` (`keyword_id` ASC);

CREATE INDEX `behagunea_app_keyword_mention_31e0dc7e` ON `talaiaDB`.`behagunea_app_keyword_mention` (`mention_id` ASC);

CREATE INDEX `keyword_mention_index1` ON `talaiaDB`.`behagunea_app_keyword_mention` (`manual_polarity` ASC);

CREATE INDEX `keyword_mention_index2` ON `talaiaDB`.`behagunea_app_keyword_mention` (`keyword_id` ASC, `manual_polarity` ASC);

CREATE INDEX `km_manualpol` USING BTREE ON `talaiaDB`.`behagunea_app_keyword_mention` (`manual_polarity` ASC);

CREATE INDEX `km_pol` USING BTREE ON `talaiaDB`.`behagunea_app_keyword_mention` (`polarity` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_user` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(15) CHARACTER SET 'utf8' NOT NULL,
  `firstname` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `surname` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `email` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `affiliation` LONGTEXT CHARACTER SET 'utf8' NOT NULL,
  `keyword_admin` TINYINT(1) NOT NULL,
  `user_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `behagunea_app_user_user_id_0889f962_fk_auth_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `talaiaDB`.`auth_user` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE UNIQUE INDEX `user_id` ON `talaiaDB`.`behagunea_app_user` (`user_id` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_user_feed`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_user_feed` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_user_feed` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `feed_id` INT(11) NOT NULL,
  `active` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  CONSTRAINT `behagunea_app_user_feed_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `talaiaDB`.`behagunea_app_user` (`id`),
  CONSTRAINT `behagune_app_user_feed_feed_id`
    FOREIGN KEY (`feed_id`)
    REFERENCES `talaiaDB`.`behagunea_app_feed` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 50
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE UNIQUE INDEX `behagune_app_user_feed_unique` ON `talaiaDB`.`behagunea_app_user_feed` (`user_id` ASC, `feed_id` ASC);

CREATE INDEX `behagune_app_user_feed_feed_id` ON `talaiaDB`.`behagunea_app_user_feed` (`feed_id` ASC);


-- -----------------------------------------------------
-- Table `talaiaDB`.`behagunea_app_user_keyword`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `talaiaDB`.`behagunea_app_user_keyword` ;

CREATE TABLE IF NOT EXISTS `talaiaDB`.`behagunea_app_user_keyword` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `keyword_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `behagunea_app_user_key_user_id_3b72d5ef_fk_behagunea_app_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `talaiaDB`.`behagunea_app_user` (`id`),
  CONSTRAINT `behagune_keyword_id_01abd3d8_fk_behagunea_app_keyword_keyword_id`
    FOREIGN KEY (`keyword_id`)
    REFERENCES `talaiaDB`.`behagunea_app_keyword` (`keyword_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 191
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

CREATE INDEX `behagune_keyword_id_01abd3d8_fk_behagunea_app_keyword_keyword_id` ON `talaiaDB`.`behagunea_app_user_keyword` (`keyword_id` ASC);

CREATE INDEX `behagunea_app_user_key_user_id_3b72d5ef_fk_behagunea_app_user_id` ON `talaiaDB`.`behagunea_app_user_keyword` (`user_id` ASC);


-- -----------------------------------------------------
-- User talaia
-- -----------------------------------------------------

SHOW WARNINGS;
SET SQL_MODE = '';
GRANT USAGE ON *.* TO talaia;
 DROP USER talaia;
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
SHOW WARNINGS;
CREATE USER 'talaia' IDENTIFIED BY 'talaiaAdmin';

GRANT ALL ON `talaiaDB`.* TO 'talaia';
GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `talaiaDB`.* TO 'moodmap';
GRANT SELECT ON TABLE `talaiaDB`.* TO 'talaia';
GRANT SELECT, INSERT, TRIGGER ON TABLE `talaiaDB`.* TO 'talaia';
SHOW WARNINGS;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;