Accendo / Publikované

Autor: Libor Bešenyi

Dneska si ukážeme prácu so systémovými tabuľkami a predvedieme si referencie. Článok je určený začiatočníkom aj pokročilým užívateľom (podľa toho, kto čo hľadá).

Firebird poznámky II.

Joinovanie

Pri práci s Firebirdom ma troška zaskočila jedná situácia, keď pri joinovaní tabuliek som použil zátvorky tam, kde som nemal. Preto v poznámkach ku FB nesmieme zabúdať na upozornenie, že zápis:

[TYP] JOIN Tabulka [AS] AliasTabulky ON [(]Podmienka[)]

Nezahŕňa prácu so zátvorkami tabuľky a aliasu, napríklad:

LEFT OUTER JOIN (Tabulka [AS] Alias) ... <--- ZATVORKY NESMU BYT

Blok kódu

Tiež práca s blokmi kódov je v rôznych databázach odlišná. Firebird napríklad nepodporuje ukončenie bloku delimiterom (ak sa nejedná o storovanú procedúru) a blok je typický:

BEGIN

  ...

END

Ak používame IF, tak kód môže vyzerať napríklad takto:

  -- Nejedna sa o root

  IF (inRodic IS NOT NULL) THEN

  BEGIN

    -- Overenie existencie korena (ak sa nejedna o root)

    SELECT DefiniciaJoinov FROM SYS_DefiniciaJoinov WHERE (DefiniciaJoinov = :inKoren) INTO :Pom;

    IF (Pom IS NULL) THEN

      EXCEPTION Ex_ZlaReferencia 'Neexistuje koreòová tabuľka!';

    -- Overenie existencie rodica

   ...

 END -- (inRodic IS NOT NULL) NIE JE BODKOCIARKA

  -- Zbehli sme az tu => zapis

   ...

Premenné

            Ešte jedná poznámka pre tých, ktorý si zvykli na „štandard“ prefixu zadávanie názvu premenných so zavináčom. Firebird to nepodporuje L!

DECLARE VARIABLE @Index INTEGER; -- NESMIE BYT ZAVINAC

Admin tools

            Pri práci s databázami je vhodné pracovať aj s pomôckami, ktoré nám uľahčujú prácu. V prvom rade je to databázový modeler, v ktorom vytvárame model databázy a v prípade potreby ho meníme. Tento model potom vyexportujeme priamo do databázy (ODBC), alebo sa vygeneruje skript, ktorý pustíme nad databázou. Takýchto programov existuje mnoho, treba len dávať pozor, aby bola aplikácia určená nášmu databázovému systému. Ja využívam Power Designer, ktorý dokáže pracovať s InterBase databázami (Firebird). Tento program však nie je zadarmo (určite existuje mnoho free alternatív).

            No a taktiež je vhodné mať lepší interaktívny klient, ako ponúka inštalácia Firebirdu (ISQL). Našiel som jeden veľmi šikovný program, ktorý mi pripomína troška Sybase Central a je dokonca v edícií FREE. Stačí si ho doklikať na URL:

http://www.hk-software.net/ibexpert.com/content/http://www.hk-software.net/ibexpert.com/content/

Jedná sa o šikovnú alternatívu ISQL, ktorou dokážeme databázu vytvoriť „naklikaním“. Potom sa udržujú zoznamy jednotlivých databázových objektov a dokonca existuje aj automatické dopĺňanie možného kódu (zoznam procedúr, jej argumenty apod.). Highlight editor je samozrejmosťou. V platenej edícii Personal je už širšia paleta nástrojov, ktoré sú potrebné pre „profi“ klientelu, ako plánovače, analyzátory apod. Tieto vecičky sú fantastické, ak náš systém je hrozne lenivý a mi hľadáme odpoveď, ktorá jeho časť ho spomaľuje. Niekedy zistíme, že problém je v jednej funkcii, ktorá zožerie 99% času. Takto ju rýchlo nájdeme a optimalizujeme...

Pozn.: Možné nástroje: Visio, SmartDraw, FeniSQL, FlameRobin, IBAccess, Marathon...

Referencie

