6 # This script checks for required updates to the database.
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
12 # - Would also be a good idea to offer to do a backup at this time...
14 # NOTE: If you do something more than once in here, make it table driven.
23 # FIXME - The user might be installing a new database, so can't rely
24 # on /etc/koha.conf anyway.
31 %existingtables, # tables already in database
35 $type, $null, $key, $default, $extra,
36 $prefitem, # preference item in systempreferences table
43 my $dbh = C4::Context->dbh;
44 print "connected to your DB. Checking & modifying it\n" unless $silent;
49 # Tables to add if they don't exist
51 categorytable => "(categorycode char(5) NOT NULL default '',
52 description text default '',
53 itemtypecodes text default '',
54 PRIMARY KEY (categorycode)
56 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
57 description text default '',
58 itemtypecodes text default '',
59 PRIMARY KEY (subcategorycode)
61 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
62 description text default '',
63 itemtypecodes text default '',
64 PRIMARY KEY (mediatypecode)
67 `timestamp` TIMESTAMP NOT NULL ,
68 `user` INT( 11 ) NOT NULL ,
69 `module` TEXT default '',
70 `action` TEXT default '' ,
71 `object` INT(11) default '' ,
72 `info` TEXT default '' ,
73 PRIMARY KEY ( `timestamp` , `user` )
78 # tablename => { 'field' => 'fieldtype' },
81 my %dropable_table = (
82 # tablename => 'tablename',
86 # tablename => "field1,field2",
88 # the other hash contains other actions that can't be done elsewhere. they are done
89 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
91 # The tabledata hash contains data that should be in the tables.
92 # The uniquefieldrequired hash entry is used to determine which (if any) fields
93 # must not exist in the table for this row to be inserted. If the
94 # uniquefieldrequired entry is already in the table, the existing data is not
95 # modified, unless the forceupdate hash entry is also set. Fields in the
96 # anonymous "forceupdate" hash will be forced to be updated to the default
97 # values given in the %tabledata hash.
101 # { uniquefielrequired => 'fieldname', # the primary key in the table
102 # fieldname => fieldvalue,
103 # fieldname2 => fieldvalue2,
106 systempreferences => [
108 uniquefieldrequired => 'variable',
109 variable => 'Activate_Log',
111 forceupdate => { 'explanation' => 1,
113 explanation => 'Turn Log Actions on DB On an Off',
117 uniquefieldrequired => 'variable',
118 variable => 'IndependantBranches',
120 forceupdate => { 'explanation' => 1,
122 explanation => 'Turn Branch independancy management On an Off',
130 my %fielddefinitions = (
132 # { field => 'fieldname',
133 # type => 'fieldtype',
146 # Get version of MySQL database engine.
147 my $mysqlversion = `mysqld --version`;
148 $mysqlversion =~ /Ver (\S*) /;
150 if ( $mysqlversion ge '3.23' ) {
151 print "Could convert to MyISAM database tables...\n" unless $silent;
154 #---------------------------------
157 # Collect all tables into a list
158 $sth = $dbh->prepare("show tables");
160 while ( my ($table) = $sth->fetchrow ) {
161 $existingtables{$table} = 1;
165 # Now add any missing tables
166 foreach $table ( keys %requiretables ) {
167 unless ( $existingtables{$table} ) {
168 print "Adding $table table...\n" unless $silent;
169 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
172 print "Error : $sth->errstr \n";
178 # now drop useless tables
179 foreach $table ( keys %dropable_table ) {
180 if ( $existingtables{$table} ) {
181 print "Dropping unused table $table\n" if $debug and not $silent;
182 $dbh->do("drop table $table");
184 print "Error : $dbh->errstr \n";
189 #---------------------------------
192 foreach $table ( keys %requirefields ) {
193 print "Check table $table\n" if $debug and not $silent;
194 $sth = $dbh->prepare("show columns from $table");
197 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
199 $types{$column} = $type;
201 foreach $column ( keys %{ $requirefields{$table} } ) {
202 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
203 if ( !$types{$column} ) {
205 # column doesn't exist
206 print "Adding $column field to $table table...\n" unless $silent;
207 $query = "alter table $table
208 add column $column " . $requirefields{$table}->{$column};
209 print "Execute: $query\n" if $debug;
210 my $sti = $dbh->prepare($query);
213 print "**Error : $sti->errstr \n";
220 foreach $table ( keys %fielddefinitions ) {
221 print "Check table $table\n" if $debug;
222 $sth = $dbh->prepare("show columns from $table");
225 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
227 $definitions->{$column}->{type} = $type;
228 $definitions->{$column}->{null} = $null;
229 $definitions->{$column}->{key} = $key;
230 $definitions->{$column}->{default} = $default;
231 $definitions->{$column}->{extra} = $extra;
233 my $fieldrow = $fielddefinitions{$table};
234 foreach my $row (@$fieldrow) {
235 my $field = $row->{field};
236 my $type = $row->{type};
237 my $null = $row->{null};
238 my $key = $row->{key};
239 my $default = $row->{default};
240 $default="''" unless $default;
241 my $extra = $row->{extra};
242 my $def = $definitions->{$field};
243 unless ( $type eq $def->{type}
244 && $null eq $def->{null}
245 && $key eq $def->{key}
246 && $default eq $def->{default}
247 && $extra eq $def->{extra} )
253 if ( $key eq 'PRI' ) {
254 $key = 'PRIMARY KEY';
256 unless ( $extra eq 'auto_increment' ) {
259 # if it's a new column use "add", if it's an old one, use "change".
261 if ($definitions->{$field}->{type}) {
262 $action="change $field"
266 # if it's a primary key, drop the previous pk, before altering the table
268 if ($key ne 'PRIMARY KEY') {
269 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
271 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
273 $sth->execute($default);
274 print " Alter $field in $table\n" unless $silent;
280 # Populate tables with required data
281 foreach my $table ( keys %tabledata ) {
282 print "Checking for data required in table $table...\n" unless $silent;
283 my $tablerows = $tabledata{$table};
284 foreach my $row (@$tablerows) {
285 my $uniquefieldrequired = $row->{uniquefieldrequired};
286 my $uniquevalue = $row->{$uniquefieldrequired};
287 my $forceupdate = $row->{forceupdate};
290 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
292 $sth->execute($uniquevalue);
294 foreach my $field (keys %$forceupdate) {
295 if ($forceupdate->{$field}) {
296 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
297 $sth->execute($row->{$field}, $uniquevalue);
301 print "Adding row to $table: " unless $silent;
305 foreach my $field ( keys %$row ) {
306 next if $field eq 'uniquefieldrequired';
307 next if $field eq 'forceupdate';
308 my $value = $row->{$field};
309 push @values, $value;
310 print " $field => $value" unless $silent;
311 $fieldlist .= "$field,";
312 $placeholders .= "?,";
314 print "\n" unless $silent;
315 $fieldlist =~ s/,$//;
316 $placeholders =~ s/,$//;
319 "insert into $table ($fieldlist) values ($placeholders)");
320 $sth->execute(@values);
325 # at last, remove useless fields
326 foreach $table ( keys %uselessfields ) {
327 my @fields = split /,/,$uselessfields{$table};
330 foreach my $fieldtodrop (@fields) {
331 $fieldtodrop =~ s/\t//g;
332 $fieldtodrop =~ s/\n//g;
334 $sth = $dbh->prepare("show columns from $table");
336 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
338 $exists =1 if ($column eq $fieldtodrop);
341 print "deleting $fieldtodrop field in $table...\n" unless $silent;
342 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
354 # Revision 1.112 2005/07/26 08:19:47 hdl
355 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
357 # Revision 1.111 2005/07/25 15:35:38 tipaul
358 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
359 # So, the updatedatabase script can highly be cleaned (90% removed).
360 # Let's play with the new Koha DB structure now ;-)