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)
107 lines
4.3 KiB
SQL
107 lines
4.3 KiB
SQL
-- Migration: Create database schema for play-life project
|
||
-- This script creates all tables and materialized views needed for the project
|
||
|
||
-- ============================================
|
||
-- Table: projects
|
||
-- ============================================
|
||
CREATE TABLE IF NOT EXISTS projects (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL,
|
||
priority SMALLINT,
|
||
CONSTRAINT unique_project_name UNIQUE (name)
|
||
);
|
||
|
||
-- ============================================
|
||
-- Table: entries
|
||
-- ============================================
|
||
-- This table stores entries with creation dates
|
||
-- Used in weekly_report_mv for grouping by week
|
||
CREATE TABLE IF NOT EXISTS entries (
|
||
id SERIAL PRIMARY KEY,
|
||
text TEXT NOT NULL,
|
||
created_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- Table: nodes
|
||
-- ============================================
|
||
-- This table stores nodes linked to projects and entries
|
||
-- Contains score information used in weekly reports
|
||
CREATE TABLE IF NOT EXISTS nodes (
|
||
id SERIAL PRIMARY KEY,
|
||
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
||
entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
|
||
score NUMERIC(8,4)
|
||
);
|
||
|
||
-- Create index on project_id for better join performance
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_project_id ON nodes(project_id);
|
||
-- Create index on entry_id for better join performance
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_entry_id ON nodes(entry_id);
|
||
|
||
-- ============================================
|
||
-- Table: weekly_goals
|
||
-- ============================================
|
||
-- This table stores weekly goals for projects
|
||
CREATE TABLE IF NOT EXISTS weekly_goals (
|
||
id SERIAL PRIMARY KEY,
|
||
project_id INTEGER NOT NULL REFERENCES 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,
|
||
CONSTRAINT weekly_goals_project_id_goal_year_goal_week_key UNIQUE (project_id, goal_year, goal_week)
|
||
);
|
||
|
||
-- Create index on project_id for better join performance
|
||
CREATE INDEX IF NOT EXISTS idx_weekly_goals_project_id ON weekly_goals(project_id);
|
||
|
||
-- ============================================
|
||
-- Materialized View: weekly_report_mv
|
||
-- ============================================
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS weekly_report_mv AS
|
||
SELECT
|
||
p.id AS project_id,
|
||
agg.report_year,
|
||
agg.report_week,
|
||
-- Используем COALESCE для установки total_score в 0.0000, если нет данных (NULL)
|
||
COALESCE(agg.total_score, 0.0000) AS total_score
|
||
FROM
|
||
projects p
|
||
LEFT JOIN
|
||
(
|
||
-- 1. Предварительная агрегация: суммируем score по неделям
|
||
-- Используем ISOYEAR для корректной работы на границе года
|
||
SELECT
|
||
n.project_id,
|
||
EXTRACT(ISOYEAR FROM e.created_date)::INTEGER AS report_year,
|
||
EXTRACT(WEEK FROM e.created_date)::INTEGER AS report_week,
|
||
SUM(n.score) AS total_score
|
||
FROM
|
||
nodes n
|
||
JOIN
|
||
entries e ON n.entry_id = e.id
|
||
GROUP BY
|
||
1, 2, 3
|
||
) agg
|
||
-- 2. Присоединяем агрегированные данные ко ВСЕМ проектам
|
||
ON p.id = agg.project_id
|
||
ORDER BY
|
||
p.id, agg.report_year, agg.report_week;
|
||
|
||
-- Create index on materialized view for better query performance
|
||
CREATE INDEX IF NOT EXISTS idx_weekly_report_mv_project_year_week
|
||
ON weekly_report_mv(project_id, report_year, report_week);
|
||
|
||
-- ============================================
|
||
-- Comments for documentation
|
||
-- ============================================
|
||
COMMENT ON TABLE projects IS 'Projects table storing project information with priority';
|
||
COMMENT ON TABLE entries IS 'Entries table storing entry creation timestamps';
|
||
COMMENT ON TABLE nodes IS 'Nodes table linking projects, entries and storing scores';
|
||
COMMENT ON TABLE weekly_goals IS 'Weekly goals for projects';
|
||
COMMENT ON MATERIALIZED VIEW weekly_report_mv IS 'Materialized view aggregating weekly scores by project using ISOYEAR for correct week calculations at year boundaries';
|
||
|