3 # Copyright 2013 Rijksmuseum
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
20 # This script imports/exports systempreferences to file.
21 # Two interesting features are:
22 # 1) It may help you to compare systempreferences between Koha instances.
23 # 2) You can also quickly restore subsets of preferences while testing.
24 # Just leave only e.g. some circulations prefs in a file and compare with
29 use open OUT => ':encoding(UTF-8)', ':std';
35 my $dbh = C4::Context->dbh;
37 my ( $help, $cmd, $filename, $override, $compare_add, $compare_del, $compare_upd, $ignore_opt );
41 'file:s' => \$filename,
42 'add' => \$compare_add,
43 'del' => \$compare_del,
44 'upd' => \$compare_upd,
45 'ign-opt' => \$ignore_opt,
48 if ( $filename && !-e $filename && $cmd !~ /^b/ ) {
49 die "File $filename not found";
51 if ( !$cmd || !$filename || $help ) {
52 pod2usage( -verbose => 2 );
56 #------------------------------------------------------------------------------
59 if ( $cmd =~ /^b/i && $filename ) {
60 my $dbprefs = ReadPrefsFromDb();
61 open my $fh, '>:encoding(UTF-8)', $filename;
62 SavePrefsToFile( $dbprefs, $fh );
66 #test pref file: read and save for gaining confidence :) run a diff
67 if ( $cmd =~ /^t/i && $filename ) {
68 my $fileprefs = ReadPrefsFromFile($filename);
69 open my $fh, '>:encoding(UTF-8)', $filename . ".sav";
70 SavePrefsToFile( $fileprefs, $fh );
74 #compare prefs (with db)
75 if ( $cmd =~ /^c/i && $filename ) {
76 my $dbprefs = ReadPrefsFromDb();
77 my $fileprefs = ReadPrefsFromFile($filename);
80 my $cmp = ComparePrefs( $dbprefs, $fileprefs );
81 PrintCompare( $cmp, "database", "file $filename" );
82 HandleCompareChanges( $cmp, $dbprefs, $fileprefs )
83 if $compare_add || $compare_del || $compare_upd;
87 if ( $cmd =~ /^r/i && $filename ) {
88 my $fileprefs = ReadPrefsFromFile($filename);
89 CheckVersionPref($fileprefs);
91 #override this check by removing Version from your file
92 #if you know what you are doing of course
93 SavePrefsToDb($fileprefs);
96 #------------------------------------------------------------------------------
99 my ( $ch, $s1, $s2 ) = @_;
100 foreach ( sort keys %$ch ) {
103 if ( $v eq '1' ) { print "Not in $s2"; }
104 elsif ( $v eq '2' ) { print "Not in $s1"; }
105 else { print "Different values: $v"; }
110 sub HandleCompareChanges {
111 my ( $cmp_pref, $dbpref, $filepref ) = @_;
113 foreach my $k ( sort keys %$cmp_pref ) {
114 my $cmp = $cmp_pref->{$k};
116 $t += DeleteOnePref($k) if $compare_del;
117 } elsif ( $cmp eq '2' ) {
118 my $kwc = $filepref->{$k}->{orgkey};
119 my $val = $filepref->{$k}->{value};
120 my $type = $filepref->{$k}->{type};
121 $t += InsertIgnoreOnePref( $kwc, $val, $type ) if $compare_add;
122 } elsif ($cmp) { #should contain something..
123 my $val = $filepref->{$k}->{value};
124 $t += UpdateOnePref( $k, $val ) if $compare_upd;
127 print "Adjusted $t prefs from this compare.\n";
131 my ( $ph1, $ph2 ) = @_;
133 foreach my $k ( keys %$ph1 ) {
134 if ( !exists $ph2->{$k} ) {
137 my $v1 = $ph1->{$k}->{value} // 'NULL';
138 my $v2 = $ph2->{$k}->{value} // 'NULL';
140 $res->{$k} = "$v1 / $v2";
144 foreach my $k ( keys %$ph2 ) {
145 if ( !exists $ph1->{$k} ) {
152 sub ReadPrefsFromDb {
153 my $sql = 'SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable';
154 my $hash = $dbh->selectall_hashref( $sql, 'variable' );
158 sub ReadPrefsFromFile {
160 open my $fh, '<:encoding(UTF-8)', $filename;
164 for ( my $t = 0 ; $t < @lines ; $t++ ) {
165 next if $lines[$t] =~ /^\s*#|^\s*$/; # comment line or empty line
166 my @l = split ",", $lines[$t], 4;
167 die "Invalid pref file; check line " . ++$t if @l < 4 || $l[0] !~ /^\d+$/ || $t + $l[0] >= @lines;
169 $hash->{$key} = { orgkey => $l[1], value => $l[3], type => $l[2] };
170 for ( my $j = 0 ; $j < $l[0] ; $j++ ) { $hash->{$key}->{value} .= $lines[ $t + $j + 1 ]; }
172 $hash->{$key}->{value} =~ s/\n$//; #only 'last' line
177 sub SavePrefsToFile {
178 my ( $hash, $fh ) = @_;
179 print $fh '#cmp_sysprefs.pl: ' . C4::Context->config('database') . ', ' . localtime . "\n";
180 foreach my $k ( sort keys %$hash ) {
182 #sort handles underscore differently than mysql?
183 my $c = CountLines( $hash->{$k}->{value} );
184 my $kwc = $hash->{$k}->{orgkey}; # key-with-case
185 print $fh "$c,$kwc," . ( $hash->{$k}->{type} // 'Free' ) . ',' . ( $hash->{$k}->{value} // 'NULL' ) . "\n";
193 #will not erase everything! you can do that in mysql :)
194 foreach my $k ( keys %$hash ) {
195 my $v = $hash->{$k}->{value} eq 'NULL' ? undef : $hash->{$k}->{value};
196 my $kwc = $hash->{$k}->{orgkey} // $k;
197 my $type = $hash->{$k}->{type} // 'Free';
199 #insert and update seem overkill, but better than delete and insert
200 #you cannot assume that the pref IS or IS NOT there
201 InsertIgnoreOnePref( $kwc, $v, $type );
202 UpdateOnePref( $k, $v );
205 print "Updated $t prefs\n";
208 sub InsertIgnoreOnePref {
209 my ( $kwc, $v, $t ) = @_;
211 'INSERT IGNORE INTO systempreferences (variable, value, type)
212 VALUES (?,?,?)', undef, ( $kwc, $v, $t )
214 return !defined($i) || $i eq '0E0'? 0: $i;
219 return if lc $k eq 'version';
220 my $i = $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) );
221 return !defined($i) || $i eq '0E0'? 0: $i;
226 return if lc $k eq 'version';
227 my $sql = 'DELETE FROM systempreferences WHERE variable=?';
228 unless ($ignore_opt) {
229 $sql .= " AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";
231 my $i = $dbh->do( $sql, undef, ($k) );
232 return !defined($i) || $i eq '0E0'? 0: $i;
235 sub CheckVersionPref { #additional precaution
236 #if there are versions, compare them
238 my $hv = $hash->{version}->{value};
239 return if !defined $hv;
240 my ($dv) = $dbh->selectrow_array(
241 'SELECT value FROM systempreferences
242 WHERE variable LIKE ?', undef, ('version')
244 return if !defined $dv;
245 die "Versions do not match ($dv, $hv)" if $dv ne $hv;
250 return ( $_[0] && ( @ma = $_[0] =~ /\r?\n|\r\n?/g ) ) ? scalar @ma : 0;
259 cmp_sysprefs.pl -help
261 cmp_sysprefs.pl -cmd backup -file prefbackup
263 cmp_sysprefs.pl -cmd compare -file prefbackup -upd
265 cmp_sysprefs.pl -cmd compare -file prefbackup -del -ign-opt
267 cmp_sysprefs.pl -cmd restore -file prefbackup
271 This script may backup, compare and restore system preferences from file.
273 Precaution: only the last command or file name will be used. The add, del and
274 upd parameters are extensions for the compare command. They allow you to act
275 immediately on the compare results.
277 When restoring a preferences file containing a version pref to a database having
278 another version, the restore will not be made. Similarly, a version pref will
279 never be overwritten. A restore will overwrite prefs but not delete them.
281 It is possible to edit the preference backup files. But be careful. The first
282 parameter for each preference is a line count. Some preference values use more
283 than one line. If you edit a file, make sure that the line counts are still
286 You can compare/restore using edited/partial preference files. Take special
287 care when using the del parameter in comparing such a partial file. It will
288 delete all prefs in the database not found in your partial file. Partial pref
289 files can however be very useful when testing or monitoring a limited set of
292 The ign-opt flag allows you to delete preferences that have explanation or
293 options in the database. If you do not set this flag, a compare with delete
294 will by default only delete preferences without explanation/options. Use this
295 option only if you understand the risk. Note that a restore will recover value,
296 not explanation or options. (See also BZ 10199.)
302 Print this usage statement.
306 Command: backup, compare, restore or test.
310 Name of the file used in command.
314 Only for compares: restore preferences not present in database.
318 Only for compares: delete preferences not present in file.
322 Only for compares: update preferences when values differ.
326 Ignore options/explanation when comparing with delete flag. Use this flag with care.