9. diel - SQLite - Otázky cez viac tabuliek (JOIN)

V predchádzajúcom kvíze, Kvíz - Export, import, radenie a dátové typy v SQLite, sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

Dnes začneme robiť na jednoduchom redakčnom systéme, ktorý môže pripomínať ten tu na ITnetwork. Ukážeme si dopytovanie cez viacero tabuliek.

Konceptuálny model

V nasledujúcich dieloch si teda v databáze vytvoríme takýto zjednodušený ITnetwork. Pobavme sa najskôr o tom, ako to bude vyzerať. Dnes stihneme pochopiteľne len malú časť. Pretože obrázok niekedy povie viac ako tisíc slov, začnime práve ním.

Čo vidíte je tzv. konceptuálny model. Je vytvorený pomocou notácie (grafického jazyka) UML av praxi sa takéto diagramy veľmi často tvoria predtým, než začneme písať nejaký kód. Dobre si tak najskôr rozmyslíme, čo že to vlastne chceme urobiť.

Vidíme, že v systéme figuruje používateľ, ktorý môže písať komentáre a články. Články spadajú do sekcií. Ide teda o databázu takého veľmi jednoduchého redakčného systému, ktorý si vďaka ITnetwork určite dokážete predstaviť.

Príprava tabuliek a dát

Dnes sa zameriame na otázky cez viacero tabuliek. Poďme si najskôr nejaké tabuľky vytvoriť. Bohato nám budú stačiť používatelia a články.

Užívatelia

Pretože užívateľ bude vyzerať trochu inak, než nám vyzeral doteraz, založíme si tabuľku používateľa znova. Tú súčasnú teda dropneme:

DROP TABLE "uzivatele";

Následne vytvoríme tabuľku novú. Užívateľ tu bude mať (okrem id) prezývku, email a heslo:

CREATE TABLE "uzivatele" (
    "uzivatele_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "prezdivka" TEXT,
    "email" TEXT,
    "heslo" TEXT
);

Do užívateľov si rovno nejaké vložíme:

INSERT INTO "uzivatele" ("prezdivka", "email", "heslo") VALUES
('Míša', '[email protected]', 'dGg#@$DetA53d'),
('David', '[email protected]', '$#fdfgfHBKBKS'),
('Denny', '[email protected]', 'Jmls_aSW2RFss'),
('Ema', '[email protected]', 'fw8QT32qmcsld');

Články

Článok bude prepojený s užívateľom, ktorý ho napísal, teda s jeho autorom. Tabuľky prepojíme tak, že do tabuľky clanky pridáme stĺpec s id autora. Tam bude hodnota id užívateľa (teda primárny kľúč z tabuľky používateľa), ktorý článok napísal.

Hovoríme o väzbe 1:N (1 užívateľ má N (niekoľko) článkov a každý článok patrí práve jednému užívateľovi). Časť (tu článok) má vždy uložené id celku (tu užívateľ) kam patrí.

Článok bude obsahovať (opäť okrem svojho id) id autora, krátky popis, url, kľúčové slová, titulok, obsah a dátum publikácie. Založme si tabuľku clanky:

CREATE TABLE "clanky" (
    "clanky_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "autor_id" INTEGER,
    "popis" TEXT,
    "url" TEXT,
    "klicova_slova" TEXT,
    "titulek" TEXT,
    "obsah" TEXT,
    "publikovano" TEXT
);

Ďalej pridáme články ak nim priradíme užívateľov ako autorov. Vzal som 4 články tu z ITnetwork, ktoré som značne skrátil a zjednodušil. Otázka bude nasledujúca:

