101 lines
3.0 KiB
Markdown
101 lines
3.0 KiB
Markdown
|
#### создадим небольшую базу данных для хранения документов и смоделируем пример с представлениями, чтобы показать, как работают рекурсивные зависимости и как удалять/восстанавливать объекты.
|
|||
|
|
|
|||
|
|
## 1. Создание структуры базы данных
|
|||
|
|
|
|||
|
|
```sql
|
|||
|
|
-- Таблица для типов документов
|
|||
|
|
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```
|
|||
|
|
Показывает последние версии каждого документа.
|
|||
|
|
|
|||
|
|
```sql
|
|||
|
|
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```
|
|||
|
|
Объединяет метаданные документов с их последними версиями.
|
|||
|
|
|
|||
|
|
```sql
|
|||
|
|
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```
|
|||
|
|
Объединяет информацию о документах с их авторами.
|
|||
|
|
```sql
|
|||
|
|
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;
|
|||
|
|
```
|
|||
|
|
|