Attachment #19055 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 || $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 (at most) $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 ) = @_;
    $dbh->do(
        'INSERT IGNORE INTO systempreferences (variable, value, type)
        VALUES (?,?,?)', undef, ( $kwc, $v, $t )
    );
}

sub UpdateOnePref {
    my ( $k, $v ) = @_;
    return if lc $k eq 'version';
    $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) );
        undef,($v,$k));
}

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,'')=''";
    }
    $dbh->do( $sql, undef, ($k) );
}

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 (-161 / +200 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 \|\| $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