postgreSQL_def/readme.md

создадим небольшую базу данных для хранения документов и смоделируем пример с представлениями, чтобы показать, как работают рекурсивные зависимости и как удалять/восстанавливать объекты.

1. Создание структуры базы данных

-- Таблица для типов документов
CREATE TABLE document_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL
);

-- Таблица для метаданных документов
CREATE TABLE documents (
    doc_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    type_id INT REFERENCES document_types(type_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для версий документов
CREATE TABLE document_versions (
    version_id SERIAL PRIMARY KEY,
    doc_id INT REFERENCES documents(doc_id),
    content TEXT NOT NULL,
    version_number INT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для авторов документов
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- Таблица для связи документов с авторами
CREATE TABLE document_authors (
    doc_id INT REFERENCES documents(doc_id),
    author_id INT REFERENCES authors(author_id),
    PRIMARY KEY (doc_id, author_id)
);

2. Создание представлений

Представление 1: latest_document_versions

Показывает последние версии каждого документа.

CREATE VIEW latest_document_versions AS
SELECT 
    dv.doc_id,
    dv.content,
    dv.version_number,
    dv.updated_at
FROM 
    document_versions dv
JOIN (
    SELECT doc_id, MAX(version_number) AS max_version
    FROM document_versions
    GROUP BY doc_id
) latest ON dv.doc_id = latest.doc_id AND dv.version_number = latest.max_version;

Представление 2: document_details

Объединяет метаданные документов с их последними версиями.

CREATE VIEW document_details AS
SELECT 
    d.doc_id,
    d.title,
    dt.type_name,
    ldv.content,
    ldv.updated_at
FROM 
    documents d
JOIN 
    document_types dt ON d.type_id = dt.type_id
LEFT JOIN 
    latest_document_versions ldv ON d.doc_id = ldv.doc_id;

Представление 3: author_documents

Объединяет информацию о документах с их авторами.

CREATE VIEW author_documents AS
SELECT 
    a.name AS author_name,
    a.email,
    dd.doc_id,
    dd.title,
    dd.type_name,
    dd.content,
    dd.updated_at
FROM 
    authors a
JOIN 
    document_authors da ON a.author_id = da.author_id
JOIN 
    document_details dd ON da.doc_id = dd.doc_id;