Tvorba sloupce `webalized` pomocí MySQL

Dnes jsem řešil zajímavý problém a nakonec ho taky vyřešil, tak bych rád přihodil do diskuze :)

Situace: v MySQL tabulce uchovávám speciálně sloupec webalized kvůli „cool url“. Poté když přistoupím na web/clanek/jak-na-nette , porovnávám v databázi místo bežného id právě tento sloupec webalized. (Pokud vybírám články pomocí ID, které je v URL taky, není co řešit).

Ok, to by nebyl problém, prostě při přidávání článku vygeneruju „cool url“ a uložím ji k záznamu do tabulky. Ale co když chci, obyčejnému uživateli, dát možnost upravovat tuto tabulku a záznamy přidávat? Mohu mu napsat administraci, kde vše ošetřím ručně.

Jenže já chci použít automatizovaný nástroj na administraci. Konkrétně skvělý Adminer Editor.

Pak dojde na situaci, že uživatel s přístupem (např přes Adminer Editor) musí při změně názvu článku/podniku/… měnit i právě sloupec webalized. Což je pro něho naprosto nepochopitelné a celý web nám bude pravidelně rozsypávat :)

Řešení: Toto byla moje situace. Začal jsem se pídit po řešení přímo na vrstvě MySQL (engine innoDb). Zjistil jsem několik nepříjemných detailů. Například to, že MySQL nemá nic jako náhradu přes regexp, ba dokonce nedisponuje funkcí typu StrTr na jednoduchou náhradu znaků v řetězci.

Takže jsem si vytvořil jednoduchý PHP skriptík, který mi vygeneroval dost brutální SQL dotaz převádějící utf8 řetězec na ascii a taky vyhazuje různé nepovolené znaky.

Toto řešení není zdaleka tak obecné jako v případě třídy Strings, ale pro mé (a myslím, že pro většinu) případů to stačí. (Nelze použít nic jako „vše kromě [a-z] nahraď pomlčkou“ !).

Poté jsem sestavil trigger, který po přidání/editaci vezme zadaný sloupec nazev a vytvoří (doplní) sloupec webalized.

Teď už funguje vše jak má automaticky :) Ještě tam je pár věcí, které chci doupravit, ale to určitě každý zvládne sám ;)

Takže pro inspiraci, jak takový trigger vypadá, přikládám jeho kód (přidání):

DELIMITER |
CREATE TRIGGER tr1 BEFORE INSERT ON azyly
FOR EACH ROW
SET NEW.webalized=LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NEW.nazev,'á','a'),'ä','a'),'č','c'),'ď','d'),'é','e'),'ě','e'),'ë','e'),'í','i'),'ï','i'),'ň','n'),'ó','o'),'ö','o'),'ř','r'),'š','s'),'ť','t'),'ú','u'),'ů','u'),'ü','u'),'ý','y'),'ÿ','y'),'ž','z'),'Á','a'),'Ä','a'),'Č','c'),'Ď','d'),'É','e'),'Ě','e'),'Ë','e'),'Í','i'),'Ï','i'),'Ň','n'),'Ó','o'),'Ö','o'),'Ř','r'),'Š','s'),'Ť','t'),'Ú','u'),'Ů','u'),'Ü','u'),'Ý','y'),'Ÿ','y'),'Ž','z'),'`',''),'''',''),'"',''),'^',''),'~',''), ' ','-'), '!','-'), '\"','-'), '#','-'), '$','-'), '%','-'), '&','-'), '\'','-'), '(','-'), ')','-'), '*','-'), '+','-'), ',','-'), '-','-'), '.','-'), '/','-'), ':','-'), ';','-'), '<','-'), '=','-'), '>','-'), '?','-'), '@','-'), '[','-'), '\\','-'), ']','-'), '^','-'), '_','-'), '`','-'), '{','-'), '|','-'), '}','-'), '~','-'), '','-'));
END IF;
|
DELIMITER ;

A podobně editace:

DELIMITER |
CREATE TRIGGER tr2 BEFORE UPDATE ON azyly
FOR EACH ROW
SET NEW.webalized=LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NEW.nazev,'á','a'),'ä','a'),'č','c'),'ď','d'),'é','e'),'ě','e'),'ë','e'),'í','i'),'ï','i'),'ň','n'),'ó','o'),'ö','o'),'ř','r'),'š','s'),'ť','t'),'ú','u'),'ů','u'),'ü','u'),'ý','y'),'ÿ','y'),'ž','z'),'Á','a'),'Ä','a'),'Č','c'),'Ď','d'),'É','e'),'Ě','e'),'Ë','e'),'Í','i'),'Ï','i'),'Ň','n'),'Ó','o'),'Ö','o'),'Ř','r'),'Š','s'),'Ť','t'),'Ú','u'),'Ů','u'),'Ü','u'),'Ý','y'),'Ÿ','y'),'Ž','z'),'`',''),'''',''),'"',''),'^',''),'~',''), ' ','-'), '!','-'), '\"','-'), '#','-'), '$','-'), '%','-'), '&','-'), '\'','-'), '(','-'), ')','-'), '*','-'), '+','-'), ',','-'), '-','-'), '.','-'), '/','-'), ':','-'), ';','-'), '<','-'), '=','-'), '>','-'), '?','-'), '@','-'), '[','-'), '\\','-'), ']','-'), '^','-'), '_','-'), '`','-'), '{','-'), '|','-'), '}','-'), '~','-'), '','-'));
END IF;
|
DELIMITER ;

Ještě jednou podotýkám, že jsem to opravdu nevypisoval ručně ty znaky, ale prošel foreachem pole, které už nekdo pracně vytvořil :) (a prošel ASCII kvůli znakům převáděným na -)

Triggery AFTER INSERT ON / AFTER UPDATE ON nelze použít kvůli vazbě na sebe sama.

Pokud máte někdo zkušenost s lepším řešením, sem s tím :)

Btw: neefektivita je daní za pohodlnost. Navíc se provádí tento dotaz jen při přidávání, což v mém případě není tak často. A čekal jsem, že to bude pomalejší :)

Editoval lukyer (31. 8. 2011 3:24)