Подвалы Вавилонской башни, или Об интернационализации баз данных с доступом через ORM

Владислав Иофе, наш архитектор приложений, опубликовал на портале «Хабрахабр» пост, посвященный хранению и обработке многоязычных данных. Какие способы хранения существуют? Какими преимуществами и недостатками они обладают и есть ли идеальный вариант? Об этом — в материале «Подвалы Вавилонской башни, или Об интернационализации баз данных с доступом через ORM».

Введение

В предыдущей статье на примере доменной сущности товара мы рассмотрели собственные типы данных для многоязычных приложений. Мы научились описывать и использовать атрибуты сущностей, имеющие значения на различных языках. Но вопросы хранения и обработки в реляционной СУБД, а также проблемы эффективной работы в коде приложения до сих пор актуальны.

IT-сообщество использует различные способы хранения многоязычных данных. Способы эти кардинально различаются эффективностью запросов, устойчивостью к добавлению новых локализаций, объемом данных, удобством для приложения-потребителя.

Однако в индустрии все еще нет решения Database Internationalization for Dummies. Вместе с вами мы попробуем немного заполнить этот пробел: опишем возможные способы, оценим их преимущества и недостатки, выберем эффективные. Мы не собираемся изобретать серебряную пулю, но сценарий, который будем рассматривать, довольно типичен для корпоративных приложений. Надеемся, многим он окажется полезен.

Приведенные в статье фрагменты кода — на языке C#. На GitHub можно найти примеры реализации механизмов интернационализации с использованием двух различных связок ORM и СУБД: NHibernate + Oracle Database и Entity Framework Core + SQL Server. Разработчикам, использующим упомянутые ORM, будет интересно узнать конкретные приемы и трудности работы с многоязычными данными, а также блокирующие дефекты фреймворков и перспективы их устранения. Изложенные ниже принципы и примеры работы с многоязычными данными легко перенести и на другие языки и технологии.

Условия задачи

Наше приложение должно работать сразу с несколькими языками. На любом из них в зависимости от окружения пользователя будут отображаться и вводиться оперативные и справочные данные. При этом существуют сценарии, требующие умения в рамках одной сессии работать с данными сразу на всех языках.

Для примера рассмотрим уже знакомую нам доменную сущность товара, имеющую наименование на различных языках.

public class Product
{
    public long Id {get; set; } 
    public String Code { get; set; }
    public MultiCulturalString Name { get; set; }
}

Сформулируем требования к хранению и обработке данных сущности с многоязычными атрибутами:

• Хранение, чтение и запись таких сущностей должны быть и через ORM (преимущественно), и средствами СУБД (хотя бы для целей сопровождения);
• Многоязычных атрибутов может быть несколько.
• Должно выполняться требование локализуемости.
• Должна быть возможность быстрого поиска и сортировки по локализованным значениям многоязычного атрибута:
  • только для заданной локали;
  • с учетом заданного алгоритма обработки альтернативных ресурсов (если для запрошенной локали значение отсутствует).
• Должна быть возможность поиска по локализованным значениям многоязычного атрибута среди всех локалей.
• Должна быть возможность расширения для локализации не только строковых атрибутов, но и атрибутов других типов (полезен MultiCultural<T>).
• Объемы хранимых данных и трафика между БД и приложением должны быть допустимыми.

Напомним, что алгоритм обработки альтернативных ресурсов IEnumerable<CultureInfo> IResourceFallbackProcess.GetFallbackChain(CultureInfo initial) может возвращать различные порядки поиска локализаций для различных начальных локалей initial:

• для initial локали ru-RU: ru-RU -> ru;

• initial локали en-US: en-US -> en -> ru;

• initial локали kz-KZ: kz-KZ -> kz -> ru;

• initial локали zh-CH: zh-CH -> zh-CHS -> zh-Hans -> zh -> en.

Обзор существующих возможностей

СУБД

Какие же фичи есть в крупнейших современных СУБД для интернационализации? Это:

• Юникод;

• Collation (правила сравнения символов, регистрочувствительность и пр.);

• Дата-время с часовым поясом/смещением.

Эти фичи поддерживают многие крупные вендоры. Приведем ссылки на некоторые статьи:

• Microsoft: International Considerations for Databases and Database Engine Applications;

• Oracle: Designing Database Schemas to Support Multiple Languages.

Однако никаких «стандартных» схем хранения многоязычных данных не существует. Нет их и в самом свежем стандарте SQL:2016.

