database/init/02_book_catalog.sql - develop - miragecatv/library-api-csharp

database/init/02_book_catalog.sql
286 строк8 KB
Nikita P
Initial BookDB project
22 июл 2026, 21:33
22 июл 2026, 21:331c0f3fe
-- BookDB
-- Представление каталога и триггеры INSERT / UPDATE / DELETE.

CREATE OR REPLACE VIEW book_catalog AS
SELECT
    e.id AS edition_id,
    b.title,
    authors.names AS authors,
    b.description,
    b.original_year,
    p.name AS publisher,
    s.name AS series,
    genres.names AS genres,
    e.isbn,
    e.publication_year,
    e.page_count,
    e.binding_type,
    e.condition,
    e.reading_status,
    e.notes
FROM edition e
JOIN book b ON b.id = e.book_id
LEFT JOIN publisher p ON p.id = e.publisher_id
LEFT JOIN series s ON s.id = e.series_id
LEFT JOIN LATERAL (
    SELECT string_agg(a.full_name, '; ' ORDER BY a.full_name) AS names
    FROM book_author ba
    JOIN author a ON a.id = ba.author_id
    WHERE ba.book_id = b.id
) authors ON TRUE
LEFT JOIN LATERAL (
    SELECT string_agg(g.name, '; ' ORDER BY g.name) AS names
    FROM book_genre bg
    JOIN genre g ON g.id = bg.genre_id
    WHERE bg.book_id = b.id
) genres ON TRUE;

COMMENT ON VIEW book_catalog IS
'Виртуальный каталог для просмотра, добавления, изменения и удаления книг.';

CREATE OR REPLACE FUNCTION book_catalog_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_book_id BIGINT;
    v_author_id BIGINT;
    v_genre_id BIGINT;
    v_publisher_id BIGINT;
    v_series_id BIGINT;
    v_value TEXT;
BEGIN
    IF NEW.title IS NULL OR btrim(NEW.title) = '' THEN
        RAISE EXCEPTION 'Название книги обязательно';
    END IF;

    IF NEW.authors IS NULL OR btrim(NEW.authors) = '' THEN
        RAISE EXCEPTION 'Нужно указать хотя бы одного автора';
    END IF;

    INSERT INTO book (title, description, original_year)
    VALUES (btrim(NEW.title), NULLIF(btrim(NEW.description), ''), NEW.original_year)
    RETURNING id INTO v_book_id;

    FOR v_value IN
        SELECT btrim(value)
        FROM regexp_split_to_table(NEW.authors, '\s*;\s*') AS value
        WHERE btrim(value) <> ''
    LOOP
        INSERT INTO author (full_name)
        VALUES (v_value)
        ON CONFLICT (full_name)
        DO UPDATE SET full_name = EXCLUDED.full_name
        RETURNING id INTO v_author_id;

        INSERT INTO book_author (book_id, author_id)
        VALUES (v_book_id, v_author_id)
        ON CONFLICT DO NOTHING;
    END LOOP;

    IF NEW.genres IS NOT NULL AND btrim(NEW.genres) <> '' THEN
        FOR v_value IN
            SELECT btrim(value)
            FROM regexp_split_to_table(NEW.genres, '\s*;\s*') AS value
            WHERE btrim(value) <> ''
        LOOP
            INSERT INTO genre (name)
            VALUES (v_value)
            ON CONFLICT (name)
            DO UPDATE SET name = EXCLUDED.name
            RETURNING id INTO v_genre_id;

            INSERT INTO book_genre (book_id, genre_id)
            VALUES (v_book_id, v_genre_id)
            ON CONFLICT DO NOTHING;
        END LOOP;
    END IF;

    IF NEW.publisher IS NOT NULL AND btrim(NEW.publisher) <> '' THEN
        INSERT INTO publisher (name)
        VALUES (btrim(NEW.publisher))
        ON CONFLICT (name)
        DO UPDATE SET name = EXCLUDED.name
        RETURNING id INTO v_publisher_id;
    END IF;

    IF NEW.series IS NOT NULL AND btrim(NEW.series) <> '' THEN
        INSERT INTO series (name)
        VALUES (btrim(NEW.series))
        ON CONFLICT (name)
        DO UPDATE SET name = EXCLUDED.name
        RETURNING id INTO v_series_id;
    END IF;

    INSERT INTO edition (
        book_id, publisher_id, series_id, isbn, publication_year,
        page_count, binding_type, condition, reading_status, notes
    )
    VALUES (
        v_book_id,
        v_publisher_id,
        v_series_id,
        NULLIF(btrim(NEW.isbn), ''),
        NEW.publication_year,
        NEW.page_count,
        NULLIF(btrim(NEW.binding_type), ''),
        NULLIF(btrim(NEW.condition), ''),
        NULLIF(btrim(NEW.reading_status), ''),
        NULLIF(btrim(NEW.notes), '')
    )
    RETURNING id INTO NEW.edition_id;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION book_catalog_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_book_id BIGINT;
    v_author_id BIGINT;
    v_genre_id BIGINT;
    v_publisher_id BIGINT;
    v_series_id BIGINT;
    v_value TEXT;
