4 # This script checks for required updates to the database.
6 # Part of the Koha Library Software www.koha.org
7 # Licensed under the GPL.
10 # - Would also be a good idea to offer to do a backup at this time...
20 my %existingtables; # tables already in database
27 # Tables to add if they don't exist
29 shelfcontents=>"( shelfnumber int not null,
30 itemnumber int not null,
32 bookshelf=>"( shelfnumber int auto_increment primary key,
33 shelfname char(255))",
34 z3950queue=>"( id int auto_increment primary key,
43 identifier char(30))",
44 z3950results=>"( id int auto_increment primary key,
54 branchrelations=>"( branchcode varchar(4),
55 categorycode varchar(4))",
56 websites=>"( websitenumber int(11) NOT NULL auto_increment,
57 biblionumber int(11) NOT NULL default '0',
61 PRIMARY KEY (websitenumber) )",
70 # Get version of MySQL database engine.
71 my $mysqlversion=`mysqld --version`;
72 $mysqlversion=~/Ver (\S*) /;
74 if ($mysqlversion ge '3.23') {
75 print "Could convert to MyISAM database tables...\n";
78 #---------------------------------
81 # Collect all tables into a list
82 my $sth=$dbh->prepare("show tables");
84 while (my ($table) = $sth->fetchrow) {
85 $existingtables{$table}=1;
88 # Now add any missing tables
89 foreach $table ( keys %requiretables ) {
90 unless ($existingtables{$table} ) {
91 print "Adding $table table...\n";
92 my $sth=$dbh->prepare(
93 "create table $table $requiretables{$table}" );
96 print "Error : $sth->errstr \n";
103 unless ($existingtables{'z3950servers'}) {
104 print "Adding z3950servers table...\n";
105 my $sti=$dbh->prepare("create table z3950servers (
116 $sti=$dbh->prepare("insert into z3950servers
117 values ('z3950.loc.gov',
121 'Library of Congress',
126 #---------------------------------
130 # Get list of columns from biblioitems table
132 my $sth=$dbh->prepare("show columns from biblioitems");
134 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
135 $types{$column}=$type;
137 unless ($types{'lccn'}) {
138 # Add LCCN field to biblioitems db
139 print "Adding lccn field to biblioitems table...\n";
140 my $sti=$dbh->prepare("alter table biblioitems
141 add column lccn char(25)");
144 unless ($types{'marc'}) {
145 # Add MARC field to biblioitems db (not used anymore)
146 print "Adding marc field to biblioitems table...\n";
147 my $sti=$dbh->prepare("alter table biblioitems
148 add column marc text");
152 # Get list of columns from biblioitems table
155 my $sth=$dbh->prepare("show columns from items");
157 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
158 $itemtypes{$column}=$type;
161 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
162 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
165 print "Setting maximum barcode length to 20 (was $oldlength).\n";
166 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
171 # extending the timestamp in branchtransfers...
174 my $sth=$dbh->prepare("show columns from branchtransfers");
176 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
177 $branchtransfers{$column}=$type;
180 unless ($branchtransfers{'datesent'} eq 'datetime') {
181 print "Setting type of datesent in branchtransfers to datetime.\n";
182 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
186 unless ($branchtransfers{'datearrived'} eq 'datetime') {
187 print "Setting type of datearrived in branchtransfers to datetime.\n";
188 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
192 # changing the branchcategories table around...
193 my %branchcategories;
195 my $sth=$dbh->prepare("show columns from branchcategories");
197 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
198 $branchcategories{$column}=$type;
201 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
202 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
203 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
205 $sth=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
209 unless ($branchcategories{'branchcode'} eq 'varchar(4)') {
210 print "Changing branchcode in branchcategories to categoryname text.\n";
211 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
215 unless ($branchcategories{'codedescription'} eq 'text') {
216 print "Replacing branchholding in branchcategories with codedescription text.\n";
217 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");