Changeset 23cd88b for scripts

Timestamp:

08/05/26 10:46:23 (23 hours ago)

Author:

Filip Gavrilovski <filipgavrilovski28@…>

Branches:

main

Children:

c7f743d

Parents:

947fcaa

Message:

update ddl and dml scripts

Location:

scripts

Files:

• ddl.sql (modified) (3 diffs)
• dml.sql (modified) (4 diffs)

scripts/ddl.sql

@@ -29 +29 @@
     full_name TEXT NOT NULL, 
     email TEXT NOT NULL,
-    password TEXT NOT NULL,
+    password_hash TEXT NOT NULL,
     username TEXT NOT NULL, 
     profile_photo TEXT
@@ -85 +85 @@
     name   TEXT NOT NULL,
     created_by BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE
+);
+
+CREATE TABLE CONTRIBUTIONS (
+    musical_entity_id BIGINT NOT NULL REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
+    artist_id BIGINT NOT NULL REFERENCES ARTISTS(user_id) ON DELETE CASCADE,
+    contribution_order INT NOT NULL,
+ 
+    CONSTRAINT contributions_pk
+        PRIMARY KEY (musical_entity_id, artist_id),
+ 
+    CONSTRAINT contributions_order_positive
+        CHECK (contribution_order > 0),
+);
+
+CREATE TABLE ROLES (
+    role_id BIGINT PRIMARY KEY,
+    name TEXT NOT NULL,
+ 
+    CONSTRAINT roles_name_unique UNIQUE (name)
 );
 
@@ -152 +171 @@
 );
 
