(bug fix): Fixed typo.
[koha.git] / updater / updatedatabase
1 #!/usr/bin/perl
2
3 # $Id$
4
5 # Database Updater
6 # This script checks for required updates to the database.
7
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
10
11 # Bugs/ToDo:
12 # - Would also be a good idea to offer to do a backup at this time...
13
14 # NOTE:  If you do something more than once in here, make it table driven.
15
16 use strict;
17
18 # CPAN modules
19 use DBI;
20
21 # Koha modules
22 use C4::Context;
23         # FIXME - /etc/koha.conf might not exist, so shouldn't use
24         # C4::Context.
25
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
28
29 my $debug=0;
30
31 my (
32         $sth, $sti,
33         $query,
34         %existingtables,        # tables already in database
35         %types,
36         $table,
37         $column,
38         $type, $null, $key, $default, $extra,
39         $prefitem,              # preference item in systempreferences table
40 );
41
42 my $dbh = C4::Context->dbh;
43
44 #-------------------
45 # Defines
46
47 # Tables to add if they don't exist
48 my %requiretables=(
49     shelfcontents=>"( shelfnumber int not null,
50                                                         itemnumber int not null,
51                                                         flags int)",
52     bookshelf=>"( shelfnumber int auto_increment primary key,
53                                                 shelfname char(255))",
54     z3950queue=>"( id int auto_increment primary key,
55                                                 term text,
56                                                 type char(10),
57                                                 startdate int,
58                                                 enddate int,
59                                                 done smallint,
60                                                 results longblob,
61                                                 numrecords int,
62                                                 servers text,
63                                                 identifier char(30))",
64         z3950results=>"( id int auto_increment primary key,
65                                                 queryid int,
66                                                 server char(255),
67                                                 startdate int,
68                                                 enddate int,
69                                                 results longblob,
70                                                 numrecords int,
71                                                 numdownloaded int,
72                                                 highestseen int,
73                                                 active smallint)",
74         branchrelations=>"( branchcode varchar(4),
75                                                         categorycode varchar(4))",
76         websites=>"( websitenumber int(11) NOT NULL auto_increment,
77                                                 biblionumber int(11) NOT NULL default '0',
78                                                 title text,
79                                                 description text,
80                                                 url varchar(255),
81                                                 PRIMARY KEY (websitenumber) )",
82         marcrecorddone=>"( isbn char(40),
83                                                                 issn char(40),
84                                                                 lccn char(40),
85                                                                 controlnumber char(40))",
86     uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
87                                                         marc longblob,
88                                                         hidden smallint(6) default NULL,
89                                                         name varchar(255) default NULL)",
90         ethnicity=>"( code varchar(10) NOT NULL default '',
91                                         name varchar(255) default NULL,
92                                         PRIMARY KEY  (code)   )",
93         sessions=>"( sessionID varchar(255) NOT NULL default '',
94                                                 userid varchar(255) default NULL,
95                                                 ip varchar(16) default NULL,
96                                                 lasttime int,
97                                                 PRIMARY KEY (sessionID)   )",
98     sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
99                                                                 userid char(100) NOT NULL default '',
100                                                                 ip char(18) NOT NULL default '',
101                                                                 url text NOT NULL default ''  )",
102         bibliothesaurus=> "( code BIGINT not null AUTO_INCREMENT,
103                                                 freelib CHAR (255) not null ,
104                                                 stdlib CHAR (255) not null ,
105                                                 type CHAR (80) not null ,
106                                                 PRIMARY KEY (code),
107                                                 INDEX (freelib),index(stdlib),index(type) )",
108         marc_biblio => "(
109                                                 bibid bigint(20) unsigned NOT NULL auto_increment,
110                                                 biblionumber int(11) NOT NULL default '0',
111                                                 datecreated date NOT NULL default '0000-00-00',
112                                                 datemodified date default NULL,
113                                                 origincode char(20) default NULL,
114                                                 PRIMARY KEY  (bibid),
115                                                 KEY origincode (origincode),
116                                                 KEY biblionumber (biblionumber)
117                                                 ) ",
118         marc_blob_subfield => "(
119                                         blobidlink bigint(20) NOT NULL auto_increment,
120                                         subfieldvalue longtext NOT NULL,
121                                         PRIMARY KEY  (blobidlink)
122                                         ) ",
123         marc_subfield_structure => "(
124                                                 tagfield char(3) NOT NULL default '',
125                                                 tagsubfield char(1) NOT NULL default '',
126                                                 liblibrarian char(255) NOT NULL default '',
127                                                 libopac char(255) NOT NULL default '',
128                                                 repeatable tinyint(4) NOT NULL default '0',
129                                                 mandatory tinyint(4) NOT NULL default '0',
130                                                 kohafield char(40) NOT NULL default '',
131                                                 tab tinyint(1),
132                                                 authorised_value char(10) default NULL,
133                                                 PRIMARY KEY  (tagfield,tagsubfield),
134                                                 KEY kohafield (kohafield),
135                                                 KEY tab (tab)
136                                                 )",
137         marc_subfield_table => "(
138                                                 subfieldid bigint(20) unsigned NOT NULL auto_increment,
139                                                 bibid bigint(20) unsigned NOT NULL default '0',
140                                                 tag char(3) NOT NULL default '',
141                                                 tagorder tinyint(4) NOT NULL default '1',
142                                                 tag_indicator char(2) NOT NULL default '',
143                                                 subfieldcode char(1) NOT NULL default '',
144                                                 subfieldorder tinyint(4) NOT NULL default '1',
145                                                 subfieldvalue varchar(255) default NULL,
146                                                 valuebloblink bigint(20) default NULL,
147                                                 PRIMARY KEY  (subfieldid),
148                                                 KEY bibid (bibid),
149                                                 KEY tag (tag),
150                                                 KEY tag_indicator (tag_indicator),
151                                                 KEY subfieldorder (subfieldorder),
152                                                 KEY subfieldcode (subfieldcode),
153                                                 KEY subfieldvalue (subfieldvalue),
154                                                 KEY tagorder (tagorder)
155                                         )",
156         marc_tag_structure => "(
157                                         tagfield char(3) NOT NULL default '',
158                                         liblibrarian char(255) NOT NULL default '',
159                                         libopac char(255) NOT NULL default '',
160                                         repeatable tinyint(4) NOT NULL default '0',
161                                         mandatory tinyint(4) NOT NULL default '0',
162                                         PRIMARY KEY  (tagfield)
163                                         )",
164         marc_word => "(
165                                 bibid bigint(20) NOT NULL default '0',
166                                 tag char(3) NOT NULL default '',
167                                 tagorder tinyint(4) NOT NULL default '1',
168                                 subfieldid char(1) NOT NULL default '',
169                                 subfieldorder tinyint(4) NOT NULL default '1',
170                                 word varchar(255) NOT NULL default '',
171                                 sndx_word varchar(255) NOT NULL default '',
172                                 KEY bibid (bibid),
173                                 KEY tag (tag),
174                                 KEY tagorder (tagorder),
175                                 KEY subfieldid (subfieldid),
176                                 KEY subfieldorder (subfieldorder),
177                                 KEY word (word),
178                                 KEY sndx_word (sndx_word)
179                         )",
180         marc_breeding =>"( file varchar(80) NOT NULL default '',
181                                                                 isbn varchar(10) NOT NULL default '',
182                                                                 marc text NOT NULL,
183                                                                 PRIMARY KEY  (isbn)
184                                                                 )",
185         authorised_values => "(id int(11) NOT NULL auto_increment,
186                                                                         category char(10) NOT NULL default '',
187                                                                         authorised_value char(80) NOT NULL default '',
188                                                                         PRIMARY KEY  (id),
189                                                                         KEY name (category)
190                                                                 )",
191         );
192
193
194 my %requirefields=(
195         biblio=>{ 'abstract' => 'text' },
196         deletedbiblio=>{ 'abstract' => 'text' },
197         biblioitems=>{ 'lccn' => 'char(25)',
198                                                 'url' => 'varchar(255)',
199                                                 'marc' => 'text' },
200         deletedbiblioitems=>{ 'lccn' => 'char(25)',
201                                                                 'url' => 'varchar(255)',
202                                                                 'marc' => 'text' },
203         branchtransfers=>{ 'datearrived' => 'datetime' },
204         statistics=>{'borrowernumber' =>'int(11)'},
205         aqbooksellers=>{'invoicedisc' =>'float(6,4)',
206                                                 'nocalc' => 'int(11)'},
207         borrowers=>{'userid' => 'char(30)',
208                                         'password' => 'char(30)',},
209         aqorders=>{'budgetdate' => 'date'},
210         #added so that reference items are not available for reserves...
211         itemtypes=>{'notforloan' => 'smallint(6)'},
212         systempreferences =>{'explanation' => 'char(80)'},
213 );
214
215 my %dropable_table=(
216                     classification   =>'classification',
217                     multipart        =>'multipart',
218                     multivolume      =>'multivolume',
219                     newitems         =>'newitems',
220                     procedures       =>'procedures',
221                     publisher        =>'publisher',
222                     searchstats      =>'searchstats',
223                     serialissues     =>'serialissues',
224                     );
225
226 # Default system preferences
227 my %defaultprefs=(
228                 'autoMemberNum'=> ['1','1 or else. If 1, Barcode is auto-calculated'],
229                 'acquisitions'=> ['simple','normal or simple : will use acquisition system found in directory acqui.simple or acquisition'],
230                 'template' => ['default','template default name'],
231                 'autoBarcode' => ['0','1 or else. If 1, Barcode is auto-calculated'],
232                 'insecure' => ['no','if YES, no auth at all is needed. Be careful if you set this to yes !']
233                   );
234
235
236 # Start checking
237
238 # Get version of MySQL database engine.
239 my $mysqlversion=`mysqld --version`;
240 $mysqlversion=~/Ver (\S*) /;
241 $mysqlversion=$1;
242 if ($mysqlversion ge '3.23') {
243         print "Could convert to MyISAM database tables...\n";
244 }
245
246 #---------------------------------
247 # Tables
248
249 # Collect all tables into a list
250 $sth=$dbh->prepare("show tables");
251 $sth->execute;
252 while (my ($table) = $sth->fetchrow) {
253     $existingtables{$table}=1;
254 }
255
256 # Now add any missing tables
257 foreach $table ( keys %requiretables ) {
258         print "Checking $table table...\n" if $debug;
259         unless ($existingtables{$table} ) {
260                 print "Adding $table table...\n";
261                 my $sth=$dbh->prepare(
262                         "create table $table $requiretables{$table}" );
263                 $sth->execute;
264                 if ($sth->err) {
265                                 print "Error : $sth->errstr \n";
266                                 $sth->finish;
267                 } # if error
268         } # unless exists
269 } # foreach
270
271 # now drop useless tables
272 foreach $table ( keys %dropable_table) {
273         print "Dropping unused tables...\n" if $debug;
274         if ($existingtables{$table} ) {
275                 $dbh->do("drop table $table");
276                 if ($dbh->err) {
277                 print "Error : $dbh->errstr \n";
278                 }
279         }
280 }
281 unless ($existingtables{'z3950servers'}) {
282         print "Adding z3950servers table...\n";
283         my $sti=$dbh->prepare("create table z3950servers (
284                                                                                 host char(255),
285                                                                                 port int,
286                                                                                 db char(255),
287                                                                                 userid char(255),
288                                                                                 password char(255),
289                                                                                 name text,
290                                                                                 id int,
291                                                                                 checked smallint,
292                                                                                 rank int)");
293         $sti->execute;
294         $sti=$dbh->prepare("insert into z3950servers
295                                                                 values ('z3950.loc.gov',
296                                                                 7090,
297                                                                 'voyager',
298                                                                 '', '',
299                                                                 'Library of Congress',
300                                                                 1, 1, 1)");
301         $sti->execute;
302 }
303
304 #---------------------------------
305 # Columns
306
307 foreach $table ( keys %requirefields ) {
308         print "Check table $table\n" if $debug;
309         $sth=$dbh->prepare("show columns from $table");
310         $sth->execute();
311         undef %types;
312         while ( ($column, $type, $null, $key, $default, $extra)
313                         = $sth->fetchrow) {
314                 $types{$column}=$type;
315         } # while
316         foreach $column ( keys %{ $requirefields{$table} } )  {
317                 print "  Check column $column\n" if $debug;
318                 if ( ! $types{$column} ) {
319                 # column doesn't exist
320                 print "Adding $column field to $table table...\n";
321                 $query="alter table $table
322                         add column $column " . $requirefields{$table}->{$column} ;
323                 print "Execute: $query\n" if $debug;
324                 my $sti=$dbh->prepare($query);
325                 $sti->execute;
326                         if ($sti->err) {
327                                         print "**Error : $sti->errstr \n";
328                                         $sti->finish;
329                         } # if error
330                 } # if column
331         } # foreach column
332 } # foreach table
333
334 # Get list of columns from items table
335 my %itemtypes;
336
337 $sth=$dbh->prepare("show columns from items");
338 $sth->execute;
339 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
340         $itemtypes{$column}=$type;
341 }
342
343 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
344         $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
345         my $oldlength=$1;
346         if ($oldlength<20) {
347                 print "Setting maximum barcode length to 20 (was $oldlength).\n";
348                 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
349                 $sti->execute;
350         }
351 }
352
353 # extending the timestamp in branchtransfers...
354 my %branchtransfers;
355
356 $sth=$dbh->prepare("show columns from branchtransfers");
357 $sth->execute;
358 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
359     $branchtransfers{$column}=$type;
360 }
361
362 unless ($branchtransfers{'datesent'} eq 'datetime') {
363     print "Setting type of datesent in branchtransfers to datetime.\n";
364     my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
365     $sti->execute;
366 }
367
368 unless ($branchtransfers{'datearrived'} eq 'datetime') {
369     print "Setting type of datearrived in branchtransfers to datetime.\n";
370     my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
371     $sti->execute;
372 }
373
374 # changing the branchcategories table around...
375 my %branchcategories;
376
377 $sth=$dbh->prepare("show columns from branchcategories");
378 $sth->execute;
379 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
380         $branchcategories{$column}=$type;
381 }
382
383 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
384         print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
385         my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
386         $sti->execute;
387         $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
388         $sti->execute;
389 }
390
391 unless ($branchcategories{'categoryname'} eq 'text') {
392         print "Changing branchcode in branchcategories to categoryname text.\n";
393         my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
394         $sth->execute;
395 }
396
397 unless ($branchcategories{'codedescription'} eq 'text') {
398         print "Replacing branchholding in branchcategories with codedescription text.\n";
399         my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
400         $sth->execute;
401 }
402
403
404 # Populate systempreferences if it is empty
405
406 foreach $prefitem ( keys %defaultprefs ) {
407         $sth=$dbh->prepare("select value
408                                                                 from systempreferences
409                                                                 where variable=?");
410         $sth->execute($prefitem);
411         unless ($sth->rows) {
412                 print "Adding system preference item $prefitem with value " .$defaultprefs{$prefitem}[0] ."\n";
413                 $sti=$dbh->prepare("insert into systempreferences (variable, value,explanation) values (?,?,?)");
414                 $sti->execute($prefitem,$defaultprefs{$prefitem}[0],$defaultprefs{$prefitem}[1]);
415         } # unless
416 } # foreach
417
418
419 $sth->finish;
420
421 exit;
422
423 # $Log$
424 # Revision 1.24  2002/10/30 14:00:23  arensb
425 # (bug fix): Fixed typo.
426 #
427 # Revision 1.23  2002/10/25 10:55:46  tipaul
428 # Road to 1.3.2
429 # * bugfixes and improvements
430 # * manage mandatory MARC subfields
431 # * new table : authorised_values. this table contains categories and authorised values for the category. On MARC management, you can map a subfield to a authorised_values category. If you do this, the subfield can only be filled with a authorised_value of the selected category.
432 # this submit contains everything needed :
433 # * updatedatabase
434 # * admin screens
435 # * "links" management
436 # * creation of a html-list if a subfield is mapped to an authorised value.
437 #
438 # Note this is different from authorities support, which will come soon.
439 # The authorised_values is supposed to contains a "small" number of authorised values for a category (less than 50-100). If you enter more authorised values than this, it should be hard to find what you want in a BIG list...
440 #
441 # Revision 1.22  2002/10/15 10:08:19  tipaul
442 # fixme corrected, re-indent and adding the marc_breeding table (see commit of marcimport.pl for more explanations about breeding)
443 #
444 # Revision 1.21  2002/10/14 11:48:59  tipaul
445 # bugfix
446 #
447 # Revision 1.20  2002/10/10 04:49:41  arensb
448 # Added some FIXME comments.
449 #
450 # Revision 1.19  2002/10/05 10:17:17  arensb
451 # Merged with arensb-context branch: use C4::Context->dbh instead of
452 # &C4Connect, and generally prefer C4::Context over C4::Database.
453 #
454 # Revision 1.18.2.2  2002/10/05 06:18:43  arensb
455 # Added a whole mess of FIXME comments.
456 #
457 # Revision 1.18.2.1  2002/10/04 02:46:00  arensb
458 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
459 # C4Connect.
460 #
461 # Revision 1.18  2002/09/24 13:50:55  tipaul
462 # long WAS the road to 1.3.0...
463 # coming VERY SOON NOW...
464 # modifying installer and buildrelease to update the DB
465 #
466 # Revision 1.17  2002/09/24 12:57:35  tipaul
467 # long WAS the road to 1.3.0...
468 # coming VERY SOON NOW...
469 # modifying installer and buildrelease to update the DB
470 #
471 # Revision 1.16  2002/07/31 02:34:27  finlayt
472 #
473 # added "notforloan" field to the itemtypes table.
474 #
475 # Revision 1.15  2002/07/20 22:30:06  rangi
476 # Making sure fix makes it into the main branch as well
477 # Fix for bug 69
478 #
479 # Revision 1.14  2002/07/08 16:20:26  tonnesen
480 # Added sessionqueries table and password/userid fields to borrowers table
481 #
482 # Revision 1.13  2002/07/04 18:05:36  tonnesen
483 # bug fix
484 #
485 # Revision 1.12  2002/07/04 16:41:06  tonnesen
486 # Merged changes from rel-1-2.  Abstracted table structure changes by alan.
487 #