Lekce 1 - PostgreSQL - Úvod a příprava prostředí

Vítejte u prvního dílu seriálu tutoriálů k databázi PostgreSQL.

Seriál je určen pro všechny, co se ještě s databázemi nesetkali a potřebují s nimi pracovat v dalším jazyce (např. v PHP na webu nebo v Javě v desktopové aplikaci). Smyslem je naučit vás základy jazyka SQL, které jsou podobné ve všech SQL databázích (PostgreSQL, MySQL, MS-SQL, SQLite...). Postupně si ukážeme vytváření tabulek, vkládání dat, jejich modifikaci a nakonec i výběr, vyhledávání a komplikovanější dotazy přes více tabulek.

Pracovat budeme pouze se samotnou databází a bez dalšího programovacího jazyka. Nebude to ale tak suché, jak to zní, protože budeme používat grafické rozhraní pgAdmin. Ukážeme si, jak věci naklikat (ty které jdou) a jak to samé zapsat i jako SQL dotaz. Z grafického rozhraní hezky pochopíte co přesně děláte a poté si to spojíte s SQL příkazem. Nabyté znalosti můžete využít v kterémkoli jazyce pro práci s PostgreSQL databází (např. v PHP či v Javě) a s minimální úpravou i s kteroukoli jinou SQL databází. Po dokončení tohoto seriálu budete schopni pracovat s databází na takové úrovni, abyste dokázali vytvořit jednoduchý redakční systém nebo podobnou aplikaci.

Relační databáze

PostgreSQL je tzv. relační databáze. Tento pojem označuje databázi založenou na tabulkách. Každá tabulka obsahuje položky jednoho typu. Můžeme mít tedy tabulku uzivatele, další tabulku clanky a další třeba komentare.

Databázovou tabulku si můžeme představit třeba jako tabulku v Excelu. Tabulka uzivatele by mohla vypadat asi takto:

Položky (konkrétně zde uživatelé) ukládáme na jednotlivé řádky, sloupce pak označují atributy (vlastnosti, chcete-li), které položky mají. PostgreSQL databáze je typovaná, to znamená, že každý sloupec má pevně stanovený datový typ (číslo, znak, krátký text, dlouhý text...) a může obsahovat hodnoty jen tohoto typu. Pokud chceme s relační databází rozumně pracovat, každý řádek v tabulce by měl být opatřený unikátním identifikátorem. U uživatelů by to mohlo být třeba rodné číslo, mnohem častěji se však používají identifikátory umělé a to tak, že uživatele prostě očíslujeme. K tomu se dostaneme později.

Slovo relační označuje vztah (anglicky relation). Ten je mezi tabulkami nebo mezi entitami v jedné tabulce. To si ale necháme na jindy a zatím budeme pracovat jen s jednou tabulkou zároveň.

RDBMS

Možná vás napadlo, k čemu vlastně potřebujeme nějakou databázi. Data bychom stejně dobře mohli ukládat do nějakých textových souborů, binárek, XML nebo něčeho podobného. Určitě by to nějak fungovalo, nebo ne?

Označení databáze je vlastně nepřesné a v odborné literatuře se setkáme s označením RDBMS (Relation DataBase Management System). Česky je to přeloženo jako "systém řízení báze dat", což zní opravdu hrozně a proto budu dále používat označení databázový stroj nebo RDBMS.

Databázový stroj (tedy zde PostgreSQL) není jen úložiště dat. Jedná se o velmi sofistikovaný a odladěný nástroj, který za nás řeší spoustu problémů a zároveň je extrémně jednoduchý k použití. S databází totiž komunikujeme jazykem SQL, kterým jsou v podstatě lidsky srozumitelné věty.
Spolu s ukládáním dat je ale třeba dále řešit mnoho dalších věcí. Asi by nás napadlo např. zabezpečení nebo optimalizace výkonu. RDBMS toho ale dělá ještě mnohem více, řeší za nás problém současné editace stejné položky několika uživateli ve stejný okamžik, který by jinak mohl zapříčinit nekonzistenci databáze. RDBMS data v tomto případě zamkne a odemkne až po vykonání zápisu. Dále umožňuje spojovat několik dotazů do transakcí, kdy se série dotazů vykoná vždy celá nebo vůbec. Nestane se, že by se vykonala jen část. Tyto vlastnosti databázového stroje jsou shrnovány zkratkou ACID, pojďme si ji vysvětlit.

ACID

ACID je akronym slov Atomicity (nedělitelnost), Consistency (validita), Isolation (izolace) a Durability (trvanlivost). Jednotlivé složky mají následující význam:

