Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Многоязыковые поля в БД. Как реализовать? / 8 сообщений из 8, страница 1 из 1
17.06.2008, 18:08
    #35378183
Ilya Storozhilov
Ilya Storozhilov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Здравствуйте!

Имеется необходимость реализации многоязыковых полей в информационной системе, т.е. возможность хранения текстовых данных в полях БД на нескольких языках одновременно. При этом отображение данных пользователям должно происходить с учетом их языковых предпочтений, которые (предпочтения), вообще говоря, являются упорядоченными справочниками. Более, того, если данных в конкретном поле таблицы БД согласно, предпочтений пользователя, не найдено вообще, то должны быть выданы хоть какие-нибудь имеющиеся данные в соответствии с некими общими системными языковыми предпочтениями.

В ИС между клиентом и SQL-сервером (PostgreSQL) имеется "SOAP-прослойка" в виде собственноручно реализованного на C++ (правда с использованием библиотеки gSOAP) SOAP-сервера, который и обращается непосредственно к SQL-серверу (с помощью libpqxx), а с клиентом общается посредством SOAP-сообщений.

На данный момент у меня в голове имеется следующие решения поставленной задачи:

1. "Реляционная модель". Грубо говоря, используем механизм ссылочной целостности, предоставляемый СУБД приблизительно следующим образом:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
multilingual_fields (
    id integer
);

multilingual_values (
    id integer,
    multilingual_field_id integer,
    language_id integer,
    value varchar
);

Таким образом, например, многоязыковое поле name таблицы regions примет вид:

Код: plaintext
1.
2.
3.
regions (
    id integer,
    name__multilingual_field_id foreign key references multilingual_fields (id)
);

При этом искомые поля таблиц БД в которых надо хранить данные на многих языках являются внешними ключами на записи таблицы multilingual_fields, а собственно их значения выбираются из таблицы multilingual_values. Здесь имеется проблема при выполнении SELECT-запросов, ведь объеденить, например, оператором JOIN таблицы regions, multilingual_fields и multilingual_values не получится, т.к. из multilingual_values нужно выбрать не строго определенное значение, а значение, определяемое алгоритмом, который оператор JOIN обеспечить не может. Таким образом, для получения из БД значений согласно языковых предпочтений пользователя нужно:

а) либо для каждого многоязыкового поля каждой записи таблицы (напр. regions) вызывать некую ф-цию, которая выбирает самое подходящее для пользователя значение, что фатально отразится на быстродействии, т.к. ф-ция при каждом вызове она будет запускать как минимум один SQL-запрос на получение id языков имеющихся многоязыковых значений для выбора подходящего;

б) либо в первом SELECT-запросе получать только id записей таблицы multilingual_fields (поле regions.name__multilingual_field_id), затем в SOAP-сервере выполнять второй SELECT-запрос на получение всех значений из multilingual_values с multilingual_values.multilingual_field_id равными всем multilingual_fields.id, которые были получены из предыдущего SELECT-запроса и выбрать из них самые подходящие "силами" SOAP-сервера. В принципе, этот подход можно попытаться оптимизировать возвратом в первом SELECT-запросе "самых лучших" значений (самый "верхний" язык из справочника языковых предпочтений пользователя) с использованием OUTER JOIN, а в втором запросе подгрузить и обработать все, что осталось недополученным. Имеем избыточность трафика между SQL и SOAP-сервером.

Еще одно потенциальное "затруднение" реляционной модели: размер таблицы multilingual_values будет невероятным. Это может быть проблемой?

2. "Текстовая модель". Искомые поля БД (например, regions.name varchar) представляют собой определенным образом "закодированные" (разделители, XML, etc...) значения, которые "распарсиваются" ф-цией SQL-сервера, не обращающейся к БД и принимающей на вход собственно значение поля и массивы пользовательских и системных языковых предпочтений. С быстродействием тут меньше всего проблем (нет обращений к БД в ф-ции), как и с лишним трафиком между SQL и SOAP-серверами, однако имеется "трабл" с ссылочной целостностью. В качестве решения можно, например, реализовать универсальный триггер, который при записи в многоязыковое поле проверяет правильность его структуры, наличие языков в соответствующем справочнике и "вешается" на таблицы с многоязыковыми полями, для которых (полей) придется завести некий "системный" справочник, чтоб триггер мог понять какие varchar-поля являются многоязыковыми.

3. Реализовать свой тип данных (в PostgreSQL это возможно). Однако официальная С++ (libpqxx) библиотека доступа к PostgreSQL массивы-то не понимает не говоря уж о "прикручивании" к ней своего типа данных, посему в этом случае придется что-то придумывать с доступом к PostgreSQL (видимо писать свою С++ библиотеку)... Очень не хочется, честно говоря...

Подскажите что-нибудь, пожалуйста... :)
Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.06.2008, 18:57
    #35378324
Andrey Daeron
Andrey Daeron
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Ilya Storozhilov
Подскажите что-нибудь, пожалуйста... :)
Спасибо!
Мы пошли "северным морским путём" (с).

Каждое поле дублируется на необходимое количество языков + есть признак перевода для языка + признак оригинальности для языка. Поля прозрачно дублируются (т.е. если пользователь ввёл поле на русском, оно автоматом пронеслось на аглицкий и украинский).

