db_structure.sql

-- MySQL Script generated by MySQL Workbench
-- 05/28/17 03:09:40
-- 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 hacktm
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `hacktm` ;

-- -----------------------------------------------------
-- Schema hacktm
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `hacktm` DEFAULT CHARACTER SET utf8 ;
USE `hacktm` ;

-- -----------------------------------------------------
-- Table `hacktm`.`companies`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`companies` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`companies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`services`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`services` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`services` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `alias` VARCHAR(127) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `alias_UNIQUE` (`alias` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`scrapers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`scrapers` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`scrapers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(127) NOT NULL,
  `url` VARCHAR(127) NOT NULL,
  `script_name` VARCHAR(127) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `last_updated` DATETIME NULL,
  `error` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
KEY_BLOCK_SIZE = 4;


-- -----------------------------------------------------
-- Table `hacktm`.`company_service`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`company_service` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`company_service` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `companies_id` INT NOT NULL,
  `services_id` INT NOT NULL,
  `scrapers_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_company_service_companies1_idx` (`companies_id` ASC),
  INDEX `fk_company_service_services1_idx` (`services_id` ASC),
  INDEX `fk_company_service_scrapers1_idx` (`scrapers_id` ASC),
  CONSTRAINT `fk_company_service_companies1`
    FOREIGN KEY (`companies_id`)
    REFERENCES `hacktm`.`companies` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_company_service_services1`
    FOREIGN KEY (`services_id`)
    REFERENCES `hacktm`.`services` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_company_service_scrapers1`
    FOREIGN KEY (`scrapers_id`)
    REFERENCES `hacktm`.`scrapers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`bundles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`bundles` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`bundles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `price` DECIMAL NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `scrapers_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_bundles_scrapers1_idx` (`scrapers_id` ASC),
  CONSTRAINT `fk_bundles_scrapers1`
    FOREIGN KEY (`scrapers_id`)
    REFERENCES `hacktm`.`scrapers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`packages`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`packages` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`packages` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_service_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL NOT NULL,
  `bundles_id` INT NULL,
  `scraper_id_hint` VARCHAR(127) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_packages_company_service1_idx` (`company_service_id` ASC),
  INDEX `fk_packages_bundles1_idx` (`bundles_id` ASC),
  UNIQUE INDEX `scraper_id_hint_UNIQUE` (`scraper_id_hint` ASC),
  CONSTRAINT `fk_packages_company_service1`
    FOREIGN KEY (`company_service_id`)
    REFERENCES `hacktm`.`company_service` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_packages_bundles1`
    FOREIGN KEY (`bundles_id`)
    REFERENCES `hacktm`.`bundles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`service_characteristics`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`service_characteristics` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`service_characteristics` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(250) NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `services_id` INT NOT NULL,
  `alias` VARCHAR(45) NOT NULL,
  `units` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_service_characteristics_services1_idx` (`services_id` ASC),
  UNIQUE INDEX `alias_UNIQUE` (`alias` ASC),
  CONSTRAINT `fk_service_characteristics_services1`
    FOREIGN KEY (`services_id`)
    REFERENCES `hacktm`.`services` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hacktm`.`package_characteristics`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hacktm`.`package_characteristics` ;

CREATE TABLE IF NOT EXISTS `hacktm`.`package_characteristics` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` VARCHAR(1024) NOT NULL,
  `packages_id` INT NOT NULL,
  `service_characteristics_id` INT NOT NULL,
  `units` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_package_characteristics_packages1_idx` (`packages_id` ASC),
  INDEX `fk_package_characteristics_service_characteristics1_idx` (`service_characteristics_id` ASC),
  CONSTRAINT `fk_package_characteristics_packages1`
    FOREIGN KEY (`packages_id`)
    REFERENCES `hacktm`.`packages` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_package_characteristics_service_characteristics1`
    FOREIGN KEY (`service_characteristics_id`)
    REFERENCES `hacktm`.`service_characteristics` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE = '';
GRANT USAGE ON *.* TO hacktm;
 DROP USER hacktm;
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE USER 'hacktm' IDENTIFIED BY 'hacktm';

GRANT ALL ON `hacktm`.* TO 'hacktm';

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

-- -----------------------------------------------------
-- Data for table `hacktm`.`companies`
-- -----------------------------------------------------
START TRANSACTION;
USE `hacktm`;
INSERT INTO `hacktm`.`companies` (`id`, `name`) VALUES (1, 'Telekom');
INSERT INTO `hacktm`.`companies` (`id`, `name`) VALUES (2, 'Orange');

COMMIT;


