Návrhář databázových dotazů

Obecné informace

• Databázový dotaz definuje zdroj dat načítaných z databáze, který je následně používán v ovládacích prvcích editačních formulářů, nahlížecích stránek, portletech a ve skriptech.
• Databázový dotaz určuje, jaká množina databázových záznamů bude načtena a předána k dalšímu zpracování nebo vizualizaci.
• Databázový dotaz je samostatný konfigurovatelný objekt aplikace, který zapouzdřuje logiku výběru dat z databáze a odděluje ji od vizuálních ovládacích prvků.
• Výsledkem databázového dotazu je množina dat, která je předána ovládacím prvkům nebo skriptům k vyhodnocení či zobrazení.

Běžný dotaz

• Běžný dotaz se používá u všech ovládacích prvků, které jako zdroj dat využívají přímo databázová data.
• Běžný dotaz je výchozím typem v návrháři databázových dotazů.
• Při sestavování výsledného SQL dotazu používá pouze klauzule „SELECT“, „FROM“, „JOIN“, „WHERE“ a „ORDER BY“.
• Běžný dotaz nepoužívá klauzuli „GROUP BY“ a neobsahuje žádné agregační funkce.

Agregační dotaz

• Agregační dotaz se používá u nahlížecích tabulek a grafů, které zobrazují agregovaná data.

• Agregační dotaz pracuje s klauzulí „GROUP BY“ a agregačními funkcemi nad databázovými sloupci.

• Agregační dotaz je definován v návrháři databázových dotazů:
  • zaškrtnutím volby „Agregační dotaz“,
  • seznamem seskupovacích sloupců, které se objeví ve výsledném SQL dotazu v klauzuli „GROUP BY“.

• Podporované způsoby seskupování zahrnují:
  • běžné seskupení podle sloupce („GROUP BY ng_“),
  • seskupení podle hodiny ve dni,
  • seskupení podle hodiny,
  • seskupení podle dne,
  • seskupení podle týdne,
  • seskupení podle měsíce,
  • seskupení podle kvartálu,
  • seskupení podle roku.

• Agregační dotaz dále obsahuje:
  • seznam agregovaných sloupců, které se ve výsledném SQL dotazu objeví ve spojení s funkcemi „COUNT“, „SUM“, „AVG“, „MAX“ a „MIN“, s možností použití podmíněné logiky pomocí „CASE WHEN“,
  • seznam informativních sloupců, které se ve výsledném SQL dotazu objeví jako konstanty nebo vypočtené hodnoty a jsou následně vyhodnoceny lexikálním analyzátorem,
  • seznam sloupců, podle kterých je možné filtrovat data v nahlížecí tabulce nebo grafu,
  • seznam hlaviček pro seskupování sloupců v nahlížecí tabulce,
  • seznam barev použitých pro vykreslení jednotlivých datových řad v grafu.

• Jednotlivé sloupce agregačního dotazu používají následující syntaxi názvu:

„název@matematický_výraz@zobrazovaná_hodnota@podmínka”.

• Název určuje popisek sloupce zobrazovaný v nahlížecí tabulce nebo grafu.
• Matematický výraz je volitelný a používá se u sloupců typu „Výraz, formát dle“.
• Zobrazovaná hodnota je volitelná a umožňuje nahradit vypočtenou hodnotu textem nebo skrýt celý sloupec pomocí hodnoty „@@“.
• Podmínka je volitelná a umožňuje omezit zobrazení řádků výsledné množiny dat.

• Příklady názvů sloupců:
  • „Součet prvního a druhého sloupce@#c0#+#c1#“,
  • „Součet prvního a druhého sloupce@#c0#+#c1#@Skrytá hodnota“,
  • „Součet s hodnotou na dalším řádku@+1“,
  • „Součet s hodnotou na předchozím řádku@-1“,
  • „Součet prvního a druhého sloupce a hodnoty na dalším řádku@+1#c0# + #c1#“,
  • „Součet hodnot větších než 5@@@>5“,
  • „Sloupec@+sum“,
  • „Sloupec@-sum“.