Запросы к БД на клиенте выглядят следующим образом:
Код: plaintext
SELECT %name% FROM table;
и преобразовываются в
Код: plaintext
SELECT name_rus, name_rus_is_original, name_rus_is_translated FROM table;

Подствеку прорабатывает клиент, бизнес логику - сервер. Автоматизм смены перевода для непереведённых полей - на сервере... в общем - работает. Работает очень шустро. При написании тоже недолго, если сразу на это заморочится.
Новый язык добавляется DDL. Предпочтения выставляются на клиенте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.06.2008, 19:23
    #35378364
pamir
pamir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Andrey DaeronНовый язык добавляется DDL. Предпочтения выставляются на клиенте.А не задолбаетесь? А новые таблицы не забываете в DDL скрипт вносить? А клиент сам не может язык добавить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.06.2008, 19:30
    #35378376
Jelis
Jelis
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Я релизовывал что-то похожее... Все слова хранились на английском, а таблица была доступна только через представление типа :
Код: plaintext
1.
2.
CREATE VIEW T1_v AS
SELECT id, translate(name) AS name FROM T1;

Где функция translate() переводила "на лету" слова на установленный на данный момент для пользователя язык. Слова перевода вообщемто почти также зранились в отдельной таблице, если слова нет в словаре возвращаеться полученое значение. Проблеммы конечно есть при добавлении пользователем новых слов (он должен сразу добавлять для всех языков), хотя мне это было не актуально, так как переводились в основном таблицы-"словари" куда пользователь вообщем-то не имел прав что-то добавлять.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.06.2008, 09:05
    #35378872
Andrey Daeron
Andrey Daeron
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
pamir Andrey DaeronНовый язык добавляется DDL. Предпочтения выставляются на клиенте.А не задолбаетесь? А новые таблицы не забываете в DDL скрипт вносить? А клиент сам не может язык добавить?
1. >200 таблиц, пока полёт нормальный, незадолбались.
2. Сейчас нет, но скорее из-за непонимания последним последствий этого. А поскольку не сильно нужно - то и фиг с ним. Для программеров есть функция добавления и удаления языка, которая делает DDL, перегенерирует автоматом триггера и т.д.

PS Еще один ньюанс который мы _обязаны_ были учитывать - это всяческие констрейнты уникальности. Это сделало затруднительным вынос в отдельную таблицу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.06.2008, 15:38
    #35380520
Ilya Storozhilov
Ilya Storozhilov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Очень-очень-очень не хочется завязываться на DDL, честно-говоря...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.06.2008, 15:56
    #35380607
Andrey Daeron
Andrey Daeron
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
Ilya StorozhilovОчень-очень-очень не хочется завязываться на DDL, честно-говоря...
Можно сравнить количество патчей на БД в Вашей системе и частоту добавления/удаления языка.
ДДЛ - самый быстрый вариант потом в эксплуатации, самый медленный при изменении количества языков.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2008, 14:17
    #35398861
sergomor
sergomor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Многоязыковые поля в БД. Как реализовать?
мы делали так

имеем общую последовательность (sequence) для таблиц, которые имеют столбцы со значением на разных языках.

пример:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE country
(
  id bigint NOT NULL DEFAULT nextval('uid_seq'::regclass),
  "name" character varying( 80 ) NOT NULL,
  population bigint,
  landarea bigint,
  CONSTRAINT pk_country_id PRIMARY KEY (id)
)

здесь id - уникальный идентификатор для таблиц, которые содержит многоязычные поля (в данном случае "name").

на все подобные таблицы вешаем тригер, который чистит ресурсы при удалении записи
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE OR REPLACE FUNCTION lang_resource_ondelete_object()
  RETURNS trigger AS
$BODY$
BEGIN
	DELETE from lang_resource WHERE object_uid=OLD.id;
	RETURN OLD;
END;
$BODY$

для хранения мультиязычных данных, используем таблицу вида:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE lang_resource
(
  id bigserial NOT NULL,
  lang character( 2 ),
  "name" character varying( 20 ) NOT NULL,
  object_uid bigint NOT NULL,
  "value" character varying( 2000 ) NOT NULL,
  CONSTRAINT pk_lang_resource_id PRIMARY KEY (id)
)

здесь:
lang - язык
name - имя мультиязычного поля в таблице;
object_uid - уникальный идентификатор строки из таблицы (в примере "country.id");
value - значение поля (например, "Россия")

выбор языкозависимого ресурса происходит с помощью процедуры:
Код: plaintext
1.
2.
3.
4.
5.
6.
CREATE OR REPLACE FUNCTION gettext(bigint, character varying, character varying)
  RETURNS character varying AS
$BODY$
	SELECT value from lang_resource WHERE object_uid=$ 1  AND name=$ 2  and lang=$ 3 ;
$BODY$
  LANGUAGE 'sql' STABLE

или с помощью join

пример:
Код: plaintext
1.
SELECT COALESCE (gettext (country.id, 'name', 'ru'), country.name) FROM country WHERE id= 1 ;

сечас в таблице: lang_resource около 500 000 строк, работает достаточно быстро
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Многоязыковые поля в БД. Как реализовать? / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]