Traduki

Introduction

traduki is an open source package, which consists of internationalization helper classes targeted for SQLAlchemy-based python projects. The advantage of using traduki is that it removes the burden of defining translation tables, and provides a consistent, intuitive and easy way to introduce internationalization into your application. Minimalistic design allowed us to use only SQLAlchemy as a python dependency.

Why we were in a need of something different

As Paylogic operates in several countries, internationalization is a strong requirement. However, we used to do internationalization differently from how we do it these days. Our former approach was to join translation tables in order to obtain the translations. This allowed us to search on the internationalized fields, but it required a lot of joins, even in cases where searching was not a requirement. We could either eager load, load relationships at the same time the parent is loaded, or lazy load, load relationships the first time they are accessed. When we lazy load, and access an internationalized property, we cause two queries per property.

One of these queries is to the translations table, and the other one to a table with only the ids. The translations table had one translation per row, which made it inefficient and difficult to get all the translations for all the properties in one row. For example,

where the text_id references the title_id and subtitle_id fields from a hypothetical Event table.

This approach was inefficient because for n properties and m languages, we would need to do n*m joins. The difficult part comes from the fact that it was cumbersome to write those queries. In the case of the previous example,

a_alias = aliased(Translation)

q = session.query(Event).\
    join(Translation).\
    filter(Translation.text_id==Event.title_id).\
    filter(a_alias.text_id==Event.subtitle_id).\
    filter(Event.id.in_(event_ids)).\
    all()

The end-result was that we did not do this, and we were doing more one-row queries. We should mention that normally we don't need objects to have dynamic list of available languages. Maybe it is a strict requirement in other use cases, but in our use case it is enough to just use a static set of available languages, which change infrequently.

Another issue with this approach was that the number of results returned from queries was not deterministic, and required left joins which is even worse performance-wise. Most of the time you want to eager load relationships. However, in this case you can never apply a limit or offset because you cannot trust the number of rows returned.

The aforementioned approach had performance issues. We wanted to be able to search on the internationalized fields and search fast, which was not possible. Another requirement was that we wanted language chains. What this means is that if your language is Dutch, but only the English version of the text is available, we should display by default the English version of it.

Advantages of the new design

In the end, taking into consideration our motivation and requirements, we came up with our solution on how to solve the problem of i18n. The following example illustrates our current approach using traduki. Let's assume that we have a table Event, and we want the title and subtitle to be translated into English and Dutch. The Event table contains the ids of the fields that we wish to have available in those two languages. Let's also assume that for the event with id = 8, the Dutch translation is not available.

The Translation table would then contain a reference to those fields that we wish to have translated. The id = 10 for the title and id = 11 for the subtitle of the first event, and id = 25 and id = 26 for the title and subtitle for the second event respectively. It also contains the translated texts in English and Dutch (only for the first event). With this approach, we can easily get the translated texts by joining the Event and Translation tables.

The advantage of this approach is that with a simple join between these tables on the id of the text (for example the title_id), we get one row with all the translations.

q = session.query(Translation).join(Event, Event.title_id==Translation.id)

As can be seen from the query, for n properties and m languages, the number of joins is reduced from n*m to n, making them also more intuitive since all translated items are foreign keys to the Translation table, joining once per foreign key. Additionally, traduki returns a user-friendly format of this result as a dictionary of language codes and translations. For example:

{'en': 'English title 1', 'nl': 'Dutch title 1'}

In case of the second event, where the Dutch translation is not available, traduki falls back to the language that we have defined, in this case English. So it will return:

{'en': 'English title 2', 'nl': 'English title 2'}

This approach has one drawback. When a new language is introduced then we need to alter the translations table to include it. Although this operation can be expensive, we found out that the gains in performance are higher, because we search and sort much more often than we add new languages. Also, for us it is more important to have a static set of available languages than actually adding new languages.

How it works

traduki is very simple to use. The following example is a concise and stand-alone application that illustrates the use of traduki. It is split in parts, to better explain how each part works.

import traduki

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite://')

Base = declarative_base()

def get_current_language():
    """Current language callback for our project."""
    return 'en'


def get_language_chain():
    """Language chain (fall-back rule) callback for our project."""
    return {'*': 'en'}

i18n_attributes = traduki.initialize(
    Base, ['en', 'nl'], get_current_language, get_language_chain)

class Translation(Base):

    __tablename__ = 'traduki_translation'

    id = Column(Integer, primary_key=True)

    en = Column(UnicodeText, nullable=True, index=True)
    nl = Column(UnicodeText, nullable=True, index=True)

class Model(Base):

    __tablename__ = 'model'

    id = Column(Integer, primary_key=True)

    title_id = i18n_attributes.i18n_column(nullable=False, unique=False)
    title = i18n_attributes.i18n_relation(title_id)
    """Title."""

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

model = Model()
model.title = {'en': 'English title', 'nl': 'Dutch title'}
session.add(model)
session.commit()

session.refresh(model)

assert model.title.get_dict() == {'en': 'English title', 'nl': 'Dutch title'}
assert model.title.en == 'English title'

virtualenv env

source env/bin/activate

pip install sqlalchemy traduki

python example.py

english_title_objects = (
    session.query(Model)
    .join(
        i18n_attributes.Translation,
        Model.title_id == i18n_attributes.Translation.id)
    .filter(i18n_attributes.Translation.en.isnot(None))
    .all()
)

The difference from SQLAlchemy-i18n

We conducted research on how to make an efficient design. We tried lots of ways to minimize the timing of the queries for large datasets. Also we've looked around for existing solutions and we found SQLAlchemy-i18n.

The approach of this project is to create a separate translations table and each row in the table is a translation in a specific language for a specific field. This is similar to our previous approach and has the same limitations in performance. You need to explicitly query for languages and fields and do lots of joins. What traduki does, is load all the languages and translations for a field. This might sound like a lot of overhead at first, but in modern applications you usually have 10-12 languages and you want them to be available all at once in the client.

Conclusion

In general, not much has been done on i18n in open source. We hope that traduki will prove useful for projects that require an efficient and easy to use internationalization system. We are waiting for your feedback and recommendations. Check traduki in our github profile.