Converteren naar een meertalige database

Toen ik dit project, deze website, startte, hield ik er rekening mee dat het project meerdere talen moest ondersteunen. Maar natuurlijk heb ik niet echt gekeken naar het ontwerp van de database vanwege alle andere dingen die ik moest leren, toevoegen en implementeren. Na een bepaald moment kon ik meerdere talen weergeven en selecteren, maar dat was voor de teksten in de code en sjablonen en niet voor de inhoud van de database, zoals blogberichten en pagina's.

Om de inhoud meertalig te maken heb ik onderzoek gedaan en ontdekt dat er verschillende manieren zijn om dit te doen, elk met zijn voor- en nadelen. Als u nieuw bent in het onderwerp, raad ik u aan om de onderstaande links te bekijken. Ik heb besloten dat er maar één juiste manier is om dit te doen en dat is door een 'vertaaltabel' toe te voegen voor elke tabel die velden bevat die in meerdere talen beschikbaar moeten zijn, of, als u dat wenst, een 'vertaalobject' toe te voegen voor elk object dat attributen bevat die in meerdere talen beschikbaar moeten zijn. Ik weet dat ik nu voor complexere vragen zal komen te staan, zoals een terugval naar de standaardtaal, en het gebruik van tellen, paginering en limiet.

Standaardtaal, geselecteerde taal en taaluitval

Language fallback is het mechanisme waarbij we de contentitems in de standaardtaal tonen als deze niet beschikbaar is voor de geselecteerde taal. Een terugval in de taal is een beslissing die je moet nemen.

Zonder een taalfoutje laat je gewoon de inhoud in de geselecteerde taal zien, als die niet beschikbaar is dan is dat jammer (403). Dit is relatief eenvoudig. Als de geselecteerde taal Duits is, worden alleen de Duitse items getoond, als de geselecteerde taal Engels is, alleen de Engelse items.

Met de taal fallback toont u de contentitems in de geselecteerde taal, indien niet beschikbaar, dan toont u ze in de standaardtaal. De standaardtaal is de taal die altijd aanwezig moet zijn. Voor deze website heb ik besloten om een uitwijkmogelijkheid te implementeren en in de toekomst wil ik graag bepaalde uitwijkmogelijkheden voor een geselecteerde taal niet meer kunnen tonen.

Toevoegen van de talen aan de database

Voor alle meertalige content items moet een extra tabel worden toegevoegd. Ik heb al talen gedefinieerd in de configuratie, maar nu heb ik een taaltabel gemaakt:

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)

Dit maakt het ook mogelijk om in de toekomst een taal in en uit te schakelen. Vervolgens heb ik de inhoudscategorie gebruikt, dus kijk hoe dit werkt, ik heb net de categorie vertalingstabel toegevoegd die bijna een kopie is van de categorie tabel:

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

Na het doorvoeren van de wijzigingen en de mogelijkheid om categorieën en vertalingen aan te maken en te bewerken was het tijd voor wat testen. Mijn belangrijkste zorg was hoe ik de data uit deze nieuwe opzet kon krijgen, inclusief hoe ik een uitwijking naar een standaardtaal kon implementeren.

Verhuizen naar de vertalingen was niet veel werk, omdat ik de oorspronkelijke versie van de vertaaltabellen vrijwel identiek heb gehouden aan de tabellen. Voor de vertaaltabellen heb ik de language_id, ingesteld op de standaard taal, en content_item_id, ingesteld op het id-veld toegevoegd.

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

Het SQL te krijgen van de categorieën ziet er niet te ingewikkeld uit. De vertaaltabel wordt geallizeerd in twee tabellen, de eerste, tr_selected, gebruikt om categorieën voor de geselecteerde taal op te vragen, en de tweede, tr_default, om categorieën op te vragen voor de standaard taal. Hieronder selecteren we de naam en de slakkenvelden, language_id=3 betekent Duits en language_id=1 betekent Engels.
Als de vertaling voor de geselecteerde taal niet wordt gevonden, dan wordt de standaardtaal gebruikt.