Из любопытных академических публикаций можно отметить кандидатскую диссертацию экс-главы Исследовательской группы многоязычных систем Microsoft Multilingual Information Processing on Relational Database Architectures, 2005. В работе рассматриваются проблемы кросс-языковых запросов, многоязычные операторы соединения (multilingual join operators), алгебра запросов для нового типа хранения многоязычных данных и упомянутых операторов.

ORM

Документация по NHibernate порадовала присутствием статьи Localization techniques. В ней рассматриваются два способа хранения (с вариациями):

• Одна колонка, в которой хранится пользовательский тип данных — словарь локаль-данные.
• Отдельные таблицы для хранения локализованных атрибутов (1-2 шт.).

Примечательно, что в этой статье даже не упоминается достаточно очевидный и популярный способ хранения — многоколоночный (колонка на локаль).

По Entity Framework значимых материалов найти не удалось.

Сравнение хранений

Для начала сформируем критерии сравнения. Для этого описанные выше требования переформулируем в более технические. Как мы убедимся далее, все они довольно жесткие.

Локализуемость. Изначально требование означает отсутствие изменений в коде, когда новая локализация добавляется в приложение. Применительно к приложениям с локализованными данными мы, например, можем говорить об отсутствии изменений в схеме данных и маппингов ORM.

Поиск и сортировка для заданной локали. В терминах многоязычной строки это означает возможность использовать индексы по результатам функции String MultiCulturalString.GetString(IResourceFallbackProcess resourceFallbackProcess, CultureInfo culture, bool useFallback) для всех осмысленных в данном приложении комбинаций значений ее параметров. Экземпляр IResourceFallbackProcess в приложении, вероятно, единственный, при useFallback = false значение resourceFallbackProcess роли не играет, поэтому число необходимых индексов по локализованным данным не более 2N, где N — число различных локалей, используемых во всех записях в многоязычном атрибуте.

Поиск среди всех локалей. Требуется поддержка функции вида MultiCulturalString.FindLocalizedStringCulture(String localizedString) с типом возвращаемого значения CultureInfo.

Поиск подходящей локализации. Локаль пользователя всегда специфичная, то есть определяет не только язык, но и региональные параметры. А для хранения в большинстве случаев достаточно использовать нейтральные локали (не задающие специфику региона), «ближайшие» к выбранным специфичным.

Поэтому из приведенных выше десяти локалей нам достаточно предусмотреть хранение только для четырех нейтральных: ru, en, kz, zh-Hans. А в ORM можно поддержать функционал приведения специфичной локали к нейтральной при присваивании атрибута и (или) сохранении сущности.

Пригодность для ORM. У выбранного ORM должно быть достаточно точек расширения, чтобы мы смогли претворить в жизнь все наши фантазии о многоязычном хранении. Иначе придется писать новый фреймворк.

Многоколоночное хранение

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

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

С созданием индексов для каждой из name_*-колонок для случая, когда мы не используем поиск подходящей локализации, вроде все очевидно. Но как будет выглядеть запрос, в котором должен работать IResourceFallbackProcess?

Рассмотрим, например, такой запрос:

var enUS = CultureInfo.GetCultureInfo("en-US");
var productName = "...";
var result = GetRepository<Product>()
   .Where(p => p.Name.ToString(enUS) == productName)
   .SingleOrDefault();

Думаю, вы согласитесь, что он вполне может быть реализован следующим SQL-запросом:

SELECT pr.id_product, pr.code, pr.name_ru, pr.name_en, pr.name_kz, pr.name_zh_hans
FROM t_product pr
WHERE isnull(pr.name_en, isnull(pr.name_ru, '')) LIKE @p1
ORDER BY isnull(pr.name_en, isnull(pr.name_ru, '')), pr.id_product

Чтобы получать «честный» null вместо пустой строки, когда запрашиваемое значение отсутствует, с точки зрения многоязычной строки следует использовать одну из перегрузок MultiCulturalString.GetString. Тогда выражение фильтрации в SQL немного упростится:

SELECT pr.id_product, pr.code, pr.name_ru, pr.name_en, pr.name_kz, pr.name_zh_hans
FROM t_product pr
WHERE isnull(pr.name_en, pr.name_ru) LIKE @p1
ORDER BY isnull(pr.name_en, pr.name_ru), pr.id_product

Получаем, что еще хотя бы по одному индексу на локаль нам необходимо строить по выражению с isnull, которое является SQL-отображением цепочки поиска альтернативных ресурсов. Такие индексы обычно называют функциональными, в SQL Server это индексы над вычислимыми колонками. В приведенном выше описании таблицы t_product эти индексы имеют суффикс _stdfallback.

