Attachment #19070 for bug #9998

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




# 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.


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 );
    $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 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;
            my $val=$filepref->{$k}->{value};
            ++$t && UpdateOnePref($k,$val) if $compare_upd;
        }
    }
    print "Adjusted $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";
                $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";
            ($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

Return to bug 9998

Lines 17-23 Link Here

(-)a/misc/maintenance/cmp_sysprefs.pl (-165 / +204 lines)
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