Bug 24973

Summary: Allow to localize and translate system preferences with new yaml based installer
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Installation and upgrade (web-based installer)Assignee: Bernardo Gonzalez Kriegel <bgkriegel>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: bgkriegel, gmcharlt, jonathan.druart, lucas, magnus, martin.renvoize
Version: master   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.11.00
Bug Depends on: 13897    
Bug Blocks: 24897, 24972, 27619, 27620, 27621, 27622, 27623, 27624, 27625, 27822    
Attachments: Bug 24973: rewrite subset of system preferences as YAML
Bug 24973: Install localized sysprefs
Bug 24973: add locale-sysprefs.yml to localized dirs
Bug 24973: Translate locale-syspref.yml
Bug 24973: Add *-locale-prefs.po translation files
Bug 24973: Load custom localization file
Bug 24973: Load custom localization file
Bug 24973: Load custom localization file
Bug 24973: Load custom localization file
Bug 24973: Load custom localization file

Description Katrin Fischer 2020-03-25 12:18:00 UTC
In the de-DE installer a sysprefs.sql file is used to make some changes to the system preferences for a smoother installation experience. The file can be found here:

http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/de-DE/mandatory/system_preferences.sql;h=2692f02b02da9100630a92c4f89fe7caba8ac786;hb=7ce4122131a6c3d7f0fe7c953dc95fd5d831bf7f

It's a mix of:
- Translating prefs content (HTML prefs, they might go away to news sometime)
- Localization settings (date format, currency format, time format)
- Translating pref options (borrower titles and relationships)

