Minulý měsíc se na vsevjednom.cz konal velký přechod na MySQL 5 a PHP 5, a to společně s přechodem na nejnovější stable verzi Debianu — 4.0.
Dnes jsem se rozhodl konečně vyzkoušet některou z řady možností, které nabízí MySQL 5 oproti čtverce: procedury.
Uložené procedury slouží ke zjednodušení kódu. Provádíme-li tedy například ve více skriptech několik stejných SQL dotazů za sebou, můžeme si pro ně vytvořit proceduru. Potřebujeme-li pak něco změnit, měníme pouze proceduru, nemusíme tak procházet jednotlivé soubory a zdlouhavě vyhledávat a měnit části kódu. Procedury jsou vlastně podobné jako klasické funkce, avšak s tím rozdílem, že je nedefinujeme vždy na začátku při provádění scriptu, ale jsou trvale uložené a zkompilované na serveru. Což se také podepisuje na rychlosti provádění procedur oproti běžnému zápisu.
Dost plácání; a protože nemám rád nekonkrétní příklady, uvedu jako příklad zjednodušený kód, který jsem dnes kuchal a předělával do procedury — kód počítadla na vv1. Rozhodl jsem se pro něj proto, že je to služba generující jednu z největších zátěží, a tak jsem si od úpravy kódu sliboval její snížení.
U počítadla používám více tabulek a při každém načtení počítadla se v některých z nich na základě různých podmínek aktualizují informace — počet zhlédnutí vždy a počet denních návštěv společně s vložením návštěvy do tabulky detailů jen při novém přístupů.
Uvedu názorný příklad, abyste byli v obraze:
CREATE TABLE `pocitadlo` (
`poc` int(11) NOT NULL auto_increment,
`celkem` int(8) NOT NULL default '0',
PRIMARY KEY (`poc`)
) ENGINE=MyISAM;
CREATE TABLE `pocitadlo_data` (
`id` int(11) NOT NULL auto_increment,
`poc` int(11) NOT NULL default '0',
`time` int(11) NOT NULL default '0',
`ip` varchar(31) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM;
CREATE TABLE `pocitadlo_dny` (
`poc` int(11) NOT NULL default '0',
`datum` date NOT NULL default '0000-00-00',
`navstevy` smallint(5) NOT NULL default '0',
`zhlednuti` smallint(5) NOT NULL default '0',
UNIQUE KEY `poc` (`poc`,`datum`)
) ENGINE=MyISAM;
První tabulka obsahuje id počítadla a celkovou hodnotu návštěv. Do druhé tabulky se ukládají jednotlivé návštěvy: čas a IP adresa. No a ve třetí tabulce je vždy ke každému počítadlu a dnu počet návštěv a zhlédnutí.
Původní kód tedy vypadal ku příkladu nějak takto:
1 <!--?
2 $existuje=mysql_query("SELECT 1 FROM pocitadlo_data WHERE poc=".$poc." AND ip='".$_SERVER['REMOTE_ADDR']."' AND time-->".(time()-3600));
3
4 if(mysql_num_rows($existuje)==0){
5 mysql_query("INSERT INTO pocitadlo_data VALUES('', ".$poc.", ".time().", '".$_SERVER['REMOTE_ADDR']."')");
6 mysql_query("UPDATE pocitadlo SET celkem = celkem+1 WHERE poc = ".$poc);
7 mysql_query("UPDATE pocitadlo_dny SET navstevy = navstevy+1, zhlednuti = zhlednuti+1 WHERE poc = ".$poc." AND datum='".date("Y-m-d")."'");
8 }
9
10 else{
11 mysql_query("UPDATE pocitadlo_dny SET zhlednuti = zhlednuti+1 WHERE poc = ".$poc." AND datum='".date("Y-m-d")."'");
12 }
13 ?>
To máme pár podmínek a v nejhorším případě 4 SQL dotazy (v originální verzi počítadla je to ještě trochu více propodmínkované, v závislosti na různých nastaveních počítadla, části kódu se SQL dotazy se tedy opakovaly i několikrát). Teď si ale vytvoříme proceduru, díky které nebudeme potřebovat ani žádnou podmínku v php:
CREATE PROCEDURE `pocitadlo_vloz` (`poc` int, `ip` varchar(15), OUT zapocitan boolean)
BEGIN
IF EXISTS(SELECT 1 FROM `pocitadlo_data` WHERE `poc`=poc AND `ip`=ip AND `time`<(UNIX_TIMESTAMP()-3600)) THEN
UPDATE `pocitadlo_dny` SET `zhlednuti` = `zhlednuti`+1 WHERE `poc` = poc AND `datum`=CURDATE();
SET zapocitan=0;
ELSE
INSERT INTO `pocitadlo_data` (`poc`, `time`, `ip`) VALUES(poc, UNIX_TIMESTAMP(), ip);
UPDATE `pocitadlo` SET `celkem` = `celkem`+1 WHERE `poc` = poc;
UPDATE `pocitadlo_dny` SET `navstevy` = `navstevy`+1, `zhlednuti` = `zhlednuti`+1 WHERE `poc` = poc AND `datum`=CURDATE();
SET zapocitan=1;
END IF;
END;
Prvně — dbejte na to, aby byly názvy sloupců a tabulek v uvozovkách `! Jinak to nemusí fungovat!
A teď už k popisu: příkaz CREATE PROCEDURE snad vysvětlovat nemusím — znamená prostě vytvoření procedury; následuje její název. V závorce jsou pak vstupní proměnné, které do procedury vkládáme. Ty mohou být jak vstupní (IN), tak i výstupní (OUT) nebo obojetné (INOUT). O jaký typ proměnné se jedná, uvedeme před jejím názvem. V případě IN proměnných není třeba psát nic, jsou tak standardně označeny všechny. Dále je třeba nezapomenout uvést datový typ všech proměnných, což je povinné.
Vlastní kód procedury pak začneme příkazem BEGIN. Pak už můžeme používat podmínky, cykly, definovat si další proměnné a podobně; zkrátka využívat všech funkcí MySQL. Já ve svém příkladu potřebuji zjistit, zda již v tabulce pocitadlo_data existuje hodnota IP uživatele, a to vložená v poslední hodině. Použil jsem tedy podmínku a funkci EXISTS, která vrací TRUE. V případě, že záznam existuje, přičtu jen počet zhlédnutí a do výstupní proměnné zapocitan si uložím hodnotu 0. V opačném případě je uživatel započítán, je akutalizována hodnota celkových a dnešních návštěch a zhlédnutí. Do proměnné zapocitan nyní ukládám hodnotu 1. Následuje END IF, tedy konec podmínky, a END pro ukončení kódu procedury.
Proceduru vložte například v konsoli (mysql -u uzivatel -p heslo) úplně stejně jako jiný SQL kód (nejdříve je třeba si zvolit databázi: use nazev_databaze) s tím, že je nejdříve třeba použít příkaz DELIMITER //, aby vám konsole nezalamovala řádky jako nové příkazy; phpMyAdmin zatím vkládat nebo prohlížet procedury neumí.
Nyní jsme hotovi a můžeme proceduru zavolat (CALL) pomocí mysql_query v php kódu. Já jsem si navíc ještě vytvořil výstupní proměnnou zapocitan — tu vyberu dalším dotazem.
1 <!--?
2 mysql_query("CALL pocitadlo_vloz(".$poc.", '".$ip."', @zapocitan)");
3 $zapocitan=mysql_result(mysql_query("SELECT @zapocitan"), 0);
4 echo $zapocitan;
5 ?-->
Budete-li chtít proceduru odstranit, stačí spustit SQL kód DROP PROCEDURE `nazev_procedury`. Status procedury pak příkazem: SHOW PROCEDURE STATUS LIKE 'nazev_procedury'. Pro zobrazení kódu by mělo fungovat SHOW PROCEDURE CODE `pocitadlo_vloz`, avšak to mi nefungovalo ani v konsoli, ani v PMA (dle manuálu funguje od verze 5.0.17, já mám 5.0.32). Pokud si však budete chtít zobrazit kompletní kód procedury v PMA, objevil jsem „fígl“: stačí si nechat vyexportovat strukturu tabulky.
Tak to by mělo být vše. Snažil jsem se to vysvětlit „česky“, snad se mi to povedlo.