Tao webových aplikací a MySQL: jak předělat enumy na odkazy do externich tabulek

Pro začátek: tento článek není určen těm, kteří mají nulové povědomí o programovávání a návrhu SQL databází a současně chtějí, aby to tak taky zůstalo. Možná dobře dělají - ale takoví lidé taky k tomuto tématu naprosto nemají co dodat v komentářích.

Tento článek dále nejspíš bude nudit pokročilé návrháře složitějších webových aplikací, kteří jsou už dávno zvyklí si sami navrhovat tabulky a relace mezi nimi a nespoléhají se na různé hotové CMS frameworky. Pro ně bude nejspíš zbytečný. Bude lepší, když ho přestanou číst hned teď, než aby vznikaly v komentářích nějaké offtopic zmatky. (Samozřejmě - když si dají tu práci, a opraví mě v mých úvahách, tak to nakonec může taky mít význam).

Zmateni budou asi lidé, kteří se domnívají, že MySQL = PHPMyAdmin. Na druhou stranu - pokud mi tito uživatelé vysvětlí, že uvedený příklad se zrušením starého vícesloupcového primárního klíče a vytvorením nového LZE provést pomocí PHPMyAdmin, tak bude mírně překvapený já... (nezkoušel jsem to, nepožívám to).

A teď k věci: řadoví programátoři (codeři) mívají často nulové povědomí o návrhu databázových tabulek a relací mezi nimi - a co hůře, nemají pocit, že by to byl závažný problém. Je to z toho důvodu, že typický coder je (i v dnešní době) zvyklý používat v jemu známých programovacích (nebo formátovacích) jazycích jen poměrně malou úroveň abstrakce, žije v okouzlení z toho, jak komplexní aplikace je pomocí této nízké úrovně abstrakce vytvářet - a v první fázi se snaží složitějším úrovním abstrakce, které mu nabízí nějaké nové prostředí, pokud možno vyhnout - a použít pouze tu abstrakci, která se podobá té, na kterou je programátor už zvyklý.

(Tento problém je ve skutečností složitější, a týká se ve společnosti všech profesionálů, kteří si zvykli na celý svět pohlížet optikou svojí vlastí úzké specializace... někdy se tomu říká "profesionální deformace" ale s profesionalitou vykonávání nějaké činnosti to ve skutečnosti nesouvisí - jen s její četností: existují samozřejmě i "amatérské deformace" - víceméně jde o stereotypní myšlení jako takové)

Programátoři, kteří začínali pracovat v procedurálních programovacích jazycích, a nikdy se ani nesetkali s jinými přístupy, většinou mají pocit, že SQL je triviální a není u něj co řešit. Tedy - samozřejmě, že dnes je spousta lidí, kteří se sice nesetkali s konceptem logického nebo funkcionálního programování - ale zato vyrůstali v prostředí tzv. "klikacího programování" - tedy, jsou zvyklí si celou aplikaci v podstatě "nakreslit" v nějakém klikacím nesmyslu, z hlediska vstupních a výstupních sestav - a pak implementují pouze dílčí metody svázané s jednotlivými "událostmi" - takovýmto "klikacím programátorem" jsem nikdy nebyl, tak si vůbec ani nedovedu představit, jak tento druh programátorů vnímá abstraktní koncepty v programování. Pro mě je zdrojový kód programu především jakýmsi příběhem, vyprávěním - nevnímám ho příliš vizuálně.

Já sám jsem se myšlence psaní aplikací propojených s SQL databázemi dlouho vyhýbal: vyšel jsem kdysi z prostředí PC a MS-DOS, kde za ctnost byla považována co možná největší nezávislost aplikace na různých ovladačích nebo nedej bože předinstalovaných knihovnách - a představa, že moje aplikace vyžaduje ke svému běhu nějaký složitý předinstalovaný runtime, dokonce v nějaké konkrétní verzi, mě nejdříve dost odpuzovala. Moje první webové aplikace tedy nepracovaly s SQL tabulkami, ale s textovými soubory na disku (a bral jsem to natolik vážně, že jsem se přinutil řešit i záležitosti jako zamykání otevřených souborů kvůli hrozbě paralelního přístupu více běžících kopií aplikace k jednomu souboru, apod.). Ale protože svět kolem mě se vyvíjel jiným směrem, tak jsem byl donucen začít SQL přeci jen používat - a jako mnozí jiní programátoři: získal jsem pocit, že ve chvíli, kdy jsem se naučil INSERT, UPDATE, DELETE a SELECT, už nic dalšího vlastně nepotřebuju. Ale co je zajímavé - zjistil jsem, že toto si myslí i spousta jiných programátorů ! A co víc: za posledních 10 let, co se zabývám spíš opravováním chyb aplikací postavených nad SQL, jsem zjistil, že toto prostředí je vyloženě optimalizované tak, aby umožňovalo velmi snadný vznik "nefatálních" chyb - které buď brzdí přímo běh aplikace (chybné indexování tabulek nebo chybně sestavené dotazy bez přihlédnutí k použití indexů), nebo extrémně komplikují její pozdější úpravy.

