Changes between Version 1 and Version 2 of P6

Timestamp:

04/25/26 21:08:24 (7 days ago)

Author:

211099

Comment:

--

P6

@@ -101 +101 @@
 )
 }}}
+
+=== 2. Validate and insert story into user reading list with confirmation
+{{{ 
+WITH
+published_story AS (
+    SELECT s.story_id, s.short_description
+    FROM story s
+    JOIN status st ON s.story_id = st.story_id
+    WHERE s.story_id = 1
+      AND st.status = 'published'
+),
+user_list AS (
+    SELECT list_id, list_name, is_public
+    FROM reading_list
+    WHERE list_id = 1
+      AND user_id = 4
+),
+already_added AS (
+    SELECT 1
+    FROM reading_list_items
+    WHERE list_id = 1
+      AND story_id = 1
+),
+inserted AS (
+    INSERT INTO reading_list_items (list_id, story_id, added_at)
+    SELECT ul.list_id, ps.story_id, CURRENT_TIMESTAMP
+    FROM user_list ul
+    CROSS JOIN published_story ps
+    WHERE NOT EXISTS (SELECT 1 FROM already_added)
+    RETURNING list_id, story_id, added_at
+)
+SELECT
+    rl.list_id,
+    rl.list_name,
+    rl.is_public,
+    u.username                  AS owner,
+    COUNT(rli.story_id)         AS total_items,
+    MAX(rli.added_at)           AS last_added_at,
+    CASE
+        WHEN ins.story_id IS NOT NULL         THEN 'Story successfully added'
+        WHEN EXISTS (SELECT 1 FROM already_added) THEN 'Story already in list'
+        ELSE                                       'Failed: story not published or list not found'
+    END                         AS result_message
+FROM reading_list       rl
+JOIN users              u   ON rl.user_id  = u.user_id
+LEFT JOIN reading_list_items rli ON rl.list_id = rli.list_id
+LEFT JOIN inserted      ins ON ins.list_id  = rl.list_id
+WHERE rl.list_id = 1
+GROUP BY rl.list_id, rl.list_name, rl.is_public, u.username, ins.story_id;
+}}}
+
+=== Relational Algebra
+{{{
+
+PublishedStory <-
+π story_id, short_description
+(
+  σ s.story_id = :story_id ∧ st.status = 'published'
+  (
+    story s
+    ⨝ (s.story_id = st.story_id) status st
+  )
+)
+
+UserList <-
+σ list_id = :list_id ∧ user_id = :user_id
+(
+  reading_list
+)
+
+AlreadyAdded <-
+σ list_id = :list_id ∧ story_id = :story_id
+(
+  reading_list_items
+)
+
+NewItem <-
+{ (list_id := :list_id, story_id := :story_id, added_at := CURRENT_TIMESTAMP) }
+
+ToInsert <-
+(UserList ⨯ PublishedStory)
+− π list_id, story_id, added_at (AlreadyAdded)
+
+reading_list_items <- reading_list_items ∪ ToInsert
+
+UpdatedCount <-
+γ list_id;
+  total_items  := COUNT(story_id),
+  last_added   := MAX(added_at)
+(
+  σ list_id = :list_id (reading_list_items)
+)
+
+Result <-
+π
+  rl.list_id,
+  rl.list_name,
+  rl.is_public,
+  u.username                                        → owner,
+  uc.total_items,
+  uc.last_added,
+  CASE
+    WHEN ins.story_id IS NOT NULL     → 'Story successfully added'
+    WHEN AlreadyAdded ≠ ∅             → 'Story already in list'
+    ELSE                               'Failed: story not published or list not found'
+  END                                               → result_message
+(
+  (
+    reading_list rl
+    ⨝ (rl.user_id = u.user_id) users u
+  )
+  ⨝  (rl.list_id = uc.list_id) UpdatedCount uc
+  ⟕  (rl.list_id = ins.list_id) ToInsert ins
+)
+}}}