mauviette/data/db/moviedb.sql

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "Genre";
CREATE TABLE IF NOT EXISTS "Genre" (
	"id"	INTEGER NOT NULL,
	"name"	VARCHAR(32),
	PRIMARY KEY("id")
);
DROP TABLE IF EXISTS "Movie";
CREATE TABLE IF NOT EXISTS "Movie" (
	"id"	VARCHAR NOT NULL,
	"title"	VARCHAR,
	"year"	DATE,
	"color"	VARCHAR,
	"language"	VARCHAR,
	"country"	VARCHAR,
	"content_rating"	VARCHAR,
	"duration"	INTEGER,
	"gross"	INTEGER,
	"budget"	INTEGER,
	"aspect_ratio"	FLOAT,
	"facebook_likes"	INTEGER,
	"imdb_score"	INTEGER,
	"num_user_for_reviews"	INTEGER,
	"plot_keywords"	VARCHAR,
	"facenumber_in_poster"	INTEGER,
	PRIMARY KEY("id")
);
DROP TABLE IF EXISTS "Artist";
CREATE TABLE IF NOT EXISTS "Artist" (
	"id"	INTEGER NOT NULL,
	"name"	VARCHAR,
	PRIMARY KEY("id" AUTOINCREMENT),
	UNIQUE("name")
);
DROP TABLE IF EXISTS "User";
CREATE TABLE IF NOT EXISTS "User" (
	"id"	INTEGER NOT NULL,
	"username"	VARCHAR,
	"email"	VARCHAR(120),
	"password_hash"	VARCHAR(128),
	UNIQUE("username"),
	PRIMARY KEY("id" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "movie_genres";
CREATE TABLE IF NOT EXISTS "movie_genres" (
	"movie_id"	VARCHAR NOT NULL,
	"genre_id"	INTEGER NOT NULL,
	PRIMARY KEY("movie_id","genre_id"),
	FOREIGN KEY("movie_id") REFERENCES "Movie"("id"),
	FOREIGN KEY("genre_id") REFERENCES "Genre"("id")
);
DROP TABLE IF EXISTS "appearances";
CREATE TABLE IF NOT EXISTS "appearances" (
	"id"	INTEGER NOT NULL,
	"role"	VARCHAR(32),
	"facebook_likes"	INTEGER,
	"movie_id"	INTEGER,
	"artist_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("movie_id") REFERENCES "Movie"("id"),
	FOREIGN KEY("artist_id") REFERENCES "Artist"("id")
);
DROP TABLE IF EXISTS "seen_movies";
CREATE TABLE IF NOT EXISTS "seen_movies" (
	"id"	INTEGER NOT NULL,
	"rating"	INTEGER,
	"movie_id"	INTEGER,
	"user_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("user_id") REFERENCES "User"("id"),
	FOREIGN KEY("movie_id") REFERENCES "Movie"("id")
);
DROP TABLE IF EXISTS "recommended_movies";
CREATE TABLE IF NOT EXISTS "recommended_movies" (
	"id"	INTEGER NOT NULL,
	"date"	DATE NOT NULL,
	"score"	INTEGER NOT NULL,
	"movie_id"	INTEGER,
	"user_id"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("movie_id") REFERENCES "Movie"("id"),
	FOREIGN KEY("user_id") REFERENCES "User"("id")
);
DROP INDEX IF EXISTS "ix_User_email";
CREATE UNIQUE INDEX IF NOT EXISTS "ix_User_email" ON "User" (
	"email"
);
COMMIT;