Conversión a una base de datos multilingüe

Cuando empecé este proyecto, este sitio web, tuve en cuenta que tenía que ser compatible con varios idiomas. Pero, por supuesto, no me fijé realmente en el diseño de bases de datos debido a todas las demás cosas que tenía que aprender, añadir e implementar. Después de un cierto momento pude mostrar y seleccionar múltiples idiomas, pero eso fue para los textos en el código y las plantillas y no para los elementos de contenido de la base de datos como entradas de blog y páginas.

Para hacer el contenido multilenguaje investigué y encontré que hay varias maneras de hacer esto, cada una con sus pros y sus contras. Si eres nuevo en el tema, te recomiendo que mires los siguientes enlaces. Decidí que sólo hay una forma correcta de hacerlo y es añadiendo una"tabla de traducción" para cada tabla que contenga campos que deban estar disponibles en varios idiomas o, si lo prefiere, añadiendo un"objeto de traducción" para cada objeto que contenga atributos que deban estar disponibles en varios idiomas. Sé que ahora tendré que hacer frente a consultas más complejas, como la repetición del idioma predeterminado y el uso de conteo, paginación y límite.

Idioma por defecto, idioma seleccionado e idioma alternativo

Language fallback es el mecanismo mediante el cual mostramos los elementos de contenido en el idioma por defecto si no está disponible para el idioma seleccionado. El repliegue del lenguaje es una decisión que usted tiene que tomar.

Sin necesidad de volver a mostrar los elementos de contenido en el idioma seleccionado, si no está disponible, entonces es una lástima (403). Esto es relativamente fácil. Si el idioma seleccionado es alemán, muestre sólo los elementos alemanes; si el idioma seleccionado es inglés, muestre sólo los elementos ingleses.

Con la función de retroceso de idioma se muestran los elementos de contenido en el idioma seleccionado; si no están disponibles, se muestran en el idioma predeterminado. El idioma por defecto es el idioma que siempre debe estar presente. Para este sitio web decidí implementar la función de reserva de idioma y en el futuro me gustaría no mostrar ciertos elementos de reserva en absoluto para un idioma seleccionado.

Añadir los idiomas a la base de datos

Se debe añadir una tabla adicional para todos los elementos de contenido multilingüe. Ya he definido los idiomas en la configuración, pero ahora he creado una tabla de idiomas:

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)

Esto también permite habilitar y deshabilitar un idioma en el futuro. Luego utilicé la categoría de ítems de contenido para ver cómo funciona, sólo añadí la tabla de traducción de categorías, que es casi una copia de la tabla de categorías:

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'))

Después de implementar los cambios y poder crear y editar categorías y traducciones, llegó el momento de realizar algunas pruebas. Mi principal preocupación era cómo obtener los datos de esta nueva configuración, incluyendo cómo implementar la función de repliegue a un idioma predeterminado.

Pasar a las traducciones no fue mucho trabajo, ya que mantuve la versión inicial de las tablas de traducción casi idéntica a las tablas. Para las tablas de conversión añadí el set language_id, al idioma por defecto, y content_item_id, al campo id.

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

El SQL conseguir las categorías no parece complicado. La tabla de conversión se divide en dos tablas, la primera, tr_seleccionada, que se utiliza para consultar categorías para el idioma seleccionado, y la segunda, tr_default, para consultar categorías para el idioma predeterminado. A continuación, seleccionamos el nombre y los campos slug, language_id=3 significa alemán y language_id=1 significa inglés.
Si no se encuentra la traducción para el idioma seleccionado, se utiliza el idioma por defecto.

Importante: tenga en cuenta que el campo de título en el modelo tiene la cadena vacía como valor predeterminado. Por lo tanto, la consulta a continuación funciona, pero sólo si no se ha especificado el registro de idioma.

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;

Para hacer esto con SQLAlchemy nosotros primero nos aliamos con la tabla de traducción. A continuación usamos el func.coalesce reemplazo de IFNULL, y el resto se ve muy parecido a la SQL consulta:

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()

¿Debemos seleccionar atributos (campos) u objetos (registros)? Los registros de la tabla de conversión sólo están presentes si se ha añadido una traducción. Parece lógico seleccionar objetos en lugar de atributos, pero esto no es posible con SQLAlchemy, véase también más adelante.

Queremos que las entradas del blog sean seleccionadas por fecha de creación. También porque filtramos los subresultados unidos, usando published = 1, obtenemos muchos NULL valores para title y slug. La razón es, por supuesto, que el resultado devuelve todos los registros de la tabla content_item. Podemos eliminar estos NULL registros añadiendo una HAVING sentencia para añadir el final. Ahora el resultado es correcto.
Pero hay una trampa. Por el momento, el número de registros en la tabla content_item es de 230. El número de entradas publicadas en el blog es de 12. Esto significa que la HAVING pieza debe filtrar más de 200 registros, 95%!

La WHERE parte fue añadida para filtrar los campos content_item_type, published y content_item_parent_id. Esto se hace para reducir el número de registros de la HAVING pieza. También agregué una ORDER BY cláusula para mostrar primero las entradas más recientes del blog.

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;

La consulta anterior se basa en un ejemplo de Internet. Todavía no estoy seguro de la HAVING parte y también tuve que añadir DISTINCT para eliminar las filas duplicadas. De todos modos, el SQLAlchemy equivalente de la consulta anterior SQL es:

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()