О виде запроса в случае поиска строки среди всех локализаций мы предоставим читателю возможность пофантазировать.

Такой способ хранения и доступа реализован для одного из клиентов нашей компании.

Одноколоночное сериализованное хранение

В этом варианте многоязычный атрибут занимает только одну колонку. Многоязычная строка может быть сериализована как в бинарном, так и в человекочитаемом виде. Или, например, в Oracle колонка может иметь объектный тип.

Учитывая современные тенденции развития стандарта SQL, стоит обратить внимание на XML- или JSON-хранение. Oracle Database, SQL Server, DB2, PostgreSQL, MySQL имеют довольно серьезную поддержку XML и (или) JSON.

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

SQL-запрос для поиска будет выглядеть примерно так:

SELECT pr.id_product, pr.code, pr.name
FROM t_product pr
WHERE McsGetString(pr.name, 'en', 'en,ru') LIKE @p1
ORDER BY McsGetString(pr.name, 'en', 'en,ru'), pr.id_product

При появлении новой локали нам все же придется добавлять новые функциональные индексы по результатам функции McsGetString. Добавление только индексов — более безопасное действие, чем добавление новых колонок. В отличие от многоколоночного хранения маппинги ORM, вероятно, изменять не придется.

Реляционное хранение

Здесь мы заводим отдельную таблицу для хранения локализованных значений. Вариаций такого хранения множество. Рассмотрим только один из них.

SQL-запроc для поиска только по одной локали выглядит довольно просто:

SELECT pr.id_product, pr.code, pl.name
FROM t_product pr
  LEFT JOIN t_product_localizable pl 
    ON pr.id_product = pl.id_product AND pl.locale = 'en'
WHERE pl.name LIKE @p1
ORDER BY pl.name, pr.id_product

SQL-запроc с учетом поиска подходящих локализаций уже более громоздкий:

SELECT pr.id_product, pr.code, pl_en.name, pl_ru.name
FROM t_product pr
  LEFT JOIN t_product_localizable pl_ru 
    ON pr.id_product = pl_ru.id_product AND pl_ru.locale = 'ru' 
  LEFT JOIN t_product_localizable pl_en 
    ON pr.id_product = pl_en.id_product AND pl_en.locale = 'en' 
WHERE isnull(pl_en.name, isnull(pl_ru.name, '')) LIKE @p1
ORDER BY isnull(pl_en.name, isnull(pl_ru.name, '')), pr.id_product

Такой запрос преподносит нам целый букет сюрпризов.

Во-первых, стоимость запроса уже заметно выше, чем в предыдущих вариантах.

Во-вторых, при помощи такого запроса проблематично инстанцировать сущность с полностью инициализированным многоязычным атрибутом. Либо мы должны добавить LEFT JOIN для всех локалей, список которых нужно знать заранее, либо отказаться от использования атрибута типа MultiCulturalString, заменив на String и уменьшив число покрываемых сценариев. Еще один вариант — поддержать ленивую загрузку значений для различных локалей внутри многоязычной строки. Каждая из альтернатив имеет право на жизнь с учетом конкретных требований к продукту.

В-третьих, мы собираемся поддержать работу с многоязычными атрибутами в существующих ORM. Но нам вряд ли удастся найти такую точку расширения, чтобы добавить JOIN в SQL-запрос, при этом оставаясь в рамках простого LINQ-запроса:

var result =  GetRepository<Product>()
    .Where(p => p.Name.ToString(enUS) == productName)
    .SingleOrDefault();

Соединения с таблицей t_product_localizable можно заменить на подзапросы, но это принципиально ничего не меняет.

Весьма любопытный результат, не правда ли? Зато требование локализуемости выполняется.

Выводы

Как мы и ожидали, найти серебряную пулю среди рассмотренных вариантов не удалось. В каждом из них в большей или меньшей степени нарушается одно или несколько требований. Однако это не повод ничего не делать. Нам необходимо принять компромиссное решение, выбрать золотую середину.

Наиболее взвешенным и перспективным мы посчитали вариант одноколоночного сериализованного хранения. Поэтому в следующем разделе предлагаем рассмотреть особенности реализаций этого варианта для связок NHibernate + Oracle Database и Entity Framework Core + SQL Server. Сериализацию многоязычных атрибутов будем делать в XML, а для доступа к локализованным значениям из БД — использовать средства СУБД.

Расширяем ORM

Итак, мы предпримем попытку поддержать работу с сущностями, содержащими атрибут типа MultiCulturalString, а также запрос поиска таких сущностей по локализованному значению атрибута.

