OKi, tak začneme.

Požadavky

• ukládání dat ve více jazycích
• řazení těchto dat podle zvyklostí daného locale
• jednoduchost pro databázi i aplikační server

Začnu tím třetím, tedy jednoduchostí. Vrchol jednoduchosti vídím v Oracle:

CREATE TABLE loc_test (
    lt_text NVARCHAR2(64))
/

INSERT INTO loc_test (lt_text) VALUES ('Petr')
/
INSERT INTO loc_test (lt_text) VALUES ('František')
/
INSERT INTO loc_test (lt_text) VALUES ('Áron')
/
INSERT INTO loc_test (lt_text) VALUES ('Antonín')
/
INSERT INTO loc_test (lt_text) VALUES ('Petříček')
/

COMMIT
/

ALTER SESSION SET NLS_SORT = CZECH_AI
/

SELECT
    lt_text
FROM
    loc_test
ORDER BY
    lt_text
/

ALTER SESSION SET NLS_SORT = ASCII7
/

SELECT
    lt_text
FROM
    loc_test
ORDER BY
    lt_text
/

SELECT
    lt_text
FROM
    loc_test
ORDER BY
    NLSSORT(lt_text, 'NLS_SORT = CZECH_AI')
/

DROP TABLE loc_test
/

Použití je velmi jednoduché. Ve výsledku je vidět, že to skutečně funguje tak, jak chceme:

CREATE TABLE succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
COMMIT succeeded.
ALTER SESSION SET succeeded.
LT_TEXT                                                          
---------------------------------------------------------------- 
Antonín                                                          
Áron                                                             
František                                                        
Petr                                                             
Petříček                                                         

5 rows selected

ALTER SESSION SET succeeded.
LT_TEXT                                                          
---------------------------------------------------------------- 
Antonín                                                          
František                                                        
Petříček                                                         
Petr                                                             
Áron                                                             

5 rows selected

LT_TEXT                                                          
---------------------------------------------------------------- 
Antonín                                                          
Áron                                                             
František                                                        
Petr                                                             
Petříček                                                         

5 rows selected

DROP TABLE loc_test succeeded.

Tak! A jak něco podobného udělat například v PostgreSQL? PostgreSQL umožňuje nastavit locale při vytváření databáze. Což je docela slabé. Proto jsem došel k následujícímu řešení, které je optimalizované na maximální výkon pro SELECT (to byl požadavek) a jednoduchost volání jako v Oracle.

Základní schéma

Na začátku je tabulka LOCALES, která funguje jako číselník podporovaných locales. Jednotlivá locale se zadávají v Javí notaci, tedy JAZYK_ZEMĚ_VARIANTA. Pak tam jsou naimplementované funkce, které umějí dohledat locale stejně tak, jak to dělá Java (java.util.ResourceBundle). Další tabulkou je tabulka TABLES, která ukládá názvy tabulek, které jsou pod lokalizací. Všechny funkce, které nějak mohou afektovat data (například přidání locale, odebrání locale), díky této tabulce automaticky přidávají nové jazyky. Úplně vespod jsou funkce pro podporu uživatelských proměnných v session, implementace chybějících funkcí PL/PgSQL a tak.

Data pod lokalizací

Základem jsou tři tabulky. Tabulka se suffixem _C, která ukládá nelokalizované atributy, další se suffixem _L, která ukládá lokalizovaná data a poslední se suffixem _D, která drží stav NULL, NOT NULL pro lokalizovaná data. Příklad:

CREATE TABLE i18n.example_c (
        exmp_id bigint DEFAULT nextval('sq_exmp_id') NOT NULL,
        exmp_type bigint NOT NULL,
        exmp_creation_time timestamp with time zone NOT NULL DEFAULT NOW()
);

ALTER TABLE i18n.example_c ADD CONSTRAINT pk_exmp_id PRIMARY KEY (exmp_id);


CREATE TABLE i18n.example_l (
        exmp_entity bigint NOT NULL,
        exmp_locale bigint NOT NULL,
        exmp_name varchar NULL,
        exmp_value varchar NULL,
        exmp_value_ord bytea NULL
);

ALTER TABLE i18n.example_l ADD CONSTRAINT pk_exmp_entity_exmp_locale PRIMARY KEY (exmp_entity, exmp_locale);
ALTER TABLE i18n.example_l ADD CONSTRAINT fk_exmp_entity_exmp_id FOREIGN KEY (exmp_entity) REFERENCES i18n.example_c (exmp_id) ON DELETE CASCADE;
ALTER TABLE i18n.example_l ADD CONSTRAINT fk_exmp_locale_locale_id FOREIGN KEY (exmp_locale) REFERENCES i18n.locale (id);


CREATE TABLE i18n.example_d (
        column_name varchar NOT NULL,
        nullable boolean NOT NULL
);

ALTER TABLE i18n.example_d ADD CONSTRAINT pk_example_d PRIMARY KEY (column_name);

Pak si nadefinuješ pohledy, které celou tuto strukturu zobrazí jako jednu tabulku:

CREATE VIEW i18n.example AS
SELECT
                c.exmp_id,
                c.exmp_type,
                c.exmp_creation_time,
                l.exmp_locale,
                l.exmp_name,
                l.exmp_value,
                l.exmp_value_ord
        FROM
                example_c c INNER JOIN example_l l ON (c.exmp_id = l.exmp_entity)
        WHERE
                l.exmp_locale = i18n.get_session_locale_id();


CREATE VIEW i18n.example_a AS
SELECT
                c.exmp_id,
                c.exmp_type,
                c.exmp_creation_time,
                l.exmp_locale,
                l.exmp_name,
                l.exmp_value,
                l.exmp_value_ord
        FROM
                example_c c INNER JOIN example_l l ON (c.exmp_id = l.exmp_entity);

Ta view se suffixem _A zobrazují veškeré locales (nejsou omezena na aktuální locale v session). Jak je vidět, pro každý řádek v tabulce EXAMPLE_C je v tabulce EXAMPLE_L vygenerován speciální řádek pro každé locale. Když přidáš další locale, "systém" automaticky do všech těchto kompozitů dogeneruje prázdné řádky.

Řazení

Tak, to byla struktura. Abychom mohli řadit v závislosti na locale, potřebujeme collation key. Ten spočítáme podle locale například Javou (třídy java.util.Locale a java.text.Collator). Získaný collation key uložíme do sloupku EXMP_VALUE_ORD v tabulce EXAMPLES_L a pak jen tento sloupek uvedeme v ORDER BY klauzuli. Jak jednoduché, že?

Jen precizní je dostatečně dobré

Nakonec napíšeme tři funkce, které pomocí RULEs zaregistrujeme jako Oraclí INSTEAD OF — TRIGGERy na ta VIEWs, které za nás automaticky při INSERTu nebo UPDATE přepočítají collation key. Ve všech dotazech tedy používáme vlastně jen ta VIEWs, protože se díky pravidlům chovají jako opravdové tabulky.

---

Takže — namísto Oraclího ALTER SESSION SET NLS_SORT… zavoláš i18n.set_session_locale(…) dál používáš databázi, jak je třeba. Když potřebuješ řadit jinak, buď zavoláš set_session_locale znovu, nebo si napíšeš vlastní ORDER BY klauzuli.