8d79a8f692
- Заменен EXTRACT(YEAR) на EXTRACT(ISOYEAR) в materialized view для корректной работы на границе года - Обновлена миграция 001_create_schema.sql для использования ISOYEAR - Создана миграция 006_fix_weekly_report_mv_structure.sql для исправления структуры view (LEFT JOIN) - Добавлен endpoint /admin/recreate-mv для пересоздания materialized view - Обновлена документация миграций в README.md - Обновлены зависимости Go (go.mod, go.sum)
109 lines
4.3 KiB
Markdown
109 lines
4.3 KiB
Markdown
# Database Migrations
|
||
|
||
Этот каталог содержит SQL миграции для создания структуры базы данных проекта play-life.
|
||
|
||
## Использование
|
||
|
||
### Создание базы данных с нуля
|
||
|
||
Выполните миграцию для создания всех таблиц и представлений:
|
||
|
||
```bash
|
||
psql -U your_user -d your_database -f 001_create_schema.sql
|
||
```
|
||
|
||
Или через docker-compose:
|
||
|
||
```bash
|
||
docker-compose exec db psql -U playeng -d playeng -f /migrations/001_create_schema.sql
|
||
```
|
||
|
||
## Структура базы данных
|
||
|
||
### Таблицы
|
||
|
||
1. **projects** - Проекты
|
||
- `id` (SERIAL PRIMARY KEY)
|
||
- `name` (VARCHAR(255) NOT NULL, UNIQUE)
|
||
- `priority` (SMALLINT)
|
||
|
||
2. **entries** - Записи с текстом и датами создания
|
||
- `id` (SERIAL PRIMARY KEY)
|
||
- `text` (TEXT NOT NULL)
|
||
- `created_date` (TIMESTAMP WITH TIME ZONE NOT NULL, DEFAULT CURRENT_TIMESTAMP)
|
||
|
||
3. **nodes** - Узлы, связывающие проекты и записи
|
||
- `id` (SERIAL PRIMARY KEY)
|
||
- `project_id` (INTEGER NOT NULL, FK -> projects.id ON DELETE CASCADE)
|
||
- `entry_id` (INTEGER NOT NULL, FK -> entries.id ON DELETE CASCADE)
|
||
- `score` (NUMERIC(8,4))
|
||
|
||
4. **weekly_goals** - Недельные цели для проектов
|
||
- `id` (SERIAL PRIMARY KEY)
|
||
- `project_id` (INTEGER NOT NULL, FK -> projects.id ON DELETE CASCADE)
|
||
- `goal_year` (INTEGER NOT NULL)
|
||
- `goal_week` (INTEGER NOT NULL)
|
||
- `min_goal_score` (NUMERIC(10,4) NOT NULL, DEFAULT 0)
|
||
- `max_goal_score` (NUMERIC(10,4))
|
||
- `actual_score` (NUMERIC(10,4), DEFAULT 0)
|
||
- `priority` (SMALLINT)
|
||
- UNIQUE CONSTRAINT: `(project_id, goal_year, goal_week)`
|
||
|
||
### Materialized View
|
||
|
||
- **weekly_report_mv** - Агрегированные данные по неделям для каждого проекта
|
||
- `project_id` (INTEGER)
|
||
- `report_year` (INTEGER)
|
||
- `report_week` (INTEGER)
|
||
- `total_score` (NUMERIC)
|
||
|
||
## Миграции
|
||
|
||
### Порядок применения миграций
|
||
|
||
1. **001_create_schema.sql** - Создание базовой структуры (таблицы, индексы, materialized view)
|
||
2. **002_add_dictionaries.sql** - Добавление таблиц для словарей
|
||
3. **003_remove_words_unique_constraint.sql** - Удаление уникального ограничения на words.name
|
||
4. **004_add_config_dictionaries.sql** - Добавление связи между конфигурациями и словарями
|
||
5. **005_fix_weekly_report_mv.sql** - Исправление использования ISOYEAR вместо YEAR для корректной работы на границе года
|
||
6. **006_fix_weekly_report_mv_structure.sql** - Исправление структуры view (добавление LEFT JOIN для включения всех проектов)
|
||
|
||
### Применение миграций
|
||
|
||
Для существующей базы данных применяйте миграции последовательно:
|
||
|
||
```bash
|
||
psql -U playeng -d playeng -f migrations/005_fix_weekly_report_mv.sql
|
||
psql -U playeng -d playeng -f migrations/006_fix_weekly_report_mv_structure.sql
|
||
```
|
||
|
||
Или через docker-compose:
|
||
|
||
```bash
|
||
docker-compose exec db psql -U playeng -d playeng -f /migrations/005_fix_weekly_report_mv.sql
|
||
docker-compose exec db psql -U playeng -d playeng -f /migrations/006_fix_weekly_report_mv_structure.sql
|
||
```
|
||
|
||
## Обновление Materialized View
|
||
|
||
После изменения данных в таблицах `nodes` или `entries`, необходимо обновить materialized view:
|
||
|
||
```sql
|
||
REFRESH MATERIALIZED VIEW weekly_report_mv;
|
||
```
|
||
|
||
## Связи между таблицами
|
||
|
||
- `nodes.project_id` → `projects.id` (ON DELETE CASCADE)
|
||
- `nodes.entry_id` → `entries.id` (ON DELETE CASCADE)
|
||
- `weekly_goals.project_id` → `projects.id` (ON DELETE CASCADE)
|
||
|
||
## Индексы
|
||
|
||
Созданы индексы для оптимизации запросов:
|
||
- `idx_nodes_project_id` на `nodes(project_id)`
|
||
- `idx_nodes_entry_id` на `nodes(entry_id)`
|
||
- `idx_weekly_goals_project_id` на `weekly_goals(project_id)`
|
||
- `idx_weekly_report_mv_project_year_week` на `weekly_report_mv(project_id, report_year, report_week)`
|
||
|