| 
      
            Line 0
          
      
      
        Link Here
      
     | 
  
          
            
              | 0 | 
              -   | 
              1 | 
              #!/usr/bin/perl  | 
            
            
               | 
               | 
              2 | 
               | 
            
            
              | 3 | 
              # Copyright 2013 Rijksmuseum  | 
            
            
              | 4 | 
              #  | 
            
            
              | 5 | 
              # This file is part of Koha.  | 
            
            
              | 6 | 
              #  | 
            
            
              | 7 | 
              # Koha is free software; you can redistribute it and/or modify it under the  | 
            
            
              | 8 | 
              # terms of the GNU General Public License as published by the Free Software  | 
            
            
              | 9 | 
              # Foundation; either version 2 of the License, or (at your option) any later  | 
            
            
              | 10 | 
              # version.  | 
            
            
              | 11 | 
              #  | 
            
            
              | 12 | 
              # Koha is distributed in the hope that it will be useful, but WITHOUT ANY  | 
            
            
              | 13 | 
              # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR  | 
            
            
              | 14 | 
              # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.  | 
            
            
              | 15 | 
              #  | 
            
            
              | 16 | 
              # You should have received a copy of the GNU General Public License along  | 
            
            
              | 17 | 
              # with Koha; if not, write to the Free Software Foundation, Inc.,  | 
            
            
              | 18 | 
              # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.  | 
            
            
              | 19 | 
               | 
            
            
              | 20 | 
              # This script imports/exports systempreferences to file.  | 
            
            
              | 21 | 
              # Two interesting features are:  | 
            
            
              | 22 | 
              # 1) It may help you to compare systempreferences between Koha instances.  | 
            
            
              | 23 | 
              # 2) You can also quickly restore subsets of preferences while testing.  | 
            
            
              | 24 | 
              #    Just leave only e.g. some circulations prefs in a file and compare with  | 
            
            
              | 25 | 
              #    the update flag.  | 
            
            
              | 26 | 
               | 
            
            
              | 27 | 
              use strict;  | 
            
            
              | 28 | 
              use warnings;  | 
            
            
              | 29 | 
              use open OUT => ':encoding(UTF-8)', ':std';  | 
            
            
              | 30 | 
               | 
            
            
              | 31 | 
              use Getopt::Long;  | 
            
            
              | 32 | 
              use Pod::Usage;  | 
            
            
              | 33 | 
               | 
            
            
              | 34 | 
              use C4::Context;  | 
            
            
              | 35 | 
              my $dbh = C4::Context->dbh;  | 
            
            
              | 36 | 
               | 
            
            
              | 37 | 
              my ( $help, $cmd, $filename, $override, $compare_add, $compare_del, $compare_upd, $ignore_opt );  | 
            
            
              | 38 | 
              GetOptions(  | 
            
            
              | 39 | 
                  'help'    => \$help,  | 
            
            
              | 40 | 
                  'cmd:s'   => \$cmd,  | 
            
            
              | 41 | 
                  'file:s'  => \$filename,  | 
            
            
              | 42 | 
                  'add'     => \$compare_add,  | 
            
            
              | 43 | 
                  'del'     => \$compare_del,  | 
            
            
              | 44 | 
                  'upd'     => \$compare_upd,  | 
            
            
              | 45 | 
                  'ign-opt' => \$ignore_opt,  | 
            
            
              | 46 | 
              );  | 
            
            
              | 47 | 
               | 
            
            
              | 48 | 
              if ( $filename && !-e $filename && $cmd !~ /^b/ ) { | 
            
            
              | 49 | 
                  die "File $filename not found";  | 
            
            
              | 50 | 
              }  | 
            
            
              | 51 | 
              if ( !$cmd || !$filename || $help ) { | 
            
            
              | 52 | 
                  pod2usage( -verbose => 2 );  | 
            
            
              | 53 | 
                  exit;  | 
            
            
              | 54 | 
              }  | 
            
            
              | 55 | 
               | 
            
            
              | 56 | 
              #------------------------------------------------------------------------------  | 
            
            
              | 57 | 
               | 
            
            
              | 58 | 
              #backup prefs  | 
            
            
              | 59 | 
              if ( $cmd =~ /^b/i && $filename ) { | 
            
            
              | 60 | 
                  my $dbprefs = ReadPrefsFromDb();  | 
            
            
              | 61 | 
                  open my $fh, '>:encoding(UTF-8)', $filename;  | 
            
            
              | 62 | 
                  SavePrefsToFile( $dbprefs, $fh );  | 
            
            
              | 63 | 
                  close $fh;  | 
            
            
              | 64 | 
              }  | 
            
            
              | 65 | 
               | 
            
            
              | 66 | 
              #test pref file: read and save for gaining confidence :) run a diff  | 
            
            
              | 67 | 
              if ( $cmd =~ /^t/i && $filename ) { | 
            
            
              | 68 | 
                  my $fileprefs = ReadPrefsFromFile($filename);  | 
            
            
              | 69 | 
                  open my $fh, '>:encoding(UTF-8)', $filename . ".sav";  | 
            
            
              | 70 | 
                  SavePrefsToFile( $fileprefs, $fh );  | 
            
            
              | 71 | 
                  close $fh;  | 
            
            
              | 72 | 
              }  | 
            
            
              | 73 | 
               | 
            
            
              | 74 | 
              #compare prefs (with db)  | 
            
            
              | 75 | 
              if ( $cmd =~ /^c/i && $filename ) { | 
            
            
              | 76 | 
                  my $dbprefs   = ReadPrefsFromDb();  | 
            
            
              | 77 | 
                  my $fileprefs = ReadPrefsFromFile($filename);  | 
            
            
              | 78 | 
               | 
            
            
              | 79 | 
                  #compare now  | 
            
            
              | 80 | 
                  my $cmp = ComparePrefs( $dbprefs, $fileprefs );  | 
            
            
              | 81 | 
                  PrintCompare( $cmp, "database", "file $filename" );  | 
            
            
              | 82 | 
                  HandleCompareChanges( $cmp, $dbprefs, $fileprefs )  | 
            
            
              | 83 | 
                    if $compare_add || $compare_del || $compare_upd;  | 
            
            
              | 84 | 
              }  | 
            
            
              | 85 | 
               | 
            
            
              | 86 | 
              #restore prefs  | 
            
            
              | 87 | 
              if ( $cmd =~ /^r/i && $filename ) { | 
            
            
              | 88 | 
                  my $fileprefs = ReadPrefsFromFile($filename);  | 
            
            
              | 89 | 
                  CheckVersionPref($fileprefs);  | 
            
            
              | 90 | 
               | 
            
            
              | 91 | 
                  #override this check by removing Version from your file  | 
            
            
              | 92 | 
                  #if you know what you are doing of course  | 
            
            
              | 93 | 
                  SavePrefsToDb($fileprefs);  | 
            
            
              | 94 | 
              }  | 
            
            
              | 95 | 
               | 
            
            
              | 96 | 
              #------------------------------------------------------------------------------  | 
            
            
              | 97 | 
               | 
            
            
              | 98 | 
              sub PrintCompare { | 
            
            
              | 99 | 
                  my ( $ch, $s1, $s2 ) = @_;  | 
            
            
              | 100 | 
                  foreach ( sort keys %$ch ) { | 
            
            
              | 101 | 
                      my $v = $ch->{$_}; | 
            
            
              | 102 | 
                      print "$_: ";  | 
            
            
              | 103 | 
                      if    ( $v eq '1' ) { print "Not in $s2"; } | 
            
            
              | 104 | 
                      elsif ( $v eq '2' ) { print "Not in $s1"; } | 
            
            
              | 105 | 
                      else                { print "Different values: $v"; } | 
            
            
              | 106 | 
                      print "\n";  | 
            
            
              | 107 | 
                  }  | 
            
            
              | 108 | 
              }  | 
            
            
              | 109 | 
               | 
            
            
              | 110 | 
              sub HandleCompareChanges { | 
            
            
              | 111 | 
                  my ( $cmp_pref, $dbpref, $filepref ) = @_;  | 
            
            
              | 112 | 
                  my $t = 0;  | 
            
            
              | 113 | 
                  foreach my $k ( sort keys %$cmp_pref ) { | 
            
            
              | 114 | 
                      my $cmp = $cmp_pref->{$k}; | 
            
            
              | 115 | 
                      if ( $cmp eq '1' ) { | 
            
            
              | 116 | 
                          $t += DeleteOnePref($k) if $compare_del;  | 
            
            
              | 117 | 
                      } elsif ( $cmp eq '2' ) { | 
            
            
              | 118 | 
                          my $kwc  = $filepref->{$k}->{orgkey}; | 
            
            
              | 119 | 
                          my $val  = $filepref->{$k}->{value}; | 
            
            
              | 120 | 
                          my $type = $filepref->{$k}->{type}; | 
            
            
              | 121 | 
                          $t += InsertIgnoreOnePref( $kwc, $val, $type ) if $compare_add;  | 
            
            
              | 122 | 
                      } elsif ($cmp) {    #should contain something.. | 
            
            
              | 123 | 
                          my $val = $filepref->{$k}->{value}; | 
            
            
              | 124 | 
                          $t += UpdateOnePref( $k, $val ) if $compare_upd;  | 
            
            
              | 125 | 
                      }  | 
            
            
              | 126 | 
                  }  | 
            
            
              | 127 | 
                  print "Adjusted $t prefs from this compare.\n";  | 
            
            
              | 128 | 
              }  | 
            
            
              | 129 | 
               | 
            
            
              | 130 | 
              sub ComparePrefs { | 
            
            
              | 131 | 
                  my ( $ph1, $ph2 ) = @_;  | 
            
            
              | 132 | 
                  my $res = {}; | 
            
            
              | 133 | 
                  foreach my $k ( keys %$ph1 ) { | 
            
            
              | 134 | 
                      if ( !exists $ph2->{$k} ) { | 
            
            
              | 135 | 
                          $res->{$k} = 1; | 
            
            
              | 136 | 
                      } else { | 
            
            
              | 137 | 
                          my $v1 = $ph1->{$k}->{value} // 'NULL'; | 
            
            
              | 138 | 
                          my $v2 = $ph2->{$k}->{value} // 'NULL'; | 
            
            
              | 139 | 
                          if ( $v1 ne $v2 ) { | 
            
            
              | 140 | 
                              $res->{$k} = "$v1 / $v2"; | 
            
            
              | 141 | 
                          }  | 
            
            
              | 142 | 
                      }  | 
            
            
              | 143 | 
                  }  | 
            
            
              | 144 | 
                  foreach my $k ( keys %$ph2 ) { | 
            
            
              | 145 | 
                      if ( !exists $ph1->{$k} ) { | 
            
            
              | 146 | 
                          $res->{$k} = 2; | 
            
            
              | 147 | 
                      }  | 
            
            
              | 148 | 
                  }  | 
            
            
              | 149 | 
                  return $res;  | 
            
            
              | 150 | 
              }  | 
            
            
              | 151 | 
               | 
            
            
              | 152 | 
              sub ReadPrefsFromDb { | 
            
            
              | 153 | 
                  my $sql = 'SELECT variable AS orgkey, LOWER(variable) AS variable, value, type FROM systempreferences ORDER BY variable';  | 
            
            
              | 154 | 
                  my $hash = $dbh->selectall_hashref( $sql, 'variable' );  | 
            
            
              | 155 | 
                  return $hash;  | 
            
            
              | 156 | 
              }  | 
            
            
              | 157 | 
               | 
            
            
              | 158 | 
              sub ReadPrefsFromFile { | 
            
            
              | 159 | 
                  my ($file) = @_;  | 
            
            
              | 160 | 
                  open my $fh, '<:encoding(UTF-8)', $filename;  | 
            
            
              | 161 | 
                  my @lines = <$fh>;  | 
            
            
              | 162 | 
                  close $fh;  | 
            
            
              | 163 | 
                  my $hash;  | 
            
            
              | 164 | 
                  for ( my $t = 0 ; $t < @lines ; $t++ ) { | 
            
            
              | 165 | 
                      next if $lines[$t] =~ /^\s*#|^\s*$/;    # comment line or empty line  | 
            
            
              | 166 | 
                      my @l = split ",", $lines[$t], 4;  | 
            
            
              | 167 | 
                      die "Invalid pref file; check line " . ++$t if @l < 4 || $l[0] !~ /^\d+$/ || $t + $l[0] >= @lines;  | 
            
            
              | 168 | 
                      my $key = lc $l[1];  | 
            
            
              | 169 | 
                      $hash->{$key} = { orgkey => $l[1], value => $l[3], type => $l[2] }; | 
            
            
              | 170 | 
                      for ( my $j = 0 ; $j < $l[0] ; $j++ ) { $hash->{$key}->{value} .= $lines[ $t + $j + 1 ]; } | 
            
            
              | 171 | 
                      $t = $t + $l[0];  | 
            
            
              | 172 | 
                      $hash->{$key}->{value} =~ s/\n$//;      #only 'last' line | 
            
            
              | 173 | 
                  }  | 
            
            
              | 174 | 
                  return $hash;  | 
            
            
              | 175 | 
              }  | 
            
            
              | 176 | 
               | 
            
            
              | 177 | 
              sub SavePrefsToFile { | 
            
            
              | 178 | 
                  my ( $hash, $fh ) = @_;  | 
            
            
              | 179 | 
                  print $fh '#cmp_sysprefs.pl: ' . C4::Context->config('database') . ', ' . localtime . "\n"; | 
            
            
              | 180 | 
                  foreach my $k ( sort keys %$hash ) { | 
            
            
              | 181 | 
               | 
            
            
              | 182 | 
                      #sort handles underscore differently than mysql?  | 
            
            
              | 183 | 
                      my $c   = CountLines( $hash->{$k}->{value} ); | 
            
            
              | 184 | 
                      my $kwc = $hash->{$k}->{orgkey};                # key-with-case | 
            
            
              | 185 | 
                      print $fh "$c,$kwc," . ( $hash->{$k}->{type} // 'Free' ) . ',' . ( $hash->{$k}->{value} // 'NULL' ) . "\n"; | 
            
            
              | 186 | 
                  }  | 
            
            
              | 187 | 
              }  | 
            
            
              | 188 | 
               | 
            
            
              | 189 | 
              sub SavePrefsToDb { | 
            
            
              | 190 | 
                  my ($hash) = @_;  | 
            
            
              | 191 | 
                  my $t = 0;  | 
            
            
              | 192 | 
               | 
            
            
              | 193 | 
                  #will not erase everything! you can do that in mysql :)  | 
            
            
              | 194 | 
                  foreach my $k ( keys %$hash ) { | 
            
            
              | 195 | 
                      my $v = $hash->{$k}->{value} eq 'NULL' ? undef : $hash->{$k}->{value}; | 
            
            
              | 196 | 
                      my $kwc  = $hash->{$k}->{orgkey} // $k; | 
            
            
              | 197 | 
                      my $type = $hash->{$k}->{type}   // 'Free'; | 
            
            
              | 198 | 
               | 
            
            
              | 199 | 
                      #insert and update seem overkill, but better than delete and insert  | 
            
            
              | 200 | 
                      #you cannot assume that the pref IS or IS NOT there  | 
            
            
              | 201 | 
                      InsertIgnoreOnePref( $kwc, $v, $type );  | 
            
            
              | 202 | 
                      UpdateOnePref( $k, $v );  | 
            
            
              | 203 | 
                      $t++;  | 
            
            
              | 204 | 
                  }  | 
            
            
              | 205 | 
                  print "Updated $t prefs\n";  | 
            
            
              | 206 | 
              }  | 
            
            
              | 207 | 
               | 
            
            
              | 208 | 
              sub InsertIgnoreOnePref { | 
            
            
              | 209 | 
                  my ( $kwc, $v, $t ) = @_;  | 
            
            
              | 210 | 
                  my $i = $dbh->do(  | 
            
            
              | 211 | 
                      'INSERT IGNORE INTO systempreferences (variable, value, type)  | 
            
            
              | 212 | 
                      VALUES (?,?,?)', undef, ( $kwc, $v, $t )  | 
            
            
              | 213 | 
                  );  | 
            
            
              | 214 | 
                  return !defined($i) || $i eq '0E0'? 0: $i;  | 
            
            
              | 215 | 
              }  | 
            
            
              | 216 | 
               | 
            
            
              | 217 | 
              sub UpdateOnePref { | 
            
            
              | 218 | 
                  my ( $k, $v ) = @_;  | 
            
            
              | 219 | 
                  return if lc $k eq 'version';  | 
            
            
              | 220 | 
                  my $i = $dbh->do( 'UPDATE systempreferences SET value=? WHERE variable=?', undef, ( $v, $k ) );  | 
            
            
              | 221 | 
                  return !defined($i) || $i eq '0E0'? 0: $i;  | 
            
            
              | 222 | 
              }  | 
            
            
              | 223 | 
               | 
            
            
              | 224 | 
              sub DeleteOnePref { | 
            
            
              | 225 | 
                  my ($k) = @_;  | 
            
            
              | 226 | 
                  return if lc $k eq 'version';  | 
            
            
              | 227 | 
                  my $sql = 'DELETE FROM systempreferences WHERE variable=?';  | 
            
            
              | 228 | 
                  unless ($ignore_opt) { | 
            
            
              | 229 | 
                      $sql .= " AND COALESCE(explanation,'')='' AND COALESCE(options,'')=''";  | 
            
            
              | 230 | 
                  }  | 
            
            
              | 231 | 
                  my $i = $dbh->do( $sql, undef, ($k) );  | 
            
            
              | 232 | 
                  return !defined($i) || $i eq '0E0'? 0: $i;  | 
            
            
              | 233 | 
              }  | 
            
            
              | 234 | 
               | 
            
            
              | 235 | 
              sub CheckVersionPref {    #additional precaution | 
            
            
              | 236 | 
                                        #if there are versions, compare them  | 
            
            
              | 237 | 
                  my ($hash) = @_;  | 
            
            
              | 238 | 
                  my $hv = $hash->{version}->{value}; | 
            
            
              | 239 | 
                  return if !defined $hv;  | 
            
            
              | 240 | 
                  my ($dv) = $dbh->selectrow_array(  | 
            
            
              | 241 | 
                      'SELECT value FROM systempreferences  | 
            
            
              | 242 | 
                      WHERE variable LIKE ?', undef, ('version') | 
            
            
              | 243 | 
                  );  | 
            
            
              | 244 | 
                  return if !defined $dv;  | 
            
            
              | 245 | 
                  die "Versions do not match ($dv, $hv)" if $dv ne $hv;  | 
            
            
              | 246 | 
              }  | 
            
            
              | 247 | 
               | 
            
            
              | 248 | 
              sub CountLines { | 
            
            
              | 249 | 
                  my @ma;  | 
            
            
              | 250 | 
                  return ( $_[0] && ( @ma = $_[0] =~ /\r?\n|\r\n?/g ) ) ? scalar @ma : 0;  | 
            
            
              | 251 | 
              }  | 
            
            
              | 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  |