161 lines
6.7 KiB
MySQL
161 lines
6.7 KiB
MySQL
|
-- Migration 025: Reifegradmodell-Kontext als M:N (Fokusbereich, Stilrichtung, Zielgruppe)
|
|||
|
|
-- + Bootstrap: ein Modell aus allen importierten Skills (Wiki / Migration 023)
|
|||
|
|
-- Datum: 2026-04-27
|
|||
|
|
|
|||
|
|
-- ============================================================================
|
|||
|
|
-- JUNCTION TABLES
|
|||
|
|
-- ============================================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS maturity_model_focus_areas (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
maturity_model_id INT NOT NULL REFERENCES maturity_models(id) ON DELETE CASCADE,
|
|||
|
|
focus_area_id INT NOT NULL REFERENCES focus_areas(id) ON DELETE CASCADE,
|
|||
|
|
is_primary BOOLEAN DEFAULT false,
|
|||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|||
|
|
UNIQUE (maturity_model_id, focus_area_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmfa_model ON maturity_model_focus_areas(maturity_model_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmfa_focus ON maturity_model_focus_areas(focus_area_id);
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS maturity_model_style_directions (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
maturity_model_id INT NOT NULL REFERENCES maturity_models(id) ON DELETE CASCADE,
|
|||
|
|
style_direction_id INT NOT NULL REFERENCES style_directions(id) ON DELETE CASCADE,
|
|||
|
|
is_primary BOOLEAN DEFAULT false,
|
|||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|||
|
|
UNIQUE (maturity_model_id, style_direction_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmsd_model ON maturity_model_style_directions(maturity_model_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmsd_style ON maturity_model_style_directions(style_direction_id);
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS maturity_model_target_groups (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
maturity_model_id INT NOT NULL REFERENCES maturity_models(id) ON DELETE CASCADE,
|
|||
|
|
target_group_id INT NOT NULL REFERENCES target_groups(id) ON DELETE CASCADE,
|
|||
|
|
is_primary BOOLEAN DEFAULT false,
|
|||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|||
|
|
UNIQUE (maturity_model_id, target_group_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmtg_model ON maturity_model_target_groups(maturity_model_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_mmtg_tg ON maturity_model_target_groups(target_group_id);
|
|||
|
|
|
|||
|
|
-- ============================================================================
|
|||
|
|
-- Daten aus 1:1-FKs übernehmen (falls Spalten noch existieren), dann FK-Spalten entfernen
|
|||
|
|
-- ============================================================================
|
|||
|
|
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
IF EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_schema = 'public' AND table_name = 'maturity_models' AND column_name = 'focus_area_id'
|
|||
|
|
) THEN
|
|||
|
|
INSERT INTO maturity_model_focus_areas (maturity_model_id, focus_area_id, is_primary)
|
|||
|
|
SELECT id, focus_area_id, true
|
|||
|
|
FROM maturity_models
|
|||
|
|
WHERE focus_area_id IS NOT NULL
|
|||
|
|
ON CONFLICT (maturity_model_id, focus_area_id) DO NOTHING;
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
IF EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_schema = 'public' AND table_name = 'maturity_models' AND column_name = 'style_direction_id'
|
|||
|
|
) THEN
|
|||
|
|
INSERT INTO maturity_model_style_directions (maturity_model_id, style_direction_id, is_primary)
|
|||
|
|
SELECT id, style_direction_id, true
|
|||
|
|
FROM maturity_models
|
|||
|
|
WHERE style_direction_id IS NOT NULL
|
|||
|
|
ON CONFLICT (maturity_model_id, style_direction_id) DO NOTHING;
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
IF EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_schema = 'public' AND table_name = 'maturity_models' AND column_name = 'target_group_id'
|
|||
|
|
) THEN
|
|||
|
|
INSERT INTO maturity_model_target_groups (maturity_model_id, target_group_id, is_primary)
|
|||
|
|
SELECT id, target_group_id, true
|
|||
|
|
FROM maturity_models
|
|||
|
|
WHERE target_group_id IS NOT NULL
|
|||
|
|
ON CONFLICT (maturity_model_id, target_group_id) DO NOTHING;
|
|||
|
|
END IF;
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
ALTER TABLE maturity_models DROP CONSTRAINT IF EXISTS maturity_models_context_name_unique;
|
|||
|
|
|
|||
|
|
ALTER TABLE maturity_models DROP COLUMN IF EXISTS focus_area_id;
|
|||
|
|
ALTER TABLE maturity_models DROP COLUMN IF EXISTS style_direction_id;
|
|||
|
|
ALTER TABLE maturity_models DROP COLUMN IF EXISTS target_group_id;
|
|||
|
|
|
|||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_maturity_models_wiki_import
|
|||
|
|
ON maturity_models(import_source, import_id)
|
|||
|
|
WHERE import_id IS NOT NULL;
|
|||
|
|
|
|||
|
|
-- ============================================================================
|
|||
|
|
-- Bootstrap: Standard-Modell für alle Skills aus der Wiki-Matrix (Migration 023)
|
|||
|
|
-- ============================================================================
|
|||
|
|
|
|||
|
|
DO $$
|
|||
|
|
DECLARE
|
|||
|
|
mid INT;
|
|||
|
|
n_skills INT;
|
|||
|
|
BEGIN
|
|||
|
|
SELECT COUNT(*) INTO n_skills FROM skills;
|
|||
|
|
IF n_skills = 0 THEN
|
|||
|
|
RAISE NOTICE 'Bootstrap: keine Skills – übersprungen';
|
|||
|
|
RETURN;
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
SELECT id INTO mid FROM maturity_models WHERE import_id = 'faehigkeitsmatrix_karatetrainer' LIMIT 1;
|
|||
|
|
IF mid IS NOT NULL THEN
|
|||
|
|
RAISE NOTICE 'Bootstrap: Modell bereits vorhanden (id=%)', mid;
|
|||
|
|
RETURN;
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
INSERT INTO maturity_models (name, description, level_count, status, import_source, import_id, version)
|
|||
|
|
VALUES (
|
|||
|
|
'Fähigkeitsmatrix (Wiki Import)',
|
|||
|
|
'Alle Fähigkeiten aus der Wiki-Matrix (Karatetrainer). Haupt- und Untergruppen kommen aus skill_main_categories / skill_categories.',
|
|||
|
|
5,
|
|||
|
|
'active',
|
|||
|
|
'wiki_matrix',
|
|||
|
|
'faehigkeitsmatrix_karatetrainer',
|
|||
|
|
'1.0'
|
|||
|
|
)
|
|||
|
|
RETURNING id INTO mid;
|
|||
|
|
|
|||
|
|
INSERT INTO maturity_model_focus_areas (maturity_model_id, focus_area_id, is_primary)
|
|||
|
|
SELECT mid, fa.id, (fa.name = 'Karate')
|
|||
|
|
FROM focus_areas fa
|
|||
|
|
WHERE fa.name IN ('Karate', 'Selbstverteidigung', 'Gewaltschutz')
|
|||
|
|
ON CONFLICT (maturity_model_id, focus_area_id) DO NOTHING;
|
|||
|
|
|
|||
|
|
INSERT INTO model_levels (maturity_model_id, level_number, name, description, sort_order)
|
|||
|
|
VALUES
|
|||
|
|
(mid, 1, 'Einsteiger', NULL, 1),
|
|||
|
|
(mid, 2, 'Grundlagen', NULL, 2),
|
|||
|
|
(mid, 3, 'Aufbau', NULL, 3),
|
|||
|
|
(mid, 4, 'Fortgeschritten', NULL, 4),
|
|||
|
|
(mid, 5, 'Experte', NULL, 5);
|
|||
|
|
|
|||
|
|
INSERT INTO model_skills (maturity_model_id, skill_id, sort_order, relevance)
|
|||
|
|
SELECT
|
|||
|
|
mid,
|
|||
|
|
s.id,
|
|||
|
|
ROW_NUMBER() OVER (
|
|||
|
|
ORDER BY mc.sort_order NULLS LAST, sc.sort_order NULLS LAST, s.name
|
|||
|
|
),
|
|||
|
|
NULL
|
|||
|
|
FROM skills s
|
|||
|
|
LEFT JOIN skill_categories sc ON s.category_id = sc.id
|
|||
|
|
LEFT JOIN skill_main_categories mc ON s.main_category_id = mc.id;
|
|||
|
|
|
|||
|
|
INSERT INTO model_skill_levels (maturity_model_id, skill_id, level_number, description, observable_criteria)
|
|||
|
|
SELECT mid, sld.skill_id, sld.level, sld.description, NULL
|
|||
|
|
FROM skill_level_definitions sld
|
|||
|
|
ON CONFLICT (maturity_model_id, skill_id, level_number) DO NOTHING;
|
|||
|
|
|
|||
|
|
RAISE NOTICE 'Bootstrap: Reifegradmodell id=% angelegt (% Skills)', mid, n_skills;
|
|||
|
|
END $$;
|