It would be great if there was a way to keep this kind of modifications when switching to the new yaml based installer files.
Comment 1 Jonathan Druart 2020-03-25 13:06:29 UTC
Bernardo, could we imagine having the sysprefs' values translatable?
Comment 2 Bernardo Gonzalez Kriegel 2020-03-25 14:11:45 UTC
(In reply to Jonathan Druart from comment #1)
> Bernardo, could we imagine having the sysprefs' values translatable?

It's in my TODO list, but to translate explanations column.

In this case it's the values that must be translated.
Proposal:
* Split sysprefs.sql into localizable and not localizable parts
  eg. sysprefs localsysprefs, our choice
* Put them in /en/ dir
* rewrite them as yaml, on syspref only translate explanation, on localsysprefs explanation and value
* add strings from sysprefs in xx-YY-installer.po
* add strings from localsysprefs in it's own po file.
  This file should contain useful comments so that the translators know what values are valid.

What do you think?
Comment 3 Jonathan Druart 2020-03-26 13:44:02 UTC
(In reply to Bernardo Gonzalez Kriegel from comment #2)
> (In reply to Jonathan Druart from comment #1)
> > Bernardo, could we imagine having the sysprefs' values translatable?
> 
> It's in my TODO list, but to translate explanations column.
> 
> In this case it's the values that must be translated.
> Proposal:
> * Split sysprefs.sql into localizable and not localizable parts
>   eg. sysprefs localsysprefs, our choice
> * Put them in /en/ dir
> * rewrite them as yaml, on syspref only translate explanation, on
> localsysprefs explanation and value
> * add strings from sysprefs in xx-YY-installer.po
> * add strings from localsysprefs in it's own po file.
>   This file should contain useful comments so that the translators know what
> values are valid.
> 
> What do you think?

We do not need to translate the explanation, it's not used. So no need to split them.
Comment 4 Jonathan Druart 2020-03-26 13:46:22 UTC
(In reply to Katrin Fischer from comment #0)
> In the de-DE installer a sysprefs.sql file is used to make some changes to
> the system preferences for a smoother installation experience. The file can
> be found here:
> 
> http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/
> mysql/de-DE/mandatory/system_preferences.sql;
> h=2692f02b02da9100630a92c4f89fe7caba8ac786;
> hb=7ce4122131a6c3d7f0fe7c953dc95fd5d831bf7f
> 
> It's a mix of:
> - Translating prefs content (HTML prefs, they might go away to news sometime)
> - Localization settings (date format, currency format, time format)
> - Translating pref options (borrower titles and relationships)
> 
> It would be great if there was a way to keep this kind of modifications when
> switching to the new yaml based installer files.

We must split this bug report, there are 2 different requests:
1. Translate the default value of the sysprefs
2. Set predefined settings depending on the region/country/etc

Then it seems we need to define some options, but we need to list exhaustively which ones are valid.
Comment 5 Bernardo Gonzalez Kriegel 2020-03-26 14:26:46 UTC
(In reply to Jonathan Druart from comment #3)
> 
> We do not need to translate the explanation, it's not used. So no need to
> split them.

Yes, I realized that and discarded my own idea.


(In reply to Jonathan Druart from comment #4)
> 
> We must split this bug report, there are 2 different requests:
> 1. Translate the default value of the sysprefs
> 2. Set predefined settings depending on the region/country/etc
> 
> Then it seems we need to define some options, but we need to list
> exhaustively which ones are valid.

My current approach is to rewrite in yaml format only a prefs subset (using those from DE, but can be defined), then producing a PO file like 

#. Use this to SET sysprefs
#. variable: OpacNav
#. options: 70|10
#. explanation: Use HTML tags to add navigational links to the left-hand navigational bar in OPAC
#: installer/data/mysql/en//sysprefs.yml:systempreferences:1:row:13:mul:1
msgid "Important links here."
msgstr ""

#. Use this to SET sysprefs
#. variable: BorrowersTitles
#. options: 
#. explanation: Define appropriate Titles for patrons
#: installer/data/mysql/en//sysprefs.yml:systempreferences:1:row:4:mul:1
msgid "Mr|Mrs|Miss|Ms"
msgstr ""

The text after '#.' is shown in pootle as comments to the left of the translation boxes (see https://translate.koha-community.org/es/19.11/translate/es-ES-staff-prog.po#unit=10916796)

With po entries like this strings, in it's own file, we cat 'set' values for a selected group of sysprefs

I'm using a sysprefs.yml file in 'en/' dir, that give us (2.), and its 'translation' (1.)
Comment 6 Bernardo Gonzalez Kriegel 2020-04-16 11:15:55 UTC
Created attachment 103071 [details] [review]
Bug 24973: rewrite subset of system preferences as YAML

This patch removes a subset of system preferences
from sysprefs.sql and rewrite them as YAML to
SET their value for different languages using
the translation process.

The selected subset is an arbitrary, at this moment
is similar to the one present in de-DE sysprefs file.
Other languages update a different subset of sysprefs.

I think that what the final subset will be falls
outside the scope of this report. It can be enlarged
or shortened at will.

Test plan in following patch.
Comment 7 Bernardo Gonzalez Kriegel 2020-04-16 11:15:59 UTC
Created attachment 103072 [details] [review]
Bug 24973: Install localized sysprefs

This patch changes installation files to
process and install locale_sysprefs.yml

This file must be present on each install
dir. It complements sysprefs.sql, and is
processed just before it.

To test for EN
1) Apply the patch
2) Do a normal install
   only change to note is a new section
   when installer reports installed files
   named 'En data added', 'En' for english
   and shows locale-syspref.yml
3) Verify no changes on sysprefs values
Comment 8 Bernardo Gonzalez Kriegel 2020-04-16 11:16:05 UTC
Created attachment 103073 [details] [review]
Bug 24973: add locale-sysprefs.yml to localized dirs

This patch put a copy of en/locale-sysprefs.yml on
each localized installation directory.

Each file complements general sysprefs.sql file
to enable a normal install.

To test:
1) Apply the patch

2) Do a clean install for any of current localized
dirs: de-DE,es-ES,fr-CA,fr-FR,it-IT,nb-NO,pl-PL,ru-RU
and uk-UA

3) Check systempreferecences table content
No changes expected.
Comment 9 Bernardo Gonzalez Kriegel 2020-04-16 11:16:09 UTC
Created attachment 103074 [details] [review]
Bug 24973: Translate locale-syspref.yml

This patch modifies LangInstaller.pm to
enable translation of the new localized
system preference file.

This 'translation' is special because it's
used to SET some system preferences, not
just translate some strings.

The translation .po file is special, with a
structure similar to the syspref translation file.
It adds some comments to guide the translator
in order to put correct values in the translation,
perhaps an explanation will be needed in release notes.

All new entries are set as 'fuzzy' and have a copy of
the 'en' values as a reference.

To test:
1) Apply the patch
2) Create translations file for a 'new' language
  cd misc/translation
  ./translate create xx-YY

  check new file po/xx-YY-locale-prefs.po
