loadData.sql

drop table if exists ImdbMovies;
create table ImdbMovies (
  movieID char(9),
  title varchar(256),
  originalTitle varchar(256),
  year int,
  releaseDate datetime,
  genre varchar(32),
  duration int,
  country varchar(128),
  language varchar(128),
  director varchar(64),
  writer varchar(64),
  productionCompany varchar(128),
  actors varchar(128),
  description text,
  score float,
  votes int,
  budget varchar(32),
  grossIncomeUSA varchar(32),
  grossIncomeWorldwide varchar(32),
  metascore float,
  userScore float,
  criticScore float
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb movies.csv' ignore into table ImdbMovies
  fields terminated by ','
  enclosed by '"'
  escaped by ''
  lines terminated by '\n'
  ignore 1 lines;
update ImdbMovies set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';
update ImdbMovies set budget = '$ 0' where budget = '';
update ImdbMovies set grossIncomeUSA = '$ 0' where grossIncomeUSA = '';
update ImdbMovies set grossIncomeWorldwide = '$ 0' where grossIncomeWorldwide = '';

drop table if exists ImdbNames;
create table ImdbNames (
  personID char(9),
  name varchar(64),
  birthName varchar(256),
  height int,
  bio text,
  birthDetails varchar(256),
  dateOfBirth datetime,
  placeOfBirth varchar(256),
  deathDetails varchar(256),
  dateOfDeath datetime,
  placeOfDeath varchar(128),
  reasonOfDeath varchar(128),
  spouses int,
  divorces int,
  spousesWithChildren int,
  children int
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb names.csv' ignore into table ImdbNames
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
update ImdbNames set dateOfBirth = null where cast(dateOfBirth as char(20)) = '0000-00-00 00:00:00';
update ImdbNames set dateOfDeath = null where cast(dateOfDeath as char(20)) = '0000-00-00 00:00:00';

-- drop table if exists ImdbRatings;
-- create table ImdbRatings (
--   movieID char(9),
--   weightedScore float,
--   votes int,
--   score float
-- );
-- load data infile '/var/lib/mysql-files/03-Movies/IMDb ratings.csv' ignore into table ImdbRatings
--   fields terminated by ','
--   enclosed by '"'
--   lines terminated by '\n'
--   ignore 1 lines;

drop table if exists ImdbTitlePrincipals;
create table ImdbTitlePrincipals (
  movieID char(9),
  ordering int,
  personID char(9),
  category varchar(32),
  job varchar(256),
  characters varchar(512)
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb title_principals.csv' ignore into table ImdbTitlePrincipals
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;

drop table if exists MojoBudgetData;
create table MojoBudgetData (
  movieID char(9),
  title varchar(256),
  year int,
  director varchar(64),
  writer varchar(64),
  producer varchar(64),
  composer varchar(64),
  cinematography varchar(64),
  actor1 varchar(64),
  actor2 varchar(64),
  actor3 varchar(64),
  actor4 varchar(64),
  budget bigint,
  domestic bigint,
  international bigint,
  worldwide bigint,
  mpaa varchar(16),
  runtime varchar(16),
  genre1 varchar(16),
  genre2 varchar(16),
  genre3 varchar(16),
  genre4 varchar(16),
  link varchar(64)
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Mojo_budget_data.csv' ignore into table MojoBudgetData
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;

drop table if exists MojoBudgetUpdate;
create table MojoBudgetUpdate (
  movieID char(9),
  title varchar(256),
  year int,
  trivia text,
  mpaa varchar(16),
  releaseDate datetime,
  runtime varchar(16),
  distributor varchar(64),
  director varchar(64),
  writer varchar(64),
  producer varchar(64),
  composer varchar(64),
  cinematography varchar(64),
  actor1 varchar(64),
  actor2 varchar(64),
  actor3 varchar(64),
  actor4 varchar(64),
  budget bigint,
  domestic bigint,
  international bigint,
  worldwide bigint,
  genre1 varchar(16),
  genre2 varchar(16),
  genre3 varchar(16),
  genre4 varchar(16),
  html varchar(64)
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Mojo_budget_update.csv' ignore into table MojoBudgetUpdate
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
update MojoBudgetUpdate set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';

drop table if exists MoviesMetadata;
create table MoviesMetadata (
  adult bool,
  belongsToCollection text,
  budget int,
  genres text,
  homepage varchar(256),
  id int,
  movieID char(9),
  language varchar(8),
  originalTitle varchar(256),
  overview text,
  popularity float,
  posterPath varchar(64),
  productionCompanies text,
  productionCountries text,
  releaseDate datetime,
  revenue bigint,
  runtime int,
  spokenLanguages text,
  status varchar(16),
  tagline text,
  title varchar(256),
  video bool,
  score float,
  votes int
);
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/movies_metadata.csv' ignore into table MoviesMetadata
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
update MoviesMetadata set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';

-- drop table if exists MoviesCredits;
-- create table MoviesCredits (
--   cast text,
--   crew text,
--   id int
-- );
-- load data infile '/var/lib/mysql-files/03-Movies/credits.csv' ignore into table MoviesCredits
--   fields terminated by ','
--   enclosed by '"'
--   lines terminated by '\n'
--   ignore 1 lines;