Simplified syspref handling

Status:	unknown
Sponsored by:	no-one
Developed by:	Magnus Enger
Expected for:	none
Bug number:	Bug 6537
Work in progress repository:
Description:	At the moment there is one sysprefs.sql file for each of 8 languages, all defining sysprefs and their descriptions. This leads to a situation where developers need to update all 8 files when sysprefs are added or changed, which makes it easy to forgot a file or make some other mistake. And lets hope more languages are added in time...

Work in progress: https://github.com/MagnusEnger/kohawork/tree/bug6537-sysprefs

One master syspref.sql

Would it be possible to have one master sysprefs.sql file, which defines all of the sysprefs (with or without the explanation column), independent of language - and then the possibility of having a much smaller SQL file available to the installer, which just sets those syspref defaults that should differ from the global defaults?

During installation the global sysprefs.sql would be loaded into the database first, and then there could be the option of loading an additional file that sets other values than the global defaults. For Norwegian installations we would want to set the date format to something other than the default, for example.

New possibilities

With the handling of sysprefs proposed here it would be feasible to have alternate syspref files, e.g. one for public libraries and one for academic libraries (a feature that has been mentioned). Which one is actually loaded into the database could then be decided when the web installer is run.

This implementation would also make it possible to choose for any given language to make the language specific sysprefs.sql optional or mandatory. So one could have e.g.

two optional files, one for public and one for academic libraries
one mandatory file and one optional one that can supplement or override it (as long as one makes sure the mandatory one is loaded before the optional one, this depends on the names of the .sql files! See the POD for C4::Installer::load_sql_in_order() for the gory details.)

What to do

Move installer/data/mysql/en/mandatory/sysprefs.sql to installer/data/mysql/sysprefs.sql
- Remove unused columns?
Change the web-installer to load this file, after the structure has been created
Create simplified syspref files for each language, which only contains the sysprefs that have default values that differ from the defaults defined in the global sysprefs.sql
- This should give an error if one tries to insert a syspref that is not in the global sysprefs.sql: UPDATE systempreferences SET value = x WHERE variable = y;

Creating simplified syspref files

Load the existing syspref files one by one into a database and dump out the value and variable columns, something like this:

mysql -u <databaseuser> -p -e "select value, variable into outfile '/tmp/syspref-<languagecode>.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' from <database>.systempreferences order by variable;"

This should give files like e.g. /tmp/syspref-en.txt with contents similar to this:

ordering,AcqCreateItem
categorycode,AddPatronLists
0,advancedMARCeditor
itemtypes,AdvancedSearchTypes
1,AllFinesNeedOverride
0,AllowAllMessageDeletion
0,AllowFineOverride
etc

Now compare each non-English file to the English one with diff:

diff /tmp/syspref-en.txt /tmp/syspref-de.txt

This should point out the lines where the values for any syspref are not the same, e.g.:

$ diff /tmp/syspref-en.txt /tmp/syspref-de.txt 5d4
47c40
< father|mother,borrowerRelationship
---
> Vater|Mutter,borrowerRelationship
49c42
< Mr|Mrs|Miss|Ms,BorrowersTitles
---
> Herr|Frau,BorrowersTitles
etc

The lines we are interested in are the ones preceded with ">", so this can be boiled down to:

Vater|Mutter,borrowerRelationship
Herr|Frau,BorrowersTitles

And turned into SQL:

UPDATE systempreferences SET value = 'Vater|Mutter' WHERE variable = 'borrowerRelationship';
UPDATE systempreferences SET value = 'Herr|Frau' WHERE variable = 'BorrowersTitles';

which is then used to replace the contents of installer/data/mysql/de-DE/mandatory/sysprefs.sql.

Remove unused columns?

This might be a good opportunity for removing some unused columns in the systempreferences table.

mysql> describe systempreferences;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| variable    | varchar(50) | NO   | PRI |         |       |
| value       | text        | YES  |     | NULL    |       |
| options     | mediumtext  | YES  |     | NULL    |       |
| explanation | text        | YES  |     | NULL    |       |
| type        | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Not used, because they are handled by the .pref files:

explanation
options (i think?)

These columns are still used for local sysprefs, so we better keep them!

Remove tests?

Looks like this will make xt/syspref.t and xt/check_sysprefs.t obsolete?

Things to remember

~~Update kohastructure.sql if we remove unused columns~~
~~Update updatedatabase.pl~~
Update the System Preferences page

Simplified syspref handling RFC

Contents