Umíme to s Delphi: 92. díl – komponenty Interbase a tvorba SQL dotazů

Dnešní díl seriálu je zaměřen na popis základních komponent pro práci s databázovými platformami Interbase/Firebird. Vysvětlíme si i vztah některých Interbase komponent k BDE. V závěru článku se naučíme vytvářet SQL dotazy, vysvětlíme si význam klíčového slova JOIN a poznáme, jak získávat data z většího počtu databázových tabulek.
V předchozích dílech seriálu jsme si vysvětlili základy práce s databázovou platformou Interbase/Firebird. Umíme založit databázi, známe princip vytváření databázových tabulek a víme, jak tabulky naplnit daty. Naprogramovali jsme též jednoduchou Interbase aplikaci v Delphi.

Než si ukážeme některé obtížnější a pokročilejší příklady, musíme si podrobně vysvětlit, jakými způsoby je možné v Delphi s databázemi Interbase/Firebird pracovat. Jak jsme již poznali, paleta komponent Delphi obsahuje záložku Interbase s komponentami, díky nimž je možné k Interbase/Firebird databázím přistupovat přímo, bez nutnosti využívat nějaké rozhraní (např. ODBC). Díky tomu je práce s těmito platformami v Delphi rychlá a umožňuje relativně široké spektrum služeb.

Na komponenty ze záložky Interbase se podíváme právě dnes.

Databázové komponenty Interbase – obecné informace

Databázové komponenty Interbase jsou vesměs odvozeny od základní třídy TDataSet. Je snad zbytečné zdůrazňovat, že mohou používat všechny databázové ovládací prvky (Data Controls), podporují editor datových položek a mnoho vlastností lze nastavovat klasicky, tedy v době návrhu. Tyto komponenty samozřejmě lze používat v editoru datových modulů (Data Module Designer).

Protože jsme se v předchozích dílech seriálu často zabývali rozhraním BDE a komponentami souvisejícími s BDE, pokusím se některé komponenty ze záložky Interbase přirovnat k odpovídajícím komponentám Interbase. Nejdůležitější komponentou je zřejmě IBTable, která odpovídá komponentě Table ze záložky BDE. Analogickou podobnost najdeme také mezi komponentami IBQuery z Interbase a Query z BDE. Pokud jste se při tvorbě aplikací dostali až k návrhu uložených procedur (Stored Procedures), používali jste zřejmě v BDE komponentu StoredProc a nyní v Interbase použijete (aniž to někoho překvapí) IBStoredProc. Tyto tři uvedené komponenty jsou k dispozici především z důvodu určité kompatibility (či spíše snazší přenositelnosti) mezi aplikacemi používajícími BDE a aplikacemi pracujícími s Interbase.

U nových aplikací se doporučuje používání komponenty IBDataSet. Ta totiž umožňuje pracovat s živou množinou záznamů – výsledků dotazu SELECT (viz níže).

I některé další Interbase komponenty mají svůj protějšek u BDE, například komponenta IBDatabase napodobuje komponentu BDE Database. Pokud jste však v BDE vytvářeli jen jednodušší aplikace, komponentu Database jste pravděpodobně nepotřebovali: v BDE aplikacích ji není třeba explicitně uvádět.

Komponenty Interbase – přehled

Následující přehled by měl sloužit jako stručná referenční příručka komponent ze záložky Interbase.

Komponenta IBTable

IBTable reprezentuje data z jedné Interbase tabulky nebo pohledu. Jedná se o kopmponentu z vrstvy DataSet, která zapouzdřuje jednu (zvolenou) databázovou tabulku. Pokud potřebujeme zpřístupnit data právě z jedné tabulky nebo pohledu, je IBTable vhodným kandidátem – poskytuje totiž přímý přístup ke každému záznamu (řádce) a položce (poli) příslušné tabulky. Vzhledem k možnosti používat filtry (vlastnost Filter) umožňuje komponenta IBTable pracovat také s podmnožinou (s vybranými řádky) dané tabulky.

Důležitá je vlastnost TableName, která indikuje jméno databázové tabulky nebo pohledu, jenž má být v IBTable zapouzdřen(a). Zapomenout však nesmíme ani na vlastnost Database, která identifikuje databázi, s níž pracujeme. Tato identifikace je realizována opět databázovou komponentou – jinak řečeno, hodnotou vlastnosti Database je název komponenty (např. IBDatabase1), která zapouzdřuje používanou Interbase databázi.

