Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/29/26 18:49:26 (3 weeks ago)

Author:

231101

Comment:

--

DatabaseProgramming

@@ +1 @@
+=Функции, процедури и тригери
+
+Во овој дел се прикажани SQL функциите, процедурите и тригер-функциите кои се користат во системот за управување со библиотека.
+Тие ја имплементираат главната бизнис логика на апликацијата, како што се позајмување книги, враќање книги, регистрација на членови, резервација на книги, автоматска проверка на членство и автоматско креирање казна при задоцнето враќање.
+
+
+
+== 1. Функции ==
+
+=== 1.1 borrow_book ===
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION public.borrow_book(
+p_borrowing_id bigint,
+p_member_id bigint,
+p_copy_id bigint,
+p_employee_id bigint
+)
+RETURNS text
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+v_available INTEGER;
+v_membership_status VARCHAR;
+BEGIN
+
+```
+-- Проверка дали книгата е достапна
+SELECT available
+INTO v_available
+FROM bookcopy
+WHERE copy_id = p_copy_id;
+
+IF v_available = 0 THEN
+    RETURN 'Book copy is not available';
+END IF;
+
+-- Проверка дали членот е активен
+SELECT membership_status
+INTO v_membership_status
+FROM member
+WHERE member_id = p_member_id;
+
+IF v_membership_status != 'ACTIVE' THEN
+    RETURN 'Member is not active';
+END IF;
+
+-- Креирање borrowing
+INSERT INTO borrowing(
+    borrowing_id,
+    book_id,
+    borrow_date,
+    due_date,
+    copy_id,
+    membership_id,
+    borrowed_by
+)
+VALUES(
+    p_borrowing_id,
+    (SELECT book_id
+     FROM bookcopy
+     WHERE copy_id = p_copy_id),
+    CURRENT_DATE,
+    CURRENT_DATE + INTERVAL '14 days',
+    p_copy_id,
+    p_member_id,
+    p_employee_id
+);
+
+-- Update availability
+UPDATE bookcopy
+SET available = 0
+WHERE copy_id = p_copy_id;
+
+RETURN 'Book borrowed successfully';
+```
+
+END;
+$function$;
+}}}
+
+'''Опис:'''
+Функцијата `borrow_book` се користи за позајмување конкретна копија од книга.
+Најпрво проверува дали копијата е достапна, а потоа проверува дали членот има активен статус.
+Ако условите се исполнети, се внесува нов запис во табелата `borrowing` и копијата се означува како недостапна.
+
+'''Употреба:'''
+Оваа функција ја имплементира главната логика за позајмување книга во библиотеката.
+Се користи за да се спречи позајмување на недостапна книга или позајмување од член кој нема активен статус.
+Со тоа се обезбедува контрола врз процесот на позајмување и ажурирање на достапноста на книгите.
+
+
+
+=== 1.2 register_member ===
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION public.register_member(
+p_member_id bigint,
+p_first_name character varying,
+p_last_name character varying,
+p_email character varying,
+p_phone character varying,
+p_address text
+)
+RETURNS text
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+v_existing_email INTEGER;
+v_existing_member INTEGER;
+BEGIN
+
+```
+-- check for existing member
+SELECT COUNT(*)
+INTO v_existing_member
+FROM member
+WHERE member_id = p_member_id;
+
+IF v_existing_member > 0 THEN
+    RETURN 'Member is already registered';
+END IF;
+
+-- check for existing email
+SELECT COUNT(*)
+INTO v_existing_email
+FROM member
+WHERE email = p_email;
+
+IF v_existing_email > 0 THEN
+    RETURN 'Email already exists';
+END IF;
+
+-- create new member
+INSERT INTO member(
+    member_id,
+    first_name,
+    last_name,
+    email,
+    phone,
+    address,
+    membership_status,
+    membership_start_date,
+    membership_end_date
+)
+VALUES(
+    p_member_id,
+    p_first_name,
+    p_last_name,
+    p_email,
+    p_phone,
+    p_address,
+    'ACTIVE',
+    CURRENT_DATE,
+    CURRENT_DATE + INTERVAL '1 year'
+);
+
+RETURN 'Member registered successfully';
+```
+
+END;
+$function$;
+}}}
+
+'''Опис:'''
+Функцијата `register_member` се користи за регистрација на нов член во библиотеката.
+Најпрво проверува дали веќе постои член со истиот `member_id`, а потоа проверува дали внесениот email веќе се користи.
+Ако членот и email адресата не постојат, се креира нов член со статус `ACTIVE` и членство со важност од една година.
+
+'''Употреба:'''
+Оваа функција ја имплементира логиката за регистрација на корисници во библиотечниот систем.
+Се користи за да се спречи дуплирање на членови и користење на иста email адреса од повеќе корисници.
+На овој начин системот обезбедува правилна евиденција на членовите.
+
+
+
+=== 1.3 return_book ===
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION public.return_book(p_borrowing_id bigint)
+RETURNS text
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+v_due_date DATE;
+v_copy_id BIGINT;
+v_return_date DATE;
+v_late_days INTEGER;
+v_status_id INTEGER;
+BEGIN
+
+```
+-- take info from borrowing
+SELECT due_date, copy_id, return_date
+INTO v_due_date, v_copy_id, v_return_date
+FROM borrowing
+WHERE borrowing_id = p_borrowing_id;
+
+-- check for existing borrow
+IF v_copy_id IS NULL THEN
+    RETURN 'Borrowing record not found';
+END IF;
+
+-- check if book is returned
+IF v_return_date IS NOT NULL THEN
+    RETURN 'Book already returned';
+END IF;
+
+-- Update return date
+UPDATE borrowing
+SET return_date = CURRENT_DATE
+WHERE borrowing_id = p_borrowing_id;
+
+-- bookstatus
+SELECT status_id
+INTO v_status_id
+FROM bookcopy
+WHERE copy_id = v_copy_id;
+
+-- check for dameged book
+IF v_status_id = 5 THEN
+    UPDATE bookcopy
+    SET available = 0
+    WHERE copy_id = v_copy_id;
+
+    RETURN 'Book returned with damage';
+END IF;
+
+-- book is available
+UPDATE bookcopy
+SET available = 1,
+    status_id = 1
+WHERE copy_id = v_copy_id;
+
+-- check for late retun
+IF v_due_date IS NOT NULL THEN
+    v_late_days := CURRENT_DATE - v_due_date;
+
+    IF v_late_days > 0 THEN
+        RETURN 'Book returned late by ' || v_late_days || ' days';
+    END IF;
+END IF;
+
+RETURN 'Book returned successfully';
+```
+
+END;
+$function$;
+}}}
+
+'''Опис:'''
+Функцијата `return_book` се користи при враќање на позајмена книга.
+Таа проверува дали постои запис за позајмување, дали книгата веќе е вратена и потоа го ажурира датумот на враќање.
+Дополнително, проверува дали книгата е оштетена и дали е вратена со задоцнување.
+
+'''Употреба:'''
+Оваа функција ја имплементира логиката за враќање книги во библиотеката.
+Ако книгата е вратена во добра состојба, копијата повторно се означува како достапна.
+Ако книгата е оштетена, таа останува недостапна, а ако е вратена по рокот, системот враќа информација за бројот на денови на доцнење.
+
+'''Забелешка:'''
+Во оваа функција оштетена книга се проверува со `status_id = 5`.
+Ако во базата оштетените книги се означуваат со друга вредност, на пример `status_id = 2`, тогаш условот треба да се усогласи со вистинската вредност во табелата `bookcopy`.
+
+
+
+== 2. Процедури ==
+
+=== 2.1 sp_borrow_book ===
+
+{{{#!sql
+CREATE OR REPLACE PROCEDURE public.sp_borrow_book(
+IN p_book_id integer,
+IN p_membership_id integer,
+IN p_due_date date,
+IN p_borrowed_by integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+DECLARE
+v_copy_id INT;
+BEGIN
+
+```
+-- free copy
+SELECT copy_id
+INTO v_copy_id
+FROM bookcopy
+WHERE book_id = p_book_id
+  AND available = 1
+LIMIT 1;
+
+-- doesnt have-error
+IF v_copy_id IS NULL THEN
+    RAISE EXCEPTION 'No available copy for this book!';
+END IF;
+
+-- borrowing row
+INSERT INTO borrowing(
+    book_id,
+    borrow_date,
+    return_date,
+    due_date,
+    return_status,
+    copy_id,
+    membership_id,
+    borrowed_by,
+    returned_by,
+    reservation_id
+)
+VALUES (
+    p_book_id,
+    CURRENT_DATE,
+    null,
+    p_due_date,
+    NULL,
+    v_copy_id,
+    p_membership_id,
+    p_borrowed_by,
+    NULL,
+    NULL
+);
+
+-- not available
+UPDATE bookcopy
+SET available = 0
+WHERE copy_id = v_copy_id;
+```
+
+END;
+$procedure$;
+}}}
+
+'''Опис:'''
+Процедурата `sp_borrow_book` се користи за позајмување книга според `book_id`.
+Таа најпрво бара слободна копија од дадената книга во табелата `bookcopy`.
+Ако нема достапна копија, се јавува грешка, а ако има, се креира запис во `borrowing` и копијата се означува како недостапна.
+
+'''Употреба:'''
+Оваа процедура ја имплементира логиката за автоматско избирање достапна копија при позајмување книга.
+Корисникот или библиотекарот не мора рачно да избира конкретен `copy_id`, туку системот сам ја наоѓа првата достапна копија.
+Ова ја поедноставува работата на библиотекарот и го намалува ризикот од грешки.
+
+
+
+=== 2.2 sp_reserve_book ===
+
+{{{#!sql
+CREATE OR REPLACE PROCEDURE public.sp_reserve_book(
+IN p_member_id integer,
+IN p_book_id integer
+)
+LANGUAGE plpgsql
+AS $procedure$
+DECLARE
+v_copy_id INT;
+BEGIN
+
+```
+-- check for free copy
+SELECT copy_id
+INTO v_copy_id
+FROM bookcopy
+WHERE book_id = p_book_id
+  AND available = 1
+LIMIT 1;
+
+--if doesnt have copy, make reservation
+IF v_copy_id IS NULL THEN
+
+    INSERT INTO reservation(
+        member_id,
+        book_id,
+        requested_date,
+        expected_date,
+        status,
+        copy_id
+    )
+    VALUES (
+        p_member_id,
+        p_book_id,
+        CURRENT_DATE,
+        CURRENT_DATE + 7,
+        'waiting',
+        NULL
+    );
+
+ELSE
+
+    RAISE NOTICE 'Book is available. No reservation needed.';
+
+END IF;
+```
+
+END;
+$procedure$;
+}}}
+
+'''Опис:'''
+Процедурата `sp_reserve_book` се користи за резервација на книга.
+Најпрво проверува дали постои достапна копија од книгата.
+Ако нема достапна копија, се креира запис во табелата `reservation` со статус `waiting`.
+
+'''Употреба:'''
+Оваа процедура ја имплементира логиката за резервација на книги кои моментално не се достапни.
+Ако книгата има слободна копија, резервација не е потребна и системот прикажува известување.
+Ако книгата нема слободна копија, членот се става на листа на чекање.
+
+
+
+== 3. Trigger функции ==
+
+=== 3.1 fn_create_fine_on_return ===
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION public.fn_create_fine_on_return()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $function$
+DECLARE
+days_late INT;
+total_amount INT;
+fine_per_day INT := 10;
+BEGIN
+
+```
+IF NEW.return_date IS NOT NULL
+   AND NEW.return_date > NEW.due_date THEN
+
+    days_late := NEW.return_date - NEW.due_date;
+    total_amount := days_late * fine_per_day;
+
+    INSERT INTO fine(
+        fine_id,
+        membership_id,
+        borrowing_id,
+        amount,
+        created_date,
+        payment_id,
+        fee_type_id
+    )
+    VALUES (
+        (SELECT COALESCE(MAX(fine_id),0)+1 FROM fine),
+        NEW.membership_id,
+        NEW.borrowing_id,
+        total_amount,
+        CURRENT_DATE,
+        NULL,
+        1
+    );
+
+END IF;
+
+RETURN NEW;
+```
+
+END;
+$function$;
+}}}
+
+'''Опис:'''
+Trigger функцијата `fn_create_fine_on_return` служи за автоматско креирање казна при задоцнето враќање на книга.
+Таа проверува дали `return_date` е поголем од `due_date`.
+Ако книгата е вратена по рокот, се пресметува бројот на задоцнети денови и се внесува нов запис во табелата `fine`.
+
+'''Употреба:'''
+Оваа trigger функција ја имплементира логиката за автоматска наплата на казни.
+Се користи за да не мора библиотекарот рачно да пресметува казна при секое задоцнето враќање.
+Висината на казната се пресметува според бројот на задоцнети денови, при што во функцијата е поставена вредност од 10 по ден.
+
+
+
+=== 3.2 check_membership_status ===
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION public.check_membership_status()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $function$
+BEGIN
+
+```
+--if membership is expiredd
+IF NEW.membership_end_date < CURRENT_DATE THEN
+    NEW.membership_status := 'EXPIRED';
+
+--if doesn't
+ELSE
+    NEW.membership_status := 'ACTIVE';
+END IF;
+
+RETURN NEW;
+```
+
+END;
+$function$;
+}}}
+
+'''Опис:'''
+Trigger функцијата `check_membership_status` се користи за автоматска проверка на статусот на членството.
+Ако датумот `membership_end_date` е помал од тековниот датум, статусот на членот се поставува на `EXPIRED`.
+Во спротивно, статусот се поставува на `ACTIVE`.
+
+'''Употреба:'''
+Оваа trigger функција ја имплементира логиката за автоматско ажурирање на статусот на членството.
+Се користи за системот сам да одреди дали членот има активно или истечено членство.
+На овој начин се избегнува рачно ажурирање на статусот на членовите.
+
+
+
+
+