3) Update translations
   translate some strings on previous file,
   remember that those values sets the preference
   ./translate update xx-YY
   check translations are preserved

3) Install translations
   ./translate install xx-YY

   check new locale-sysprefs.yml file in
   installer/data/mysql/xx-YY/
   check 'translated' options

4) Do a clean install with the new language
   Check correct value on sysprefs
Comment 10 Bernardo Gonzalez Kriegel 2020-04-16 11:16:22 UTC
Created attachment 103075 [details] [review]
Bug 24973: Add *-locale-prefs.po translation files

This patch adds the new translation file for all
current languages.

To test:
1) Check update for a language (with no install dir)
   cd misc/translator
   ./translate update pt-BR

2) Check install
   ./translate install pt-BR

    verify creation of install dir ( installer/data/mysql/pt-BR)
    and locale_prefs.yml file

3) Do a clean install for your selected language,
   no problems expected
Comment 11 Bernardo Gonzalez Kriegel 2020-04-16 11:25:56 UTC
Hi, I've uploaded my proposal for this problem.
I create a new translation file, needed because it must show a message/advice to the translator. 
Then I need a new way to build the translation file, duplicating some of the code, can't found a better way for the moment, and release time is approaching, hope this pass.
Comment 12 Martin Renvoize 2020-05-11 11:48:56 UTC
Struggling to wrap my head around this one at the moment.. do we basically end up with still having to maintain a set of distinct sysprefs.yaml files within the koha repo?

If that's the case.. I wonder if we could instead split localisation into a distinct step in the installer.. so prefs are translated 'as is'.. but if you want to add/change defaults those changes go into their own distinct area and files.. something akin to the atomic updates and we run those after the rest of install as a localisation step.
Comment 13 Bernardo Gonzalez Kriegel 2020-05-11 21:19:10 UTC
(In reply to Martin Renvoize from comment #12)
> Struggling to wrap my head around this one at the moment.. do we basically
> end up with still having to maintain a set of distinct sysprefs.yaml files
> within the koha repo?

Hi Martin,
I'm not entirely happy with this approach too, and I'm open to suggestions.
As of now we can only do inserts using yaml, perhaps we can leave syspres.sql as is and then add a yaml file to 'update' some selected subset of sysprefs, using this last one to translate.

> If that's the case.. I wonder if we could instead split localisation into a
> distinct step in the installer.. so prefs are translated 'as is'.. but if
> you want to add/change defaults those changes go into their own distinct
> area and files.. something akin to the atomic updates and we run those after
> the rest of install as a localisation step.

I like the idea :)
It's simpler, I'll try to write an alternative patch.
Thanks
Comment 14 Martin Renvoize 2020-05-12 08:43:14 UTC
Thanks for all your work here Bernardo.

I never realise quite how involved translation and localisation gets.  Your ongoing work here is greatly appreciated both by myself and the wider community I'm sure.
Comment 15 Katrin Fischer 2020-05-15 08:58:26 UTC
Thx for all the work here everyone.

Just a thought: Why not keep it simple for now? We could keep the SQL files for any additional changes needed after the default installer files are loaded.

At the moment it's very easy to customize the installer, as you just need to add your own directory (optional) and a text and sql file. If we could keep that ability at the same time, it would be a nice benefit. I have to admit, we run some changes on the frameworks and other standard settings.
Comment 16 Bernardo Gonzalez Kriegel 2020-05-15 12:48:11 UTC
My turn to thank you "All" for your work :)

