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',
120 my %fielddefinitions = (
122 # { field => 'fieldname',
123 # type => 'fieldtype',
136 # Get version of MySQL database engine.
137 my $mysqlversion = `mysqld --version`;
138 $mysqlversion =~ /Ver (\S*) /;
140 if ( $mysqlversion ge '3.23' ) {
141 print "Could convert to MyISAM database tables...\n" unless $silent;
144 #---------------------------------
147 # Collect all tables into a list
148 $sth = $dbh->prepare("show tables");
150 while ( my ($table) = $sth->fetchrow ) {
151 $existingtables{$table} = 1;
155 # Now add any missing tables
156 foreach $table ( keys %requiretables ) {
157 unless ( $existingtables{$table} ) {
158 print "Adding $table table...\n" unless $silent;
159 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
162 print "Error : $sth->errstr \n";
168 # now drop useless tables
169 foreach $table ( keys %dropable_table ) {
170 if ( $existingtables{$table} ) {
171 print "Dropping unused table $table\n" if $debug and not $silent;
172 $dbh->do("drop table $table");
174 print "Error : $dbh->errstr \n";
179 #---------------------------------
182 foreach $table ( keys %requirefields ) {
183 print "Check table $table\n" if $debug and not $silent;
184 $sth = $dbh->prepare("show columns from $table");
187 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
189 $types{$column} = $type;
191 foreach $column ( keys %{ $requirefields{$table} } ) {
192 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
193 if ( !$types{$column} ) {
195 # column doesn't exist
196 print "Adding $column field to $table table...\n" unless $silent;
197 $query = "alter table $table
198 add column $column " . $requirefields{$table}->{$column};
199 print "Execute: $query\n" if $debug;
200 my $sti = $dbh->prepare($query);
203 print "**Error : $sti->errstr \n";
210 foreach $table ( keys %fielddefinitions ) {
211 print "Check table $table\n" if $debug;
212 $sth = $dbh->prepare("show columns from $table");
215 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
217 $definitions->{$column}->{type} = $type;
218 $definitions->{$column}->{null} = $null;
219 $definitions->{$column}->{key} = $key;
220 $definitions->{$column}->{default} = $default;
221 $definitions->{$column}->{extra} = $extra;
223 my $fieldrow = $fielddefinitions{$table};
224 foreach my $row (@$fieldrow) {
225 my $field = $row->{field};
226 my $type = $row->{type};
227 my $null = $row->{null};
228 my $key = $row->{key};
229 my $default = $row->{default};
230 $default="''" unless $default;
231 my $extra = $row->{extra};
232 my $def = $definitions->{$field};
233 unless ( $type eq $def->{type}
234 && $null eq $def->{null}
235 && $key eq $def->{key}
236 && $default eq $def->{default}
237 && $extra eq $def->{extra} )
243 if ( $key eq 'PRI' ) {
244 $key = 'PRIMARY KEY';
246 unless ( $extra eq 'auto_increment' ) {
249 # if it's a new column use "add", if it's an old one, use "change".
251 if ($definitions->{$field}->{type}) {
252 $action="change $field"
256 # if it's a primary key, drop the previous pk, before altering the table
258 if ($key ne 'PRIMARY KEY') {
259 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
261 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
263 $sth->execute($default);
264 print " Alter $field in $table\n" unless $silent;
270 # Populate tables with required data
271 foreach my $table ( keys %tabledata ) {
272 print "Checking for data required in table $table...\n" unless $silent;
273 my $tablerows = $tabledata{$table};
274 foreach my $row (@$tablerows) {
275 my $uniquefieldrequired = $row->{uniquefieldrequired};
276 my $uniquevalue = $row->{$uniquefieldrequired};
277 my $forceupdate = $row->{forceupdate};
280 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
282 $sth->execute($uniquevalue);
284 foreach my $field (keys %$forceupdate) {
285 if ($forceupdate->{$field}) {
286 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
287 $sth->execute($row->{$field}, $uniquevalue);
291 print "Adding row to $table: " unless $silent;
295 foreach my $field ( keys %$row ) {
296 next if $field eq 'uniquefieldrequired';
297 next if $field eq 'forceupdate';
298 my $value = $row->{$field};
299 push @values, $value;
300 print " $field => $value" unless $silent;
301 $fieldlist .= "$field,";
302 $placeholders .= "?,";
304 print "\n" unless $silent;
305 $fieldlist =~ s/,$//;
306 $placeholders =~ s/,$//;
309 "insert into $table ($fieldlist) values ($placeholders)");
310 $sth->execute(@values);
315 # at last, remove useless fields
316 foreach $table ( keys %uselessfields ) {
317 my @fields = split /,/,$uselessfields{$table};
320 foreach my $fieldtodrop (@fields) {
321 $fieldtodrop =~ s/\t//g;
322 $fieldtodrop =~ s/\n//g;
324 $sth = $dbh->prepare("show columns from $table");
326 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
328 $exists =1 if ($column eq $fieldtodrop);
331 print "deleting $fieldtodrop field in $table...\n" unless $silent;
332 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
344 # Revision 1.111 2005/07/25 15:35:38 tipaul
345 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
346 # So, the updatedatabase script can highly be cleaned (90% removed).
347 # Let's play with the new Koha DB structure now ;-)