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

(-)a/misc/maintenance/cmp_sysprefs.pl (-165 / +204 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 || !$filename || $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 $t prefs from this compare.\n";
170
    #at most refers to: insert ignore, update ... where ...
171
    #the number is a maximum, no guarantee
172
}
128
}
173
129
174
sub ComparePrefs {
130
sub ComparePrefs {
175
    my ($ph1, $ph2)=@_;
131
    my ( $ph1, $ph2 ) = @_;
176
    my $res={};
132
    my $res = {};
177
    foreach my $k (keys %$ph1) {
133
    foreach my $k ( keys %$ph1 ) {
178
        if(!exists $ph2->{$k}) {
134
        if ( !exists $ph2->{$k} ) {
179
            $res->{$k}=1;
135
            $res->{$k} = 1;
180
        }
136
        } else {
181
        else {
137
            my $v1 = $ph1->{$k}->{value} // 'NULL';
182
            my $v1=$ph1->{$k}->{value}//'NULL';
138
            my $v2 = $ph2->{$k}->{value} // 'NULL';
183
            my $v2=$ph2->{$k}->{value}//'NULL';
139
            if ( $v1 ne $v2 ) {
184
            if($v1 ne $v2) {
140
                $res->{$k} = "$v1 / $v2";
185
                $res->{$k}="$v1 / $v2";
186
            }
141
            }
187
        }
142
        }
188
    }
143
    }
189
    foreach my $k (keys %$ph2) {
144
    foreach my $k ( keys %$ph2 ) {
190
        if(!exists $ph1->{$k}) {
145
        if ( !exists $ph1->{$k} ) {
191
            $res->{$k}=2;
146
            $res->{$k} = 2;
192
        }
147
        }
193
    }
148
    }
194
    return $res;
149
    return $res;
195
}
150
}
196
151
197
sub ReadPrefsFromDb {
152
sub ReadPrefsFromDb {
198
    my $sql="SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable";
153
    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');
154
    my $hash = $dbh->selectall_hashref( $sql, 'variable' );
200
    return $hash;
155
    return $hash;
201
}
156
}
202
157
203
sub ReadPrefsFromFile {
158
sub ReadPrefsFromFile {
204
    my($file)=@_;
159
    my ($file) = @_;
205
    open my $fh,'<:encoding(UTF-8)', $filename;
160
    open my $fh, '<:encoding(UTF-8)', $filename;
206
    my @lines=<$fh>;
161
    my @lines = <$fh>;
207
    close $fh;
162
    close $fh;
208
    my $hash;
163
    my $hash;
209
    for(my $t=0; $t<@lines; $t++) {
164
    for ( my $t = 0 ; $t < @lines ; $t++ ) {
210
        next if $lines[$t]=~/^\s*#|^\s*$/; # comment line or empty line
165
        next if $lines[$t] =~ /^\s*#|^\s*$/;    # comment line or empty line
211
        my @l= split ",", $lines[$t], 4;
166
        my @l = split ",", $lines[$t], 4;
212
        die "Invalid pref file; check line ".++$t if @l<4 || $l[0]!~/^\d+$/ || $t+$l[0]>=@lines;
167
        die "Invalid pref file; check line " . ++$t if @l < 4 || $l[0] !~ /^\d+$/ || $t + $l[0] >= @lines;
213
        my $key=lc $l[1];
168
        my $key = lc $l[1];
214
        $hash->{$key}={ orgkey=>$l[1], value => $l[3], type=> $l[2] };
169
        $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];}
170
        for ( my $j = 0 ; $j < $l[0] ; $j++ ) { $hash->{$key}->{value} .= $lines[ $t + $j + 1 ]; }
216
        $t=$t+$l[0];
171
        $t = $t + $l[0];
217
        $hash->{$key}->{value}=~ s/\n$//; #only 'last' line
172
        $hash->{$key}->{value} =~ s/\n$//;      #only 'last' line
218
    }
173
    }
219
    return $hash;
174
    return $hash;
