createTables.sql 12.7 KB
Newer Older
kcchik's avatar
kcchik committed
1
drop table if exists MaxIDs;
kcchik's avatar
lmao    
kcchik committed
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table if exists ProductionCompaniesInMovies;
drop table if exists ProductionCompany;
drop table if exists LanguagesInMovies;
drop table if exists Languages;
drop table if exists CountriesInMovies;
drop table if exists Countries;
drop table if exists GenresInMovies;
drop table if exists Genres;
drop table if exists Reviews;
drop table if exists UsersFavouriteMovies;
drop table if exists Users;
drop table if exists PeopleInMovies;
drop table if exists DeadPeople;
drop table if exists People;
drop table if exists Budget;
drop table if exists Income;
kcchik's avatar
sql  
kcchik committed
18
drop table if exists Movies;
kcchik's avatar
lmao    
kcchik committed
19

kcchik's avatar
sql  
kcchik committed
20
create table Movies (
kcchik's avatar
kcchik committed
21
  movieID char(9) not null,
kcchik's avatar
sql  
kcchik committed
22
  title varchar(256),
kcchik's avatar
lmao    
kcchik committed
23
24
  originalTitle varchar(256),
  year int,
kcchik's avatar
kcchik committed
25
  releaseDate datetime,
kcchik's avatar
lmao    
kcchik committed
26
27
28
29
30
  duration int,
  description text,
  averageRating float,
  totalRating int,
  metascore float
kcchik's avatar
sql  
kcchik committed
31
32
);
alter table Movies add primary key (movieID);
kcchik's avatar
kcchik committed
33
replace into Movies (movieID, title) select movieID, title from MojoBudgetData;
kcchik's avatar
kcchik committed
34
35
replace into Movies (movieID, title, releaseDate, description) select movieID, title, releaseDate, tagline from MoviesMetadata;
replace into Movies (movieID, title, releaseDate, description) select movieID, title, releaseDate, trivia from MojoBudgetUpdate;
kcchik's avatar
lmao    
kcchik committed
36
37
replace into Movies (movieID, title, originalTitle, year, releaseDate, duration, description, averageRating, totalRating, metascore)
select movieID, title, originalTitle, year, releaseDate, duration, description, score, votes, metascore from ImdbMovies;
Daniel Kim's avatar
Daniel Kim committed
38
alter table Movies add index yrIndex(year);
kcchik's avatar
sql  
kcchik committed
39
40

create table Income (
kcchik's avatar
kcchik committed
41
  movieID char(9) not null,
kcchik's avatar
kcchik committed
42
43
  incomeDomestic bigint,
  incomeWorldwide bigint
kcchik's avatar
sql  
kcchik committed
44
);
kcchik's avatar
kcchik committed
45
alter table Income add primary key (movieID);
kcchik's avatar
sql  
kcchik committed
46
alter table Income add foreign key (movieID) references Movies(movieID);
kcchik's avatar
kcchik committed
47
48
49
50
51
52
53
54
replace into Income (movieID, incomeWorldwide) select movieID, revenue from MoviesMetadata;
replace into Income (movieID, incomeDomestic, incomeWorldwide) select movieID, domestic, worldwide from MojoBudgetData;
replace into Income (movieID, incomeDomestic, incomeWorldwide) select movieID, domestic, worldwide from MojoBudgetUpdate;
replace into Income (movieID, incomeDomestic, incomeWorldwide) select
  movieID,
  cast(regexp_substr(grossIncomeUSA, '[^$ ]*$') as unsigned),
  cast(regexp_substr(grossIncomeWorldwide, '[^$ ]*$') as unsigned)
from ImdbMovies;
kcchik's avatar
sql  
kcchik committed
55
56

create table Budget (
kcchik's avatar
kcchik committed
57
  movieID char(9) not null,
kcchik's avatar
kcchik committed
58
  budget bigint
kcchik's avatar
sql  
kcchik committed
59
);
kcchik's avatar
kcchik committed
60
alter table Budget add primary key (movieID);
kcchik's avatar
sql  
kcchik committed
61
alter table Budget add foreign key (movieID) references Movies(movieID);
kcchik's avatar
kcchik committed
62
63
64
65
66
67
replace into Budget (movieID, budget) select movieID, budget from MojoBudgetData;
replace into Budget (movieID, budget) select movieID, budget from MojoBudgetUpdate;
replace into Budget (movieID, budget) select
  movieID,
  cast(regexp_substr(budget, '[^$ ]*$') as unsigned)
from ImdbMovies;
kcchik's avatar
sql  
kcchik committed
68

