[980eeda] | 1 | /*
|
---|
| 2 | * funkcija koja ke bide check_constraint za tip na person vo soodvetnite relacii
|
---|
| 3 | */
|
---|
| 4 | create function check_constraint_person(integer) returns char
|
---|
| 5 | as $$ select type from persons where person_id = $1 $$
|
---|
| 6 | language sql;
|
---|
| 7 |
|
---|
| 8 |
|
---|
| 9 | -- dodavanje tabeli
|
---|
| 10 | create table users(
|
---|
| 11 | user_id serial primary key,
|
---|
| 12 | username varchar(50) not null unique,
|
---|
| 13 | name varchar(50) not null,
|
---|
| 14 | surname varchar(50) not null,
|
---|
| 15 | email varchar(100) not null unique,
|
---|
| 16 | password varchar(30) not null,
|
---|
| 17 | constraint ck_password check(length(password) >= 9)
|
---|
| 18 | );
|
---|
| 19 |
|
---|
| 20 | create table persons(
|
---|
| 21 | person_id serial primary key,
|
---|
| 22 | name varchar(100) not null,
|
---|
| 23 | surname varchar(100) not null,
|
---|
| 24 | type char(1) not null,
|
---|
| 25 | date_of_birth date not null,
|
---|
| 26 | image_url varchar(300) not null,
|
---|
| 27 | description varchar(300) not null,
|
---|
| 28 | constraint ck_type check (type ='A' or type='D')
|
---|
| 29 | );
|
---|
| 30 |
|
---|
| 31 | create table movies(
|
---|
| 32 | movie_id serial primary key,
|
---|
| 33 | title varchar(150) not null unique,
|
---|
| 34 | description varchar(1000) not null,
|
---|
| 35 | image_url varchar(300) not null,
|
---|
| 36 | airing_date date not null,
|
---|
| 37 | imdb_rating float,
|
---|
| 38 | director_id integer,
|
---|
| 39 | constraint fk_movie_director foreign key (director_id) references persons(person_id)
|
---|
| 40 | on delete cascade on update cascade,
|
---|
| 41 | constraint ck_person_is_director check( check_constraint_person(director_id) = 'D')
|
---|
| 42 | /*
|
---|
| 43 | * dodaden uslov za foreing key
|
---|
| 44 | */
|
---|
| 45 | );
|
---|
| 46 |
|
---|
| 47 | create table genres(
|
---|
| 48 | genre_id serial primary key,
|
---|
| 49 | genre_type varchar(100) not null unique
|
---|
| 50 | );
|
---|
| 51 |
|
---|
| 52 | create table movie_rates(
|
---|
| 53 | movie_id integer,
|
---|
| 54 | user_id integer,
|
---|
| 55 | reason varchar(300) not null,
|
---|
| 56 | stars_rated integer not null,
|
---|
| 57 | constraint pk_movie_rates primary key (movie_id,user_id),
|
---|
| 58 | constraint fk_rated_movie foreign key (movie_id) references movies(movie_id)
|
---|
| 59 | on delete cascade on update cascade,
|
---|
| 60 | constraint fk_user_rate foreign key (user_id) references users(user_id)
|
---|
| 61 | on delete cascade on update cascade,
|
---|
| 62 | constraint ck_movie_stars check (stars_rated >= 1 and stars_rated <= 10)
|
---|
| 63 | );
|
---|
| 64 |
|
---|
| 65 | create table movie_likes(
|
---|
| 66 | movie_id integer,
|
---|
| 67 | user_id integer,
|
---|
| 68 | constraint pk_movie_likes primary key (movie_id,user_id),
|
---|
| 69 | constraint fk_liked_movie foreign key (movie_id) references movies(movie_id)
|
---|
| 70 | on delete cascade on update cascade,
|
---|
| 71 | constraint fk_user_like foreign key (user_id) references users(user_id)
|
---|
| 72 | on delete cascade on update cascade
|
---|
| 73 | );
|
---|
| 74 |
|
---|
| 75 | create table movie_genres(
|
---|
| 76 | movie_id integer,
|
---|
| 77 | genre_id integer,
|
---|
| 78 | constraint pk_movie_genres primary key (movie_id,genre_id),
|
---|
| 79 | constraint fk_movie_genre foreign key (movie_id) references movies(movie_id)
|
---|
| 80 | on delete cascade on update cascade,
|
---|
| 81 | constraint fk_genre_type_movie foreign key (genre_id) references genres(genre_id)
|
---|
| 82 | on delete cascade on update cascade
|
---|
| 83 | );
|
---|
| 84 |
|
---|
| 85 | create table user_genres(
|
---|
| 86 | user_id integer,
|
---|
| 87 | genre_id integer,
|
---|
| 88 | constraint pk_user_genres primary key (user_id,genre_id),
|
---|
| 89 | constraint fk_user_genre foreign key (user_id) references users(user_id)
|
---|
| 90 | on delete cascade on update cascade,
|
---|
| 91 | constraint fk_genre_like_user foreign key (genre_id) references genres(genre_id)
|
---|
| 92 | on delete cascade on update cascade
|
---|
| 93 | );
|
---|
| 94 |
|
---|
| 95 | create table movie_actors(
|
---|
| 96 | movie_id integer,
|
---|
| 97 | actor_id integer,
|
---|
| 98 | constraint pk_movie_actors primary key (movie_id,actor_id),
|
---|
| 99 | constraint fk_actors_in_movie foreign key (movie_id) references movies(movie_id)
|
---|
| 100 | on delete cascade on update cascade,
|
---|
| 101 | constraint fk_actor_acts_movie foreign key (actor_id) references persons(person_id)
|
---|
| 102 | on delete cascade on update cascade,
|
---|
| 103 | constraint ck_person_is_actor check(check_constraint_person(actor_id) = 'A')
|
---|
| 104 | /*
|
---|
| 105 | * dodaeno
|
---|
| 106 | * potrebno e i ovde ogranicuvanje na actor_id, negovoto svojstvo type = 'A'
|
---|
| 107 | */
|
---|
| 108 | );
|
---|
| 109 |
|
---|
| 110 | create table person_rates(
|
---|
| 111 | person_id integer,
|
---|
| 112 | user_id integer,
|
---|
| 113 | reason varchar(300) not null,
|
---|
| 114 | stars_rated integer not null,
|
---|
| 115 | constraint pk_person_rates primary key (person_id,user_id),
|
---|
| 116 | constraint fk_rated_person foreign key (person_id) references persons(person_id)
|
---|
| 117 | on delete cascade on update cascade,
|
---|
| 118 | constraint fk_user_rate_person foreign key (user_id) references users(user_id)
|
---|
| 119 | on delete cascade on update cascade,
|
---|
| 120 | constraint ck_person_stars check (stars_rated >= 1 and stars_rated <= 10)
|
---|
| 121 | );
|
---|
| 122 |
|
---|
| 123 | create table discussions(
|
---|
| 124 | discussion_id serial primary key,
|
---|
| 125 | type char(1) not null,
|
---|
| 126 | text varchar(1000) not null,
|
---|
| 127 | title varchar(250) not null,
|
---|
| 128 | date date not null,
|
---|
| 129 | user_id integer not null,
|
---|
| 130 | movie_id integer,
|
---|
| 131 | person_id integer,
|
---|
| 132 | constraint fk_user_created foreign key (user_id) references users(user_id)
|
---|
| 133 | on delete cascade on update cascade,
|
---|
| 134 | constraint ck_type_discussion check( (type = 'M' and movie_id notnull and person_id isnull)
|
---|
| 135 | or (type='P' and person_id notnull and movie_id isnull)),
|
---|
| 136 | constraint fk_discussion_movie foreign key (movie_id) references movies(movie_id)
|
---|
| 137 | on delete cascade on update cascade,
|
---|
| 138 | constraint fk_discussion_person foreign key (person_id) references persons(person_id)
|
---|
| 139 | on delete cascade on update cascade
|
---|
| 140 | );
|
---|
| 141 |
|
---|
| 142 |
|
---|
| 143 |
|
---|
| 144 | create table replies(
|
---|
| 145 | discussion_id integer,
|
---|
| 146 | reply_id serial,
|
---|
| 147 | text varchar(1000) not null,
|
---|
| 148 | date date not null,
|
---|
| 149 | user_id integer not null,
|
---|
| 150 | constraint pk_replies primary key(discussion_id,reply_id),
|
---|
| 151 | constraint fk_user_create_reply foreign key (user_id) references users(user_id)
|
---|
| 152 | on delete cascade on update cascade,
|
---|
| 153 | constraint fk_reply_discussion foreign key (discussion_id) references discussions(discussion_id)
|
---|
| 154 | on delete cascade on update cascade
|
---|
| 155 |
|
---|
| 156 |
|
---|
| 157 | );
|
---|
| 158 |
|
---|
| 159 |
|
---|
| 160 | create table reply_likes(
|
---|
| 161 | reply_id integer,
|
---|
| 162 | discussion_id integer,
|
---|
| 163 | user_id integer,
|
---|
| 164 | constraint pk_reply_likes primary key (reply_id, discussion_id,user_id),
|
---|
| 165 | constraint fk_user_like foreign key (user_id) references users(user_id)
|
---|
| 166 | on delete cascade on update cascade,
|
---|
| 167 | constraint fk_reply_like foreign key (reply_id, discussion_id) references replies(reply_id, discussion_id)
|
---|
| 168 | on delete cascade on update cascade
|
---|
| 169 | );
|
---|
| 170 |
|
---|
| 171 |
|
---|
| 172 |
|
---|
| 173 | create table discussion_likes(
|
---|
| 174 | discussion_id integer,
|
---|
| 175 | user_id integer,
|
---|
| 176 | constraint pk_discussion_likes primary key (discussion_id,user_id),
|
---|
| 177 | constraint fk_liked_discussion foreign key (discussion_id) references discussions(discussion_id)
|
---|
| 178 | on delete cascade on update cascade,
|
---|
| 179 | constraint fk_user_like foreign key (user_id) references users(user_id)
|
---|
| 180 | on delete cascade on update cascade
|
---|
| 181 | );
|
---|
| 182 |
|
---|
| 183 |
|
---|
| 184 |
|
---|
| 185 |
|
---|
| 186 | -- brisenje tabeli
|
---|
| 187 | drop table if exists reply_likes;
|
---|
| 188 | drop table if exists discussion_likes;
|
---|
| 189 | drop table if exists replies;
|
---|
| 190 | drop table if exists discussions;
|
---|
| 191 | drop table if exists person_rates;
|
---|
| 192 | drop table if exists movie_actors;
|
---|
| 193 | drop table if exists user_genres;
|
---|
| 194 | drop table if exists movie_genres;
|
---|
| 195 | drop table if exists movie_likes;
|
---|
| 196 | drop table if exists movie_rates;
|
---|
| 197 | drop table if exists genres;
|
---|
| 198 | drop table if exists movies;
|
---|
| 199 | drop table if exists persons;
|
---|
| 200 | drop table if exists users;
|
---|
| 201 |
|
---|
| 202 |
|
---|
| 203 |
|
---|
| 204 |
|
---|
| 205 |
|
---|
| 206 |
|
---|
| 207 |
|
---|
| 208 |
|
---|
| 209 |
|
---|
| 210 |
|
---|