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 '' )",
99 biblio=>{ 'abstract' => 'text' },
100 deletedbiblio=>{ 'abstract' => 'text' },
101 biblioitems=>{ 'lccn' => 'char(25)',
102 'url' => 'varchar(255)',
104 deletedbiblioitems=>{ 'lccn' => 'char(25)',
105 'url' => 'varchar(255)',
107 branchtransfers=>{ 'datearrived' => 'datetime' },
108 statistics=>{'borrowernumber' =>'int(11)'},
109 aqbooksellers=>{'invoicedisc' =>'float(6,4)',
110 'nocalc' => 'int(11)'},
111 borrowers=>{'userid' => 'char(30)',
112 'password' => 'char(30)',},
113 aqorders=>{'budgetdate' => 'date'},
114 #added so that reference items are not available for reserves...
115 itemtypes=>{'notforloan' => 'smallint(6)'},
118 # Default system preferences
120 'autoMemberNum'=> '1',
121 'acquisitions'=> 'simple',
130 # Get version of MySQL database engine.
131 my $mysqlversion=`mysqld --version`;
132 $mysqlversion=~/Ver (\S*) /;
134 if ($mysqlversion ge '3.23') {
135 print "Could convert to MyISAM database tables...\n";
138 #---------------------------------
141 # Collect all tables into a list
142 $sth=$dbh->prepare("show tables");
144 while (my ($table) = $sth->fetchrow) {
145 $existingtables{$table}=1;
148 # Now add any missing tables
149 foreach $table ( keys %requiretables ) {
150 print "Checking $table table...\n" if $debug;
151 unless ($existingtables{$table} ) {
152 print "Adding $table table...\n";
153 my $sth=$dbh->prepare(
154 "create table $table $requiretables{$table}" );
157 print "Error : $sth->errstr \n";
163 unless ($existingtables{'z3950servers'}) {
164 print "Adding z3950servers table...\n";
165 my $sti=$dbh->prepare("create table z3950servers (
176 $sti=$dbh->prepare("insert into z3950servers
177 values ('z3950.loc.gov',
181 'Library of Congress',
186 #---------------------------------
189 foreach $table ( keys %requirefields ) {
190 print "Check table $table\n" if $debug;
191 $sth=$dbh->prepare("show columns from $table");
194 while ( ($column, $type, $null, $key, $default, $extra)
196 $types{$column}=$type;
198 foreach $column ( keys %{ $requirefields{$table} } ) {
199 print " Check column $column\n" if $debug;
200 if ( ! $types{$column} ) {
201 # column doesn't exist
202 print "Adding $column field to $table table...\n";
203 $query="alter table $table
204 add column $column " . $requirefields{$table}->{$column} ;
205 print "Execute: $query\n" if $debug;
206 my $sti=$dbh->prepare($query);
209 print "**Error : $sti->errstr \n";
216 # Get list of columns from items table
219 my $sth=$dbh->prepare("show columns from items");
221 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
222 $itemtypes{$column}=$type;
225 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
226 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
229 print "Setting maximum barcode length to 20 (was $oldlength).\n";
230 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
235 # extending the timestamp in branchtransfers...
238 my $sth=$dbh->prepare("show columns from branchtransfers");
240 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
241 $branchtransfers{$column}=$type;
244 unless ($branchtransfers{'datesent'} eq 'datetime') {
245 print "Setting type of datesent in branchtransfers to datetime.\n";
246 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
250 unless ($branchtransfers{'datearrived'} eq 'datetime') {
251 print "Setting type of datearrived in branchtransfers to datetime.\n";
252 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
256 # changing the branchcategories table around...
257 my %branchcategories;
259 my $sth=$dbh->prepare("show columns from branchcategories");
261 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
262 $branchcategories{$column}=$type;
265 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
266 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
267 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
269 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
273 unless ($branchcategories{'categoryname'} eq 'text') {
274 print "Changing branchcode in branchcategories to categoryname text.\n";
275 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
279 unless ($branchcategories{'codedescription'} eq 'text') {
280 print "Replacing branchholding in branchcategories with codedescription text.\n";
281 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
286 # Populate systempreferences if it is empty
288 foreach $prefitem ( keys %defaultprefs ) {
289 $sth=$dbh->prepare("select value
290 from systempreferences
292 $sth->execute($prefitem);
293 unless ($sth->rows) {
294 print "Adding system preference item $prefitem with value " .
295 $defaultprefs{$prefitem} ."\n";
297 insert into systempreferences (variable, value)
299 $sti->execute($prefitem,$defaultprefs{$prefitem});
310 # Revision 1.16 2002/07/31 02:34:27 finlayt
312 # added "notforloan" field to the itemtypes table.
314 # Revision 1.15 2002/07/20 22:30:06 rangi
315 # Making sure fix makes it into the main branch as well
318 # Revision 1.14 2002/07/08 16:20:26 tonnesen
319 # Added sessionqueries table and password/userid fields to borrowers table
321 # Revision 1.13 2002/07/04 18:05:36 tonnesen
324 # Revision 1.12 2002/07/04 16:41:06 tonnesen
325 # Merged changes from rel-1-2. Abstracted table structure changes by alan.