Lars
1b7a0405e9
- Integrated exercise progression graphs into the backend and frontend, allowing users to visualize relationships between exercises. - Updated API endpoints for managing exercise progression graphs and edges, enhancing the exercise management capabilities. - Added a new tab in the ExercisesListPage for displaying progression graphs and included a panel in the ExerciseFormPage for editing. - Incremented application version to 0.8.6 and updated changelog to reflect new features and improvements.
39 lines
2.1 KiB
SQL
39 lines
2.1 KiB
SQL
-- 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);
|