Uložení jazykových verzí

Školení, která pořádám

Představte si, že máte vytvořit vícejazyčný web, který bude obsahovat nějaké výrobky. Ty budou mít některé položky společné (id, cena), jiné budou jazykově specifické (název, popis). Přepínání jazyka na webu popíšu někdy jindy, teď bych se rád věnoval tomu, jak se data dají uložit do databáze.

Pokud se v jazykových verzích mají zobrazovat tatáž data, rovnou můžeme zavrhnout myšlenku, že by se jednotlivé jazykové verze ukládaly do zvláštních databází nebo by byly na úrovni záznamu odlišeny identifikátorem jazyka – tedy např. vyrobky(id int, jazyk enum('cs', 'en'), nazev varchar(50), popis text, cena int). Tento způsob by totiž velice snadno mohl vést k nekonzistenci jazykových verzí, navíc by se společná data zbytečně ukládala duplicitně.

Nabízí se pro každou překládanou hodnotu a každou jazykovou verzi vytvořit samostatný sloupec, tedy např. vyrobky(id int, nazev_cs varchar(50), nazev_en varchar(50), popis_cs text, popis_en text, cena int). Toto řešení má spoustu výhod – data jsou snadno dostupná a dají se dobře indexovat, aplikace se navíc od jednojazyčné nemusí moc lišit:

<?php
// jednojazyčná aplikace
mysql_query("UPDATE vyrobky SET nazev = '$nazev' WHERE id = $id");
$row = mysql_fetch_assoc(mysql_query("SELECT * FROM vyrobky WHERE id = $id"));
echo $row["nazev"];

// vícejazyčná verze - v proměnné $LANG je uložen aktuální jazyk
mysql_query("UPDATE vyrobky SET nazev_$LANG = '$nazev' WHERE id = $id");
$row = mysql_fetch_assoc(mysql_query("SELECT * FROM vyrobky WHERE id = $id"));
echo $row["nazev_$LANG"];
?>

Řešení má samozřejmě i nevýhodu – pokud se rozhodneme přidat další jazykovou verzi, je potřeba změnit strukturu databáze. Dá se na to samozřejmě napsat skript, je to ale další práce navíc. Pokud musí aplikace přidání jazyka umožňovat bez zásahu do struktury databáze, je nutné jazykové verze vyřešit jinak. Nejlepší asi je vytvořit tabulku, do které se budou ukládat jazykové hlášky a vhodně to propojit. Možnosti vidím tyto:

  1. vyrobky(id int, cena int), vyrobky_texty(id int, jazyk char(2), nazev varchar(50), popis text)
  2. vyrobky(id int, nazev int, popis int, cena int), texty(jazyk char(2), kod int, preklad text)
  3. vyrobky(id int, cena int), texty(jazyk char(2), tabulka varchar(30 64), sloupec varchar(30 64), radek int, preklad text)

První způsob předpokládá vytvoření další tabulky pro každou tabulku, která obsahuje jazykové hlášky. Druhý a třetí způsob naopak ukládá všechny jazykové hlášky do sloupce typu TEXT. Asi nemusím popisovat, že provádění dotazů nad všemi těmito strukturami není právě pohodlné. Celkově vzato je podle mého názoru jeden způsob horší než druhý a menší zlo je při přidání jazykové verze změnit strukturu databáze. Pokud to možné opravdu není, asi bych raději pořádně pohlídal možnou nekonzistenci dat a použil vyrobky(id int, jazyk char(2), nazev varchar(50), popis text, cena int).

Pro usnadnění přidání jazyka je samozřejmě nutné PHP kód psát nezávisle na dosud existujících jazycích:

<?php
// špatně rozšiřitelné
$set["nazev_cs"] = "'" . mysql_real_escape_string($_POST["nazev_cs"]) . "'";
$set["nazev_en"] = "'" . mysql_real_escape_string($_POST["nazev_en"]) . "'";

// snadno rozšiřitelné - stačí upravit pole $LANGS
foreach ($LANGS as $lang) {
    $set["nazev_$lang"] = "'" . mysql_real_escape_string($_POST["nazev_$lang"]) . "'";
}
?>

Někdy se také může stát, že některá jazyková verze není k dispozici kompletní. Pokud chceme mít co nejjednodušší a nejrychlejší dotazy pro získání dat, je možné do nepřeložených sloupců ukládat verzi, která je k dispozici vždy (např. anglickou). Je ale potřeba dát pozor na aktualizace – při úpravě záchranné jazykové verze je potřeba změnit i verze na ní závislé. Druhá nevýhoda spočívá v tom, že není poznat, které texty už přeložené jsou a které ne – na základě shody to možné není, protože některé texty mohou mít stejný překlad do různých jazyků. Vyřešit to lze např. přidáním speciálního znaku na konec nepřeložených textů a jeho odstraňováním při výpisu, to ale není právě nejčistší způsob. Třetí nevýhoda spočívá ve větších diskových nárocích, ta je ale nahrazena rychlejším získáváním dat. V řadě případů proto asi bude lepší místo nepřeložených textů ukládat NULL a záchrannou verzi načítat až při získávání dat:

