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.
29 %existingtables, # tables already in database
33 $type, $null, $key, $default, $extra,
34 $prefitem, # preference item in systempreferences table
40 # Tables to add if they don't exist
42 shelfcontents=>"( shelfnumber int not null,
43 itemnumber int not null,
45 bookshelf=>"( shelfnumber int auto_increment primary key,
46 shelfname char(255))",
47 z3950queue=>"( id int auto_increment primary key,
56 identifier char(30))",
57 z3950results=>"( id int auto_increment primary key,
67 branchrelations=>"( branchcode varchar(4),
68 categorycode varchar(4))",
69 websites=>"( websitenumber int(11) NOT NULL auto_increment,
70 biblionumber int(11) NOT NULL default '0',
74 PRIMARY KEY (websitenumber) )",
75 marcrecorddone=>"( isbn char(40),
78 controlnumber char(40))",
79 uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
81 hidden smallint(6) default NULL,
82 name varchar(255) default NULL)",
83 ethnicity=>"( code varchar(10) NOT NULL default '',
84 name varchar(255) default NULL,
85 PRIMARY KEY (code) )",
86 sessions=>"( sessionID varchar(255) NOT NULL default '',
87 userid varchar(255) default NULL,
88 ip varchar(16) default NULL,
90 PRIMARY KEY (sessionID) )",
91 sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
92 userid char(100) NOT NULL default '',
93 ip char(18) NOT NULL default '',
94 url text NOT NULL default '' )",
95 bibliothesaurus=> "( code BIGINT not null AUTO_INCREMENT,
96 freelib CHAR (255) not null ,
97 stdlib CHAR (255) not null ,
98 type CHAR (80) not null ,
100 INDEX (freelib),index(stdlib),index(type) )",
102 bibid bigint(20) unsigned NOT NULL auto_increment,
103 biblionumber int(11) NOT NULL default '0',
104 datecreated date NOT NULL default '0000-00-00',
105 datemodified date default NULL,
106 origincode char(20) default NULL,
108 KEY origincode (origincode),
109 KEY biblionumber (biblionumber)
111 marc_blob_subfield => "(
112 blobidlink bigint(20) NOT NULL auto_increment,
113 subfieldvalue longtext NOT NULL,
114 PRIMARY KEY (blobidlink)
116 marc_subfield_structure => "(
117 tagfield char(3) NOT NULL default '',
118 tagsubfield char(1) NOT NULL default '',
119 liblibrarian char(255) NOT NULL default '',
120 libopac char(255) NOT NULL default '',
121 repeatable tinyint(4) NOT NULL default '0',
122 mandatory tinyint(4) NOT NULL default '0',
123 kohafield char(40) NOT NULL default '',
124 PRIMARY KEY (tagfield,tagsubfield),
125 KEY kohafield (kohafield)
127 marc_subfield_table => "(
128 subfieldid bigint(20) unsigned NOT NULL auto_increment,
129 bibid bigint(20) unsigned NOT NULL default '0',
130 tag char(3) NOT NULL default '',
131 tagorder tinyint(4) NOT NULL default '1',
132 tag_indicator char(2) NOT NULL default '',
133 subfieldcode char(1) NOT NULL default '',
134 subfieldorder tinyint(4) NOT NULL default '1',
135 subfieldvalue varchar(255) default NULL,
136 valuebloblink bigint(20) default NULL,
137 PRIMARY KEY (subfieldid),
140 KEY tag_indicator (tag_indicator),
141 KEY subfieldorder (subfieldorder),
142 KEY subfieldcode (subfieldcode),
143 KEY subfieldvalue (subfieldvalue),
144 KEY tagorder (tagorder)
146 marc_tag_structure => "(
147 tagfield char(3) NOT NULL default '',
148 liblibrarian char(255) NOT NULL default '',
149 libopac char(255) NOT NULL default '',
150 repeatable tinyint(4) NOT NULL default '0',
151 mandatory tinyint(4) NOT NULL default '0',
152 PRIMARY KEY (tagfield)
155 bibid bigint(20) NOT NULL default '0',
156 tag char(3) NOT NULL default '',
157 tagorder tinyint(4) NOT NULL default '1',
158 subfieldid char(1) NOT NULL default '',
159 subfieldorder tinyint(4) NOT NULL default '1',
160 word varchar(255) NOT NULL default '',
161 sndx_word varchar(255) NOT NULL default '',
164 KEY tagorder (tagorder),
165 KEY subfieldid (subfieldid),
166 KEY subfieldorder (subfieldorder),
168 KEY sndx_word (sndx_word)
174 biblio=>{ 'abstract' => 'text' },
175 deletedbiblio=>{ 'abstract' => 'text' },
176 biblioitems=>{ 'lccn' => 'char(25)',
177 'url' => 'varchar(255)',
179 deletedbiblioitems=>{ 'lccn' => 'char(25)',
180 'url' => 'varchar(255)',
182 branchtransfers=>{ 'datearrived' => 'datetime' },
183 statistics=>{'borrowernumber' =>'int(11)'},
184 aqbooksellers=>{'invoicedisc' =>'float(6,4)',
185 'nocalc' => 'int(11)'},
186 borrowers=>{'userid' => 'char(30)',
187 'password' => 'char(30)',},
188 aqorders=>{'budgetdate' => 'date'},
189 #added so that reference items are not available for reserves...
190 itemtypes=>{'notforloan' => 'smallint(6)'},
191 systempreferences =>{'explanation' => 'char(80)'},
195 classification =>'classification',
196 multipart =>'multipart',
197 multivolume =>'multivolume',
198 newitems =>'newitems',
199 procedures =>'procedures',
200 publisher =>'publisher',
201 searchstats =>'searchstats',
202 serialissues =>'serialissues',
205 # Default system preferences
207 'autoMemberNum'=> ['1','1 or else. If 1, Barcode is auto-calculated'],
208 'acquisitions'=> ['simple','normal or simple : will use acquisition system found in directory acqui.simple or acquisition'],
209 'template' => ['default','template default name']
214 my $dbh = C4::Context->dbh;
218 # Get version of MySQL database engine.
219 my $mysqlversion=`mysqld --version`;
220 $mysqlversion=~/Ver (\S*) /;
222 if ($mysqlversion ge '3.23') {
223 print "Could convert to MyISAM database tables...\n";
226 #---------------------------------
229 # Collect all tables into a list
230 $sth=$dbh->prepare("show tables");
232 while (my ($table) = $sth->fetchrow) {
233 $existingtables{$table}=1;
236 # Now add any missing tables
237 foreach $table ( keys %requiretables ) {
238 print "Checking $table table...\n" if $debug;
239 unless ($existingtables{$table} ) {
240 print "Adding $table table...\n";
241 my $sth=$dbh->prepare(
242 "create table $table $requiretables{$table}" );
245 print "Error : $sth->errstr \n";
251 # now drop useless tables
252 foreach $table ( keys %dropable_table) {
253 print "Dropping unused tables...\n" if $debug;
254 if ($existingtables{$table} ) {
255 $dbh->do("drop table $table");
257 print "Error : $dbh->errstr \n";
261 unless ($existingtables{'z3950servers'}) {
262 print "Adding z3950servers table...\n";
263 my $sti=$dbh->prepare("create table z3950servers (
274 $sti=$dbh->prepare("insert into z3950servers
275 values ('z3950.loc.gov',
279 'Library of Congress',
284 #---------------------------------
287 foreach $table ( keys %requirefields ) {
288 print "Check table $table\n" if $debug;
289 $sth=$dbh->prepare("show columns from $table");
292 while ( ($column, $type, $null, $key, $default, $extra)
294 $types{$column}=$type;
296 foreach $column ( keys %{ $requirefields{$table} } ) {
297 print " Check column $column\n" if $debug;
298 if ( ! $types{$column} ) {
299 # column doesn't exist
300 print "Adding $column field to $table table...\n";
301 $query="alter table $table
302 add column $column " . $requirefields{$table}->{$column} ;
303 print "Execute: $query\n" if $debug;
304 my $sti=$dbh->prepare($query);
307 print "**Error : $sti->errstr \n";
314 # Get list of columns from items table
317 # FIXME - There's already a $sth in this scope.
318 my $sth=$dbh->prepare("show columns from items");
320 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
321 $itemtypes{$column}=$type;
324 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
325 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
328 print "Setting maximum barcode length to 20 (was $oldlength).\n";
329 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
334 # extending the timestamp in branchtransfers...
337 # FIXME - There's already a $sth in this scope.
338 my $sth=$dbh->prepare("show columns from branchtransfers");
340 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
341 $branchtransfers{$column}=$type;
344 unless ($branchtransfers{'datesent'} eq 'datetime') {
345 print "Setting type of datesent in branchtransfers to datetime.\n";
346 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
350 unless ($branchtransfers{'datearrived'} eq 'datetime') {
351 print "Setting type of datearrived in branchtransfers to datetime.\n";
352 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
356 # changing the branchcategories table around...
357 my %branchcategories;
359 # FIXME - There's already a $sth in this scope.
360 my $sth=$dbh->prepare("show columns from branchcategories");
362 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
363 $branchcategories{$column}=$type;
366 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
367 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
368 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
370 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
374 unless ($branchcategories{'categoryname'} eq 'text') {
375 print "Changing branchcode in branchcategories to categoryname text.\n";
376 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
380 unless ($branchcategories{'codedescription'} eq 'text') {
381 print "Replacing branchholding in branchcategories with codedescription text.\n";
382 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
387 # Populate systempreferences if it is empty
389 foreach $prefitem ( keys %defaultprefs ) {
390 $sth=$dbh->prepare("select value
391 from systempreferences
393 $sth->execute($prefitem);
394 unless ($sth->rows) {
395 print "Adding system preference item $prefitem with value " .
396 $defaultprefs{$prefitem}[0] ."\n";
398 insert into systempreferences (variable, value,explanation)
400 $sti->execute($prefitem,$defaultprefs{$prefitem}[0],$defaultprefs{$prefitem}[1]);
410 # Revision 1.19 2002/10/05 10:17:17 arensb
411 # Merged with arensb-context branch: use C4::Context->dbh instead of
412 # &C4Connect, and generally prefer C4::Context over C4::Database.
414 # Revision 1.18.2.2 2002/10/05 06:18:43 arensb
415 # Added a whole mess of FIXME comments.
417 # Revision 1.18.2.1 2002/10/04 02:46:00 arensb
418 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
421 # Revision 1.18 2002/09/24 13:50:55 tipaul
422 # long WAS the road to 1.3.0...
423 # coming VERY SOON NOW...
424 # modifying installer and buildrelease to update the DB
426 # Revision 1.17 2002/09/24 12:57:35 tipaul
427 # long WAS the road to 1.3.0...
428 # coming VERY SOON NOW...
429 # modifying installer and buildrelease to update the DB
431 # Revision 1.16 2002/07/31 02:34:27 finlayt
433 # added "notforloan" field to the itemtypes table.
435 # Revision 1.15 2002/07/20 22:30:06 rangi
436 # Making sure fix makes it into the main branch as well
439 # Revision 1.14 2002/07/08 16:20:26 tonnesen
440 # Added sessionqueries table and password/userid fields to borrowers table
442 # Revision 1.13 2002/07/04 18:05:36 tonnesen
445 # Revision 1.12 2002/07/04 16:41:06 tonnesen
446 # Merged changes from rel-1-2. Abstracted table structure changes by alan.