View | Details | Raw Unified | Return to bug 9998
Collapse All | Expand All

(-)a/misc/maintenance/cmp_sysprefs.pl (-161 / +200 lines)
Lines 17-23 Link Here
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
19
20
21
# This script imports/exports systempreferences to file.
20
# This script imports/exports systempreferences to file.
22
# Two interesting features are:
21
# Two interesting features are:
23
# 1) It may help you to compare systempreferences between Koha instances.
22
# 1) It may help you to compare systempreferences between Koha instances.
Lines 27-290 Link Here
27
26
28
use strict;
27
use strict;
29
use warnings;
28
use warnings;
30
use open OUT=>':encoding(UTF-8)', ':std';
29
use open OUT => ':encoding(UTF-8)', ':std';
31
30
32
use Getopt::Long;
31
use Getopt::Long;
32
use Pod::Usage;
33
33
34
use C4::Context;
34
use C4::Context;
35
my $dbh=C4::Context->dbh;
35
my $dbh = C4::Context->dbh;
36
36
37
my ($help, $cmd, $filename, $override,$compare_add,$compare_del,$compare_upd,
37
my ( $help, $cmd, $filename, $override, $compare_add, $compare_del, $compare_upd, $ignore_opt );
38
    $ignore_opt);
39
GetOptions(
38
GetOptions(
40
    'help'       => \$help,
39
    'help'    => \$help,
41
    'cmd:s'      => \$cmd,
40
    'cmd:s'   => \$cmd,
42
    'file:s'     => \$filename,
41
    'file:s'  => \$filename,
43
    'add'        => \$compare_add,
42
    'add'     => \$compare_add,
44
    'del'        => \$compare_del,
43
    'del'     => \$compare_del,
45
    'upd'        => \$compare_upd,
44
    'upd'     => \$compare_upd,
46
    'ign-opt'    => \$ignore_opt,
45
    'ign-opt' => \$ignore_opt,
47
);
46
);
48
47
49
if($filename && !-e $filename && $cmd !~ /^b/) {
48
if ( $filename && !-e $filename && $cmd !~ /^b/ ) {
50
    die "File $filename not found";
49
    die "File $filename not found";
51
}
50
}
52
if(!$cmd || $help) {
51
if ( !$cmd || $help ) {
53
    Usage();
52
    pod2usage( -verbose => 2 );
54
    exit;
53
    exit;
55
}
54
}
56
55
57
#------------------------------------------------------------------------------
56
#------------------------------------------------------------------------------
58
57
59
#backup prefs
58
#backup prefs
60
if($cmd=~ /^b/i && $filename) {
59
if ( $cmd =~ /^b/i && $filename ) {
61
    my $dbprefs=ReadPrefsFromDb();
60
    my $dbprefs = ReadPrefsFromDb();
62
    open my $fh,'>:encoding(UTF-8)', $filename;
61
    open my $fh, '>:encoding(UTF-8)', $filename;
63
    SavePrefsToFile($dbprefs, $fh);
62
    SavePrefsToFile( $dbprefs, $fh );
64
    close $fh;
63
    close $fh;
65
}
64
}
66
65
67
#test pref file: read and save for gaining confidence :) run a diff
66
#test pref file: read and save for gaining confidence :) run a diff
68
if($cmd=~ /^t/i && $filename) {
67
if ( $cmd =~ /^t/i && $filename ) {
69
    my $fileprefs=ReadPrefsFromFile($filename);
68
    my $fileprefs = ReadPrefsFromFile($filename);
70
    open my $fh,'>:encoding(UTF-8)', $filename.".sav";
69
    open my $fh, '>:encoding(UTF-8)', $filename . ".sav";
71
    SavePrefsToFile($fileprefs, $fh);
70
    SavePrefsToFile( $fileprefs, $fh );
72
    close $fh;
71
    close $fh;
73
}
72
}
74
73
75
#compare prefs (with db)
74
#compare prefs (with db)
76
if($cmd=~ /^c/i && $filename) {
75
if ( $cmd =~ /^c/i && $filename ) {
77
    my $dbprefs=ReadPrefsFromDb();
76
    my $dbprefs   = ReadPrefsFromDb();
78
    my $fileprefs=ReadPrefsFromFile($filename);
77
    my $fileprefs = ReadPrefsFromFile($filename);
78
79
    #compare now
79
    #compare now
80
    my $cmp=ComparePrefs($dbprefs,$fileprefs);
80
    my $cmp = ComparePrefs( $dbprefs, $fileprefs );
81
    PrintCompare($cmp,"database","file $filename");
81
    PrintCompare( $cmp, "database", "file $filename" );
82
    HandleCompareChanges($cmp,$dbprefs,$fileprefs)
82
    HandleCompareChanges( $cmp, $dbprefs, $fileprefs )
83
        if $compare_add || $compare_del || $compare_upd;
83
      if $compare_add || $compare_del || $compare_upd;
84
}
84
}
85
85
86
#restore prefs
86
#restore prefs
87
if($cmd=~ /^r/i && $filename) {
87
if ( $cmd =~ /^r/i && $filename ) {
88
    my $fileprefs=ReadPrefsFromFile($filename);
88
    my $fileprefs = ReadPrefsFromFile($filename);
89
    CheckVersionPref($fileprefs);
89
    CheckVersionPref($fileprefs);
90
        #override this check by removing Version from your file
90
91
        #if you know what you are doing of course
91
    #override this check by removing Version from your file
92
    #if you know what you are doing of course
92
    SavePrefsToDb($fileprefs);
93
    SavePrefsToDb($fileprefs);
93
}
94
}
94
95
95
#------------------------------------------------------------------------------
96
#------------------------------------------------------------------------------
96
97
97
sub Usage {
98
    print <<_USAGE_
99
100
This script may backup, compare and restore system preferences from file.
101
The following parameters are available:
102
    -help:         Print this usage statement
103
    -cmd:          Command: backup, compare, restore or test
104
    -file:         File name for command
105
    -add:          Only for compares: restore preferences not in db
106
    -del:          Only for compares: delete preferences not in file
107
    -upd:          Only for compares: update preferences when values differ
108
    -ign-opt:      Ignore options/explanation when comparing with delete flag
109
110
Precaution: only the last command or file name will be used. The add, del and
111
upd parameters are extensions for the compare command. They allow you to act
112
immediately on the compare results.
113
114
When restoring a preferences file containing a version pref to a database having
115
another version, the restore will not be made. Similarly, a version pref will
116
never be overwritten. A restore will overwrite prefs but not delete them.
117
118
It is possible to edit the preference backup files. But be careful. The first
119
parameter for each preference is a line count. Some preference values use more
120
than one line. If you edit a file, make sure that the line counts are still
121
valid.
122
123
You can compare/restore using edited/partial preference files. Take special
124
care when using the del parameter in comparing such a partial file. It will
125
delete all prefs in the database not found in your partial file. Partial pref
126
files can however be very useful when testing or monitoring a limited set of
127
prefs.
128
129
The ign-opt flag allows you to delete preferences that have explanation or
130
options in the database. If you do not set this flag, a compare with delete
131
will by default only delete preferences without explanation/options. Use this
132
option only if you understand the risk. Note that a restore will recover value,
133
not explanation or options. (See also BZ 10199.)
134
135
_USAGE_
136
}
137
138
sub PrintCompare {
98
sub PrintCompare {
139
    my($ch,$s1,$s2)=@_;
99
    my ( $ch, $s1, $s2 ) = @_;
140
    foreach(sort keys %$ch) {
100
    foreach ( sort keys %$ch ) {
141
        my $v=$ch->{$_};
101
        my $v = $ch->{$_};
142
        print "$_: ";
102
        print "$_: ";
143
        if($v eq '1') { print "Not in $s2"; }
103
        if    ( $v eq '1' ) { print "Not in $s2"; }
144
        elsif($v eq '2') { print "Not in $s1"; }
104
        elsif ( $v eq '2' ) { print "Not in $s1"; }
145
        else { print "Different values: $v"; }
105
        else                { print "Different values: $v"; }
146
        print "\n";
106
        print "\n";
147
    }
107
    }
148
}
108
}
149
109
150
sub HandleCompareChanges {
110
sub HandleCompareChanges {
151
    my($cmp_pref,$dbpref,$filepref)=@_;
111
    my ( $cmp_pref, $dbpref, $filepref ) = @_;
152
    my $t=0;
112
    my $t = 0;
153
    foreach my $k (sort keys %$cmp_pref) {
113
    foreach my $k ( sort keys %$cmp_pref ) {
154
        my $cmp=$cmp_pref->{$k};
114
        my $cmp = $cmp_pref->{$k};
155
        if( $cmp eq '1' ) {
115
        if ( $cmp eq '1' ) {
156
            ++$t && DeleteOnePref($k) if $compare_del;
116
            ++$t && DeleteOnePref($k) if $compare_del;
157
        }
117
        } elsif ( $cmp eq '2' ) {
158
        elsif( $cmp eq '2' ) {
118
            my $kwc  = $filepref->{$k}->{orgkey};
159
            my $kwc=$filepref->{$k}->{orgkey};
119
            my $val  = $filepref->{$k}->{value};
160
            my $val=$filepref->{$k}->{value};
120
            my $type = $filepref->{$k}->{type};
161
            my $type=$filepref->{$k}->{type};
121
            ++$t && InsertIgnoreOnePref( $kwc, $val, $type ) if $compare_add;
162
            ++$t && InsertIgnoreOnePref($kwc,$val,$type) if $compare_add;
122
        } elsif ($cmp) {    #should contain something..
163
        }
123
            my $val = $filepref->{$k}->{value};
164
        elsif($cmp) { #should contain something..
124
            ++$t && UpdateOnePref( $k, $val ) if $compare_upd;
165
            my $val=$filepref->{$k}->{value};
166
            ++$t && UpdateOnePref($k,$val) if $compare_upd;
167
        }
125
        }
168
    }
126
    }
169
    print "Adjusted (at most) $t prefs from this compare.\n";
127
    print "Adjusted (at most) $t prefs from this compare.\n";
128
170
    #at most refers to: insert ignore, update ... where ...
129
    #at most refers to: insert ignore, update ... where ...
171
    #the number is a maximum, no guarantee
130
    #the number is a maximum, no guarantee
172
}
131
}
173
132
174
sub ComparePrefs {
133
sub ComparePrefs {
175
    my ($ph1, $ph2)=@_;
134
    my ( $ph1, $ph2 ) = @_;
176
    my $res={};
135
    my $res = {};
177
    foreach my $k (keys %$ph1) {
136
    foreach my $k ( keys %$ph1 ) {
178
        if(!exists $ph2->{$k}) {
137
        if ( !exists $ph2->{$k} ) {
179
            $res->{$k}=1;
138
            $res->{$k} = 1;
180
        }
139
        } else {
181
        else {
140
            my $v1 = $ph1->{$k}->{value} // 'NULL';
182
            my $v1=$ph1->{$k}->{value}//'NULL';
141
            my $v2 = $ph2->{$k}->{value} // 'NULL';
183
            my $v2=$ph2->{$k}->{value}//'NULL';
142
            if ( $v1 ne $v2 ) {
184
            if($v1 ne $v2) {
143
                $res->{$k} = "$v1 / $v2";
185
                $res->{$k}="$v1 / $v2";
186
            }
144
            }
187
        }
145
        }
188
    }
146
    }
189
    foreach my $k (keys %$ph2) {
147
    foreach my $k ( keys %$ph2 ) {
190
        if(!exists $ph1->{$k}) {
148
        if ( !exists $ph1->{$k} ) {
191
            $res->{$k}=2;
149
            $res->{$k} = 2;
192
        }
150
        }
193
    }
151
    }
194
    return $res;
152
    return $res;
195
}
153
}
196
154
197
sub ReadPrefsFromDb {
155
sub ReadPrefsFromDb {
198
    my $sql="SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable";
156
    my $sql = 'SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable';
199
    my $hash= $dbh->selectall_hashref($sql,'variable');
157
    my $hash = $dbh->selectall_hashref( $sql, 'variable' );
200
    return $hash;
158
    return $hash;
201
}
159
}
202
160
203
sub ReadPrefsFromFile {
161
sub ReadPrefsFromFile {
204
    my($file)=@_;
162
    my ($file) = @_;
205
    open my $fh,'<:encoding(UTF-8)', $filename;
163
    open my $fh, '<:encoding(UTF-8)', $filename;
206
    my @lines=<$fh>;
164
    my @lines = <$fh>;
207
    close $fh;
165
    close $fh;
208
    my $hash;
166
    my $hash;
209
    for(my $t=0; $t<@lines; $t++) {
167
    for ( my $t = 0 ; $t < @lines ; $t++ ) {
210
        next if $lines[$t]=~/^\s*#|^\s*$/; # comment line or empty line
168
        next if $lines[$t] =~ /^\s*#|^\s*$/;    # comment line or empty line
211
        my @l= split ",", $lines[$t], 4;
169
        my @l = split ",", $lines[$t], 4;
212
        die "Invalid pref file; check line ".++$t if @l<4 || $l[0]!~/^\d+$/ || $t+$l[0]>=@lines;
170
        die "Invalid pref file; check line " . ++$t if @l < 4 || $l[0] !~ /^\d+$/ || $t + $l[0] >= @lines;
213
        my $key=lc $l[1];
171
        my $key = lc $l[1];
214
        $hash->{$key}={ orgkey=>$l[1], value => $l[3], type=> $l[2] };
172
        $hash->{$key} = { orgkey => $l[1], value => $l[3], type => $l[2] };
215
        for(my $j=0; $j<$l[0]; $j++){$hash->{$key}->{value}.= $lines[$t+$j+1];}
173
        for ( my $j = 0 ; $j < $l[0] ; $j++ ) { $hash->{$key}->{value} .= $lines[ $t + $j + 1 ]; }
216
        $t=$t+$l[0];
174
        $t = $t + $l[0];
217
        $hash->{$key}->{value}=~ s/\n$//; #only 'last' line
175
        $hash->{$key}->{value} =~ s/\n$//;      #only 'last' line
218
    }
176
    }
219
    return $hash;
177
    return $hash;
220
}
178
}
221
179
222
sub SavePrefsToFile {
180
sub SavePrefsToFile {
223
    my ($hash, $fh)=@_;
181
    my ( $hash, $fh ) = @_;
224
    print $fh '#cmp_sysprefs.pl: '.C4::Context->config('database').
182
    print $fh '#cmp_sysprefs.pl: ' . C4::Context->config('database') . ', ' . localtime . "\n";
225
        ', '.localtime."\n";
183
    foreach my $k ( sort keys %$hash ) {
226
    foreach my $k (sort keys %$hash) {
184
227
    #sort handles underscore differently than mysql?
185
        #sort handles underscore differently than mysql?
228
        my $c= CountLines($hash->{$k}->{value});
186
        my $c   = CountLines( $hash->{$k}->{value} );
229
        my $kwc= $hash->{$k}->{orgkey}; # key-with-case
187
        my $kwc = $hash->{$k}->{orgkey};                # key-with-case
230
        print $fh "$c,$kwc,".
188
        print $fh "$c,$kwc," . ( $hash->{$k}->{type} // 'Free' ) . ',' . ( $hash->{$k}->{value} // 'NULL' ) . "\n";
231
            ($hash->{$k}->{type}//'Free').','.
232
            ($hash->{$k}->{value}//'NULL')."\n";
233
    }
189
    }
234
}
190
}
235
191
236
sub SavePrefsToDb {
192
sub SavePrefsToDb {
237
    my ($hash)=@_;
193
    my ($hash) = @_;
238
    my $t=0;
194
    my $t = 0;
195
239
    #will not erase everything! you can do that in mysql :)
196
    #will not erase everything! you can do that in mysql :)
240
    foreach my $k (keys %$hash) {
197
    foreach my $k ( keys %$hash ) {
241
        my $v=$hash->{$k}->{value} eq 'NULL'? undef: $hash->{$k}->{value};
198
        my $v = $hash->{$k}->{value} eq 'NULL' ? undef : $hash->{$k}->{value};
242
        my $kwc=$hash->{$k}->{orgkey}//$k;
199
        my $kwc  = $hash->{$k}->{orgkey} // $k;
243
        my $type=$hash->{$k}->{type}//'Free';
200
        my $type = $hash->{$k}->{type}   // 'Free';
201
244
        #insert and update seem overkill, but better than delete and insert
202
        #insert and update seem overkill, but better than delete and insert
245
        #you cannot assume that the pref IS or IS NOT there
203
        #you cannot assume that the pref IS or IS NOT there
246
        InsertIgnoreOnePref($kwc,$v,$type);
204
        InsertIgnoreOnePref( $kwc, $v, $type );
247
        UpdateOnePref($k, $v);
205
        UpdateOnePref( $k, $v );
248
        $t++;
206
        $t++;
249
    }
207
    }
250
    print "Updated $t prefs\n";
208
    print "Updated $t prefs\n";
251
}
209
}
252
210
253
sub InsertIgnoreOnePref {
211
sub InsertIgnoreOnePref {
254
    my($kwc,$v,$t)=@_;
212
    my ( $kwc, $v, $t ) = @_;
255
    $dbh->do("INSERT IGNORE INTO systempreferences (variable, value, type)
213
    $dbh->do(
256
        VALUES (?,?,?)",undef,($kwc,$v,$t));
214
        'INSERT IGNORE INTO systempreferences (variable, value, type)
215
        VALUES (?,?,?)', undef, ( $kwc, $v, $t )
216
    );
257
}
217
}
258
218
259
sub UpdateOnePref {
219
sub UpdateOnePref {
260
    my($k,$v)=@_;
220
    my ( $k, $v ) = @_;
261
    return if lc $k eq 'version';
221
    return if lc $k eq 'version';
262
    $dbh->do("UPDATE systempreferences SET value=? WHERE variable=?",
222
    $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) );
263
        undef,($v,$k));
264
}
223
}
265
224
266
sub DeleteOnePref {
225
sub DeleteOnePref {
267
    my($k)=@_;
226
    my ($k) = @_;
268
    return if lc $k eq 'version';
227
    return if lc $k eq 'version';
269
    my $sql="DELETE FROM systempreferences WHERE variable=?";
228
    my $sql = 'DELETE FROM systempreferences WHERE variable=?';
270
    unless($ignore_opt) {
229
    unless ($ignore_opt) {
271
        $sql.=" AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";
230
        $sql .= " AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";
272
    }
231
    }
273
    $dbh->do($sql,undef,($k));
232
    $dbh->do( $sql, undef, ($k) );
274
}
233
}
275
234
276
sub CheckVersionPref { #additional precaution
235
sub CheckVersionPref {    #additional precaution
277
                       #if there are versions, compare them
236
                          #if there are versions, compare them
278
    my ($hash)=@_;
237
    my ($hash) = @_;
279
    my $hv=$hash->{version}->{value};
238
    my $hv = $hash->{version}->{value};
280
    return if !defined $hv;
239
    return if !defined $hv;
281
    my ($dv)=$dbh->selectrow_array("SELECT value FROM systempreferences
240
    my ($dv) = $dbh->selectrow_array(
282
        WHERE variable LIKE ?",undef,('version'));
241
        'SELECT value FROM systempreferences
242
        WHERE variable LIKE ?', undef, ('version')
243
    );
283
    return if !defined $dv;
244
    return if !defined $dv;
284
    die "Versions do not match ($dv, $hv)" if $dv ne $hv;
245
    die "Versions do not match ($dv, $hv)" if $dv ne $hv;
285
}
246
}
286
247
287
sub CountLines {
248
sub CountLines {
288
    my @ma;
249
    my @ma;
289
    return ($_[0] && (@ma=$_[0]=~/\r?\n|\r\n?/g))? scalar @ma: 0;
250
    return ( $_[0] && ( @ma = $_[0] =~ /\r?\n|\r\n?/g ) ) ? scalar @ma : 0;
290
}
251
}
291
- 
252
253
=head1 NAME
254
255
cmp_sysprefs.pl
256
257
=head1 SYNOPSIS
258
259
cmp_sysprefs.pl -help
260
261
cmp_sysprefs.pl -cmd backup -file prefbackup
262
263
cmp_sysprefs.pl -cmd compare -file prefbackup -upd
264
265
cmp_sysprefs.pl -cmd compare -file prefbackup -del -ign-opt
266
267
cmp_sysprefs.pl -cmd restore -file prefbackup
268
269
=head1 DESCRIPTION
270
271
This script may backup, compare and restore system preferences from file.
272
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.
276
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.
280
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
284
valid.
285
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
290
prefs.
291
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.)
297
298
=over 8
299
300
=item B<-help>
301
302
Print this usage statement.
303
304
=item B<-cmd>
305
306
Command: backup, compare, restore or test.
307
308
=item B<-file>
309
310
Name of the file used in command.
311
312
=item B<-add>
313
314
Only for compares: restore preferences not present in database.
315
316
=item B<-del>
317
318
Only for compares: delete preferences not present in file.
319
320
=item B<-upd>
321
322
Only for compares: update preferences when values differ.
323
324
=item B<-ign-opt>
325
326
Ignore options/explanation when comparing with delete flag. Use this flag with care.
327
328
=back
329
330
=cut

Return to bug 9998