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...
32 # Get version of MySQL database engine.
33 my $mysqlversion=`mysqld --version`;
34 $mysqlversion=~/Ver (\S*) /;
36 if ($mysqlversion ge '3.23') {
37 print "Could convert to MyISAM database tables...\n";
40 #---------------------------------
43 # Collect all tables into a list
44 my $sth=$dbh->prepare("show tables");
46 while (my ($table) = $sth->fetchrow) {
50 # Now add any missing tables
52 # Add tables for virtual bookshelf management
53 unless ($tables{'shelfcontents'}) {
54 print "Adding shelfcontents table...\n";
55 my $sti=$dbh->prepare("create table shelfcontents (
56 shelfnumber int not null,
57 itemnumber int not null,
61 unless ($tables{'bookshelf'}) {
62 print "Adding bookshelf table...\n";
63 my $sti=$dbh->prepare("create table bookshelf (
64 shelfnumber int auto_increment primary key,
65 shelfname char(255))");
69 # Add tables required by Z-3950 scripts
71 unless ($tables{'z3950queue'}) {
72 print "Adding z3950queue table...\n";
73 my $sti=$dbh->prepare("create table z3950queue (
74 id int auto_increment primary key,
83 identifier char(30))");
87 unless ($tables{'z3950results'}) {
88 print "Adding z3950results table...\n";
89 my $sti=$dbh->prepare("create table z3950results (
90 id int auto_increment primary key,
102 unless ($tables{'z3950servers'}) {
103 print "Adding z3950servers table...\n";
104 my $sti=$dbh->prepare("create table z3950servers (
115 $sti=$dbh->prepare("insert into z3950servers
116 values ('z3950.loc.gov',
120 'Library of Congress',
125 # Create new branchrelations table if it doesnt already exist....
126 unless ($tables{'branchrelations'} ) {
127 print "creating branchrelations table\n";
128 my $sth=$dbh->prepare("create table branchrelations (
129 branchcode varchar(4),
130 categorycode varchar(4))");
134 #---------------------------------
138 # Get list of columns from biblioitems table
140 my $sth=$dbh->prepare("show columns from biblioitems");
142 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
143 $types{$column}=$type;
145 unless ($types{'lccn'}) {
146 # Add LCCN field to biblioitems db
147 print "Adding lccn field to biblioitems table...\n";
148 my $sti=$dbh->prepare("alter table biblioitems
149 add column lccn char(25)");
152 unless ($types{'marc'}) {
153 # Add MARC field to biblioitems db (not used anymore)
154 print "Adding marc field to biblioitems table...\n";
155 my $sti=$dbh->prepare("alter table biblioitems
156 add column marc text");
160 # Get list of columns from biblioitems table
163 my $sth=$dbh->prepare("show columns from items");
165 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
166 $itemtypes{$column}=$type;
169 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
170 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
173 print "Setting maximum barcode length to 20 (was $oldlength).\n";
174 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
179 # extending the timestamp in branchtransfers...
182 my $sth=$dbh->prepare("show columns from branchtransfers");
184 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
185 $branchtransfers{$column}=$type;
188 unless ($branchtransfers{'datesent'} eq 'datetime') {
189 print "Setting type of datesent in branchtransfers to datetime.\n";
190 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
194 unless ($branchtransfers{'datearrived'} eq 'datetime') {
195 print "Setting type of datearrived in branchtransfers to datetime.\n";
196 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
200 # changing the branchcategories table around...
201 my %branchcategories;
203 my $sth=$dbh->prepare("show columns from branchcategories");
205 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
206 $branchcategories{$column}=$type;
209 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
210 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
211 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
213 $sth=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
217 unless ($branchcategories{'branchcode'} eq 'varchar(4)') {
218 print "Changing branchcode in branchcategories to categoryname text.\n";
219 my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
223 unless ($branchcategories{'codedescription'} eq 'text') {
224 print "Replacing branchholding in branchcategories with codedescription text.\n";
225 my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");