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))",
64 # Get version of MySQL database engine.
65 my $mysqlversion=`mysqld --version`;
66 $mysqlversion=~/Ver (\S*) /;
68 if ($mysqlversion ge '3.23') {
69 print "Could convert to MyISAM database tables...\n";
72 #---------------------------------
75 # Collect all tables into a list
76 my $sth=$dbh->prepare("show tables");
78 while (my ($table) = $sth->fetchrow) {
79 $existingtables{$table}=1;
82 # Now add any missing tables
83 foreach $table ( keys %requiretables ) {
84 unless ($existingtables{$table} ) {
85 print "Adding $table table...\n";
86 my $sth=$dbh->prepare(
87 "create table $table $requiretables{$table}" );
90 print "Error : $sth->errstr \n";
97 unless ($existingtables{'z3950servers'}) {
98 print "Adding z3950servers table...\n";
99 my $sti=$dbh->prepare("create table z3950servers (
110 $sti=$dbh->prepare("insert into z3950servers
111 values ('z3950.loc.gov',
115 'Library of Congress',
120 #---------------------------------
124 # Get list of columns from biblioitems table
126 my $sth=$dbh->prepare("show columns from biblioitems");
128 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
129 $types{$column}=$type;
131 unless ($types{'lccn'}) {
132 # Add LCCN field to biblioitems db
133 print "Adding lccn field to biblioitems table...\n";
134 my $sti=$dbh->prepare("alter table biblioitems
135 add column lccn char(25)");
138 unless ($types{'marc'}) {
139 # Add MARC field to biblioitems db (not used anymore)
140 print "Adding marc field to biblioitems table...\n";
141 my $sti=$dbh->prepare("alter table biblioitems
142 add column marc text");
146 # Get list of columns from biblioitems table
149 my $sth=$dbh->prepare("show columns from items");
151 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
152 $itemtypes{$column}=$type;
155 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
156 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
159 print "Setting maximum barcode length to 20 (was $oldlength).\n";
160 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
165 # extending the timestamp in branchtransfers...
168 my $sth=$dbh->prepare("show columns from branchtransfers");
170 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
171 $branchtransfers{$column}=$type;
174 unless ($branchtransfers{'datesent'} eq 'datetime') {
175 print "Setting type of datesent in branchtransfers to datetime.\n";
176 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
180 unless ($branchtransfers{'datearrived'} eq 'datetime') {
181 print "Setting type of datearrived in branchtransfers to datetime.\n";
182 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
186 # changing the branchcategories table around...
187 my %branchcategories;
189 my $sth=$dbh->prepare("show columns from branchcategories");
191 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
192 $branchcategories{$column}=$type;
195 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
196 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
197 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
199 $sth=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
203 unless ($branchcategories{'branchcode'} eq 'varchar(4)') {
204 print "Changing branchcode in branchcategories to categoryname text.\n";
205 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
209 unless ($branchcategories{'codedescription'} eq 'text') {
210 print "Replacing branchholding in branchcategories with codedescription text.\n";
211 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");