I když mi ještě 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 1000× 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 na to, 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 (podporuje obě syntaxe: Oracle i 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íž se o chybě nedozví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ává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, 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)
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ě, 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) 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, ovšem v daný okamžik se události 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 není již vyví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.
