From aa4fde7d0d6dee14e3715f3063f16ee4f623b936 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 8 May 2013 16:29:23 +0200 Subject: [PATCH] Bug 9998: new tool to import/export and compare system preferences 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. Signed-off-by: Kyle M Hall Amended patch Only cosmetic stuff: Perltidied with xt/perltidyrc. Replaced most double quotes by single quotes around SQL statements. Moved the usage into POD for pod2usage. Passing no file shows help screen too. Counting the db updates more accurately with return value of do. Signed-off-by: Marcel de Rooy Signed-off-by: Jonathan Druart Signed-off-by: Galen Charlton --- misc/maintenance/cmp_sysprefs.pl | 330 +++++++++++++++++++++++++++++++ 1 file changed, 330 insertions(+) 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 0000000000..19e7126ebd --- /dev/null +++ b/misc/maintenance/cmp_sysprefs.pl @@ -0,0 +1,330 @@ +#!/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 Pod::Usage; + +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 || !$filename || $help ) { + pod2usage( -verbose => 2 ); + 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 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 $t prefs from this compare.\n"; +} + +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 ) = @_; + my $i = $dbh->do( + 'INSERT IGNORE INTO systempreferences (variable, value, type) + VALUES (?,?,?)', undef, ( $kwc, $v, $t ) + ); + return !defined($i) || $i eq '0E0'? 0: $i; +} + +sub UpdateOnePref { + my ( $k, $v ) = @_; + return if lc $k eq 'version'; + my $i = $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) ); + return !defined($i) || $i eq '0E0'? 0: $i; +} + +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,'')=''"; + } + my $i = $dbh->do( $sql, undef, ($k) ); + return !defined($i) || $i eq '0E0'? 0: $i; +} + +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; +} + +=head1 NAME + +cmp_sysprefs.pl + +=head1 SYNOPSIS + +cmp_sysprefs.pl -help + +cmp_sysprefs.pl -cmd backup -file prefbackup + +cmp_sysprefs.pl -cmd compare -file prefbackup -upd + +cmp_sysprefs.pl -cmd compare -file prefbackup -del -ign-opt + +cmp_sysprefs.pl -cmd restore -file prefbackup + +=head1 DESCRIPTION + +This script may backup, compare and restore system preferences from file. + +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.) + +=over 8 + +=item B<-help> + +Print this usage statement. + +=item B<-cmd> + +Command: backup, compare, restore or test. + +=item B<-file> + +Name of the file used in command. + +=item B<-add> + +Only for compares: restore preferences not present in database. + +=item B<-del> + +Only for compares: delete preferences not present in file. + +=item B<-upd> + +Only for compares: update preferences when values differ. + +=item B<-ign-opt> + +Ignore options/explanation when comparing with delete flag. Use this flag with care. + +=back + +=cut -- 2.39.5