48 lines
1.3 KiB
MySQL
48 lines
1.3 KiB
MySQL
|
-- =============================================================================
|
|||
|
|
-- DC_CATALOG_GEN_ALTER.sql
|
|||
|
|
-- Ergänzt dc_question_catalogs um Spalten für asynchrone Katalog-Generierung.
|
|||
|
|
-- Idempotent: Fehler "Spalte existiert bereits" wird ignoriert.
|
|||
|
|
-- =============================================================================
|
|||
|
|
|
|||
|
|
BEGIN
|
|||
|
|
EXECUTE IMMEDIATE '
|
|||
|
|
ALTER TABLE dc_question_catalogs ADD (
|
|||
|
|
generation_status VARCHAR2(20) DEFAULT ''READY'',
|
|||
|
|
generation_error VARCHAR2(4000)
|
|||
|
|
)
|
|||
|
|
';
|
|||
|
|
DBMS_OUTPUT.PUT_LINE('Spalten generation_status/generation_error angelegt.');
|
|||
|
|
EXCEPTION
|
|||
|
|
WHEN OTHERS THEN
|
|||
|
|
IF SQLCODE = -1430 THEN
|
|||
|
|
DBMS_OUTPUT.PUT_LINE('Spalten existieren bereits – kein Änderungsbedarf.');
|
|||
|
|
ELSE
|
|||
|
|
RAISE;
|
|||
|
|
END IF;
|
|||
|
|
END;
|
|||
|
|
/
|
|||
|
|
|
|||
|
|
-- Bestehende Zeilen bekommen Status READY (Kataloge wurden manuell erstellt)
|
|||
|
|
UPDATE dc_question_catalogs
|
|||
|
|
SET generation_status = 'READY'
|
|||
|
|
WHERE generation_status IS NULL;
|
|||
|
|
|
|||
|
|
COMMIT;
|
|||
|
|
|
|||
|
|
-- Constraint nachträglich hinzufügen (idempotent)
|
|||
|
|
BEGIN
|
|||
|
|
EXECUTE IMMEDIATE '
|
|||
|
|
ALTER TABLE dc_question_catalogs
|
|||
|
|
ADD CONSTRAINT chk_cat_gen_status
|
|||
|
|
CHECK (generation_status IN (''GENERATING'', ''READY'', ''ERROR''))
|
|||
|
|
';
|
|||
|
|
EXCEPTION
|
|||
|
|
WHEN OTHERS THEN
|
|||
|
|
IF SQLCODE IN (-2264, -2261) THEN NULL; -- Constraint existiert bereits
|
|||
|
|
ELSE RAISE;
|
|||
|
|
END IF;
|
|||
|
|
END;
|
|||
|
|
/
|
|||
|
|
|
|||
|
|
COMMIT;
|