MySQL5:Jemný úvod do uložených procedur MySQL5
MySQL5: Gentle introduction to stored procedures of MySQL5
Tento článek jsem začal psát před vánocemi v nadšení, že MySQL podporuje uložené procedury (SP). Tato vlastnost byla jeden čas označována jako nejočekávanější a nejužitečnější přínos MySQL 5.0. Zákonitě se objevila řada článků s touto tematikou. Trochu překvapivě, po počátečním nadšení, zájem o SP prudce ochabl. Bohužel první verze MySQL obsahovaly řadu chyb, které zdaleka nebyly odstraňovány tak rychle, jak by se slušelo a chyby se nevyhnuly ani implementaci uložených procedur. S dokončením článku jsem čekal na odstranění chyby znemožňující rekurzivní volání procedury. Stále i ve verzi 5.1 je vývoj uložených procedur obtížný vzhledem k mizerné diagnostice chyb a řadě neopravených chyb nebo chybějícím vlastnostem jazyka. Vzhledem k tomu, že MySQL je prakticky poslední O.S. databází, kde jsou implementovány uložené procedury, jazyk uložených procedur vychází z ANSI SQL a má po syntaktické stránce ze všech O.S. databází nejblíže k SQL/PSM. Již jsem zmínil, že úplnost a kvalita implementace SP, není srovnatelná s např. Postgresem nebo Firebirdem. Rozumím tomu tak, že priority MySQL jsou jinde: clustering, partitioning a replikace. Příklady SQL procedur, které uvádím v tomto článku, jsou odladěné v MySQL 5.1.9 a pro jiné verze nemusí být funkční.
Úvod
Jazyk S.P. respektuje ANSI SQL (jako např. DB2). Před dvěma lety jsem si u MySQL stěžoval na nedostatek dokumentace ohledně SP. Té je na internetu nyní relativně dost, i když občas jsou problémy s kvalitou nebo aktuálností. Někdy i oficiální dokumentace obsahuje poměrně dost závažné chyby. Kromě toho v rámci seriálu o MySQL vyšly, tuším, dva články na linuxsoftu. Existují dva základní důvody, na nichž se prakticky všichni shodneme, a pak dva, na kterých se nejspíš nedomluvíme, proč je používat:
- rychlost - v některých případech podstatně vyšší rychlost zpracování úlohy (zhruba záleží na poměru mezi objemem zpracovávaných a výsledných dat) vyplývá z faktu, že minimalizujeme transfer dat mezi klientem a serverem (serializace, komunikace, ...). To má zvlášť význam, když mezi databází a vaší aplikací jsou brzdy typu ODBC nebo BDE. Vyšší rychlost zpracování úloh se musí samozřejmě pozitivně promítnout na celkové prostupnosti serveru. Asi málokdo bude psát uloženou proceduru pro numerické řešení integrálu. Ne že by to bylo tak úplně od věci, takové šarlatánství má ale úplně jiný důvod - bod 4. Občas se objeví tvrzení, že uložené procedury jsou přeložené, předkompilované. To zpravidla neznamená, že by se kód překládal do strojového kódu, ale že se používají tzv. připravené SQL příkazy (prepared statements). Výjimkou jsou novější verze Oracle, kde se SP skutečně překládají. MySQL neprovádí ani implicitní transformaci SQL příkazů na předpřipravené SQL příkazy.
- bezpečnost - prostřednictvím uložených procedur můžeme jistým způsobem distribuovat svá práva k tabulkám (potažmo k samotným datům), a to tak, že definujeme proceduru s právy autora (SECURITY DEFINER). V druhém režimu (SECURITY INVOKER) procedura získává práva volajícího.
- adaptabilita a portabilita - zatímco na prvních dvou bodech se prakticky všichni zainteresovaní shodnou, tak tady začínají první názorové neshody. Uložené procedury vytvářejí vrstvu mezi vlastními daty a klientskou aplikací. Obyčejně tato vrstva není absolutní - občas nějaký ten SELECT v aplikaci zůstane. Při změně struktury tabulek, při portaci aplikace, vám ale ten zapomenutý SELECT může pěkně zamotat hlavu. Portace a aktualizace uložených procedur není tak problematická, jak by se na první pohled mohlo zdát. Nejbolavějším omezením uložených procedur je absolutní zákaz interakce s uživatelem (chvíli to bolí, než si na to zvyknete) - nemůžete si odskočit a spustit message box a přeptat se, jestli to uživatel fakt myslel vážně. Kupodivu to ale vede k přehlednějšímu a čitelnějšímu kódu, který se snáze modifikuje a udržuje. Navíc neinteraktivní SP se dobře automatizovaně testují. Rozdíly v syntaxi jazyků jsou, ale nezpůsobují reálně větší problémy (když se s nimi počítá). Samozřejmě, že vždy je něco, co se portuje obtížně. Např. multirecordset (stacked recordset) je v PostgreSQL nebo Oraclu trochu pracně generován prostřednictvím kurzorů, a v MySQL nebo v T-SQL naopak velice jednoduše, jako výsledek všech volných SELECTů.
- architektura - tak tady to už vře, tady už se stoprocentně neshodneme. V podstatě jde o poměr kódu klientské aplikace a uložených procedur. Já jsem extremista, optimum vidím tak někde kolem nuly. Opačný extrém je degradace databáze na pouhé úložiště dat, tedy hodnota blížící se nekonečnu. Tato problematika je o něco širší, váže se na (ne)závislost aplikace na databázi, tj. čím víc chci využít možnosti některého systému, tím se stávám na něm závislejším a také více potřebuji specialisty. Logiku, kterou přesuneme do databáze může používat každý, kdo má přístup k databázi - odvárek distribuovaných objektů. O co se zvětší kód databáze, o to se zmenší kód aplikace - získáme vyváženější aplikaci. Na druhou stranu, často to vede k tomu, že musíme některé části kódu duplikovat. Např. web. formulář obsahuje kód pro ergonomické zadávání hodnot a kontrolu dat, tutéž kontrolu obsahuje databáze. Opět se ale nejedná o nepřekonatelný problém. Navíc většina moderních RDBMS umožňuje alespoň jedním způsobem sdílení knihoven mezi SP a ostatními server side aplikacemi (Yukon ~ dot NET, Oracle ~ Java, PostgreSQL ~ Perl, Python, Php, MsSQL2k - COM).
Podpora uložených procedur v MySQL5 znamená, že všude kde bude nainstalovaná tato databáze, máme k dispozici určité minimální prostředí pro běh našich SQL/PSM skriptů. Tedy řadu úloh můžeme vyřešit přímo v MySQL bez potřeby instalace dalšího dodatečného software - generování testovacích dat, filtrování a transformace dat, atd.
Tiše předpokládám, že laskavý čtenář ví, co jsou to uložené procedury. Pokud ne, tak se jedná o skripty aktivované databázovým systémem a to v akci na přímý nebo nepřímý požadavek uživatele databázového systému. Od obyčejných skriptů se liší uložením zdrojového kódu v databázi a využíváním specifického funkčního rozhraní umožňující přístup k interním funkcím databázového systému. Nicméně, ne vždy musí být kód uložen v databázi (PL/Java) a ne vždy se musí využívat API databáze (pl/sh). Každá uložená procedura má jméno, seznam argumentů a tělo obsahující SQL příkazy, deklarace lokálních proměnných, ošetření chyb, cykly a podmínky atd. Rozlišuje se mezi procedurami a mezi funkcemi. Obyčejně při návrhu funkcí musíme respektovat určité restrikce, můžeme je ale použít v příkazu SELECT.
Od časů K&R každý manuál musí začít programem helloworld.
1 DELIMITER //
2 SET sql_mode=ansi//
3 CREATE PROCEDURE hello(IN komu varchar(20)) SELECT 'hello '||komu; //
4 DELIMITER ;
5 SET sql_mode='';
6
7 CALL hello('world');
Přeskočil jsem nultou variantu funkce bez argumentů. Všimněte si: a) kód uložené procedury zapisuji do databáze DDL příkazem, b) uloženou proceduru mohu spouštět prostřednictvím libovolného mysql klienta, ať to je to podpora mysql v php nebo v interaktivní konzoli nebo v phpmysqladminu. V tomto případě výstup z procedury je realizován volným příkazem SELECT (příkazů SELECT může být víc, výsledkem je multirecordset). Další možností je použití OUT parametrů - obdoba předávání parametrů odkazem v klasických prg. jazycích.
1 DELIMITER //
2 SET sql_mode=ansi//
3 CREATE PROCEDURE hello(IN komu varchar(20), OUT res varchar(40)) SET res='hello '||komu; //
4 DELIMITER ;
5 SET sql_mode='';
6
7 CALL hello('world', @x);
8 SELECT @x;
9
10 DROP PROCEDURE hello;
SQL příkazy v uložených procedurách musí být ukončeny středníkem. Jenže tento symbol se už používá v konzoli coby symbol konce SQL příkazu. Pokud chceme v konzoli zapsat uloženou proceduru, musíme symbol konce SQL příkazu předefinovat - k tomu slouží příkaz DELIMITER [1]. Nastavením globální proměnné ansi_mode MySQL bude respektovat ANSI SQL zápis. Ve výchozím režimu nemáme k dispozici operátor ||. I když zruším ansi_mode [5], procedura proběhne správně. Proč? Spolu s kódem procedury se ukládá i aktuální konfigurace MySQL. Bohužel, i zde je opět chyba, nebo alespoň určitá nekonzistence. Systémovou proměnnou max_sp_recursion_depth musíme nastavovat zvlášť při každém přihlášení k databázi. Proceduru odstraníme příkazem DROP PROCEDURE [10].
Prefixem @ označujeme globální (session) proměnné. Nikde je nedeklarujeme, přímo jim přiřadíme hodnotu [7]. Jedná se skutečně o proměnné na straně serveru (server side), takže je můžete používat v každém rozhraní. Globální proměnné použijeme nejen pro zobrazení výsledků uložených procedur. Velice často na sebe SQL příkazy navazují, a jsou provázané skrz určitou hodnotu, kterou získáme prvním SELECTEM. S globálními proměnnými mohu eliminovat parametrizaci (sestavování) SQL na stranně klienta (hlavně hrozí riziko chybné konverze mezi serverem a cílovým prostředím). V pětce vše zůstává na stranně serveru, posílám jen SQL příkazy. Kromě jiného se glob. prom. nechají šikovně použít pro číslování řádků. Stejně jako v T-SQL prefix @@ se používá pro systémové proměnné.
1 SET @r = 0; 2 SELECT @r := @r + 1, tab.* FROM tab
Nejčastějším klientem MySQL bude PHP, což je důvod pro ukázku volání uložené procedury z tohoto prostředí (PHP5), dále budou uvedeny příklady Perlu, a C#. PHP5 obsahuje nové API pro MySQL, které zpřístupňuje vlastnosti verze 4.11 a vyšších - mimo jiné vázané vstupní a výstupní proměnné a připravené příkazy (pro přehlednost v příkladu neuvádím kontrolu chyb).
1 $mysqli = new mysqli($host, $user, $password, $db);
2 $stmt = $mysqli->prepare("CALL Hello(?, @x)");
3 $stmt->bind_param("s", $param); /* s jako string */
4 $param = "world";
5 $stmt->execute();
6 $stmt = $mysqli->prepare("SELECT @x");
7 $stmt->bind_result($col1_x);
8 $stmt->execute(); /* musi se spustit novy pripraveny statement*/
9 if ($stmt->fetch())
10 printf("%s ", $col1_x);
11 $stmt->close();
12 $mysqli->close();
Příkaz bind_param na řádku [3] vytváří vazbu mezi prvním parametrem příkazu a proměnnou $param. Prvním argumentem této metody je formátovací řetězec - jeden znak, jedna proměnná, určující konverzi. K dispozici jsou čtyři možnosti: i - integer, d - double, s - string, b - blob. Příkaz bind_result z řádku [6] vytváří naopak vazbu mezi prvním sloupcem výstupu a proměnnou $col1_x. Jen pro úplnost dodávám, že proměnná $param se čte až v příkazu execute [5], a $col1_x plní příkazem fetch [8]. Vázané proměnné spolu s připravenými příkazy jsou velice účinné proti SQL injektáži.
Generování křížové sestavy
Jazyk uložených procedur je procedurální jazyk jako všechny ostatní - Cčko, Perl, Modula nebo ADA. Najdeme v něm deklaraci proměnných, cykly, podmínky. Zvláštností je neexistence vstupních a výstupních funkcí (I/O funkcí), což je daň za přenesení na server (taky není možné, aby se v uložených procedurách čekalo na interakci uživatele - čas na serveru je příliš drahý). Druhou zvláštností, jistě překvapující, je integrované SQL. Chvíli mi trvalo, než jsem tomu přišel na chuť. Je to můj soukromý názor, který nikomu nevnucuji, ale tím, že SQL příkazy nejsou rozbité uvozovkami a zavřené do parametrů funkcí, jsou mnohem čitelnější. To nemluvím o tom, že se vlastně nic nového učit nemusíte: funkce a datové typy znáte a používáte v SQL. Navíc jsou jen podmínky, cykly, proměnné - lepidlo, které udržuje SQL příkazy pohromadě. Na netu najdete tucty jednoduchých příkladů, na kterých je sice názorně vidět syntaktický zápis té které konstrukce, ale vlastně vůbec žádný přínos oproti neprocedurálnímu stylu. Jeden z pěkných příkladů, které jsem našel, je procedura generující křížové sestavy.
Příklad přebírám z blogu Rolanda Baumana, který ostatně doporučuji k přečtení. Z pětky byli blogeři celkem u vytržení, a ani se nedivím. Vůči čtyřce je to generační skok, navíc podepřený relativně kvalitní dokumentací. V podstatě všechny features pětky jsou důkladně popsány včetně uložených procedur. Musím ocenit jejich podporu uživatelů i to, jak si udržují a budují komunitu. Kód jsem mírně upravil přepsáním do ANSI SQL.
O co jde. Naznačím postup (jinak, je to hodně šikovný starý obecný postup, jak vykouzlit křížovou tabulku, aniž bychom potřebovali podporu systému):
CREATE TABLE employees (
id INT auto_increment PRIMARY KEY,
shop_id INT,
gender ENUM('m', 'f'),
name VARCHAR(32),
salary INT
);
CREATE TABLE shops (
shop_id INT auto_increment PRIMARY KEY,
shop VARCHAR(32)
);
INSERT INTO shops (shop)
VALUES ('Zurich'), ('New York'), ('London');
INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);
Jednoduše získáme tabulku o prodeji v Londýně nebo v Curychu. Ovšem získat závislost o výši prodeji dle měst a pohlaví prodejce je řádově komplikovanější (sloupce: f, m, total; řádky: města).
SELECT shop, SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f, SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m, SUM(salary) AS total FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop
O první půlku práce se postará agregace (GROUP BY), tj. separaci dat do skupin podle měst. Dále si už musíme pomoci sami. V SQL nedokážeme zařídit, aby se ve sloupci 'f' neobjevily "nesprávné" hodnoty, dokážeme ale zajistit, aby se tyto hodnoty nepřičítaly a byly ignorovány - CASE gender WHEN 'f' THEN salary ELSE 0 END. Rolandova procedura nedělá nic jiného, než že SELECT v tomto tvaru vygeneruje a provede. Kontrolní otázka: jak upravit tento dotaz, aby zobrazoval počty a nikoliv součty.
1 DELIMITER //
2 SET sql_mode=ansi //
3 CREATE PROCEDURE xtab3(dimx_name VARCHAR(32), dimx_source VARCHAR(32),
dimy_name VARCHAR(32), dimy_source VARCHAR(256),
expr VARCHAR(32))
4 BEGIN
5 SET @col_list_expr = 'SELECT GROUP_CONCAT(DISTINCT '
|| '\'SUM(CASE ' || dimx_name || ' WHEN \'\'\'||'
|| dimx_name || '||\'\'\' THEN ' || expr || ' ELSE 0 END) '
|| ' AS \'\'\'||'||dimx_name||'||\'\'\' \')INTO @col_list ' || dimx_source;
6 PREPARE col_list_pc FROM @col_list_expr;
7 EXECUTE col_list_pc;
8 DEALLOCATE PREPARE col_list_pc;
9 SET @xtab_expr = 'SELECT '||dimy_name||','||@col_list
|| ', SUM('||expr||') AS Total '|| dimy_source ||' GROUP BY '||dimy_name;
10 PREPARE xtab FROM @xtab_expr;
11 EXECUTE xtab;
12 DEALLOCATE PREPARE xtab;
13 END //
14 SET sql_mode=''//
15 DELIMITER ;
[5] Použití globální proměnné tu bije do očí. Obchází se tak chyba, která neumožňuje v příkazu PREPARE použít lokální proměnnou. Generování SELECTu jiným SELECTem je nejrychlejší a taky nejméně čitelný způsob řešení této úlohy, jak se sami můžete přesvědčit. [7,11] Provedení připraveného příkazu - pokud se SQL příkaz generuje až za běhu procedury, označujeme jej jako dynamický příkaz. [7] Vygeneruje seznam parametrů, [9] k seznamu přidá popisku a sloupec Total. Dynamické příkazy nesmí obsahovat lokální proměnné, ty jsou mimo viditelnost. Mohou ale, v místech parametrů nebo části INTO, obsahovat globální proměnné. Dynamický SQL příkaz musíme před samotným spuštěním "zkompilovat" - příkaz PREPARE [6,10]. Vygenerovaný SELECT bude uložen v globální proměnné @xtab_expr (SELECT @xtab_expr);
mysql> CALL xtab3('gender','FROM employees', 'shop','FROM employees INNER JOIN shops USING (shop_id)','salary');
+----------+-------+------+--------+
| shop | m | f | Total |
+----------+-------+------+--------+
| London | 10300 | 0 | 10300 |
| New York | 0 | 5600 | 5600 |
| Zurich | 4500 | 4700 | 10200 |
+----------+-------+------+--------+
3 rows in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
Přestože tato procedura už nějakou práci zastane, neobsahuje jedinou podmínku, jediný cyklus. Podobné funkce nemohou nahradit specializované nástroje, a ani se o to nesnaží. V případech, kdy postačí, vám ušetří práci s konfigurací OLAPu. Na internetu jsem hledal podobné příklady chytrých uložených procedur a nenašel. Nenašel jsem ani komerční knihovny, což mne trochu zarazilo. Trochu naivně jsem doufal, že s příchodem MySQL5 se situace změní. Pokud byste věděli o nějaké pěkné a užitečné proceduře nebo balíku, dejte mi vědět. Pozn. obecně dobrých knihoven uložených procedur je jak šafránu a to nejen pro MySQL.
Optimalizace indexu
Našel jsem ještě jednu hezkou a jednoduchou procedurku, a to v blogu Markuse Poppa. MySQL umožňuje nastavit délku indexu. Není praktické indexovat řetězce v celé jejich délce. Většinou máme dostatečnou míru selektivity už po prvních n (n <20) znacích. Jak ale zjistit optimální délku? Markus přichází s mírou, kterou nazývá jednoznačnost a spočítá ji jako poměr mezi počtem jednoznačně identifikovatelných řádků a celkovým počtem řádků.
SELECT count(distinct left(field_name, indexed_length)) / count(*) * 100 FROM table_name;
Markusův kód jsem opět mírně poupravil. Kód používá jednu dočasnou tabulku, které se vytváří v proceduře. Na závěr se provede select z této tabulky. Tato technika se často používá u Microsoft SQL Serveru. Je tu ovšem jeden drobný rozdíl. V T-SQL má dočasná tabulka omezenou životnost na transakci, v které byla vytvořena. U MySQL nic takového neexistuje, takže v proceduře ji musíme před ukončením explicitně odstranit.
1 DELIMITER //
2 SET sql_mode=ansi //
3 DROP PROCEDURE IF EXISTS getUniqueness //
4 CREATE PROCEDURE getUniqueness(IN _table VARCHAR(255),
5 IN _column VARCHAR(255), IN _noFrom INT, IN _noTo INT)
6 BEGIN
7 DROP TEMPORARY TABLE IF EXISTS tt_uniqueness;
8 CREATE TEMPORARY TABLE tt_uniqueness
9 (noChar int unsigned not null, uniqueness decimal(10,2) not null);
10 SET @sql = 'insert into tt_uniqueness '
11 || 'select ?, cast(count(distinct left(' || _column || ', ?)) / count(*) * 100 as decimal(10,2)) from '
12 || _table;
13 PREPARE pSql FROM @sql;
14 SET @count = _noFrom;
15 WHILE @count < _noTo DO
16 EXECUTE pSql USING @count, @count;
17 SET @count = @count + 1;
18 END WHILE;
19 SELECT * FROM tt_uniqueness;
20 DROP TEMPORARY TABLE tt_uniqueness;
21 END//
22 DELIMITER ;
O co tu jde. Opět používáme dynamický SQL příkaz, a to protože název tabulky nesmí být parametrizován v statickém SQL příkazu [10]. Vygenerovaný dynamický příkaz používá vázané proměnné - symbol ?, a fráze USING v příkazu EXECUTE [16]. Jazyk PSM (Persistent Stored Module) v MySQL neobsahuje cyklus FOR (ANSI SQL3/PSM jej obsahuje - pro iteraci nad tabulkou), musíme si vystačit s klasickým WHILE, END WHILE - je to vlastně první ukázka konstrukce cyklu [15]. Volným SELECTem vrátíme výsledek [19].
mysql> CALL getUniqueness('phpbb_posts_text', 'post_text', 4, 10);
+--------+------------+
| noChar | uniqueness |
+--------+------------+
| 4 | 51.18 |
| 5 | 59.41 |
| 6 | 67.94 |
| 7 | 71.47 |
| 8 | 78.82 |
| 9 | 84.12 |
| 10 | 85.00 |
+--------+------------+
10 rows in set (0.03 sec)
Přidávám ukázku volání této procedury v Perlu (opět zkráceno o kontrolu chyb) prostřednictvím knihovny DBI.
use strict; use DBI;
my $dbh->DBI->connect(' ', {RaiseError => 1, AutoCommit => 0});
my $sth = $dbh->prepare("call getUniqueness(?,?,?,?)");
$sth->bind_param(1, $tbname, $DBI::SQL_VARCHAR);
$sth->bind_param(2, $colname, $DBI::SQL_VARCHAR);
$sth->bind_param(3, $nofrom, $DBI::SQL_INTEGER);
$sth->bind_param(4, $noTo, $DBI::SQL_INTEGER);
$sth->execute();
$sth->finish();
$dbh->disconnect();
Rekurze
Když jsem se poprvé setkal s rekurzí, tak jsem vůbec nechápal, o co jde. Většina překladačů a interpretů ji nepodporovala, a spíš se hledaly nerekurzivní řešení. Staré časy - MicroBáze Pascalu, TurboPascalu, jsem rád, že mám na co vzpomínat. I když ještě mi něco uteklo, časy děrných štítků, počítačů Ural. Doba, kdy výpočet výplat pro 10 tisíc lidí trval celou noc, a pro operátory to bylo super dobrodružství. Slyšel jsem, že někde se starší dáma podívala na výslednou sestavu, a když se jí něco nezdálo, tak se počítalo znova, až se jí to zdálo. Sedím u nadupaného notesu, snad 1000x rychlejšího než CP/M na mém didaktiku, a hned bych měnil. Dost nostalgie. Rekurze tu je. Není to abstraktní konstrukce, stačí se podívat kolem sebe. Jedna z větví databázových systémů, dodnes žijící, i když už zdaleka ne tak markantní (bez ohledu, co si těch pár nadšenců myslí) to respektuje, a podporuje velice efektivní práci se stromy. Jsou to tzv. síťové databáze. Relační databáze nejsou na rekurzi zdaleka tak dobře vybavené (existují dvě rozšíření - Oracle a ANSI, které tuto problematiku jistým způsobem řeší). 100% nepřipravené jsou Open Source databáze. Pro PostgreSQL existuje patch, který byl ale zamítnut (podporující obě syntaxe: Oracle, ANSI). Podporu rekurze by měl obsahovat Firebird 3.0. O co jde? Většinou o úlohu prohledání stromu do šířky nebo do hloubky. Bez podpory na úrovni SQL musíme tuto úlohu řešit na aplikační úrovni nebo pomocí uložených procedur. Pozn.: existují minimálně tři řešení uložení rekurzivních dat, kdy můžeme získat seznam potomků, aniž bychom museli rekurzivně volat proceduru.
Implicitně MySQL nedovoluje rekurzivní volání procedur. To ale nepředstavuje žádný problém. Stačí změnit systémovou proměnnou max_sp_recursion_depth:
set @@max_sp_recursion_depth=32;
Důležitou roli v SQL/PSM hraje zachytávání výjimek. Je to určité specifikum tohoto jazyka a ostatně specifický je i způsob jak jsou výjimky ošetřeny. Na rozdíl od většiny ostatních jazyků, kde se používají konstrukce a la strukturované výjimky, SQL/PSM používá chybové handlery - subrutiny, které se aktivují při chybě. Lze zachytit konkrétní i libovolnou chybu. Což ovšem nedoporučuji, protože v MySQL zatím neexistuje způsob, jak detekovat důvod chyby, a tudíž o chybě se nedovíte vůbec nic. Např. iterace nad výsledkem dotazu se v SQL/PSM řeší následovně:
1 BEGIN 2 DECLARE done BOOLEAN DEFAULT false; 3 DECLARE v_a, v_b VARCHAR(20); 4 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; 5 DECLARE c CURSOR FOR SELECT a,b FROM mytab; 6 OPEN c; 7 FETCH c INTO v_a, v_b; 8 WHILE NOT done DO 9 FETCH c INTO v_a, v_b; 10 END WHILE; 11 CLOSE c; 12 END;
V případě, že kurzor narazí na konec množiny v příkazu FETCH [7,9], aktivuje se obsluha chyby NOT FOUND. V tomto případě se jedná o tzv. CONTINUE HANDLER, tj. spustí se ošetření chyby (nastavení proměnné done [4]) a pokračuje se následujícím příkazem [8,10]. Příkaz FETCH načte jeden řádek z vstupní množiny otevřeného [6] kurzoru. Načtené sloupce se uloží do lokálních proměnných v_a a v_b. Doplním, kurzory se v SQL používají pro sekvenční přístup k datům. Otevřený kurzor se uvolňuje příkazem CLOSE [11].
Funkčně stejný kód demonstrující cyklus REPEAT UNTIL:
1 BEGIN
2 DECLARE done BOOLEAN DEFAULT false;
3 DECLARE v_a, v_b VARCHAR(20);
4 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
5 DECLARE c CURSOR FOR SELECT a,b FROM mytab;
6 OPEN c;
7 REPEAT
8 FETCH c INTO v_a, v_b;
9 IF not done THEN
...
10 END IF;
11 UNTIL done END REPEAT;
12 CLOSE c;
13 END;
Jazykoví puristé a experti se mohou přít, která varianta je elegantnější a lepší. Mně osobně přijde jedna za osmnáct a druhá bez dvou za dvacet. V dokumentaci MySQL se používá druhá varianta. Nejlepší varianta je ta, kterou zatím MySQL nepodporuje - použití konstrukce FOR. Následující příklad je modifikací příkladu z firemní dokumentace Petra Gultuzana. Odstranil jsem pouze nesmyslné zachytání chyb, což je styl, který rozhodně na veřejnosti propagovat nehodlám. Následující dvě procedury vypíší seznam potomků zadaného prvku. Nejdříve vytvoříme dočasnou tabulku [8], která se naplní rekurzivním voláním druhé procedury [12]. Poté se provede volný SELECT této dočasné tabulky [14](tj. tímto způsobem dostaneme výsledek ven z procedury) a dočasná tabulka se odstraní [15]. Druhá procedura není nijak robustní. Pokud by data nereprezentovala strom, tak dojde k chybnému výsledku nebo zacyklení. Druhá procedura iteruje nad potomky [28] - uloží záznam do dočasné tabulky (tabulky s výsledkem) [32] a rekurzivně spouští sama sebe pro dohledání potomků [33].
1 DELIMITER //
2 DROP PROCEDURE IF EXISTS hierarchy//
3 CREATE PROCEDURE hierarchy (start_with CHAR(10))
4 BEGIN
5 DECLARE v_person_id, v_father_id INT;
6 DECLARE v_person_name CHAR(20);
7 DROP TABLE IF EXISTS Temporary_Table;
8 CREATE TEMPORARY TABLE Temporary_Table (
person_id INT,
person_name CHAR(20),
father_id INT,
level INT
);
9 SELECT person_id, person_name INTO v_person_id, v_person_name
FROM Persons WHERE person_name = start_with LIMIT 1;
10 IF NOT v_person_id IS NULL THEN
11 INSERT INTO Temporary_Table VALUES
(v_person_id, v_person_name, v_father_id, 0);
12 CALL hierarchy2(v_person_id, 1);
13 END IF;
14 SELECT person_id, person_name, father_id, level
FROM Temporary_Table ORDER BY level;
15 DROP TEMPORARY TABLE Temporary_Table;
16 END; //
17 DELIMITER ;
18 DELIMITER //
19 DROP PROCEDURE IF EXISTS hierarchy2//
20 CREATE PROCEDURE hierarchy2(start_with INT, level INT)
21 BEGIN
22 DECLARE v_person_id, v_father_id INT;
23 DECLARE v_person_name CHAR(20);
24 DECLARE done BOOLEAN DEFAULT FALSE;
25 DECLARE c CURSOR FOR
SELECT person_id, person_name, father_id
FROM Persons WHERE father_id = start_with;
26 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
27 OPEN c;
28 REPEAT
29 SET v_person_id=NULL;
30 FETCH c INTO v_person_id, v_person_name, v_father_id;
31 IF done=FALSE THEN
32 INSERT INTO Temporary_Table VALUES
(v_person_id, v_person_name, v_father_id, level);
33 CALL hierarchy2(v_person_id,level+1);
34 END IF;
35 UNTIL done END REPEAT;
36 CLOSE c;
37 END; //
38 DELIMITER ;
Abychom mohli kód spustit, potřebujeme testovací data:
CREATE TABLE Persons ( person_id INT, person_name CHAR(20), father_id INT ); INSERT INTO Persons VALUES (1,'Grandpa',NULL); INSERT INTO Persons VALUES (2,'Pa-1',1),(3,'Pa-2',1); INSERT INTO Persons VALUES (4,'Grandson-1',2),(5,'Grandson-2',2);
Po spuštění získáme tabulku:
mysql> call hierarchy('Grandpa');
call hierarchy('Grandpa');
+-----------+-------------+-----------+-------+
| person_id | person_name | father_id | level |
+-----------+-------------+-----------+-------+
| 1 | Grandpa | NULL | 0 |
| 2 | Pa-1 | 1 | 1 |
| 3 | Pa-2 | 1 | 1 |
| 4 | Grandson-1 | 2 | 2 |
| 5 | Grandson-2 | 2 | 2 |
+-----------+-------------+-----------+-------+
5 rows in set (0,00 sec)
Query OK, 0 rows affected, 1 warning (0,00 sec)
Monitorování růstu databáze
Inspirací pro následující příklad byla Oracle XE. Přiznám se, že www rozhraní mne příjemně překvapilo. U předchozích verzí jsem měl pocit, že snad s nimi nikdo kromě studentů a pár zoufalců nemůže pracovat a ostatní raději používají příkazovou řádku. Jednou z funkcí je i zobrazení statistik velikosti a růstu tabulek. Napsal jsem tedy uloženou proceduru, která zobrazí deset největších tabulek a deset nejrychleji rostoucích tabulek. Kromě jiného je i ukázkou možného použití multirecordsetu. Prvním úkolem je získání a archivace údajů o velikosti tabulky. Obecně se tyto hodnoty získávají ze systémových tabulek. ANSI SQL přináší tzv. informační schémata, které MySQL 5 implementuje. V podstatě se jedná o standardizované pohledy do systémových tabulek. Díky nim odpadá jeden problém při portaci aplikací - prohledávání struktury databáze. Příkaz SELECT table_name FROM information_schema.tables bude funkční na PostgreSQL, MySQL a všech ostatních databázích, které v tomto bodě respektují ANSI SQL.
1 DELIMITER //
2 DROP PROCEDURE IF EXISTS set_stat //
3 CREATE PROCEDURE set_stat (schma VARCHAR(64))
4 BEGIN
5 DECLARE done, cursor_done BOOLEAN DEFAULT FALSE;
6 DECLARE v_table_name VARCHAR(64);
7 DECLARE v_table_rows, v_data_length INTEGER;
8 DECLARE v_hist_rows, v_hist_length INTEGER;
9 DECLARE c CURSOR FOR
10 SELECT table_name, table_rows, data_length
FROM information_schema.tables WHERE table_schema = schma;
11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
12 IF (SELECT table_name FROM information_schema.tables
WHERE table_name = 'size_log') IS NULL THEN
13 CREATE TABLE size_log (
table_schema VARCHAR(64),
table_name VARCHAR(64),
table_rows INTEGER,
data_length INTEGER,
inserted TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
15 END IF;
16 OPEN c;
17 REPEAT
18 FETCH c INTO v_table_name, v_table_rows, v_data_length;
19 IF v_table_name <> 'size_log' THEN
13 SET cursor_done = done;
14 SET v_hist_rows = NULL;
15 SELECT table_rows, data_length INTO v_hist_rows, v_hist_length
FROM size_log WHERE table_name = v_table_name AND table_schema = schma
ORDER BY inserted DESC LIMIT 1;
16 SET done = false; -- je nutno provest reset promenne
17 -- pridavam pouze nove zaznamy nebo zmeny
18 IF (v_hist_rows IS NULL)
OR (v_hist_rows IS NOT NULL
AND v_table_rows <> v_hist_rows AND v_data_length <> v_hist_length) THEN
19 INSERT INTO size_log(table_schema, table_name, table_rows, data_length)
VALUES(schma, v_table_name, v_table_rows, v_data_length);
20 END IF;
21 END IF;
22 UNTIL cursor_done END REPEAT;
23 CLOSE c;
24 END //
25 DELIMITER ;
Procedura set_stat ukládá stav tabulek zadaného schématu. V případě, že neexistuje tabulka size_log [12], se vytvoří [13]. Poté iterace přes všechny tabulky [17] mimo tabulku size_log. Aktuální údaje porovnávám s archivovanými hodnotami [18] a pokud došlo ke změně, tak uložím aktuální údaje o velikosti tabulky [19]. Vzhledem k tomu, že mi flag NOT FOUND (a nepřímo i obsah proměnné done) mi může nahodit jak příkaz FETCH [18] tak příkaz SELECT INTO [15], musím si zálohovat obsah proměnné done [13] a opakovaně jej resetovat [16]. Dále, v případě, že SELECT INTO skončí neúspěšně (příznakem NOT FOUND), nepřepíše se obsah proměnných v_hist_rows, v_hist_length [15]. Protože proměnnou v_hist_rows používám k detekci, zda-li už mám záznam v tabulce size_log, musím ji nastavit na NULL [14].
1 DELIMITER //
2 DROP PROCEDURE IF EXISTS stat //
3 CREATE PROCEDURE stat (sort_by CHAR(1), schma VARCHAR(64))
4 BEGIN
5 DECLARE done, cursor_done BOOLEAN DEFAULT FALSE;
6 DECLARE v_table_name VARCHAR(64);
7 DECLARE v_table_rows, v_data_length INTEGER;
8 DECLARE v_hist_rows, v_hist_length INTEGER;
9 DECLARE c CURSOR FOR
10 SELECT table_name, table_rows, data_length
FROM information_schema.tables WHERE table_schema = schma;
11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
12 DROP TABLE IF EXISTS tmp_table;
13 CREATE TEMPORARY TABLE tmp_table (
table_schema VARCHAR(64),
table_name VARCHAR(64),
table_rows INTEGER,
data_length INTEGER
);
14 IF (SELECT table_name FROM information_schema.tables
WHERE table_name = 'size_log') IS NULL THEN
15 CREATE TABLE size_log (
table_schema VARCHAR(64),
table_name VARCHAR(64),
table_rows INTEGER,
data_length INTEGER,
inserted TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
16 END IF;
17 OPEN c;
18 REPEAT
19 FETCH c INTO v_table_name, v_table_rows, v_data_length;
20 SET cursor_done = done;
21 SET v_hist_rows = NULL;
22 SELECT table_rows, data_length INTO v_hist_rows, v_hist_length
FROM size_log WHERE table_name = v_table_name AND table_schema = schma
ORDER BY inserted DESC LIMIT 1;
23 SET done = false; -- je nutno provest reset promenne
24 IF v_hist_rows IS NOT NULL
AND v_table_rows <> v_hist_rows AND v_data_length <> v_hist_length THEN
25 INSERT INTO tmp_table VALUES(schma, v_table_name,
v_table_rows - v_hist_rows, v_data_length - v_hist_length);
26 END IF;
27 UNTIL cursor_done END REPEAT;
28 CLOSE c;
29 CASE sort_by
30 WHEN 'r' THEN
31 SELECT table_schema, table_name, table_rows, data_length
FROM information_schema.tables WHERE table_schema = schma
ORDER BY table_rows DESC LIMIT 10;
32 SELECT * FROM tmp_table ORDER BY table_rows DESC LIMIT 10;
33 WHEN 'l' THEN
34 SELECT table_schema, table_name, table_rows, data_length
35 FROM information_schema.tables WHERE table_schema = schma
ORDER BY data_length DESC LIMIT 10;
36 SELECT * FROM tmp_table ORDER BY data_length DESC LIMIT 10;
37 END CASE;
38 DROP TABLE tmp_table;
39 END; //
40 DELIMITER ;
V proceduře stat už skoro není co komentovat. Deset největších tabulek přebírá přímo z informačního schématu. Deset nejrychleji rostoucích tabulek získám dynamicky rozdílem hodnot z aktuálního stavu a stavu uloženém v tabulce size_log. Za zmínění snad stojí skutečnost, že konstrukce CASE v SQL/PSM není totožná s SQL příkazem CASE. Výsledek vidíte na následujícím výpisu:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.9-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> call stat('l','mysql');
call stat('l','mysql');
+--------------+---------------+------------+-------------+
| table_schema | table_name | table_rows | data_length |
+--------------+---------------+------------+-------------+
| mysql | help_topic | 464 | 253564 |
| mysql | help_keyword | 384 | 75648 |
| mysql | help_category | 37 | 21497 |
| mysql | help_relation | 740 | 6660 |
| mysql | proc | 8 | 5852 |
| mysql | size_log | 32 | 984 |
| mysql | db | 2 | 880 |
| mysql | user | 4 | 224 |
| mysql | Persons | 5 | 145 |
| mysql | fx2 | 7 | 49 |
+--------------+---------------+------------+-------------+
10 rows in set (0,26 sec)
+--------------+------------+------------+-------------+
| table_schema | table_name | table_rows | data_length |
+--------------+------------+------------+-------------+
| mysql | fx2 | 1 | 7 |
+--------------+------------+------------+-------------+
1 row in set (0,27 sec)
Query OK, 0 rows affected, 1 warning (0,28 sec)
Pokud bych chtěl poslední příklad vyšperkovat, nastavil bych automatické spouštění procedury set_stat pomocí Event Scheduleru. Event Scheduler (od verze 5.1) je obdoba Jobs v SQL serveru nebo crontabu v Unixu. Událost (Event) je databázový objekt obsahující jeden nebo několik SQL příkazů, které se provedou v předepsaný čas nebo se provádějí opakovaně v zadaném intervalu. Defaultně je plánovač událostí zablokován a je třeba jej explicitně povolit: SET @@global.event_scheduler = ON. K provedení tohoto příkazu potřebujete práva superuživatele. Pro vyzkoušení uvedu příklad, který každou minutu přidá záznam do tabulky test. Nicméně alespoň v mé instalaci je i v této funkci chyba. Události se aktivovaly, nicméně v daný okamžik se událost přiřazený SQL příkaz provedl několikrát (když jsem používal jednovteřinový interval). S minutovým intervalem nebyly žádné problémy.
CREATE TABLE testt(t TIMESTAMP);
CREATE EVENT into_testt
ON SCHEDULE EVERY 1 MINUTE DO
INSERT INTO testt VALUES(CURRENT_TIMESTAMP);
Funkci set_stat určitě nebudeme volat po minutě. Dejme tomu jednou týdně. Potom událost můžeme definovat příkazem:
CREATE EVENT call_set_stat
ON SCHEDULE EVERY 1 WEEK STARTS '2006-07-01 13:00:00' DO
CALL set_stat();
Na závěr uvedu ještě slíbený příklad c# kódu (mono) pro volání uložené procedury. V Linuxu jsou k dispozici dva drivery. Volně dostupný ByteFX je již nevyvíjený (a také nepodporuje uložené procedury), ale lze jej získat jako RPM balíček. Proto se zaměřím na druhý driver. Driver MySQL connector/NET sice podporuje SP, ale musíme jej nainstalovat ručně (Pouze verze 1.0.7 a vyšší jsou funkční i mimo o.s. MS Windows). Pokud získáme knihovnu MySQL.Data.Dll (z mysql.com), musíme ji zaregistrovat příkazem:
gacutil -i MySql.Data.dll
Když jsem zkoušel metodologicky volat uložené procedury, opět jsem narazil na chybu. Ta ovšem může být způsobena použitím vývojové verze MySQL. Proto proceduru volám klasicky SQL příkazem CALL [13]. Jedno zavolání Readeru [16] mi vrací dva recordsety. Po přečtení prvého se přesunu na druhý voláním metody NextResult() [32]. Jinak tento příklad představuje vzorové čtení recordsetu prostřednictvím ADO.NET.
1 using System;
2 using System.Data;
3 using MySql.Data.MySqlClient;
4 public class Test
5 {
6 public static void Main(string[] args)
7 {
8 string connectionString = "Database=mysql; User ID=root;";
9 IDbConnection dbcon;
10 dbcon = new MySqlConnection(connectionString);
11 dbcon.Open();
12 IDbCommand dbcmd = dbcon.CreateCommand();
13 dbcmd.CommandText = "CALL stat(?type,?scheme)";
14 dbcmd.Parameters.Add( new MySqlParameter("?type","l"));
15 dbcmd.Parameters.Add( new MySqlParameter("?scheme","mysql"));
16 IDataReader reader = dbcmd.ExecuteReader();
17 Console.WriteLine("".PadRight(62,'-'));
18 Console.WriteLine(" {0,-15} | {1,-15} | {2,10} | {3,10}",
"table schema",
"table name", "table rows", "data length");
19 Console.WriteLine("".PadRight(62,'-'));
20 Console.WriteLine(" Největší tabulky ");
21 Console.WriteLine("".PadRight(62,'-'));
22 while(reader.Read()) {
23 string table_schema = (string) reader["table_schema"];
24 string table_name = (string) reader["table_name"];
25 long table_rows = (long) reader["table_rows"];
26 long data_length = (long) reader["data_length"];
27 Console.WriteLine(" {0,-15} | {1,-15} | {2,10:g} | {3,10:g}",
table_schema,
table_name, table_rows, data_length);
28 }
29 Console.WriteLine("".PadRight(62,'-'));
30 Console.WriteLine(" Nejrychleji rostoucí tabulky ");
31 Console.WriteLine("".PadRight(62,'-'));
32 reader.NextResult();
33 while(reader.Read()) {
34 string table_schema = (string) reader["table_schema"];
35 string table_name = (string) reader["table_name"];
36 int table_rows = (int) reader["table_rows"];
37 int data_length = (int) reader["data_length"];
38 Console.WriteLine(" {0,-15} | {1,-15} | {2,10:g} | {3,10:g}",
table_schema,
table_name, table_rows, data_length);
39 }
40 reader.Close();
41 reader = null;
42 dbcmd.Dispose();
43 dbcmd = null;
45 dbcon.Close();
46 dbcon = null;
47 }
48 }
Skript přeložíme a spustíme dvojicí příkazů:
mcs mono-test.cs -r:System.Data.dll -r:MySql.Data.dll mono mono-test.exe
V těchto článcích jsem zdaleka nepopsal veškerou funkcionalitu uložených procedur. Namátkou jsem opomněl: triggery, zabezpečení, charakteristiku funkce (DETERMINISTIC|NON DETERMINISTIC|MODIFIES SQL DATA atd), atd. Ani jsem se o to nepokoušel. Zatím jsem se nesetkal s nikým, kdo by SP v MySQL 5 použil v reálných aplikacích. Jedná se zatím o příliš žhavou novinku, a odhaduji, že bude potřeba minimálně rok, dva než budou vychytány všechny chyby a než si ji osvojí programátoři - viz nástup PHP5.