Преобразование в многоязычную базу данных

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

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

Язык по умолчанию, выбранный язык и языковой запас по умолчанию

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

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

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

Добавление языков в базу данных

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

class Language(Base):

    __tablename__ = 'language'

    id = Column(Integer, primary_key=True)

    # enable/disable language
    is_active = Column(Boolean, default=False, index=True)

    # name in your own language
    name = Column(String(100), server_default='', index=True)

    # short name in your own language
    short_name = Column(String(100), server_default='', index=True)

    # name in native language
    native_name = Column(String(100), server_default='', index=True)

    # short name in native language
    native_short_name = Column(String(100), server_default='', index=True)

    # language_region_code: en_US, de_DE, ...
    language_region_code = Column(String(16), server_default='', index=True)

    # lang_code: actual code shown to visitor, en, es, de (but also can be en_US, es_ES, etc.)
    language_code = Column(String(16), server_default='', index=True)

Это также позволяет включать и выключать язык в будущем. Затем я использовал категорию элементов содержимого, чтобы посмотреть, как это работает, я просто добавил таблицу перевода категорий, которая является почти копией таблицы категорий:

class ContentItemCategory(Base):

    __tablename__ = 'content_item_category'

    id = Column(Integer, primary_key=True)

    name = Column(String(50), server_default='')
    description = Column(String(200), server_default='')

    # one-to-many relationship with translation
    # we only use this relationship to append translations
    content_item_category_translations = relationship(
        'ContentItemCategoryTranslation', 
        backref='content_item_category',
        lazy='dynamic')


class ContentItemCategoryTranslation(Base):

    __tablename__ = 'content_item_category_translation'

    id = Column(Integer, primary_key=True)

    # name, slug, description in specified language
    name = Column(String(100), server_default='')
    slug = Column(String(100), server_default='')
    description = Column(String(200), server_default='')

    # one-to-many relationship with language
    language_id = Column(Integer, ForeignKey('language.id'))

    # one-to-many relationship with content_item_category
    content_item_category_id = Column(Integer, ForeignKey('content_item_category.id'))

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

Переход на переводы не был трудоемким делом, так как я сохранил исходную версию таблиц перевода практически идентичной таблицам. Для таблиц перевода я добавил параметр language_id, заданный по умолчанию, и параметр content_item_id, заданный в поле id.

INSERT INTO content_item_translation (id, ..., language_id, content_item_id) select id, ..., 1, id from content_item;

Получить категории не кажется сложным. SQL Таблица трансляции разбита на две таблицы: первая, tr_selected, используется для запроса категорий для выбранного языка, а вторая, tr_default, для запроса категорий для языка по умолчанию. Ниже мы выбираем поля имени и слизняка, language_id=3 означает немецкий и language_id=1 означает английский.
Если перевод для выбранного языка не найден, используется язык по умолчанию.

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

SELECT
    category.id,
    category.name,
    IFNULL(tr_selected.name, tr_default.name) name,
    IFNULL(tr_selected.slug, tr_default.slug) slug
  FROM content_item_category category
  LEFT OUTER JOIN content_item_category_translation tr_selected
    ON category.id = tr_selected.content_item_category_id AND tr_selected.language_id = 3
  LEFT OUTER JOIN content_item_category_translation tr_default
    ON category.id = tr_default.content_item_category_id AND tr_default.language_id = 1
  WHERE category.id = 3;

Для этого сначала SQLAlchemy мы называем таблицу трансляции псевдонимом. Далее мы используем func.coalesce замену символа IFNULL, а остальные выглядят очень по-разному, как и SQL запрос:

tr_selected, tr_default = aliased(ContentItemCategoryTranslation), aliased(ContentItemCategoryTranslation)
r = db.query(
  func.coalesce(tr_selected.name, tr_default.name),
  func.coalesce(tr_selected.slug, tr_default.slug)).\
    select_from(ContentItemCategory).\
    outerjoin(tr_selected, and_((ContentItemCategory.id == tr_selected.content_item_category_id), (tr_selected.language_id == 3))  ).\
    outerjoin(tr_default, and_((ContentItemCategory.id == tr_default.content_item_category_id), (tr_default.language_id == 1))  ).all()

Следует ли выбирать атрибуты (поля) или объекты (записи)? Записи в таблице переводов присутствуют только в том случае, если перевод был добавлен. Представляется логичным выбирать объекты вместо атрибутов, но это невозможно с SQLAlchemy, см. также ниже.