• Atomicity - Operace v transakci se provedou jako jedna atomická (nedělitelná) operace. Tzn. že pokud nějaká část operace selže, vrátí se databáze do původního stavu a žádné části transakce nebudou provedeny. Reálný příklad je např. převod peněz na bankovním účtu. Pokud se nepodaří peníze odečíst z jednoho účtu, nebudou ani připsány na účet druhý. Jinak by byla databáze v nekonzistentním stavu. Pokud bychom si práci s daty řešili sami, mohlo by se nám toto velmi jednoduše stát.
• Consistency - Stav databáze po dokončení transakce je vždy konzistentní, tedy validní podle všech definovaných pravidel a omezení. Nikdy nenastane situace, že by se databáze nacházela v nekonzistentním stavu.
• Isolation - Operace jsou izolované a navzájem se neovlivňují. Pokud se sejde v jeden okamžik více dotazů na zápis do stejného řádku, jsou vykonávány postupně, jako ve frontě.
• Durability - Všechna zapsaná data jsou okamžitě zapsána na trvanlivá úložiště (na pevný disk), v případě výpadku el. energie nebo jiného přerušení provozu RDBMS vše zůstane tak, jak bylo těsně před výpadkem.

Databáze (přesněji databázový stroj) je tedy černá skříňka, se kterou naše aplikace komunikuje a do které ukládá veškerá data. Její použití je velmi jednoduché a je odladěna tak, jak bychom si sami zápis dat v programu asi těžko udělali. Vůbec se nemusíme starat o to, jak jsou data fyzicky uložena, s databází komunikujeme pomocí jednoduchého dotazovacího jazyka SQL, viz dále. V dnešní době se vůbec nevyplatí zatěžovat se otázkou ukládání dat, jednoduše sáhneme po hotové databázi, kterých je obrovský výběr a jsou většinou zadarmo. O databázi občas hovoříme jako o 3. vrstvě aplikace (1. vrstva je uživatelské rozhraní, 2. vlastní logika aplikace, 3. je právě datová vrstva).

Potřebné nástroje

Začněme tedy. Potřebovat budeme databázi PostgreSQL a pgAdmin, což je grafické uživatelské rozhraní pro administraci databáze. Instalační program PostgreSQL, součástí kterého je i nejnovější verze prostředí pgAdmin, si můžeme stáhnout z oficiálních stránek.

Instalace

Stáhneme si tedy ideálně nejnovější verzi PostgreSQL pro náš systém a nainstalujeme jej. Instalace je jednoduchá a uživatelsky přívětivá. Po vybrání složky pro instalaci nám bude nabídnut výběr komponent, kromě samotné PostgreSQL i pgAdmin a další. Necháme všechny vybrané:

V dalších krocích vybereme adresář pro data, nastavíme heslo, port (výchozí je 5432) a jazyk (můžeme nechat výchozí).

Hlavně si zapamatujte heslo, které budete během instalace zadávat.

Po dokončení instalace se spustí nástroj Stack Builder pro instalaci dodatečného software. Zde není potřeba vybírat nic, vše potřebné je již nainstalováno.

Lekce jsou aktualizované na PostgreSQL ve verzi 13+ a pgAdmin 4 ve verzi 5+. Je možné, že v novějších verzích se postupy budou lehce lišit.

pgAdmin

pgAdmin je nejpoužívanější grafické prostředí pro práci s PostgreSQL databází. Otevřeme si ho:

Dvojklikem na nabídku se servery se zobrazí okno pro zadání hesla, které jsme zadávali v průběhu instalace:

Po úspěšném připojení je nám celá databáze k dispozici. Zatím sice máme jen jednu s názvem postgres, ale můžeme jich mít kolik chceme:

Základní pojmy

• schéma (schema) - Schémata jsou základní organizační jednotkou databáze. Pokud bychom si databázi představili jako stát, tak schémata by byly kraje. PostgreSQL automaticky při vytváření nové databáze vytváří schéma s názvem "public". Vše co vytvoříte umístí PostgreSQL do tohoto schéma, pokud mu neřeknete jinak. V rámci jednoduché databáze, která obsahuje pouze několik tabulek, je zcela v pořádku umístit vše do jednoho schéma. Pokud ale budete mít databázi se stovkami či tisíci tabulek, je určitě vhodné vytvořit více schémat a tabulky si zorganizovat.
• katalog (catalog) - Katalogy jsou systémová schémata. Jakékoliv informace o databázi a jejích objektech naleznete zde.
• role (role) - PostgreSQL používá role pro řízení přístupových práv k databázi. Roli si můžeme představit jako databázového uživatele (nebo jako skupinu uživatelů). Role, které se mohou přihlásit, se nazývají přihlašovací role (login roles). Role, které mohou obsahovat další role, se nazývají skupinové role (group roles). Když instalujete PostgreSQL, vytvoří se role postgres, pomocí které se můžete přihlásit.

V další lekci, PostgreSQL - Vytvoření databáze a tabulky, si vytvoříme svou první databázi a v ní i nějakou tabulku.