220
}
175
}
221
176
222
sub SavePrefsToFile {
177
sub SavePrefsToFile {
223
    my ($hash, $fh)=@_;
178
    my ( $hash, $fh ) = @_;
224
    print $fh '#cmp_sysprefs.pl: '.C4::Context->config('database').
179
    print $fh '#cmp_sysprefs.pl: ' . C4::Context->config('database') . ', ' . localtime . "\n";
225
        ', '.localtime."\n";
180
    foreach my $k ( sort keys %$hash ) {
226
    foreach my $k (sort keys %$hash) {
181
227
    #sort handles underscore differently than mysql?
182
        #sort handles underscore differently than mysql?
228
        my $c= CountLines($hash->{$k}->{value});
183
        my $c   = CountLines( $hash->{$k}->{value} );
229
        my $kwc= $hash->{$k}->{orgkey}; # key-with-case
184
        my $kwc = $hash->{$k}->{orgkey};                # key-with-case
230
        print $fh "$c,$kwc,".
185
        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
    }
186
    }
234
}
187
}
235
188
236
sub SavePrefsToDb {
189
sub SavePrefsToDb {
237
    my ($hash)=@_;
190
    my ($hash) = @_;
238
    my $t=0;
191
    my $t = 0;
192
239
    #will not erase everything! you can do that in mysql :)
193
    #will not erase everything! you can do that in mysql :)
240
    foreach my $k (keys %$hash) {
194
    foreach my $k ( keys %$hash ) {
241
        my $v=$hash->{$k}->{value} eq 'NULL'? undef: $hash->{$k}->{value};
195
        my $v = $hash->{$k}->{value} eq 'NULL' ? undef : $hash->{$k}->{value};
242
        my $kwc=$hash->{$k}->{orgkey}//$k;
196
        my $kwc  = $hash->{$k}->{orgkey} // $k;
243
        my $type=$hash->{$k}->{type}//'Free';
197
        my $type = $hash->{$k}->{type}   // 'Free';
198
244
        #insert and update seem overkill, but better than delete and insert
199
        #insert and update seem overkill, but better than delete and insert
245
        #you cannot assume that the pref IS or IS NOT there
200
        #you cannot assume that the pref IS or IS NOT there
246
        InsertIgnoreOnePref($kwc,$v,$type);
201
        InsertIgnoreOnePref( $kwc, $v, $type );
247
        UpdateOnePref($k, $v);
202
        UpdateOnePref( $k, $v );
248
        $t++;
203
        $t++;
249
    }
204
    }
250
    print "Updated $t prefs\n";
205
    print "Updated $t prefs\n";
251
}
206
}
252
207
253
sub InsertIgnoreOnePref {
208
sub InsertIgnoreOnePref {
254
    my($kwc,$v,$t)=@_;
209
    my ( $kwc, $v, $t ) = @_;
255
    $dbh->do("INSERT IGNORE INTO systempreferences (variable, value, type)
210
    my $i = $dbh->do(
256
        VALUES (?,?,?)",undef,($kwc,$v,$t));
211
        'INSERT IGNORE INTO systempreferences (variable, value, type)
212
        VALUES (?,?,?)', undef, ( $kwc, $v, $t )
213
    );
214
    return !defined($i) || $i eq '0E0'? 0: $i;
257
}
215
}
258
216
259
sub UpdateOnePref {
217
sub UpdateOnePref {
260
    my($k,$v)=@_;
218
    my ( $k, $v ) = @_;
261
    return if lc $k eq 'version';
219
    return if lc $k eq 'version';
262
    $dbh->do("UPDATE systempreferences SET value=? WHERE variable=?",
220
    my $i = $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) );
263
        undef,($v,$k));
221
    return !defined($i) || $i eq '0E0'? 0: $i;
264
}
222
}
265
223
266
sub DeleteOnePref {
224
sub DeleteOnePref {
267
    my($k)=@_;
225
    my ($k) = @_;
268
    return if lc $k eq 'version';
226
    return if lc $k eq 'version';
269
    my $sql="DELETE FROM systempreferences WHERE variable=?";
227
    my $sql = 'DELETE FROM systempreferences WHERE variable=?';
270
    unless($ignore_opt) {
228
    unless ($ignore_opt) {
271
        $sql.=" AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";
229
        $sql .= " AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";
272
    }
230
    }
273
    $dbh->do($sql,undef,($k));
231
    my $i = $dbh->do( $sql, undef, ($k) );
232
    return !defined($i) || $i eq '0E0'? 0: $i;
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