Jako příklad ne-optimálního návrhu, který na začátku vypadá, že ušetří spoustu práce, ale rychle se ukáže, že jí přidělá spoustu navíc, uvedu použití výčtového typu - tedy typu SQL sloupce enum. Po letech praxe jsem dospěl k tomu, že tenhle sloupec byla téměř vždy použít - a zejména pokud obsahoval více než dvě hodnoty (zatím nevím o důvodu, proč ho nepoužívat jako pojmenovanou náhradu logického typu boolean). Jde přesně o ten typ vhledu, který mistr bojových umění není schopen žákovi vysvětlit jinak, než že ho nechá na sebe znovu a znovu útočit hrubou silou, kterou je snadné obrátit proti jemu samotnému: proč databáze vůbec umožňují použít sloupce typu enum, pokud to komplikuje údržbu aplikací, zeptal se učeník mistra ? A mistr ho praštil holí, ta hůl došla osvícení a začalo pršet - a tlesknutí jednou rukou může být plácnutí se do čela, ale také nemusí: zkrátka kromě "ano", "ne", "nevím" ještě existuje možnost "neumím to přesně vysvětlit, zkus to ode mě odkoukat, jak to dělám".

Standard Query Language (SQL) původně bylo navržený spíš jako uživatelský, než jako programátorský jazyk: ještě v učebnicích (či popularizačních publikacích) o informatice z 80. let 20. století převládala představa, že uživatelé se budou učit nějaký lidem i strojům srozumitelný jazyk, ve kterém by se ptali databázových systémů na to, co chtějí zjistit. Předpokládalo se, že půjde o jazyk se stručnou a přehlednou syntaxí (oproti tehdejším programovacím jazykům určitě), bez nutnosti deklarovat proměnné, který se uživatelé naučí, a budou ho používat v podstatě přímo jako uživatelské rozhraní. A budou přímo pracovat s výstupem, kterým databáze na jejich dotaz odpoví. Dnešní podoba využití tohoto jazyka, kdy SQL dotazy jsou nejčastěji sestavovány jiným programovacím jazykem (pozor - často v podstatě jazykem nižší úrovně! třeba PHP nabízí nesrovnatelně nižší úroveň abstrakce, než SQL) jako textové řetězce, na základě interaktivního vstupu z webové aplikace - a výstup je dále předáván přes až tři vrstvy abstrakce (kód aplikace, HTML šablony, CSS stylesheet) by podle mě jeho původní autory velice překvapila.

Mimochodem - followuju na Twitteru programátorku, která změnila parametr "pohlaví" v enginu Diaspory (hypotetické decentralizované náhrady Facebooku, která je bohužel podle mě z řady objektivních příčin odsouzena k všeobecnému nezájmu širší veřejnosti) z boolovského přepínátka "Male/Female" na textové políčko, kam si kdokoliv může napsat cokoliv: a samozřejmě jí fandím ! Sice čistě logicky dávají smysl i hodnoty "muž/žena/jiné/neuvedeno" a k tomu možnost nastavit míru zveřejnění libovolných hodnot z profilu - ale kouzlo anti-byrokratické subverze spočívá právě v možnosti rozlišit třeba hodnoty "muž/žena/a není to jedno ?" - což je emocionálně dost odlišné od "muž/žena/jiné/neuvedeno" - v podstatě jde hlavně o vytvoření informačního šumu, který dále zhoršuje možnost zneužití osobních údajů. Ale zpět k optimalizacím...

