Letmý pohled na Alfu MySQL 5.0 z pohledu postgresisty

Před několika lety, když jsem si začínal hrát s SQL, byla MySQL první nainstalovanou SQL databází na mém počítači. A také první odinstalovanou. SQL jsem rozuměl jak koza petrželi a v MySQL mi nešly přepsat SQL příkazy z moudrých knížek, které jsem tehdy měl po ruce. Zkusil jsem PostgreSQL tehdy ve verzi 6.x a zůstal u ní. Všechny konstrukce, o kterých se tehdy psalo, zvládala. Navíc jsem si mohl poprvé vyzkoušet uložené procedury, které měly po zkušenostech okolí s MSSQL 6.x chuť zakázaného ovoce. Prostě nostalgicky na to vzpomínám. V prosinci MySQL AB ohlásili 5.0 Alfu, která by měla zvládat jak složitější SQL dotazy, tak uložené procedury, a já se nemohl dočkat, až si ji u sebe vyzkouším. Následující text si nehraje na důkladnou analýzu, obsahuje vlastně jen pár postřehů.

Instalace je na mém RH9 vcelku bezproblémová. Jediným drobným zádrhelem bylo, že uživatel mysql neměl přístupová práva do /usr/local/var, což dokumentace tiše předpokládá. Defaultní umístění databází je jiné než v Pg. Pro každou databázi se v /usr/local/var vytváří podadresář, soubory podpory InnoDB tabulek se vytvářejí také v tomto adresáři (pokud nezměníte konfiguraci).

cd zdroj
make
make install
do /etc/ld.so.conf pridat /usr/local/lib/mysql
ldconfig
cp zdroj/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
cp zdroj/support-files/mysql.conf /etc/my.cnf
mysql_install_db
mysqladmin -u root password 'newpass'
groupadd mysql
useradd -g mysql mysql
chown -R mysql /usr/local/var/mysql
chgrp -R mysql /usr/local/var/mysql
chmod o+rwx /usr/local/var
/etc/init.d/mysql start
mysqladmin create testdb
mysql testdb

MySQL verze 5.x umí prakticky vše, co zhýralý programátor čeká od databáze: uložené procedury, dotazy parametrizovatelné uživatelskými (session) proměnnými, vnořené dotazy, CASE, regulární výrazy. Sice se sem tam najde drobná vada na kráse, ale tváří se to hezky a na alfu i celkem použitelně. Některé konstrukce bych rád viděl i v PostgreSQL (v pohodlí práce s příkazovou řádkou má stále databáze s delfínkem co dohánět). Příkazy konzole jsou jen o trochu jiné. Pro mne jediným zásadním rozdílem je \. – provedení příkazů ze souborů.

SELECT @i:=3;
SELECT * FROM zamestnanci WHERE id=@i;

nebo jinak (číslování řádků)

SELECT @x:=0;
SELECT @x:=@x+1, zamestnanci.* FROM zamestnanci;

PostgreSQL něco podobného umí, ale pouze na úrovni psql, ne jako sql příkazy, takže parametrizaci nemůžeme použít ve svých aplikacích. Navíc nezvládne výrazy. Příkaz set nemůže být součástí sql výrazu. Zvládne na druhou stranu příkazy shellu (funkcionalita psql se pravděpodobně rozšíří o cykly a podmínky – stále se ale bude jednat o řešení na úrovni psql).