<?php
// jeden způsob
$row = mysql_fetch_assoc(mysql_query("SELECT * FROM vyrobky WHERE id = $id"));
echo (isset($row["nazev_$LANG"]) ? $row["nazev_$LANG"] : "<span lang='en'>$row[nazev_en]</span>");

// druhý způsob
$row = mysql_fetch_assoc(mysql_query("SELECT IFNULL(nazev_$LANG, nazev_en) AS nazev FROM vyrobky WHERE id = $id"));
echo $row["nazev"];
?>

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Řešení problému, 4.5.2005, diskuse: 24 (nové: 0)

Diskuse

Petr:

Chces rict, ze se vyplati, kdyz ma treba vyrobek 20 textovych polozek a existuje 5 mutaci, vytvaret tabulku o 100 sloupcich?

Pavel:

"provádění dotazů nad všemi těmito strukturami není právě pohodlné" ... jestliže použiji <flame>opravdovou</flame> databázi, která umí pohledy, tak PHP vývojáře toto vůbec nemusí zajímat a navíc mohu na úrovni databáze ošetřit chybějící překlad požadovaného textu

lamka:

Asi tak. Nejvíc se děsím toho, že z mysql se sice za chvilku taky stane OPRAVDOVÁ DATABÁZE, ovšem "programátoři" budou pořád stejní a jejich techniky pořád stejně špatné. Ne nadarmo výuka db probíhá na Oracle! Řešit jinak chybějící vlastnosti db je jednodušší než se zbavovat chybných návyků.

(pozn. pro pana bloggera - ne, lamka nespolkl všechu chytrost světa, ale taky nepíše blog, kde by se snažil přenášet na ostatní své chybné poznatky.)

ikona Jakub Vrána OpenID:

Bohužel zase jenom štěkáte místo uvádění konkrétních faktů.

Fanda:

Mohl byste nastínit strukturu databáze pro použití pohledů jak zmiňujete?

ikona Jakub Vrána OpenID:

Viz http://php.vrana.cz/ulozeni-prekladu-do-samostatne-tabulky.php. Není to nic pěkného ani s opravdovou databází.

migon:

ja bych rekl ze ano :-) je to tak, u te aplikace musis predpokladat ze se rozsiri

Solvina:

Uz jsem dlouho zadny multijazycny web nepsal, nicmene vzdycky jsem se snazil pouzivat postup cislo 1 - z vice duvodu.
Nejdulezitejsi je cistota navrhu databaze, program, ktery modifikuje db strukturu mi neprijde jako dobry napad (zde uvedeny Jirkuv priklad je AFAIK __jediny__ dobry duvod proc tohle zverstvo povolit).
Spolecne s prekladem polozek v db (webshop) je potreba resit preklady statickych textu - nejhorsi mozna varianta je nejaka obdoba getLang($str){global $lang;...}; a v ruku v ruce jdouci kusy kodu: if($lang==""){dlouhy HTML text}elseif($lang==""){...}, neni spatne pouziti gettext, ale imho je nelepsi pouzit oddelene nezavisle jazykove verze, templaty a nastroj na porovnavani (diff) s inteligentnim filtrovanim. (V tomto pripade ani ukladani vicejazycnych verzich v db neresite, mate jednu db pro kazdou jazykovou verzi - nehodi se pro klasicke webshopy, ale spis pro nas chudaky, co zdedili velky 'IS' psany v php)

No a mit normalni databazi (tohle nema byt zaklad flame - firebird, postgresql) s views, stored procedures, tak neni potreba modifikace db tabulek.

Fak:

tak jelikož MySQL 5 už ty věci umí, tak by mě zajímalo, jak řešit problém překladu s views a stored procedures.. poradíte?

Marabu:

Pokud je id integer (což píšete v definici tabulky), proč ho dáváte do apostrofů? Ty jsou vyhrazené pro textové řetězce, numerické se nequotují.

ikona Jakub Vrána OpenID:

Čísla se do apostrofů uzavírat mohou (přinejmenším v MySQL) a já jsem tomu uvykl kvůli ošetření nedůvěryhodných dat - http://php.vrana.cz/obrana-proti-sql-injection.php.

Marabu:

Jenže vy ta nedůvěryhodná data takhle neošetřujete, vy je cpete přímo do databáze s předpokladem, že když ta data nebudou taková jako čekáte, tak prostě nic nevypadne - místo abyste je tam dal rovnou korektní? To je typická ukázka zpraseně-PHPčkovského-netypového uvažování.

ikona Jakub Vrána OpenID:

Příklad vhodného použití tohoto způsobu: Pokud mám na webu odkazy vždy jen na numerické ID, tak se nemusím bát toho, že by při korektním používání aplikace byla data nesprávná.

Když se na mou aplikaci pokusí někdo zaútočit tak, že mi podstrčí nenumerické ID, tak nesmím dopustit, aby provedl nějakou neplechu, ale nemusím s ním jednat v rukavičkách typu "Litujeme, ale výrobek s ID 'abc' neexistuje."