To, že Firebird je relačná databáza, niet pochýb. Jednotlivé tabuľky dokážeme medzi sebou prepájať indexovými odkazmi na ich kľúče. Tomuto vzťahu hovoríme relácia. Relácia je však pomyseľná. Ak v jeden tabuľke máme odkaz na kľúč inej tabuľky, to číslo samo o sebe nič nehovorí. Môže to byť odkaz, výška inteligencie alebo číslo domu. O tom, že je to relácia, vieme len mi. Aby o takomto vzťahu vedela aj databáza, musíme reláciu fyzickejšie zadefinovať a vytvoriť z nej referenciu.

            Referencia je už teda databázový objekt, ktorý definujeme nad tabuľkou. Jedná sa o cudzí kľúč (foreign key), ktorý ma vzťah ku inému záznamu. Ukážeme si to všetko na jednoduchom príklade a rovno využijeme priestor a referenciu nevytvoríme medzi dvoma tabuľkami, ale len medzi jednou.

            Veľa začiatočníkov dokáže vymýšľať obludné databázové štruktúry, ak chce do databázy natrieskať strom. Je pravda, že relačná databáza na toto nie je vhodne postavená, ale na Caché nie je dôvod prechádzať J. Teda v čom je problém? Predstavme si učebnicový príklad: zapíšme zoznam zamestnancov do databázy a nech okrem mena a priezviska tam máme aj informáciu o och nadriadenom. Po analýze problému zisťujeme, že sa jedná o strom, pretože aj nadriadený je len zamestnanec a aj on má svojich nadriadených. Tí, ktorí už nemajú nadriadených, sú konatelia spoločnosti. Takto nám vlastne vzniká strom závislosti na „rodičovi“ (alebo vlastníkovi). Toto všetko dokážeme definovať šikovne pomocou jednej tabuľky:

-- Tabulka

CREATE TABLE Zamestnanec (

Zamestnanec INTEGER NOT NULL,

Meno VARCHAR(20),

Priezvisko VARCHAR(20),

Nadriadeny INTEGER,

CONSTRAINT PK_Zamestnanec PRIMARY KEY (Zamestnanec)

);

            Ďalej si pripravme vhodnú pôdu pre ukážku. Zadefinujeme si sekvenciu, ktorou budeme autoinkrementovať tabuľku a procedúrku, ktorá nám pomôže s vkladaním údajov:

-- Sekvencia pre generovanie PK

CREATE GENERATOR Seq_Zamestnanec;

-- Procedura na zapis zaznamov do tabulky

CREATE PROCEDURE Zamestnanec_Insert (

inMeno VARCHAR(20),

inPriezvisko VARCHAR(20),

inNadriadeny INTEGER) AS

BEGIN

-- Zapiseme do tabulky s autoinkrementaciou

INSERT INTO Zamestnanec

(Zamestnanec, Meno, Priezvisko, Nadriadeny)

VALUES

(GEN_ID(Seq_Zamestnanec, 1), :inMeno, :inPriezvisko, :inNadriadeny);

END;

            Tak, majme teraz firemnú štruktúru nasledujúcu: Mame troch konateľov (majitelia). Janko Hráško sa stará o divíziu personalistiky a styku s klientmi. Pod ním sa nachádza riaditeľ spomínanej divízie Lukáš Haluška, ktorý má dvoch zamestnancov: Ľudmilu Podjavorinská a Katarínu Popolovú. Druhý konateľ spoločnosti, má na starosti výrobnú divíziu. Pod ním priamo je milión robotníkov, ktorých nebudeme ukladať do databázy. Tretí konateľ Tomáš Hrbatý, je tichým spoločníkom a vo firme nezastáva žiadnu funkciu. Teda, naplníme si teraz tabuľku údajmi:

EXECUTE PROCEDURE Zamestnanec_Insert('Janko','Hrasko',NULL); -- ID 1

EXECUTE PROCEDURE Zamestnanec_Insert('Eva','Fuzikova',NULL); -- ID 2

EXECUTE PROCEDURE Zamestnanec_Insert('Tomas','Hrbaty',NULL); -- ID 3

EXECUTE PROCEDURE Zamestnanec_Insert('Lukas','Haluska',1 /* ID NADRIADENEHO */); -- ID 4

EXECUTE PROCEDURE Zamestnanec_Insert('Ludmila','Podjavorinska',4 /* ID NADRIADENEHO */);

