DatabaseCreation: napredni.ddl

File napredni.ddl, 9.1 KB (added by 231159, 12 hours ago)
Line 
1CREATE TABLE article_source
2(
3 article_id int4 NOT NULL,
4 source_id int4 NOT NULL
5);
6
7CREATE TABLE source
8(
9 id SERIAL NOT NULL,
10 created_by int4 NOT NULL,
11 title char(255),
12 url char(255),
13 source_type char(255),
14 published_date date,
15 PRIMARY KEY (id)
16);
17
18CREATE TABLE media_file
19(
20 id SERIAL NOT NULL,
21 file_url char(255),
22 file_name char(255),
23 media_type char(255),
24 mime_type char(255),
25 file_size_bytes int4,
26 uploaded_by int4 NOT NULL,
27 created_at date,
28 PRIMARY KEY (id)
29);
30
31CREATE TABLE article_media
32(
33 article_id int4 NOT NULL,
34 media_id int4 NOT NULL,
35 position int4,
36 caption char(255)
37);
38
39CREATE TABLE fact_checks
40(
41 id SERIAL NOT NULL,
42 article_id int4 NOT NULL,
43 checker_id int4 NOT NULL,
44 verdict char(255),
45 status char(255),
46 notes char(255),
47 is_active int4,
48 reviewed_at date,
49 created_at date,
50 PRIMARY KEY (id)
51);
52
53CREATE TABLE article
54(
55 id SERIAL NOT NULL,
56 title char(255) NOT NULL,
57 slug char(255) NOT NULL UNIQUE,
58 summary char(255) NOT NULL,
59 content char(16383) NOT NULL,
60 author_id int4 NOT NULL,
61 category_id int4 NOT NULL,
62 country char(255) NOT NULL,
63 prev_id int4,
64 status char(255) NOT NULL,
65 language char(255) NOT NULL,
66 deleted_at date,
67 created_at date,
68 updated_at date,
69 published_at date,
70 PRIMARY KEY (id)
71);
72
73CREATE TABLE category
74(
75 id SERIAL NOT NULL,
76 name char(255),
77 slug char(255),
78 description char(255),
79 parent_category_id int4,
80 PRIMARY KEY (id)
81);
82
83CREATE TABLE article_tags
84(
85 article_id int4 NOT NULL,
86 tag_id int4 NOT NULL
87);
88
89CREATE TABLE "tag"
90(
91 id SERIAL NOT NULL,
92 name char(255) UNIQUE,
93 slug char(255) NOT NULL,
94 PRIMARY KEY (id)
95);
96
97CREATE TABLE "user"
98(
99 id SERIAL NOT NULL,
100 username char(255),
101 email char(255),
102 password_hash char(255),
103 first_name char(255),
104 last_name char(255),
105 bio text,
106 profile_image_url char(255),
107 status char(255),
108 email_verified_at date,
109 last_login_at date,
110 deleted_at date,
111 created_at date,
112 updated_at date,
113 PRIMARY KEY (id)
114);
115
116CREATE TABLE user_prefrence
117(
118 user_id int4 NOT NULL,
119 notify_new_articles int4,
120 notify_comment_replies int4,
121 notify_fact_check_results int4,
122 preferred_language char(255),
123 preferred_categories char(255),
124 updated_at date
125);
126
127CREATE TABLE user_roles
128(
129 user_id int4 NOT NULL,
130 role_id int4 NOT NULL,
131 assigned_at date,
132 assigned_by int4
133);
134
135CREATE TABLE role
136(
137 id SERIAL NOT NULL,
138 name char(255),
139 description char(255),
140 PRIMARY KEY (id)
141);
142
143CREATE TABLE journalist_profile
144(
145 id SERIAL NOT NULL,
146 user_id int4 NOT NULL UNIQUE,
147 agency_id int4 NOT NULL,
148 verification_status char(255),
149 specialization char(255),
150 years_experience int4,
151 created_at date,
152 PRIMARY KEY (id)
153);
154
155CREATE TABLE agency
156(
157 id SERIAL NOT NULL,
158 name char(255),
159 country char(255),
160 website char(255),
161 description char(255),
162 created_at date,
163 PRIMARY KEY (id)
164);
165
166CREATE TABLE comment
167(
168 id SERIAL NOT NULL,
169 article_id int4,
170 user_id int4 NOT NULL,
171 parent_comment_id int4,
172 content char(255) NOT NULL,
173 created_at date,
174 updated_at date,
175 deleted_at date,
176 PRIMARY KEY (id)
177);
178
179CREATE TABLE comment_rating
180(
181 id SERIAL NOT NULL,
182 comment_id int4 NOT NULL,
183 user_id int4 NOT NULL,
184 vote int4,
185 created_at date,
186 PRIMARY KEY (id)
187);
188
189CREATE TABLE article_rating
190(
191 id SERIAL NOT NULL,
192 article_id int4 NOT NULL,
193 user_id int4 NOT NULL,
194 rating float4,
195 created_at date,
196 PRIMARY KEY (id)
197);
198
199CREATE TABLE journalist_followers
200(
201 journalist_id int4 NOT NULL,
202 follower_id int4 NOT NULL,
203 created_at date
204);
205
206CREATE TABLE audit_log
207(
208 id SERIAL NOT NULL,
209 actor_id int4 NOT NULL,
210 action char(255),
211 details char(255),
212 ip_hash char(255),
213 created_at date,
214 PRIMARY KEY (id)
215);
216
217CREATE TABLE notification
218(
219 id SERIAL NOT NULL,
220 user_id int4 NOT NULL,
221 type char(255),
222 message char(255),
223 is_read int4,
224 created_at date,
225 PRIMARY KEY (id)
226);
227
228CREATE TABLE article_views
229(
230 id SERIAL NOT NULL,
231 article_id int4 NOT NULL,
232 user_id int4 NOT NULL,
233 session_id int4,
234 ip_hash char(255),
235 country char(255),
236 device_type char(255),
237 referrer char(255),
238 viewed_at date,
239 PRIMARY KEY (id)
240);
241
242CREATE TABLE role_permission
243(
244 role_id int4 NOT NULL,
245 permission_id int4 NOT NULL
246);
247
248CREATE TABLE permissions
249(
250 id SERIAL NOT NULL,
251 "desc" char(255),
252 name char(255),
253 "group" char(255),
254 PRIMARY KEY (id)
255);
256
257ALTER TABLE article_source
258 ADD CONSTRAINT FKarticle_so294493 FOREIGN KEY (source_id) REFERENCES source (id);
259
260ALTER TABLE category
261 ADD CONSTRAINT FKcategory465764 FOREIGN KEY (parent_category_id) REFERENCES category (id);
262
263ALTER TABLE article
264 ADD CONSTRAINT FKarticle918178 FOREIGN KEY (category_id) REFERENCES category (id);
265
266ALTER TABLE source
267 ADD CONSTRAINT FKsource777451 FOREIGN KEY (created_by) REFERENCES "user" (id);
268
269ALTER TABLE media_file
270 ADD CONSTRAINT FKmedia_file869966 FOREIGN KEY (uploaded_by) REFERENCES "user" (id);
271
272ALTER TABLE article
273 ADD CONSTRAINT FKarticle826005 FOREIGN KEY (author_id) REFERENCES "user" (id);
274
275ALTER TABLE user_prefrence
276 ADD CONSTRAINT FKuser_prefr169160 FOREIGN KEY (user_id) REFERENCES "user" (id);
277
278ALTER TABLE fact_checks
279 ADD CONSTRAINT FKfact_check483552 FOREIGN KEY (checker_id) REFERENCES "user" (id);
280
281ALTER TABLE fact_checks
282 ADD CONSTRAINT FKfact_check308080 FOREIGN KEY (article_id) REFERENCES article (id);
283
284ALTER TABLE user_roles
285 ADD CONSTRAINT FKuser_roles830070 FOREIGN KEY (role_id) REFERENCES role (id);
286
287ALTER TABLE user_roles
288 ADD CONSTRAINT FKuser_roles215996 FOREIGN KEY (user_id) REFERENCES "user" (id);
289
290ALTER TABLE journalist_profile
291 ADD CONSTRAINT FKjournalist407222 FOREIGN KEY (user_id) REFERENCES "user" (id);
292
293ALTER TABLE journalist_profile
294 ADD CONSTRAINT FKjournalist264095 FOREIGN KEY (agency_id) REFERENCES agency (id);
295
296ALTER TABLE comment
297 ADD CONSTRAINT FKcomment514096 FOREIGN KEY (parent_comment_id) REFERENCES comment (id);
298
299ALTER TABLE comment
300 ADD CONSTRAINT FKcomment869114 FOREIGN KEY (user_id) REFERENCES "user" (id);
301
302ALTER TABLE comment
303 ADD CONSTRAINT FKcomment976754 FOREIGN KEY (article_id) REFERENCES article (id);
304
305ALTER TABLE comment_rating
306 ADD CONSTRAINT FKcomment_ra221829 FOREIGN KEY (comment_id) REFERENCES comment (id);
307
308ALTER TABLE comment_rating
309 ADD CONSTRAINT FKcomment_ra862915 FOREIGN KEY (user_id) REFERENCES "user" (id);
310
311ALTER TABLE article_rating
312 ADD CONSTRAINT FKarticle_ra780805 FOREIGN KEY (article_id) REFERENCES article (id);
313
314ALTER TABLE article_rating
315 ADD CONSTRAINT FKarticle_ra888445 FOREIGN KEY (user_id) REFERENCES "user" (id);
316
317ALTER TABLE journalist_followers
318 ADD CONSTRAINT FKjournalist156737 FOREIGN KEY (journalist_id) REFERENCES "user" (id);
319
320ALTER TABLE journalist_followers
321 ADD CONSTRAINT FKjournalist868684 FOREIGN KEY (follower_id) REFERENCES "user" (id);
322
323ALTER TABLE audit_log
324 ADD CONSTRAINT FKaudit_log816898 FOREIGN KEY (actor_id) REFERENCES "user" (id);
325
326ALTER TABLE notification
327 ADD CONSTRAINT FKnotificati703203 FOREIGN KEY (user_id) REFERENCES "user" (id);
328
329ALTER TABLE article_views
330 ADD CONSTRAINT FKarticle_vi823522 FOREIGN KEY (user_id) REFERENCES "user" (id);
331
332ALTER TABLE article_views
333 ADD CONSTRAINT FKarticle_vi931162 FOREIGN KEY (article_id) REFERENCES article (id);
334
335ALTER TABLE article_source
336 ADD CONSTRAINT FKarticle_so184222 FOREIGN KEY (article_id) REFERENCES article (id);
337
338ALTER TABLE article_media
339 ADD CONSTRAINT FKarticle_me498888 FOREIGN KEY (article_id) REFERENCES article (id);
340
341ALTER TABLE article_media
342 ADD CONSTRAINT FKarticle_me594913 FOREIGN KEY (media_id) REFERENCES media_file (id);
343
344ALTER TABLE article_tags
345 ADD CONSTRAINT FKarticle_ta701610 FOREIGN KEY (article_id) REFERENCES article (id);
346
347ALTER TABLE article_tags
348 ADD CONSTRAINT FKarticle_ta402916 FOREIGN KEY (tag_id) REFERENCES "tag" (id);
349
350ALTER TABLE role_permission
351 ADD CONSTRAINT FKrole_permi569425 FOREIGN KEY (role_id) REFERENCES role (id);
352
353ALTER TABLE role_permission
354 ADD CONSTRAINT FKrole_permi972007 FOREIGN KEY (permission_id) REFERENCES permissions (id);
355
356ALTER TABLE article
357 ADD CONSTRAINT has_previus_version FOREIGN KEY (prev_id) REFERENCES article (id);