(In reply to Katrin Fischer from comment #15)
> Just a thought: Why not keep it simple for now? We could keep the SQL files
> for any additional changes needed after the default installer files are
> loaded.

If I correctly interpret what you say is to leave all or part of the directory structure and at least one sql file with customizations, then the translation process populates those directories and then installation applies them. 
Is that correct?

Something like this can be used for system preferences and frameworks, since they are loaded before the other files.
Or if we agree in a hardcoded name then we could guarantee that it loads last.

> At the moment it's very easy to customize the installer, as you just need to
> add your own directory (optional) and a text and sql file. If we could keep
> that ability at the same time, it would be a nice benefit. I have to admit,
> we run some changes on the frameworks and other standard settings.

It's easy, if you know how to do it :)
Comment 17 Jonathan Druart 2020-05-15 13:00:59 UTC
We could have a "localization" directory with common files (from Koha codebase) to have some localization stuffs set by default.
localization/es-ES de-DE, etc.
Then an empty localization/custom directory that we would look up to find .sql files.
Comment 18 Bernardo Gonzalez Kriegel 2020-05-15 15:45:53 UTC
(In reply to Jonathan Druart from comment #17)
> We could have a "localization" directory with common files (from Koha
> codebase) to have some localization stuffs set by default.
> localization/es-ES de-DE, etc.
> Then an empty localization/custom directory that we would look up to find
> .sql files.

You mean something like:

1) Before "./translate install xx-YY"
en/
    mandatory/
    marcflavour/
    optional/

localization/
    de-DE/
    fr-CA/
    xx-YY/


2) After "translate install xx-YY", new dir with translations
xx-YY/
    mandatory/
    marcflavour/
    optional/

and we load first from 'xx-YY/' and last from 'localization/xx-YY/',
with xx-YY lang maintainer responsible for xx-YY/ content (mostly updates)
Comment 19 Bernardo Gonzalez Kriegel 2020-05-15 21:44:38 UTC
Created attachment 104971 [details] [review]
Bug 24973: Load custom localization file

This is a working POC patch

It adds a new localization directory that could
host a custom SQL file for each language,
xx-YY/custom.sql

This will be the last file to be loaded at
install time, it can be used to set any difference
from default values.

It can't be deselected at install time.

To test:
1) Apply the patch
2) Install es-ES translation
   ( cd misc/translation; ./translate install es-ES )
3) Do a clean install using es-ES
4) After all files have been loaded check:
    * A new section labeled "Localization data added"
      with one file, custom.sql
    * Inspect the value of FrameworksLoaded syspref,
      last entry must be custom.sql
5) Remove/rename the file or localization dir and
   repeat 3/4, install must proceed normally
Comment 20 Bernardo Gonzalez Kriegel 2020-05-15 21:58:41 UTC
New patch, new proposal.
One custom SQL file for each language in a new dir, untranslatable of course.
It's ignored for english.

Please tell me what you think :)
Comment 21 Jonathan Druart 2020-05-18 08:35:26 UTC
(In reply to Bernardo Gonzalez Kriegel from comment #18)
> (In reply to Jonathan Druart from comment #17)
> > We could have a "localization" directory with common files (from Koha
> > codebase) to have some localization stuffs set by default.
> > localization/es-ES de-DE, etc.
> > Then an empty localization/custom directory that we would look up to find
> > .sql files.
> 
> You mean something like:
> [...]

Yes!
Comment 22 Jonathan Druart 2020-05-18 08:36:01 UTC
Switching to NSO to get feedback on the POC.
Comment 23 Bernardo Gonzalez Kriegel 2020-05-18 16:44:38 UTC
Created attachment 105032 [details] [review]
Bug 24973: Load custom localization file

It adds a new localization directory that could
host a custom SQL file for each language,
xx-YY/custom.sql

This will be the last file to be loaded at
install time, it can be used to set any difference
from default values.

It can't be deselected at install time.

Moved corresponding files for de-DE, it-IT,
nb-NO and completed es-ES

To test:
1) Apply the patch
2) Install es-ES/de-DE/it-IT/nb-NO translation (any lang)
   ( cd misc/translation; ./translate install es-ES )
3) Do a clean install using es-ES
4) After all files have been loaded check:
    * A new section labeled "Localization data added"
      with one file, custom.sql
    * Inspect the value of FrameworksLoaded syspref,
      last entry must be custom.sql
5) Remove/rename the file or localization dir and
   repeat 3/4, install must proceed normally
