3 # This script will check for required updates to the database. Would also be a
4 # good idea to offer to do a backup at this time...
18 my $mysqlversion=`mysqld --version`;
19 $mysqlversion=~/Ver (\S*) /;
21 if ($mysqlversion ge '3.23') {
22 print "Could convert to MyISAM database tables...\n";
25 my $sth=$dbh->prepare("show tables");
27 while (my ($table) = $sth->fetchrow) {
32 # Add tables for virtual bookshelf management
34 unless ($tables{'shelfcontents'}) {
35 print "Adding shelfcontents table...\n";
36 my $sti=$dbh->prepare("create table shelfcontents (shelfnumber int not null, itemnumber int not null, flags int)");
39 unless ($tables{'bookshelf'}) {
40 print "Adding bookshelf table...\n";
41 my $sti=$dbh->prepare("create table bookshelf (shelfnumber int auto_increment primary key, shelfname char(255))");
45 # Add tables required by Z-3950 scripts
47 unless ($tables{'z3950queue'}) {
48 print "Adding z3950queue table...\n";
49 my $sti=$dbh->prepare("create table z3950queue (id int auto_increment primary key, term text, type char(10), startdate int, enddate int, done smallint, results longblob, numercords int, servers text, identifier char(30))");
53 unless ($tables{'z3950results'}) {
54 print "Adding z3950results table...\n";
55 my $sti=$dbh->prepare("create table z3950results (id int auto_increment primary key, queryid int, server char(255), startdate int, enddate int, results longblob, numrecords int, numdownloaded int, highestseen int, active smallint)");
58 unless ($tables{'z3950servers'}) {
59 print "Adding z3950servers table...\n";
60 my $sti=$dbh->prepare("create table z3950servers (host char(255), port int, db char(255), userid char(255), password char(255), name text, id int, checked smallint, rank int)");
62 $sti=$dbh->prepare("insert into z3950servers values ('z3950.loc.gov', 7090, 'voyager', '', '', 'Library of Congress', 1, 1, 1)");
68 # Get list of columns from biblioitems table
70 my $sth=$dbh->prepare("show columns from biblioitems");
72 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
73 $types{$column}=$type;
75 unless ($types{'lccn'}) {
76 # Add LCCN field to biblioitems db
77 print "Adding lccn field to biblioitems table...\n";
78 my $sti=$dbh->prepare("alter table biblioitems add column lccn char(25)");
81 unless ($types{'marc'}) {
82 # Add MARC field to biblioitems db (not used anymore)
83 print "Adding marc field to biblioitems table...\n";
84 my $sti=$dbh->prepare("alter table biblioitems add column marc text");
88 # Get list of columns from biblioitems table
91 my $sth=$dbh->prepare("show columns from items");
93 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
94 $itemtypes{$column}=$type;
97 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
98 $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
101 print "Setting maximum barcode length to 20 (was $oldlength).\n";
102 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
107 # extending the timestamp in branchtransfers...
110 my $sth=$dbh->prepare("show columns from branchtransfers");
112 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
113 $branchtransfers{$column}=$type;
116 unless ($branchtransfers{'datesent'} eq 'datetime') {
117 print "Setting type of datesent in branchtransfers to datetime.\n";
118 my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
122 unless ($branchtransfers{'datearrived'} eq 'datetime') {
123 print "Setting type of datearrived in branchtransfers to datetime.\n";
124 my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
128 # changing the branchcategories table around...
129 my %branchcategories;
131 my $sth=$dbh->prepare("show columns from branchcategories");
133 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
134 $branchcategories{$column}=$type;
137 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
138 print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
139 my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
141 $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
145 unless ($branchcategories{'branchcode'} eq 'varchar(4)') {
146 print "Setting type of branchcode in branchcategories to varchar(4).\n";
147 my $sti=$dbh->prepare("alter table branchcategories change branchcode branchcode varchar(4)");
151 unless ($branchcategories{'codedescription'} eq 'text') {
152 print "Replacing branchholding in branchcategories with codedescription text.\n";
153 my $sti=$dbh->prepare("alter table branchcategories change branchholding codedescription text");