Changes between Version 3 and Version 4 of DatabaseProgramming-AdvDb

Timestamp:

06/04/26 09:37:27 (12 days ago)

Author:

231175

Comment:

--

DatabaseProgramming-AdvDb

@@ -19 +19 @@
         e_id bigint;
     begin
+        -- Validate rating first, before any DB work
+        if new_rating < 1 or new_rating > 5 then
+            raise exception 'Rating must be between 1 and 5, got %', new_rating
+                using errcode = 'CHECK_VIOLATION';
+        end if;
+
+
         select e.id into e_id
         from enrollment e
@@ -25 +32 @@
         limit 1;
 
-        if exists(select 1 from enrollment e join review rv on e.id = rv.enrollment_id where e.id = e_id) then
-            return null; -- User has already written a review for this course version
-        else
-            insert into review (rating, comment, date, enrollment_id)
-            values (write_review.new_rating, write_review.new_comment, now(), e_id)
-            returning * into r;
-            return r;
+        -- Validate enrollment exists
+        if e_id is null then
+            raise exception 'No enrollment found for user % on course version %',
+                user_id, course_version_id
+                using errcode = 'NO_DATA_FOUND';
         end if;
+
+        -- Validate there's no review for that enrollment
+        if exists(select 1 from review rv where rv.enrollment_id = e_id) then
+            raise exception 'Review already submitted for enrollment %', e_id
+                using errcode = 'UNIQUE_VIOLATION';
+        end if;
+
+
+        insert into review (rating, comment, date, enrollment_id)
+        values (new_rating, new_comment, now(), e_id)
+        returning * into r;
+
+        return r;
     end;
 $$ language plpgsql;