1481175 - Add an `active` marker to translations and ensure its consistency

Status: RESOLVED FIXED

(Webtools Graveyard :: Pontoon, enhancement, P2)

Description

[[DEACTIVATED] Adrian Gaudebert]

Pontoon has a notion of an "active" translation for each entity+locale pair. The active translation is, in descending order: 
- the approved translation
- the fuzzy translation (there can only be zero or one of either approved or fuzzy)
- the latest suggested translation

Finding in a SQL query which translation is the active is pretty complex, because there can be any number of suggestions, and we have plurals for PO files. 

By adding an `active` marker to our Translations table, we would make the logic of finding the active translation for each entity and plural form a lot simpler. We would move the complexity to the adding/updating methods of translations, but I think that's a good trade-off. 

This work will require, as far as I can tell: 

1. adding a marker to the Translations table, called active, that is a boolean and that has a strong unique constraint: can be true for only one (locale + entity + plural_form) association;
2. refactoring code to ensure that marker is updated correctly when data changes (through the Translate app or the Sync app);
3. making sure the constraint is never broken;
4. using that marker to simplify logic wherever getting the active translation is used.

That work will also unblock adding some features (see Blocks).

Comment 1

[Axel Hecht [:Pike]]

Implementation note, on elmo, I use a marker table `Active`, which references back to my actual data table.

No idea if that idea was actually good or not, just sayin'

Comment 2

[Matjaz Horvat [:mathjazz]]

In addition to bug 1407180 (unchanged filter), having an easy way to get active translations would also help the get-entities view (needed to load entities and translations to sidebar and editor). Maybe, it could even allow us to stop denormalizing stats.

In the past I experimented with approach closer to :Pike's proposal (ActiveTranslation table vs active field in the Translation table). That table also contained all fields needed for searching and filtering enities, which made these operations much faster and also simplified the code.

See the last two commits in the "faster-get-entities" branch:
https://github.com/mathjazz/pontoon/tree/faster-get-entities

Comment 3

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/pontoon/pull/1072

Comment 4

[[github robot]]

Commit pushed to master at https://github.com/mozilla/pontoon

https://github.com/mozilla/pontoon/commit/6db15d17c9707ac1340119acc0e67dba7839fac0
Fix bug 1481175: Store active translation in the DB (#1072)

This patch introduces a Translations.active field. It is Boolean and
has a constraint to be True for a maximum of one unique combination of
(locale, entity, plural_form). Active translations are displayed in the
string list and as the first entry in the History tab.

The idea is to store active translations in the DB, instead of
calculating them on the fly, which is expensive. Hence, we're adding a
data migration to set Translation.active to True where needed for
existing translations. We also make sure that the active field is
updated properly when translations are changed. And finally, we're
making use of the active field instead of calculating it in the code.

For practical reasons, we're also fixing two other bugs:
* Bug 1477581: Unfuzzy on mass reject (and approve). These changes
  prevent translations from being in double state: fuzzy and approved
  or rejected.
* Bug 1397138: Adding suggestions must not unfuzzy strings.

The patch also removes the Translation.entity_document field and
related trigger and function, which were introduced earlier and aren't
used anymore.

It also makes code for unapproving, unfuzzying and rejecting
translations more consistent and maintainable.