Tuve que buscar en Internet por qué estaba trabajando con, MariaDB pero no trabajando con SQLAlchemy, ver también los enlaces de abajo. Necesitamos la columna literal_columna aquí, esto sólo parece funcionar para MySQL / MariaDB (?). No puedo confirmarlo, sólo uso MariaDB...

Supongamos que cuando se publica una traducción de un idioma, todo está bien, lo que significa que a todos los campos se les ha asignado un valor. En este caso sólo tenemos que comprobar la existencia de un registro de traducción. Afortunadamente, la consulta anterior lo está haciendo por nosotros. Si el modelo de traducción define un campo de título con un valor predeterminado de ''' (cadena vacía), entonces la consulta responde con NULL el título si el registro de traducción no existe. Exactamente lo que queremos.

La consulta anterior devuelve atributos, no objetos. No conozco una forma de devolver objetos ya que los siguientes cambios fallan porque sólo pueden devolver valores literales:

    func.coalesce(tr_selected, tr_default)

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

Por ahora no conozco otra forma de añadir todos los atributos a la consulta(?).

Otra forma de implementar la solución de emergencia del lenguaje

Me encantan las consultas simples y selectas y la consulta de la unión externa es compleja, así que intentemos buscar otra forma de hacerlo. Me gustaría que me devolvieran los objetos completos, una lista de tuplas (content_item, content_item_translation), para facilitar el procesamiento en la plantilla. Supongamos que el idioma seleccionado es el alemán y que el idioma por defecto es el inglés. Nuestros artículos de contenido publicados pueden ser los siguientes:

+-----------------+-----------------------------+-----------------------------+
| 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          |                             |                             |
+-----------------+-----------------------------+-----------------------------+

Supongamos que son entradas de blog. Entonces tenemos 4 entradas en inglés (language_id=1), y 2 en alemán (language_id=3). Suponga también que siempre se realiza un repliegue de idioma y que los elementos de idioma predeterminados están siempre presentes. Entonces el número total de entradas de blog no es nada más que contar el número de entradas de blog para el idioma por defecto. Necesitamos usar tanto el content_item como las tablas de traducción 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)
    );

Lo que se deduce de lo anterior es que cuando el idioma seleccionado es el alemán, se debe recurrir a él cuando el content_item.id tiene traducciones al inglés pero no al alemán. A través de un sindicato podemos obtener, fusionar y encargar los artículos en alemán e inglés. Podemos usar UNION_ALL aquí porque sabemos que los ítems en alemán e inglés están separados. Al final ponemos a ORDER BY para ordenar los resultados.

# 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;

También podemos añadir paginación añadiendo LIMIT 2 OFFSET 1 a la consulta. Esto funciona. ¿Es mejor que la consulta de la unión externa? Algunas pruebas mostraron que es al menos dos veces más lento. Pero es simple, lo que también es una gran ventaja. Y ahora es posible obtener objetos de la SQLAlchemy consulta en lugar de atributos. Justo lo que quería, por eso me volví `objeto'. Para mejorar el rendimiento podemos almacenar en caché los resultados de las consultas, que no cambian con frecuencia. Pero eso se puede hacer después. La SQLAlchemy consulta equivalente es:

    # 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()

Estoy contento porque la consulta del sindicato devuelve tuplas con objetos que se pueden pasar sin manipulación a la Jinja plantilla. Y para obtener las entradas del blog para la paginación, simplemente añadimos las funciones de desplazamiento y límite a la consulta. Obtener el número total de entradas de blog es simplemente obtener el número de entradas de blog para el idioma por defecto.

También hay algo confuso aquí. Puede ordenar por el created_on del elemento de contenido o el created on del elemento de contenido translation created_on. Si usas este último, tus entradas en el blog pueden confundirse. Así que por el momento ordeno por el created_on del blog_post, no de las versiones traducidas.

Para tener pruebas de que funciona he añadido dos entradas en alemán y una en español. Por el momento estoy en el proceso de conversión, en el momento de escribir este post sólo he traducido el título, subtítulos y meta datos. Compruebe el alemán y el español para ver los cambios.

Resumen

Esta entrada describe una forma de añadir soporte en varios idiomas para elementos de contenido como entradas y páginas de blogs. La tabla de traducción añadida para cada tabla que necesita traducciones hace las cosas más complicadas. Estoy un poco preocupado por el uso de la memoria (y el rendimiento) cuando la tabla de elementos de contenido crece. Pero podemos utilizar la caché de resultados de consultas (por ejemplo, de 1 a 5 minutos), ya que estos valores no cambian a menudo.

A la hora de crear consultas, a menudo empiezo a utilizar SQL, y luego lo transformo en SQLAlchemy, pero no soy el único.

En uno de los (muy) próximos posts puedo mostrar un diagrama de los modelos involucrados. Si has estado leyendo artículos anteriores, esto no será sorprendente. El elemento de contenido tiene una relación de uno a muchos con su modelo de traducción, la traducción de elementos de contenido. El modelo de elementos de contenido tiene relaciones de muchos a muchos con los modelos de categorías de elementos de contenido y de etiquetas de elementos de contenido, ambos con tablas de traducción. Y el modelo de categoría de contenido de ítems tiene una relación de uno a muchos con su modelo de traducción de contenido de ítems, categoría de ítems y traducción. Etc.

Enlaces / créditos

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