Comment 24 Bernardo Gonzalez Kriegel 2020-05-18 17:11:56 UTC
(In reply to Jonathan Druart from comment #22)
> Switching to NSO to get feedback on the POC.

Uploaded revised patch, now include files for de-DE, it-IT and nb-NO,
taken from their language dirs.

With something like this de-DE is covered and main dir could be removed.
it-IT needs UNIMARC, more work to do
nb-NO uses NORMARC, this case needs more thinking


But any future language can be added there to have the few customizations deemed essential by its maintainer.

If this approach is accepted we need to change the Bug description.
Comment 25 Magnus Enger 2020-05-19 06:48:26 UTC
(In reply to Bernardo Gonzalez Kriegel from comment #24)
> nb-NO uses NORMARC, this case needs more thinking

NORMARC is actually dead (Bug 18984), no new sites should be created with NORMARC as the MARC flavour. New sites should just use the default English MARC 21 frameworks. 

(Existing sites will continue to use NORMARC a bit longer, but hopefully we will convert to MARC 21 some time before the end of the year.)
Comment 26 Martin Renvoize 2020-08-05 15:08:49 UTC
(In reply to Bernardo Gonzalez Kriegel from comment #24)
> (In reply to Jonathan Druart from comment #22)
> > Switching to NSO to get feedback on the POC.
> 
> Uploaded revised patch, now include files for de-DE, it-IT and nb-NO,
> taken from their language dirs.
> 
> With something like this de-DE is covered and main dir could be removed.
> it-IT needs UNIMARC, more work to do
> nb-NO uses NORMARC, this case needs more thinking
> 
> 
> But any future language can be added there to have the few customizations
> deemed essential by its maintainer.
> 
> If this approach is accepted we need to change the Bug description.

This looks great to me and seems to achieve what's desired.. but I'm not the target audience so I'm not comfortable doing a QA step.. I suppose doing the SO would politely push it into Katrins pile.  One note though.. I don't see the additional languages you suggest you added in the above patch?
Comment 27 Martin Renvoize 2020-08-12 14:56:25 UTC
(In reply to Martin Renvoize from comment #26)
> (In reply to Bernardo Gonzalez Kriegel from comment #24)
> > (In reply to Jonathan Druart from comment #22)
> > > Switching to NSO to get feedback on the POC.
> > 
> > Uploaded revised patch, now include files for de-DE, it-IT and nb-NO,
> > taken from their language dirs.
> > 
> > With something like this de-DE is covered and main dir could be removed.
> > it-IT needs UNIMARC, more work to do
> > nb-NO uses NORMARC, this case needs more thinking
> > 
> > 
> > But any future language can be added there to have the few customizations
> > deemed essential by its maintainer.
> > 
> > If this approach is accepted we need to change the Bug description.
> 
> This looks great to me and seems to achieve what's desired.. but I'm not the
> target audience so I'm not comfortable doing a QA step.. I suppose doing the
> SO would politely push it into Katrins pile.  One note though.. I don't see
> the additional languages you suggest you added in the above patch?

Bernardo.. do you still have the de-DE, it-IT parts of the patch somewhere?

If so, I can signoff and we can move into QA
Comment 28 Bernardo Gonzalez Kriegel 2020-08-12 16:07:08 UTC
Created attachment 108130 [details] [review]
Bug 24973: Load custom localization file

It adds a new localization directory that could
host a custom SQL file for each language,
xx-YY/custom.sql

This will be the last file to be loaded at
install time, it can be used to set any difference
from default values.

It can't be deselected at install time.

Moved corresponding files for de-DE, it-IT,
nb-NO and completed es-ES

To test:
1) Apply the patch
2) Install es-ES/de-DE/it-IT/nb-NO translation (any lang)
   ( cd misc/translation; ./translate install es-ES )
3) Do a clean install using es-ES
4) After all files have been loaded check:
    * A new section labeled "Localization data added"
      with one file, custom.sql
    * Inspect the value of FrameworksLoaded syspref,
      last entry must be custom.sql
5) Remove/rename the file or localization dir and
   repeat 3/4, install must proceed normally
Comment 29 Bernardo Gonzalez Kriegel 2020-08-12 16:10:50 UTC
(In reply to Martin Renvoize from comment #27)
> 
> Bernardo.. do you still have the de-DE, it-IT parts of the patch somewhere?
> 
> If so, I can signoff and we can move into QA

Hi Martin, uploaded patch with all changes this time.
Thanks!
Comment 30 Bernardo Gonzalez Kriegel 2020-08-13 03:00:48 UTC
(In reply to Bernardo Gonzalez Kriegel from comment #29)
> (In reply to Martin Renvoize from comment #27)
> > 
> > Bernardo.. do you still have the de-DE, it-IT parts of the patch somewhere?
> > 
> > If so, I can signoff and we can move into QA
> 
> Hi Martin, uploaded patch with all changes this time.
> Thanks!

It's the same patch, sorry. 
But if you look at the details, you will see that some of the files are simply renamed

 rename installer/data/mysql/{de-DE/mandatory/system_preferences.sql => localization/de-DE/custom.sql} (98%)
 rename installer/data/mysql/{de-DE/mandatory/system_preferences.txt => localization/de-DE/custom.txt} (100%)
 rename installer/data/mysql/{it-IT/necessari/system_preferences.sql => localization/it-IT/custom.sql} (99%)
 rename installer/data/mysql/{nb-NO/1-Obligatorisk/system_preferences.sql => localization/nb-NO/custom.sql} (100%)
Comment 31 Martin Renvoize 2020-08-13 09:10:08 UTC
Excellent, thanks Bernardo, looks good now I can see the renames :)
Comment 32 Martin Renvoize 2020-08-13 09:11:06 UTC
Created attachment 108166 [details] [review]
Bug 24973: Load custom localization file

It adds a new localization directory that could
host a custom SQL file for each language,
xx-YY/custom.sql

This will be the last file to be loaded at
install time, it can be used to set any difference
from default values.

It can't be deselected at install time.

Moved corresponding files for de-DE, it-IT,
nb-NO and completed es-ES

To test:
1) Apply the patch
2) Install es-ES/de-DE/it-IT/nb-NO translation (any lang)
   ( cd misc/translation; ./translate install es-ES )
3) Do a clean install using es-ES
4) After all files have been loaded check:
    * A new section labeled "Localization data added"
      with one file, custom.sql
    * Inspect the value of FrameworksLoaded syspref,
      last entry must be custom.sql
