bekind/init.sql at master · markort147/bekind · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
\c bekind;

CREATE TABLE people
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE roles
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE sagas
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE studios
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE genres
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE countries
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

CREATE TABLE movies
(
    id    SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    rate  INT          NOT NULL,
    year  INT          NOT NULL,
    seen  INT          NOT NULL,
);

CREATE TABLE people_movies
(
    id        SERIAL PRIMARY KEY,
    person_id INT NOT NULL,
    movie_id  INT NOT NULL,
    role_id   INT NOT NULL,

    FOREIGN KEY (person_id) REFERENCES people (id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies (id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE,
);

CREATE TABLE sagas_movies
(
    id        SERIAL PRIMARY KEY,
    saga_id   INT NOT NULL,
    movie_id  INT NOT NULL,

    FOREIGN KEY (saga_id) REFERENCES sagas (id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies (id) ON DELETE CASCADE,
);

CREATE TABLE studios_movies
(
    id        SERIAL PRIMARY KEY,
    studio_id INT NOT NULL,
    movie_id  INT NOT NULL,

    FOREIGN KEY (studio_id) REFERENCES studios (id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies (id) ON DELETE CASCADE,
);

CREATE TABLE genres_movies
(
    id        SERIAL PRIMARY KEY,
    genre_id  INT NOT NULL,
    movie_id  INT NOT NULL,

    FOREIGN KEY (genre_id) REFERENCES genres (id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies (id) ON DELETE CASCADE,
);

CREATE TABLE countries_movies
(
    id        SERIAL PRIMARY KEY,
    country_id INT NOT NULL,
    movie_id   INT NOT NULL,

    FOREIGN KEY (country_id) REFERENCES countries (id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies (id) ON DELETE CASCADE,
);

INSERT INTO roles (name) VALUES
('director'),
('writer'),
('cinematographer'),
('composer'),
('editor'),
('producer');