Tento článek jsem začal psát před vánocemi v nadšení, že MySQL podporuje uložené procedury. 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. datábá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í.
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 d. 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, 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ř. webový 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 interaktivni konzoli nebo v phpmysqladminu. V tomto případě je výstup z procedury 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 programovacích 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 straně klienta (hlavně hrozí riziko chybné konverze mezi serverem a cílovým prostředím). V pětce vše zůstává na straně serveru, posílám jen SQL příkazy. Kromě jiného se globální proměnné nechají šikovně použít pro číslování řádků. Stejně jako v T-SQL se prefix @@ 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ůvodem 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 if ($stmt->fetch())
9 printf("%s ", $col1_x);
10 $stmt->close();
11 $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]. Vazané proměnné spolu s připravenými příkazy jsou velice účinné proti SQL injektáži.
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 bloggeř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šeme 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. o 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říčí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('||dimx_name||') 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.
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 knihvny 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();
ČLÁNKY DO MAILU