RelationalDesign: kreiranje.sql

File kreiranje.sql, 9.7 KB (added by 191273, 4 years ago)

kreiranje.sql updated

Line 
1-- DROP TYPE project."offer_status";
2
3CREATE TYPE project."offer_status" AS ENUM (
4 'IN PROGRESS',
5 'WAITING FOR PAYMENT',
6 'COMPLETED',
7 'DECLINED');
8
9-- DROP TYPE project."user_type";
10
11CREATE TYPE project."user_type" AS ENUM (
12 'ORGANIZER',
13 'ARTIST',
14 'MANAGER');
15
16-- project.artist_types definition
17
18-- Drop table
19
20-- DROP TABLE project.artist_types;
21
22CREATE TABLE project.artist_types (
23 id serial4 NOT NULL,
24 "name" varchar NOT NULL,
25 CONSTRAINT artist_types_pk PRIMARY KEY (id)
26);
27CREATE UNIQUE INDEX artist_types_id_uindex ON project.artist_types USING btree (id);
28
29
30-- project.event_types definition
31
32-- Drop table
33
34-- DROP TABLE project.event_types;
35
36CREATE TABLE project.event_types (
37 id serial4 NOT NULL,
38 "name" varchar NOT NULL,
39 CONSTRAINT event_types_pk PRIMARY KEY (id)
40);
41CREATE UNIQUE INDEX event_types_id_uindex ON project.event_types USING btree (id);
42
43
44-- project.genres definition
45
46-- Drop table
47
48-- DROP TABLE project.genres;
49
50CREATE TABLE project.genres (
51 id serial4 NOT NULL,
52 "name" varchar NOT NULL,
53 CONSTRAINT genres_pk PRIMARY KEY (id)
54);
55CREATE UNIQUE INDEX genres_id_uindex ON project.genres USING btree (id);
56
57
58-- project.password_resets definition
59
60-- Drop table
61
62-- DROP TABLE project.password_resets;
63
64CREATE TABLE project.password_resets (
65 email varchar(255) NOT NULL,
66 "token" varchar(255) NOT NULL,
67 created_at timestamp(0) NULL
68);
69CREATE INDEX password_resets_email_index ON project.password_resets USING btree (email);
70
71
72-- project.users definition
73
74-- Drop table
75
76-- DROP TABLE project.users;
77
78CREATE TABLE project.users (
79 id serial4 NOT NULL,
80 email varchar NOT NULL,
81 username varchar(100) NOT NULL,
82 "password" varchar NOT NULL,
83 "name" varchar(80) NOT NULL,
84 created_at timestamp NOT NULL,
85 updated_at timestamp NULL,
86 deleted_at timestamp NULL,
87 email_verified_at timestamp NULL,
88 profile_picture varchar NOT NULL,
89 last_login_at timestamp NULL,
90 last_login_ip varchar(45) NULL,
91 user_agent varchar NULL,
92 "type" project."user_type" NOT NULL,
93 remember_token varchar(100) NULL,
94 stripe_id varchar NULL,
95 pm_type varchar NULL,
96 pm_last_four bpchar(4) NULL,
97 trial_ends_at timestamp NULL,
98 CONSTRAINT users_pk PRIMARY KEY (id)
99);
100CREATE UNIQUE INDEX users_email_uindex ON project.users USING btree (email);
101CREATE UNIQUE INDEX users_id_uindex ON project.users USING btree (id);
102CREATE UNIQUE INDEX users_username_uindex ON project.users USING btree (username);
103
104
105-- project.managers definition
106
107-- Drop table
108
109-- DROP TABLE project.managers;
110
111CREATE TABLE project.managers (
112 user_id int4 NOT NULL,
113 CONSTRAINT managers_pk PRIMARY KEY (user_id),
114 CONSTRAINT managers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
115);
116
117
118-- project.organizers definition
119
120-- Drop table
121
122-- DROP TABLE project.organizers;
123
124CREATE TABLE project.organizers (
125 user_id int4 NOT NULL,
126 city varchar(85) NULL,
127 country varchar(56) NULL,
128 CONSTRAINT organizers_pk PRIMARY KEY (user_id),
129 CONSTRAINT organizers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
130);
131CREATE UNIQUE INDEX organizers_user_id_uindex ON project.organizers USING btree (user_id);
132
133
134-- project.artists definition
135
136-- Drop table
137
138-- DROP TABLE project.artists;
139
140CREATE TABLE project.artists (
141 user_id int4 NOT NULL,
142 birth_date date NULL,
143 city varchar(85) NULL,
144 country varchar(56) NULL,
145 short_description text NULL,
146 price_per_hour float4 NULL,
147 instagram_link varchar NULL,
148 soundcloud_link varchar NULL,
149 apple_music_link varchar NULL,
150 youtube_link varchar NULL,
151 spotify_link varchar NULL,
152 manager_id int4 NULL,
153 artist_type_id int4 NULL,
154 admin_verified_at timestamp NULL,
155 CONSTRAINT artists_pk PRIMARY KEY (user_id),
156 CONSTRAINT artists_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
157 CONSTRAINT artists_fk_manager FOREIGN KEY (manager_id) REFERENCES project.managers(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
158 CONSTRAINT artists_fk_type FOREIGN KEY (artist_type_id) REFERENCES project.artist_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
159);
160CREATE UNIQUE INDEX artists_user_id_uindex ON project.artists USING btree (user_id);
161
162
163-- project.events definition
164
165-- Drop table
166
167-- DROP TABLE project.events;
168
169CREATE TABLE project.events (
170 id serial4 NOT NULL,
171 title varchar(100) NOT NULL,
172 slug varchar(150) NOT NULL,
173 start_time time NOT NULL,
174 end_time time NOT NULL,
175 event_date date NOT NULL,
176 city varchar(85) NOT NULL,
177 country varchar(56) NOT NULL,
178 description text NULL,
179 event_type_id int4 NOT NULL,
180 updated_at timestamp NULL,
181 created_at timestamp NOT NULL,
182 organizer_id int4 NOT NULL,
183 CONSTRAINT events_pk PRIMARY KEY (id),
184 CONSTRAINT events_fk_event_type FOREIGN KEY (event_type_id) REFERENCES project.event_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
185 CONSTRAINT events_fk_organizer FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
186);
187CREATE UNIQUE INDEX events_id_uindex ON project.events USING btree (id);
188CREATE UNIQUE INDEX events_slug_uindex ON project.events USING btree (slug);
189
190
191-- project.manager_invites definition
192
193-- Drop table
194
195-- DROP TABLE project.manager_invites;
196
197CREATE TABLE project.manager_invites (
198 id serial4 NOT NULL,
199 email varchar NOT NULL,
200 invitation_token varchar NOT NULL,
201 created_at timestamp NOT NULL,
202 updated_at timestamp NULL,
203 registered_at timestamp NULL,
204 artist_id int4 NOT NULL,
205 CONSTRAINT manager_invites_pk PRIMARY KEY (id),
206 CONSTRAINT manager_invites_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
207);
208CREATE UNIQUE INDEX manager_invites_id_uindex ON project.manager_invites USING btree (id);
209
210
211-- project.offers definition
212
213-- Drop table
214
215-- DROP TABLE project.offers;
216
217CREATE TABLE project.offers (
218 id serial4 NOT NULL,
219 slug varchar(150) NOT NULL,
220 payment_type int4 NOT NULL,
221 created_at timestamp NOT NULL,
222 updated_at timestamp NULL,
223 completed_at timestamp NULL,
224 artist_id int4 NOT NULL,
225 event_id int4 NOT NULL,
226 price float4 NULL,
227 status project."offer_status" NOT NULL,
228 CONSTRAINT offers_pk PRIMARY KEY (id),
229 CONSTRAINT offers_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
230 CONSTRAINT offers_fk_event FOREIGN KEY (event_id) REFERENCES project.events(id) ON DELETE RESTRICT ON UPDATE RESTRICT
231);
232CREATE UNIQUE INDEX offers_id_uindex ON project.offers USING btree (id);
233CREATE UNIQUE INDEX offers_slug_uindex ON project.offers USING btree (slug);
234
235
236-- project.reviews definition
237
238-- Drop table
239
240-- DROP TABLE project.reviews;
241
242CREATE TABLE project.reviews (
243 id serial4 NOT NULL,
244 rating int4 NOT NULL,
245 "content" text NOT NULL,
246 created_at timestamp NOT NULL DEFAULT now(),
247 organizer_id int4 NOT NULL,
248 artist_id int4 NOT NULL,
249 CONSTRAINT reviews_pk PRIMARY KEY (id),
250 CONSTRAINT reviews_fk_artists FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
251 CONSTRAINT reviews_fk_organizers FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE CASCADE ON UPDATE CASCADE
252);
253CREATE UNIQUE INDEX reviews_id_uindex ON project.reviews USING btree (id);
254
255
256-- project.transactions definition
257
258-- Drop table
259
260-- DROP TABLE project.transactions;
261
262CREATE TABLE project.transactions (
263 id serial4 NOT NULL,
264 "name" varchar NOT NULL,
265 stripe_id varchar NOT NULL,
266 stripe_price float4 NULL,
267 created_at timestamp NOT NULL,
268 updated_at timestamp NULL,
269 offer_id int4 NOT NULL,
270 invoice_id varchar NULL,
271 CONSTRAINT transactions_pk PRIMARY KEY (id),
272 CONSTRAINT transactions_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
273);
274CREATE UNIQUE INDEX transactions_id_uindex ON project.transactions USING btree (id);
275CREATE UNIQUE INDEX transactions_name_uindex ON project.transactions USING btree (name);
276
277
278-- project.artist_images definition
279
280-- Drop table
281
282-- DROP TABLE project.artist_images;
283
284CREATE TABLE project.artist_images (
285 id serial4 NOT NULL,
286 "path" varchar NOT NULL,
287 created_at timestamp NOT NULL,
288 updated_at timestamp NULL,
289 artist_id int4 NOT NULL,
290 CONSTRAINT images_pk PRIMARY KEY (id),
291 CONSTRAINT artist_images_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE
292);
293CREATE UNIQUE INDEX images_id_uindex ON project.artist_images USING btree (id);
294
295
296-- project.artist_sings_genres definition
297
298-- Drop table
299
300-- DROP TABLE project.artist_sings_genres;
301
302CREATE TABLE project.artist_sings_genres (
303 artist_id int4 NOT NULL,
304 genre_id int4 NOT NULL,
305 CONSTRAINT artist_sings_genres_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
306 CONSTRAINT artist_sings_genres_fk_genre FOREIGN KEY (genre_id) REFERENCES project.genres(id) ON DELETE RESTRICT ON UPDATE RESTRICT
307);
308
309
310-- project.offer_comments definition
311
312-- Drop table
313
314-- DROP TABLE project.offer_comments;
315
316CREATE TABLE project.offer_comments (
317 id serial4 NOT NULL,
318 "content" text NOT NULL,
319 created_at timestamp NOT NULL,
320 updated_at timestamp NULL,
321 author_id int4 NOT NULL,
322 offer_id int4 NOT NULL,
323 CONSTRAINT offer_comments_pk PRIMARY KEY (id),
324 CONSTRAINT offer_comments_fk_author FOREIGN KEY (author_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
325 CONSTRAINT offer_comments_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
326);
327CREATE UNIQUE INDEX offer_comments_id_uindex ON project.offer_comments USING btree (id);