Při práci s komponentou IBTable (a vůbec s databázovými aplikacemi Interbase) je nutné nastavit také vlastnost Transaction – každá operace s Interbase databází totiž musí probíhat právě v rámci jedné transakce (viz níže). Podobně jako u vlastnosti Database, i hodnotou vlastnosti Transaction je název komponenty (např. IBTransaction1).

Zdůrazněme dále (snad již dostatečně) známou věc, že aby komponenta IBTable začala skutečně poskytovat „skutečná“ data, je nutné nastavit vlastnost Active na True.

Komponenta IBQuery

IBQuery je dalším zástupcem komponent z vrstvy dataset (datová sada). Používá SQL příkazů k získání dat z jedné nebo více tabulek Interbase databáze. IBQuery způsobí spuštění (provedení) příslušného dotazu a nabízí k dispozici jeho výsledky.

Tato komponenta poskytuje tzv. „read-only dataset“, tedy datovou sadu určenou pouze pro čtení. Jinak řečeno – výsledek dotazu nelze modifikovat. Pokud potřebujeme modifikovat výsledek dotazu získaného pomocí IBQuery, je nutné použít spolupráci s komponentou IBUpdateSQL.

Komponenty umožňující získat data prostřednictvím databázového dotazu jsou velmi užitečné a poskytují mnohem vyšší flexibilitu než komponenty zapouzdřující jednu konkrétní databázovou tabulku. Dokumentace k Delphi zmiňuje následující důvody, proč jsou dotazové komponenty výhodné:

  • V jednom okamžiku mohou poskytovat data z více tabulek zároveň. Odborným termínem se jedná o relační operaci „spojení tabulek“, v jazyce SQL je realizována klíčovým slovem JOIN. Ukázky spojování tabulek v SQL uvedeme za okamžik.
  • Automaticky obsahuje možnost zpřístupnit (vrátit) jen podmnožinu řádků a sloupců v příslušné tabulce (příslušných tabulkách) na rozdíl od komponent vracejících vždy celou množinu řádků a sloupců. Jinak řečeno – databázový server může posílat menší objemy dat, čímž by měla vzrůst efektivita databázových operací. Jedná se opět o vlastnost databázového dotazovacího jazyka (SQL), příklady uvedeme opět záhy.

Poznámka: dokumentace k Delphi také uvádí, že IBQuery má svůj význam i při vývoji škálovatelných databázových aplikací. Pokud existuje jakákoliv, byť i velmi malá pravděpodobnost, že dnes vyvíjená (lokální) aplikace poběží v budoucnosti po síti (tedy bude se jednat o plnohodnotnou architekturu klient/server, kde server bude geograficky umístěn odděleně od klientů), je vhodné používat od samého počátku výhradně komponenty IBQuery. Vrátí se to v budoucnosti mnohem jednodušší možností škálování.

Mezi důležité vlastnosti musíme zařadit tradiční vlastnost Active, kterou musíme nastavit na True, abychom komponentu „přinutili k činnosti“. Nejdůležitější vlastností je však SQL, která obsahuje SQL příkaz, který má být proveden v okamžiku

  • zavolání metody ExecSQL nebo
  • zavolání metody Open nebo
  • nastavení vlastnosti Active na True.

V době návrhu samozřejmě můžeme vlastnost SQL editovat ve String List Editoru z Object Inspectoru. Důležité však je, že vlastnost SQL může v jednom okamžiku obsahovat jen jeden SQL dotaz.

Zapomenout nesmíme ani na dvě tradiční vlastnosti – Database a Transaction. Jejich význam je zcela stejný jako v případě komponenty IBTable (viz výše).

Zajímavá je také vlastnost DataSource, její význam je stejný jako význam vlastnosti DataSource, kterou jsme si podrobně popisovali v dílu věnovaném analogické komponentě v BDE. Potřebujeme ji v případě, kdy chceme v SQL dotazu používat parametry (vlastnost Params).

Nyní se pojďme alespoň ve stručnost vrátit ke zmíněným výhodám dotazových komponent. Ukážeme si, jak tyto výhody v praxi vypadají (tedy jak se promítnou v databázových dotazech).

Chceme-li získat data z více tabulek, je nutné v příkazu SELECT vyjmenovat všechny příslušné tabulky. Je možné používat dvou způsobů, staršího a novějšího.

