AP10, IN10 SQL

SQL (syntaxe a sémantika příkazů; vestavěné funkce, triggery, uložené procedury, příkazy pro definici dat; transakční zpracování; atomické operace; optimalizace dotazů)

• SQL (dříve SEQUEL) – Structured Query Language. Jedná se o standardizovaný dotazovací databázový jazyk, jehož vývoj byl započat firmou IBM a který má několik verzí standardů, které postupem času vznikly.
• ANSI – americký institut, který vydává standardy pro jazyk SQL.
• SQL:1999 (nebo SQL-3) – standard pro jazyk SQL, který obsahuje objektové prvky. V praxi se tato norma ne vždy plně implementuje a zpravidla bývá rozšiřována prvky, které jsou pro každý databázový systém (DBMS) specifické.
• SQL:2008 – nejnovější norma SQL. Možno použít ORDER BY vně kurzorů, přidává triggery typu INSTEAD OF a příkaz TRUNCATE.
• Transakce – pojem, který označuje posloupnost operací (část programu, sekvenci příkazů jazyka SQL), která přistupuje a aktualizuje (mění) data. Transakce pracuje s konzistentní databází, během spouštění transakce může být databáze v nekonzistentním stavu, ale po úspěšném dokončení transakce musí být databáze konzistentní. (více v samostatné části níže)
• DDL (Data Definition Language) – podjazyk pro definici dat, který slouží pro specifikaci definice schématu databáze
• DML (Data Manipulation Language) – podjazyk pro manipulaci s daty, který slouží pro práci s obsahem tabulek
• DCL (Data Control Language) – podjazyk pro řízení práce s daty, který obsahuje správu transakcí, nastavení práv atp.
• Syntax – je dobrou zvyklostí psát příkazy velkými písmeny a názvy objektů obalovat speciálními znaky, aby tak nedocházelo ke kolizím mezi názvy objektů a rezervovanými klíčovými slovy (tzv. escaping). U MS SQL Serveru jsou těmito znaky hranaté závorky, v případě PostgreSQL uvozovky, MySQL používá zpětné apostrofy. Za každým příkazem může a nemusí být oddělující středník. Řádkové komentáře realizujeme dvěma pomlčkami, blokové klasicky lomítkem a hvězdičkou.

Jazyk SQL obsahuje i vestavěné funkce, mezi ně patří především tzv. agregační funkce, ale dále do nich patří např. funkce pro práci s řetězci (SUBSTRING, LOWER atd.), pro zjištění aktuálního data a času atd. Množina dostupných funkcí je hodně závislá na zvoleném DBMS, kterým může být Oracle, Postgresql, SQL Server atd.

• COUNT – počet záznamů ve vrácené množině. Aplikuje-li se ve formě „COUNT(*)“, tak je vrácen počet všech záznamů; pokud je aplikován ve tvaru „COUNT(nazev_sloupce)“, tak je vrácen počet záznamů, které nemají ve sloupci „nazev_sloupce“ hodnotu NULL.
• AVG – průměr dané hodnoty ve všech záznamech.
• SUM – součet všech hodnot daného sloupce.
• MIN, MAX – minimální, resp. maximální, hodnota daného sloupce v záznamech.

Definuje sadu příkazů, které lze použít pro vytváření, úpravu a odstraňování objektů v databázi. Nejčastěji spravovanými objekty jsou: tabulky, pohledy, procedury, funkce.

• CREATE – vytvoření objektu.
• ALTER – úprava. V případě změny tabulky se může jednat o přidání/úpravu/odstranění sloupce, změny datového typu sloupce, změny relace a jiných integritních omezení. Příklad přidání sloupce do tabulky: ALTER TABLE [Nazev_Tabulky] ADD COLUMN [sValue3] Boolean.
• DROP – odstranění. Příklad odstranění procedury: DROP PROCEDURE [Procedura1].

Množina příkazů, které se využívají pro výběr, vkládání, úpravu a mazání dat v tabulkách.

• INSERT – vloží záznam do právě jedné tabulky.
• UPDATE – upraví libovolný počet záznamů v právě jedné tabulce.
• DELETE – odstraní libovolný počet záznamů z právě jedné tabulky.
• SELECT – vybírá data z databáze, umožňuje výběr, agregaci a řazení dat. (odpovídá operaci projekce v relační algebře)

Zpravidla se jedná o příkazy, které slouží k řízení transakcí, nastavení práv a jiné.

• BEGIN – spustí transakci s uvedeným názvem, který není povinný. Často se ještě uvádí úroveň izolace, která je použitá během zpracování příkazů kvůli předejití konkurenčním operacím. Příklad: BEGIN TRAN [MyTran1].

• COMMIT – potvrdí transakci. Příklad: COMMIT TRAN [MyTran1]
• ROLLBACK – vrátí provedené změny. Může se rušit jak celá transakce, tak její část od určitého bodu až po chvíli, kdy je ROLLBACK volán (viz. SAVE). Příklad: ROLLBACK TRAN [MyTran1]
• SAVE – tento příkaz není součástí standardu, ale většina db systémů jej podporuje. Ukládá stav transakce, do něhož je možné se vrátit, pokud není žádoucí vracet úplně celou transakci. Příklad: SAVE TRAN [MyCheckpoint1].

