Changes between Version 4 and Version 5 of P6

Timestamp:

04/25/26 21:47:03 (7 days ago)

Author:

211099

Comment:

--

P6

@@ -224 +224 @@
 )
 }}}
+
+=== 3.Detailed report on percentage change in collaborator count and invitation validation for a story
+=== SQL
+{{{
+WITH
+owned_story AS (
+    SELECT s.story_id, s.short_description
+    FROM story s
+    JOIN writer w ON s.user_id = w.user_id
+    WHERE s.story_id = 5
+      AND s.user_id = :owner_user_id
+),
+invited_writer AS (
+    SELECT u.user_id, u.username, u.user_name, u.surname
+    FROM users u
+    JOIN writer w ON u.user_id = w.user_id
+    WHERE u.username = :invited_username
+),
+already_collaborating AS (
+    SELECT 1
+    FROM collaboration
+    WHERE user_id  = (SELECT user_id  FROM invited_writer)
+      AND story_id = (SELECT story_id FROM owned_story)
+),
+current_collaborators AS (
+    SELECT
+        u.username,
+        u.user_name,
+        u.surname,
+        r.roles,
+        p.permission_level,
+        c.collab_created_at
+    FROM collaboration    c
+    JOIN users            u ON c.user_id  = u.user_id
+    JOIN roles            r ON c.user_id  = r.user_id AND c.story_id = r.story_id
+    JOIN permission_level p ON c.user_id  = p.user_id AND c.story_id = p.story_id
+    WHERE c.story_id = (SELECT story_id FROM owned_story)
+),
+notification_preview AS (
+    SELECT
+        iw.user_id AS recipient_user_id,
+        iw.username AS recipient_username,
+        'You have been invited to collaborate on "'
+            || os.short_description || '"' AS notification_content,
+        'collaboration' AS type,
+        '/story/' || os.story_id::VARCHAR   AS link
+    FROM invited_writer iw
+    CROSS JOIN owned_story os
+)
+SELECT
+    cc.username,
+    cc.user_name,
+    cc.surname,
+    cc.roles,
+    cc.permission_level,
+    cc.collab_created_at,
+    COUNT(*) OVER () AS total_collaborators,
+    np.notification_content AS pending_notification,
+    CASE
+        WHEN NOT EXISTS (SELECT 1 FROM owned_story)          THEN 'Failed: story not found or not owned by you'
+        WHEN NOT EXISTS (SELECT 1 FROM invited_writer)       THEN 'Failed: invited user is not a writer'
+        WHEN EXISTS     (SELECT 1 FROM already_collaborating) THEN 'User is already a collaborator'
+        ELSE                                                       'Ready to add collaborator'
+    END   AS status
+FROM current_collaborators          cc
+LEFT JOIN notification_preview      np ON np.recipient_username = :invited_username
+ORDER BY cc.collab_created_at;
+}}}
+=== Relational Algebra
+{{{
+OwnedStory ←
+π story_id, short_description
+(
+  σ s.story_id = :story_id ∧ s.user_id = :owner_user_id
+  (
+    story s
+    ⨝ (s.user_id = w.user_id) writer w
+  )
+)
+
+InvitedWriter ←
+π user_id, username, user_name, surname
+(
+  σ u.username = :invited_username
+  (
+    users u
+    ⨝ (u.user_id = w.user_id) writer w
+  )
+)
+
+AlreadyCollaborating ←
+σ user_id = (π user_id (InvitedWriter))
+  ∧ story_id = (π story_id (OwnedStory))
+(
+  collaboration
+)
+
+CurrentCollaborators ←
+π
+  u.username, u.user_name, u.surname,
+  r.roles, p.permission_level,
+  c.collab_created_at
+(
+  (
+    (
+      σ story_id = (π story_id (OwnedStory)) (collaboration c)
+      ⨝ (c.user_id = u.user_id) users u
+    )
+    ⨝ (c.user_id = r.user_id ∧ c.story_id = r.story_id) roles r
+  )
+  ⨝ (c.user_id = p.user_id ∧ c.story_id = p.story_id) permission_level p
+)
+
+NotificationPreview ←
+π
+  iw.user_id        → recipient_user_id,
+  iw.username       → recipient_username,
+  'You have been invited to collaborate on "' || os.short_description || '"'
+                    → notification_content,
+  'collaboration'   → type,
+  '/story/' || os.story_id → link
+(
+  InvitedWriter iw
+  ⨯ OwnedStory os
+)
+
+Result ←
+π
+  cc.username,
+  cc.user_name,
+  cc.surname,
+  cc.roles,
+  cc.permission_level,
+  cc.collab_created_at,
+  COUNT(*) OVER ()  → total_collaborators,
+  np.notification_content → pending_notification,
+  CASE
+    WHEN OwnedStory = ∅           → 'Failed: story not found or not owned by you'
+    WHEN InvitedWriter = ∅        → 'Failed: invited user is not a writer'
+    WHEN AlreadyCollaborating ≠ ∅ → 'User is already a collaborator'
+    ELSE                          → 'Ready to add collaborator'
+  END               → status
+(
+  CurrentCollaborators cc
+  ⟕ (cc.username = np.recipient_username) NotificationPreview np
+)
+}}}