Мы хотим, чтобы записи в блоге были выбраны по дате создания. Также, так как мы фильтруем объединенные подрезультаты, используя публикуемые = 1, мы получаем много значений для заголовка и слизняка. Причина, конечно, в том, что результат возвращает все записи таблицы content_item. Мы можем удалить эти записи, добавив HAVING оператор добавить конец. Теперь результат правильный.
Но есть подвох. На данный момент количество записей в таблице content_item равно 230. Количество опубликованных сообщений в блоге - 12. Это значит, что HAVING партия должна отфильтровать более 200 записей, 95%!

Добавлена WHERE часть для фильтрации полей content_item_type, published и content_item_parent_id. Это делается для того, чтобы сократить количество записей на HAVING изделие. Я также добавил ORDER BY пункт, чтобы показать самые последние записи в блоге в первую очередь.

SELECT
    DISTINCT
    content_item.id,
    content_item.title,
    IFNULL(tr_selected.created_on, tr_default.created_on) created_on,
    IFNULL(tr_selected.title, tr_default.title) tr_title,
    IFNULL(tr_selected.slug, tr_default.slug) slug
  FROM content_item_translation tr_where, content_item
  LEFT OUTER JOIN content_item_translation tr_selected
    ON (
      content_item.id = tr_selected.content_item_id 
      AND tr_selected.published = 1 
      AND tr_selected.content_item_parent_id = 0 
      AND tr_selected.language_id = 3)
  LEFT OUTER JOIN content_item_translation tr_default
    ON (
      content_item.id = tr_default.content_item_id
      AND tr_default.published = 1 
      AND tr_default.content_item_parent_id = 0 
      AND tr_default.language_id = 1)
  WHERE
    content_item.id = tr_where.content_item_id
    AND content_item.content_item_type = 1
    AND tr_where.published = 1
    AND tr_default.content_item_parent_id = 0
  HAVING 
    tr_title IS NOT NULL
  ORDER BY created_on desc;

Вышеприведенный запрос основан на примере из Интернета. Я до сих пор не уверен насчет HAVING детали и должен был добавить, чтобы удалить дублирующиеся строки. В любом случае, SQLAlchemy запрос, эквивалентный вышеуказанному запросу, выглядит следующим образом:

tr_selected, tr_default, tr_where = aliased(ContentItemTranslation), aliased(ContentItemTranslation), aliased(ContentItemTranslation)
result_tuples = db.query(
    ContentItem.id, 
    ContentItem.title, 
    func.coalesce(tr_selected.created_on, tr_default.created_on).label('tr_created_on'),
    func.coalesce(tr_selected.title, tr_default.title).label('tr_title'),
    func.coalesce(tr_selected.slug, tr_default.slug).label('tr_slug')).\
    select_from(tr_where, ContentItem).\
    outerjoin(tr_selected, and_(
            (ContentItem.id == tr_selected.content_item_id),
            (tr_selected.published == 1),
            (tr_selected.content_item_parent_id == 0),
            (tr_selected.language_id == 3))).\
    outerjoin(tr_default, and_(
            (ContentItem.id == tr_default.content_item_id), 
            (tr_default.published == 1),
            (tr_default.content_item_parent_id == 0),
            (tr_default.language_id == 1))).\
    filter(and_(
        (ContentItem.id == tr_where.content_item_id),
        (ContentItem.content_item_type == 1),
        (tr_where.published == 1),
        (tr_where.content_item_parent_id == 0))).\
    having(literal_column('tr_title').isnot(None)).\
    distinct().\
    order_by(desc('tr_created_on')).all()

Мне пришлось искать в интернете, почему я работал, MariaDB но не работал с интернетом SQLAlchemy, см. также ссылки ниже. Нам нужен литерал_столбец, похоже, он работает только для MySQL / MariaDB (?). Я не могу подтвердить это, я использую только ...

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

Вышеуказанный запрос возвращает атрибуты, а не объекты. Я не знаю, как вернуть объекты, так как следующие изменения не дают результата, потому что они могут вернуть только буквальные значения:

    func.coalesce(tr_selected, tr_default)

    case([(ContentItem.title == None, tr_default)],
        else_ = tr_selected),

Пока я не знаю другого способа добавить все атрибуты к запросу(?).

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

