Changes between Version 2 and Version 3 of P6

Timestamp:

04/25/26 21:42:40 (7 days ago)

Author:

211099

Comment:

--

P6

@@ -124 +124 @@
       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
+list_items AS (
+    SELECT
+        rli.list_id,
+        COUNT(rli.story_id) AS total_items,
+        MAX(rli.added_at)   AS last_added_at
+    FROM reading_list_items rli
+    WHERE rli.list_id = 1
+    GROUP BY rli.list_id
 )
 SELECT
@@ -136 +137 @@
     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,
+    u.username                              AS owner,
+    ps.story_id                             AS story_to_add,
+    ps.short_description                    AS story_description,
+    COALESCE(li.total_items, 0)             AS total_items,
+    li.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;
+        WHEN NOT EXISTS (SELECT 1 FROM published_story) THEN 'Failed: story not published or not found'
+        WHEN NOT EXISTS (SELECT 1 FROM user_list)       THEN 'Failed: reading list not found or not owned by you'
+        WHEN EXISTS     (SELECT 1 FROM already_added)   THEN 'Story already in list'
+        ELSE                                                 'Ready to add story'
+    END                                     AS status
+FROM reading_list               rl
+JOIN users                      u   ON rl.user_id  = u.user_id
+LEFT JOIN published_story       ps  ON ps.story_id = 1
+LEFT JOIN list_items            li  ON li.list_id  = rl.list_id
+WHERE rl.list_id = 1;
 }}}
 
 === Relational Algebra
 {{{
-
-PublishedStory <-
+ValidateAndInsertStoryIntoUserReadingListWithConfirmation
+
+
+PublishedStory ←
 π story_id, short_description
 (
-  σ s.story_id = :story_id ∧ st.status = 'published'
+  σ s.story_id = 1 ∧ st.status = 'published'
   (
     story s
@@ -165 +170 @@
 )
 
-UserList <-
-σ list_id = :list_id ∧ user_id = :user_id
-(
-  reading_list
-)
-
-AlreadyAdded <-
-σ list_id = :list_id ∧ story_id = :story_id
+UserList ←
+π list_id, list_name, is_public
+(
+  σ list_id = 1 ∧ user_id = 4
+  (
+    reading_list
+  )
+)
+
+AlreadyAdded ←
+σ list_id = 1 ∧ story_id = 1
 (
   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 <-
+ListItems ←
 γ list_id;
   total_items  := COUNT(story_id),
-  last_added   := MAX(added_at)
-(
-  σ list_id = :list_id (reading_list_items)
-)
-
-Result <-
+  last_added_at := MAX(added_at)
+(
+  σ list_id = 1
+  (
+    reading_list_items
+  )
+)
+
+Result ←
 π
   rl.list_id,
@@ -200 +202 @@
   rl.is_public,
   u.username                                        → owner,
-  uc.total_items,
-  uc.last_added,
+  ps.story_id                                       → story_to_add,
+  ps.short_description                              → story_description,
+  COALESCE(li.total_items, 0)                       → total_items,
+  li.last_added_at,
   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
-)
-}}}
+    WHEN PublishedStory = ∅   → 'Failed: story not published or not found'
+    WHEN UserList = ∅         → 'Failed: reading list not found or not owned by you'
+    WHEN AlreadyAdded ≠ ∅     → 'Story already in list'
+    ELSE                      → 'Ready to add story'
+  END                                               → status
+(
+  (
+    (
+      σ rl.list_id = 1 (reading_list rl)
+      ⨝ (rl.user_id = u.user_id) users u
+    )
+    ⟕ (ps.story_id = 1) PublishedStory ps
+  )
+  ⟕ (li.list_id = rl.list_id) ListItems li
+)
+}}}