Adminator/geograsim
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
geograsim/.dontDeploy/entwurfswerkzeug/_gen_sql_phase3.php
T
Adminator 1e51ef7def Nachtrag: alle bisher untracked Ordner + hängende Änderungen mit-committen 
- Konzept/, didaktik_geografie/, didaktik_simulation/, v2-modules/, v2-platform/
- 12 code-workspace-Files
- STATUS-*.md
- viele M/D/R-Änderungen an bereits getrackten Files
- .gitignore verstärkt: **/.humaninput/, **/secret_keys.txt

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
2026-07-08 02:27:02 +02:00

201 lines
9.8 KiB
PHP
Raw Permalink Blame History

<?php
// Generator für die Phase-3-Migration. Liest lehrplan-synthese-phase3.json und schreibt SQL.
$j = json_decode(file_get_contents(__DIR__ . '/lehrplan-synthese-phase3.json'), true);
function sqlstr($s) {
if ($s === null) return 'NULL';
return "'" . str_replace(["\\", "'"], ["\\\\", "''"], $s) . "'";
}
$out = '';
$out .= "-- =================================================================\n";
$out .= "-- Lehrplan-Erweiterung Phase 3 — ENTWURF (NICHT auto-ausführen)\n";
$out .= "-- Erstellt: 2026-06-08 (Atlas)\n";
$out .= "-- Quelle: .dontDeploy/entwurfswerkzeug/lehrplan-synthese-phase3.json\n";
$out .= "--\n";
$out .= "-- Inhalt:\n";
$out .= "-- 1. Neue Plattform-Kompetenz welternaehrung-konsum-disparitaeten\n";
$out .= "-- 2. Quick-Fixes Phase 1 (4 DE-URLs + 9 AT-Subjects)\n";
$out .= "-- 3. Optional: plattformweites GW-Fach-Update (33 Anker)\n";
$out .= "-- 4. 118 neue Anker (AT 31 + CH 41 + DE 46)\n";
$out .= "-- 5. kompetenz_modules-Verknuepfungen (weltkueche + 3 sek.)\n";
$out .= "--\n";
$out .= "-- Idempotenz: alle INSERTs verwenden ON DUPLICATE KEY UPDATE oder INSERT IGNORE.\n";
$out .= "-- Mehrfach-Ausfuehrung veraendert die DB ab dem zweiten Lauf nicht.\n";
$out .= "--\n";
$out .= "-- ACHTUNG: Vor Punkt 4 muss EINMALIG ein UNIQUE-Index angelegt werden:\n";
$out .= "-- ALTER TABLE lehrplan_anchors\n";
$out .= "-- ADD UNIQUE KEY uniq_anchor_identity (country, reference(120), kompetenz_id);\n";
$out .= "-- Ohne diesen Index sind die folgenden Anker-INSERTs nicht idempotent.\n";
$out .= "-- =================================================================\n\n";
$out .= "START TRANSACTION;\n\n";
// ===== 1. Neue Plattform-Kompetenz =====
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- 1. Neue Plattform-Kompetenz: welternaehrung-konsum-disparitaeten\n";
$out .= "-- -----------------------------------------------------------------\n";
$k = $j['meta']['neu_konsolidierte_plattform_kompetenz'];
$out .= "INSERT INTO kompetenzen (key_slug, title, description, category, age_min, age_max, sort_order)\n";
$out .= "VALUES (\n";
$out .= " " . sqlstr($k['key_slug']) . ",\n";
$out .= " " . sqlstr($k['title']) . ",\n";
$out .= " " . sqlstr($k['description']) . ",\n";
$out .= " " . sqlstr($k['category']) . ",\n";
$out .= " " . (int)$k['age_min'] . ", " . (int)$k['age_max'] . ", 108\n";
$out .= ") ON DUPLICATE KEY UPDATE\n";
$out .= " title = VALUES(title),\n";
$out .= " description = VALUES(description),\n";
$out .= " category = VALUES(category),\n";
$out .= " age_min = VALUES(age_min),\n";
$out .= " age_max = VALUES(age_max);\n\n";
$out .= "-- Variable fuer spaetere Anker-INSERTs:\n";
$out .= "SET @welternaehrung_id := (SELECT id FROM kompetenzen WHERE key_slug = 'welternaehrung-konsum-disparitaeten');\n\n";
// ===== 2. Quick-Fixes =====
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- 2. Quick-Fixes aus Phase 1\n";
$out .= "-- -----------------------------------------------------------------\n";
$qf1 = $j['quick_fix_details']['url_dgfg_alt_zu_vgdh'];
$out .= "-- 2.1: 4 DE-DGfG-URLs auf VGDH-PDF korrigieren (Phase 1: url_dead)\n";
$ids = implode(',', $qf1['betroffene_ids']);
$out .= "UPDATE lehrplan_anchors\n";
$out .= " SET reference_url = " . sqlstr($qf1['neuer_url']) . "\n";
$out .= " WHERE id IN (" . $ids . ")\n";
$out .= " AND reference_url = " . sqlstr($qf1['alter_url']) . ";\n";
$out .= "-- Vorher pruefen: SELECT id, reference_url FROM lehrplan_anchors WHERE id IN (" . $ids . ");\n\n";
$qf2 = $j['quick_fix_details']['subject_politische_bildung'];
$out .= "-- 2.2: 5 AT-Anker Politische Bildung -> Querschnitt-Kennzeichnung\n";
$ids2 = implode(',', $qf2['betroffene_ids']);
$out .= "UPDATE lehrplan_anchors\n";
$out .= " SET subject = " . sqlstr($qf2['neuer_subject']) . "\n";
$out .= " WHERE id IN (" . $ids2 . ")\n";
$out .= " AND subject = 'Politische Bildung';\n\n";
$qf3 = $j['quick_fix_details']['subject_informatische_bildung'];
$out .= "-- 2.3: 4 AT-Anker Informatische Bildung -> Digitale Grundbildung\n";
$ids3 = implode(',', $qf3['betroffene_ids']);
$out .= "UPDATE lehrplan_anchors\n";
$out .= " SET subject = " . sqlstr($qf3['neuer_subject']) . "\n";
$out .= " WHERE id IN (" . $ids3 . ")\n";
$out .= " AND subject = 'Informatische Bildung';\n\n";
// ===== 3. Optionales GW-Update =====
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- 3. OPTIONAL: Plattformweites GW-Fach-Update (33 AT-Anker)\n";
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- Vorher-Vergleich (fuer Review):\n";
$out .= "-- SELECT COUNT(*) AS gw_count FROM lehrplan_anchors WHERE subject='GW' AND country='AT';\n";
$out .= "-- Erwartet: 33 (Stand 2026-06-08)\n";
$out .= "-- Idempotenz: nach erstem Lauf 0 Treffer, da WHERE subject=GW.\n";
$out .= "--\n";
$out .= "-- Auskommentiert — bei Bedarf manuell aktivieren:\n";
$out .= "-- UPDATE lehrplan_anchors\n";
$out .= "-- SET subject = 'Geografie und wirtschaftliche Bildung'\n";
$out .= "-- WHERE subject = 'GW' AND country = 'AT';\n";
$out .= "--\n";
$out .= "-- Nachher-Vergleich:\n";
$out .= "-- SELECT COUNT(*) AS gw_count_after FROM lehrplan_anchors WHERE subject='GW' AND country='AT';\n";
$out .= "-- Erwartet: 0\n";
$out .= "-- SELECT COUNT(*) AS gwb_count FROM lehrplan_anchors\n";
$out .= "-- WHERE subject='Geografie und wirtschaftliche Bildung' AND country='AT';\n";
$out .= "-- Erwartet: 33\n\n";
// ===== 4. Neue Anker =====
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- 4. Neue Lehrplan-Anker (118 Datensaetze: AT 31 + CH 41 + DE 46)\n";
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- Identitaets-Match: (country, reference, kompetenz_id).\n";
$out .= "-- Bei Re-Ausfuehrung wird der Datensatz aktualisiert (URL/Quote/Title).\n\n";
$komp_map = [
'klimawandel' => 1,
'naturgefahren-hochwasser' => 2,
'karten-navigation-alpen' => 3,
'stadtplanung-raum' => 4,
'raeumliche-orientierung-europa' => 41,
'kartenkompetenz-logistik' => 42,
'wirtschaft-logistikverstaendnis' => 43,
'planungs-entscheidungskompetenz' => 44,
'verkehrserziehung-sicherheit' => 45,
'systemisches-denken' => 46,
'energieversorgung-speicherung' => 57,
'erdmechanik-klimazonen' => 80,
'eu-politische-beteiligung' => 93,
'landwirtschaft-klimazonen' => 107,
];
$idx = 0;
$total = count($j['anchors_neu']);
foreach ($j['anchors_neu'] as $a) {
$idx++;
$slug = $a['kompetenz_zuordnung'];
if ($slug === 'welternaehrung-konsum-disparitaeten') {
$komp_expr = '@welternaehrung_id';
} else {
if (!isset($komp_map[$slug])) {
$out .= "-- WARN: unbekannter Kompetenz-Slug $slug fuer " . $a['_source'] . " — uebersprungen\n\n";
continue;
}
$komp_expr = (string)$komp_map[$slug];
}
$out .= "-- [" . $idx . "/" . $total . "] " . $a['_source'] . " -> " . $slug . "\n";
$out .= "INSERT INTO lehrplan_anchors\n";
$out .= " (kompetenz_id, country, anchor_type, region, school_type, grade, subject, title, reference, reference_url, quote, sort_order)\n";
$out .= "VALUES (\n";
$out .= " " . $komp_expr . ",\n";
$out .= " " . sqlstr($a['country']) . ",\n";
$out .= " " . sqlstr($a['anchor_type']) . ",\n";
$out .= " " . ($a['region'] === null ? 'NULL' : sqlstr($a['region'])) . ",\n";
$out .= " " . sqlstr($a['school_type']) . ",\n";
$out .= " " . sqlstr($a['grade']) . ",\n";
$out .= " " . sqlstr($a['subject']) . ",\n";
$out .= " " . sqlstr($a['title']) . ",\n";
$out .= " " . sqlstr($a['reference']) . ",\n";
$out .= " " . sqlstr($a['reference_url']) . ",\n";
$out .= " " . sqlstr($a['quote']) . ",\n";
$out .= " " . (int)$a['sort_order'] . "\n";
$out .= ") ON DUPLICATE KEY UPDATE\n";
$out .= " anchor_type = VALUES(anchor_type),\n";
$out .= " region = VALUES(region),\n";
$out .= " school_type = VALUES(school_type),\n";
$out .= " grade = VALUES(grade),\n";
$out .= " subject = VALUES(subject),\n";
$out .= " title = VALUES(title),\n";
$out .= " reference_url = VALUES(reference_url),\n";
$out .= " quote = VALUES(quote),\n";
$out .= " sort_order = VALUES(sort_order);\n\n";
}
// ===== 5. kompetenz_modules =====
$out .= "-- -----------------------------------------------------------------\n";
$out .= "-- 5. kompetenz_modules-Verknuepfungen\n";
$out .= "-- -----------------------------------------------------------------\n";
foreach ($j['kompetenz_modules_neu'] as $km) {
$slug = $km['kompetenz_id_oder_slug'];
if ($slug === 'welternaehrung-konsum-disparitaeten') {
$kid_expr = '@welternaehrung_id';
} else {
if (!isset($komp_map[$slug])) continue;
$kid_expr = (string)$komp_map[$slug];
}
$out .= "INSERT IGNORE INTO kompetenz_modules (kompetenz_id, module_id, level, strength)\n";
$out .= "VALUES (" . $kid_expr . ", " . sqlstr($km['module_id']) . ", " . (int)$km['level'] . ", " . sqlstr($km['strength']) . ");\n";
}
$out .= "\n";
$out .= "COMMIT;\n\n";
$out .= "-- =================================================================\n";
$out .= "-- Erwartete Statistik nach Migration:\n";
$out .= "-- SELECT country, COUNT(*) FROM lehrplan_anchors GROUP BY country;\n";
$out .= "-- AT 134 (103+31), CH 57 (16+41), DE 64 (18+46), LI 7. Total: 262.\n";
$out .= "-- SELECT COUNT(*) FROM kompetenzen;\n";
$out .= "-- Vorher 14 + 1 neu = 15.\n";
$out .= "-- =================================================================\n";
file_put_contents(__DIR__ . '/../../App/Don_t_Deploy/2026-06-08-lehrplan-erweiterung-phase3.sql', $out);
echo "SQL geschrieben: " . strlen($out) . " Bytes\n";
Reference in New Issue View Git Blame Copy Permalink