Мне нравятся простые запросы на выборку, и вышеприведенный внешний запрос сложен, поэтому давайте попробуем посмотреть на другой способ сделать это. Я бы хотел, чтобы мне вернули полный список объектов, список кортежей (content_item, content_item_translation), для удобства обработки в шаблоне. Предположим, что выбран немецкий язык, а языком по умолчанию является английский. Наши опубликованные материалы могут быть следующими:

+-----------------+-----------------------------+-----------------------------+
| content_item.id | content_item_translation.id | content_item_translation.id | 
|                 |            EN               |            DE               |
+-----------------+-----------------------------+-----------------------------+
|      7          |                             |                             |
|      6          |            6                |                             |
|      5          |            4                |            5                |
|      4          |                             |                             |
|      3          |            2                |            3                |
|      2          |            1                |                             |
|      1          |                             |                             |
+-----------------+-----------------------------+-----------------------------+

Предположим, это записи в блоге. Затем у нас есть 4 записи в блоге на английском языке (language_id=1), и 2 на немецком (language_id=3). Также предположим, что языковая пауза всегда выполняется и элементы языка по умолчанию присутствуют. Тогда общее количество записей в блоге будет не более чем подсчетом количества записей в блоге для языка по умолчанию. Необходимо использовать как таблицы content_item, так и таблицы content_item_translation.

# select EN titles
SELECT 
  ci.id as ci_id, ci_tr.id as ci_tr_id, ci_tr.title as ci_tr_title
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND ci_tr.content_item_id = ci.id
    AND ci_tr.language_id = 1
    AND ci_tr.published = 1
    AND ci_tr.content_item_parent_id = 0;

# count EN titles
SELECT 
  count( distinct ci_tr.id ) 
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND ci_tr.content_item_id = ci.id
    AND ci_tr.language_id = 1
    AND ci_tr.published = 1
    AND ci_tr.content_item_parent_id = 0;

# get content_item.id list for DE
SELECT 
  distinct ci.id
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND ci_tr.content_item_id = ci.id
    AND ci_tr.language_id = 3
    AND ci_tr.published = 1
    AND ci_tr.content_item_parent_id = 0;

# get content_item.id list for EN without DE
SELECT
  distinct ci.id
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND (ci_tr.content_item_id = ci.id
    AND ci_tr.language_id = 1
    AND ci_tr.published = 1
    AND ci_tr.content_item_parent_id = 0)
    AND ci.id NOT IN (
      SELECT
        distinct cis.id
        FROM content_item cis, content_item_translation cis_tr
        WHERE 
          cis.content_item_type = 1
          AND (cis_tr.content_item_id = cis.id
          AND cis_tr.language_id = 3
          AND cis_tr.published = 1
          AND cis_tr.content_item_parent_id = 0)
    );

Из вышеизложенного следует, что если выбран немецкий язык, необходимо выполнить откат, если файл content_item.id переведен не на немецкий, а на английский язык. Используя профсоюз, мы можем получить, объединить и заказать товары для немецкого и английского языков. Мы можем использовать UNION_ALL здесь, потому что знаем, что немецкий и английский элементы разделены. В конце концов, мы сортируем результаты ORDER BY .

# get german items
SELECT ci.id as ci_id, 
        ci_tr.id as ci_tr_id, 
        ci_tr.created_on as ci_tr_created_on, 
        ci_tr.title as ci_tr_title
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND ci_tr.content_item_id = ci.id
    AND ci_tr.language_id = 3
    AND ci_tr.published = 1
    AND ci_tr.content_item_parent_id = 0

UNION ALL

# add english items
SELECT ci.id as ci_id, 
        ci_tr.id as ci_tr_id, 
        ci_tr.created_on as ci_tr_created_on, 
        ci_tr.title as ci_tr_title
  FROM content_item ci, content_item_translation ci_tr
  WHERE
    ci.content_item_type = 1
    AND ci_tr.content_item_id = ci.id
  AND ci.id IN (
    SELECT
      distinct cid.id
      FROM content_item cid, content_item_translation cid_tr
      WHERE
      cid.content_item_type = 1
      AND (cid_tr.content_item_id = cid.id
      AND cid_tr.language_id = 1
      AND cid_tr.published = 1
      AND cid_tr.content_item_parent_id = 0)
      AND cid.id NOT IN (
        SELECT
        distinct cis.id
        FROM content_item cis, content_item_translation cis_tr
        WHERE 
          cis.content_item_type = 1
          AND (cis_tr.content_item_id = cis.id
          AND cis_tr.language_id = 3
          AND cis_tr.published = 1
          AND cis_tr.content_item_parent_id = 0)
      )
  )