Вопрос поиска многоязычного атрибута по всем локалям мы рассматривать не будем, предоставив это заинтересованному читателю.

В реализации мы исходим из следующих принципов:

• Сущности описываются классами POCO (в Java — POJO).
• Сущности отделены от маппинга на объекты БД, в том числе от преобразований в формат БД и обратно.
• В запросах по возможности применяем «родное» API многоязычной строки. Таким образом получим прозрачное использование одного API и на клиенте, и на сервере.
• Деление на сборки должно быть мелким для лучшего разделения ответственностей, контроля зависимостей и переиспользования.

Приведем диаграмму зависимостей с мелким делением на сборки.

Сериализовывать значения многоязычной строки мы будем в XML. В .NET удачно разделены ответственности за содержание сериализованных данных (ISerializable, ISerializationSurrogate) и конечное сериализованное представление (IFormatter). И если первую ответственность вполне логично возложить на сам сериализуемый объект, то вторая зависит от использования. Поэтому для форматирования применим найденный на просторах интернета XmlFormatter: IFormatter, использующий ISerializable-возможности объектов.

NHibernate + Oracle Database

NHibernate, пожалуй, уже давно наиболее функциональный ORM под .NET. Вместе с тем он содержит противоречивые наслоения, возникшие на разных этапах его развития. Сейчас версии выходят крайне редко, контрибьюторов осталось мало. Некоторые давно ожидаемые исправления дефектов, видимо, не выйдут никогда.

Чтобы загрузить и сохранить значения многоязычной строки, нам необходимо реализовать IUserType, в котором и использовать упомянутый XmlFormatter.

Многоязычная строка в XML выглядит довольно очевидно.

<?xml version="1.0" encoding="UTF-8"?>
<MultiCulturalString xsi:type="MultiCulturalString" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://custis.ru/i18n">
  <ru>Шоколад Алина</ru>
  <en>Chocolate Alina</en>
</MultiCulturalString>

«Сердце» функции доступа к локализованным значениям McsGetString использует XQuery.

SELECT XMLCast(
  XMLQuery('declare namespace i18n="http://custis.ru/i18n"; for $mcs_locale in $mcs/i18n:MultiCulturalString/* where $mcs_locale/name() = $locale return $mcs_locale/text()'
    PASSING a_mcs AS "mcs", a_locale AS "locale" RETURNING CONTENT)
  AS VARCHAR2(4000 CHAR))
INTO l_value
FROM dual;

Чтобы использовать эту функцию в HQL, достаточно добавить реализацию ISQLFunction в конфигурацию NHibernate. Но кроме того, мы хотим, чтобы обращение в LINQ-to-Database к перегрузкам MultiCulturalString.ToString() превращалось в вызов функции McsGetString. Впрочем, и для этого в NHibernate есть точка расширения: достаточно реализовать IHqlGeneratorForMethod и также зарегистрировать реализацию в конфигурации. Сама реализация IHqlGeneratorForMethod ожидаемо преобразует одно дерево выражений в другое.

Вот, собственно говоря, и все. Неужели мы молодцы, а с NHibernate все так беспроблемно? Увы, нет!

Вот такой простой тест не пройдет.

[Test]
public void TestNh2500()
 
{
    using (var session = SessionFactory.OpenSession())
    {
        var product = new Product
        {
            Code = ProductCode,
            Name = new MultiCulturalString(ru, ProductNameRu)
                .SetLocalizedString(en, ProductNameEn)
        };
 
        session.Save(product);
    }
 
    using (var session = SessionFactory.OpenSession())
    {
        var product = session.AsQueryable<Product>()
            .SingleOrDefault(p => p.Name.ToString(zhCHS)) == ProductNameEn);
 
        Assert.IsNotNull(product);
        Assert.AreEqual(ProductCode, product.Code);
    }
 
    using (var session = SessionFactory.OpenSession())
    {
        var product = session.AsQueryable<Product>()
            .SingleOrDefault(p => p.Name.ToString(ruRU)) == ProductNameEn);
 
        // The next line throws AssertionException
        Assert.IsNull(product);
    }
}

Для обоих запросов будет сгенерирован одинаковый SQL:

    SELECT
        product0_.id_product AS id1_0_,
        product0_.code AS code0_,
        product0_.name AS name0_ 
    FROM
        t_product product0_ 
    WHERE
        McsGetString(product0_.name, 'zh-CHS', 'zh-CHS,zh-Hans,zh,en')=:p0;

