39 lines
2.1 KiB
MySQL
39 lines
2.1 KiB
MySQL
|
-- Migration 032: Progressionsgraph zwischen Übungen (Übung → Übung), getrennt von Varianten-Progression (014).
|
||
|
|
-- CURR-002 (1): gerichtete Kanten mit optionalem Graph-Kontainer; FK auf exercises; MVP edge_type erweiterbar.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS exercise_progression_graphs (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
visibility VARCHAR(50) NOT NULL DEFAULT 'private'
|
||
|
|
CHECK (visibility IN ('private', 'club', 'official')),
|
||
|
|
club_id INT REFERENCES clubs(id) ON DELETE SET NULL,
|
||
|
|
created_by INT REFERENCES profiles(id) ON DELETE SET NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_progression_graphs_club ON exercise_progression_graphs(club_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_progression_graphs_creator ON exercise_progression_graphs(created_by);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_progression_graphs_visibility ON exercise_progression_graphs(visibility);
|
||
|
|
|
||
|
|
DROP TRIGGER IF EXISTS exercise_progression_graphs_update ON exercise_progression_graphs;
|
||
|
|
CREATE TRIGGER exercise_progression_graphs_update
|
||
|
|
BEFORE UPDATE ON exercise_progression_graphs
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS exercise_progression_edges (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
graph_id INT NOT NULL REFERENCES exercise_progression_graphs(id) ON DELETE CASCADE,
|
||
|
|
from_exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
|
||
|
|
to_exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
|
||
|
|
edge_type VARCHAR(50) NOT NULL DEFAULT 'next_exercise',
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
CHECK (from_exercise_id <> to_exercise_id),
|
||
|
|
UNIQUE (graph_id, from_exercise_id, to_exercise_id, edge_type)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_progression_edges_graph ON exercise_progression_edges(graph_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_progression_edges_from ON exercise_progression_edges(from_exercise_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_progression_edges_to ON exercise_progression_edges(to_exercise_id);
|