Kategória: Linux všeobecne

Zmenené: 30. august 2013

Cudzie kľúče v SQLite3 a Dia

Pred časom som napísal článok, kde som predstavil možnosti modelovania databázy pomocou UML nástroja na tvorbu diagramov Dia. V tomto článku predstavím modelovanie cudzích kľúčov SQLite3.

Upozornenie

Nebudem vymýšľať nič nové, ale prevediem do grafickej podoby príklad z dokumentácie SQLite3.

Základná štruktúra

Dokumentácia prichádza so základným príkladom, ktorý potom neskôr rozvíja. Základná štruktúra databázy zahŕňa dve tabuľky, a to artist a track:

CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY,
    artistname  TEXT
);

CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT,
    trackartist INTEGER     -- Musí byť mapované na artist.artistid!
);

Pričom neskôr definíciu tabuľky track rozširuje pomocou cudzieho kľúča takto:

CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT,
    trackartist INTEGER,
    FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

No a aby ste mohli testovať prácu cudzích kľúčov, dokumentácia vkladá aj vzorové údaje, ktoré vo výstupe vyzerajú takto:

SELECT * FROM artist;
artistid  artistname
--------  -----------------
1         Dean Martin
2         Frank Sinatra

SELECT * FROM track;
trackid  trackname          trackartist
-------  -----------------  -----------
11       That is Amore      1
12       Christmas Blues    1
13       My Way             2

Mojimi cieľom je toto isté dosiahnuť pomocou Dia a nástroja parsediasql, tak idem na to!

Vytvorenie tabuliek

Nástroj Trieda

Začnem vytvorením tabuliek, na čo použijem tvary UML a na definíciu tabuľky konkrétne prvok Trieda. Ako vidíte na obrázku vpravo, je to hneď prvý nástroj na liste UML. Takže kliknite na tvar a do plochy Dia vložte dva tvary Trieda, pretože potrebujete dve tabuľky. Ďalej budem tomuto tvaru hovoriť tabuľka, aby som používal rovnaké pomenovanie v Dia i v SQL.

Nasleduje nastavenie tabuľky. Dvojklikom na tabuľku v ploche Dia sa otvorí dialóg nastavení tabuľky. Má viacero záložiek, na potreby SQL sú však dôležité len dve, a to záložky:

• Trieda
• Atribúty

V záložke Trieda treba vyplniť názov tabuľky do poľa Meno triedy, to by nemal byť problém. Okrem tohoto povinného nastavenia môžete nastaviť, ktoré prvky budú viditeľné v rozhraní Dia, ale nezaregistroval som, že by to malo vplyv na výsledné SQL.

Definícia mena tabuľky

Záložka Atribúty slúži na definíciu polí tabuľky. Na obrázku nižšie vidno definíciu poľa artistid, ale pekne postupne. Na pridanie (a tiež odstránenie, či zmenu poradia) slúžia tlačidlá v pravej časti záložky. Pre každé pole tabuľky je potrebné nastaviť Názov a Typ. V oboch prípadoch význam zodpovedá názvu, teda nastavujete meno stĺpca (poľa) tabuľky a jeho typ. Okrem toho môžete nastaviť aj predvolenú hodnotu, a to v poli Hodnota. V poli Viditeľnosť ponechajte predvolenú hodnotu, tj. Verejný.

Primárny kľúč vytvoríte nastavením poľa Viditeľnosť na hodnotu Chránený. Potom sa už samotný nástroj parsediasql postará o vygenerovanie správnych parametrov primárneho kľúča. V zobrazení modelu je potom označený znakom # pred menom stĺpca.

Definícia polí tabuľky

Upozornenie

Samozrejme (a odporúčam to) môžete zmeniť nastavenie vzhľadu v záložke Štýl.

Výsledkom tohoto snaženia by mal byť model ako na obrázku:

Základný model databázy

Otestovanie výstupu

Po vytvorení (a uložení!!!) základnej štruktúry, nastal čas skontrolovať, či to, čo modelujem, má aj správny výstup. Existuje niekoľko spôsobov ako to urobiť. Ten základný je nechať si zobraziť výstup príkazu parsediasql, teda pozrieť výsledné SQL.

Samotný program parsediasql (balík libparse-dia-sql-perl) vyžaduje pri spustení zadanie vstupného súboru (voľba --file) a typ výslednej databázy (voľba --db). Ja som si svoj model databázy uložil do súboru fkeys.dia no a vytváram databázu typu sqlite3, takže príkaz vyzerá takto:

parsediasql --file fkeys.dia --db sqlite3
[INFO] associations is an empty ARRAY ref
[INFO] components is an empty ARRAY ref
[INFO] associations is an empty ARRAY ref
...

