· 8 years ago · Mar 01, 2018, 12:08 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema moviedash
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema moviedash
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `moviedash` DEFAULT CHARACTER SET utf8 ;
18USE `moviedash` ;
19
20-- -----------------------------------------------------
21-- Table `moviedash`.`genres`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `moviedash`.`genres` (
24 `id` INT(11) NOT NULL AUTO_INCREMENT,
25 `description` VARCHAR(45) NOT NULL,
26 `isActive` TINYINT(4) NOT NULL DEFAULT 1,
27 PRIMARY KEY (`id`))
28ENGINE = InnoDB
29AUTO_INCREMENT = 21
30DEFAULT CHARACTER SET = utf8
31COMMENT = ' ';
32
33
34-- -----------------------------------------------------
35-- Table `moviedash`.`items_type`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `moviedash`.`items_type` (
38 `id` INT(11) NOT NULL AUTO_INCREMENT,
39 `description` VARCHAR(45) NULL DEFAULT NULL,
40 `isActive` TINYINT(4) NULL DEFAULT 1,
41 PRIMARY KEY (`id`))
42ENGINE = InnoDB
43AUTO_INCREMENT = 3
44DEFAULT CHARACTER SET = utf8;
45
46
47-- -----------------------------------------------------
48-- Table `moviedash`.`items`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `moviedash`.`items` (
51 `id` INT(11) NOT NULL AUTO_INCREMENT,
52 `type` INT(11) NOT NULL,
53 `title` VARCHAR(100) NOT NULL,
54 `resume` VARCHAR(300) NULL DEFAULT NULL,
55 `rating` FLOAT NULL DEFAULT 5,
56 `thumbnail` VARCHAR(100) NULL DEFAULT NULL,
57 `isActive` TINYINT(4) NULL DEFAULT 1,
58 PRIMARY KEY (`id`),
59 INDEX `items_type_FK_idx` (`type` ASC),
60 CONSTRAINT `items_type_FK`
61 FOREIGN KEY (`type`)
62 REFERENCES `moviedash`.`items_type` (`id`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB
66AUTO_INCREMENT = 3
67DEFAULT CHARACTER SET = utf8
68COMMENT = 'It stores main data of movies and TV series.';
69
70
71-- -----------------------------------------------------
72-- Table `moviedash`.`items_fragment`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `moviedash`.`items_fragment` (
75 `id` INT(11) NOT NULL AUTO_INCREMENT,
76 `mainItem` INT(11) NOT NULL,
77 `episode` INT(11) NULL DEFAULT NULL,
78 `season` INT(11) NULL DEFAULT NULL,
79 `title` VARCHAR(45) NOT NULL,
80 `resume` VARCHAR(200) NULL DEFAULT NULL,
81 `thumbnail` VARCHAR(45) NULL DEFAULT NULL,
82 `isActive` TINYINT(4) NOT NULL DEFAULT 1,
83 `isAvailable` TINYINT(4) NOT NULL DEFAULT 1,
84 PRIMARY KEY (`id`),
85 INDEX `items_FK_idx` (`mainItem` ASC),
86 CONSTRAINT `items_FK`
87 FOREIGN KEY (`mainItem`)
88 REFERENCES `moviedash`.`items` (`id`)
89 ON UPDATE CASCADE)
90ENGINE = InnoDB
91AUTO_INCREMENT = 6
92DEFAULT CHARACTER SET = utf8
93COMMENT = 'It stores data about episodes or movies.';
94
95
96-- -----------------------------------------------------
97-- Table `moviedash`.`items_fragment_video`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `moviedash`.`items_fragment_video` (
100 `id` INT(11) NOT NULL AUTO_INCREMENT,
101 `item_fragment` INT(11) NOT NULL,
102 `portugueseVersion` VARCHAR(200) NULL DEFAULT NULL,
103 `internationalVersion` VARCHAR(200) NULL DEFAULT NULL,
104 `isActive` TINYINT(4) NULL DEFAULT NULL,
105 PRIMARY KEY (`id`),
106 INDEX `fragment_FK_idx` (`item_fragment` ASC),
107 CONSTRAINT `fragment_FK`
108 FOREIGN KEY (`item_fragment`)
109 REFERENCES `moviedash`.`items_fragment` (`id`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112ENGINE = InnoDB
113AUTO_INCREMENT = 8
114DEFAULT CHARACTER SET = utf8;
115
116
117-- -----------------------------------------------------
118-- Table `moviedash`.`items_genre`
119-- -----------------------------------------------------
120CREATE TABLE IF NOT EXISTS `moviedash`.`items_genre` (
121 `item_id` INT(11) NOT NULL,
122 `genre_id` INT(11) NOT NULL,
123 PRIMARY KEY (`item_id`, `genre_id`),
124 INDEX `genre_FK_idx` (`genre_id` ASC),
125 CONSTRAINT `genre_FK`
126 FOREIGN KEY (`genre_id`)
127 REFERENCES `moviedash`.`genres` (`id`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `item_FK`
131 FOREIGN KEY (`item_id`)
132 REFERENCES `moviedash`.`items` (`id`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION)
135ENGINE = InnoDB
136DEFAULT CHARACTER SET = utf8;
137
138USE `moviedash`;
139
140DELIMITER $$
141USE `moviedash`$$
142CREATE
143DEFINER=`darnley`@`%`
144TRIGGER `moviedash`.`items_fragment_video_BEFORE_INSERT`
145BEFORE INSERT ON `moviedash`.`items_fragment_video`
146FOR EACH ROW
147BEGIN
148 IF NEW.portugueseVersion = '' THEN
149 SET NEW.portugueseVersion = NULL;
150 END IF;
151 IF NEW.internationalVersion = '' THEN
152 SET NEW.internationalVersion = NULL;
153 END IF;
154END$$
155
156USE `moviedash`$$
157CREATE
158DEFINER=`darnley`@`%`
159TRIGGER `moviedash`.`items_fragment_video_BEFORE_UPDATE`
160BEFORE UPDATE ON `moviedash`.`items_fragment_video`
161FOR EACH ROW
162BEGIN
163 IF NEW.portugueseVersion = '' THEN
164 SET NEW.portugueseVersion = NULL;
165 END IF;
166 IF NEW.internationalVersion = '' THEN
167 SET NEW.internationalVersion = NULL;
168 END IF;
169END$$
170
171
172DELIMITER ;
173
174SET SQL_MODE=@OLD_SQL_MODE;
175SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
176SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;