Andmebaasioperatsiooni lepingu ja PostgreSQL funktsiooni seos

See leht selgitab interaktiivselt, kuidas andmebaasioperatsiooni kirjeldus (leping) on aluseks vastava PostgreSQL andmebaasi funktsiooni loomisel. Klõpsa funktsiooni koodi osadel, et näha, millised lepingu osad neile vastavad.

Funktsioon

CREATE OR REPLACE FUNCTION f_muuda_tootaja_aktuaalseks(
    p_meiliaadress Tootaja.meiliaadress%TYPE /* Töötaja meiliaadress, keda muuta aktuaalseks */
)
RETURNS Tootaja.tootaja_id%TYPE /* Tagastab muudetud töötaja ID */
LANGUAGE sql
SECURITY DEFINER /* Käivitatakse looja õigustes */
SET search_path = pg_catalog, public, pg_temp /* Turvaline otsingutee */
BEGIN ATOMIC
    UPDATE public.Tootaja AS o /* Kasutame tabeli aliast 'o' vastavalt operatsiooni kirjeldusele */
    SET on_aktuaalne = TRUE
    WHERE o.meiliaadress = p_meiliaadress /* Eeltingimus: eksisteerib sellise meiliaadressiga töötaja */
      AND o.on_aktuaalne = FALSE /* Eeltingimus: töötaja on mitteaktiivne */
      AND EXISTS ( /* Eeltingimus: leidub vastav Keeleoskus rida */
          SELECT 1
          FROM public.Keeleoskus AS ko
          /* JOIN public.Keel AS k ON ko.keel_kood = k.keel_kood -- Keel tabeli JOIN pole rangelt vajalik, kuna kontrollime koodi otse */
          WHERE ko.tootaja_id = o.tootaja_id /* Seos töötajaga 'o' */
            AND ko.keel_kood = 'est' /* Keel peab olema 'est' */
            AND ko.keeleoskuse_tase >= 8 /* Keeleoskuse tase vähemalt 8 */
	  FOR UPDATE /* Et keegi ei saaks samal ajal keeleoskust muuta või kustutada */
      )
    RETURNING o.tootaja_id; /* Järeltingimus: tagastab muudetud rea ID */
END; /* SQL-standardile vastav keha lõpp */

/* Kommentaari lisamine funktsioonile */
COMMENT ON FUNCTION f_muuda_tootaja_aktuaalseks(varchar) IS
'OP9: Muudab töötaja aktuaalseks (on_aktuaalne=TRUE) antud meiliaadressi (p_meiliaadress) alusel.
Eeltingimused: Töötaja peab eksisteerima, olema mitteaktiivne (on_aktuaalne=FALSE) ning tal peab olema keeleoskus eesti keeles (keel_kood=''est'') tasemel vähemalt 8.
Tagastab õnnestumisel muudetud töötaja ID.';

Operatsiooni leping

OP9 Muuda töötaja aktuaalseks (
p_meiliaadress
)

Eeltingimused:

Töötaja eksemplar o (millel on meiliaadress=p_meiliaadress ja on_aktuaalne=FALSE) on registreeritud
Leidub Keeleoskus eksemplar ko, mis on seotud o ja mille puhul (keeleoskuse_tase>=8) ja mis on seotud Keel eksemplariga k (millel on kood='est')

Järeltingimused:

--Väärtusta atribuute

o.on_aktuaalne:= TRUE

Kasutus kasutusjuhtude poolt: Muuda töötaja aktuaalseks

Kuva/peida andmebaasi skeemi fragmendid

Allpool on näidatud domeenide, tabelite ja indeksite loomise laused. Esile on tõstetud operatsioonis otseselt kasutatavad tabelid: Tootaja, Keeleoskus ja Keel.

CREATE DOMAIN d_nimetus VARCHAR(50) NOT NULL
CONSTRAINT chk_nimetus_pole_tyhi CHECK (VALUE!~'^[[:space:]]*$');

CREATE TABLE Keel
(
	keel_kood char(3)	 NOT NULL,
	nimetus d_nimetus,
	on_aktiivne boolean NOT NULL   DEFAULT TRUE,
	CONSTRAINT PK_Keel PRIMARY KEY (keel_kood),
	CONSTRAINT AK_Keel_nimetus UNIQUE (nimetus),
	CONSTRAINT CHK_Keel_keel_kood CHECK (keel_kood~'^[a-z]{3}$')
)
;