Přidávání "přívlastků" k hodnocení článků a komentářů, které jsem před nedávnem implementoval, bylo učebnicovým příkladem chybného návrhu SQL tabulky. V důsledku toho jsem se postupně dostával do větších a větších problémů, a zkomplikoval si možnost přidávání nových moderačních úrovní - až se nakonec začaly objevovat další, zcela nečekané chyby, které už jsem ani nebyl schopen v kódu aplikace dohledat. Naštěstí nejsem jaderný inženýr - takže chybná koncepce implementovaná v mé aplikaci má jen minimální šanci způsobit evakuaci obyvatelstva z rozsáhlého území - maximálně se někomu na obrazovce neobjeví to, co čeká, a já se tím zcela lokálně znemožním. Ale přesně to je ta chvíle, kdy vám alespoň mírně pokročilý programátor řekne "tak teď přišel čas celou aplikaci od základu přepsat". A já se vám teď pokusím předvést, že jsou chvíle, kdy to je pravda.

Proces přestavby designu aplikace předvádím pouze na jediné tabulce guestbook - v této tabulce se původně vyskytoval enum sloupec pojmenovaný modtext:

CREATE TABLE `guestbook_mod` (
  `id` int(11) NOT NULL default '0',
  `modcount` int(11) NOT NULL default '1',
  `modtext` enum('informativní', 'vtipné', 'zábavné', 'chytré', 'zajímavé', 'moudré', 'cool', 'sexy', 'offtopic', 'spam', 'troll', 'lživé', 'nepřesné', 'nudné', 'provokativní', 'agresivní', 'hloupé', 'morbidní', 'trapné', 'trefné', 'sexistické', 'výstižné', 'faq', 'kvalitní', 'nekvalitní', 'krásné', 'ošklivé', 'pravdivé', 'nedomyšlené', podivné', 'zlé', 'debilní') NOT NULL default 'informativní',
  PRIMARY KEY  (`id`,`modtext`),
  KEY `id` (`id`)
)

Abych mohl zrušit z mnoha důvodů nepraktický enum sloupec (tyto důvody důkladně prodiskutuju v závěru článku), založil jsem zcela novou tabulku, umožňující přidávat nové správcem webu definované moderační úrovně i bez modifikace kódu:

CREATE TABLE `modlevels` (
  `id` int(3) NOT NULL auto_increment,
  `modtext` varchar(16) NOT NULL default '',
  `modpositive` enum('','y') NOT NULL default '',
  `modnegative` enum('','y') NOT NULL default '',
  `modclanek` enum('','y') NOT NULL default 'y',
  PRIMARY KEY  (`id`)
) 

+----+--------------+-------------+-------------+-----------+
| id | modtext      | modpositive | modnegative | modclanek |
+----+--------------+-------------+-------------+-----------+
|  1 | informativní | y           |             | y         |
|  2 | vtipné       | y           |             | y         |
|  3 | zábavné      | y           |             | y         |
|  4 | chytré       | y           |             | y         |
|  5 | zajímavé     | y           |             | y         |
|  6 | moudré       | y           |             | y         |
|  7 | cool         | y           |             | y         |
|  8 | sexy         | y           |             | y         |
|  9 | trefné       | y           |             | y         |
| 10 | výstižné     | y           |             | y         |
| 11 | kvalitní     | y           |             | y         |
| 12 | faq          | y           |             | y         |
| 13 | krásné       | y           |             | y         |
| 14 | přesné       | y           |             | y         |
| 15 | dlouhé       | y           | y           | y         |
| 16 | stručné      | y           | y           | y         |
| 17 | podivné      | y           | y           | y         |
| 18 | provokativní | y           | y           | y         |
| 19 | offtopic     |             | y           |           |
| 20 | spam         |             | y           |           |
| 21 | troll        |             | y           |           |
| 22 | lživé        |             | y           | y         |
| 23 | nepřesné     |             | y           | y         |
| 24 | nudné        |             | y           | y         |
| 25 | agresivní    |             | y           | y         |
| 26 | hloupé       |             | y           | y         |
| 27 | morbidní     |             | y           | y         |
| 28 | trapné       |             | y           | y         |
| 29 | sexistické   |             | y           |           |
| 30 | nekvalitní   |             | y           | y         |
| 31 | ošklivé      |             | y           | y         |
| 32 | zlé          |             | y           | y         |
| 33 | debilní      |             | y           |           |
| 34 | pravdivé     | y           |             | y         |
| 35 | nedomyšlené  |             | y           | y         |
+----+--------------+-------------+-------------+-----------+
35 rows in set (0.00 sec)

Poznámka: zařazení posledních dvou řádek na konec nemá jinou logiku, než "i mistr tesař se někdy utne" :-) prostě jsem záznamy vytvářel ručně a tyto dva přehlédl. A také na tomto místě svým čtenářům (kteří mě milují, a nejspíš by riskovali i zablokování svých internetových přípojek, aby se mě zastali ! vždyť vlastně na TečceCZ ani nemám žádnou formální funkci... jsem jen morální autorita... něco jako Kaddáfí, tedy ehm, anglická královna :-) ) mezi řádky sděluji, že pokud se dohodnou, že nějaký modlevel u komentářů nebo článků by bylo výslovně vhodné ubrat nebo přidat, tak komentář pod tímto článkem je dost možná to pravé místo, kde o tom můžeme diskutovat...

