OpenPastebin

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 `lockstock` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `lockstock`;

-- -----------------------------------------------------
-- Table `lockstock`.`permission`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`permission` (
  `permission_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `permission_code` VARCHAR(80) NOT NULL ,
  `permission_desc` VARCHAR(250) NULL DEFAULT NULL ,
  PRIMARY KEY (`permission_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0;


-- -----------------------------------------------------
-- Table `lockstock`.`rss_source`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`rss_source` (
  `rss_source_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `rss_source_uri` VARCHAR(255) NOT NULL ,
  `rss_source_name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`rss_source_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_group`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_group` (
  `user_group_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_group_name` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`user_group_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0;


-- -----------------------------------------------------
-- Table `lockstock`.`client_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`client_type` (
  `client_type_id` INT NOT NULL AUTO_INCREMENT ,
  `client_type_desc` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`client_type_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user` (
  `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_group_id` INT(10) UNSIGNED NOT NULL ,
  `client_type_id` INT NOT NULL ,
  `user_login` VARCHAR(20) NOT NULL ,
  `user_pass` VARCHAR(40) NOT NULL COMMENT 'sha1' ,
  `user_first_name` VARCHAR(40) NOT NULL ,
  `user_surname` VARCHAR(45) NOT NULL ,
  `user_email` VARCHAR(100) NOT NULL ,
  `user_address` VARCHAR(255) NOT NULL ,
  `user_phone` VARCHAR(255) NOT NULL ,
  `user_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_user_user_group1` (`user_group_id` ASC) ,
  INDEX `fk_user_client_type1` (`client_type_id` ASC) ,
  CONSTRAINT `fk_user_user_group1`
    FOREIGN KEY (`user_group_id` )
    REFERENCES `lockstock`.`user_group` (`user_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_client_type1`
    FOREIGN KEY (`client_type_id` )
    REFERENCES `lockstock`.`client_type` (`client_type_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0;


-- -----------------------------------------------------
-- Table `lockstock`.`currency`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`currency` (
  `currency_id` INT NOT NULL AUTO_INCREMENT ,
  `currency_name` VARCHAR(45) NOT NULL COMMENT 'koruna, zizala whatever' ,
  `currency_code` VARCHAR(45) NOT NULL COMMENT 'USD' ,
  PRIMARY KEY (`currency_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`xchange_rate_history`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`xchange_rate_history` (
  `currency_id` INT NOT NULL ,
  `xchange_rate_history_date` DATE NOT NULL ,
  `xchange_rate_history_rate` DECIMAL(10,3) UNSIGNED NOT NULL ,
  `xchange_rate_history_amount` SMALLINT UNSIGNED NULL ,
  PRIMARY KEY (`currency_id`, `xchange_rate_history_date`) ,
  INDEX `fk_xchange_rate_history_currency1` (`currency_id` ASC) ,
  CONSTRAINT `fk_xchange_rate_history_currency1`
    FOREIGN KEY (`currency_id` )
    REFERENCES `lockstock`.`currency` (`currency_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_group_has_permission`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_group_has_permission` (
  `user_group_id` INT(10) UNSIGNED NOT NULL ,
  `permission_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`user_group_id`, `permission_id`) ,
  INDEX `fk_user_group_has_permission1_user_group1` (`user_group_id` ASC) ,
  INDEX `fk_user_group_has_permission1_permission1` (`permission_id` ASC) ,
  CONSTRAINT `fk_user_group_has_permission1_user_group1`
    FOREIGN KEY (`user_group_id` )
    REFERENCES `lockstock`.`user_group` (`user_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_group_has_permission1_permission1`
    FOREIGN KEY (`permission_id` )
    REFERENCES `lockstock`.`permission` (`permission_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`news`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`news` (
  `news_id` INT NOT NULL AUTO_INCREMENT ,
  `user_group_id` INT(10) UNSIGNED NULL ,
  `news_text` TEXT NOT NULL ,
  PRIMARY KEY (`news_id`) ,
  INDEX `fk_news_user_group1` (`user_group_id` ASC) ,
  CONSTRAINT `fk_news_user_group1`
    FOREIGN KEY (`user_group_id` )
    REFERENCES `lockstock`.`user_group` (`user_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`symbol`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`symbol` (
  `symbol_id` INT NOT NULL AUTO_INCREMENT ,
  `symbol_code` VARCHAR(45) NOT NULL ,
  `symbol_exchange` VARCHAR(45) NOT NULL ,
  `symbol_currency` VARCHAR(45) NOT NULL ,
  `symbol_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`symbol_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = 'seznam titulu/indexu na burzach';


-- -----------------------------------------------------
-- Table `lockstock`.`user_has_symbol`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_has_symbol` (
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `symbol_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`, `symbol_id`) ,
  INDEX `fk_user_has_symbol_user1` (`user_id` ASC) ,
  INDEX `fk_user_has_symbol_symbol1` (`symbol_id` ASC) ,
  CONSTRAINT `fk_user_has_symbol_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `lockstock`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_symbol_symbol1`
    FOREIGN KEY (`symbol_id` )
    REFERENCES `lockstock`.`symbol` (`symbol_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`symbol_history`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`symbol_history` (
  `symbol_id` INT NOT NULL ,
  `symbol_history_date` DATETIME NOT NULL ,
  `symbol_history_open` DECIMAL(14,4) NOT NULL ,
  `symbol_history_high` DECIMAL(14,4) NOT NULL ,
  `symbol_history_low` DECIMAL(14,4) NOT NULL ,
  `symbol_history_close` DECIMAL(14,4) NOT NULL ,
  `symbol_history_volume` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`symbol_id`, `symbol_history_date`) ,
  INDEX `fk_symbol_history_symbol1` (`symbol_id` ASC) ,
  CONSTRAINT `fk_symbol_history_symbol1`
    FOREIGN KEY (`symbol_id` )
    REFERENCES `lockstock`.`symbol` (`symbol_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_has_currency`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_has_currency` (
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `currency_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`, `currency_id`) ,
  INDEX `fk_user_has_currency_user1` (`user_id` ASC) ,
  INDEX `fk_user_has_currency_currency1` (`currency_id` ASC) ,
  CONSTRAINT `fk_user_has_currency_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `lockstock`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_currency_currency1`
    FOREIGN KEY (`currency_id` )
    REFERENCES `lockstock`.`currency` (`currency_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`dictionary`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`dictionary` (
  `dictionary_entry` VARCHAR(255) NOT NULL ,
  `dictionary_value` TEXT NOT NULL ,
  UNIQUE INDEX `UNIQUE` (`dictionary_entry` ASC) ,
  PRIMARY KEY (`dictionary_entry`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`rss_article`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`rss_article` (
  `rss_article_id` INT NOT NULL AUTO_INCREMENT ,
  `rss_source_id` SMALLINT(5) UNSIGNED NOT NULL ,
  `rss_article_date` DATETIME NOT NULL ,
  `rss_article_uri` VARCHAR(255) NOT NULL ,
  `rss_article_contents` TEXT NOT NULL ,
  PRIMARY KEY (`rss_article_id`) ,
  INDEX `fk_rss_article_rss_source1` (`rss_source_id` ASC) ,
  CONSTRAINT `fk_rss_article_rss_source1`
    FOREIGN KEY (`rss_source_id` )
    REFERENCES `lockstock`.`rss_source` (`rss_source_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_group_has_rss_article`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_group_has_rss_article` (
  `user_group_id` INT(10) UNSIGNED NOT NULL ,
  `rss_article_id` INT NOT NULL ,
  PRIMARY KEY (`user_group_id`, `rss_article_id`) ,
  INDEX `fk_user_group_has_rss_article_user_group1` (`user_group_id` ASC) ,
  INDEX `fk_user_group_has_rss_article_rss_article1` (`rss_article_id` ASC) ,
  CONSTRAINT `fk_user_group_has_rss_article_user_group1`
    FOREIGN KEY (`user_group_id` )
    REFERENCES `lockstock`.`user_group` (`user_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_group_has_rss_article_rss_article1`
    FOREIGN KEY (`rss_article_id` )
    REFERENCES `lockstock`.`rss_article` (`rss_article_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`document`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`document` (
  `document_id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `document_title` VARCHAR(255) NOT NULL ,
  `document_src` VARCHAR(255) NOT NULL ,
  `document_tar` VARCHAR(255) NOT NULL COMMENT 'treba navazat na stranku - zrejme to bude pres idcka, az bude jasna struktura menu atd... @TODO' ,
  PRIMARY KEY (`document_id`) ,
  INDEX `fk_document_user1` (`user_id` ASC) ,
  CONSTRAINT `fk_document_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `lockstock`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_group_has_document`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_group_has_document` (
  `user_group_id` INT(10) UNSIGNED NOT NULL ,
  `document_id` INT NOT NULL ,
  PRIMARY KEY (`user_group_id`, `document_id`) ,
  INDEX `fk_user_group_has_document_user_group1` (`user_group_id` ASC) ,
  INDEX `fk_user_group_has_document_document1` (`document_id` ASC) ,
  CONSTRAINT `fk_user_group_has_document_user_group1`
    FOREIGN KEY (`user_group_id` )
    REFERENCES `lockstock`.`user_group` (`user_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_group_has_document_document1`
    FOREIGN KEY (`document_id` )
    REFERENCES `lockstock`.`document` (`document_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`broker`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`broker` (
  `broker_id` INT NOT NULL AUTO_INCREMENT ,
  `broker_name` VARCHAR(255) NULL ,
  PRIMARY KEY (`broker_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`mcent_salsesman`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`mcent_salsesman` (
  `mcent_salsesman_id` INT NOT NULL AUTO_INCREMENT ,
  `mcent_salsesman_name` VARCHAR(255) NULL ,
  PRIMARY KEY (`mcent_salsesman_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_has_broker`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_has_broker` (
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `broker_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`, `broker_id`) ,
  INDEX `fk_user_has_broker_user1` (`user_id` ASC) ,
  INDEX `fk_user_has_broker_broker1` (`broker_id` ASC) ,
  CONSTRAINT `fk_user_has_broker_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `lockstock`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_broker_broker1`
    FOREIGN KEY (`broker_id` )
    REFERENCES `lockstock`.`broker` (`broker_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `lockstock`.`user_has_mcent_salsesman`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lockstock`.`user_has_mcent_salsesman` (
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `mcent_salsesman_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`, `mcent_salsesman_id`) ,
  INDEX `fk_user_has_mcent_salsesman_user1` (`user_id` ASC) ,
  INDEX `fk_user_has_mcent_salsesman_mcent_salsesman1` (`mcent_salsesman_id` ASC) ,
  CONSTRAINT `fk_user_has_mcent_salsesman_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `lockstock`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_mcent_salsesman_mcent_salsesman1`
    FOREIGN KEY (`mcent_salsesman_id` )
    REFERENCES `lockstock`.`mcent_salsesman` (`mcent_salsesman_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
:: Download	15385.txt	15385.txt.gz	15385.html	15385.pdf
:: Print into	HTML document		PDF document