Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

06/07/26 22:04:33 (10 days ago)

Author:

231062

Comment:

--

DatabaseProgramming

@@ +1 @@
+**Functions of common actions**
+
+Renting a book\\
+Checks if user is eligible to rent a copy 
+{{{
+CREATE OR REPLACE FUNCTION fn_rent_copy(
+    p_user_id BIGINT,
+    p_copy_id BIGINT,
+    p_employee_id BIGINT
+)
+RETURNS BIGINT LANGUAGE plpgsql AS $$
+DECLARE
+    v_membership_id BIGINT;
+    v_max_days INT;
+    v_due TIMESTAMP;
+    v_rental_id BIGINT;
+BEGIN
+    SELECT m.id, mp.max_rent_period_days
+    INTO v_membership_id, v_max_days
+    FROM Membership m
+    JOIN MembershipPlan mp ON mp.id = m.membership_plan_id
+    WHERE m.user_id = p_user_id
+      AND m.cancelled_at IS NULL
+      AND m.expires_at > NOW()
+    LIMIT 1;
+
+    IF v_membership_id IS NULL THEN
+        RAISE EXCEPTION 'No active membership';
+    END IF;
+
+    IF NOT fn_can_rent(p_user_id) THEN
+        RAISE EXCEPTION 'Max concurrent rentals reached';
+    END IF;
+
+
+    IF EXISTS (
+        SELECT 1 FROM Rental
+        WHERE resource_copy_id = p_copy_id
+          AND returned_at IS NULL
+    ) THEN
+        RAISE EXCEPTION 'Copy already rented';
+    END IF;
+
+    v_due := NOW() + (v_max_days || ' days')::INTERVAL;
+
+    INSERT INTO Rental(resource_copy_id, rented_at, due_at, membership_id, employee_id)
+    VALUES (p_copy_id, NOW(), v_due, v_membership_id, p_employee_id)
+    RETURNING id INTO v_rental_id;
+
+    RETURN v_rental_id;
+END;
+$$;
+}}}
+
+Helper function to check if a user can rent
+{{{
+CREATE OR REPLACE FUNCTION fn_can_rent(p_user_id BIGINT)
+RETURNS BOOLEAN LANGUAGE plpgsql AS $$
+DECLARE
+    v_max INT;
+    v_current INT;
+BEGIN
+    SELECT mp.max_concurrent_rentals
+    INTO v_max
+    FROM Membership m
+    JOIN MembershipPlan mp ON mp.id = m.membership_plan_id
+    WHERE m.user_id = p_user_id
+      AND m.cancelled_at IS NULL
+      AND m.expires_at > NOW()
+    LIMIT 1;
+
+    IF v_max IS NULL THEN
+        RETURN FALSE;
+    END IF;
+
+    SELECT COUNT(*)
+    INTO v_current
+    FROM Rental r
+    JOIN Membership m ON m.id = r.membership_id
+    WHERE m.user_id = p_user_id
+      AND r.returned_at IS NULL;
+
+    RETURN v_current < v_max;
+END;
+$$;
+}}}
+
+Returning copies
+Calculates penalty upon book return if applicable
+{{{
+CREATE OR REPLACE FUNCTION fn_return_copy(p_rental_id BIGINT)
+RETURNS NUMERIC LANGUAGE plpgsql AS $$
+DECLARE
+    v_due TIMESTAMP;
+    v_penalty NUMERIC := 0;
+    v_days_late INT;
+BEGIN
+    SELECT due_at INTO v_due
+    FROM Rental
+    WHERE id = p_rental_id;
+
+    IF v_due IS NULL THEN
+        RAISE EXCEPTION 'Rental not found';
+    END IF;
+
+    IF NOW() > v_due THEN
+        v_days_late := EXTRACT(DAY FROM NOW() - v_due);
+        v_penalty := v_days_late * 10; -- configurable
+    END IF;
+
+    UPDATE Rental
+    SET returned_at = NOW(),
+        penalty = v_penalty
+    WHERE id = p_rental_id;
+
+    RETURN v_penalty;
+END;
+$$;
+}}}
+
+Registering to an event
+{{{
+CREATE OR REPLACE FUNCTION fn_register_to_event(
+    p_user_id BIGINT,
+    p_event_id BIGINT
+)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE
+    v_capacity INT;
+    v_count INT;
+BEGIN
+    SELECT capacity INTO v_capacity
+    FROM Event
+    WHERE id = p_event_id;
+
+    SELECT COUNT(*) INTO v_count
+    FROM EventRegistration
+    WHERE event_id = p_event_id;
+
+    IF v_count >= v_capacity THEN
+        RAISE EXCEPTION 'Event is full';
+    END IF;
+
+    INSERT INTO EventRegistration(user_id, event_id, registered_at)
+    VALUES (p_user_id, p_event_id, NOW());
+END;
+$$;
+}}}
+
+Adding an edition to the wishlist
+{{{
+CREATE OR REPLACE FUNCTION fn_add_to_wishlist(
+    p_user_id BIGINT,
+    p_isbn VARCHAR
+)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE
+    v_wishlist_id BIGINT;
+BEGIN
+    SELECT id INTO v_wishlist_id
+    FROM Wishlist
+    WHERE user_id = p_user_id;
+
+    IF v_wishlist_id IS NULL THEN
+        INSERT INTO Wishlist(user_id)
+        VALUES (p_user_id)
+        RETURNING id INTO v_wishlist_id;
+    END IF;
+
+    INSERT INTO Wishlist_entry(edition_isbn, wishlist_id)
+    VALUES (p_isbn, v_wishlist_id)
+    ON CONFLICT DO NOTHING;
+END;
+$$;
+}}}
+
+
+Active membership check
+{{{
+CREATE OR REPLACE FUNCTION fn_has_active_membership(
+    p_user_id BIGINT
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN EXISTS (
+        SELECT 1
+        FROM Membership
+        WHERE user_id = p_user_id
+          AND cancelled_at IS NULL
+          AND expires_at > NOW()
+    );
+END;
+$$;
+}}}
+
+Create membership
+{{{
+CREATE OR REPLACE FUNCTION fn_create_membership(
+    p_user_id BIGINT,
+    p_membership_plan_id BIGINT
+)
+RETURNS BIGINT
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_duration_days INT;
+    v_membership_id BIGINT;
+    v_started_at TIMESTAMP := NOW();
+    v_expires_at TIMESTAMP;
+BEGIN
+    IF fn_has_active_membership(p_user_id) THEN
+        RAISE EXCEPTION 'User already has an active membership';
+    END IF;
+
+    SELECT duration_days
+    INTO v_duration_days
+    FROM MembershipPlan
+    WHERE id = p_membership_plan_id;
+
+    IF v_duration_days IS NULL THEN
+        RAISE EXCEPTION 'Membership plan not found';
+    END IF;
+
+    v_expires_at := v_started_at +
+                     (v_duration_days || ' days')::INTERVAL;
+
+    INSERT INTO Membership(
+        user_id,
+        membership_plan_id,
+        started_at,
+        expires_at,
+        created_at
+    )
+    VALUES (
+        p_user_id,
+        p_membership_plan_id,
+        v_started_at,
+        v_expires_at,
+        NOW()
+    )
+    RETURNING id INTO v_membership_id;
+
+    RETURN v_membership_id;
+END;
+$$;
+}}}
+
+Renew membership
+{{{
+CREATE OR REPLACE FUNCTION fn_renew_membership(
+    p_user_id BIGINT,
+    p_new_plan_id BIGINT
+)
+RETURNS BIGINT
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_current_membership_id BIGINT;
+    v_duration_days INT;
+    v_start_date TIMESTAMP;
+    v_expires_at TIMESTAMP;
+    v_new_membership_id BIGINT;
+BEGIN
+    SELECT id, expires_at
+    INTO v_current_membership_id, v_start_date
+    FROM Membership
+    WHERE user_id = p_user_id
+      AND cancelled_at IS NULL
+    ORDER BY expires_at DESC
+    LIMIT 1;
+
+    SELECT duration_days
+    INTO v_duration_days
+    FROM MembershipPlan
+    WHERE id = p_new_plan_id;
+
+    IF v_duration_days IS NULL THEN
+        RAISE EXCEPTION 'Membership plan not found';
+    END IF;
+
+    IF v_start_date IS NULL OR v_start_date < NOW() THEN
+        v_start_date := NOW();
+    END IF;
+
+    v_expires_at := v_start_date +
+                    (v_duration_days || ' days')::INTERVAL;
+
+    IF v_current_membership_id IS NOT NULL THEN
+        UPDATE Membership
+        SET cancelled_at = NOW()
+        WHERE id = v_current_membership_id;
+    END IF;
+
+    INSERT INTO Membership(
+        user_id,
+        membership_plan_id,
+        started_at,
+        expires_at,
+        created_at
+    )
+    VALUES (
+        p_user_id,
+        p_new_plan_id,
+        NOW(),
+        v_expires_at,
+        NOW()
+    )
+    RETURNING id INTO v_new_membership_id;
+
+    RETURN v_new_membership_id;
+END;
+$$;
+}}}
+
+Cancel membership
+{{{
+CREATE OR REPLACE FUNCTION fn_cancel_membership(
+    p_membership_id BIGINT
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Membership
+        WHERE id = p_membership_id
+    ) THEN
+        RAISE EXCEPTION 'Membership not found';
+    END IF;
+
+    UPDATE Membership
+    SET cancelled_at = NOW()
+    WHERE id = p_membership_id
+      AND cancelled_at IS NULL;
+END;
+$$;
+}}}
+
+Get average book rating
+{{{
+CREATE OR REPLACE FUNCTION fn_avg_rating(p_isbn VARCHAR)
+RETURNS NUMERIC LANGUAGE plpgsql AS $$
+BEGIN
+    RETURN (
+        SELECT ROUND(AVG(rating), 2)
+        FROM Review
+        WHERE resource_edition_isbn = p_isbn
+    );
+END;
+$$;
+}}}
+
+**Triggers**
+
+Automatic penalty calculation on rental update
+{{{
+CREATE OR REPLACE FUNCTION trg_rental_return()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_days_late INT;
+BEGIN
+    IF OLD.returned_at IS NULL AND NEW.returned_at IS NOT NULL THEN
+        IF NEW.due_at IS NOT NULL AND NEW.returned_at > NEW.due_at THEN
+            v_days_late := EXTRACT(DAY FROM NEW.returned_at - NEW.due_at);
+            NEW.penalty := COALESCE(NEW.penalty, 0) + (v_days_late * 10);
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER trg_rental_return_update
+BEFORE UPDATE OF returned_at ON Rental
+FOR EACH ROW
+EXECUTE FUNCTION trg_rental_return();
+}}}
+
+User soft deletion
+{{{
+CREATE OR REPLACE FUNCTION trg_soft_delete_user()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF OLD.deleted THEN
+        RETURN NULL;
+    END IF;
+
+    UPDATE "User"
+    SET
+        first_name = '[deleted_user]',
+        last_name  = '[deleted_user]',
+        email      = '[deleted_user@example.com]',
+        password   = '[deleted]',
+        deleted    = TRUE,
+        deleted_at = NOW(),
+        updated_at = NOW()
+    WHERE id = OLD.id;
+
+    RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER trg_soft_delete_user
+BEFORE DELETE ON "User"
+FOR EACH ROW
+EXECUTE FUNCTION trg_soft_delete_user();
+}}}