java-filmorate

Проект рейтинга фильмов

Изменения в 12 спринте

1 Функциональность «Отзывы». 4 SP

2 Функциональность «Поиск». 3SP

3 Функциональность «Общие фильмы». 1 SP

4 Функциональность «Рекомендации». 3 SP

5 Функциональность «Лента событий». 3 SP

6 Удаление фильмов и пользователей. 2 SP

7 Добавление режиссёров в фильмы. 4 SP

8 Вывод самых популярных фильмов по жанру и годам. 2 SP

Диаграмма БД

https://dbdiagram.io/embed/64da052f02bd1c4a5ebcdea6

Скрипт для БД PostgreeSQL

DROP TABLE IF EXISTS user_friends cascade ;
DROP TABLE IF EXISTS film_genres cascade ;
DROP TABLE IF EXISTS likes cascade ;
DROP TABLE IF EXISTS genres cascade ;
DROP TABLE IF EXISTS user_reviews cascade ;
DROP TABLE IF EXISTS film_directors cascade ;

DROP TABLE IF EXISTS reviews cascade ;
DROP TABLE IF EXISTS directors cascade ;
DROP TABLE IF EXISTS entities cascade ;
DROP TABLE IF EXISTS feeds cascade ;
DROP TABLE IF EXISTS films cascade ;
DROP TABLE IF EXISTS users cascade ;
DROP TABLE IF EXISTS mpas cascade ;

CREATE TABLE users (
  user_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email varchar(255) NOT NULL,
  login varchar(32)  NOT NULL,
  name varchar(64) NOT NULL,
  birthday date NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE user_friends (
  user_id integer NOT NULL,
  friend_id integer NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now()),
  PRIMARY KEY (user_id, friend_id)
);

CREATE TABLE films (
  film_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar(255) NOT NULL,
  description text,
  release_date date NOT NULL,
  duration integer NOT NULL,
  rating_id integer,
  rate double,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE genres (
  genre_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar(16) UNIQUE NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE film_genres (
  film_id integer NOT NULL,
  genre_id integer NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now()),
  PRIMARY KEY (film_id, genre_id)
);

CREATE TABLE mpas (
  rating_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar(8) UNIQUE NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE likes (
  user_id integer NOT NULL,
  film_id integer NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now()),
  PRIMARY KEY (user_id, film_id)
);

CREATE TABLE reviews (
  review_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id integer NOT NULL,
  film_id integer NOT NULL,
  is_positive integer NOT NULL DEFAULT 1,
  useful integer,
  content varchar(255),
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE user_reviews (
  user_id integer,
  review_id integer,
  mark integer NOT NULL DEFAULT 0,
  updated_at timestamp NOT NULL DEFAULT (now()),
  PRIMARY KEY (user_id, review_id)
);

CREATE TABLE directors (
  director_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar(255) UNIQUE NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE film_directors (
  director_id integer,
  film_id integer,
  updated_at timestamp NOT NULL DEFAULT (now()),
  PRIMARY KEY (director_id, film_id)
);

CREATE TABLE entities (
  entity_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar(255) UNIQUE NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE feeds (
  feed_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id integer NOT NULL,
  event_type ENUM ('LIKE', 'REVIEW', 'FRIEND') NOT NULL,
  operation ENUM ('REMOVE', 'ADD', 'UPDATE') NOT NULL,
  entity_id integer NOT NULL,
  updated_at timestamp NOT NULL DEFAULT (now())
);

ALTER TABLE film_directors ADD FOREIGN KEY (director_id) REFERENCES directors (director_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE film_directors ADD FOREIGN KEY (film_id) REFERENCES films (film_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE user_reviews ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE user_reviews ADD FOREIGN KEY (review_id) REFERENCES reviews (review_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE reviews ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE reviews ADD FOREIGN KEY (film_id) REFERENCES films (film_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE feeds ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE feeds ADD FOREIGN KEY (entity_id) REFERENCES entities (entity_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE user_friends ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE user_friends ADD FOREIGN KEY (friend_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE film_genres ADD FOREIGN KEY (film_id) REFERENCES films (film_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE film_genres ADD FOREIGN KEY (genre_id) REFERENCES genres (genre_id) ON DELETE CASCADE ON UPDATE NO ACTION;

--ALTER TABLE films ADD FOREIGN KEY (rating_id) REFERENCES mpas (rating_id) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE likes ADD FOREIGN KEY (film_id) REFERENCES films (film_id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE likes ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

Примеры скориптов

Скрипт друзей

SELECT f.login,
	f.name,
	f.email,
	f.birthday
FROM user_friend uf
LEFT JOIN "user" f ON uf.friend_id = f.user_id
LEFT JOIN "user" u ON uf.user_id = u.user_id
WHERE u.login = 'ivan'