• Hlavičky agregačního dotazu používají syntaxi:

„počet_sloučených_buněk@název_hlavičky”.

• Příklady hlaviček:
  • 1. řádek: „5@X;1@Y“,
  • 2. řádek: „2@A;3@B;1@C“.

Vnořené záznamy

• Vnořený záznam je databázový záznam přidružený ke svému rodičovskému záznamu pomocí cizích klíčů „pid“ a „pform“.
• Tyto cizí klíče jsou součástí každé databázové tabulky a určují ID rodičovského záznamu a ID editačního formuláře, ve kterém se rodičovský záznam nachází.
• Odkazy pomocí „pid“ a „pform“ mohou směřovat jak do jiné databázové tabulky, tak i do stejné tabulky (rekurzivní struktury, např. ovládací prvek „Tree“).
• Hodnoty „pid“ a „pform“ jsou vyplňovány automaticky podle kontextu, ze kterého byl nový záznam vytvořen.
  • Při vytvoření záznamu z nahlížecí stránky mají hodnotu „0“.
  • Při vytvoření záznamu z jiného databázového záznamu jsou nastaveny podle ID rodiče a jeho formuláře.
• Použití sloupce „pform“ je obvykle minimální a uplatňuje se zejména v případech, kdy jedna tabulka slouží více rodičovským formulářům.
• Sloupce „pid“ a „pform“ nejsou ve výchozím stavu indexované. Při jejich aktivním používání je nutné indexování zapnout v nastavení editačního formuláře.
• Nahlížecí tabulky v editačních formulářích obsahují volbu „Zobrazit pouze vnořené záznamy“, která automaticky doplňuje podmínku „PID = ID aktuálního záznamu“.
• Nahlížecí tabulky na nahlížecích stránkách obsahují volbu „Skrýt vnořené záznamy“, která automaticky doplňuje podmínku „PID = 0“.

Omezující podmínky dotazu

• Databázový dotaz může obsahovat libovolné množství omezujících podmínek, které následují za klauzulí „WHERE“.

• Jednotlivé podmínky mohou být kombinovány pomocí logických operátorů „AND“ a „OR“ a seskupovány pomocí levých a pravých závorek.

• Výsledná podmínka v jazyce SQL je sestavena automaticky na základě seznamu podmínek definovaných v grafickém návrháři databázových dotazů.

• V podmínkách dotazu lze používat následující rozšířené zápisy, které jsou návrhářem automaticky interpretovány do odpovídající SQL syntaxe:

• Datové typy „Integer“ a „Long“ umožňují zápis ve tvaru „levá_strana = 1;2;3“. Tento zápis je interpretován jako „levá_strana IN (1, 2, 3)“.
• Datový typ „String“ umožňuje zápis ve tvaru „levá_strana = (array)A;B;C“. Tento zápis je interpretován jako „levá_strana IN ('A', 'B', 'C')“.

• Pokud je na levé straně podmínky použit ovládací prvek typu „MultiListBox“ (hodnoty oddělené tabulátorem) a na pravé straně je textová konstanta, je podmínka interpretována jako:
  • „JoinText2(levá_strana, pravá_strana)“ u databáze Firebird,
  • „dbo.JoinNtext(levá_strana, pravá_strana)“ u databáze Microsoft SQL Server.

Výsledný SQL dotaz vyhodnotí všechny záznamy, které mají ve sloupci typu „MultiListBox“ uloženou alespoň jednu hodnotu shodnou s textovou konstantou na pravé straně podmínky.

• Pokud je na levé i pravé straně podmínky použit ovládací prvek typu „MultiListBox“, je podmínka interpretována stejným způsobem pomocí funkcí „JoinText2“ (Firebird) nebo „dbo.JoinNtext“ (MSSQL). Výsledný SQL dotaz vyhodnotí všechny záznamy, u nichž existuje alespoň jedna shodná hodnota v obou sloupcích.