BEGIN
    IF NEW.edition_id <> OLD.edition_id THEN
        RAISE EXCEPTION 'edition_id изменять нельзя';
    END IF;

    IF NEW.title IS NULL OR btrim(NEW.title) = '' THEN
        RAISE EXCEPTION 'Название книги обязательно';
    END IF;

    IF NEW.authors IS NULL OR btrim(NEW.authors) = '' THEN
        RAISE EXCEPTION 'Нужно указать хотя бы одного автора';
    END IF;

    SELECT book_id
    INTO v_book_id
    FROM edition
    WHERE id = OLD.edition_id;

    IF v_book_id IS NULL THEN
        RAISE EXCEPTION 'Издание с ID % не найдено', OLD.edition_id;
    END IF;

    UPDATE book
    SET title = btrim(NEW.title),
        description = NULLIF(btrim(NEW.description), ''),
        original_year = NEW.original_year
    WHERE id = v_book_id;

    DELETE FROM book_author WHERE book_id = v_book_id;

    FOR v_value IN
        SELECT btrim(value)
        FROM regexp_split_to_table(NEW.authors, '\s*;\s*') AS value
        WHERE btrim(value) <> ''
    LOOP
        INSERT INTO author (full_name)
        VALUES (v_value)
        ON CONFLICT (full_name)
        DO UPDATE SET full_name = EXCLUDED.full_name
        RETURNING id INTO v_author_id;

        INSERT INTO book_author (book_id, author_id)
        VALUES (v_book_id, v_author_id)
        ON CONFLICT DO NOTHING;
    END LOOP;

    DELETE FROM book_genre WHERE book_id = v_book_id;

    IF NEW.genres IS NOT NULL AND btrim(NEW.genres) <> '' THEN
        FOR v_value IN
            SELECT btrim(value)
            FROM regexp_split_to_table(NEW.genres, '\s*;\s*') AS value
            WHERE btrim(value) <> ''
        LOOP
            INSERT INTO genre (name)
            VALUES (v_value)
            ON CONFLICT (name)
            DO UPDATE SET name = EXCLUDED.name
            RETURNING id INTO v_genre_id;

            INSERT INTO book_genre (book_id, genre_id)
            VALUES (v_book_id, v_genre_id)
            ON CONFLICT DO NOTHING;
        END LOOP;
    END IF;

    v_publisher_id := NULL;
    IF NEW.publisher IS NOT NULL AND btrim(NEW.publisher) <> '' THEN
        INSERT INTO publisher (name)
        VALUES (btrim(NEW.publisher))
        ON CONFLICT (name)
        DO UPDATE SET name = EXCLUDED.name
        RETURNING id INTO v_publisher_id;
    END IF;

    v_series_id := NULL;
    IF NEW.series IS NOT NULL AND btrim(NEW.series) <> '' THEN
        INSERT INTO series (name)
        VALUES (btrim(NEW.series))
        ON CONFLICT (name)
        DO UPDATE SET name = EXCLUDED.name
        RETURNING id INTO v_series_id;
    END IF;

    UPDATE edition
    SET publisher_id = v_publisher_id,
        series_id = v_series_id,
        isbn = NULLIF(btrim(NEW.isbn), ''),
        publication_year = NEW.publication_year,
        page_count = NEW.page_count,
        binding_type = NULLIF(btrim(NEW.binding_type), ''),
        condition = NULLIF(btrim(NEW.condition), ''),
        reading_status = NULLIF(btrim(NEW.reading_status), ''),
        notes = NULLIF(btrim(NEW.notes), '')
    WHERE id = OLD.edition_id;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION book_catalog_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_book_id BIGINT;
BEGIN
    SELECT book_id
    INTO v_book_id
    FROM edition
    WHERE id = OLD.edition_id;

    DELETE FROM edition
    WHERE id = OLD.edition_id;

    IF v_book_id IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM edition WHERE book_id = v_book_id) THEN
        DELETE FROM book WHERE id = v_book_id;
    END IF;

    RETURN OLD;
END;
$$;

DROP TRIGGER IF EXISTS trg_book_catalog_insert ON book_catalog;
DROP TRIGGER IF EXISTS trg_book_catalog_update ON book_catalog;
DROP TRIGGER IF EXISTS trg_book_catalog_delete ON book_catalog;

CREATE TRIGGER trg_book_catalog_insert
INSTEAD OF INSERT ON book_catalog
FOR EACH ROW EXECUTE FUNCTION book_catalog_insert();

CREATE TRIGGER trg_book_catalog_update
INSTEAD OF UPDATE ON book_catalog
FOR EACH ROW EXECUTE FUNCTION book_catalog_update();

CREATE TRIGGER trg_book_catalog_delete
INSTEAD OF DELETE ON book_catalog
FOR EACH ROW EXECUTE FUNCTION book_catalog_delete();