CREATE TABLE Amet
(
	amet_kood smallint NOT NULL,
	nimetus d_nimetus,
	on_aktiivne boolean NOT NULL   DEFAULT TRUE,
	max_tootunde_nadalas smallint NOT NULL   DEFAULT 40,
	min_palk decimal(10,2) NOT NULL,
	CONSTRAINT PK_Amet PRIMARY KEY (amet_kood),
	CONSTRAINT AK_Amet_nimetus UNIQUE (nimetus),
	CONSTRAINT CHK_Amet_max_tootunde_nadalas CHECK (max_tootunde_nadalas BETWEEN 0 AND 50),
	CONSTRAINT CHK_Amet_min_palk CHECK (min_palk>1300),
	CONSTRAINT CHK_Amet_max_tootunde_nadalas_min_palk CHECK (NOT (max_tootunde_nadalas>40) OR min_palk>1600)
)
;

CREATE TABLE Tootaja
(
	tootaja_id serial NOT NULL,
	meiliaadress varchar(254)	 NOT NULL,
	synni_kp date NOT NULL,
	kontaktaadress varchar(100)	 NOT NULL,
	reg_aeg timestamp(0)	 NOT NULL   DEFAULT LOCALTIMESTAMP(0),
	on_aktuaalne boolean NOT NULL   DEFAULT FALSE,
	eesnimi varchar(50)	 NULL,
	perenimi varchar(50)	 NULL,
	amet_kood smallint NOT NULL,
	CONSTRAINT PK_Tootaja PRIMARY KEY (tootaja_id),
	CONSTRAINT AK_Tootaja_meiliaadress UNIQUE (meiliaadress),
	CONSTRAINT CHK_Tootaja_meiliaadress CHECK (meiliaadress LIKE '%@%'),
	CONSTRAINT CHK_Tootaja_synni_kp CHECK (synni_kp>='1920-01-01' AND synni_kp='2000-01-01' AND reg_aeg<'2100-01-01'),
	CONSTRAINT CHK_Tootaja_eesnimi CHECK (eesnimi!~'^[[:space:]]*$'),
	CONSTRAINT CHK_Tootaja_perenimi CHECK (perenimi!~'^[[:space:]]*$'),
	CONSTRAINT CHK_Tootaja_eesnimi_perenimi CHECK (eesnimi IS NOT NULL OR perenimi IS NOT NULL),
	CONSTRAINT CHK_Tootaja_reg_aeg_synni_kp CHECK (reg_aeg>synni_kp),
	CONSTRAINT FK_Tootaja_Amet FOREIGN KEY (amet_kood) REFERENCES Amet (amet_kood) ON DELETE No Action ON UPDATE Cascade
)
;

CREATE TABLE Keeleoskus
(
	tootaja_id integer NOT NULL,
	keel_kood char(3)	 NOT NULL,
	keeleoskuse_tase smallint NOT NULL,
	CONSTRAINT PK_Keeleoskus PRIMARY KEY (tootaja_id,keel_kood),
	CONSTRAINT CHK_Keeleoskus_keeleoskuse_tase CHECK (keeleoskuse_tase BETWEEN 1 AND 10),
	CONSTRAINT FK_Keeleoskus_Tootaja FOREIGN KEY (tootaja_id) REFERENCES Tootaja (tootaja_id) ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT FK_Keeleoskus_Keel FOREIGN KEY (keel_kood) REFERENCES Keel (keel_kood) ON DELETE No Action ON UPDATE Cascade
)
;

CREATE TABLE Esindaja
(
	tootaja_id integer NOT NULL,
	esindustasu decimal(10,2) NOT NULL,
	CONSTRAINT PK_Esindaja PRIMARY KEY (tootaja_id),
	CONSTRAINT CHK_Esindaja_esindustasu CHECK (esindustasu>0),
	CONSTRAINT FK_Esindaja_Tootaja FOREIGN KEY (tootaja_id) REFERENCES Tootaja (tootaja_id) ON DELETE Cascade ON UPDATE No Action
)
;

CREATE INDEX IXFK_Tootaja_Amet ON Tootaja (amet_kood ASC)
;

CREATE INDEX IXFK_Keeleoskus_Keel ON Keeleoskus (keel_kood ASC)
;