EXECUTE PROCEDURE Zamestnanec_Insert('Katarina','Popolova',4 /* ID NADRIADENEHO */);

            Takouto reprezentáciu jednoducho dokážeme vytiahnuť z databázy zoznam mien zamestnancov s menami ich nadriadených. Na tabuľku musíme najoinovať znova rovnakú tabuľku, ale tento krát cez referenčný odkaz. Teda zamestnanec odkazuje na svojho nadriadeného prostredníctvom hodnoty uloženej v poli nadriadený, pričom sa jedná o ID zamestnanca v tejto tabuľke. Takže selekt vyzerá takto:

SELECT

Zam.Priezvisko AS Zamestnanec,

Nad.Priezvisko AS Nadriadeny

FROM Zamestnanec AS Zam

LEFT OUTER JOIN Zamestnanec AS Nad ON (Nad.Zamestnanec = Zam.Nadriadeny)

            Pripomínam, že je nutné použiť OUTER join, pretože v prípade INNER ako určite všetci vieme, by sme stratili hodnoty konateľov, pretože by neexistoval záznam o nadriadenom v tabuľke (NULL).

            Toto je však stále len referenčný spôsob. Skúsme pridať ešte nejakého zamestnanca nášmu tichému spoločníkovi:

EXECUTE PROCEDURE Zamestnanec_Insert('Alexander','Macedonsky',3 /* ID NADRIADENEHO */);

            Teraz si predstavme, že divízia, ktorú vedie úplne skrachuje a dokonca aj konateľa vylúčia zo spoločnosti. Pokúsme sa vymazať konateľa a ešte raz si vypíšme zamestnancov:

DELETE FROM Zamestnanec WHERE (Zamestnanec = 3 /* ID */);

SELECT

Zam.Priezvisko AS Zamestnanec,

Nad.Priezvisko AS Nadriadeny

FROM Zamestnanec AS Zam

LEFT OUTER JOIN Zamestnanec AS Nad ON (Nad.Zamestnanec = Zam.Nadriadeny)

ZAMESTNANEC      NADRIADENY

Hrasko                        null

Fuzikova                      null

Haluska                       Hrasko

Podjavorinska              Haluska

Popolova                     Haluska

Macedonsky                null

Máme teda zoznam, no stále v ňom figuruje aj zamestnanec konateľa, ktorého sme vymazali, on len stratil informáciu o tom „komu patrí“. Toto je vlastne spomínaný vzťah relácie, ktorý je skôr pomyselný. Poďme si ale našu architektúru prerobiť na referenčnú. Musíme pridať jeden foregin key, do tabuľky:

            V prvom rade, však musíme vymazať údaje z tabuľky (v prípade referencie medzi dvoma tabuľkami, údaje musíme vymazať všade).

POZOR: Ak teda plánujeme integrovať referencie do našich systémov, musíme si vytvoriť bezpečný spôsob updatu databázy zákazníka, pretože ak sa náš program používa niekoľko mesiacov a potom sa rozhodneme pracovať s referenciami, nemôžeme si dovoliť mazať existujúce údaje.

            Syntax pridania referencie je jednoduchá, zmeníme tabuľku tak, že pridáme cudzí kľúč kľuč na pole referenčného odkazu, potom zadefinujeme druhú tabuľku (v našom prípade tú istú) a zavesíme odkaz jej kľúč (u nás index):

ADD CONSTRAINT [Názov FK] FOREIGN KEY (Referenčné pole)

  REFERENCES [Druhá tabuľka] (Jej pole)

Príklad:

DELETE FROM Zamestnanec; -- Vymazeme data

-- Vytvorime FK

ADD CONSTRAINT FK_ZamestnanecNadriadeny foreign key (Nadriadeny)

REFERENCES SYS_DefiniciaJoinov (Zamestnanec);

            Ak sa teraz pokúsime vymazať záznam, na ktorý neodkazuje žiadna referencia, operácia prebehne v poriadku. Ak však budeme vymazávať záznam, na ktorý odkazuje niečo v databáze referenčne, nepodarí sa nám to (museli by sme rekurzívne vymazávať od konca stromu ku koreňu – jedná sa o tzv. restrikčný vzťah, ktorý je implicitný). To isté však platí aj o pridávaní záznamov! Ak sme vymazali databázu tak záznamy sa zmazali, ale ich indexy sa posunuli. Pridanie konateľov zbehne v poriadku, pretože neodkazujú na nijaký záznam:

