X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=updater%2Fupdatedatabase;h=963bab87ad3a9789f8e40b94bc7512c83cf4ebe9;hb=1a3e1c2248238d5a4a4625d53c34ec52210bb94b;hp=0961c65704b15aa23e523e094875c5563a22f167;hpb=9b815d5af32ebffab32146990ade3a54c0533193;p=koha.git diff --git a/updater/updatedatabase b/updater/updatedatabase index 0961c65704..963bab87ad 100755 --- a/updater/updatedatabase +++ b/updater/updatedatabase @@ -3,12 +3,12 @@ # $Id$ # Database Updater -# This script checks for required updates to the database. +# This script checks for required updates to the database. # Part of the Koha Library Software www.koha.org # Licensed under the GPL. -# Bugs/ToDo: +# 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. @@ -19,117 +19,494 @@ use strict; use DBI; # Koha modules -use C4::Database; +use C4::Context("/etc/koha.conf.tmp"); -my $debug=0; +# FIXME - /etc/koha.conf might not exist, so shouldn't use +# C4::Context. + +# FIXME - The user might be installing a new database, so can't rely +# on /etc/koha.conf anyway. + +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 + $sth, $sti, + $query, + %existingtables, # tables already in database + %types, + $table, + $column, + $type, $null, $key, $default, $extra, + $prefitem, # preference item in systempreferences table ); +my $dbh = C4::Context->dbh; + #------------------- # 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 '' )", +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 %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)',}, - aqorders=>{'budgetdate' => 'date'}, ); -# Default system preferences -my %defaultprefs=( - 'autoMemberNum'=> '1', - 'acquisitions'=> 'simple', +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 -my $dbh=C4Connect; # Start checking # Get version of MySQL database engine. -my $mysqlversion=`mysqld --version`; -$mysqlversion=~/Ver (\S*) /; -$mysqlversion=$1; -if ($mysqlversion ge '3.23') { +my $mysqlversion = `mysqld --version`; +$mysqlversion =~ /Ver (\S*) /; +$mysqlversion = $1; +if ( $mysqlversion ge '3.23' ) { print "Could convert to MyISAM database tables...\n"; } @@ -137,47 +514,58 @@ if ($mysqlversion ge '3.23') { # Tables # Collect all tables into a list -$sth=$dbh->prepare("show tables"); +$sth = $dbh->prepare("show tables"); $sth->execute; -while (my ($table) = $sth->fetchrow) { - $existingtables{$table}=1; +while ( my ($table) = $sth->fetchrow ) { + $existingtables{$table} = 1; } # 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 - -unless ($existingtables{'z3950servers'}) { + 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 ( $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; } @@ -186,125 +574,360 @@ unless ($existingtables{'z3950servers'}) { foreach $table ( keys %requirefields ) { print "Check table $table\n" if $debug; - $sth=$dbh->prepare("show columns from $table"); + $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} ) { - # 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 + 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} ) { + + # 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 + +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; -my $sth=$dbh->prepare("show columns from items"); +$sth = $dbh->prepare("show columns from items"); $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 ) +{ + $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; +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; -my $sth=$dbh->prepare("show columns from branchtransfers"); +$sth = $dbh->prepare("show columns from branchtransfers"); $sth->execute; -while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) { - $branchtransfers{$column}=$type; +while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) +{ + $branchtransfers{$column} = $type; } -unless ($branchtransfers{'datesent'} eq 'datetime') { +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"); + my $sti = + $dbh->prepare( + "alter table branchtransfers change datesent datesent datetime"); $sti->execute; } -unless ($branchtransfers{'datearrived'} eq 'datetime') { +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"); + my $sti = + $dbh->prepare( + "alter table branchtransfers change datearrived datearrived datetime"); $sti->execute; } # changing the branchcategories table around... my %branchcategories; -my $sth=$dbh->prepare("show columns from branchcategories"); +$sth = $dbh->prepare("show columns from branchcategories"); $sth->execute; -while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) { - $branchcategories{$column}=$type; +while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) +{ + $branchcategories{$column} = $type; } -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"); +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 = + $dbh->prepare( + "alter table branchcategories add primary key (categorycode)"); $sti->execute; } -unless ($branchcategories{'categoryname'} eq 'text') { +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"); + 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"); +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 systempreferences if it is empty - -foreach $prefitem ( keys %defaultprefs ) { - $sth=$dbh->prepare("select value - from systempreferences - where variable=?"); - $sth->execute($prefitem); - unless ($sth->rows) { - print "Adding system preference item $prefitem with value " . - $defaultprefs{$prefitem} ."\n"; - $sti=$dbh->prepare(" - insert into systempreferences (variable, value) - values (?,?)"); - $sti->execute($prefitem,$defaultprefs{$prefitem}); - } # unless -} # foreach - +# 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