Dejme tomu, že máme (pro jednoduchost) dvě tabulky (Zamestnanec a Oddeleni) s následujícími daty:

Tabulka Zamestnanec

Jmeno IDoddel
Josef Novák 2
Petr Vopička 4

Tabulka Oddeleni

Nazev IDoddel
Odbyt 2
Návrh 4

Starší způsob tvorby SQL dotazů vypadá na první pohled jednodušeji, například:

SELECT Jmeno, Nazev FROM Zamestnanec, Oddeleni

Ovšem pozor – takto formulovaný dotaz vrátí ohromné množství záznamů, a to i nesmyslných. (z hlediska kontextu a v kontrastu s naším očekáváním). V předchozím případě by dotaz vrátil následující čtveřici záznamů:

Josef Novák, Odbyt
Petr Vopička, Odbyt
Josef Novák, Návrh
Petr Vopička, Návrh

Musíme totiž specifikovat podmínku, při jejímž splnění chceme spojení provést. Neuvedeme-li ji (jako v předchozím případě), dojde k úplnému (kompletnímu) spojení a výsledkem dotazu budou záznamy typu „každý s každým“. Proto dotaz vylepšíme:

SELECT Jmeno, Nazev FROM Zamestnanec, Oddeleni WHERE Zamestnanec.IDoddel = Oddeleni.IDoddel

Nyní již bude výsledek dotazu „v pořádku“, tedy dostaneme dva záznamy:

Josef Novák, Odbyt
Petr Vopička, Návrh

Co kdybychom však chtěli vrátit jen ty zaměstnance, kteří pracují v oddělení Odbyt? Pak musíme specifikovat tzv. omezující podmínku dotazu (ponecháme teď stranou, že bychom museli znát ID jednotlivých oddělení a soustředíme se jen na konstrukci dotazů):

SELECT Jmeno, Nazev FROM Zamestnanec, Oddeleni WHERE Zamestnanec.IDoddel = Oddeleni.IDoddel AND Zamestnanec.IDoddel = 2

Výsledkem takového dotazu bude jediný záznam:

Josef Novák, Návrh.

Podíváte-li se na předchozí dotaz, spatříte, že za klíčovým slovem WHERE se nacházejí dvě omezující podmínky:

Zamestnanec.IDoddel = Oddeleni.IDoddel
Zamestnanec.IDoddel = 2

Obě tyto podmínky ovlivňují výsledek dotazu (i když v tomto jednoduchém případě s minimem dat je ten vliv poněkud sporný). Přesto je však každá z nich postavena na jiném principu a vznikla z jiného popudu:

  • podmínka první (IDoddel = IDoddel) definuje podmínku, při níž má dojít ke spojení dvou databázových tabulek. Z každé tabulky chceme vrátit jen takové záznamy, které mají svůj protějšek (specifikovaný zde operací =) v jiné tabulce. Na hodnotě záznamů nezáleží, záleží jen na tom, splňují-li určitou podmínku související s jinou tabulkou. Jedná se tedy o podmínku spojení, nikoliv o omezení dat.
  • podmínka druhá (IDoddel = 2) naopak definuje podmínku, za níž nás příslušný záznam zajímá. Zde je nám naopak lhostejné, jaká je situace v druhé tabulce (a zda má nastat spojení): důležité pro nás je, aby hodnota určité datové položky byla 2. Jedná se tedy o omezující podmínku dat, nikolic o podmínku spojení.

Tento rozdíl je nakonec z uživatelského hlediska nepodstatný, ale z hlediska programátora (či spíše „návrháře dotazů“) je velmi důležitý, protože jeho uvědomění je důkazem dobrému porozumění problému a dobrého sepětí s modelem systému. Proto se spíše doporučuje používat druhý formát dotazů, který používá klíčové slovo JOIN (touto otázkou jsme se již před časem v seriálu velmi stručně zabývali). Přepišme poslední dotaz do této druhé formulace:

SELECT Jmeno, Nazev FROM Zamestnanec JOIN Oddeleni ON Zamestnanec.IDoddel = Oddeleni.IDoddel WHERE Zamestnanec.IDoddel = 2

