Více jazyků (pro web) v databázi

Vytvářet dnes systém (ať se jedná o CMS, nebo e-obchod), který má „natvrdo zadrátovaný“ jen jeden jazyk znamená omezení pro zákazníky, co kdyby chtěli v budoucnu zasáhnout i zahraniční trhy. Trvalé nadpisy a statické texty lze vyřešit snadno, použitím souboru s jejich definicemi, který se naimportuje podle patřičné jazykové verze u zákazníka, ale trochu těžší situace nastane u kategorií, zboží, článků.

Začnu u kategorií. Pro multijazyčný web potřebuji 2 tabulky (přesněji řečeno 3, ale tabulka langs je jednotná pro celý web).:

CREATE TABLE `langs` (
  `langs_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shortcut` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `visorder` int(10) NOT NULL DEFAULT '1',
  `admin_allowed` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `user_allowed` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `user_default` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `currencies_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`langs_id`),
  KEY `visorder` (`visorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;
INSERT INTO `langs` VALUES(1, 'cs', 'Česky', 1, 1, 1, 1, 2);
INSERT INTO `langs` VALUES(2, 'en', 'English', 2, 0, 1, 0, 0);
INSERT INTO `langs` VALUES(3, 'de', 'Deutsch', 3, 0, 1, 0, 0);

CREATE TABLE `categories` (
  `categories_id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `code` varchar(32) COLLATE utf8_czech_ci NOT NULL,
  `visible` int(10) unsigned NOT NULL DEFAULT '0',
  `highlighted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `visorder` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  PRIMARY KEY (`categories_id`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=4 ;
INSERT INTO `categories` VALUES(0000000001, 'NOTY', 1, 1, 1);
INSERT INTO `categories` VALUES(0000000002, 'txt', 1, 1, 3);
INSERT INTO `categories` VALUES(0000000003, 'ssa', 1, 1, 2);

CREATE TABLE `categories_descriptions` (
  `categories_id` int(10) unsigned NOT NULL DEFAULT '0',
  `langs_id` int(10) unsigned NOT NULL DEFAULT '0',
  `description` text COLLATE utf8_czech_ci,
  `title` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `img` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  UNIQUE KEY `categories_id` (`categories_id`,`langs_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `categories_descriptions` VALUES(1, 1, 'Noty skladeb', 'Noty', 'noty-noty', NULL);
INSERT INTO `categories_descriptions` VALUES(1, 2, 'Dots of Music', 'Dots', 'noty-dots', NULL);
INSERT INTO `categories_descriptions` VALUES(2, 1, 'Texty mých skladeb', 'Texty', 'txt-texty', NULL);
INSERT INTO `categories_descriptions` VALUES(3, 2, 'test', 'ddt', 'ssa-ddt', NULL);

-- pohled pro usetreni casu pri vyberech na webu (frontend)
CREATE VIEW `categories_all` AS select `t1`.`categories_id` AS `categories_id`,
  `t1`.`visible` AS `visible`,`t1`.`visorder` AS `visorder`,`t1`.`highlighted` AS `highlighted`,
  `t2`.`langs_id` AS `langs_id`,`t2`.`description` AS `description`,`t2`.`title` AS `title`,
  `t2`.`name` AS `name`,`t2`.`img` AS `img` from (`categories` `t1` left join
  `categories_descriptions` `t2` on((`t1`.`categories_id` = `t2`.`categories_id`)));

Výběr na straně uživatele je snadný, celou dobu, kterou tráví u webu se mimo jiné předává i identifikátor jazyka a kategorie se vybírají z pohledu s omezením na patřičný identifikátor jazyka; z pohedu se vybírá jen kvůli pohodlnosti, nemusím přemýšlet nad JOINy. V administraci je situace o něco těžší.

V administraci se kategorie pro editaci vybírá 2 SELECTy z tabulek, zde by mi výběr z pohledu byl na obtíž. 1. SELECT slouží pro výběr obecných parametrů a 2. pro jazykově závislé řetězce.

--vyber obecnych dat:
'SELECT * FROM categories WHERE categories_id=\'' .	$pid . '\';
--vyber jazykove zavislych dat
'SELECT `t1`.`langs_id`, `t1`.`description` AS `lngDescr`, ' .
	'`t2`.`title`, `t2`.`description` AS `cdescription` FROM `' .	DBPREFIX . 'langs` AS `t1` ' .
	'LEFT JOIN `' . DBPREFIX . 'categories_descriptions` AS `t2` ON `t1`.`langs_id`=`t2`.`langs_id` WHERE ' .
	'`t1`.`user_allowed`=\'1\' AND `t2`.`categories_id`=\'' . $pid . '\' UNION ALL SELECT ' .
	' `t1`.`langs_id`, `t1`.`description` AS `lngDescr`, \'\' AS `title`, \'\' AS `cdescription` FROM `' .
	DBPREFIX . 'langs` AS `t1` WHERE `t1`.`user_allowed`=\'1\' AND `t1`.`langs_id` NOT IN (SELECT `langs_id`' .
	'FROM `' . DBPREFIX . 'categories_descriptions` WHERE `categories_id`=\'' . $pid . '\')'

Pro začátečníky může být problém v pochopení druhého dotazu, je v něm UNION ALL, jehož účel je neznalému nejasný. První polovina dotazu, po zmíněný UNION je snad zřejmá, prostě z tabulky „lokalizací“ se vyberou vyplněné řádky, jež se doplní o název a id patřičného jazyka. Ta „nepochopitelná“ druhá polovina dotazu mi doplní výstup o nedefinované přklady patřičné kategorie. Pak stačí jen dvourozměrné pole projet sekvencí foreach při zobrazení (smozřejmě, že inputy jsou definovány jako pole s klíčem langs_id).

Doufám, že se nemusím věnovat podrobnějšímu vysvětlování a ti, kdož tu hledají poučení jsou na takové výši, že dovedou dotazy pochopit.

Trochu zajímavější je situace u článků. Tam už není zajímavé moci je psát ve všech jazycích, jež jsou pro web povolené, určitě nebudu potřebovat portugalskou verzi příspěvku, když mám kategorii, do níž jej řadím, jen česky, německy a rusky. Ano, je potřeba se omezit jen na jazyky pro něž je kategorie definována.

Struktura dat, tj. rozdělení do 2 tabulek, je podobná kategoriím, nebudu uvádět, jen v obecné tabulce je categories_id, kterýžto sloupec určuje do jaké kategorie příspěvek patří. Výběr pro editaci (jen 2. dotaz) pak vypadá následujícím způsobem:

'SELECT `t2`.`langs_id`, `t2`.`visorder`, `t2`.`description` AS `langDesc`, ' .
	'`t1`.`perex` AS `perex`, `t1`.`title` AS `title`, `t1`.`article` AS `article` FROM `' . DBPREFIX . 'articles` AS `t1` LEFT JOIN `' .
	DBPREFIX . 'langs` AS `t2` ON `t1`.`langs_id`=`t2`.`langs_id` WHERE `t1`.`content_id`=\'' . $pid .
	'\' UNION SELECT `t2`.`langs_id`, `t2`.`visorder`, `t2`.`description` AS `langDesc`, ' .
	' \'\' AS `perex`, \'\' AS `title`, \'\' AS `article` FROM `' . DBPREFIX . 'langs` AS `t2` WHERE `langs_id` IN (' .
		'SELECT `langs_id` FROM `' . DBPREFIX . 'sections_all` WHERE `sections_id`=\'' . $sect . '\'' . 
	') AND `langs_id` NOT IN (' .
		'SELECT `langs_id` FROM `' . DBPREFIX . 'articles` WHERE `content_id`=\'' . $pid . '\'' .
	') ORDER BY `visorder`'

Závěrem

Popsaná problematika je zajímavá, samozřejmě že existuje triviálnější řešení cyklického kladení dotazů, ale pak se můžete se zlou potázat u svého webhostera. Pokud jste zdatní vývojáři, určitě máte vlastní řešení a na tom mém najdete nejednu chybu, nikdo není dokonalý a každý se neustále učí. Prostě tento článek berte jen jako hint pro ty, kdo se učí a jedno z možných řešení jim může pomoci psát kód co nejméně náročný na zdroje serveru, kde běží.

Flattr this!

5 thoughts on “Více jazyků (pro web) v databázi”

  1. Mel bych dve pripominky – vizualni styl box neni pro SQL uplne nejprehlednejsi – existuje rada zapisu – napr. od Joe Celka. 🙂
    Ty dotazy jsou vytrzene z kontextu, nicmene tak jak jsou zapsane jsou nachylne na SQL injektaz 🙁

    1. [Pavel Stěhule:] Děkuji za komentář. Odpovím na obě připomínky.
      1. Joe Celko, neznám. Nebyl by odkaz?
      2. Proměnná $pid není jen vzatý $_GET/$_POST, ani veškeré vstupy. Ve svém frameworku mám veškeré ošetření vstupů.
      Trochu jsem tu strukturu vyčistil, v reálu tam mám ještě pár sloupečků pro realizaci hierarchické struktury, pochopitelně trochu jsem to upravil, protože jsem potřeboval paralelně vedle sebe několikero stromů a potřeboval jsem pořešit řazení.
      V tomto článku jsem prostě jen nastřelil jak pořešit ty výběry, pokud možno na jediný dotaz, bez cyklů atd. Učebnici jak to nepsat mám v podobě zdrojáků ZenCartu (bohužel na té jsem jeden projekt rozjel a teď jak ji odstavit pro přechod na vlastní engine) a OSCommerce.

  2. S timto pristupem mam ten problem, ze se predpoklada shodna struktura vsech jazykovych mutaci webu. Je dost problem systemove takto udelat napr. plnohodnotnou ceskou verzi a pouze jednostrankovou anglickou.

    Pro clanky proto pouzivam spise ukladani do stromu, kdy mam tolik korenovych uzlu, kolik mam mutaci a stranky mezi sebou eventualne prolinkovavam (tzn. mam tabulku translations, kde mam k hlavnimu jazyku privazane reference na ostatni stranky).

    Obdobny pristup zde navrzenemu ale beru jako vhodny pro katalog s produkty, ktere zakaznik asi nebude chtit vicekrat zadavat a proto k nim akorat dopreklada zbytek… nejdu ale pres tabulku s preklady, ale pres pojmenovani atributu (viz. http://php.vrana.cz/ukladani-vicejazycnych-zaznamu.php)

    1. [Tomáš Mačuga]: Děkuji za komentář. Stromy mám, pro kategorie, v předchozí odpovědi na Pavla jsem dával i odkaz (musím upravit styl, je nějaký neviditelný). Vydělává mi eshop, takže mám logiku přemýšlení uzpůsobenou shopu, možná je to špatně pro běžný web. Jak jsem napsal, je to jedno z možných řešení a to od Jakuba Vrány tímto nezavrhuji.

  3. prave sem chtel poukazat na to, ze neni treba jit cestou pouze jednoho reseni, ale lze je zkombinovat 🙂 takze spis jenom ukazka smeru uvazovani pro bezny web, protoze i na shopu je nekdy potreba tvorit obsah pro zakazniky

Komentáře nejsou povoleny.