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 - /etc/koha.conf might not exist, so shouldn't use
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
45 # Tables to add if they don't exist
47 shelfcontents=>"( shelfnumber int not null,
48 itemnumber int not null,
50 bookshelf=>"( shelfnumber int auto_increment primary key,
51 shelfname char(255))",
52 z3950queue=>"( id int auto_increment primary key,
61 identifier char(30))",
62 z3950results=>"( id int auto_increment primary key,
72 branchrelations=>"( branchcode varchar(4),
73 categorycode varchar(4))",
74 websites=>"( websitenumber int(11) NOT NULL auto_increment,
75 biblionumber int(11) NOT NULL default '0',
79 PRIMARY KEY (websitenumber) )",
80 marcrecorddone=>"( isbn char(40),
83 controlnumber char(40))",
84 uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
86 hidden smallint(6) default NULL,
87 name varchar(255) default NULL)",
88 ethnicity=>"( code varchar(10) NOT NULL default '',
89 name varchar(255) default NULL,
90 PRIMARY KEY (code) )",
91 sessions=>"( sessionID varchar(255) NOT NULL default '',
92 userid varchar(255) default NULL,
93 ip varchar(16) default NULL,
95 PRIMARY KEY (sessionID) )",
96 sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
97 userid char(100) NOT NULL default '',
98 ip char(18) NOT NULL default '',
99 url text NOT NULL default '' )",
100 bibliothesaurus=> "( code BIGINT not null AUTO_INCREMENT,
101 freelib CHAR (255) not null ,
102 stdlib CHAR (255) not null ,
103 type CHAR (80) not null ,
105 INDEX (freelib),index(stdlib),index(type) )",
107 bibid bigint(20) unsigned NOT NULL auto_increment,
108 biblionumber int(11) NOT NULL default '0',
109 datecreated date NOT NULL default '0000-00-00',
110 datemodified date default NULL,
111 origincode char(20) default NULL,
113 KEY origincode (origincode),
114 KEY biblionumber (biblionumber)
116 marc_blob_subfield => "(
117 blobidlink bigint(20) NOT NULL auto_increment,
118 subfieldvalue longtext NOT NULL,
119 PRIMARY KEY (blobidlink)
121 marc_subfield_structure => "(
122 tagfield char(3) NOT NULL default '',
123 tagsubfield char(1) NOT NULL default '',
124 liblibrarian char(255) NOT NULL default '',
125 libopac char(255) NOT NULL default '',
126 repeatable tinyint(4) NOT NULL default '0',
127 mandatory tinyint(4) NOT NULL default '0',
128 kohafield char(40) NOT NULL default '',
129 PRIMARY KEY (tagfield,tagsubfield),
130 KEY kohafield (kohafield)
132 marc_subfield_table => "(
133 subfieldid bigint(20) unsigned NOT NULL auto_increment,
134 bibid bigint(20) unsigned NOT NULL default '0',
135 tag char(3) NOT NULL default '',
136 tagorder tinyint(4) NOT NULL default '1',
137 tag_indicator char(2) NOT NULL default '',
138 subfieldcode char(1) NOT NULL default '',
139 subfieldorder tinyint(4) NOT NULL default '1',
140 subfieldvalue varchar(255) default NULL,
141 valuebloblink bigint(20) default NULL,
142 PRIMARY KEY (subfieldid),
145 KEY tag_indicator (tag_indicator),
146 KEY subfieldorder (subfieldorder),
147 KEY subfieldcode (subfieldcode),
148 KEY subfieldvalue (subfieldvalue),
149 KEY tagorder (tagorder)
151 marc_tag_structure => "(
152 tagfield char(3) NOT NULL default '',
153 liblibrarian char(255) NOT NULL default '',
154 libopac char(255) NOT NULL default '',
155 repeatable tinyint(4) NOT NULL default '0',
156 mandatory tinyint(4) NOT NULL default '0',
157 PRIMARY KEY (tagfield)
160 bibid bigint(20) NOT NULL default '0',
161 tag char(3) NOT NULL default '',
162 tagorder tinyint(4) NOT NULL default '1',
163 subfieldid char(1) NOT NULL default '',
164 subfieldorder tinyint(4) NOT NULL default '1',
165 word varchar(255) NOT NULL default '',
166 sndx_word varchar(255) NOT NULL default '',
169 KEY tagorder (tagorder),
170 KEY subfieldid (subfieldid),
171 KEY subfieldorder (subfieldorder),
173 KEY sndx_word (sndx_word)
179 biblio=>{ 'abstract' => 'text' },
180 deletedbiblio=>{ 'abstract' => 'text' },
181 biblioitems=>{ 'lccn' => 'char(25)',
182 'url' => 'varchar(255)',
184 deletedbiblioitems=>{ 'lccn' => 'char(25)',
185 'url' => 'varchar(255)',
187 branchtransfers=>{ 'datearrived' => 'datetime' },
188 statistics=>{'borrowernumber' =>'int(11)'},
189 aqbooksellers=>{'invoicedisc' =>'float(6,4)',
190 'nocalc' => 'int(11)'},
191 borrowers=>{'userid' => 'char(30)',
192 'password' => 'char(30)',},
193 aqorders=>{'budgetdate' => 'date'},
194 #added so that reference items are not available for reserves...
195 itemtypes=>{'notforloan' => 'smallint(6)'},
196 systempreferences =>{'explanation' => 'char(80)'},
200 classification =>'classification',
201 multipart =>'multipart',
202 multivolume =>'multivolume',
203 newitems =>'newitems',
204 procedures =>'procedures',
205 publisher =>'publisher',
206 searchstats =>'searchstats',
207 serialissues =>'serialissues',
210 # Default system preferences
212 'autoMemberNum'=> ['1','1 or else. If 1, Barcode is auto-calculated'],
213 'acquisitions'=> ['simple','normal or simple : will use acquisition system found in directory acqui.simple or acquisition'],
214 'template' => ['default','template default name']
219 my $dbh = C4::Context->dbh;
223 # Get version of MySQL database engine.
224 my $mysqlversion=`mysqld --version`;
225 $mysqlversion=~/Ver (\S*) /;
227 if ($mysqlversion ge '3.23') {
228 print "Could convert to MyISAM database tables...\n";
231 #---------------------------------
234 # Collect all tables into a list
235 $sth=$dbh->prepare("show tables");
237 while (my ($table) = $sth->fetchrow) {
238 $existingtables{$table}=1;
241 # Now add any missing tables
242 foreach $table ( keys %requiretables ) {
243 print "Checking $table table...\n" if $debug;
244 unless ($existingtables{$table} ) {
245 print "Adding $table table...\n";
246 my $sth=$dbh->prepare(
247 "create table $table $requiretables{$table}" );
250 print "Error : $sth->errstr \n";
256 # now drop useless tables
257 foreach $table ( keys %dropable_table) {
258 print "Dropping unused tables...\n" if $debug;
259 if ($existingtables{$table} ) {
260 $dbh->do("drop table $table");
262 print "Error : $dbh->errstr \n";
266 unless ($existingtables{'z3950servers'}) {
267 print "Adding z3950servers table...\n";
268 my $sti=$dbh->prepare("create table z3950servers (
279 $sti=$dbh->prepare("insert into z3950servers
280 values ('z3950.loc.gov',
284 'Library of Congress',
289 #---------------------------------
292 foreach $table ( keys %requirefields ) {
293 print "Check table $table\n" if $debug;
294 $sth=$dbh->prepare("show columns from $table");
297 while ( ($column, $type, $null, $key, $default, $extra)
299 $types{$column}=$type;
301 foreach $column ( keys %{ $requirefields{$table} } ) {
302 print " Check column $column\n" if $debug;
303 if ( ! $types{$column} ) {
304 # column doesn't exist
305 print "Adding $column field to $table table...\n";
306 $query="alter table $table
307 add column $column " . $requirefields{$table}->{$column} ;
308 print "Execute: $query\n" if $debug;
309 my $sti=$dbh->prepare($query);
312 print "**Error : $sti->errstr \n";
319 # Get list of columns from items table
322 # FIXME - There's already a $sth in this scope.
323 my $sth=$dbh->prepare("show columns from items");
325 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
326 $itemtypes{$column}=$type;
329 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
330 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
333 print "Setting maximum barcode length to 20 (was $oldlength).\n";
334 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
339 # extending the timestamp in branchtransfers...
342 # FIXME - There's already a $sth in this scope.
343 my $sth=$dbh->prepare("show columns from branchtransfers");
345 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
346 $branchtransfers{$column}=$type;
349 unless ($branchtransfers{'datesent'} eq 'datetime') {
350 print "Setting type of datesent in branchtransfers to datetime.\n";
351 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
355 unless ($branchtransfers{'datearrived'} eq 'datetime') {
356 print "Setting type of datearrived in branchtransfers to datetime.\n";
357 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
361 # changing the branchcategories table around...
362 my %branchcategories;
364 # FIXME - There's already a $sth in this scope.
365 my $sth=$dbh->prepare("show columns from branchcategories");
367 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
368 $branchcategories{$column}=$type;
371 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
372 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
373 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
375 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
379 unless ($branchcategories{'categoryname'} eq 'text') {
380 print "Changing branchcode in branchcategories to categoryname text.\n";
381 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
385 unless ($branchcategories{'codedescription'} eq 'text') {
386 print "Replacing branchholding in branchcategories with codedescription text.\n";
387 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
392 # Populate systempreferences if it is empty
394 foreach $prefitem ( keys %defaultprefs ) {
395 $sth=$dbh->prepare("select value
396 from systempreferences
398 $sth->execute($prefitem);
399 unless ($sth->rows) {
400 print "Adding system preference item $prefitem with value " .
401 $defaultprefs{$prefitem}[0] ."\n";
403 insert into systempreferences (variable, value,explanation)
405 $sti->execute($prefitem,$defaultprefs{$prefitem}[0],$defaultprefs{$prefitem}[1]);
415 # Revision 1.20 2002/10/10 04:49:41 arensb
416 # Added some FIXME comments.
418 # Revision 1.19 2002/10/05 10:17:17 arensb
419 # Merged with arensb-context branch: use C4::Context->dbh instead of
420 # &C4Connect, and generally prefer C4::Context over C4::Database.
422 # Revision 1.18.2.2 2002/10/05 06:18:43 arensb
423 # Added a whole mess of FIXME comments.
425 # Revision 1.18.2.1 2002/10/04 02:46:00 arensb
426 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
429 # Revision 1.18 2002/09/24 13:50:55 tipaul
430 # long WAS the road to 1.3.0...
431 # coming VERY SOON NOW...
432 # modifying installer and buildrelease to update the DB
434 # Revision 1.17 2002/09/24 12:57:35 tipaul
435 # long WAS the road to 1.3.0...
436 # coming VERY SOON NOW...
437 # modifying installer and buildrelease to update the DB
439 # Revision 1.16 2002/07/31 02:34:27 finlayt
441 # added "notforloan" field to the itemtypes table.
443 # Revision 1.15 2002/07/20 22:30:06 rangi
444 # Making sure fix makes it into the main branch as well
447 # Revision 1.14 2002/07/08 16:20:26 tonnesen
448 # Added sessionqueries table and password/userid fields to borrowers table
450 # Revision 1.13 2002/07/04 18:05:36 tonnesen
453 # Revision 1.12 2002/07/04 16:41:06 tonnesen
454 # Merged changes from rel-1-2. Abstracted table structure changes by alan.