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...
27 %existingtables, # tables already in database
31 $type, $null, $key, $default, $extra,
37 # Tables to add if they don't exist
39 shelfcontents=>"( shelfnumber int not null,
40 itemnumber int not null,
42 bookshelf=>"( shelfnumber int auto_increment primary key,
43 shelfname char(255))",
44 z3950queue=>"( id int auto_increment primary key,
51 identifier char(30))",
52 z3950results=>"( id int auto_increment primary key,
62 branchrelations=>"( branchcode varchar(4),
63 categorycode varchar(4))",
64 marcrecorddone=>"( isbn char(40),
67 controlnumber char(40))",
68 uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
70 hidden smallint(6) default NULL,
71 name varchar(255) default NULL)",
72 ethnicity=>"( code varchar(10) NOT NULL default '',
73 name varchar(255) default NULL,
74 PRIMARY KEY (code) )",
78 biblio=>{ 'abstract' => 'text' },
79 deletedbiblio=>{ 'abstract' => 'text' },
80 biblioitems=>{ 'lccn' => 'char(25)',
81 'url' => 'varchar(255)',
83 deletedbiblioitems=>{ 'lccn' => 'char(25)',
84 'url' => 'varchar(255)',
86 branchtransfers=>{ 'datearrived' => 'datetime' },
95 # Get version of MySQL database engine.
96 my $mysqlversion=`mysqld --version`;
97 $mysqlversion=~/Ver (\S*) /;
99 if ($mysqlversion ge '3.23') {
100 print "Could convert to MyISAM database tables...\n";
103 #---------------------------------
106 # Collect all tables into a list
107 $sth=$dbh->prepare("show tables");
109 while (my ($table) = $sth->fetchrow) {
110 $existingtables{$table}=1;
113 # Now add any missing tables
114 foreach $table ( keys %requiretables ) {
115 print "Checking $table table...\n" if $debug;;
116 unless ($existingtables{$table} ) {
117 print "Adding $table table...\n";
118 my $sth=$dbh->prepare(
119 "create table $table $requiretables{$table}" );
122 print "Error : $sth->errstr \n";
128 unless ($existingtables{'z3950servers'}) {
129 print "Adding z3950servers table...\n";
130 my $sti=$dbh->prepare("create table z3950servers (
141 $sti=$dbh->prepare("insert into z3950servers
142 values ('z3950.loc.gov',
146 'Library of Congress',
151 #---------------------------------
154 foreach $table ( keys %requirefields ) {
155 print "Check table $table\n";
156 $sth=$dbh->prepare("show columns from $table");
159 while ( ($column, $type, $null, $key, $default, $extra)
161 $types{$column}=$type;
163 foreach $column ( keys %{ $requirefields{$table} } ) {
164 print " Check column $column\n";
165 if ( ! $types{$column} ) {
166 # column doesn't exist
167 print "Adding $column field to $table table...\n";
168 $query="alter table $table
169 add column $column " . $requirefields{$table}->{$column} ;
170 print "Execute: $query\n" if $debug;
171 my $sti=$dbh->prepare($query);
174 print "**Error : $sti->errstr \n";
181 # Get list of columns from items table
184 my $sth=$dbh->prepare("show columns from items");
186 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
187 $itemtypes{$column}=$type;
190 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
191 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
194 print "Setting maximum barcode length to 20 (was $oldlength).\n";
195 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
200 # extending the timestamp in branchtransfers...
203 my $sth=$dbh->prepare("show columns from branchtransfers");
205 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
206 $branchtransfers{$column}=$type;
209 unless ($branchtransfers{'datesent'} eq 'datetime') {
210 print "Setting type of datesent in branchtransfers to datetime.\n";
211 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
215 unless ($branchtransfers{'datearrived'} eq 'datetime') {
216 print "Setting type of datearrived in branchtransfers to datetime.\n";
217 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
221 # changing the branchcategories table around...
222 my %branchcategories;
224 my $sth=$dbh->prepare("show columns from branchcategories");
226 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
227 $branchcategories{$column}=$type;
230 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
231 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
232 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
234 $sth=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
238 unless ($branchcategories{'branchcode'} eq 'varchar(4)') {
239 print "Changing branchcode in branchcategories to categoryname text.\n";
240 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
244 unless ($branchcategories{'codedescription'} eq 'text') {
245 print "Replacing branchholding in branchcategories with codedescription text.\n";
246 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
257 # Revision 1.4.2.6 2002/06/20 15:07:08 amillar
258 # Add table ethnicity