MyIMDB.sqlite

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

DROP TABLE `users` ;
DROP TABLE `comments`; 
DROP TABLE `actors`; 
DROP TABLE `movies`; 
DROP TABLE `actor_movies`; 
DROP TABLE `genres`; 
DROP TABLE "movie_genres"; 
DROP TABLE `user_roles`; 
DROP TABLE `user_role_types`; 

-- test
CREATE TABLE 'users' ( 
	-- user_id INTEGER PRIMARY KEY, 
	id INTEGER PRIMARY KEY,
	user_name TEXT UNIQUE NOT NULL, 
	first_name TEXT, 
	last_name TEXT, 
	password_enc TEXT, 
	email TEXT UNIQUE NOT NULL,
	email_validated BOOLEAN NOT NULL DEFAULT 0
        --CHECK (email_validated in (0, 1)) DEFAULT 0
);

INSERT INTO 'users' (user_name, email) VALUES ('demo', 'demo@xyzmail.com'); 


CREATE TABLE `comments` ( comment_id INTEGER PRIMARY KEY, comment TEXT );
CREATE TABLE `actors` ( actor_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, date_of_birth TEXT );

CREATE TABLE 'movies' ( 
  id          INTEGER PRIMARY KEY, 
  title       TEXT, 
  launch_date TEXT, 
  duration    INTEGER, 
  avg_rating  INTEGER 
);

CREATE TABLE `actor_movies` ( actor_id INTEGER REFERENCES actors(actor_id), movie_id INTEGER REFERENCES movies(movie_id) );
CREATE TABLE `genres` ( genre_id INTEGER PRIMARY KEY, genre TEXT );
CREATE TABLE "movie_genres" ( movie_id INTEGER REFERENCES movies(movie_id), genre_id INTEGER REFERENCES genres(genre_id));
CREATE TABLE `user_roles` ( user_id INTEGER REFERENCES user(user_id), role_id INTEGER REFERENCES roles(role_id) );
CREATE TABLE `user_role_types` (role_id INTEGER PRIMARY KEY, type TEST);



DROP TABLE IF EXISTS 'users_movies';

CREATE TABLE "users_movies" (
  'user_id'   INTEGER NOT NULL ,
  'movie_id'  INTEGER NOT NULL ,
  'rating'    INTEGER CHECK (rating  in (0, 1, 2, 3, 4, 5)), -- null is not rated
  'flagged'   BOOLEAN NOT NULL CHECK (flagged in (0, 1)) DEFAULT 0,   --for later viewing 
  PRIMARY KEY ('user_id','movie_id'),
  FOREIGN KEY(movie_id) REFERENCES movies(id),
  FOREIGN KEY(user_id)  REFERENCES users(id)
);  
 


 INSERT INTO 'users_movies' (user_id, movie_id, rating, flagged) VALUES 
   (1, 1, 0, 0),
   (1, 2, 2, 0), 
   (1, 3, 4, 1), 
   (1, 6, 5, 0); 

INSERT INTO `movies` (title, launch_date, duration, avg_rating) VALUES 
  ('Fight Club','1999-10-15',139,3),
  ('Seven','1995-09-22',127,3),
  ('Ocean Eleven',NULL,NULL,4),
  ('99',NULL,NULL,NULL),
  ('Star Wars', '1977-05-25',NULL,NULL),
  ('The Empire Strikes Back','1980-05-21',NULL,NULL),
  ('Return of the Jedi','1983-05-25',NULL,NULL);
COMMIT;


--

select * from users;

select * from movies;

select * from users_movies;