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)'},
194 classification =>'classification',
195 multipart =>'multipart',
196 multivolume =>'multivolume',
197 newitems =>'newitems',
198 procedures =>'procedures',
199 publisher =>'publisher',
200 searchstats =>'searchstats',
201 serialissues =>'serialissues',
204 # Default system preferences
206 'autoMemberNum'=> '1',
207 'acquisitions'=> 'simple',
216 # Get version of MySQL database engine.
217 my $mysqlversion=`mysqld --version`;
218 $mysqlversion=~/Ver (\S*) /;
220 if ($mysqlversion ge '3.23') {
221 print "Could convert to MyISAM database tables...\n";
224 #---------------------------------
227 # Collect all tables into a list
228 $sth=$dbh->prepare("show tables");
230 while (my ($table) = $sth->fetchrow) {
231 $existingtables{$table}=1;
234 # Now add any missing tables
235 foreach $table ( keys %requiretables ) {
236 print "Checking $table table...\n" if $debug;
237 unless ($existingtables{$table} ) {
238 print "Adding $table table...\n";
239 my $sth=$dbh->prepare(
240 "create table $table $requiretables{$table}" );
243 print "Error : $sth->errstr \n";
249 # now drop useless tables
250 foreach $table ( keys %dropable_table) {
251 print "Dropping unused tables...\n" if $debug;
252 if ($existingtables{$table} ) {
253 $dbh->do("drop table $table");
255 print "Error : $dbh->errstr \n";
259 unless ($existingtables{'z3950servers'}) {
260 print "Adding z3950servers table...\n";
261 my $sti=$dbh->prepare("create table z3950servers (
272 $sti=$dbh->prepare("insert into z3950servers
273 values ('z3950.loc.gov',
277 'Library of Congress',
282 #---------------------------------
285 foreach $table ( keys %requirefields ) {
286 print "Check table $table\n" if $debug;
287 $sth=$dbh->prepare("show columns from $table");
290 while ( ($column, $type, $null, $key, $default, $extra)
292 $types{$column}=$type;
294 foreach $column ( keys %{ $requirefields{$table} } ) {
295 print " Check column $column\n" if $debug;
296 if ( ! $types{$column} ) {
297 # column doesn't exist
298 print "Adding $column field to $table table...\n";
299 $query="alter table $table
300 add column $column " . $requirefields{$table}->{$column} ;
301 print "Execute: $query\n" if $debug;
302 my $sti=$dbh->prepare($query);
305 print "**Error : $sti->errstr \n";
312 # Get list of columns from items table
315 my $sth=$dbh->prepare("show columns from items");
317 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
318 $itemtypes{$column}=$type;
321 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
322 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
325 print "Setting maximum barcode length to 20 (was $oldlength).\n";
326 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
331 # extending the timestamp in branchtransfers...
334 my $sth=$dbh->prepare("show columns from branchtransfers");
336 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
337 $branchtransfers{$column}=$type;
340 unless ($branchtransfers{'datesent'} eq 'datetime') {
341 print "Setting type of datesent in branchtransfers to datetime.\n";
342 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
346 unless ($branchtransfers{'datearrived'} eq 'datetime') {
347 print "Setting type of datearrived in branchtransfers to datetime.\n";
348 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
352 # changing the branchcategories table around...
353 my %branchcategories;
355 my $sth=$dbh->prepare("show columns from branchcategories");
357 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
358 $branchcategories{$column}=$type;
361 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
362 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
363 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
365 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
369 unless ($branchcategories{'categoryname'} eq 'text') {
370 print "Changing branchcode in branchcategories to categoryname text.\n";
371 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
375 unless ($branchcategories{'codedescription'} eq 'text') {
376 print "Replacing branchholding in branchcategories with codedescription text.\n";
377 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
382 # Populate systempreferences if it is empty
384 foreach $prefitem ( keys %defaultprefs ) {
385 $sth=$dbh->prepare("select value
386 from systempreferences
388 $sth->execute($prefitem);
389 unless ($sth->rows) {
390 print "Adding system preference item $prefitem with value " .
391 $defaultprefs{$prefitem} ."\n";
393 insert into systempreferences (variable, value)
395 $sti->execute($prefitem,$defaultprefs{$prefitem});
406 # Revision 1.17 2002/09/24 12:57:35 tipaul
407 # long WAS the road to 1.3.0...
408 # coming VERY SOON NOW...
409 # modifying installer and buildrelease to update the DB
411 # Revision 1.16 2002/07/31 02:34:27 finlayt
413 # added "notforloan" field to the itemtypes table.
415 # Revision 1.15 2002/07/20 22:30:06 rangi
416 # Making sure fix makes it into the main branch as well
419 # Revision 1.14 2002/07/08 16:20:26 tonnesen
420 # Added sessionqueries table and password/userid fields to borrowers table
422 # Revision 1.13 2002/07/04 18:05:36 tonnesen
425 # Revision 1.12 2002/07/04 16:41:06 tonnesen
426 # Merged changes from rel-1-2. Abstracted table structure changes by alan.