82 lines
3.6 KiB
SQL
82 lines
3.6 KiB
SQL
-- ---------------------------------------------------------------------------
|
||
-- HANDGESCHRIEBEN. Prisma erzeugt hier zusaetzlich Loeschbefehle fuer die
|
||
-- zusammengesetzten Fremdschluessel (Mandantengrenze), die Trigramm-Indizes
|
||
-- (Archivsuche), die partiellen eindeutigen Indizes und die Trigger auf
|
||
-- veroeffentlichten Versionen: es kennt sie nicht, weil sie sich im Schema nicht
|
||
-- ausdruecken lassen. Diese Loeschbefehle sind bewusst gestrichen.
|
||
--
|
||
-- tests/datenbankregeln.test.ts prueft, dass alle diese Objekte vorhanden sind –
|
||
-- ein versehentlich stehengelassener Loeschbefehl faellt dort sofort auf.
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
-- Anhaenge und Fotos an Schritten (#032).
|
||
--
|
||
-- Die Datei liegt im Dateisystem unter ANHAENGE_PFAD, hier stehen die
|
||
-- Metadaten. Gegen bytea spricht die Sicherung: ein pg_dump mit Fotos ist nach
|
||
-- einem Jahr keines, das jemand einspielt.
|
||
|
||
CREATE TABLE "Anhang" (
|
||
"id" TEXT NOT NULL,
|
||
"schrittergebnisId" TEXT NOT NULL,
|
||
"laufId" TEXT NOT NULL,
|
||
"kundeId" TEXT NOT NULL,
|
||
"dateiname" TEXT NOT NULL,
|
||
"medientyp" TEXT NOT NULL,
|
||
"groesse" INTEGER NOT NULL,
|
||
"pruefsumme" TEXT NOT NULL,
|
||
"erstelltVonId" TEXT,
|
||
"erstelltAm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT "Anhang_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
CREATE INDEX "Anhang_schrittergebnisId_idx" ON "Anhang"("schrittergebnisId");
|
||
CREATE INDEX "Anhang_laufId_idx" ON "Anhang"("laufId");
|
||
CREATE INDEX "Anhang_kundeId_idx" ON "Anhang"("kundeId");
|
||
|
||
-- Schluesselkandidaten fuer die Mandantengrenze unten. Lauf hatte bisher keinen –
|
||
-- die vier bestehenden zusammengesetzten Fremdschluessel zeigen auf Geraet,
|
||
-- Mitarbeiter und Abteilung, nicht auf Lauf.
|
||
CREATE UNIQUE INDEX "Schrittergebnis_id_laufId_key" ON "Schrittergebnis"("id", "laufId");
|
||
CREATE UNIQUE INDEX "Lauf_id_kundeId_key" ON "Lauf"("id", "kundeId");
|
||
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_schrittergebnisId_fkey"
|
||
FOREIGN KEY ("schrittergebnisId") REFERENCES "Schrittergebnis"("id")
|
||
ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_laufId_fkey"
|
||
FOREIGN KEY ("laufId") REFERENCES "Lauf"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_kundeId_fkey"
|
||
FOREIGN KEY ("kundeId") REFERENCES "Kunde"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_erstelltVonId_fkey"
|
||
FOREIGN KEY ("erstelltVonId") REFERENCES "Benutzer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- ---- Mandantengrenze und Zusammenhalt --------------------------------------
|
||
--
|
||
-- Dieselbe Bauart wie bei Lauf und Geraet: ein Anhang kann strukturell nicht zu
|
||
-- einem Schrittergebnis eines anderen Laufs und nicht zu einem anderen Kunden
|
||
-- als sein Lauf gehoeren.
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_ergebnis_gleicher_lauf"
|
||
FOREIGN KEY ("schrittergebnisId", "laufId") REFERENCES "Schrittergebnis" ("id", "laufId")
|
||
ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_lauf_gleicher_kunde"
|
||
FOREIGN KEY ("laufId", "kundeId") REFERENCES "Lauf" ("id", "kundeId")
|
||
ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- Eine Groesse von 0 waere eine leere Datei, und eine negative gibt es nicht.
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_groesse_positiv" CHECK ("groesse" > 0);
|
||
|
||
-- SHA-256 als Hex: 64 Zeichen, nur Ziffern und a-f. Eine Pruefsumme, die keine
|
||
-- ist, wuerde nach einer Ruecksicherung nichts beantworten.
|
||
ALTER TABLE "Anhang"
|
||
ADD CONSTRAINT "Anhang_pruefsumme_form" CHECK ("pruefsumme" ~ '^[0-9a-f]{64}$');
|