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) )",
76 marcrecorddone=>"( isbn char(40),
79 controlnumber char(40))",
80 uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
82 hidden smallint(6) default NULL,
83 name varchar(255) default NULL)",
84 ethnicity=>"( code varchar(10) NOT NULL default '',
85 name varchar(255) default NULL,
86 PRIMARY KEY (code) )",
90 biblio=>{ 'abstract' => 'text' },
91 deletedbiblio=>{ 'abstract' => 'text' },
92 biblioitems=>{ 'lccn' => 'char(25)',
93 'url' => 'varchar(255)',
95 deletedbiblioitems=>{ 'lccn' => 'char(25)',
96 'url' => 'varchar(255)',
98 branchtransfers=>{ 'datearrived' => 'datetime' },
99 statistics=>{'borrowernumber' =>'int(11)'},
100 aqbooksellers=>{'invoicedisc' =>'float(6,4)',
101 'nocalc' => 'int(11)'},
104 # Default system preferences
106 'autoMemberNum'=> '1',
107 'acquisitions'=> 'simple',
116 # Get version of MySQL database engine.
117 my $mysqlversion=`mysqld --version`;
118 $mysqlversion=~/Ver (\S*) /;
120 if ($mysqlversion ge '3.23') {
121 print "Could convert to MyISAM database tables...\n";
124 #---------------------------------
127 # Collect all tables into a list
128 $sth=$dbh->prepare("show tables");
130 while (my ($table) = $sth->fetchrow) {
131 $existingtables{$table}=1;
134 # Now add any missing tables
135 foreach $table ( keys %requiretables ) {
136 print "Checking $table table...\n" if $debug;
137 unless ($existingtables{$table} ) {
138 print "Adding $table table...\n";
139 my $sth=$dbh->prepare(
140 "create table $table $requiretables{$table}" );
143 print "Error : $sth->errstr \n";
149 unless ($existingtables{'z3950servers'}) {
150 print "Adding z3950servers table...\n";
151 my $sti=$dbh->prepare("create table z3950servers (
162 $sti=$dbh->prepare("insert into z3950servers
163 values ('z3950.loc.gov',
167 'Library of Congress',
172 #---------------------------------
175 foreach $table ( keys %requirefields ) {
176 print "Check table $table\n" if $debug;
177 $sth=$dbh->prepare("show columns from $table");
180 while ( ($column, $type, $null, $key, $default, $extra)
182 $types{$column}=$type;
184 foreach $column ( keys %{ $requirefields{$table} } ) {
185 print " Check column $column\n" if $debug;
186 if ( ! $types{$column} ) {
187 # column doesn't exist
188 print "Adding $column field to $table table...\n";
189 $query="alter table $table
190 add column $column " . $requirefields{$table}->{$column} ;
191 print "Execute: $query\n" if $debug;
192 my $sti=$dbh->prepare($query);
195 print "**Error : $sti->errstr \n";
202 # Get list of columns from items table
205 my $sth=$dbh->prepare("show columns from items");
207 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
208 $itemtypes{$column}=$type;
211 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
212 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
215 print "Setting maximum barcode length to 20 (was $oldlength).\n";
216 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
221 # extending the timestamp in branchtransfers...
224 my $sth=$dbh->prepare("show columns from branchtransfers");
226 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
227 $branchtransfers{$column}=$type;
230 unless ($branchtransfers{'datesent'} eq 'datetime') {
231 print "Setting type of datesent in branchtransfers to datetime.\n";
232 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
236 unless ($branchtransfers{'datearrived'} eq 'datetime') {
237 print "Setting type of datearrived in branchtransfers to datetime.\n";
238 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
242 # changing the branchcategories table around...
243 my %branchcategories;
245 my $sth=$dbh->prepare("show columns from branchcategories");
247 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
248 $branchcategories{$column}=$type;
251 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
252 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
253 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
255 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
259 unless ($branchcategories{'categoryname'} eq 'text') {
260 print "Changing branchcode in branchcategories to categoryname text.\n";
261 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
265 unless ($branchcategories{'codedescription'} eq 'text') {
266 print "Replacing branchholding in branchcategories with codedescription text.\n";
267 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
272 # Populate systempreferences if it is empty
274 foreach $prefitem ( keys %defaultprefs ) {
275 $sth=$dbh->prepare("select value
276 from systempreferences
278 $sth->execute($prefitem);
279 unless ($sth->rows) {
280 print "Adding system preference item $prefitem with value " .
281 $defaultprefs{$prefitem} ."\n";
283 insert into systempreferences (variable, value)
285 $sti->execute($prefitem,$defaultprefs{$prefitem});
296 # Revision 1.12 2002/07/04 16:41:06 tonnesen
297 # Merged changes from rel-1-2. Abstracted table structure changes by alan.