Все дело в дефекте NH-2500: LINQ-запросы даже разных сессий кэшируются на уровне фабрики сессий и переиспользуются, несмотря на различные значения параметров запроса. Хотя баг уже шесть лет как критичный, исправление войдет только в будущую версию 5.1. А пока можно выпустить fork NHibernate. Если вам понадобится править какие-то другие дефекты NHibernate, низкая динамика продукта делает риски «протухания» ваших правок невысокими.

Entity Framework Core + SQL Server

Не так давно вышел EF Core 2.0, и дальнейшее развитие именно за этой кроссплатформенной ветвью. Для наших примеров мы выбрали его, а не EF 6, так как надеемся на скорое решение описанных ниже проблем.

К сожалению, в EF Core мы не можем реализовать поддержку пользовательского типа, в том числе MultiCulturalString. Но уже в версии фреймворка 2.1 появится такая возможность (см. issue #242).

А пока что создадим прокси-класс для нашего товара со строковым атрибутом RawName.

public class Product
{
    public virtual long Id { get; protected set; }
    public virtual String Code { get; set; }
    public virtual MultiCulturalString Name { get; set; }
}
 
public class ProductProxy : Product
{
    public override MultiCulturalString Name
    {
        get => base.Name;
        set
        {
            _rawName = ConvertToStoredValue(value);
            base.Name = value;
        }
    }
 
    public virtual String RawName
    {
        get => _rawName;
        set
        {
            base.Name = ParseStoredValue(value);
            _rawName = value;
        }
    }
    private String _rawName;
 
    ... 
}

Именно прокси-класс будет участвовать в нашем DbContext. Как вы догадались, атрибут Name в маппинге участвовать не будет вовсе. Ну и конечно же, читателю хотелось бы используемый в маппинге атрибут RawName сделать protected, благо ORM-у это не мешает. Но не торопитесь!

Уже знакомый нам запрос

var result = GetRepository<ProductProxy>()
    .Where(p => p.Name.ToString(enUS) == productName)
    .SingleOrDefault();

заработает в EF без дополнительных усилий с нашей стороны. Но только на клиенте! Ведь атрибут Name никак не маппится на БД.

Большое подспорье, что EF позволяет получать предупреждения о клиентском выполнении части запроса либо запрещать его при помощи DbContextOptionsBuilder.ConfigureWarnings.

И даже после того, как будет сделан issue #242, мы можем использовать только статические функции для маппинга на функции БД. Использовать экземплярную функцию ToString не получится (см. issue #9213).

Возможным (пусть и временным) выходом для нас видится объявление методов-расширений DbUserDefinedMethods.McsGetString(this String mcs, ...), по сигнатурам совпадающих с многочисленными перегрузками MultiCulturalString.ToString, без учета первого параметра this. Запросы будут иметь несколько искусственный вид:

var result = GetRepository<ProductProxy>()
    .Where(p => p.RawName.McsGetString(enUS) == productName)
    .SingleOrDefault();

Такие методы-расширения регистрируются в построителе модели, и задается преобразование из одного дерева выражений в другое, реализация которого практически не отличается от реализации IHqlGeneratorForMethod для NHibernate.

Вдобавок в EF вычислимость выражения анализируется на серверной стороне без учета преобразования зарегистрированной функции. Поэтому перегрузки DbUserDefinedMethods.McsGetString, содержащие неизвестные ORM типы CultureInfo и IResourceFallbackProcess, будут всегда вычисляться на клиенте.

Альтернатива всем перечисленным «костылям» в EF есть — это написание собственного провайдера. Тогда можно поддержать и любые типы, и необходимые экземплярные функции. Но с точки зрения поддержки, синхронизации с провайдером-оригиналом, который активно развивается, такое архитектурное решение выглядит слабым. Поэтому будем надеяться на хорошую динамику новой ветви развития EF.

Заключение

Мы рассмотрели возможный сценарий использования и реализации поддержки многоязычных атрибутов. В то же время для некоторых простых сценариев использования многоязычных атрибутов достаточно в сущности декларировать только строковый атрибут, а для переключения между локализациями — вводить специальные методы вида static String InLocale(this String mcs, CultureInfo culture, ...).

Недостаток рассмотренного варианта одноколоночного сериализованного хранения — в частности, больший трафик между клиентом и сервером и более высокое потребление памяти.

Тем не менее поддержка многоязычных атрибутов и запросов с ними со стороны ORM позволяет получить полное, однородное и прозрачное для разработчиков решение. С нашей точки зрения, это неоспоримое преимущество описанного подхода.