Upozornenie

Zobrazenie informácií [INFO] možno riadiť voľbou --loglevel.

Avšak, takto mi to zobrazuje viac informácií ako potrebujem, preto používam kombináciu príkazov, ktoré mi priamo vytvoria databázu SQLite3 a následne mi zobrazia len požadované informácie priamo z databázy:

rm -f test.db; parsediasql --file fkeys.dia --db sqlite3 | sqlite3 test.db; \
    sqlite3 test.db ".schema artist"; sqlite3 test.db ".schema track"
[INFO] associations is an empty ARRAY ref
[INFO] components is an empty ARRAY ref
[INFO] associations is an empty ARRAY ref

CREATE TABLE artist (
   artistid   INTEGER PRIMARY KEY NOT NULL ,
   artistname TEXT
);
CREATE TABLE track (
   trackid     INTEGER ,
   trackname   TEXT    ,
   trackartist INTEGER --  Musí byť mapované na artist.artistid!
);

Môžete si skontrolovať, že výsledné SQK je rovnaké, ako som uviedol na začiatku. Takže super, štruktúra databázy je vytvorená.

Pridanie dát

Nástroj Komponent

Čo by to bola za ukážková databáza, keby neobsahovala žiadne dáta, všakže? takže ďalším krokom je pridanie vzorových dát, tak ako sú uvedené na začiatku článku.

Na pridávanie dát do tabuliek slúži tvar Komponent. I v tomto prípade budú potrebné dva tvary, pretože pridávate dáta do dvoch tabuliek, ale ja to zase popíšem len na jednom. Práca s prvkom Komponent je trochu zložitejšia, pretože tu dochádza ku kombinácii dvoch spôsobov úprav, resp. modelovanie sa skladá z dvoch krokov:

1. nastavenie príkazu vloženia
2. zadanie dát

Nastavenie príkazu

Nastavenie príkazu vkladania urobíte tak, že dvakrát kliknete na prvok v ploche Dia, čím otvoríte jeho vlastnosti. iste si všimnete, že sú výrazne chudobnejšie ako v predošlom prípade, ale to je dobre. V otvorenom dialógu je dôležité len jedno pole, a to s názvom Stereotyp. Otázkou ostáva, čo tam napísať.

Tu si pomôžem ukážkou SQL. Ak na vloženie dát teba príkaz SQL v tvare:

INSERT INTO artist (artistid, artistname) VALUES (1, "Dean Martin");

Tak do poľa Stereotyp patrí časť, ktorá popisuje meno tabuľky a jej stĺpce, teda:

artist (artistid, artistname)

Vyplnenie poľa Stereotyp

Rada

Pretože je možné vkladať dáta aj bez explicitného vymenovania polí (ak sú zadávané hodnoty všetkých polí v správnom poradí), je možné pole Stereotyp vyplniť len menom tabuľky…

Zadanie dát

Zadávanie dát zrealizujete priamou úpravou textovým nástrojom (kláves F2), keď znova využijem vyššie spomenutý príklad, do prvku patrí časť v zátvorke, za kľúčovým slovom VALUES, teda:

1, "Dean Martin"

Samozrejme, každý záznam na samostatný riadok. teraz by mal model databázy vyzerať takto:

Model databázy s dátami

Upozornenie

Všimnite si, že v ľavom prvku som použil skrátenú definíciu Stereotypu.

Otestovanie výstupu

I tento krát treba skontrolovať výsledok. tentokrát používam na konci výber pomocou SQL:

rm -f test.db; parsediasql --file fkeys.dia --db sqlite3 | sqlite3 test.db; \
    sqlite3 -column -header test.db "SELECT * FROM artist"

artistid    artistname
----------  -----------
1           Dean Martin
2           Frank Sinat

Fajn, aj tentokrát je výsledok taký, ako má byť – až na orezanie výstupu druhého stĺpca, ale schválne som to nedopísal, pretože údaje v databáze sú správne.

Pridanie cudzieho kľúča

Poslednou úlohou je pridanie obmedzenia pomocou cudzieho kľúča. V Dia na to poslúži tvar Agregácia, ale najprv trocha terminológie, aby ste sa v mojom popise nestratili:

• rodičovská tabuľka (parent table) je tabuľka, na ktorú cudzí kľúč odkazuje – tu artist;
• dcérska tabuľka (child table) je tabuľka, na kde je cudzí kľúč použitý – tu track;
• rodičovský kľúč (parent key) je stĺpec rodičovskej tabuľky, na ktorý cudzí kľúč odkazuje – tu artistid;
• dcérsky kľúč (child key) je stĺpec dcérskej tabuľky, ktorý je obmedzený cudzím kľúčom – tu trackartist.