Belangrijk: Merk op dat het titelveld in het model standaard de lege string als standaardwaarde heeft. De onderstaande vraag werkt dus wel, maar alleen als er geen taalrecord is opgegeven.

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;

Om dit te doen met SQLAlchemy we eerst een alias voor de vertaaltabel. Vervolgens gebruiken we het func.coalesce vervangen van de IFNULL, en de rest lijkt veel op de SQL vraag:

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

Moeten we attributen (velden) of objecten (records) selecteren? Vertaaltabelrecords zijn alleen aanwezig als er een vertaling is toegevoegd. Het lijkt logisch om objecten te selecteren in plaats van attributen, maar dit is niet mogelijk met SQLAlchemy, zie ook hieronder.

We willen dat de blog posts geselecteerd worden op datum van creatie. Ook omdat we de samengevoegde subresultaten filteren, met publiceerd = 1, krijgen we veel NULL waarden voor titel en slak. De reden is natuurlijk dat het resultaat alle records van de content_item tabel retourneert. We kunnen deze NULL records verwijderen door een HAVING verklaring toe te voegen aan het einde. Nu is het resultaat correct.
Maar er is een addertje onder het gras. Op dit moment is het aantal records in de content_item tabel 230. Het aantal gepubliceerde blogberichten is 12. Dit betekent dat het HAVING onderdeel meer dan 200 platen moet filteren, 95%!

Het WHERE deel is toegevoegd om de velden content_item_type, gepubliceerd en content_item_parent_id te filteren. Dit wordt gedaan om het aantal records voor het HAVING onderdeel te verminderen. Ik heb ook een ORDER BY clausule toegevoegd om eerst de meest recente blog posts te tonen.

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;

Bovenstaande vraag is gebaseerd op een voorbeeld van het internet. Ik ben nog steeds niet zeker van het HAVING onderdeel en moest ook nog toevoegen DISTINCT om dubbele rijen te verwijderen. Hoe dan ook, het SQLAlchemy query-equivalent van de bovenstaande SQL vraag is:

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

Ik moest op het internet zoeken naar de redenen waarom HAVING ik met, MariaDB maar niet met werkte SQLAlchemy, zie ook de links hieronder. We hebben hier de letterlijke kolom nodig, dit lijkt alleen te werken voor MySQL / MariaDB (?). Ik kan dit niet bevestigen, ik gebruik MariaDBalleen .

Laten we aannemen dat wanneer een taalvertaling wordt gepubliceerd, alles in orde is, wat betekent dat alle velden een waarde hebben gekregen. In dit geval hoeven we alleen maar te controleren of er een vertaaldossier bestaat. Gelukkig doet bovenstaande vraag dit voor ons. Als het vertaalmodel een titelveld definieert met een standaardwaarde van '' (lege tekenreeks), dan antwoordt de query met NULL voor de titel als het vertaalrecord niet bestaat. Precies wat we willen.

De bovenstaande query geeft attributen terug, geen objecten. Ik weet geen manier om objecten terug te geven, omdat de volgende veranderingen niet lukken omdat ze alleen letterlijke waarden kunnen teruggeven:

    func.coalesce(tr_selected, tr_default)

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

Voorlopig weet ik niet hoe ik alle attributen op een andere manier kan toevoegen aan de query(?).

Een andere manier om een taalflaude te implementeren

Ik hou van eenvoudige selecte queries en de bovenstaande buitenste gezamenlijke query is complex, dus laten we proberen te kijken naar een andere manier om dit te doen. Ik wil graag volledige objecten terug, een lijst met (content_item, content_item_vertaling) tuples, voor eenvoudige verwerking in de template. Stel dat de geselecteerde taal Duits is en de standaardtaal Engels. Onze gepubliceerde inhoud kan als volgt zijn:

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

