89 lines
3.6 KiB
PL/PgSQL
89 lines
3.6 KiB
PL/PgSQL
-- PostgreSQL-only migration script.
|
|
-- Dieses Projekt-Backend nutzt Schemas, JSONB und PostgreSQL-Datentypen.
|
|
-- Nicht auf MariaDB/MySQL ausführen.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS falukant_data.relationship_state (
|
|
id serial PRIMARY KEY,
|
|
relationship_id integer NOT NULL UNIQUE,
|
|
marriage_satisfaction integer NOT NULL DEFAULT 55 CHECK (marriage_satisfaction >= 0 AND marriage_satisfaction <= 100),
|
|
marriage_public_stability integer NOT NULL DEFAULT 55 CHECK (marriage_public_stability >= 0 AND marriage_public_stability <= 100),
|
|
lover_role text NULL CHECK (lover_role IN ('secret_affair', 'lover', 'mistress_or_favorite')),
|
|
affection integer NOT NULL DEFAULT 50 CHECK (affection >= 0 AND affection <= 100),
|
|
visibility integer NOT NULL DEFAULT 15 CHECK (visibility >= 0 AND visibility <= 100),
|
|
discretion integer NOT NULL DEFAULT 50 CHECK (discretion >= 0 AND discretion <= 100),
|
|
maintenance_level integer NOT NULL DEFAULT 50 CHECK (maintenance_level >= 0 AND maintenance_level <= 100),
|
|
status_fit integer NOT NULL DEFAULT 0 CHECK (status_fit >= -2 AND status_fit <= 2),
|
|
monthly_base_cost integer NOT NULL DEFAULT 0 CHECK (monthly_base_cost >= 0),
|
|
months_underfunded integer NOT NULL DEFAULT 0 CHECK (months_underfunded >= 0),
|
|
active boolean NOT NULL DEFAULT true,
|
|
acknowledged boolean NOT NULL DEFAULT false,
|
|
exclusive_flag boolean NOT NULL DEFAULT false,
|
|
last_monthly_processed_at timestamp with time zone NULL,
|
|
last_daily_processed_at timestamp with time zone NULL,
|
|
notes_json jsonb NULL,
|
|
flags_json jsonb NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT relationship_state_relationship_fk
|
|
FOREIGN KEY (relationship_id)
|
|
REFERENCES falukant_data.relationship(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS relationship_state_active_idx
|
|
ON falukant_data.relationship_state (active);
|
|
|
|
CREATE INDEX IF NOT EXISTS relationship_state_lover_role_idx
|
|
ON falukant_data.relationship_state (lover_role);
|
|
|
|
ALTER TABLE IF EXISTS falukant_data.child_relation
|
|
ADD COLUMN IF NOT EXISTS legitimacy text NOT NULL DEFAULT 'legitimate';
|
|
|
|
ALTER TABLE IF EXISTS falukant_data.child_relation
|
|
ADD COLUMN IF NOT EXISTS birth_context text NOT NULL DEFAULT 'marriage';
|
|
|
|
ALTER TABLE IF EXISTS falukant_data.child_relation
|
|
ADD COLUMN IF NOT EXISTS public_known boolean NOT NULL DEFAULT false;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'child_relation_legitimacy_chk'
|
|
) THEN
|
|
ALTER TABLE falukant_data.child_relation
|
|
ADD CONSTRAINT child_relation_legitimacy_chk
|
|
CHECK (legitimacy IN ('legitimate', 'acknowledged_bastard', 'hidden_bastard'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'child_relation_birth_context_chk'
|
|
) THEN
|
|
ALTER TABLE falukant_data.child_relation
|
|
ADD CONSTRAINT child_relation_birth_context_chk
|
|
CHECK (birth_context IN ('marriage', 'lover'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
COMMIT;
|
|
|
|
-- Rollback separat bei Bedarf:
|
|
-- BEGIN;
|
|
-- ALTER TABLE falukant_data.child_relation DROP CONSTRAINT IF EXISTS child_relation_birth_context_chk;
|
|
-- ALTER TABLE falukant_data.child_relation DROP CONSTRAINT IF EXISTS child_relation_legitimacy_chk;
|
|
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS public_known;
|
|
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS birth_context;
|
|
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS legitimacy;
|
|
-- DROP TABLE IF EXISTS falukant_data.relationship_state;
|
|
-- COMMIT;
|