-- Naplnime data

EXECUTE PROCEDURE Zamestnanec_Insert('Janko','Hrasko',NULL); -- ID 13

EXECUTE PROCEDURE Zamestnanec_Insert('Eva','Fuzikova',NULL); -- ID 14

EXECUTE PROCEDURE Zamestnanec_Insert('Tomas','Hrbaty',NULL); -- ID 15

Pridanie zamestnanca s neplatným odkazom, sa nám už nepodarí:

EXECUTE PROCEDURE Zamestnanec_Insert('Lukas','Haluska',1 /* ID NADRIADENEHO */);

            Tu sa už stretávame s fyzickým naviazaním referencie. Ako vidno, Lukáša pridať nemôžeme, lebo odkazuje na ID 1. To však už neexistuje. Jeho nadriadený sa „posunul“ na pozíciu 13 a teda musíme ekvivalente prispôsobiť indexovanie:

EXECUTE PROCEDURE Zamestnanec_Insert('Lukas','Haluska',13 /* ID NADRIADENEHO */); -- ID 16

EXECUTE PROCEDURE Zamestnanec_Insert('Ludmila','Podjavorinska',16 /* ID NADRIADENEHO */); -- 17

EXECUTE PROCEDURE Zamestnanec_Insert('Katarina','Popolova',16 /* ID NADRIADENEHO */); -- ID 18

            Super vlastnosťou referencie je však nastavovanie referenčného vzťahu na konkrétnu operáciu. Ak referenciu zapíšeme tak, že vytvoríme kaskádový vzťah pri vymazávaní, tak databáza vymaže rekurzívne strom podriadených za nás. Ak sa pokúsime teda vymazať konateľa, tak jeho vymazaním sa vymažú všetky záznamy podriadených. Potom sa to opakuje na záznamy podriadených podriadeným apod. (kaskáda). Takáto definícia sa vykonáva pri definícii referencie a môže byť pridaná na update záznamu (napríklad ak chceme plošne zvyšovať platy zamestnancom jednej divízie) alebo spomínané mazanie. Vzťah môže byť None, Restrict, Cascade, Set NULL, Set Default.

Príklad kaskádového mazania:

ALTER TABLE Zamestnanec

ADD CONSTRAINT FK_ZamestnanecNadriadeny FOREIGN KEY (Nadriadeny)

REFERENCES Zamestnanec (Zamestnanec)  ON DELETE CASCADE

            Narýchlo si ešte ozrejmime mazanie FK:

ALTER TABLE Zamestnanec DROP CONSTRAINT FK_ZamestnanecNadriadeny

Pozn.: Referencia tabuľky na seba samú sa nazýva unárnou.

Systémové tabuľky

            Niekedy potrebujeme pristupovať ku systémovým tabuľkami, aby sme vytiahli údaje zo štruktúry. Ak napríklad chceme vytvárať dynamické moduly, ktorých definície by sme naklikávali v nejakom setupe, tak potrebujeme dotiahnuť do DataSetu zoznam tabuliek, prípade zoznamy polí tabuľky. Firebird používa systémové „RDB“ tabuľky a tak na rýchlo si ukážeme zoznam tabuliek a zoznam polí:

SELECT

Flds.RDB$FIELD_NAME [AS Pole]

FROM RDB$RELATIONS AS Rel

INNER JOIN RDB$RELATION_FIELDS AS Flds ON Flds.RDB$RELATION_NAME = Rel.RDB$RELATION_NAME

WHERE

(Rel.RDB$SYSTEM_FLAG = 0) AND

(Rel.RDB$RELATION_NAME = 'TABULKA')

[

ORDER BY

Pole

]

SELECT

RDB$RELATION_NAME

FROM RDB$RELATIONS

WHERE'#13#10 +

(RDB$SYSTEM_FLAG = 0) AND

(RDB$VIEW_BLR IS NULL)

[

ORDER BY

RDB$RELATION_NAME

]