\set txt `links -dump  /clanek/1987`|sed "s/'/''/g"`
\set txt '\'':txt'\''
INSERT INTO texty VALUES(:txt); 

Vnořené dotazy: MySQL zvládla všechno, co mne napadlo. Sledoval jsem pouze schopnost provést dotaz, nikoliv už efektivitu provádění dotazu. Pohledy ještě tato alfa nepodporuje (v ToDo pro 5.0).

SELECT nazev, prijmeni FROM oddeleni, zamestnanci
  WHERE zamestnanci.odd = oddeleni.id AND
    zamestnanci.mzda =
      (SELECT max(mzda) FROM zamestnanci WHERE odd = oddeleni.id);

SELECT (SELECT 1) FROM (SELECT * FROM zamestnanci) z; 

Dokumentace k uloženým procedurám existuje, najdeme ji na www.mysql.com/doc/en/­Stored_Procedu­res.html. Potřebovala by HODNĚ doplnit. Oproti PostgreSQL zvládá nejen funkce, ale i procedury s INOUT parametry (PostgreSQL podporuje pouze IN, které nezvládá MySQL :-)). Naopak nemůže psát SRF funkce a zatím ani triggery. Chybí také možnost dynamicky vykonávat sql dotazy, vyvolat chyby, jakákoliv rozumná možnost trasování (lze plnit globální proměnné). Ve funkcích není dovoleno (je omezeno) používání SQL příkazů. Ostatní jsou víceméně nuance, tj. tam, kde PostgreSQL má vzor Oracle, mysql DB2 (SQL2003 syntaxe pro uložené procedury). Množiny jsou přístupné pouze pomocí kurzorů. Nicméně i s těmito funkcemi se dají dělat věci. Občas ještě ne úplně všechno zafunguje.

DROP PROCEDURE foo;
DELIMITER ~;

CREATE PROCEDURE foo(OUT param VARCHAR(1000))
BEGIN
  DECLARE c CURSOR FOR SELECT prijmeni FROM zamestnanci;
  DECLARE done int DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  DECLARE a VARCHAR(1000) DEFAULT '';
  DECLARE p VARCHAR(100);
  OPEN c;
  REPEAT
    FETCH c INTO p;
    IF NOT done THEN
      IF a = '' THEN
        SET a = p;
      ELSE
        SET a = CONCAT(a, ', ', p);
      END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE c;
  SET param = a;
END; ~
DELIMITER ;~

CALL foo(@a);
SELECT @a; --> Kresl, Bidlák, Stehule

DROP FUNCTION foof;
DELIMITER ~;

CREATE FUNCTION foof() RETURNS VARCHAR(1000)
BEGIN
  DECLARE c CURSOR FOR SELECT prijmeni FROM zamestnanci;
  DECLARE done int DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  DECLARE a VARCHAR(1000) DEFAULT '';
  DECLARE p VARCHAR(100);
  OPEN c;
  SET @b = '';
  REPEAT
    FETCH c INTO p;
    IF NOT done THEN
      SET @b = CONCAT(@b, ' ', p);
      IF a = '' THEN
        SET a = p;
      ELSE
        SET a = CONCAT(a, ', ', p);
      END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE c;
  SET @b = a;
  RETURN a;
END; ~
DELIMITER ;~

SELECT foof();  --> Stehule ????
SELECT @b;      --> Kresl, Bidlák, Stehule 

totéž v PL/pgSQL

CREATE OR REPLACE function foo() RETURNS varchar AS '
DECLARE a varchar = ''''; p varchar;
BEGIN
  FOR p IN SELECT prijmeni FROM zamestnanci LOOP
    IF a <> '''' THEN
      a := a ||'',''||p
    ELSE
      a := p;
    END IF;
    RAISE NOTICE ''a = %'', a;
  END LOOP;
  RETURN a;
END; 'LANGUAGE plpgsql; 

Obsahuje obsluhu chyb (to PostgreSQL chybí), ve výše zmíněném příkladu se bez ní ani neobejde.

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 

Při chybě 02000, tj. nenalezen záznam, se lokální proměnné nastaví na hodnotu 1 (následně se ukončí cyklus).

Dočasné tabulky vytvářené v uložené proceduře mají pouze session platnost (stejně jako v PostgreSQL). To mírně komplikuje procedury, které si vytvářejí vlastní dočasné tabulky – například pro uložení mezivýsledků, atd Nejedná se ovšem o žádný těžký problém, stačí ošetřit na začátku SP chybový stav (něco podobného v PostgreSQL v tuto chvíli nejde, resp. musíme si zjistit, zdali tabulka náhodou už neexistuje, a potom ji případně nevytvářet – příklad kódu).

DROP PROCEDURE tmpfoo;
DELIMITER ~;
CREATE PROCEDURE tmpfoo()
BEGIN
  DECLARE existuje int DEFAULT 0;
  -- ERROR 1050 (42S01): Table already exists
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S01' SET existuje=1;
  CREATE TEMPORARY TABLE aaa(x integer);
  IF existuje = 1 THEN
    DELETE FROM aaa;
  END IF;
  INSERT INTO aaa VALUES(10);
  SELECT COUNT(*) INTO @b FROM aaa;
  DROP TABLE aaa;
END; ~ 

Uložené procedury v MySQL nemají žádný problém s dočasnými tabulkami jako v PostgreSQL, kde k dočasným tabulkám můžeme přistupovat pouze prostřednictvím dynamických dotazů. PostgreSQL boužel nedokáže dynamicky překompilovat uložené procedury podle potřeby – v okamžiku, kdy je neplatné id dočasné tabulky.

CREATE OR REPLACE FUNCTION tmpfoo() RETURNS bool AS '
DECLARE pocet integer; rec RECORD;
BEGIN
  PERFORM 1 FROM pg_catalog.pg_class c
    WHERE c.relname=''aaa'' AND pg_catalog.pg_table_is_visible(c.oid)
     AND c.relkind=''r'';
  IF NOT FOUND THEN
    CREATE TEMPORARY TABLE aaa(x integer);
  ELSE
    EXECUTE ''DELETE FROM aaa'';
  END IF;
  EXECUTE ''INSERT INTO aaa VALUES(10)'';
  FOR rec IN EXECUTE ''SELECT COUNT(*) AS c FROM aaa'' LOOP
    pocet := rec.c;
  END LOOP;
  RAISE NOTICE ''V tabulce bylo % zaznamu'', pocet;
  EXECUTE ''DROP TABLE aaa'';
  RETURN ''t'';
END; ' LANGUAGE plpgsql; 

Procedury chodily tak, jak měly, volání funkce vracelo nesmysly. Netuším, zdali to bylo nedovoleným použitím SELECTu ve funkci, nebo něčím jiným.

       

Jen tak nevážně jsem si vyzkoušel rychlost interpretace (jedná se přeci jen o Alfu a uložené procedury dlouhou dobu byly pro MySQL AB na okraji zájmu). Na jednoduchém příkládku hledání největšího společného dělitele se ukazuje (zatím), že interpret MySQL je zhruba dvacetkrát pomalejší než interpret PL/pgSQL. Je mi ale jasné, že rychlost interpretu není všechno, zvlášť ne při programování uložených procedur.

DROP FUNCTION delitel;
DELIMITER ~;

CREATE FUNCTION delitel(a int, b int) RETURNS int
BEGIN
  WHILE a <> b DO
    IF a > b THEN SET a = a - b; ELSE SET b = b - a; END IF;
  END WHILE;
  RETURN a;
END; ~
DELIMITER ;~

SELECT delitel(263535344, 868); --> cca 10 sec oproti 460 milisec PostgreSQL
SELECT delitel(263535344, 868);
SELECT delitel(263535344, 868);
SELECT delitel(263535344, 868); 

Chování a vlastnosti MySQL na mě celkem udělaly dojem i přes několik nesporných chyb (referenční integrita, ačkoliv mám locales nastaveno správně – def. charset mám švédštinu). Zřetelně je znát, že vývojáři MySQL si jdou svou vlastní cestou, kdy jejich cílem zůstává malá a rychlá SQL databáze, přednost má funkcionalita a jednoduchost před „stylovou čistotou“ (např. umožnění přístupu do cizích databází bez podpory dvoufázového potvrzování). A inspiraci hledají jak u IBM (syntaxe uložených procedur), tak u Oracle (budoucí syntaxe rekurzivních dotazů).