Přidal jsem k tabulce guestbook_mod (ve které udržuji "přívlastky" přidané ke komentářům) nový sloupec modlevel:

ALTER TABLE guestbook_mod ADD COLUMN modlevel int(3) DEFAULT 0;

Pokud by návrh databáze měl být zcela čistý,tak by tento sloupec měl být správně označený jako FOREIGN KEY: nicméně já jsem v tomto případě prase, které vyvíjí kód pouze pro vlastní potřebu - a bohatě mi postačí, že případné nesmyslné hodnoty v tomto sloupci vyústí v nezobrazení odpovídajících hodnot textových. Ale pokud byste se chtěli naučit navrhovat databáze opravdu korektně, tak si význam definice mezitabulkové relace pomocí FOREIGN KEY samozřejmě nastudujte - ne vždy je situace tak triviální, jako u primitivivního CMS systému... no dobře, tak teda že jste to vy, milí čtenáři, tak tedy tady máte trochu databázové vyšší dívčí:

ALTER TABLE guestbook_mod ADD FOREIGN KEY (modlevel) REFERENCES modlevels(id) ON DELETE RESTRICT;

Nastavil jsem hodnoty tohoto nového sloupce jediným SQL příkazem (ano, přesně toto jsou momenty, kvůli kterým se vyplatí se SQL naučit, a nespoléhat se na klikací frontendy typu PHPMyAdmin nebo na to, že vaše znalosti procedurálního programování vás vždy vytrhnou...):

UPDATE guestbook_mod SET modlevel=(SELECT id FROM modlevels WHERE modlevels.modtext=guestbook_mod.modtext);

Původní jednoduchý SQL příkaz vybírající hodnoty z jediné tabulky ...

SELECT modtext,modcount FROM guestbook_mod  WHERE id=$id ORDER BY modcount DESC

... jsem nahradil složitějším:

SELECT modlevels.modtext,modcount FROM guestbook_mod JOIN modlevels ON modlevel=modlevels.id WHERE guestbook_mod.id=$id ORDER BY modcount DESC

(upřesnění modlevels.modtext bylo nutné zapsat takto - protože celou aplikaci jsem přepisoval za chodu, nad "živou" databází - a starý enum sloupec modtext přímo v tabulce guestbook_mod v té době ještě existoval)

Zcela závěrečnou fází úpravy bylo nakonec vymazání starého enum sloupce z tabulky: toto nebylo tak triviální, jak se na první pohled zdá, protože v původním návrhu tabulky jsem jako PRIMARY KEY použil dvojici sloupců (pro každou kombinaci id komentáře a přívlastku se počítal jejich počet): pamatuji se, že poprvé, když jsem se setkal s nutností rušit sloupec, který byl součástí PRIMARY KEY, jsem byl poněkud zmaten, takže i někdo jiný možná shledá následující informaci užitečnou:

ALTER TABLE guestbook_mod DROP PRIMARY KEY;
ALTER TABLE guestbook_mod DROP COLUMN modtext;
ALTER TABLE guestbook_mod ADD PRIMARY KEY (id,modlevel);

