From 22a4d630136f5734048f400f34f17673f8bb99aa Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 8 May 2013 16:29:23 +0200 Subject: [PATCH] Bug 9998: Import/export and compare system preferences Content-Type: text/plain; charset=utf-8 This patch adds a new maintenance script: cmp_sysprefs.pl Test plan: 1 Run with -cmd backup -file zz01. Check that file. Run with -cmd test -file zz01. Check zz01.sav. 2 Run with -cmd compare -file zz01. No differences expected. 3 Edit zz01: delete two prefs, change two prefs and add two new prefs. 4 Run with -cmd compare -file zz01. Are all six changes reported? 5 Add local use preference zz02 in the staff client (no explanation, options). 6 Run with -cmd backup -file zz02. 7 Delete local use pref zz02 from the staff client. 8 Run with -cmd restore -file zz02. Check if local pref zz02 came back. 9 Delete local use pref zz02 again from the staff client. 10 Run with -cmd compare -file zz02 -add. Check if local pref zz02 came back. 11 Change the value of zz02 in the staff client. 12 Run with -cmd compare -file zz02 -add. Check the value: not updated? 13 Run with -cmd compare -file zz02 -upd. Check the value: updated now? 14 Edit file zz02. Add a comment line and delete the line for pref zz02. 15 Run with -cmd compare -file zz02 -del. Is pref zz02 deleted? 16 Add local use preference zz02 in the staff client (WITH explanation). 17 Run with -cmd compare -file zz02 -del. pref zz02 should not be deleted. 18 Run with -cmd compare -file zz02 -del -ign-opt. zz02 should be deleted now. Do the next steps only on a restorable test db: 19 Create file zz03. Leave it empty. Compare with: -cmd compare -file zz03 -del -ign-opt. All prefs gone except Version? 20 Restore with: -cmd restore -file zz01.sav. Compare with -cmd compare -file zz01.sav. Nothing reported? Note: The explanation or options are not recovered. (See also BZ 10199.) This affects local use preferences only. If you need them, restore your test db. Remove the zz files. --- misc/maintenance/cmp_sysprefs.pl | 290 ++++++++++++++++++++++++++++++++++++++ 1 files changed, 290 insertions(+), 0 deletions(-) create mode 100755 misc/maintenance/cmp_sysprefs.pl diff --git a/misc/maintenance/cmp_sysprefs.pl b/misc/maintenance/cmp_sysprefs.pl new file mode 100755 index 0000000..d352d66 --- /dev/null +++ b/misc/maintenance/cmp_sysprefs.pl @@ -0,0 +1,290 @@ +#!/usr/bin/perl + +# Copyright 2013 Rijksmuseum +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + + +# This script imports/exports systempreferences to file. +# Two interesting features are: +# 1) It may help you to compare systempreferences between Koha instances. +# 2) You can also quickly restore subsets of preferences while testing. +# Just leave only e.g. some circulations prefs in a file and compare with +# the update flag. + +use strict; +use warnings; +use open OUT=>':encoding(UTF-8)', ':std'; + +use Getopt::Long; + +use C4::Context; +my $dbh=C4::Context->dbh; + +my ($help, $cmd, $filename, $override,$compare_add,$compare_del,$compare_upd, + $ignore_opt); +GetOptions( + 'help' => \$help, + 'cmd:s' => \$cmd, + 'file:s' => \$filename, + 'add' => \$compare_add, + 'del' => \$compare_del, + 'upd' => \$compare_upd, + 'ign-opt' => \$ignore_opt, +); + +if($filename && !-e $filename && $cmd !~ /^b/) { + die "File $filename not found"; +} +if(!$cmd || $help) { + Usage(); + exit; +} + +#------------------------------------------------------------------------------ + +#backup prefs +if($cmd=~ /^b/i && $filename) { + my $dbprefs=ReadPrefsFromDb(); + open my $fh,'>:encoding(UTF-8)', $filename; + SavePrefsToFile($dbprefs, $fh); + close $fh; +} + +#test pref file: read and save for gaining confidence :) run a diff +if($cmd=~ /^t/i && $filename) { + my $fileprefs=ReadPrefsFromFile($filename); + open my $fh,'>:encoding(UTF-8)', $filename.".sav"; + SavePrefsToFile($fileprefs, $fh); + close $fh; +} + +#compare prefs (with db) +if($cmd=~ /^c/i && $filename) { + my $dbprefs=ReadPrefsFromDb(); + my $fileprefs=ReadPrefsFromFile($filename); + #compare now + my $cmp=ComparePrefs($dbprefs,$fileprefs); + PrintCompare($cmp,"database","file $filename"); + HandleCompareChanges($cmp,$dbprefs,$fileprefs) + if $compare_add || $compare_del || $compare_upd; +} + +#restore prefs +if($cmd=~ /^r/i && $filename) { + my $fileprefs=ReadPrefsFromFile($filename); + CheckVersionPref($fileprefs); + #override this check by removing Version from your file + #if you know what you are doing of course + SavePrefsToDb($fileprefs); +} + +#------------------------------------------------------------------------------ + +sub Usage { + print <<_USAGE_ + +This script may backup, compare and restore system preferences from file. +The following parameters are available: + -help: Print this usage statement + -cmd: Command: backup, compare, restore or test + -file: File name for command + -add: Only for compares: restore preferences not in db + -del: Only for compares: delete preferences not in file + -upd: Only for compares: update preferences when values differ + -ign-opt: Ignore options/explanation when comparing with delete flag + +Precaution: only the last command or file name will be used. The add, del and +upd parameters are extensions for the compare command. They allow you to act +immediately on the compare results. + +When restoring a preferences file containing a version pref to a database having +another version, the restore will not be made. Similarly, a version pref will +never be overwritten. A restore will overwrite prefs but not delete them. + +It is possible to edit the preference backup files. But be careful. The first +parameter for each preference is a line count. Some preference values use more +than one line. If you edit a file, make sure that the line counts are still +valid. + +You can compare/restore using edited/partial preference files. Take special +care when using the del parameter in comparing such a partial file. It will +delete all prefs in the database not found in your partial file. Partial pref +files can however be very useful when testing or monitoring a limited set of +prefs. + +The ign-opt flag allows you to delete preferences that have explanation or +options in the database. If you do not set this flag, a compare with delete +will by default only delete preferences without explanation/options. Use this +option only if you understand the risk. Note that a restore will recover value, +not explanation or options. (See also BZ 10199.) + +_USAGE_ +} + +sub PrintCompare { + my($ch,$s1,$s2)=@_; + foreach(sort keys %$ch) { + my $v=$ch->{$_}; + print "$_: "; + if($v eq '1') { print "Not in $s2"; } + elsif($v eq '2') { print "Not in $s1"; } + else { print "Different values: $v"; } + print "\n"; + } +} + +sub HandleCompareChanges { + my($cmp_pref,$dbpref,$filepref)=@_; + my $t=0; + foreach my $k (sort keys %$cmp_pref) { + my $cmp=$cmp_pref->{$k}; + if( $cmp eq '1' ) { + ++$t && DeleteOnePref($k) if $compare_del; + } + elsif( $cmp eq '2' ) { + my $kwc=$filepref->{$k}->{orgkey}; + my $val=$filepref->{$k}->{value}; + my $type=$filepref->{$k}->{type}; + ++$t && InsertIgnoreOnePref($kwc,$val,$type) if $compare_add; + } + elsif($cmp) { #should contain something.. + my $val=$filepref->{$k}->{value}; + ++$t && UpdateOnePref($k,$val) if $compare_upd; + } + } + print "Adjusted (at most) $t prefs from this compare.\n"; + #at most refers to: insert ignore, update ... where ... + #the number is a maximum, no guarantee +} + +sub ComparePrefs { + my ($ph1, $ph2)=@_; + my $res={}; + foreach my $k (keys %$ph1) { + if(!exists $ph2->{$k}) { + $res->{$k}=1; + } + else { + my $v1=$ph1->{$k}->{value}//'NULL'; + my $v2=$ph2->{$k}->{value}//'NULL'; + if($v1 ne $v2) { + $res->{$k}="$v1 / $v2"; + } + } + } + foreach my $k (keys %$ph2) { + if(!exists $ph1->{$k}) { + $res->{$k}=2; + } + } + return $res; +} + +sub ReadPrefsFromDb { + my $sql="SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable"; + my $hash= $dbh->selectall_hashref($sql,'variable'); + return $hash; +} + +sub ReadPrefsFromFile { + my($file)=@_; + open my $fh,'<:encoding(UTF-8)', $filename; + my @lines=<$fh>; + close $fh; + my $hash; + for(my $t=0; $t<@lines; $t++) { + next if $lines[$t]=~/^\s*#|^\s*$/; # comment line or empty line + my @l= split ",", $lines[$t], 4; + die "Invalid pref file; check line ".++$t if @l<4 || $l[0]!~/^\d+$/ || $t+$l[0]>=@lines; + my $key=lc $l[1]; + $hash->{$key}={ orgkey=>$l[1], value => $l[3], type=> $l[2] }; + for(my $j=0; $j<$l[0]; $j++){$hash->{$key}->{value}.= $lines[$t+$j+1];} + $t=$t+$l[0]; + $hash->{$key}->{value}=~ s/\n$//; #only 'last' line + } + return $hash; +} + +sub SavePrefsToFile { + my ($hash, $fh)=@_; + print $fh '#cmp_sysprefs.pl: '.C4::Context->config('database'). + ', '.localtime."\n"; + foreach my $k (sort keys %$hash) { + #sort handles underscore differently than mysql? + my $c= CountLines($hash->{$k}->{value}); + my $kwc= $hash->{$k}->{orgkey}; # key-with-case + print $fh "$c,$kwc,". + ($hash->{$k}->{type}//'Free').','. + ($hash->{$k}->{value}//'NULL')."\n"; + } +} + +sub SavePrefsToDb { + my ($hash)=@_; + my $t=0; + #will not erase everything! you can do that in mysql :) + foreach my $k (keys %$hash) { + my $v=$hash->{$k}->{value} eq 'NULL'? undef: $hash->{$k}->{value}; + my $kwc=$hash->{$k}->{orgkey}//$k; + my $type=$hash->{$k}->{type}//'Free'; + #insert and update seem overkill, but better than delete and insert + #you cannot assume that the pref IS or IS NOT there + InsertIgnoreOnePref($kwc,$v,$type); + UpdateOnePref($k, $v); + $t++; + } + print "Updated $t prefs\n"; +} + +sub InsertIgnoreOnePref { + my($kwc,$v,$t)=@_; + $dbh->do("INSERT IGNORE INTO systempreferences (variable, value, type) + VALUES (?,?,?)",undef,($kwc,$v,$t)); +} + +sub UpdateOnePref { + my($k,$v)=@_; + return if lc $k eq 'version'; + $dbh->do("UPDATE systempreferences SET value=? WHERE variable=?", + undef,($v,$k)); +} + +sub DeleteOnePref { + my($k)=@_; + return if lc $k eq 'version'; + my $sql="DELETE FROM systempreferences WHERE variable=?"; + unless($ignore_opt) { + $sql.=" AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''"; + } + $dbh->do($sql,undef,($k)); +} + +sub CheckVersionPref { #additional precaution + #if there are versions, compare them + my ($hash)=@_; + my $hv=$hash->{version}->{value}; + return if !defined $hv; + my ($dv)=$dbh->selectrow_array("SELECT value FROM systempreferences + WHERE variable LIKE ?",undef,('version')); + return if !defined $dv; + die "Versions do not match ($dv, $hv)" if $dv ne $hv; +} + +sub CountLines { + my @ma; + return ($_[0] && (@ma=$_[0]=~/\r?\n|\r\n?/g))? scalar @ma: 0; +} -- 1.7.7.6