• Datový typ „String“ dále umožňuje zápis ve tvaru „levá_strana = (mlb)A#tab#B#tab#C“. Tento zápis je interpretován jako:
  • „JoinText2(levá_strana, 'A B C')“ u databáze Firebird,
  • „dbo.JoinNtext(levá_strana, 'A B C')“ u databáze Microsoft SQL Server.

Výsledný SQL dotaz vyhodnotí všechny záznamy, které mají ve sloupci s tabulátorem oddělenými hodnotami uloženou alespoň jednu hodnotu obsaženou v textové konstantě na pravé straně podmínky.

• Podmínku dotazu je možné definovat také přímo pomocí SQL syntaxe.
  • V takovém případě může být levá strana podmínky včetně operátoru libovolná.
  • Pravá strana podmínky musí vždy začínat prefixem „OK#crlf#“, za kterým následuje vlastní SQL podmínka.

• Příklady přímé SQL definice podmínky:
  • OK#crlf#0=0
  • OK#crlf#0=1
  • OK#crlf#EQUALS(ng_tb, "", 0=0, ng_tb = FORMATSTRINGSQL(#ng_tb#))
  • OK#crlf#EQUALS(ng_tb, "", 0=1, ng_tb = FORMATSTRINGSQL(#ng_tb#))

Připojení (joiny)

• Databázový dotaz může obsahovat libovolné množství připojení, která jsou sestavená pomocí klauzule „JOIN“.
• Výsledný tvar připojení v jazyce SQL je sestaven automaticky podle seznamu připojení nastavených v grafickém návrháři.
• Připojení může být definováno také pomocí SQL syntaxe. Levá strana podmínky připojení může být libovolná, pravá strana podmínky připojení musí začínat slovem „OK#crlf#“, a za ní musí následovat znění připojení pomocí jazyka SQL, například:
  • OK#crlf#INNER JOIN ng_abc J1 ON J1.pid = ng_formular.id
  • Definice připojení musí respektovat používané aliasy, takže první připojení se musí jmenovat J1, druhé J2 atd.
  • Definice připojení musí respektovat název zdrojové databázové tabulky.

1. Seznam záložek v dialogu nastavení databázového dotazu

• Obecné – Nastavení obecných vlastností
• Podmínky – Definice omezujících podmínek
• Připojení – Definice připojení – joinů
• Sloupce – Definice sloupců agregační tabulky
• Hlavičky – Definice hlaviček agregační tabulky
• Barvy – Definice barev grafu
• Ostatní – Nastavení ostatních vlastností

1.1. Záložka „Obecné“

1.1.1. Databázová tabulka

• Výběr editačního formuláře, z jehož databázové tabulky budou načteny záznamy uložené v databázi.

1.1.2. Možnosti

• Agregační dotaz – Zaškrtnutí tohoto pole určuje, zda má být výsledkem dotazu agregovaná množina dat sestavená pomocí grupování.

1.1.3. Setřídit podle

• Výběr sloupce, podle kterého budou databázové záznamy seřazeny, včetně způsobu řazení – vzestupně (ASC) nebo sestupně (DESC).
• Volitelný výběr druhého sloupce, podle kterého budou databázové záznamy seřazeny, včetně způsobu řazení – vzestupně (ASC) nebo sestupně (DESC).

1.1.4. Barva podle

• Výběr sloupce, který určuje, zda a podle jakého sloupce se bude v nahlížecí tabulce zobrazovat u každého záznamu barevný obdélníček.

1.1.5. Časový úsek podle

• Výběr sloupce, který určuje, zda a podle jakého sloupce se bude nad nahlížecí tabulkou zobrazovat filtr pro výběr časového úseku „od-do“.

1.2. Záložka „Podmínky“

• Definice omezujících podmínek dotazu, které následují za klauzulí „WHERE“ databázového dotazu.

1.2.1. Přidat podmínku

• Pomocí tlačítka „Přidat podmínku“ je možné přidat novou podmínku dotazu.

1.3. Záložka „Připojení“

