Changeset 23cd88b for scripts/ddl.sql

Timestamp:

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

Author:

Filip Gavrilovski <filipgavrilovski28@…>

Branches:

main

Children:

c7f743d

Parents:

947fcaa

Message:

update ddl and dml scripts

File:

• scripts/ddl.sql (modified) (3 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
 );