Ošetřena samozřejmě může být neexistence záznamu (např. pro odhalení již nefunkčních odkazů), ale to z typu dat nepoznám - poznám to až z dat uložených v databázi.

Petr:

A co třeba if ((int)data_od_uzivatele_nebo_url === 0) { ... } else { ... } ?? Prostě si to raději pohlídám jako int, než se spoléhat na to, že to budu hledat v db a nenajdu atd ...

ikona Jakub Vrána OpenID:

Asi jsi nepochopil, o čem mluvím. Legitimní nečíselné odkazy neexistují. Když ho někdo zadá ručně, mohu s tím zacházet stejně jako když zadá neplatné číslo.

Ján Koštial:

Majte so mnou strpenie... Píšem svoj prvý multijazyčný web a chcel by som, keď návštevník navštívi stránku po prvýkrát, aby sa nastavila jazyková verzia podľa jeho browsera/OS (pokiaľ taká existuje).
Akým spôsobom je možné zistiť jazyk klienta?
Vyparsovať z HTTP_USER_AGENT ?

ikona dgx:

Zjišťuje se to z hlavičky accept-language. Viz poslední komentář na http://diskuse.jakpsatweb.cz/index.php?action=…&topic=10613

Marek:

Zdravím. Potýkal jsem se teď také s problémem vícejazyčného webu a... měl jsem podobný nápad jako Jakub Vrána (nazev, nazev_$lang), ale portál měl být flexibilní, co se týče přidávání jazyků;-) styl dvou tabulek zustal, ale misto nazev_$lang jsem ji změnil na nayev_lang a nazev konkretniho jazyka jsem dal do sloupce... a funguje to dost obstojne. pomoci JOIN neni problem, kdyz jedna verze neexistuje tak vypsat defaultni.

Palo:

Myslím, že takéto riešenie je omnoho lepšie:
vazby (id INT);
udaje (jazyk CHAR, vazba INT, ...);

Takto môžete do tabuľky "udaje" vložiť ľubovoľný počet jazykových verzií. Tabuľka "vazby" medzitým zabezpečí aj prepínanie medzi jazykmi. Nemala by obsahovať žiadne iné stĺpec ako "id".

Udaje z DB sa budú môcť vyberať veľmi jednoducho:
SELECT * FROM vazby JOIN udaje ON vazby.id=udaje.vazba WHERE vazby.id=1 AND udaje.jazyk='en'

Samozrejme tabuľka "udaje" bude pre každý jazyk obsahovať iba jeden preklad, resp. uvedený SQL príkaz vráti vždy len jeden výsledok.

ikona Jakub Vrána OpenID:

Píšeš, že v tabulce vazby už nejsou žádné jiné sloupce. Nechceš doufám kopírovat i jazykově nazávislá data (např. cenu) do všech jazykových verzí?

Toto schéma zjednoduší přidávání jazyků, bohužel se ale zkomplikuje řada dalších věcí. Mám na to téma napsaný článek, ale alespoň namátkou:

Sloupci s přeloženými texty se nedá určit správný COLLATE (protože různé jazyky používají různé porovnávání), takže řazení buď nefunguje správně nebo se u něj nevyužijí indexy (takže je pomalé).

Protože nelze kombinovat fulltextový index s normálním, tak se dramaticky zpomalí fulltextové vyhledávání, ve kterém jsou navíc smíchané data různých jazyků, což má vliv na výkon a částečně i na funkčnost.

Je potřeba nějak řešit nepřeložené texty. U nejpřirozenějšího návrhu (nepřeložený řádek v tabulce vůbec není) se dotazy dramaticky zkomplikují.

Pro dotazy, kde je WHERE (např. cena) z hlavní tabulky a ORDER BY z překladové (např. název), se nedají použít složené indexy, což dotaz významně zpomalí.

kuba:

Pokud něco upravím v popisu základní jazykové verze, jak zajistit, abych věděl, že ostatní verze se liší od základní (došlo ke změně). Pak bych v případě takové editace také měl nějak rozlišit, jestli se jednalo jen o opravu obsahu (například pravopisná nebo styl. chyba) nebo jestli se změnil význam a je třeba jej přeložit a ostatní překlady jsou do té doby neplatné.

ikona Jakub Vrána OpenID:

Informaci o přeloženosti doporučuji ukládat bokem, např. do tabulky neprelozeno(tabulka, tabulka_id, sloupec, jazyk). Opravu překlepu může dovolit označit editační rozhraní.

Tomáš Urban:

Měl bych jeden dotaz. Mám zboží o různých parametrech (např. u jednho druhu zboží se měří průměr a délka, u druhého průměr1, průměr2 a délka,....) Jak nacpat zboží do jedné tabulky nebo budu muset použít další tabulku?

john:

diky za napomoc pri tych jazykovych verziach ! idem hned nato

Vložit příspěvek

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2016 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.