Zdrojový kód celé aplikace (ten tady necituji jednak kvůli rozsahu, jednak proto, že se stydím, že jsem pořád nucen pracovat nad bastlem vyvinutým původně ještě v PHP3) se po této úpravě značně zjednodušil zejména v těch místech, kde jsem musel kvůli chybějící informaci o použité znakové sadě v ajaxových dotazech převádět enumy zcela zbytečně na číselné hodnoty - a potom opět zpět z číselných hodnot na text. Zdánlivá úspora, které jsem dosáhl díky tomu, že hodnoty typu enum se dají přímo vypisovat do výsledné webové stránky, mě nakonec přidělala neuvěřitelné množství práce navíc: tzv. efekt "líný se strhal". Totiž i kdyby enum hodnoty neobsahovaly češtinu a bylo tím pádem možné si odpustit konverzi na číselné hodnoty uvnitř HTML formulářů a HTTP dotazů - tak stejně přidání nebo ubrání hodnoty znamenalo vždy zásah do kódu aplikace (např. v mém případě by bylo nutné ručně sestavovat rozbalovací menu pro pozitivně a negativně hodnocené komentáře a články). Použití externí tabulky s hodnotami je pro webové aplikace vhodnější už je právě kvůli možnosti automatického generování různých uživatelských nabídek: přidání nebo ubrání položky nyní nevyžaduje ani změnu zdrojového kódu, ani změnu struktury databáze. Toto představuje zajímavý příspěvek i o debaty o open source a free software a o užitečnosti otevřeného zdrojového kódu: zatímco intuitivní přístup předpokládá, že přínosem open source aplikací je to, že uživatel může změnu tohoto typu v aplikaci provést sám - tak skutečným přínosem open source je to, že aplikace postupně konvergují do stavu, kdy mohou být přizpůsobeny obrovské škále různých zadání pouhým zásahem do konfiguračních souborů a tabulek. Pravděpodobnost, že někdo upraví jednoúčelovou aplikaci tak, aby se stala univerzálně použitelnou, je vyšší právě u otevřeného vývojové modelu - což dnes stále ještě znamená především otevřený zdrojový kód v kombinaci s některou z free software licencí. Komerční dodavatelé software mají zájem dodávat bezplatné či levné "demoverze" odděleně od "prémiových verzí" - a nemají zájem na takových zásazích do zdrojového kódu, které by program učinili univerzálněji použitelný.

Možná vás zajímá, jak souvisí tato krátká přednáška z úvodu do databázových systémů (což je VŠ předmět, u kterého z odstupem let FAKT lituju, že jsem u něj namyšleně zanedbal docházku na přednášky... jenže já se k smrti nerad učím jinak, než na konkrétních příkladech... a v posluchárně jsem se navíc vždy nudil a nedokázal soustředit, tabule byla velmi neinteraktivní a velmi daleko, atd...) s mými dlouhodobými rozmáchlými plány na vývoj open source decentralizované sociální sítě, schopné konkurovat centralizovaným komerčním projektům ? Inu - snažím se přemýšlet, které design patterns jsou natolik dobré, že povedou k přirozené kompatibilitě informačních systémů, které se jich drží. Pokud se systémy mezi sebou propojí pomocí jednotného API, tak je kupříkladu jedno, jak se budou jmenovat jednotlivé sloupce lokálních databázových tabulek: název článku bude možné vždy chápat jako jeho název, součet hodnotících bodů zůstane součtem bodů, apod. Podobně, jako vyprávění určená lidem se dají dělit do jednotlivých žánrů (detektiva, komedie, tragédie, apod.), tak i víceuživatelské informační systémy v podstatě spadají do několika základních žánrů (cms, diskuzní fórum, sociální síť, eshop...) - a stejně jako příběhy vyprávěné v lidských jazycích lze překládat z jednoho jazyka do druhého (alespoň v rámci stejných kulturních okruhů) - tak i informační systémy by mezi sebou mělo jít při dodržení správných design patterns propojovat bez ohledu na jemné nuance jejich vnitřní architektury: takové operace, jako import komentáře z jiného systému pak může znamenat přidání položky do některé dílčí datové struktury (např. nový typ hodnocení, který dosud nebyl nabízen) - ale nebude vyžadovat přeprogramování samotné aplikace.