kcchik's avatar
lmao    
kcchik committed
69
70
-- person stuff
create table People (
kcchik's avatar
kcchik committed
71
  personID char(9) not null,
Daniel Kim's avatar
Daniel Kim committed
72
73
  name varchar(64),
  birthName varchar(256),
kcchik's avatar
lmao    
kcchik committed
74
75
76
77
78
79
  height int,
  bio text,
  birthDetails varchar(256),
  dateOfBirth datetime
);
alter table People add primary key (personID);
Daniel Kim's avatar
Daniel Kim committed
80
81
replace into People (personID, name, birthName, height, bio, birthDetails, dateOfBirth)
select personID, name, birthName, height, bio, birthDetails, dateOfBirth from ImdbNames;
kcchik's avatar
lmao    
kcchik committed
82
83

create table DeadPeople (
kcchik's avatar
kcchik committed
84
  personID char(9) not null,
kcchik's avatar
lmao    
kcchik committed
85
86
87
88
89
90
91
92
93
  dateOfDeath datetime,
  placeOfDeath varchar(128),
  reasonOfDeath varchar(128)
);
alter table DeadPeople add primary key (personID);
alter table DeadPeople add foreign key (personID) references People(personID);
replace into DeadPeople (personID, dateOfDeath, placeOfDeath, reasonOfDeath) select personID, dateOfDeath, placeOfDeath, reasonOfDeath from ImdbNames
where dateOfDeath != null;

Daniel Kim's avatar
Daniel Kim committed
94
create table PeopleInMovies (
kcchik's avatar
kcchik committed
95
96
  movieID char(9) not null,
  personID char(9) not null,
kcchik's avatar
kcchik committed
97
  role varchar(32)
Daniel Kim's avatar
Daniel Kim committed
98
99
100
101
);
alter table PeopleInMovies add primary key (movieID, personID);
alter table PeopleInMovies add foreign key (movieID) references Movies(movieID);
alter table PeopleInMovies add foreign key (personID) references People(personID);
kcchik's avatar
kcchik committed
102
replace into PeopleInMovies (movieID, personID, role) select movieID, personID, category from ImdbTitlePrincipals;
Daniel Kim's avatar
Daniel Kim committed
103

kcchik's avatar
lmao    
kcchik committed
104
105
-- user stuff
create table Users (
kcchik's avatar
kcchik committed
106
  userID varchar(32) not null
kcchik's avatar
lmao    
kcchik committed
107
108
109
);
alter table Users add primary key (userID);

Daniel Kim's avatar
Daniel Kim committed
110
create table UsersFavouriteMovies (
kcchik's avatar
kcchik committed
111
112
  userID varchar(32) not null,
  movieID char(9) not null
Daniel Kim's avatar
Daniel Kim committed
113
114
115
116
117
);
alter table UsersFavouriteMovies add primary key (userID, movieID);
alter table UsersFavouriteMovies add foreign key (userID) references Users(userID);
alter table UsersFavouriteMovies add foreign key (movieID) references Movies(movieID);

kcchik's avatar
lmao    
kcchik committed
118
create table Reviews (
kcchik's avatar
kcchik committed
119
120
  movieID char(9) not null,
  userID varchar(32) not null,
kcchik's avatar
lmao    
kcchik committed
121
122
123
124
125
126
  comment text
);
alter table Reviews add primary key (movieID, userID);
alter table Reviews add foreign key (movieID) references Movies(movieID);
alter table Reviews add foreign key (userID) references Users(userID);

Daniel Kim's avatar
Daniel Kim committed
127
-- genre stuff
kcchik's avatar
kcchik committed
128
create table Genres (
kcchik's avatar
kcchik committed
129
  genre varchar(16) not null
kcchik's avatar
kcchik committed
130
131
);
alter table Genres add primary key (genre);
Daniel Kim's avatar
Daniel Kim committed
132

kcchik's avatar
kcchik committed
133
134
drop table if exists temp;
create temporary table temp (val varchar(32));
Daniel Kim's avatar
Daniel Kim committed
135
drop procedure if exists parseGenre;
kcchik's avatar
kcchik committed
136
137
138
139
140
141
142
143
144
145
146
delimiter ;;
create procedure parseGenre() begin
  declare n int default 0;
  declare i int default 0;
  select count(distinct genre) from ImdbMovies into n;
  insert into temp (val) select distinct genre from ImdbMovies;
  set i = 0;
  while i < n do
    set @s = concat(
      "replace into Genres (genre) values ('",
      replace(
kcchik's avatar
lmao    
kcchik committed
147
148
149
        (select * from temp limit i, 1),
        ", ",
        "'),('"
kcchik's avatar
kcchik committed
150
151
152
153
154
155
156
      ),
      "');"
    );
    prepare stmt from @s;
    execute stmt;
    set i = i + 1;
  end while;
