9. diel - SQLite - Dopyty 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í.

V tomto tutoriáli začneme pripravovať SQLite databázu jednoduchého redakčného systému, ktorý môže pripomínať ten tu na ITnetwork. Ukážeme si dotazovanie cez viac 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íme je tzv. konceptuálny model. Je vytvorený pomocou notácie (grafického jazyka) UML. V 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 dopyty cez viacero tabuliek. Poďme si najskôr nejaké tabuľky vytvoriť. Bohato nám budú stačiť používatelia a články.

Používatelia

Pretože používateľ bude vyzerať trochu inak, než vyzeral doteraz, vytvoríme si tabuľku users znova. Tú súčasnú teda odstraníme:

DROP TABLE "users";

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

CREATE TABLE "users" (
    "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "nickname" TEXT,
    "email" TEXT,
    "password" TEXT
);

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

INSERT INTO "users" ("nickname", "email", "password") VALUES
('Michael', '[email protected]', 'dGg#@$DetA53d'),
('David', '[email protected]', '$#fdfgfHBKBKS'),
('Denny', '[email protected]', 'Jmls_aSW2RFss'),
('Emma', '[email protected]', 'fw8QT32qmcsld');

Články

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

Hovoríme o väzbe 1:N (1 používateľ má N (niekoľko) článkov a každý článok patrí práve jednému používateľovi). Časť (tu článok) má vždy uložené id celku (tu použí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. Vytvorme si tabuľku articles:

CREATE TABLE "articles" (
    "article_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "author_id" INTEGER,
    "description" TEXT,
    "url" TEXT,
    "keywords" TEXT,
    "title" TEXT,
    "content" TEXT,
    "published" TEXT
);

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

INSERT INTO "articles" ("author_id", "description", "url", "keywords", "title", "content", "published") VALUES
(1, 'What is an algorithm? If you do not know, read this article.', 'what-is-an-algorithm', 'algorithm, what is it, explanation', 'Algorithm', 'So while we are talking about algorithms, let us agree on what an algorithm is. Simply put, an algorithm is a guide to solving a problem. If we look at it from a human point of view, an algorithm could be, for example, instructions on how to get up in the morning. Although it sounds simple, it is quite a problem. Computers are machines and they do not think. So we have to describe all the steps of the algorithm in detail. This brings us to the first characteristic of an algorithm - it must be elementary (consist of a finite number of simple and easily understandable steps, i.e. commands). "Get out of bed" is definitely not an algorithm. "Open your eyes, take off the duvet, stretch yourself, put your feet on the ground and stand up" - that already sounds quite detailed and would therefore be a real algorithm. However, we will be moving in IT, so we will solve problems like sort elements by size or find an element by its content. These are the 2 basic tasks that computers do most often and which need to be perfectly thought out and optimized so that they last as little time as possible. Among other examples of algorithms, I can think of solving a quadratic equation or solving Sudoku.', '2012-3-21'),
(2, 'Bacteria are like a cellular automaton combined with a game.', 'bacteria-cell-automatic-machine', 'bacteria, automaton, algorithm', 'Bacteria', 'Bacteria are similar to the cellular automaton invented by the British mathematician John Horton Conway in 1970. The whole game is governed by four simple rules:/n/n
1. A living bacterium with less than two living neighbors dies./n
2. A living bacterium with more than three living neighbors dies of overpopulation./n
3. A living bacterium with two or three neighbors survives unchanged into the next generation./n
4. A dead bacterium, with exactly three living neighbors, comes back to life./n
These seemingly completely primitive rules can create marching groups, clusters "shooting" marching fives, surprisingly complex symmetrical explosions, oscillators (periodically oscillating groups), or an endless spectacle of how complex and perfect shapes these two conditions can create with the correct initial distribution of bacteria . The whole program is conceived as a game, you have the task of creating a colony that lives as long as possible.', '2012-2-14'),
(3, 'Cheese Mouse is a relaxing platformer.', 'cheese-mouse-relaxation-platformer', 'mouse, cheese, game', 'Cheese Mouse', 'Cheese mouse is a platform game with a "hot island atmosphere" where you control the mouse and have to get to the cheese. However, various traps and enemies such as snakes, rats, piranhas, as well as robots, mummies and various vermin prevent you from doing so. I made a game with several petro-colored worlds back in elementary school with Veisen, and it can boast the 2nd place in the Bonusweb game competition, where it won 5,000 CZK. It was created in Game Maker during the summer holidays, still in a carefree childhood, which greatly influenced its graphic side. I like to play it sometimes to relax and improve my mood.', '2004-6-22'),
(2, 'Pacman is a remake of the iconic game.', 'pacman-remake', 'pacman, remake, pampuch, game, free', 'Pacman', 'This is a very basic version of this game with a level editor so you can create your own wheels. Over time, I intend to modify it a little more and add some new elements, fullscreen and better graphics. The game engine will also be the basis of my new project Geckon man, which is currently in the scripting stage.', '2011-6-03');

Dopyty cez viac tabuliek

Teraz máme v databáze články a k nim priradených používateľov. Poďme si urobiť dopyt cez tieto dve tabuľky, získajme články a k nim pripojme prezývky ich používateľov. Slovo pripojme som nepoužil náhodou, príkaz pre spojenie dvoch tabuliek sa totiž volá JOIN. Napíšme si dopyt a potom si ho vysvetlíme. Dopyty už budeme písať na viac riadkov, aby sme sa v nich vyznali:

SELECT "title", "nickname"
FROM "articles"
JOIN "users" ON "author_id" = "user_id"
ORDER BY "nickname";

Výsledok:

Bacteria        David
Pacman          David
Cheese Mouse    Denny
Algorithm       Michael

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 a k nim pripájame používateľov, budeme vyberať z tabuľky articles. 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 používateľ, ktorého user_id je uvedené v stĺpci author_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ú dva 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

Ak uvedieme v SQL dopyte iba JOIN, pokladá ho SQLite databáza za tzv. INNER JOIN. Ak by v našom prípade neexistoval užívateľ s id, ktoré je pri článku uvedené, článok bez použí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 "articles" ("author_id", "description", "url", "keywords", "title", "content", "published") VALUES
(99, 'The article with a non-existent user is used to test the types of JOINs.', 'article-without-author', 'article, join, author, missing', 'Article without author', 'This article is assigned to a non-existent user with ID 99 and serves to test different types of JOINs in a SQLite database.', '2012-10-21');

Vložený článok sa odkazuje na užívateľa a user_id má hodnotu 99. Takový autor však v našej databáze nie je. Spustite si teraz znovu náš predchádzajúcí SQL dopyt. Pre prehľadnosť je v ňom lepšie uviesť, že chceme INNER JOIN:

SELECT "title", "nickname"
FROM "articles"
INNER JOIN "users" ON "author_id" = "user_id"
ORDER BY "nickname";

Výsledok:

Bacteria        David
Pacman          David
Cheese Mouse    Denny
Algorithm       Michael

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

LEFT OUTER JOIN

Vonkajši OUTER JOIN umožňuje 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á, ak existuje ľavá časť väzby (tu článok) a pravá (tá pripájaná, tu používateľ) neexistuje. Do hodnôt stĺpcov z pripájanej časti sa vloží NULL:

SELECT "title", "nickname"
FROM "articles"
LEFT JOIN "users" ON "author_id" = "user_id"
ORDER BY "nickname";

Výsledok:

Article without author      NULL
Bacteria                    David
Pacman                      David
Cheese Mouse                Denny
Algorithm                   Michael

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.

Wherovanie

Teoreticky sa môžeme spájanie tabuliek 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 pre vyhľadanie záznamov. Databáza si v ideálnom prípade takýto dotaz najprv prevedie na INNER JOIN a potom ho spracuje:

SELECT "title", "nickname"
FROM "articles", "users"
WHERE "author_id" = "user_id"
ORDER BY "nickname";

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

Bacteria        David
Pacman          David
Cheese Mouse    Denny
Algorithm       Michael

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

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