5) Remove/rename the file or localization dir and
   repeat 3/4, install must proceed normally

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 33 Martin Renvoize 2020-08-13 09:11:44 UTC
Works as expected as I understand it.. Signing off and pushing to Katrin's pile for QA :)
Comment 34 Katrin Fischer 2020-08-23 00:02:05 UTC
Created attachment 108946 [details] [review]
Bug 24973: Load custom localization file

It adds a new localization directory that could
host a custom SQL file for each language,
xx-YY/custom.sql

This will be the last file to be loaded at
install time, it can be used to set any difference
from default values.

It can't be deselected at install time.

Moved corresponding files for de-DE, it-IT,
nb-NO and completed es-ES

To test:
1) Apply the patch
2) Install es-ES/de-DE/it-IT/nb-NO translation (any lang)
   ( cd misc/translation; ./translate install es-ES )
3) Do a clean install using es-ES
4) After all files have been loaded check:
    * A new section labeled "Localization data added"
      with one file, custom.sql
    * Inspect the value of FrameworksLoaded syspref,
      last entry must be custom.sql
5) Remove/rename the file or localization dir and
   repeat 3/4, install must proceed normally

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 35 Jonathan Druart 2020-08-24 10:18:28 UTC
Here we are! Simple and efficient, great work :)
Comment 36 Jonathan Druart 2020-08-24 10:32:53 UTC
Pushed to master for 20.11, thanks to everybody involved!
Comment 37 Lucas Gass 2020-09-04 19:46:22 UTC
enhancement will not be backported to 20.05.x
Comment 38 Martin Renvoize 2020-09-04 21:39:47 UTC
Awesome work everyone, thanks 🙂
Comment 39 Martin Renvoize 2020-09-04 21:40:04 UTC
Awesome work everyone, thanks 🙂