• Definice připojení, které jsou sestaveny pomocí klauzule „JOIN“ databázového dotazu.

1.3.1. Přidat připojení

• Pomocí tlačítka „Přidat připojení“ je možné přidat nové připojení dotazu.

1.3.2. Aktualizovat

• Pomocí tlačítka „Aktualizovat“ se aktualizuje seznam sloupců na levé i pravé straně podmínky na základě vybrané přijoinované tabulky.

1.4. Záložka „Sloupce“

• Pouze při zaškrtnutém poli „Agregační dotaz“
• Definice sloupců výsledné agregační tabulky.

1.4.1. Přidat sloupec

• Pomocí tlačítka „Přidat sloupec“ je možné přidat nový sloupec výsledné agregační tabulky.

1.5. Záložka „Hlavičky“

• Pouze při zaškrtnutém poli „Agregační dotaz“ u ovládacího prvku „DataGrid“ nebo „LiteDataGrid“
• Definice hlaviček výsledné agregační tabulky.

1.5.1. Přidat hlavičku

• Pomocí tlačítka „Přidat hlavičku“ je možné přidat novou hlavičku výsledné agregační tabulky.

1.5.2. ?

• Pomocí tlačítka „?“ je možné zobrazit nápovědu k syntaxi hlaviček.

1.6. Záložka „Barvy“

• Pouze u ovládacího prvku „Chart“
• Definice barev, které budou použity na vykreslení jednotlivých sloupců grafu.

1.6.1. Přidat barvu

• Pomocí tlačítka „Přidat barvu“ je možné přidat novou barvu grafu.

1.7. Záložka „Ostatní“

1.7.1. Název šablony

• Název šablony slouží pro pojmenování databázového dotazu s možností jeho zkopírování při vytváření dalších databázových dotazů se stejnou zdrojovou databázovou tabulkou.
• Při vytváření nového databázového dotazu jsou všechny dostupné šablony k dispozici v rozbalovacím seznamu „Šablony“ na záložce „Obecné“. Po vybrání šablony dojde k automatickému předvyplnění všech parametrů databázového dotazu údaji ze zvolené šablony.
• Seznam všech databázových dotazů, které jsou označené jako šablony, je možné zobrazit pomocí reportu. Detailní popis reportů je uveden v samostatné příručce Reporty.

1.7.2. Poznámky

• Poznámky slouží pro zadání libovolného textu určeného pro správce aplikace.

1.7.3. Načíst pouze prvních

• Omezení maximálního počtu záznamů načtených databázovým dotazem resp. ekvivalent SQL příkazu TOP() nebo FIRST().

1.7.4. Odstranit záznamy s duplicitním

• Výběr sloupce, podle kterého budou vyhodnoceny duplicitní řádky v načtené množině dat, a tyto řádky budou následně z této množiny odstraněny.

1.7.5. Možnosti

• ngef(NETGenium.DataTable)
  • Výsledkem databázového dotazu je vždy načtená množina dat z databáze, dočasně uložená v objektu typu „DataTable“. Tato množina dat je následně předána jednotlivým ovládacím prvkům za účelem jejího vyhodnocení nebo vizualizace.
  • Zaškrtnutí tohoto pole určuje, zda má být před samotným předáním objektu „DataTable“ ovládacímu prvku spuštěna externí funkce, která má možnost měnit vlastnosti tohoto objektu – přidávat řádky, měnit hodnoty v jednotlivých sloupcích, případně řádky odstraňovat.

1.7.6. Logování

• Pomocí tlačítka „Logování“ se zobrazí detailní report s jednotlivými záznamy volání databázových dotazů a údaji o
  • datumu a času spuštění dotazu,
  • uživateli, který dotaz spustil,
  • času zpracování dotazu v milisekundách,
  • počtu vrácených záznamů a
  • konkrétním SQL dotazu.

• Počet záznamů je ve výchozím nastavení omezen na 100 záznamů. Tento počet je možné ručně zvýšit nebo snížit změnou parametru „maxrows“ v URL reportu.