2. diel - SQLite - Vytvorenie databázy a tabuľky

V minulej lekcii, Úvod do SQLite a príprava prostredia, sme si povedali niečo o relačných databázach a pripravili sme si prostredie.

Dnes si vytvoríme databázu a do nej nejakú tabuľku.

Vytvorme si databázu, obvykle nám pre jeden projekt (web) postačí jedna databáza.

Klikneme v DB Browser for SQLite na New Database. Vyberieme zložku, kde chceme databázu uložiť a vyplníme názov databázy s príponou .db (napr. web_database.db). V databázach je zvykom pomenovávať položky bez diakritiky, malými písmenami a s podčiarkovovou notáciou. Snáď vám je jasné, prečo nie je diakritika dobrý nápad, za veľkými a malými písmenami je Linux, ktorý ich rozlišuje a väčšina serverov práve na Linuxe beží.

Potvrdíme a vyskočí na nás okno na vytvorenie prvej tabuľky, spomenieme si na príklad tabuľky puužívateľov, čo sme si ukázali v minulom dieli. Prvá bunka po nás chce meno tabuľky, zadáme podľa pravidiel users. Tabuľka mala stípce meno, priezvisko, dátum narodenia a počet článkov. Ako sme už nahryzli, každá tabuľka by mala mať stípec, ktorého hodnota je pre každú položku unikátna. Týmto stípcom začneme a klikneme na tlačidlo Add, v tabuľke nižšie sa nám objaví riadok, ktorého prvý údaj - názov - prepíšeme na user_id, dátový typ necháme na INTEGER - celom čísle a ďalej zaškrtneme PK a AI. PK značí PRIMARY KEY, čo znamená, že tento stípec slúži na identifikáciu riadku v tabuľke a jeho hodnota musí byť unikátna. AI je skratka AUTOINCREMENT, teda, že sa bude hodnota user_id automaticky navyšovať a používatelia sa budú postupne číslovať. Id sa mi osvedčilo pomenovávať s prefixom tabuľky, ale nie je to nutné.

Teraz pridáme ďalšie stípce, klikneme znova na Add a tentoraz zadáme first_name. V druhom stípci nastavíme hodnotu TEXT. Toto vykonáme obdobne aj pre stípce last_name a birth_date. Poslednému stípcu s menom article_count ponecháme dátový typ INTEGER. Všimneme si, že nižšie sa nám vygeneroval kód v jazyku SQL. Celé to má vyzerať takto:

Po kliknutí na OK uvidíme na oboch paneloch štruktúru našej databázy, kde by sa mala nachádzať iba naša tabuľka users a vedľa nej kód SQL pre jej vytvorenie, vygenerovaný DB Browserom. Ak chceme v DB Browseri uložiť akékoľvek úpravy, aby sa prejavili v databáze, musíme hore kliknúť na Write Changes, čo urobíme práve teraz.

Ak by sme chceli vytvoriť databázu bez použitia DB Browsera for SQLite, tak stačí spustiť v konzole/termináli nasledujúci príkaz, ktorý vás taktiež prenesie do interaktívneho SQLite shellu:

sqlite3 web_database.db

Keďže sa databázy ukladajú ako normálne súbory, kdekoľvek, kde si zvolíte, ich odstránenie sa vykoná ako jednoduché zmazanie tohto súboru.

Jazyk SQL

SQL označuje Structured Query Language, teda štruktúrovaný dotazovací jazyk. SQL je tzv. jazyk deklaratívny. Zatiaľ čo v imperatívnych jazykoch počítača vlastne hovoríme krok po kroku, čo má urobiť, v jazykoch deklaratívnych iba hovoríme, čo má byť výsledkom a už nás nezaujíma, ako tento výsledok počítač dosiahne. Vďaka tomu sú databázové dotazy zjednodušené na príkaz typu "Vráť mi 10 používateľov s najvyšším hodnotením". Databáza takýto dotaz pochopí, rozloží si ho na nejaké svoje inštrukcie a tak ho spracuje. Nám potom naozaj vráti výsledok bez toho, aby sme tušili, ako k nemu došla. Pokiaľ vám príkaz prišiel ako nadsadenie, tak to tak nie je a príkazy naozaj takto vyzerajú. Len sú anglicky.

SQL sa pôvodne volalo SEQUEL (Structured English Query Language) a vzniklo v laboratóriách spoločnosti IBM s cieľom vytvoriť jazyk, ktorým by sa dalo komunikovať s databázou jednoduchou angličtinou. SQL (relačné) databázy sa potom rozširovali a ujali. Dnes sa prakticky nič iné nepoužíva a aj keď má SQL v objektovom programovaní značné nevýhody, firmám sa nechce prechádzať na nič iné (aj keď existujú alternatívne riešenia). Ale to sme odbočili.

Naklikaním tabuľky nám DB Browser vytvoril takýto kód, ktorý si rozoberieme.

CREATE TABLE "users" (
    "user_id"   INTEGER,
    "first_name"    TEXT,
    "last_name" TEXT,
    "birth_date"    TEXT,
    "article_count" INTEGER,
    PRIMARY KEY("user_id" AUTOINCREMENT)
);

Príkaz začína frázou CREATE TABLE, čo ako asi viete, označuje, že chceme vytvoriť tabuľku. Potom nasleduje meno tabuľky, ktoré je v SQLite obvyklé dávať do dvojitých úvodzoviek. Do zátvorky sa píšu názvy jednotlivých stípcov s ich dátovými typmi a prípadne aj ďalšími atribútmi, ako napr. tu PRIMARY KEY a AUTOINCREMENT, a oddeľujú sa čiarkou. Každý príkaz v SQL zakončujeme bodkočiarkou, ktorá ani tu nechýba.

V SQL sa väčšinou píšu príkazy veľkými písmenami, to preto, že ich to lepšie odlíši od zvyšku dotazu alebo od kódu našej aplikácie (napr. v PHP). Názvy tabuliek, stípcov a ďalšie identifikátory sú naopak malými písmenami a podčiarkovovou notáciou. Je dobrým zvykom ich vkladať medzi dvojité úvodzovky (prípadne ďalšie podporované znaky, ako spätné úvodzovky a podobne), ale nie sú povinné a príkazy prebehnú bez problémov aj bez nich.

Spustenie SQL dotazu

Najprv odstránime tabuľku users. Klikneme na nej a zvolíme možnosť Delete Table. Potom prejdeme na panel Execute SQL a do textového poľa hore napíšeme SQL dotaz pre vytvorenie tabuľky, ktorý sme si pred chvíľkou vysvetlili. Po kliknutí na šípku sa nám nižšie zobrazí hláška, že dotaz prebehol úspešne, to si môžeme skontrolovať aj v paneli Database Structure, kde uvidíme úplne to isté, ako po vytvorení tabuľky naklikaním:

Keby sme chceli odstrániť tabuľku užívateľov pomocou SQL dotazu, tak spustíme nasledujúci príkaz:

DROP TABLE "users";

Tento príkaz by sa dal preložiť ako "Vyhoď tabuľku users", viac asi nie je potrebné dodávať.

Zadávanie SQL dotazov v DB Browseri má tú výhodu, že jednak vám kľúčové slová napovedá, ale tiež, že môžete písať viac SQL dotazov pod sebou a spúšťať ich jednotlivo pomocou symbolu dvojitej šípky, alebo klávesovou skratkou Ctrl+E.

V budúcej lekcii, SQLite - Vkladanie a mazanie dát v tabuľke, si naplníme databázu nejakými dátami