-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy pathschema.sql
More file actions
356 lines (320 loc) · 11.5 KB
/
Copy pathschema.sql
File metadata and controls
356 lines (320 loc) · 11.5 KB
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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
--
-- Functions
--
-- Trigger to update column 'updated_at' every time some column is updated.
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language plpgsql;
-- Trigger to update column 'updated_at' every time some column from 'translations' table is updated.
CREATE OR REPLACE FUNCTION handle_child_update()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'UPDATE ' || TG_ARGV[0]::text || ' SET updated_at = now() WHERE uuid = $1.' || TG_ARGV[1]::text
USING NEW;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--
-- Tables
--
-- Users
CREATE TABLE IF NOT EXISTS users (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
username VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
role VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Overviews
CREATE TABLE IF NOT EXISTS overviews (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
thumbnail TEXT,
languages VARCHAR(5) ARRAY NOT NULL,
creators VARCHAR(30) ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Overviews translations
CREATE TABLE IF NOT EXISTS overviews_translations (
id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
overview_uuid UUID NOT NULL,
language VARCHAR(5) NOT NULL DEFAULT 'en-US',
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
CONSTRAINT fk_overview_uuid
FOREIGN KEY(overview_uuid)
REFERENCES overviews(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- Characters
CREATE TABLE IF NOT EXISTS characters (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
nicknames VARCHAR ARRAY,
birth_date TIMESTAMP,
death_date TIMESTAMP,
gender SMALLINT NOT NULL DEFAULT 0,
thumbnail TEXT,
actor VARCHAR(100) NOT NULL,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Actors
CREATE TABLE IF NOT EXISTS actors (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
nicknames VARCHAR ARRAY,
nationality VARCHAR(50),
birth_date TIMESTAMP,
death_date TIMESTAMP,
gender SMALLINT NOT NULL DEFAULT 0,
seasons VARCHAR ARRAY,
awards VARCHAR ARRAY,
character VARCHAR(255) NOT NULL,
thumbnail TEXT,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Socials
CREATE TABLE IF NOT EXISTS actors_socials (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
social VARCHAR(50) NOT NULL,
handle VARCHAR(50) NOT NULL,
url TEXT NOT NULL,
actor_uuid UUID NOT NULL,
CONSTRAINT fk_actor_uuid
FOREIGN KEY(actor_uuid)
REFERENCES actors(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- Episodes
CREATE TABLE IF NOT EXISTS episodes (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
duration INTEGER NOT NULL,
episode_num SMALLINT NOT NULL,
next_episode VARCHAR(255),
prev_episode VARCHAR(255),
season VARCHAR(255) NOT NULL,
thumbnail TEXT,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Episodes translations
CREATE TABLE IF NOT EXISTS episodes_translations (
id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
episode_uuid UUID NOT NULL,
language VARCHAR(5) NOT NULL DEFAULT 'en-US',
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
CONSTRAINT fk_episode_uuid
FOREIGN KEY(episode_uuid)
REFERENCES episodes(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- Seasons
CREATE TABLE IF NOT EXISTS seasons (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
duration_total INTEGER NOT NULL,
season_num SMALLINT NOT NULL,
release_date TIMESTAMP NOT NULL,
next_season VARCHAR(255),
prev_season VARCHAR(255),
episodes VARCHAR ARRAY,
soundtracks VARCHAR(255) ARRAY,
budget INTEGER,
thumbnail TEXT,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Seasons translations
CREATE TABLE IF NOT EXISTS seasons_translations (
id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
season_uuid UUID NOT NULL,
language VARCHAR(5) NOT NULL DEFAULT 'en-US',
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
genres VARCHAR(50) ARRAY,
trailers VARCHAR ARRAY,
CONSTRAINT fk_season_uuid
FOREIGN KEY(season_uuid)
REFERENCES seasons(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- Locations
CREATE TABLE IF NOT EXISTS locations (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
thumbnail TEXT,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Locations translations
CREATE TABLE IF NOT EXISTS locations_translations (
id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
location_uuid UUID NOT NULL,
language VARCHAR(5) NOT NULL DEFAULT 'en-US',
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
CONSTRAINT fk_location_uuid
FOREIGN KEY(location_uuid)
REFERENCES locations(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- Soundtracks
CREATE TABLE IF NOT EXISTS soundtracks (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
name VARCHAR(255) NOT NULL,
artist VARCHAR(255) NOT NULL,
album VARCHAR(255),
urls TEXT ARRAY NOT NULL,
duration INTEGER NOT NULL,
release_date TIMESTAMP NOT NULL,
thumbnail TEXT,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Games
CREATE TABLE IF NOT EXISTS games (
id INTEGER GENERATED ALWAYS AS IDENTITY,
uuid UUID PRIMARY KEY UNIQUE NOT NULL,
href VARCHAR(100) NOT NULL,
release_date TIMESTAMP NOT NULL,
website TEXT NOT NULL,
stores TEXT ARRAY,
playtime INTEGER,
age_rating VARCHAR(20),
modes VARCHAR(20) ARRAY,
publishers VARCHAR(50) ARRAY,
developers VARCHAR(50) ARRAY,
platforms VARCHAR(50) ARRAY,
tags VARCHAR(50) ARRAY,
thumbnail TEXT,
images TEXT ARRAY,
sources TEXT ARRAY,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()
);
-- Games translations
CREATE TABLE IF NOT EXISTS games_translations (
id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
game_uuid UUID NOT NULL,
language VARCHAR(5) NOT NULL DEFAULT 'en-US',
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
genres VARCHAR(50) ARRAY,
trailer TEXT NOT NULL,
CONSTRAINT fk_game_uuid
FOREIGN KEY(game_uuid)
REFERENCES games(uuid)
ON UPDATE CASCADE
ON DELETE CASCADE
);
--
-- Triggers
--
-- # Set triggers to update column 'updated_at' every time some column is updated.
-- Overviews
CREATE TRIGGER handle_overviews_updated_at_column BEFORE
UPDATE ON overviews
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Users
CREATE TRIGGER handle_users_updated_at_column BEFORE
UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Characters
CREATE TRIGGER handle_characters_updated_at_column BEFORE
UPDATE ON characters
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Actors
CREATE TRIGGER handle_actors_updated_at_column BEFORE
UPDATE ON actors
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Episodes
CREATE TRIGGER handle_episodes_updated_at_column BEFORE
UPDATE ON episodes
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Seasons
CREATE TRIGGER handle_seasons_updated_at_column BEFORE
UPDATE ON seasons
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Locations
CREATE TRIGGER handle_locations_updated_at_column BEFORE
UPDATE ON locations
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Soundtracks
CREATE TRIGGER handle_soundtracks_updated_at_column BEFORE
UPDATE ON soundtracks
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- Games
CREATE TRIGGER handle_games_updated_at_column BEFORE
UPDATE ON games
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
-- # Set triggers to update column 'updated_at' every time some column from '[*].translations' is updated.
-- Overviews
CREATE TRIGGER handle_overviews_child_update AFTER UPDATE ON overviews_translations
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('overviews', 'overview_uuid');
-- Actors (Socials)
CREATE TRIGGER handle_actors_child_update AFTER UPDATE ON actors_socials
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('actors', 'actor_uuid');
-- Episodes
CREATE TRIGGER handle_episodes_child_update AFTER UPDATE ON episodes_translations
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('episodes', 'episode_uuid');
-- Seasons
CREATE TRIGGER handle_seasons_child_update AFTER UPDATE ON seasons_translations
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('seasons', 'season_uuid');
-- Locations
CREATE TRIGGER handle_locations_child_update AFTER UPDATE ON locations_translations
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('locations', 'location_uuid');
-- Games
CREATE TRIGGER handle_games_child_update AFTER UPDATE ON games_translations
FOR EACH ROW EXECUTE PROCEDURE handle_child_update ('games', 'game_uuid');