· 8 years ago · Mar 20, 2018, 10:26 PM
1/*SCRIPT DATABASE GENERATOR*/
2
3USE FATTO;
4
5
6/*CREATE TABLE GENRES*/
7
8
9DROP TABLE IF EXISTS `genres`;
10CREATE TABLE `genres` (
11 `id_gr` int NOT NULL AUTO_INCREMENT COMMENT'idx',
12 `name` varchar(50) NOT NULL COMMENT'the genre type ie. terror, comedy, etc)',
13 `description` varchar(100) NOT NULL COMMENT'description of genre',
14 KEY `idx_genres_id_gr` (`id_gr`) COMMENT'index',
15 PRIMARY KEY (id_gr) COMMENT'unique key'
16)
17ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the genre type:';
18;
19
20insert into `genres`(`name`, `description`) values('Ação','');
21insert into `genres`(`name`, `description`) values('Animação','');
22insert into `genres`(`name`, `description`) values('Aventura','');
23insert into `genres`(`name`, `description`) values('Cinema de arte','');
24insert into `genres`(`name`, `description`) values('Chanchada','');
25insert into `genres`(`name`, `description`) values('Cinema catástrofe','');
26insert into `genres`(`name`, `description`) values('Comédia','');
27insert into `genres`(`name`, `description`) values('Comédia romântica','');
28insert into `genres`(`name`, `description`) values('Comédia dramática','');
29insert into `genres`(`name`, `description`) values('Comédia de ação','');
30insert into `genres`(`name`, `description`) values('Cult','');
31insert into `genres`(`name`, `description`) values('Dança','');
32insert into `genres`(`name`, `description`) values('Documentários','');
33insert into `genres`(`name`, `description`) values('Drama','');
34insert into `genres`(`name`, `description`) values('Espionagem','');
35insert into `genres`(`name`, `description`) values('Erótico','');
36insert into `genres`(`name`, `description`) values('Fantasia','');
37insert into `genres`(`name`, `description`) values('Faroeste','');
38insert into `genres`(`name`, `description`) values('Ficção cientÃfica','');
39insert into `genres`(`name`, `description`) values('Series','');
40insert into `genres`(`name`, `description`) values('Guerra','');
41insert into `genres`(`name`, `description`) values('Machinima','');
42insert into `genres`(`name`, `description`) values('Masala','');
43insert into `genres`(`name`, `description`) values('Musical','');
44insert into `genres`(`name`, `description`) values('Filme noir','');
45insert into `genres`(`name`, `description`) values('Policial','');
46insert into `genres`(`name`, `description`) values('Pornochanchada','');
47insert into `genres`(`name`, `description`) values('Pornográfico','');
48insert into `genres`(`name`, `description`) values('Robologia','');
49insert into `genres`(`name`, `description`) values('Romance','');
50insert into `genres`(`name`, `description`) values('Seriado','');
51insert into `genres`(`name`, `description`) values('Suspense','');
52insert into `genres`(`name`, `description`) values('Terror','');
53insert into `genres`(`name`, `description`) values('Trash','');
54
55==============================================================================================
56
57/*CREATE TABLE title basics*/
58
59DROP TABLE IF EXISTS `title.basics`;
60CREATE TABLE `title.basics` (
61 `id_tb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
62 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
63 `titleType` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
64 `primaryTitle` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
65 `originalTitle` varchar(100) NOT NULL COMMENT'original title, in the original language',
66 `isAdult` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
67 `startYear` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
68 `endYear` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
69 `runtimeMinutes` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
70 `genres` varchar(50) COMMENT 'includes up to three genres associated with the title',
71 KEY `idx_title.basics_id_tb` (`id_tb`) COMMENT'index',
72 KEY `idx_title.basics_tconst` (`tconst`) COMMENT'index',
73 PRIMARY KEY (tconst) COMMENT'unique key'
74)
75ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
76;
77-------------------------------------------------------------------------------------------------
78
79/*CREATE TABLE title basics audit*/
80
81DROP TABLE IF EXISTS `title.basics.Audit`;
82CREATE TABLE `title.basics.Audit` (
83 `id_tba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
84 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric unique identifier of the title',
85 `titleType_old` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
86 `titleType_new` varchar(50) NOT NULL COMMENT'the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)',
87 `primaryTitle_old` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
88 `primaryTitle_new` varchar(100) NOT NULL COMMENT'the more popular title / the title used by the filmmakers on promotional materials at the point of release',
89 `originalTitle_old` varchar(100) NOT NULL COMMENT'original title, in the original language',
90 `originalTitle_new` varchar(100) NOT NULL COMMENT'original title, in the original language',
91 `isAdult_old` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
92 `isAdult_new` tinyint(1) NOT NULL COMMENT'0: non-adult title; 1: adult title',
93 `startYear_old` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
94 `startYear_new` int(4) NOT NULL COMMENT'represents the release year of a title. In the case of TV Series, it is the series start year.',
95 `endYear_old` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
96 `endYear_new` int(4) DEFAULT NULL COMMENT'TV Series end year. ‘\N’ for all other title types',
97 `runtimeMinutes_old` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
98 `runtimeMinutes_new` int(5) NOT NULL COMMENT'primary runtime of the title, in minutes genre',
99 `genres_old` varchar(50) COMMENT 'includes up to three genres associated with the title',
100 `genres_new` varchar(50) COMMENT 'includes up to three genres associated with the title',
101 `logged_user` nvarchar(50) COMMENT'user action',
102 `oper_type` varchar(10) COMMENT'Operation type',
103 `tstamp` datetime,
104 KEY `idx_title.basics.Audit_id_tba` (`id_tba`) COMMENT'index',
105 KEY `idx_title.basics.Audit_tconst` (`tconst`) COMMENT'index',
106 PRIMARY KEY (tconst) COMMENT'unique key'
107)
108ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains the following information for titles:';
109;
110-------------------------------------------------------------------------------------------------
111
112/*CREATE TABLE title episode*/
113
114DROP TABLE IF EXISTS `title.episode`;
115CREATE TABLE `title.episode`(
116 `id_te` int NOT NULL AUTO_INCREMENT COMMENT'idx',
117 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
118 `parentTconst` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
119 `seasonNumber` SMALLINT COMMENT'season number the episode belongs to',
120 `episodeNumber` SMALLINT COMMENT'episode number of the tconst in the TV series',
121 KEY `idx_title.episode_id_te` (`id_te`) COMMENT'index',
122 KEY `idx_title.episode_tconst` (`tconst`) COMMENT'index',
123 PRIMARY KEY (tconst) COMMENT'unique key'
124 )
125ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
126;
127-------------------------------------------------------------------------------------------------
128
129/*CREATE TABLE title episode audit*/
130
131DROP TABLE IF EXISTS `title.episode.Audit`;
132CREATE TABLE `title.episode.Audit`(
133 `id_tea` int NOT NULL AUTO_INCREMENT COMMENT'idx',
134 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier of episode',
135 `parentTconst_old` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
136 `parentTconst_new` varchar(50) COMMENT'alphanumeric identifier of the parent TV Series',
137 `seasonNumber_old` SMALLINT COMMENT'season number the episode belongs to',
138 `seasonNumber_new` SMALLINT COMMENT'season number the episode belongs to',
139 `episodeNumber_old` SMALLINT COMMENT'episode number of the tconst in the TV series',
140 `episodeNumber_new` SMALLINT COMMENT'episode number of the tconst in the TV series',
141 `logged_user` nvarchar(50) COMMENT'user action',
142 `oper_type` varchar(10) COMMENT'Operation type',
143 `tstamp` datetime,
144 KEY `idx_title.episode.Audit_id_tea` (`id_tea`) COMMENT'index',
145 KEY `idx_title.episode.Audit_tconst` (`tconst`) COMMENT'index',
146 PRIMARY KEY (tconst) COMMENT'unique key'
147 )
148ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contains the tv episode information. Fields include'
149;
150-------------------------------------------------------------------------------------------------
151
152/*CREATE TABLE resources basics*/
153
154DROP TABLE IF EXISTS `resource.basics`;
155CREATE TABLE `resource.basics`(
156 `id_rb` int NOT NULL AUTO_INCREMENT COMMENT'idx',
157 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
158 `url` varchar(100) COMMENT'Media URL',
159 `poster` varchar(100) COMMENT'Poster URL',
160 KEY `idx_resource.basics_id_rb` (`id_rb`) COMMENT'index',
161 KEY `idx_tresource.basics_tconst` (`tconst`) COMMENT'index',
162 PRIMARY KEY (tconst) COMMENT'unique key'
163 )
164ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
165;
166-------------------------------------------------------------------------------------------------
167
168/*CREATE TABLE resources basics audit*/
169
170DROP TABLE IF EXISTS `resource.basics.Audit`;
171CREATE TABLE `resource.basics.Audit`(
172 `id_rba` int NOT NULL AUTO_INCREMENT COMMENT'idx',
173 `tconst` varchar(10) NOT NULL COMMENT'alphanumeric identifier basic',
174 `url_old` varchar(100) COMMENT'Media URL',
175 `url_new` varchar(100) COMMENT'Media URL',
176 `poster_old` varchar(100) COMMENT'Poster URL',
177 `poster_new` varchar(100) COMMENT'Poster URL',
178 `logged_user` nvarchar(50) COMMENT'user action',
179 `oper_type` varchar(10) COMMENT'Operation type',
180 `tstamp` datetime,
181 KEY `idx_resource.basics.Audit_id_rba` (`id_rba`) COMMENT'index',
182 KEY `idx_resource.basics.Audit_tconst` (`tconst`) COMMENT'index',
183 PRIMARY KEY (tconst) COMMENT'unique key'
184 )
185ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT'Contem os recursos para assistir'
186;
187
188==============================================================================================
189
190/* create trigger audit ins_resource.basics*/
191
192DELIMITER //
193drop trigger `ins_resource.basics`;
194create trigger `ins_resource.basics`
195 after Insert on `resource.basics`
196 FOR EACH ROW
197 BEGIN
198 INSERT INTO `resource.basics.audit`
199 (
200 `tconst`
201 ,`titleType_new`
202 ,`primaryTitle_new`
203 ,`originalTitle_new`
204 ,`isAdult_new`
205 ,`startYear_new`
206 ,`endYear_new`
207 ,`runtimeMinutes_new`
208 ,`genres_new`
209 ,`logged_user`
210 ,`oper_type`
211 ,`tstamp`
212 )
213 Values
214 (
215 `new.tconst`
216 ,`new.titleType_new`
217 ,`new.primaryTitle`
218 ,`new.originalTitle`
219 ,`new.isAdult`
220 ,`new.startYear`
221 ,`new.endYear`
222 ,`new.runtimeMinutes`
223 ,`new.genres`
224 , current_user()
225 ,'Insert'
226 ,getDate()
227 );
228 END//
229
230-------------------------------------------------------------------------------------------------
231
232DELIMITER //
233
234drop trigger `upd_resource.basics`;
235create trigger `upd_resource.basics`
236 after update on `resource.basics`
237 FOR EACH ROW
238 BEGIN
239 INSERT INTO `resource.basics.audit`
240 (
241 `tconst`
242 ,`titleType_old`
243 ,`titleType_new`
244 ,`primaryTitle_old`
245 ,`primaryTitle_new`
246 ,`originalTitle_old`
247 ,`originalTitle_new`
248 ,`isAdult_old`
249 ,`isAdult_new`
250 ,`startYear_old`
251 ,`startYear_new`
252 ,`endYear_old`
253 ,`endYear_new`
254 ,`runtimeMinutes_old`
255 ,`runtimeMinutes_new`
256 ,`genres_old`
257 ,`genres_new`
258 ,`logged_user`
259 ,`oper_type`
260 ,`tstamp`
261 )
262 Values
263 (
264 `new.tconst`
265 ,`old.titleType_new`
266 ,`new.titleType_new`
267 ,`old.primaryTitle`
268 ,`new.primaryTitle`
269 ,`old.originalTitle`
270 ,`new.originalTitle`
271 ,`old.isAdult`
272 ,`new.isAdult`
273 ,`old.startYear`
274 ,`new.startYear`
275 ,`old.endYear`
276 ,`new.endYear`
277 ,`old.runtimeMinutes`
278 ,`new.runtimeMinutes`
279 ,`old.genres`
280 ,`new.genres`
281 , current_user()
282 ,'update'
283 ,getDate()
284 );
285 END//
286
287-------------------------------------------------------------------------------------------------
288
289DELIMITER //
290
291drop trigger `del_resource.basics`;
292create trigger `del_resource.basics`
293 after delete on `resource.basics`
294 FOR EACH ROW
295 BEGIN
296 INSERT INTO `resource.basics.audit`
297 (
298 `tconst`
299 ,`titleType_old`
300 ,`primaryTitle_old`
301 ,`originalTitle_old`
302 ,`isAdult_old`
303 ,`startYear_old`
304 ,`endYear_old`
305 ,`runtimeMinutes_old`
306 ,`genres_old`
307 ,`logged_user`
308 ,`oper_type`
309 ,`tstamp`
310 )
311 Values
312 (
313 `new.tconst`
314 ,`old.titleType_new`
315 ,`old.primaryTitle`
316 ,`old.originalTitle`
317 ,`old.isAdult`
318 ,`old.startYear`
319 ,`old.endYear`
320 ,`old.runtimeMinutes`
321 ,`old.genres`
322 , current_user()
323 ,'delete'
324 ,getDate()
325 );
326 END//
327
328==============================================================================================