-CREATE TABLE ARTIST_CONTRIBUTIONS (
-    artist_id BIGINT REFERENCES ARTISTS(user_id) ON DELETE CASCADE,
-    musical_entity_id BIGINT REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
-    role TEXT NOT NULL,
-
-    CONSTRAINT artist_contribution_id
-        PRIMARY KEY (artist_id, musical_entity_id)
+CREATE TABLE CONTRIBUTION_ROLES (
+    musical_entity_id BIGINT NOT NULL,
+    artist_id BIGINT NOT NULL,
+    role_id BIGINT NOT NULL REFERENCES ROLES(role_id),
+ 
+    CONSTRAINT contribution_roles_pk
+        PRIMARY KEY (musical_entity_id, artist_id, role_id),
+ 
+    CONSTRAINT contribution_roles_contribution_fk
+        FOREIGN KEY (musical_entity_id, artist_id)
+        REFERENCES CONTRIBUTIONS(musical_entity_id, artist_id)
+        ON DELETE CASCADE
 );

scripts/dml.sql

@@ -1 +1 @@
 SET search_path TO project;
 
-INSERT INTO USERS (user_id, full_name, email, password, username, profile_photo) VALUES 
+INSERT INTO USERS (user_id, full_name, email, password_hash, username, profile_photo) VALUES 
 (1, 'Dimitar Arsov', 'dimitararsov04@gmail.com', '$2a$10$Vl6VE03lIQmGL/dW2/Ly9emzc7upycbosyjBKjIY93TbN5b1rB1Ry', 'dimitar_arsov2', null),
 (2, 'Filip Gavrilovski', 'filipgavrilovski28@gmail.com','$2a$10$oxZZfDwH9ixVdLjnaopmJufBqQpcgPp9e/Bv8aN1Kp/r.EyLIfyoi', 'filip_gavrilovski1', null),
@@ -111 +111 @@
 (13, 19,'2025-11-30 10:10:54+02'),
 (7, 21,'2025-12-06 21:23:54+02'),
-(14, 23,'2025-12-09 23:23:54+02'),
 (15, 13,'2025-12-30 21:23:54+02'),
 (16, 17,'2025-12-28 19:23:54+02'),
@@ -121 +120 @@
 (8, 18,'2026-01-05 11:23:54+02'),
 (2, 18,'2025-12-18 10:34:54+02'),
-(2, 12,'2026-02-19 16:23:54+02'),
-(1, 13,'2025-12-19 11:13:54+02');
+(2, 12,'2026-02-19 16:10:54+02'),
+(1, 13,'2025-12-19 15:13:56+02');
 
 
@@ -141 +140 @@
 (11,2), (13,2), (16,2);
 
-INSERT INTO  ARTIST_CONTRIBUTIONS (musical_entity_id, artist_id, role) VALUES
-(1,8,'MAIN_VOCAL'), (2,8,'MAIN_VOCAL'),(3,8,'MAIN_VOCAL'),
-(4,8,'MAIN_VOCAL'),(5,8,'MAIN_VOCAL'),(6,9,'MAIN_VOCAL'),
-(7,9,'MAIN_VOCAL'),(8,9,'MAIN_VOCAL'),(9,9,'MAIN_VOCAL'),
-(10,9,'MAIN_VOCAL'),(11,7,'MAIN_VOCAL'),(12,7,'MAIN_VOCAL'),
-(13,7,'MAIN_VOCAL'),(14,7,'MAIN_VOCAL'),(15,7,'MAIN_VOCAL'),
-(16,12,'MAIN_VOCAL'),(17,12,'MAIN_VOCAL'),(18,10,'MAIN_VOCAL'),
-(19,10,'MAIN_VOCAL'),(20,10,'MAIN_VOCAL'),(21,10,'MAIN_VOCAL');
+INSERT INTO CONTRIBUTIONS (musical_entity_id, artist_id, contribution_order) VALUES
+(1,8,1), (2,8,1), (3,8,1), (4,8,1), (5,8,1),
+(6,9,1), (7,9,1), (8,9,1), (9,9,1), (10,9,1),
+(11,7,1), (12,7,1), (13,7,1), (14,7,1), (15,7,1),
+(16,12,1), (17,12,1),
+(18,10,1), (19,10,1), (20,10,1), (21,10,1),
+(5,11,2),
+(13,13,2);
+
+INSERT INTO ROLES (role_id, name) VALUES
+(1, 'Lead Vocal'),
+(2, 'Backing Vocal'),
+(3, 'Guitar'),
+(4, 'Bass'),
+(5, 'Drums'),
+(6, 'Keyboards'),
+(7, 'Strings'),
+(8, 'Producer'),
+(9, 'Songwriter'),
+(10,'Mixing Engineer'),
+(11,'Mastering Engineer'),
+(12,'Featured Artist');
+ 
+INSERT INTO CONTRIBUTION_ROLES (musical_entity_id, artist_id, role_id) VALUES
+-- Kanye West: lead vocal + producer + songwriter
+(1,8,1), (1,8,8), (1,8,9),
+(2,8,1), (2,8,8), (2,8,9),
+(3,8,1), (3,8,8), (3,8,9),
+(4,8,1), (4,8,8), (4,8,9),
+(5,8,1), (5,8,8), (5,8,9),
+-- The Weeknd: vocal + songwriter
+(6,9,1), (6,9,9),
+(7,9,1), (7,9,9),
+(8,9,1), (8,9,9),
+(9,9,1), (9,9,9),
+(10,9,1), (10,9,9),
+-- Strajk: vocal + songwriter
+(11,7,1), (11,7,9),
+(12,7,1), (12,7,9),
+(13,7,1), (13,7,9),
+(14,7,1), (14,7,9),
+(15,7,1), (15,7,9),
+-- Verica Ristevska: vocal
+(16,12,1),
+(17,12,1),
+-- Bruno Mars: lead vocal + producer + songwriter
+(18,10,1), (18,10,8), (18,10,9),
+(19,10,1), (19,10,8), (19,10,9),
+(20,10,1), (20,10,8), (20,10,9),
+(21,10,1), (21,10,8), (21,10,9),
+-- guest appearances
+(5,11,12), (5,11,1),
+(13,13,12), (13,13,1);