Vidíme, že z tohoto zápisu (který způsobí vrácení stejných výsledků jako předchozí způsob) je na první pohled patrné, že spojujeme tabulky Zamestnanec a Oddeleni a že podmínkou spojení je Zamestnanec.IDoddel = Oddeleni.IDoddel. Dále je vidět, že z takto získaných výsledků dotazu nás zajímají (a mají být vráceny) jen ty, u nichž platí Zamestnanec.IDoddel = 2.

Uvádění klíčového slova JOIN má i mnoho dalších výhod, které si na tomto jednoduchém datovém modelu nemůžeme předvést. Představte si například situaci, v níž máme definovaného zaměstnance, který nepracuje v žádném oddělení. Díky JOIN (a díky konstrukci tzv. vnějších spojení, tzv. OUTER JOIN) je velmi jednoduché získat jména právě takových „bezprizorních“ zaměstnanců.

U jednoduchých dotazů je vcelku lhostejné, který zápis použijete. U složitějších dotazů „táhnoucích“ se přes větší množství databázových tabulek však začíná u tradičního zápisu (bez JOIN) hrozit ztráta orientace v rozsáhlém bloku WHERE. V takovém případě asi stůjí za úvaho přepsat dotaz s užitím JOIN.

Poslední maličkost, kterou bych rád pro úplnost dodal, je omezování výsledků dotazu ne určité sloupce zdrojové tabulky (zdrojových tabulek). Už v předchozím textu jste si mohli všimnout, jak tento proces funguje – požadované sloupce jednoduše vypíšeme za klíčové slovo SELECT. Pokud by mohla v názvech sloupců z více tabulek nastat duplicita, používá se tečková notace a uvádí se i názvy příslušných tabulek. Kdybychom například název oddělení měli ve sloupci pojmenovaném také Jmeno, vypadal by dotaz takto:

SELECT Oddeleni.Jmeno, Zamestnanec.Jmeno FROM Zamestnanec JOIN Oddeleni ... atd.

Pokud jsou názvy tabulek dlouhé a potřebujeme je uvádět opakovaně, můžeme si nadefinovat jejich aliasy, např.:

SELECT O.Jmeno, Z.Jmeno FROM Zamestnanec Z JOIN Oddeleni O ... atd.

Nechceme-li výsledky dotazu omezovat na určité sloupce, použijeme namísto jmen sloupců hvězdičku:

SELECT * FROM Zamestnanec JOIN Oddeleni ON ... atd.

Na závěr

Dnešní díl seriálu začal popisovat jednotlivé komponenty ze záložky Interbase palety komponent. Rozebrali jsme dvě, velmi důležité komponenty – IBTable a INQuery. V závěru článku jsme si vysvětlili, jakým způsobem v SQL zajistit spojení více tabulek a jakým způsobem omezit výsledky dotazu jen na některé sloupce. Za týden budeme pokračovat – popíšeme si další komponenty z uvedené záložky palety komponent.

Témata článku: Software, Programování, Následující řádek, Důležitý sloupec, Jednoduchý princip, Klíčová vlastnost, Díl, Tvorba, Nepodstatný parametr, Komponenta, Novák, Stejný princip, Následující aplikace, Stejná metoda, Vnější vrstva, Sloupec, Získaný klíč, Význam, Spojení, Josef, Tradiční vlastnosti, Stejný dotaz, From, Zavolání, Druhý případ

Určitě si přečtěte

Velká podzimní aktualizace Windows 10 je tady: Co přináší Fall Creators Update

Velká podzimní aktualizace Windows 10 je tady: Co přináší Fall Creators Update

** Po půl roce je tu další aktualizace Windows ** A opět přináší hlavně hromadu drobných kosmetických vylepšení ** Podívali jsme se na ty nejzajímavější

Včera | Jakub Čížek | 169

Budoucností Windows 10 je Fluent Design. Takto bude jednou vypadat celý systém

Budoucností Windows 10 je Fluent Design. Takto bude jednou vypadat celý systém

** Fluent Design je vzhled, do kterého postupně Microsoft převleče celý systém ** Staví na průhlednosti a velkých plochách ** Do Windows 10 se z části dostane už zítra při vydání podzimní aktualizace

16.  10.  2017 | Stanislav Janů | 154


Aktuální číslo časopisu Computer

Nový seriál o programování elektroniky

Otestovali jsme 17 bezdrátových sluchátek

Jak na nákup vánočních dárků ze zahraničí

4 tankové tiskárny v přímém souboji