Atomická operace znamená, že její vykonání je nedělitelné – buď proběhne zcela úspěšně, nebo zcela neúspěšně. Atomickými operacemi jsou například: „vlož jeden záznam“, „zobraz jeden záznam“, apod.

Oproti tomu, vykonání neatomické operace je dělitelné: dojde-li při vykonávání příkazu k chybě, operace, které již byly vykonány, nebudou vráceny zpět. To se týká například operací, které mají vliv na větší množství řádků.

Je posloupnost operací (DML příkazů), které převedou datové schéma z jednoho konzistentního stavu do druhého.

O transakci platí, že je ACID:

• A: Atomic – celá se provede, nebo se odvolá, žádné zbytky nebo vedlejší efekty.
• C: Consistent – na konci není porušeno žádné omezení databáze.
• I: Isolated – operace jsou izolovány od ostatních transakcí.
• D: Durable – po ukončení transakce jsou data trvale uložena.

Každá transakce má určitou úroveň izolace. Pokud se při vytvoření nespecifikuje, použije se výchozí úroveň.

Více transakcí může být spouštěno současně. Výhody jsou:

• Zvýšené využití procesoru a disku, které vede k vyšší transakční propustnosti (jedna transakce může používat procesor a jiná disk).
• Snížená průměrná doba odezvy (krátká transakce nemusí čekat na dokončení dlouhé). ¹⁾

Nevýhody:

• Aby se předešlo problému s přepisováním dat, je nutné zamykání řádků či tabulek.
• Je nutná režie na řešení a předcházení deadlocku.

Neboli uváznutí, označuje stav, kdy transakce A čeká na prostředky, které drží transakce B a transakce B čeká na prostředky, které má uvolnit transakce A. Vždy se reší zaříznutím jedné z transakcí, nelze čistě vyřešit a je nutné jim předcházet.

Neopakovatelné čtení označuje stav, kdy si jedna transakce přečetla záznam, provedla nějaké příkazy a pak si tento záznam přečetla znovu a už měl jiné hodnoty.

Fantom označuje data, která na první pohled „záhadně“ unikají aktualizacím.

Jsou databázové objekty, které neobsahují data, ale programy, které se nad daty v databázi mají vykonávat. Mají své parametry a lokální proměnné, které nejsou zvenku vidět.

Uložená procedura je uložená (rozuměj: uložená v databázi). To znamená, že se k ní lze chovat stejně jako ke každému jinému objektu databáze (indexu, pohledu, triggeru apod.). Lze jí založit, upravovat a smazat pomocí příkazů dotazovacího jazyka databáze.

Pro psaní uložených procedur je obvykle používán specifický jazyk konkrétní databáze, který je rozšířením jejího dotazovacího jazyka. V případě Oraclu to je procedurální jazyk PL/SQL, u SQL Serveru se jedná o T-SQL.

Jedná se o uloženou sadu SQL příkazů, kterou lze parametrizovat. Má návratový typ a často se požaduje, aby byly deterministické. V praxi se to projevuje tak, že nesmí obsahovat prvky nedeterminismu jako například aktuální datum a čas atp.

Funkce je objekt jako každý jiný, takže ji lze spravovat pomocí jazyka DDL.

V databázi specifikuje činnosti, které se mají provést v případě definované události nad databázovou tabulkou. Definovanou událostí může být například vložení nebo smazání dat. Často slouží pro složitější kontrolu integrity dat.

Triggery jako takové jsou definovány ve většině moderních databázových systémů, ovšem mírně se liší v sémantice svého provedení.

Klíčové rozdíly jsou zejména v:

• kdy přesně se trigger spustí
• jak proběhne (co ho může přerušit)
• jakým způsobem se řeší vzájemné volání triggerů, pokud je vůbec umožněno
• jak (a jestli vůbec) jsou ošetřeny nekonečné cykly vzájemného volání ²⁾

• událost je UPDATE, DELETE nebo INSERT, případně jejich kombinace (zpravidla oddělené klíčovým slovem OR)
• BEFORE triggery jsou spouštěny před provedením události, AFTER triggery až po provedení události
• Trigger je spuštěn buďto jednou při provádění příkazu (default) nebo pro každý měněný řádek (FOR EACH ROW)
• Podmínky: INSERTING, UPDATING, DELETING ³⁾

Robin Hood osobně používal tento Trigger

CREATE TRIGGER uprav_platy 
BEFORE 
UPDATE OF plat 
ON people 
FOR EACH ROW 
BEGIN 
  IF (:NEW.plat>30000) THEN :NEW.plat:=28000; END IF; 
  /* prilis by se namlsali */ 
  IF (:NEW.plat<12000) THEN :NEW.plat:=15000; END IF; 
  /* a chudym pridame */ 
END; 

Existuje celá řada způsobů, jakými lze ovlivnit rychlost zpracování dotazu, níže je seznam těch základních.