-- -----------------------------------------------------
-- Data for table `hacktm`.`services`
-- -----------------------------------------------------
START TRANSACTION;
USE `hacktm`;
INSERT INTO `hacktm`.`services` (`id`, `name`, `alias`) VALUES (1, 'Telefonie mobilă', 'mobile');
INSERT INTO `hacktm`.`services` (`id`, `name`, `alias`) VALUES (2, 'Televiziune', 'tv');
INSERT INTO `hacktm`.`services` (`id`, `name`, `alias`) VALUES (3, 'Internet', 'internet');
INSERT INTO `hacktm`.`services` (`id`, `name`, `alias`) VALUES (4, 'Energie', 'energy');
INSERT INTO `hacktm`.`services` (`id`, `name`, `alias`) VALUES (5, 'Gaz', 'gas');

COMMIT;


-- -----------------------------------------------------
-- Data for table `hacktm`.`scrapers`
-- -----------------------------------------------------
START TRANSACTION;
USE `hacktm`;
INSERT INTO `hacktm`.`scrapers` (`id`, `name`, `url`, `script_name`, `enabled`, `last_updated`, `error`) VALUES (1, 'Telekom mobile scraper', 'https://www.telekom.ro/tiles/block/onlineShopProductJson.jsp?targeter=OneSiteAbonamente&count=1&html=true', 'telekom/mbabon.py', 1, NULL, NULL);
INSERT INTO `hacktm`.`scrapers` (`id`, `name`, `url`, `script_name`, `enabled`, `last_updated`, `error`) VALUES (2, 'Telekom TV scraper', 'https://www.telekom.ro/category/televiziune-interactiva/cat9840485/', 'telekom/tv.py', 1, NULL, NULL);
INSERT INTO `hacktm`.`scrapers` (`id`, `name`, `url`, `script_name`, `enabled`, `last_updated`, `error`) VALUES (3, 'Telekom internet scraper', 'https://www.telekom.ro/category/net/cat9790490/', 'telekom/inet.py', 1, NULL, NULL);
INSERT INTO `hacktm`.`scrapers` (`id`, `name`, `url`, `script_name`, `enabled`, `last_updated`, `error`) VALUES (4, 'Orange mobile scraper', 'https://www.orange.ro/abonamente/orange-me/index.html', 'orange/mbabon.py', 1, NULL, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `hacktm`.`company_service`
-- -----------------------------------------------------
START TRANSACTION;
USE `hacktm`;
INSERT INTO `hacktm`.`company_service` (`id`, `companies_id`, `services_id`, `scrapers_id`) VALUES (DEFAULT, 1, 1, 1);
INSERT INTO `hacktm`.`company_service` (`id`, `companies_id`, `services_id`, `scrapers_id`) VALUES (DEFAULT, 1, 2, 2);
INSERT INTO `hacktm`.`company_service` (`id`, `companies_id`, `services_id`, `scrapers_id`) VALUES (DEFAULT, 1, 3, 3);
INSERT INTO `hacktm`.`company_service` (`id`, `companies_id`, `services_id`, `scrapers_id`) VALUES (DEFAULT, 2, 1, 4);

COMMIT;


-- -----------------------------------------------------
-- Data for table `hacktm`.`service_characteristics`
-- -----------------------------------------------------
START TRANSACTION;
USE `hacktm`;
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Minute naționale', 'scalar', 1, 'mobil_min_nat', '');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Trafic date', 'scalar', 1, 'mobil_date', 'MB');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'SMS naționale', 'scalar', 1, 'mobil_sms_nat', '');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Minute internaționale', 'scalar', 1, 'mobil_min_internat', '');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Viteză download (Mbps)', 'scalar', 3, 'inet_down', 'Mbps');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Viteză upload (Mbps)', 'scalar', 3, 'inet_up', 'Mbps');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Router inclus', 'boolean', 3, 'inet_router', ' ');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Receiver inclus', 'boolean', 2, 'tv_rcvr', ' ');
INSERT INTO `hacktm`.`service_characteristics` (`id`, `name`, `type`, `services_id`, `alias`, `units`) VALUES (DEFAULT, 'Număr canale', 'scalar', 2, 'tv_nchan', ' ');

COMMIT;

CREATE TABLE IF NOT EXISTS `hacktm`.`users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(250) NULL,
  `password` VARCHAR(250) NULL,
  `email` VARCHAR(45) NULL,
  `date_created` DATETIME NULL DEFAULT NOW(),
  `date_updated` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
  ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `hacktm`.`auth_keys` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` VARCHAR(45) NOT NULL,
  `key` VARCHAR(45) NOT NULL,
  `date_created` DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC),
  UNIQUE INDEX `key_UNIQUE` (`key` ASC))
  ENGINE = InnoDB;