Changes between Initial Version and Version 1 of P7

Timestamp:

04/25/26 22:54:02 (7 days ago)

Author:

211099

Comment:

--

P7

@@ +1 @@
+= Advanced Database Development (SQL DDL)
+
+== Custom Domains
+{{{
+-- Check email format
+CREATE DOMAIN valid_email AS VARCHAR(255)
+CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
+
+-- Check rating range from 0 to 5
+CREATE DOMAIN story_rating AS DECIMAL(3,2)
+CHECK (VALUE >= 0.00 AND VALUE <= 5.00);
+
+-- Status of accepted values
+CREATE DOMAIN story_status AS VARCHAR(50)
+CHECK (VALUE IN ('draft', 'published', 'archived'));
+
+-- Permission level from 1 to 5
+CREATE DOMAIN permission_lvl AS INTEGER
+CHECK (VALUE >= 1 AND VALUE <= 5);
+
+-- URL / image path
+CREATE DOMAIN valid_url AS VARCHAR(2048)
+CHECK (VALUE ~* '^https?://.+');
+}}}
+
+== Views
+{{{
+-- Detailed view for each story with statistic
+CREATE OR REPLACE VIEW story_details_view AS
+SELECT
+    s.story_id,
+    s.short_description,
+    s.mature_content,
+    s.image,
+    s.story_created_at,
+    u.username AS writer,
+    u.user_name,
+    u.surname,
+    st.status,
+    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
+    COALESCE(SUM(ch.view_count), 0) AS total_views,
+    ROUND(AVG(ch.rating), 2) AS avg_rating,
+    COUNT(DISTINCT l.user_id) AS total_likes,
+    COUNT(DISTINCT c.comment_id) AS total_comments,
+    COUNT(DISTINCT hg.genre_id) AS total_genres,
+    COUNT(DISTINCT col.user_id) AS total_collaborators
+FROM story s
+JOIN writer w   ON s.user_id    = w.user_id
+JOIN users u   ON w.user_id    = u.user_id
+JOIN "status" st  ON s.story_id   = st.story_id
+LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
+LEFT JOIN likes l   ON s.story_id   = l.story_id
+LEFT JOIN "comment" c   ON s.story_id   = c.story_id
+LEFT JOIN has_genre hg  ON s.story_id   = hg.story_id
+LEFT JOIN collaboration col ON s.story_id   = col.story_id
+GROUP BY
+    s.story_id, s.short_description, s.mature_content,
+    s.image, s.story_created_at,
+    u.username, u.user_name, u.surname, st.status;
+-- Top 10 most popular stories by likes count =
+CREATE MATERIALIZED VIEW top_stories_by_likes AS
+SELECT
+    s.story_id,
+    s.short_description,
+    u.username AS writer,
+    st.status,
+    COUNT(DISTINCT l.user_id)  AS total_likes,
+    COUNT(DISTINCT c.comment_id) AS total_comments,
+    ROUND(AVG(ch.rating), 2) AS avg_rating
+FROM story s
+JOIN writer w   ON s.user_id    = w.user_id
+JOIN users u   ON w.user_id    = u.user_id
+JOIN "status" st  ON s.story_id   = st.story_id
+AND st.status   = 'published'
+LEFT JOIN likes l   ON s.story_id   = l.story_id
+LEFT JOIN "comment" c   ON s.story_id   = c.story_id
+LEFT JOIN chapter  ch  ON s.story_id   = ch.story_id
+GROUP BY
+    s.story_id, s.short_description,
+    u.username, st.status
+ORDER BY total_likes DESC
+LIMIT 10;
+
+-- View for user profile with all his stories 
+CREATE OR REPLACE VIEW writer_profile_view AS
+SELECT
+    u.user_id,
+    u.username,
+    u.user_name,
+    u.surname,
+    u.user_created_at,
+    COUNT(DISTINCT s.story_id)AS total_stories,
+    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
+    COALESCE(SUM(ch.view_count), 0) AS total_views,
+    COUNT(DISTINCT l.user_id) AS total_likes,
+    COUNT(DISTINCT c.comment_id) AS total_comments,
+    ROUND(AVG(ch.rating), 2) AS avg_rating
+FROM users u
+JOIN writer w   ON u.user_id    = w.user_id
+LEFT JOIN story s   ON w.user_id    = s.user_id
+LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
+LEFT JOIN likes l   ON s.story_id   = l.story_id
+LEFT JOIN comment  c   ON s.story_id   = c.story_id
+GROUP BY
+    u.user_id, u.username, u.user_name,
+    u.surname, u.user_created_at;
+}}}
+
+== Stored Procedures / Functions
+-- Table for daily top story
+CREATE TABLE daily_top_stories (
+    day         DATE PRIMARY KEY,
+    story_id    INTEGER NULL REFERENCES story(story_id),
+    total_likes BIGINT
+);
+
+-- Procedure: saves the most liked story of the previous day
+CREATE OR REPLACE PROCEDURE get_daily_top_story()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    yesterday TIMESTAMP;
+BEGIN
+    yesterday := CURRENT_DATE - 1;
+
+    IF EXISTS (
+        SELECT 1 FROM likes
+        WHERE like_created_at BETWEEN yesterday AND CURRENT_DATE
+    ) THEN
+        INSERT INTO daily_top_stories (day, story_id, total_likes)
+        SELECT
+            CURRENT_DATE - 1,
+            s.story_id,
+            COUNT(l.user_id) AS total_likes
+        FROM story          s
+        JOIN likes          l ON s.story_id = l.story_id
+        WHERE l.like_created_at BETWEEN yesterday AND CURRENT_DATE
+        GROUP BY s.story_id
+        ORDER BY total_likes DESC
+        LIMIT 1;
+    ELSE
+        INSERT INTO daily_top_stories (day, story_id, total_likes)
+        VALUES (CURRENT_DATE - 1, NULL, 0);
+    END IF;
+END;
+$$;
+
+-- Procedure: sends a notification to all collaborators of a story
+CREATE OR REPLACE PROCEDURE notify_collaborators(
+    p_story_id  INTEGER,
+    p_message   TEXT,
+    p_type      VARCHAR(50),
+    p_link      VARCHAR(500)
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    collab RECORD;
+BEGIN
+    FOR collab IN
+        SELECT c.user_id
+        FROM collaboration c
+        WHERE c.story_id = p_story_id
+    LOOP
+        INSERT INTO notification (
+            notification_content,
+            is_read,
+            recipient_user_id,
+            type,
+            link,
+            notification_created_at
+        )
+        VALUES (
+            p_message,
+            FALSE,
+            collab.user_id,
+            p_type,
+            p_link,
+            CURRENT_TIMESTAMP
+        );
+    END LOOP;
+END;
+$$;
+
+-- Function: calculates the engagement score for a given story
+CREATE OR REPLACE FUNCTION calculate_engagement_score(p_story_id INTEGER)
+RETURNS DECIMAL
+LANGUAGE sql
+AS $$
+    SELECT ROUND(
+        (COUNT(DISTINCT l.user_id) + COUNT(DISTINCT c.comment_id))::DECIMAL
+        / NULLIF(COALESCE(SUM(ch.view_count), 0), 0) * 100, 2
+    )
+    FROM story s
+    LEFT JOIN likes l   ON s.story_id = l.story_id
+    LEFT JOIN "comment" c   ON s.story_id = c.story_id
+    LEFT JOIN chapter ch  ON s.story_id = ch.story_id
+    WHERE s.story_id = p_story_id;
+$$;