• indexy – nejdůležitější technika urychlení dotazů, systém si vede informace o záznamech v podobě stromu
• vyjmenování sloupců – typický dotaz „SELECT * FROM [Products]“ vybere všechny sloupce z tabulky, které možná ani nepotřebujeme, ale přinese také zpomalení z toho důvodu, že systém si musí nejdříve vytáhnout seznam sloupců z definice tabulky, aby je mohl zobrazit. Navíc takový dotaz není odolný proti změnám schématu tabulky.
• použití operátoru LIKE – Obecně není vhodné operátor používat ve tvaru LIKE '%Karel%', v tom případě musí databáze projít všechny zbývající výskyty. To při omezení vhodnou podmínkou nemusí vadit. Tvar LIKE 'Karel%' je naprosto v pořádku a indexy v tomto případě fungují, stejně tak tvar bez procent.
• dočasné tabulky – jejich použití může přinést výrazné zpomalení, pokud nejsou používány s citem a manipuluje se v nich s velkým množstvím dat.

Většina dnešních nástrojů nabízí možnost zobrazení plánu provedení dotazů, který velice usnadní jejich ladění.

Pohled je databázový objekt, který uživateli poskytuje data ve stejné podobě jako tabulka. Na rozdíl od tabulky, kde jsou data přímo uložena, obsahuje pohled pouze předpis, jakým způsobem mají být data získána z tabulek a jiných pohledů.

Takto se nazývá speciální varianta pohledů, u nichž jsou výsledky volání ukládány. Rozdíl oproti normálním pohledům je ten, že při opakovaném volání přináší znatelné urychlení a navíc je možné v nich upravovat data. Podmínkou úpravy je to, že v pohledu musí být vybrány všechny sloupce primárního klíče tabulky, jejíž data aktualizujeme. Zpravidla se aktualizují pohledy nad jednou tabulkou, aby byly úpravy průhledné a nedocházelo k nechtěným změnám. Tuto možnost podporuje například Oracle, SQL Server už nikoliv.

Moderní databázové systémy většinou umožňují integraci klasických programovacích jazyků do procedur, funkcí a triggerů, takže tímto způsobem je možné s výhodou využít například C++ nebo Javu tam, kde je SQL slabé.

Existují 4 druhy úrovní izolace dle standardu:

• Read Uncommitted – nejvíce tolerantní. Nepovoluje souběžné aktualizace, ale povoluje čtení doposud nepotvrzených změn.
• Read Committed – méně tolerantní, nepovoluje souběžné aktualizace ani čtení nepotvrzených změn. S jejím použitím lze dosáhnout vysoké propustnosti v databázi, ale při jejím použití nelze vyloučit neopakovatelné čtení a fantomy (viz. pojmy výše).
• Repeatable Read – má stejné vlastnosti jako Read Commited jen s tím rozdílem, že podporuje stabilní pohled na čtená data (tj. zamezuje neopakovatelnému čtení), ale stále mohou vznikat fantomy.
• Serializable – nejvyšší úroveň zabezpečení a blokování použitých prostředků. V případě častého používání transakcí se serializovatelným čtením může docházet k uváznutí a mají vliv na výkon systému, protože k jejich zpracování je třeba značené režie ze strany databázového systému.

• Indexy (jako techniky urychlení dotazů) byste měli být schopni více rozvést na základě znalostí z otázky Organizace souborů.
• Měli byste být schopni zadefinovat syntax SQL pomocí nějakého formálního jazyka (v podstatě popsat gramatiku pro SQL).

• FI:PV003 Architektura relačních databázových systémů (jaro 2006), RNDr. Milan Drášil, CSc.
• FI:PV063 Aplikace databázových systémů (jaro 2006), RNDr. Pavel Hajn
• FI:PB154 Základy databázových systémů (podzim 2007), prof. Ing. Pavel Zezula, CSc.

• Wikipedie - Spouště
• Wikipedie - Pohledy
• Wikipedie - Uložené procedury

• Osnova předmětu PB154 a z ní odkazované dokumenty k předmětu PB154 (česky přeložené dokumenty jsou jen pro přihlášené v IS MU)
• Kapitola 13: Transakce přístupný jen pro přihlášené v IS MU, autorem je prof. Ing. Pavel Zezula, CSc.
• Kapitola 4: SQL přístupný jen pro přihlášené v IS MU, autorem je prof. Ing. Pavel Zezula, CSc.

Petr Koutný, ICQ: 223-009-182. Nemám patent na rozum, takže klidně něco upravte, pokud vám to přijde nesmyslný

Otázku si přečetl pan RNDr. Vlastislav Dohnal a rámcově prošel. Jeho podněty pro doplnění textu, opravy nesrovnalostí a odstranění matoucích či k otázce se nevztahujících textů byly do otázky zaneseny. Tato kontrola je jen rámcová, stále se může stát, že v otázce zůstala zapomenutá chybka či nesrovnalost, vyučující za toto nenese odpovědnost, berte tuto rámcovou kontrolu jako formu pomoci od vyučujících pro studenty.