Daniel Kim's avatar
Daniel Kim committed
157
158
end;
;;
kcchik's avatar
kcchik committed
159
delimiter ;
Daniel Kim's avatar
Daniel Kim committed
160
call parseGenre();
kcchik's avatar
kcchik committed
161
162
163
drop table if exists temp;

create table GenresInMovies (
kcchik's avatar
kcchik committed
164
165
  movieID char(9) not null,
  genre varchar(16) not null
kcchik's avatar
kcchik committed
166
167
168
169
170
);
alter table GenresInMovies add primary key (movieID, genre);
alter table GenresInMovies add foreign key (movieID) references Movies(movieID);
alter table GenresInMovies add foreign key (genre) references Genres(genre);

kcchik's avatar
lmao    
kcchik committed
171
drop procedure if exists loadGenre;
kcchik's avatar
kcchik committed
172
delimiter ;;
kcchik's avatar
lmao    
kcchik committed
173
create procedure loadGenre() begin
kcchik's avatar
kcchik committed
174
175
176
177
  declare n int default 0;
  declare i int default 0;
  select count(*) from ImdbMovies into n;
  set i = 0;
kcchik's avatar
lmao    
kcchik committed
178
  while i < 1000 do
kcchik's avatar
kcchik committed
179
180
181
182
    set @s = concat(
      "insert into GenresInMovies (movieID, genre) values ('",
      (select movieID from ImdbMovies limit i, 1),
      "','",
kcchik's avatar
lmao    
kcchik committed
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
      replace(
        (select genre from ImdbMovies limit i, 1),
        ", ",
        concat("'),('", (select movieID from ImdbMovies limit i, 1), "','")
      ),
      "');"
    );
    prepare stmt from @s;
    execute stmt;
    set i = i + 1;
  end while;
end;
;;
delimiter ;
call loadGenre();

-- country stuff
Daniel Kim's avatar
Daniel Kim committed
200
201
202
203
204
create table Countries (
  country varchar(128) not null
);
alter table Countries add primary key (country);
replace into Countries (country) select distinct country from ImdbMovies;
kcchik's avatar
lmao    
kcchik committed
205

Daniel Kim's avatar
Daniel Kim committed
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
-- drop table if exists temp;
-- create table temp (val varchar(128));
-- drop procedure if exists parseCountry;
-- delimiter ;;
-- create procedure parseCountry() begin
--   declare n int default 0;
--   declare i int default 0;
--   select count(distinct country) from ImdbMovies into n;
--   insert into temp (val) select distinct country from ImdbMovies;
--   set i = 0;
--   while i < n do
--     set @s = concat(
--       "replace into Countries (country) values ('",
--       replace(
--         replace(
--           replace(
--             (select val from temp limit i, 1),
--             "'",
--             ""
--           ),
--           ",",
--           "'),('"
--         ),
--         " ",
--         ""
--       ),
--       "');"
--     );
--     prepare stmt from @s;
--     execute stmt;
--     set i = i + 1;
--   end while;
-- end;
-- ;;
-- delimiter ;
-- call parseCountry();
-- drop table if exists temp;
-- alter table Countries add index countryIndex(country);
kcchik's avatar
lmao    
kcchik committed
244

Daniel Kim's avatar
Daniel Kim committed
245
246
247
248
249
250
251
252
create table CountriesInMovies (
  movieID char(9) not null,
  country varchar(128) not null
);
alter table CountriesInMovies add primary key (movieID, country);
alter table CountriesInMovies add foreign key (movieID) references Movies(movieID);
alter table CountriesInMovies add foreign key (country) references Countries(country);
replace into CountriesInMovies (movieID, country) select movieID, country from ImdbMovies;
kcchik's avatar
lmao    
kcchik committed
253

Daniel Kim's avatar
Daniel Kim committed
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
-- drop procedure if exists loadCountry;
-- delimiter ;;
-- create procedure loadCountry() begin
--   declare n int default 0;
--   declare i int default 0;
--   select count(*) from ImdbMovies into n;
--   set i = 0;
--   while i < n do
--     set @s = concat(
--       "replace into CountriesInMovies (movieID, country) values ('",
--       (select movieID from ImdbMovies limit i, 1),
--       "','",
--       replace(
--         replace(
--           replace(
--             (select country from ImdbMovies limit i, 1),
--             "'",
--             ""
--           ),
--           ",",
--           concat("'),('", (select movieID from ImdbMovies limit i, 1), "','")
--         ),
--         " ",
--         ""
--       ),
--       "');"
--     );
--     prepare stmt from @s;
--     execute stmt;
--     set i = i + 1;
--   end while;
-- end;
-- ;;
-- delimiter ;
-- call loadCountry();
kcchik's avatar
lmao    
kcchik committed
289
290

