#!/usr/bin/perl
-# This script will check for required updates to the database. Would also be a
-# good idea to offer to do a backup at this time...
+# $Id$
+# Database Updater
+# This script checks for required updates to the database.
-use C4::Database;
-use C4::Catalogue;
+# Part of the Koha Library Software www.koha.org
+# Licensed under the GPL.
+
+# Bugs/ToDo:
+# - Would also be a good idea to offer to do a backup at this time...
+
+# NOTE: If you do something more than once in here, make it table driven.
+
+use strict;
+
+# CPAN modules
use DBI;
-use C4::Acquisitions;
-use C4::Output;
-my $dbh=C4Connect;
-my %tables;
-my %types;
+# Koha modules
+use C4::Context("/etc/koha.conf.tmp");
+# FIXME - /etc/koha.conf might not exist, so shouldn't use
+# C4::Context.
-my $mysqlversion=`mysqld --version`;
-$mysqlversion=~/Ver (\S*) /;
-$mysqlversion=$1;
-if ($mysqlversion ge '3.23') {
- print "Could convert to MyISAM database tables...\n";
-}
+# FIXME - The user might be installing a new database, so can't rely
+# on /etc/koha.conf anyway.
-my $sth=$dbh->prepare("show tables");
-$sth->execute;
-while (my ($table) = $sth->fetchrow) {
- $tables{$table}=1;
-}
+my $debug = 0;
+my (
+ $sth, $sti,
+ $query,
+ %existingtables, # tables already in database
+ %types,
+ $table,
+ $column,
+ $type, $null, $key, $default, $extra,
+ $prefitem, # preference item in systempreferences table
+);
-# Add tables for virtual bookshelf management
+my $dbh = C4::Context->dbh;
-unless ($tables{'shelfcontents'}) {
- print "Adding shelfcontents table...\n";
- my $sti=$dbh->prepare("create table shelfcontents (shelfnumber int not null, itemnumber int not null, flags int)");
- $sti->execute;
-}
-unless ($tables{'bookshelf'}) {
- print "Adding bookshelf table...\n";
- my $sti=$dbh->prepare("create table bookshelf (shelfnumber int auto_increment primary key, shelfname char(255))");
- $sti->execute;
+#-------------------
+# Defines
+
+# Tables to add if they don't exist
+my %requiretables = (
+ shelfcontents => "( shelfnumber int not null,
+ itemnumber int not null,
+ flags int)",
+ bookshelf => "( shelfnumber int auto_increment primary key,
+ shelfname char(255))",
+ z3950queue => "( id int auto_increment primary key,
+ term text,
+ type char(10),
+ startdate int,
+ enddate int,
+ done smallint,
+ results longblob,
+ numrecords int,
+ servers text,
+ identifier char(30))",
+ 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)",
+ branchrelations => "( branchcode varchar(4),
+ categorycode varchar(4))",
+ websites => "( websitenumber int(11) NOT NULL auto_increment,
+ biblionumber int(11) NOT NULL default '0',
+ title text,
+ description text,
+ url varchar(255),
+ PRIMARY KEY (websitenumber) )",
+ marcrecorddone => "( isbn char(40),
+ issn char(40),
+ lccn char(40),
+ controlnumber char(40))",
+ uploadedmarc => "( id int(11) NOT NULL auto_increment PRIMARY KEY,
+ marc longblob,
+ hidden smallint(6) default NULL,
+ name varchar(255) default NULL)",
+ ethnicity => "( code varchar(10) NOT NULL default '',
+ name varchar(255) default NULL,
+ PRIMARY KEY (code) )",
+ sessions => "( sessionID varchar(255) NOT NULL default '',
+ userid varchar(255) default NULL,
+ ip varchar(16) default NULL,
+ lasttime int,
+ PRIMARY KEY (sessionID) )",
+ sessionqueries => "( sessionID varchar(255) NOT NULL default '',
+ userid char(100) NOT NULL default '',
+ ip char(18) NOT NULL default '',
+ url text NOT NULL default '' )",
+ bibliothesaurus => "( id bigint(20) NOT NULL auto_increment,
+ freelib char(255) NOT NULL default '',
+ stdlib char(255) NOT NULL default '',
+ category char(10) NOT NULL default '',
+ level tinyint(4) NOT NULL default '1',
+ hierarchy char(80) NOT NULL default '',
+ father bigint(20) NOT NULL default '',
+ PRIMARY KEY (id),
+ KEY freelib (freelib),
+ KEY stdlib (stdlib),
+ KEY category (category),
+ KEY hierarchy (hierarchy)
+ )",
+ marc_biblio => "(
+ bibid bigint(20) unsigned NOT NULL auto_increment,
+ biblionumber int(11) NOT NULL default '0',
+ datecreated date NOT NULL default '0000-00-00',
+ datemodified date default NULL,
+ origincode char(20) default NULL,
+ PRIMARY KEY (bibid),
+ KEY origincode (origincode),
+ KEY biblionumber (biblionumber)
+ ) ",
+ marc_blob_subfield => "(
+ blobidlink bigint(20) NOT NULL auto_increment,
+ subfieldvalue longtext NOT NULL,
+ PRIMARY KEY (blobidlink)
+ ) ",
+ marc_subfield_structure => "(
+ tagfield char(3) NOT NULL default '',
+ tagsubfield char(1) NOT NULL default '',
+ liblibrarian char(255) NOT NULL default '',
+ libopac char(255) NOT NULL default '',
+ repeatable tinyint(4) NOT NULL default '0',
+ mandatory tinyint(4) NOT NULL default '0',
+ kohafield char(40) default NULL,
+ tab tinyint(1) default NULL,
+ authorised_value char(10) default NULL,
+ thesaurus_category char(10) default NULL,
+ value_builder char(80) default NULL,
+ PRIMARY KEY (tagfield,tagsubfield),
+ KEY kohafield (kohafield),
+ KEY tab (tab)
+ )",
+ marc_subfield_table => "(
+ subfieldid bigint(20) unsigned NOT NULL auto_increment,
+ bibid bigint(20) unsigned NOT NULL default '0',
+ tag char(3) NOT NULL default '',
+ tagorder tinyint(4) NOT NULL default '1',
+ tag_indicator char(2) NOT NULL default '',
+ subfieldcode char(1) NOT NULL default '',
+ subfieldorder tinyint(4) NOT NULL default '1',
+ subfieldvalue varchar(255) default NULL,
+ valuebloblink bigint(20) default NULL,
+ PRIMARY KEY (subfieldid),
+ KEY bibid (bibid),
+ KEY tag (tag),
+ KEY tag_indicator (tag_indicator),
+ KEY subfieldorder (subfieldorder),
+ KEY subfieldcode (subfieldcode),
+ KEY subfieldvalue (subfieldvalue),
+ KEY tagorder (tagorder)
+ )",
+ marc_tag_structure => "(
+ tagfield char(3) NOT NULL default '',
+ liblibrarian char(255) NOT NULL default '',
+ libopac char(255) NOT NULL default '',
+ repeatable tinyint(4) NOT NULL default '0',
+ mandatory tinyint(4) NOT NULL default '0',
+ authorised_value char(10) default NULL,
+ PRIMARY KEY (tagfield)
+ )",
+ marc_word => "(
+ bibid bigint(20) NOT NULL default '0',
+ tag char(3) NOT NULL default '',
+ tagorder tinyint(4) NOT NULL default '1',
+ subfieldid char(1) NOT NULL default '',
+ subfieldorder tinyint(4) NOT NULL default '1',
+ word varchar(255) NOT NULL default '',
+ sndx_word varchar(255) NOT NULL default '',
+ KEY bibid (bibid),
+ KEY tag (tag),
+ KEY tagorder (tagorder),
+ KEY subfieldid (subfieldid),
+ KEY subfieldorder (subfieldorder),
+ KEY word (word),
+ KEY sndx_word (sndx_word)
+ )",
+ marc_breeding => "( id bigint(20) NOT NULL auto_increment,
+ file varchar(80) NOT NULL default '',
+ isbn varchar(10) NOT NULL default '',
+ title varchar(128) default NULL,
+ author varchar(80) default NULL,
+ marc text NOT NULL,
+ encoding varchar(40) default NULL,
+ PRIMARY KEY (id),
+ KEY title (title),
+ KEY isbn (isbn)
+ )",
+ authorised_values => "(id int(11) NOT NULL auto_increment,
+ category char(10) NOT NULL default '',
+ authorised_value char(80) NOT NULL default '',
+ lib char(80) NULL,
+ PRIMARY KEY (id),
+ KEY name (category)
+ )",
+ userflags => "( bit int(11) NOT NULL default '0',
+ flag char(30), flagdesc char(255),
+ defaulton int(11)
+ )",
+);
+
+my %requirefields = (
+ biblio => { 'abstract' => 'text' },
+ deletedbiblio => { 'abstract' => 'text' },
+ biblioitems => {
+ 'lccn' => 'char(25)',
+ 'url' => 'varchar(255)',
+ 'marc' => 'text'
+ },
+ deletedbiblioitems => {
+ 'lccn' => 'char(25)',
+ 'url' => 'varchar(255)',
+ 'marc' => 'text'
+ },
+ branchtransfers => { 'datearrived' => 'datetime' },
+ statistics => { 'borrowernumber' => 'int(11)' },
+ aqbooksellers => {
+ 'invoicedisc' => 'float(6,4)',
+ 'nocalc' => 'int(11)'
+ },
+ borrowers => {
+ 'userid' => 'char(30)',
+ 'password' => 'char(30)',
+ 'flags' => 'int(11)',
+ 'textmessaging' => 'varchar(30)'
+ },
+ aqorders => { 'budgetdate' => 'date' },
+
+ #added so that reference items are not available for reserves...
+ itemtypes => { 'notforloan' => 'smallint(6)' },
+ systempreferences => { 'explanation' => 'char(80)' },
+ z3950servers => { 'syntax' => 'char(80)' },
+ borrowers => {'zipcode' => 'varchar(25)',
+ 'homezipcode' => 'varchar(25)'}
+);
+
+my %dropable_table = (
+ classification => 'classification',
+ multipart => 'multipart',
+ multivolume => 'multivolume',
+ newitems => 'newitems',
+ procedures => 'procedures',
+ publisher => 'publisher',
+ searchstats => 'searchstats',
+ serialissues => 'serialissues',
+);
+
+# The tabledata hash contains data that should be in the tables.
+# The uniquefieldrequired hash entry is used to determine which (if any) fields
+# must not exist in the table for this row to be inserted. If the
+# uniquefieldrequired entry is already in the table, the existing data is not
+# modified.
+
+my %tabledata = (
+ userflags => [
+ {
+ uniquefieldrequired => 'bit',
+ bit => 0,
+ flag => 'superlibrarian',
+ flagdesc => 'Access to all librarian functions',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 1,
+ flag => 'circulate',
+ flagdesc => 'Circulate books',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 2,
+ flag => 'catalogue',
+ flagdesc => 'View Catalogue (Librarian Interface)',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 3,
+ flag => 'parameters',
+ flagdesc => 'Set Koha system paramters',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 4,
+ flag => 'borrowers',
+ flagdesc => 'Add or modify borrowers',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 5,
+ flag => 'permissions',
+ flagdesc => 'Set user permissions',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 6,
+ flag => 'reserveforothers',
+ flagdesc => 'Reserve books for patrons',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 7,
+ flag => 'borrow',
+ flagdesc => 'Borrow books',
+ defaulton => 1
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 8,
+ flag => 'reserveforself',
+ flagdesc => 'Reserve books for self',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 9,
+ flag => 'editcatalogue',
+ flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
+ defaulton => 0
+ },
+ {
+ uniquefieldrequired => 'bit',
+ bit => 10,
+ flag => 'updatecharges',
+ flagdesc => 'Update borrower charges',
+ defaulton => 0
+ },
+ ],
+ systempreferences => [
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'autoMemberNum',
+ value => '1',
+ explanation => '1 or 0. If 1, Member number is auto-calculated'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'acquisitions',
+ value => 'simple',
+ explanation =>
+'normal or simple : whether to use "acqui" or "acqui.simple" acquisition system'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'dateformat',
+ value => 'metric',
+ explanation => 'metric, us, or iso'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'template',
+ value => 'default',
+ explanation => 'template default name'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'autoBarcode',
+ value => '1',
+ explanation => '1 or 0. If 1, Barcode is auto-calculated'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'insecure',
+ value => 'NO',
+ explanation =>
+'if YES, no auth at all is needed. Be careful if you set this to yes !'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'authoritysep',
+ value => '--',
+ explanation =>
+ 'the separator used in authority/thesaurus. Usually --'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'opaclanguages',
+ value => 'en',
+ explanation => 'languages'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'opacthemes',
+ value => 'default',
+ explanation => 'theme'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'timeout',
+ value => '12000000',
+ explanation => 'login timeout'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'marc',
+ value => 'ON',
+ explanation => 'MARC support (ON or OFF)'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'marcflavour',
+ value => 'MARC21',
+ explanation =>
+ 'your MARC flavor (MARC21 or UNIMARC) used for character encoding'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'checkdigit',
+ value => 'katipo',
+ explanation =>
+ 'none= no check on member cardnumber. katipo= katipo check'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'dateformat',
+ value => 'ISO',
+ explanation =>
+ 'date format (US mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd) '
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'maxoutstanding',
+ value => '5',
+ explanation =>
+ 'maximum amount withstanding to be able make reserves '
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'maxreserves',
+ value => '5',
+ explanation =>
+ 'maximum number of reserves a member can make '
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'noissuescharge',
+ value => '5',
+ explanation =>
+ 'maximum amount withstanding to be able to check out an item '
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'KohaAdminEmailAddress',
+ value => 'your.mail@here',
+ explanation => 'the email adress where borrowers modifs are sent'
+ },
+ ],
+
+);
+
+my %fielddefinitions = (
+ printers => [
+ {
+ field => 'printername',
+ type => 'char(40)',
+ null => '',
+ key => 'PRI',
+ default => ''
+ },
+ ],
+ aqbookfund => [
+ {
+ field => 'bookfundid',
+ type => 'char(5)',
+ null => '',
+ key => 'PRI',
+ default => ''
+ },
+ ],
+ z3950servers => [
+ {
+ field => 'id',
+ type => 'int',
+ null => '',
+ key => 'PRI',
+ default => '',
+ extra => 'auto_increment'
+ },
+ ],
+);
+
+#-------------------
+# Initialize
+
+# Start checking
+
+# Get version of MySQL database engine.
+my $mysqlversion = `mysqld --version`;
+$mysqlversion =~ /Ver (\S*) /;
+$mysqlversion = $1;
+if ( $mysqlversion ge '3.23' ) {
+ print "Could convert to MyISAM database tables...\n";
}
-# Add tables required by Z-3950 scripts
+#---------------------------------
+# Tables
-unless ($tables{'z3950queue'}) {
- print "Adding z3950queue table...\n";
- 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))");
- $sti->execute;
+# Collect all tables into a list
+$sth = $dbh->prepare("show tables");
+$sth->execute;
+while ( my ($table) = $sth->fetchrow ) {
+ $existingtables{$table} = 1;
}
-unless ($tables{'z3950results'}) {
- print "Adding z3950results table...\n";
- 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)");
- $sti->execute;
+# Now add any missing tables
+foreach $table ( keys %requiretables ) {
+ print "Checking $table table...\n" if $debug;
+ unless ( $existingtables{$table} ) {
+ print "Adding $table table...\n";
+ my $sth = $dbh->prepare("create table $table $requiretables{$table}");
+ $sth->execute;
+ if ( $sth->err ) {
+ print "Error : $sth->errstr \n";
+ $sth->finish;
+ } # if error
+ } # unless exists
+} # foreach
+
+# now drop useless tables
+foreach $table ( keys %dropable_table ) {
+ print "Dropping unused tables...\n" if $debug;
+ if ( $existingtables{$table} ) {
+ $dbh->do("drop table $table");
+ if ( $dbh->err ) {
+ print "Error : $dbh->errstr \n";
+ }
+ }
}
-unless ($tables{'z3950servers'}) {
+unless ( $existingtables{'z3950servers'} ) {
print "Adding z3950servers table...\n";
- 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)");
+ 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)"
+ );
$sti->execute;
- $sti=$dbh->prepare("insert into z3950servers values ('z3950.loc.gov', 7090, 'voyager', '', '', 'Library of Congress', 1, 1, 1)");
+ $sti = $dbh->prepare( "insert into z3950servers
+ values ('z3950.loc.gov',
+ 7090,
+ 'voyager',
+ '', '',
+ 'Library of Congress',
+ 1, 1, 1)"
+ );
$sti->execute;
}
+#---------------------------------
+# Columns
+foreach $table ( keys %requirefields ) {
+ print "Check table $table\n" if $debug;
+ $sth = $dbh->prepare("show columns from $table");
+ $sth->execute();
+ undef %types;
+ while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $types{$column} = $type;
+ } # while
+ foreach $column ( keys %{ $requirefields{$table} } ) {
+ print " Check column $column\n" if $debug;
+ if ( !$types{$column} ) {
-# Get list of columns from biblioitems table
+ # column doesn't exist
+ print "Adding $column field to $table table...\n";
+ $query = "alter table $table
+ add column $column " . $requirefields{$table}->{$column};
+ print "Execute: $query\n" if $debug;
+ my $sti = $dbh->prepare($query);
+ $sti->execute;
+ if ( $sti->err ) {
+ print "**Error : $sti->errstr \n";
+ $sti->finish;
+ } # if error
+ } # if column
+ } # foreach column
+} # foreach table
-my $sth=$dbh->prepare("show columns from biblioitems");
+foreach $table ( keys %fielddefinitions ) {
+ print "Check table $table\n" if $debug;
+ $sth = $dbh->prepare("show columns from $table");
+ $sth->execute();
+ my $definitions;
+ while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $definitions->{$column}->{type} = $type;
+ $definitions->{$column}->{null} = $null;
+ $definitions->{$column}->{key} = $key;
+ $definitions->{$column}->{default} = $default;
+ $definitions->{$column}->{extra} = $extra;
+ } # while
+ my $fieldrow = $fielddefinitions{$table};
+ foreach my $row (@$fieldrow) {
+ my $field = $row->{field};
+ my $type = $row->{type};
+ my $null = $row->{null};
+ my $key = $row->{key};
+ my $default = $row->{default};
+ my $extra = $row->{extra};
+ my $def = $definitions->{$field};
+ unless ( $type eq $def->{type}
+ && $null eq $def->{null}
+ && $key eq $def->{key}
+ && $default eq $def->{default}
+ && $extra eq $def->{extra} )
+ {
+
+ if ( $null eq '' ) {
+ $null = 'NOT NULL';
+ }
+ if ( $key eq 'PRI' ) {
+ $key = 'PRIMARY KEY';
+ }
+ unless ( $extra eq 'auto_increment' ) {
+ $extra = '';
+ }
+ my $sth =
+ $dbh->prepare(
+"alter table $table change $field $field $type $null $key $extra default ?"
+ );
+ $sth->execute($default);
+ print " Alter $field in $table\n";
+ }
+ }
+}
+
+# Get list of columns from items table
+my %itemtypes;
+
+$sth = $dbh->prepare("show columns from items");
+$sth->execute;
+while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+{
+ $itemtypes{$column} = $type;
+}
+
+unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
+ $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
+ my $oldlength = $1;
+ if ( $oldlength < 20 ) {
+ print "Setting maximum barcode length to 20 (was $oldlength).\n";
+ my $sti =
+ $dbh->prepare(
+ "alter table items change barcode barcode varchar(20) not null");
+ $sti->execute;
+ }
+}
+
+# extending the timestamp in branchtransfers...
+my %branchtransfers;
+
+$sth = $dbh->prepare("show columns from branchtransfers");
$sth->execute;
-while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $types{$column}=$type;
+while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+{
+ $branchtransfers{$column} = $type;
}
-unless ($types{'lccn'}) {
- # Add LCCN field to biblioitems db
- print "Adding lccn field to biblioitems table...\n";
- my $sti=$dbh->prepare("alter table biblioitems add column lccn char(25)");
+
+unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
+ print "Setting type of datesent in branchtransfers to datetime.\n";
+ my $sti =
+ $dbh->prepare(
+ "alter table branchtransfers change datesent datesent datetime");
$sti->execute;
}
-unless ($types{'marc'}) {
- # Add MARC field to biblioitems db (not used anymore)
- print "Adding marc field to biblioitems table...\n";
- my $sti=$dbh->prepare("alter table biblioitems add column marc text");
+
+unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
+ print "Setting type of datearrived in branchtransfers to datetime.\n";
+ my $sti =
+ $dbh->prepare(
+ "alter table branchtransfers change datearrived datearrived datetime");
$sti->execute;
}
-# Get list of columns from biblioitems table
-my %itemtypes;
+# changing the branchcategories table around...
+my %branchcategories;
-my $sth=$dbh->prepare("show columns from items");
+$sth = $dbh->prepare("show columns from branchcategories");
$sth->execute;
-while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $itemtypes{$column}=$type;
+while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+{
+ $branchcategories{$column} = $type;
}
-unless ($itemtypes{'barcode'} eq 'varchar(20)') {
- $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
- my $oldlength=$1;
- if ($oldlength<20) {
- print "Setting maximum barcode length to 20 (was $oldlength).\n";
- my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
- $sti->execute;
- }
+unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
+ print
+"Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
+ my $sti =
+ $dbh->prepare(
+"alter table branchcategories change categorycode categorycode varchar(4) not null"
+ );
+ $sti->execute;
+ $sti =
+ $dbh->prepare(
+ "alter table branchcategories add primary key (categorycode)");
+ $sti->execute;
+}
+
+unless ( $branchcategories{'categoryname'} eq 'text' ) {
+ print "Changing branchcode in branchcategories to categoryname text.\n";
+ my $sth =
+ $dbh->prepare(
+ "alter table branchcategories change branchcode categoryname text");
+ $sth->execute;
}
+unless ( $branchcategories{'codedescription'} eq 'text' ) {
+ print
+"Replacing branchholding in branchcategories with codedescription text.\n";
+ my $sth =
+ $dbh->prepare(
+ "alter table branchcategories change branchholding codedescription text"
+ );
+ $sth->execute;
+}
+
+# Populate tables with required data
+
+foreach my $table ( keys %tabledata ) {
+ print "Checking for data required in table $table...\n";
+ my $tablerows = $tabledata{$table};
+ foreach my $row (@$tablerows) {
+ my $uniquefieldrequired = $row->{uniquefieldrequired};
+ my $uniquevalue = $row->{$uniquefieldrequired};
+ my $sth =
+ $dbh->prepare(
+"select $uniquefieldrequired from $table where $uniquefieldrequired=?"
+ );
+ $sth->execute($uniquevalue);
+ unless ( $sth->rows ) {
+ print "Adding row to $table: ";
+ my @values;
+ my $fieldlist;
+ my $placeholders;
+ foreach my $field ( keys %$row ) {
+ (next) if ( $field eq 'uniquefieldrequired' );
+ my $value = $row->{$field};
+ push @values, $value;
+ print " $field => $value";
+ $fieldlist .= "$field,";
+ $placeholders .= "?,";
+ }
+ print "\n";
+ $fieldlist =~ s/,$//;
+ $placeholders =~ s/,$//;
+ my $sth =
+ $dbh->prepare(
+ "insert into $table ($fieldlist) values ($placeholders)");
+ $sth->execute(@values);
+ }
+ }
+}
$sth->finish;
-$dbh->disconnect;
+
+exit;
+
+# $Log$
+# Revision 1.47 2003/05/15 12:23:33 tipaul
+# adding zipcode and homezipcode into borrowers table (bug #246
+#
+# Revision 1.46 2003/05/08 12:48:24 wolfpac444
+# Added "noissuescharge" parameter
+#
+# Revision 1.45 2003/05/08 12:26:16 wolfpac444
+# Bug fixes
+#
+# Revision 1.44 2003/05/03 05:39:57 rangi
+# Fixing bug 429
+# (Wording changes in the explanation fields in system preferences)
+#
+# Revision 1.43 2003/05/02 23:01:09 rangi
+# Adding the textmessaging column to the borrowers table.
+# insertdata.pl is expecting this to exist, and hence modifying/adding
+# borrowers was broken.
+#
+# Also ran they script thru perltidy
+#
+# Revision 1.42 2003/04/29 16:53:25 tipaul
+# really proud of this commit :-)
+# z3950 search and import seems to works fine.
+# Let me explain how :
+# * a "search z3950" button is added in the addbiblio template.
+# * when clicked, a popup appears and z3950/search.pl is called
+# * z3950/search.pl calls addz3950search in the DB
+# * the z3950 daemon retrieve the records and stores them in z3950results AND in marc_breeding table.
+# * as long as there as searches pending, the popup auto refresh every 2 seconds, and says how many searches are pending.
+# * when the user clicks on a z3950 result => the parent popup is called with the requested biblio, and auto-filled
+#
+# Note :
+# * character encoding support : (It's a nightmare...) In the z3950servers table, a "encoding" column has been added. You can put "UNIMARC" or "USMARC" in this column. Depending on this, the char_decode in C4::Biblio.pm replaces marc-char-encode by an iso 8859-1 encoding. Note that in the breeding import this value has been added too, for a better support.
+# * the marc_breeding and z3950* tables have been modified : they have an encoding column and the random z3950 number is stored too for convenience => it's the key I use to list only requested biblios in the popup.
+#
+# Revision 1.41 2003/04/29 08:09:44 tipaul
+# z3950 support is coming...
+# * adding a syntax column in z3950 table = this column will say wether the z3950 must be called with PerferedRecordsyntax => USMARC or PerferedRecordsyntax => UNIMARC. I tried some french UNIMARC z3950 servers, and some only send USMARC, some only UNIMARC, some can answer with both.
+# Note this is a 1st draft. More to follow (today ? I hope).
+#
+# Revision 1.40 2003/04/22 10:48:27 wolfpac444
+# Added "father" column to bibliothesaurus table
+#
+# Revision 1.39 2003/04/04 08:45:00 tipaul
+# last commits before 1.9.1
+#
+# Revision 1.38 2003/03/18 10:58:19 tipaul
+# adding checkdigit parameter that choose how to check the members cardnumber.
+# At the moment :
+# * none = no checking
+# * katipo = checked as before
+#
+# Revision 1.37 2003/01/30 01:47:48 acli
+# Corrected syntax error reported by Benedict
+#
+# Made the indentation somewhat easier to read; the messiness probably caused
+# the original syntax error.
+#
+# Revision 1.36 2003/01/28 15:13:30 tipaul
+# userflag table now created in upgrade script (bugfix #171)
+#
+# Revision 1.35 2003/01/27 03:12:49 acli
+# Reworded the description for "acquisitions" to make it fit on the screen
+#
+# Added "iso" to dateformat, since dateformat is not yet being used anyway
+#
+# Revision 1.34 2003/01/23 12:30:02 tipaul
+# introducint marcflavour in systempref file : used for character decoding
+#
+# Revision 1.33 2003/01/21 09:03:27 tipaul
+# bugfix (NOTE : this bugs makes installation of the 1.3.3 a little fuzzy. Please fix your DB if you installed 1.3.3)
+#
+# Revision 1.32 2003/01/16 10:29:45 tipaul
+# adding a MARC parameter in systempref ( which is ON or OFF)
+# the search will be a marc search if MARC=ON
+# and a standard (v1.2) search if MARC=OFF
+#
+# Revision 1.31 2003/01/06 13:32:43 tipaul
+# *** empty log message ***
+#
+# Revision 1.29 2003/01/06 11:14:11 tipaul
+# last bugfixes before 1.3.3 : systempref table correctly filled
+#
+# Revision 1.28 2002/12/10 13:27:47 tipaul
+# bugfixes (davide mails in koha-dev)
+#
+# Revision 1.27 2002/11/26 15:04:54 tipaul
+# road to 1.3.2. Updating db structure during installation
+#
+# Revision 1.26 2002/11/12 17:42:40 tonnesen
+# Merged some features over from rel-1-2, including primary key checking.
+#
+# Revision 1.25 2002/11/12 16:44:38 tipaul
+# road to 1.3.2 :
+# * many bugfixes
+# * adding value_builder : you can map a subfield in the marc_subfield_structure to a sub stored in "value_builder" directory. In this directory you can create screen used to build values with any method. In this commit is a 1st draft of the builder for 100$a unimarc french subfield, which is composed of 35 digits, with 12 differents values (only the 4th first are provided for instance)
+#
+# Revision 1.24 2002/10/30 14:00:23 arensb
+# (bug fix): Fixed typo.
+#
+# Revision 1.23 2002/10/25 10:55:46 tipaul
+# Road to 1.3.2
+# * bugfixes and improvements
+# * manage mandatory MARC subfields
+# * new table : authorised_values. this table contains categories and authorised values for the category. On MARC management, you can map a subfield to a authorised_values category. If you do this, the subfield can only be filled with a authorised_value of the selected category.
+# this submit contains everything needed :
+# * updatedatabase
+# * admin screens
+# * "links" management
+# * creation of a html-list if a subfield is mapped to an authorised value.
+#
+# Note this is different from authorities support, which will come soon.
+# The authorised_values is supposed to contains a "small" number of authorised values for a category (less than 50-100). If you enter more authorised values than this, it should be hard to find what you want in a BIG list...
+#
+# Revision 1.22 2002/10/15 10:08:19 tipaul
+# fixme corrected, re-indent and adding the marc_breeding table (see commit of marcimport.pl for more explanations about breeding)
+#
+# Revision 1.21 2002/10/14 11:48:59 tipaul
+# bugfix
+#
+# Revision 1.20 2002/10/10 04:49:41 arensb
+# Added some FIXME comments.
+#
+# Revision 1.19 2002/10/05 10:17:17 arensb
+# Merged with arensb-context branch: use C4::Context->dbh instead of
+# &C4Connect, and generally prefer C4::Context over C4::Database.
+#
+# Revision 1.18.2.2 2002/10/05 06:18:43 arensb
+# Added a whole mess of FIXME comments.
+#
+# Revision 1.18.2.1 2002/10/04 02:46:00 arensb
+# Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
+# C4Connect.
+#
+# Revision 1.18 2002/09/24 13:50:55 tipaul
+# long WAS the road to 1.3.0...
+# coming VERY SOON NOW...
+# modifying installer and buildrelease to update the DB
+#
+# Revision 1.17 2002/09/24 12:57:35 tipaul
+# long WAS the road to 1.3.0...
+# coming VERY SOON NOW...
+# modifying installer and buildrelease to update the DB
+#
+# Revision 1.16 2002/07/31 02:34:27 finlayt
+#
+# added "notforloan" field to the itemtypes table.
+#
+# Revision 1.15 2002/07/20 22:30:06 rangi
+# Making sure fix makes it into the main branch as well
+# Fix for bug 69
+#
+# Revision 1.14 2002/07/08 16:20:26 tonnesen
+# Added sessionqueries table and password/userid fields to borrowers table
+#
+# Revision 1.13 2002/07/04 18:05:36 tonnesen
+# bug fix
+#
+# Revision 1.12 2002/07/04 16:41:06 tonnesen
+# Merged changes from rel-1-2. Abstracted table structure changes by alan.
+#
projects / koha.git / blobdiff