INSERT INTO "clanky" ("autor_id", "popis", "url", "klicova_slova", "titulek", "obsah", "publikovano") VALUES
(1, 'Co je to algoritmus? Pokud to nevíte, přečtěte si tento článek.', 'co-je-to-algoritmus', 'algoritmus, co je to, vysvětlení', 'Algoritmus', 'Když se bavíme o algoritmech, pojďme se tedy shodnout na tom, co ten algoritmus vůbec je. Jednoduše řečeno, algoritmus je návod k řešení nějakého problému. Když se na to podíváme z lidského pohledu, algoritmus by mohl být třeba návod, jak ráno vstát. I když to zní jednoduše, je to docela problém. Počítače jsou totiž stroje a ty nemyslí. Musíme tedy dopodrobna popsat všechny kroky algoritmu. Tím se dostáváme k první vlastnosti algoritmu - musí být elementární (skládat se z konečného počtu jednoduchých a snadno srozumitelných kroků, tedy příkazů). "Vstaň z postele" určitě není algoritmus. "Otevři oči, sundej peřinu, posaň se, dej nohy na zem a stoupni si" - to už zní docela podrobně a jednalo by se tedy o pravý algoritmus. My se však budeme pohybovat v IT, takže budeme řešit problémy jako seřaď prvky podle velikosti nebo vyhledej prvek podle jeho obsahu. To jsou totiž 2 základní úlohy, které počítače dělají nejčastěji a které je potřeba dokonale promýšlet a optimalizovat, aby trvaly co nejkratší dobu. Z dalších příkladů algoritmů mě napadá třeba vyřeš kvadratickou rovnici nebo vyřeš sudoku.', '2012-03-21'),
(2, 'Bakterie jsou obdoba buněčného automatu v kombinaci s hrou.', 'bakterie-bunecny-automat', 'bakterie, automat, algoritmus', 'Bakterie', 'Bakterie jsou obdoba buněčného automatu, který vymyslel britský matematik John Horton Conway v roce 1970. Celou tuto hru řídí čtyři jednoduchá pravidla:/n/n
1. Živá bakterie s méně, než dvěma živými sousedy umírá./n
2. Živá bakterie s více, než třemi živými sousedy umírá na přemnožení./n
3. Živá bakterie s dvoumi nebo třemi sousedy přežívá beze změny do další generace./n
4. Mrtvá bakterie, s přesně třemi živými sousedy, opět ožívá./n
Tyto zdánlivě naprosto primitivní pravidla dokáží za správného počátečního rozmístění bakterií vytvořit pochodující skupinky, shluky "vystřelující" pochodující pětice, překvapivě složité souměrné exploze, oscilátory (periodicky kmitající skupinky), či nekonečnou podívanou na to, jak složité a dokonalé obrazce dokáží tyto dvě podmínky vytvořit. Celý program je koncipován jako hra, máte za úkol vytvořit co nejdéle žijící kolonii. <a href="soubory/bakterie.zip" ', '2012-02-14'),
(3, 'Cheese Mouse je oddechová plošinovka.', 'cheese-mouse-oddechova-plosinovka', 'myš, sýr, hra', 'Cheese Mouse', 'Cheese mouse je plošinovka s "horkou ostrovní atmosférou", kde ovládáte myš a musíte se dostat k sýru. V tom vám ale brání nejrůznější nástrahy a nepřatelé jako hadi, krysy, pirane, ale i roboti, mumie a nejrůznější havěť. Hru s několika petrobarevnými světy jsem dělal ještě na základní škole s Veisenem a může se pochlubit 2. místem v Bonusweb game competition, kde vyhrála 5.000 Kč. Vznikala v Game makeru o letních prázdninách, ještě v bezstarostném dětství, což značně ovlivnilo její grafickou stránku. Rád si ji občas zahraji na odreagování a zlepšní nálady. <a href="soubory/cheesemouse.zip" />', '2004-06-22'),
(2, 'Pacman je remake kultovní hry.', 'pacman-remake', 'pacman, remake, pampuch, hra, zdarma', 'Pacman', 'Jedná se o naprosto základní verzi této hry s editorem levelů, takže si můžete vytvořit svá vlastní kola. Postupem času ji hodlám ještě trochu upravit a přidat nějaké nové prvky, fullscreen a lepší grafiku. Engine hry bude také základem mého nového projektu Geckon man, který je zatím ve fázi psaní scénáře. <a href="soubory/pacman.zip" />', '2011-06-03');

Otázky cez viac tabuliek

Teraz máme v databáze články ak nim priradených užívateľov. Poďme si urobiť otázku cez tieto 2 tabuľky, získajme články ak nim pripojme prezývky ich užívateľov. Slovo pripojme som nepoužil náhodou, príkaz pre spojenie 2 tabuliek sa totiž volá JOIN. Napíšme si otázku a potom si ju vysvetlíme. Otázky už budeme písať na viac riadkov, aby sme sa v tom vyznali.

SELECT "titulek", "prezdivka"
FROM "clanky"
JOIN "uzivatele" ON "autor_id" = "uzivatele_id"
ORDER BY "prezdivka";

Výsledok:

Bakterie        David
Pacman          David
Cheese Mouse    Denny
Algoritmus      Míša

Na prvom riadku príkazu SELECT pracujeme so stĺpcami úplne rovnako, ako keby boli v jednej tabuľke, jednoducho vymenujeme, čo nás zaujíma. Keďže vyberáme články ak nim pripájame užívateľov, budeme vyberať z tabuľky clanky. Pripojenie dát z inej tabuľky urobíme pomocou príkazu JOIN, kde uvedieme tabuľku, ktorú pripájame, a potom klauzulu ON. Klauzula ON je podobná ako WHERE, len platí pre pripájanú tabuľku a nie pre tú, z ktorej primárne vyberáme. V podmienke uvedieme, aby sa ku každému článku pripojil ten užívateľ, ktorého používateľa_id je uvedené v stĺpci autor_id. Výsledok sme zoradili podľa prezývky používateľov. Keby sme chceli len nejaké články, normálne by sme pred ORDER BY uviedli ešte WHERE, ako sme zvyknutí.

INNER JOIN a OUTER JOIN

INNER (vnútorný) a OUTER (vonkajší) JOIN sú 2 typy príkazu JOIN. Fungujú úplne rovnako, jediný rozdiel je v tom, čo sa stane, keď položka, na ktorú sa väzba odkazuje, neexistuje.

INNER JOIN

Pokiaľ uvedieme v SQL dotaze iba JOIN, pokladá ho SQLite databáza za tzv. INNER JOIN. Pokiaľ by v našom prípade neexistoval užívateľ s id, ktoré je pri článku uvedené, článok bez užívateľa by vôbec nebol vo výsledkoch obsiahnutý. Väzba je nerozdeliteľná.

Poďme si to skúsiť, pridajme si článok, ktorý bude odkazovať na id neexistujúceho používateľa:

INSERT INTO "clanky" ("autor_id", "popis", "url", "klicova_slova", "titulek", "obsah", "publikovano") VALUES
(99, 'Článek s neexistujím uživatelem slouží pro vyzkoušení typů JOINů.', 'clanek-bez-autora', 'clanek, join, autor, chybejici', 'Článek bez autora', 'Tento článek je přiřazen neexistujícímu uživateli s ID 99 a slouží k vyzkoušení různých typů JOINů v SQLite databázi.', '2012-10-21');

Vložený článok sa odkazuje na užívateľa s uzivatele_id 99, ktorý v databáze nie je. Spustite si teraz znovu náš SQL dotaz s JOINom. Pre prehľadnosť je lepšie uviesť, že chceme INNER JOIN.

SELECT "titulek", "prezdivka"
FROM "clanky"
INNER JOIN "uzivatele" ON "autor_id" = "uzivatele_id"
ORDER BY "prezdivka";

Výsledok:

Bakterie        David
Pacman          David
Cheese Mouse    Denny
Algoritmus      Míša

Výsledok je stále rovnaký, článok bez autora medzi výsledkami nie je.

LEFT OUTER JOIN

Vonkajšie JOINy umožňujú vyberať aj tie výsledky, ktoré sa nepodarilo spojiť z dôvodu chýbajúcich položiek. SQLite vie iba ten najčastejšie používaný - LEFT JOIN, ktorý výsledok uzná, pokiaľ existuje ľavá časť väzby (tu článok) a pravá (tá pripájaná, tu užívateľ) neexistuje. Do hodnôt stĺpcov z pripájanej časti sa vloží NULL.

SELECT "titulek", "prezdivka"
FROM "clanky"
LEFT JOIN "uzivatele" ON "autor_id" = "uzivatele_id"
ORDER BY "prezdivka";

Výsledok:

Článek bez autora    NULL
Bakterie             David
Pacman               David
Cheese Mouse         Denny
Algoritmus           Míša

Vidíme, že článok sa rovnako vybral, aj keď sa nepodarilo vybrať pravú časť (teda tú pripájanú, používateľa). Pred spájaním tabuliek je dobré sa zamyslieť, či nastane prípad, keď sa spojenie nepodarí a čo v tom prípade chceme robiť. Pri článku by sa toto v reáli stať asi nemalo.

Ešte nejaké JOINy by sme určite v SQLite našli, ale na naše účely nám toto bohato stačí.

Wherovanie

Teoreticky sa môžeme JOINom vyhýbať a používať namiesto nich jednoducho len klauzulu FROM a WHERE. Vo FROM uvedieme viac tabuliek oddelených čiarkami. Vo WHERE špecifikujeme podmienku spojenia tabuliek. Databáza si v ideálnom prípade takúto otázku najprv prevedie na INNER JOIN a potom ju spracuje.

SELECT "titulek", "prezdivka"
FROM "clanky", "uzivatele"
WHERE "autor_id" = "uzivatele_id"
ORDER BY "prezdivka";

Výsledok je teda rovnaký ako pri INNER JOINe:

Bakterie        David
Pacman          David
Cheese Mouse    Denny
Algoritmus      Míša

Nevýhoda wherovania je, že tak neurobíme všetky JOINy av určitých prípadoch môžu byť otázky menej optimalizované. Nikdy nevieme, ako dopyt databázy optimalizuje a optimalizácia sa bude líšiť podľa typu databázy. Tento spôsob berte skôr ako zaujímavosť a nepoužívajte ho.

V ďalšej lekcii, SQLite - Ďalšie otázky a väzba M:N , si pridáme do našej databázy ďalšiu časť redakčného systému.