Laten we aannemen dat dit blogberichten zijn. Dan hebben we 4 blog posts in het Engels (language_id=1), en 2 in het Duits (language_id=3). Stel ook dat er altijd een taaluitval is en dat de standaard taalitems altijd aanwezig zijn. Dan is het totale aantal blogberichten niets meer dan het aantal blogberichten voor de standaardtaal te tellen. We moeten zowel de content_item als de content_item_item_vertalingstabellen gebruiken.

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

Wat volgt uit het bovenstaande is dat wanneer de geselecteerde taal Duits is, een uitwijking moet worden gedaan wanneer de content_item.id Engelse maar geen Duitse vertalingen heeft. Met behulp van een vakbond kunnen we de artikelen krijgen, samenvoegen en bestellen voor Duits en Engels. We kunnen hier UNION_ALL gebruiken omdat we weten dat de Duitse en Engelse items gescheiden zijn. Aan het eind zetten we een ORDER BY lijstje om de resultaten te sorteren.

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

We kunnen ook paginering toevoegen door LIMIT 2 OFFSET 1 toe te voegen aan de query. Dit werkt. Is het beter dan de buitenste gezamenlijke vraag? Sommige tests toonden aan dat het minstens twee keer zo langzaam gaat. Maar het is eenvoudig en dat is ook een groot pluspunt. En het is nu mogelijk om objecten uit de SQLAlchemy query te halen in plaats van attributen. Precies wat ik wilde, daarom ging ik 'bezwaar' maken. Om de prestaties te verbeteren kunnen we de resultaten van de query's cachen, ze veranderen niet vaak. Maar dat kan later wel. De equivalente SQLAlchemy vraag is:

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

Ik ben blij omdat de vakbondsquery tuples met objecten retourneert die zonder manipulatie kunnen worden doorgegeven aan het Jinja sjabloon. En om de blogberichten voor paginering te krijgen, voegen we eenvoudigweg de offset toe en beperken we de functies tot de query. Het krijgen van het totale aantal blogberichten is gewoon het aantal blogberichten voor de standaardtaal.

Ook hier is er iets verwarrend. U kunt bestellen door de aangemaakte_on van het content item of de aangemaakte_on van het content item vertaling van de aangemaakte_on. Als u deze laatste gebruikt, kunnen uw blogberichten in de war raken. Dus voor het moment bestel ik bij de aangemaakte_on van de blog_post, niet van de vertaalde versies.

Om te bewijzen dat het werkt heb ik twee Duitse en een Spaanse blog post toegevoegd. Op dit moment ben ik nog steeds bezig met het converteren, op het moment dat ik dit bericht schrijf heb ik alleen de titel, ondertitel en meta data vertaald. Check Duits en Spaans om veranderingen te zien.

Samenvatting

Dit bericht beschrijft een manier om ondersteuning in meerdere talen toe te voegen voor inhoudelijke items zoals blogberichten en pagina's. De toegevoegde vertaaltabel voor elke tabel die vertalingen nodig heeft, maakt de zaken ingewikkelder. Ik maak me een beetje zorgen over het gebruik van het geheugen (en de prestaties) als de tabel met content items groeit. Maar we kunnen gebruik maken van query resultaat caching (bv. 1-5 minuten) omdat deze waarden niet vaak veranderen.

Bij het maken van query's begin ik vaak met het maken van query's SQL, en transformeer dit dan later naar SQLAlchemy, maar ik ben niet de enige.

In een van de (zeer) volgende berichten kan ik een schema van de betreffende modellen tonen. Als je eerdere berichten hebt gelezen, zal dit niet verrassend zijn. Het inhoudelijke punt heeft een één-op-veel relatie met het vertaalmodel voor de inhoudelijke vertaling van het inhoudelijke punt. Het inhoudsmodel heeft vele relaties met de inhoudscategorieën en inhoudsmodellen, beide met vertaaltabellen. En het content-item-categoriemodel heeft een één-op-veel relatie met het vertaalmodel content-item-category-vertaling. Enz.

Links / credits

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