Nástroj Agregácia

Tip

V skutočnosti môžete použiť aj prvok Asociácia (je hneď vedľa), len nebudete mať symbol konca vzťahu (relation), ale Typ môžete nastaviť vo vlastnostiach.

Pridanie tohoto prvku je mierne odlišne, oproti dvom predchádzajúcim, pretože ho treba pripojiť k príslušným tabuľkám, pričom na mieste pripojenia v tabuľke nezáleží.

Symbol kosoštvorca tvaru Agregácia slúži na označenie konca N vzťahu 1:N, a symbol smeru je použitý na indikáciu vlastníka v prípade vzťahu 1:1. Inými slovami, pri pripájaní tvaru začnite v dcérskej tabuľke (track) a ukončite v rodičovskej (artist).

Upozornenie

Ak sa vám podarí nakresliť vzťah opačne (teda z rodičovskej do dcérskej tabuľky) môžete samozrejme prvok zmazať a urobiť ho znova. Alebo môžete otočiť smer vzťahu vo vlastnostiach prvku. Sám kreslím vzťah vždy zľava doprava, aby som mal stranu A vľavo, a tak sa v tom vyznal.

Po pripojení Agregácie dvojklikom otvoríte vlastnosti prvku, kde môžete nastaviť názov prepojenia. Píšem môžete, ale nemusíte. Ak názov nenastavíte, bude názov vytvorený automaticky, a to spojením mien prepojených tabuliek. V podstate je meno užitočné len v prípade výskytu chyby, kedy chybové hlásenie obsahuje názov prepojenia.

Čo však nastaviť musíte, sú polia Úloha, a to pre oba konce vzťahu. Do týchto polí je potrebné zadať názvy polí vzťahu, teda meno dcérskeho a rodičovského kľúča. Hodnú chvíľu som dumal, ako napísať ktorý kam, ale nakoniec v tom netreba hľadať vedu. Jednoducho mená stĺpcov zadajte tak, aby boli zobrazené pri správne tabuľke!

Rada

Strana A je tá strana, kde ste začali kresliť a Strana B je tá druhá…

Vlastnosti vzťahu

Po nastavení by mal model vyzerať takto:

Model s cudzím kľúčom

Otestovanie výstupu

Tak ako v predošlých krokoch, i teraz je dobré skontrolovať výstup, že čo som to vlastne nastavil. Avšak, oproti predošlým pokusom použijem typ databázy sqlite3fk. Je to špeciálny typ databázy, ktorý je vlastne totožný s typom sqlite3, len generuje cudzie kľúče pomocou ich priamej definície a nie pomocou spúšťačov. Takže kontrolný príkaz:

rm -f test.db; parsediasql file fkeys.dia --db sqlite3fk | sqlite3 test.db; \
    sqlite3 test.db ".schema artist"; sqlite3 test.db ".schema track"

S výsledkom:

CREATE TABLE artist (
   artistid   INTEGER not null,
   artistname TEXT            ,
   constraint pk_artist primary key (artistid)
);
CREATE TABLE track (
   trackid     INTEGER ,
   trackname   TEXT    ,
   trackartist INTEGER ,--  Musí byť mapované na artist.artistid!

   foreign key(trackartist) references artist(artistid)
);

Hurá – podarilo sa!!!

Akcie cudzích kľúčov

Ako poslednú vec spomeniem akcie cudzích kľúčov. Tie sa nastavujú do poľa početnosť na strane dcérskeho kľúča.

Akcia cudzieho kľúča

Takto nastavená akcia sa vo výsledku prejaví takto:

CREATE TABLE track (
   trackid     INTEGER ,
   trackname   TEXT    ,
   trackartist INTEGER ,--  Musí byť mapované na artist.artistid!

   foreign key(trackartist) references artist(artistid) ON DELETE CASCADE
);

Upozornenie

V modeli je akcia cudzieho kľúča vypísaná pod menom dcérskeho kľúča.

Záver

Čo napísať na záver? Vlastne ani neviem. Je fakt, že použitie nástroja parsediasql je málo zdokumentované, preto som niektoré veci musel skúšať metódou pokus – omyl. Avšak, s výsledkom som spokojný, pretože vizuálne modelovanie databázy poskytuje prehľad o jej návrhu. Ale hlavnú výhodu pocítite, keď sa k nejakej databáze vrátite s odstupom času.

• Popis použitia tedia2sql
• Cudzie kľúče v SQLite3
• súbor Dia s príkladom