Simplified syspref handling RFC

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
Category:RFCs
Category:RFCs_not_targeted_at_a_particular_Koha_version