-- language stuff
Daniel Kim's avatar
Daniel Kim committed
291
292
293
294
295
create table Languages (
  language varchar(128) not null
);
alter table Languages add primary key (language);
replace into Languages (language) select distinct language from ImdbMovies;
kcchik's avatar
lmao    
kcchik committed
296

Daniel Kim's avatar
Daniel Kim committed
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
-- drop table if exists temp;
-- create table temp (val varchar(128));
-- drop procedure if exists parseLanguage;
-- delimiter ;;
-- create procedure parseLanguage() begin
--   declare n int default 0;
--   declare i int default 0;
--   select count(distinct language) from ImdbMovies into n;
--   insert into temp (val) select distinct language from ImdbMovies;
--   set i = 0;
--   while i < n do
--     set @s = concat(
--       "replace into Languages (language) values ('",
--       replace(
--         replace(
--           replace(
--             (select * from temp limit i, 1),
--             "'",
--             ""
--           ),
--           ", ",
--           "'),('"
--         ),
--         " ",
--         ""
--       ),
--       "');"
--     );
--     prepare stmt from @s;
--     execute stmt;
--     set i = i + 1;
--   end while;
-- end;
-- ;;
-- delimiter ;
-- call parseLanguage();
-- alter table Languages add index languageIndex(language);
kcchik's avatar
lmao    
kcchik committed
334

Daniel Kim's avatar
Daniel Kim committed
335
336
337
338
339
340
341
342
create table LanguagesInMovies (
  movieID char(9) not null,
  language varchar(128) not null
);
alter table LanguagesInMovies add primary key (movieID, language);
alter table LanguagesInMovies add foreign key (movieID) references Movies(movieID);
alter table LanguagesInMovies add foreign key (language) references Languages(language);
replace into LanguagesInMovies (movieID, language) select movieID, language from ImdbMovies;
Daniel Kim's avatar
Daniel Kim committed
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
-- drop procedure if exists loadLanguage;
-- delimiter ;;
-- create procedure loadLanguage() begin
--   declare n int default 0;
--   declare i int default 0;
--   select count(*) from ImdbMovies into n;
--   set i = 0;
--   while i < 5000 do
--     set @s = concat(
--       "replace into LanguagesInMovies (movieID, language) values ('",
--       (select movieID from ImdbMovies limit i, 1),
--       "','",
--       replace(
--         replace(
--           replace(
--             (select language from ImdbMovies limit i, 1),
--             "'",
--             ""
--           ),
--           ", ",
--           concat("'),('", (select movieID from ImdbMovies limit i, 1), "','")
--         ),
--         " ",
--         ""
--       ),
--       "');"
--     );
--     prepare stmt from @s;
--     execute stmt;
--     set i = i + 1;
--   end while;
-- end;
-- ;;
-- delimiter ;
-- call loadLanguage();
kcchik's avatar
lmao    
kcchik committed
378
379
380

-- production company stuff
create table ProductionCompany (
kcchik's avatar
kcchik committed
381
  productionCompany varchar(128) not null
kcchik's avatar
lmao    
kcchik committed
382
383
);
alter table ProductionCompany add primary key (productionCompany);
Daniel Kim's avatar
Daniel Kim committed
384
replace into ProductionCompany (productionCompany) select distinct productionCompany from ImdbMovies;
Daniel Kim's avatar
Daniel Kim committed
385
alter table ProductionCompany add index productionCompanyIndex(productionCompany);
kcchik's avatar
lmao    
kcchik committed
386
387

create table ProductionCompaniesInMovies (
kcchik's avatar
kcchik committed
388
389
  movieID char(9) not null,
  productionCompany varchar(128) not null
kcchik's avatar
lmao    
kcchik committed
390
391
392
393
394
);
alter table ProductionCompaniesInMovies add primary key (movieID, productionCompany);
alter table ProductionCompaniesInMovies add foreign key (movieID) references Movies(movieID);
alter table ProductionCompaniesInMovies add foreign key (productionCompany) references ProductionCompany(productionCompany);
replace into ProductionCompaniesInMovies (movieID, productionCompany) select movieID, productionCompany from ImdbMovies;
kcchik's avatar
kcchik committed
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409

-- max id
create table MaxIDs (
  entityName varchar(8),
  entityNum int
);
alter table MaxIDs add primary key (entityName);
replace into MaxIDs (entityName, entityNum) values (
  "movie",
  cast(regexp_substr((select movieID from Movies order by movieID desc limit 1), '[^tt]*$') as unsigned)
);
replace into MaxIDs (entityName, entityNum) values (
  "person",
  cast(regexp_substr((select personID from People order by personID desc limit 1), '[^nm]*$') as unsigned)
);