ORDER BY ci_tr_created_on DESC;

Мы также можем добавить нумерацию страниц, добавив в запрос LIMIT 2 OFFSET 1. Это работает. Это лучше, чем запрос на внешнее соединение? Некоторые тесты показали, что он, по крайней мере, в два раза медленнее. Но это просто, что также является большим плюсом. И теперь можно получить объекты из SQLAlchemy запроса, а не атрибуты. То, чего я хотел, вот почему я стал "объектом". Для повышения производительности мы можем кэшировать результаты запроса, они не часто меняются. Но это можно сделать позже. Эквивалентный запрос:

    # content_item_type is a constant, 1 = blog post
    # default language is english (id = 1)
    # language_id is the id of the selected language
    ci, ci_tr = aliased(ContentItem), aliased(ContentItemTranslation)
    s1 = db.query(ci, ci_tr).\
            filter(and_(\
                (ci.content_item_type == content_item_type),
                (ci_tr.content_item_id == ci.id),
                (ci_tr.language_id == language.id),
                (ci_tr.published == 1),
                (ci_tr.content_item_parent_id == 0),
                ))

    cisub, cisub_tr = aliased(ContentItem), aliased(ContentItemTranslation)
    s2_subquery = db.query(cisub.id).\
            filter(and_(\
                (cisub.content_item_type == content_item_type),
                (cisub_tr.content_item_id == cisub.id),
                (cisub_tr.language_id == language.id),
                (cisub_tr.published == 1),
                (cisub_tr.content_item_parent_id == 0)))
    
    s2 = db.query(ci, ci_tr).\
            filter(and_(\
                (ci.content_item_type == content_item_type),
                (ci_tr.content_item_id == ci.id),
                (ci_tr.language_id == 1),
                (ci_tr.published == 1),
                (ci_tr.content_item_parent_id == 0),
                (ci.id.notin_( s2_subquery ))))

    q = s1.union(s2).order_by(desc(ci.created_on))

    content_item_content_item_translation_tuples = q.all()

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

Здесь также есть нечто запутанное. Вы можете заказать по переменной create_on элемента содержимого или по переменной, созданной при переводе элемента содержимого create_on. Если вы используете последнюю, ваши записи в блоге могут запутаться. Так что на данный момент я заказываю не переведенные версии, а по созданному_ону блога_поста.

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

Резюме

В этой заметке описывается способ добавления языковой поддержки для таких элементов контента, как записи в блоге и страницы. Добавленная таблица перевода для каждой таблицы, требующей перевода, все усложняет. Меня немного беспокоит использование памяти (и производительность), когда таблица элементов содержимого растет. Но мы можем использовать кэширование результатов запроса (например, 1-5 минут), так как эти значения не часто меняются.

При создании запросов я часто начинаю использовать , а затем преобразовываю это в , но я не единственный.

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

Ссылки / кредиты

Aliasing field names in SQLAlchemy model or underlying SQL table
https://stackoverflow.com/questions/37420135/aliasing-field-names-in-sqlalchemy-model-or-underlying-sql-table?rq=1

How to Design a Localization-Ready System
https://www.vertabelo.com/blog/technical-articles/data-modeling-for-multiple-languages-how-to-design-a-localization-ready-system

Multi language database, with default fallback
https://stackoverflow.com/questions/26765175/multi-language-database-with-default-fallback

Multilanguage Database Design in MySQL
https://www.apphp.com/tutorials/index.php?page=multilanguage-database-design-in-mysql

python sqlalchemy label usage
https://stackoverflow.com/questions/15555920/python-sqlalchemy-label-usage

Schema for a multilanguage database
https://stackoverflow.com/questions/316780/schema-for-a-multilanguage-database

sqlalchemy IS NOT NULL select
https://stackoverflow.com/questions/21784851/sqlalchemy-is-not-null-select/37196866

SQLAlchemy reference label in Having clause
https://stackoverflow.com/questions/51793704/sqlalchemy-reference-label-in-having-clause

Storing multilingual records in the MySQL database
https://php.vrana.cz/storing-multilingual-records-in-the-mysql-database.php

Using Language Identifiers (RFC 3066)
http://www.i18nguy.com/unicode/language-identifiers.html

What's the best database structure to keep multilingual data? [duplicate]
https://stackoverflow.com/questions/2227985/whats-the-best-database-structure-to-keep-multilingual-data