X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fupdate22to30.pl;h=d38f080bcccc389b30e41419ac992b0536c2c538;hb=13faff1250ed3ede712c405821f3ad9187e892b6;hp=baf17e22c83fd11590959cccc08b5de302a6ae08;hpb=c87666215df3f00f5fe652d1dde36e7a8b24e993;p=koha.git diff --git a/installer/data/mysql/update22to30.pl b/installer/data/mysql/update22to30.pl index baf17e22c8..d38f080bcc 100755 --- a/installer/data/mysql/update22to30.pl +++ b/installer/data/mysql/update22to30.pl @@ -4,7 +4,7 @@ # Database Updater # This script checks for required updates to the database. -# Part of the Koha Library Software www.koha.org +# Part of the Koha Library Software www.koha-community.org # Licensed under the GPL. # Bugs/ToDo: @@ -85,7 +85,7 @@ my $DBversion = "3.00.00.000"; )", repeatable_holidays => "( `id` int(11) NOT NULL auto_increment, - `branchcode` varchar(4) NOT NULL default '', + `branchcode` varchar(10) NOT NULL default '', `weekday` smallint(6) default NULL, `day` smallint(6) default NULL, `month` smallint(6) default NULL, @@ -95,7 +95,7 @@ my $DBversion = "3.00.00.000"; )", special_holidays => "( `id` int(11) NOT NULL auto_increment, - `branchcode` varchar(4) NOT NULL default '', + `branchcode` varchar(10) NOT NULL default '', `day` smallint(6) NOT NULL default '0', `month` smallint(6) NOT NULL default '0', `year` smallint(6) NOT NULL default '0', @@ -104,13 +104,13 @@ my $DBversion = "3.00.00.000"; `description` text NOT NULL, PRIMARY KEY (`id`) )", - overduerules =>"(`branchcode` varchar(255) NOT NULL default '', - `categorycode` char(2) NOT NULL default '', + overduerules =>"(`branchcode` varchar(10) NOT NULL default '', + `categorycode` varchar(2) NOT NULL default '', `delay1` int(4) default '0', `letter1` varchar(20) default NULL, - `debarred1` char(1) default '0', + `debarred1` varchar(1) default '0', `delay2` int(4) default '0', - `debarred2` char(1) default '0', + `debarred2` varchar(1) default '0', `letter2` varchar(20) default NULL, `delay3` int(4) default '0', `letter3` varchar(20) default NULL, @@ -118,17 +118,18 @@ my $DBversion = "3.00.00.000"; PRIMARY KEY (`branchcode`,`categorycode`) )", cities => "(`cityid` int auto_increment, - `city_name` char(100) NOT NULL, - `city_zipcode` char(20), + `city_name` varchar(100) NOT NULL default '', + `city_zipcode` varchar(20), PRIMARY KEY (`cityid`) )", roadtype => "(`roadtypeid` int auto_increment, - `road_type` char(100) NOT NULL, + `road_type` varchar(100) NOT NULL default '', PRIMARY KEY (`roadtypeid`) )", labels => "( labelid int(11) NOT NULL auto_increment, + batch_id varchar(10) NOT NULL default '1', itemnumber varchar(100) NOT NULL default '', timestamp timestamp(14) NOT NULL, PRIMARY KEY (labelid) @@ -137,15 +138,26 @@ my $DBversion = "3.00.00.000"; labels_conf => "( id int(4) NOT NULL auto_increment, barcodetype char(100) default '', - title tinyint(1) default '0', - isbn tinyint(1) default '0', - itemtype tinyint(1) default '0', - barcode tinyint(1) default '0', - dewey tinyint(1) default '0', - class tinyint(1) default '0', - author tinyint(1) default '0', - papertype char(100) default '', - startrow int(2) default NULL, + title int(1) default '0', + subtitle int(1) default '0', + itemtype int(1) default '0', + barcode int(1) default '0', + dewey int(1) default '0', + class int(1) default '0', + subclass int(1) default '0', + itemcallnumber int(1) default '0', + author int(1) default '0', + issn int(1) default '0', + isbn int(1) default '0', + startlabel int(2) NOT NULL default '1', + printingtype char(32) default 'BAR', + layoutname char(20) NOT NULL default 'TEST', + guidebox int(1) default '0', + active tinyint(1) default '1', + fonttype char(10) collate utf8_unicode_ci default NULL, + ccode char(4) collate utf8_unicode_ci default NULL, + callnum_split int(1) default NULL, + text_justify char(1) collate utf8_unicode_ci default NULL, PRIMARY KEY (id) )", reviews => "( @@ -169,7 +181,7 @@ my $DBversion = "3.00.00.000"; notify_id int(11) NOT NULL default '0', `borrowernumber` int(11) NOT NULL default '0', `itemnumber` int(11) NOT NULL default '0', - `notify_date` date NOT NULL default '0000-00-00', + `notify_date` date default NULL, `notify_send_date` date default NULL, `notify_level` int(1) NOT NULL default '0', `method` varchar(20) NOT NULL default '' @@ -192,29 +204,29 @@ my $DBversion = "3.00.00.000"; ", zebraqueue => "( `id` int NOT NULL auto_increment, - `biblio_auth_number` int NOT NULL, - `operation` char(20) NOT NULL, - `server` char(20) NOT NULL , + `biblio_auth_number` int(11) NOT NULL default '0', + `operation` char(20) NOT NULL default '', + `server` char(20) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1", ); my %requirefields = ( - subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date NOT NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(12) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''}, - itemtypes => { 'imageurl' => 'char(200) NULL'}, + subscription => { 'letter' => 'varchar(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date default NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(10) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''}, + itemtypes => { 'imageurl' => 'varchar(200) NULL'}, aqbookfund => { 'branchcode' => 'varchar(4) NULL'}, aqbudget => { 'branchcode' => 'varchar(4) NULL'}, auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'}, - auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => 'VARCHAR(45) NULL', 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'}, + auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => "VARCHAR(45) NULL default ''", 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'}, marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'}, - serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',}, + serial =>{ 'publisheddate' => 'date AFTER planneddate', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',}, statistics => { 'associatedborrower' => 'integer'}, z3950servers =>{ "name" =>"text", "description" => "text NOT NULL", "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text", "type" =>"enum('zed','opensearch') NOT NULL default 'zed'", }, - issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", }, + issues =>{ 'issuedate'=>"date NULL default NULL", }, # tablename => { 'field' => 'fieldtype' }, ); @@ -231,6 +243,8 @@ my $DBversion = "3.00.00.000"; # tablename => "field1,field2", borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp", deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp", + items => "multivolumepart,multivolume,binding", + deleteditems => "multivolumepart,multivolume,binding", ); # the other hash contains other actions that can't be done elsewhere. they are done # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER) @@ -431,10 +445,10 @@ my $DBversion = "3.00.00.000"; { uniquefieldrequired => 'variable', variable => 'OpacTopissue', - value => '1', + value => '0', forceupdate => { 'explanation' => 1, 'type' => 1}, - explanation => 'Enable / Disable the top issue link on OPAC', + explanation => 'If ON, enables the \'most popular items\' link on OPAC. Warning, this is an EXPERIMENTAL feature, turning ON may overload your server', type => 'YesNo', }, { @@ -586,16 +600,41 @@ my $DBversion = "3.00.00.000"; default => '1', extra => '', }, + { + field => 'booksellerinvoicenumber', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, ], aqbookfund => [ { field => 'bookfundid', - type => 'varchar(5)', + type => 'varchar(10)', null => 'NOT NULL', - key => 'PRI', - default => '', + key => '', + default => "''", extra => '', }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'bookfundname', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + after => 'bookfundid', + }, ], aqbooksellers => [ @@ -607,6 +646,14 @@ my $DBversion = "3.00.00.000"; default => '', extra => 'auto_increment', }, + { + field => 'currency', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, { field => 'listprice', type => 'varchar(10)', @@ -631,20 +678,220 @@ my $DBversion = "3.00.00.000"; default => 'NULL', extra => '', }, - + { + field => 'address1', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'address2', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'address3', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'address4', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'accountnumber', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'othersupplier', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'specialty', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'booksellerfax', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'bookselleremail', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'booksellerurl', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'contnotes', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'postal', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, ], aqbudget => [ { field => 'bookfundid', - type => 'varchar(5)', + type => 'varchar(10)', null => 'NOT NULL', key => '', + default => "''", + exra => '', + }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NULL', + key => '', default => '', exra => '', }, ], + aqorderbreakdown => [ + { + field => 'bookfundid', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", + exra => '', + }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => '', + exra => '', + }, + ], + + aqorderdelivery => [ + { + field => 'ordernumber', + type => 'date', + null => 'NULL', + key => '', + default => 'NULL', + exra => '', + }, + { + field => 'deliverycomments', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + exra => '', + }, + ], + + aqorders => [ + { + field => 'title', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + exra => '', + }, + { + field => 'currency', + type => 'varchar(3)', + null => 'NULL', + key => '', + default => 'NULL', + exra => '', + }, + { + field => 'booksellerinvoicenumber', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'supplierreference', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'purchaseordernumber', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + ], + accountlines => [ { field => 'notify_id', @@ -682,254 +929,259 @@ my $DBversion = "3.00.00.000"; }, ], - - borrowers => [ - { field => 'firstname', - type => 'text', - null => 'NULL', - }, - { field => 'initials', - type => 'text', - null => 'NULL', - }, - { field => 'B_email', - type => 'text', - null => 'NULL', - after => 'B_zipcode', - }, + + auth_header => [ { - field => 'streetnumber', # street number (hidden if streettable table is empty) - type => 'char(10)', - null => 'NULL', - after => 'initials', + field => 'authtypecode', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'streettype', # street table, list builded from a system table - type => 'char(50)', - null => 'NULL', - after => 'streetnumber', + field => 'datecreated', + type => 'date', + null => 'NULL', + key => '', + default => "NULL", + extra => '', }, - { field => 'phone', - type => 'text', - null => 'NULL', + { + field => 'origincode', + type => 'varchar(20)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', }, { - field => 'B_streetnumber', # street number (hidden if streettable table is empty) - type => 'char(10)', - null => 'NULL', - after => 'fax', + field => 'authtrees', + type => 'mediumtext', + null => 'NULL', + key => '', + default => "", + extra => '', + after => 'origincode', }, + ], + + auth_subfield_structure => [ { - field => 'B_streettype', # street table, list builded from a system table - type => 'char(50)', - null => 'NULL', - after => 'B_streetnumber', + field => 'authtypecode', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'phonepro', - type => 'text', - null => 'NULL', - after => 'fax', + field => 'tagfield', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'address2', # complement address - type => 'text', - null => 'NULL', - after => 'address', + field => 'tagsubfield', + type => 'varchar(1)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'emailpro', - type => 'text', - null => 'NULL', - after => 'fax', - }, - { - field => 'contactfirstname', # contact's firstname - type => 'text', - null => 'NULL', - after => 'contactname', - }, - { - field => 'contacttitle', # contact's title - type => 'text', - null => 'NULL', - after => 'contactfirstname', + field => 'liblibrarian', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'branchcode', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', - extra => '', + field => 'libopac', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'categorycode', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', - extra => '', - } - ], - - biblioitems => [ - { - field => 'itemtype', + field => 'authorised_value', type => 'varchar(10)', - null => 'NOT NULL', - key => '', - default => '', - extra => '', + null => 'NULL', + key => '', + default => "NULL", + extra => '', }, { - field => 'lcsort', - type => 'varchar(25)', - null => 'NULL', - key => '', - default => '', - extra => '', + field => 'value_builder', + type => 'varchar(80)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', }, { - field => 'ccode', - type => 'varchar(4)', - null => 'NULL', - key => '', - default => '', - extra => '', + field => 'seealso', + type => 'varchar(255)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', }, { - field => 'dewey', - type => 'varchar(30)', - null => 'null', - default => '', + field => 'kohafield', + type => 'varchar(45)', + null => 'NULL', + key => '', + default => "''", extra => '', }, { - field => 'publicationyear', - type => 'text', - null => 'null', - default => '', + field => 'frameworkcode', + type => 'varchar(8)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, + ], + + auth_tag_structure => [ { - field => 'collectiontitle', - type => 'mediumtext', - null => 'null', - default => '', + field => 'authtypecode', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, { - field => 'collectionissn', - type => 'mediumtext', - null => 'null', - default => '', + field => 'tagfield', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, { - field => 'collectionvolume', - type => 'mediumtext', - null => 'null', - default => '', + field => 'liblibrarian', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, { - field => 'editionstatement', - type => 'text', - null => 'null', - default => '', + field => 'libopac', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, { - field => 'editionresponsibility', - type => 'text', - null => 'null', - default => '', + field => 'authorised_value', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => "NULL", extra => '', }, - ], - deletedbiblioitems => [ + + auth_types => [ { - field => 'itemtype', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', + field => 'auth_tag_to_report', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", extra => '', }, { - field => 'dewey', - type => 'varchar(30)', - null => 'null', + field => 'summary', + type => 'mediumtext', + null => 'NOT NULL', + key => '', default => '', extra => '', }, ], - branches => [ + + authorised_values => [ { - field => 'branchip', - type => 'varchar(15)', - null => 'NULL', - key => '', - default => '', - extra => '', + field => 'category', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'branchprinter', - type => 'varchar(100)', - null => 'NULL', - key => '', - default => '', - extra => '', + field => 'authorised_value', + type => 'varchar(80)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', }, { - field => 'branchcode', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', + field => 'lib', + type => 'varchar(80)', + null => 'NULL', + key => '', + default => 'NULL', extra => '', - } + }, ], - branchtransfers =>[ + + biblio_framework => [ { - field => 'frombranch', - type => 'VARCHAR(10)', - null => 'NOT NULL', + field => 'frameworkcode', + type => 'varchar(4)', + null => 'NOT NULL', key => '', - default => '', + default => "''", extra => '', }, { - field => 'tobranch', - type => 'VARCHAR(10)', - null => 'NOT NULL', + field => 'frameworktext', + type => 'varchar(255)', + null => 'NOT NULL', key => '', - default => '', - } - ], - - categories => [ - { - field => 'category_type', - type => 'char(1)', - null => 'NOT NULL', - key => '', - default => 'A', - extra => '', + default => "''", + extra => '', }, + ], + + borrowers => [ { - field => 'categorycode', - type => 'varchar(10)', - null => 'NOT NULL', - key => 'PRI', - default => '', + field => 'cardnumber', + type => 'varchar(16)', + null => 'NULL', + key => '', + default => 'NULL', extra => '', }, - ], - - deletedborrowers => [ + { field => 'surname', + type => 'mediumtext', + null => 'NOT NULL', + }, { field => 'firstname', type => 'text', null => 'NULL', }, + { field => 'title', + type => 'mediumtext', + null => 'NULL', + }, + { field => 'othernames', + type => 'mediumtext', + null => 'NULL', + }, { field => 'initials', type => 'text', null => 'NULL', @@ -941,13 +1193,13 @@ my $DBversion = "3.00.00.000"; }, { field => 'streetnumber', # street number (hidden if streettable table is empty) - type => 'char(10)', + type => 'varchar(10)', null => 'NULL', after => 'initials', }, { field => 'streettype', # street table, list builded from a system table - type => 'char(50)', + type => 'varchar(50)', null => 'NULL', after => 'streetnumber', }, @@ -957,13 +1209,13 @@ my $DBversion = "3.00.00.000"; }, { field => 'B_streetnumber', # street number (hidden if streettable table is empty) - type => 'char(10)', + type => 'varchar(10)', null => 'NULL', after => 'fax', }, { field => 'B_streettype', # street table, list builded from a system table - type => 'char(50)', + type => 'varchar(50)', null => 'NULL', after => 'B_streetnumber', }, @@ -997,180 +1249,1110 @@ my $DBversion = "3.00.00.000"; null => 'NULL', after => 'contactfirstname', }, - ], - - issues => [ { - field => 'borrowernumber', - type => 'int(11)', - null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed - key => '', + field => 'branchcode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'categorycode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'address', + type => 'mediumtext', + null => 'NOT NULL', default => '', - extra => '', + extra => '', }, { - field => 'itemnumber', - type => 'int(11)', - null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed - key => '', + field => 'email', + type => 'mediumtext', + null => 'NULL', default => '', - extra => '', + extra => '', }, { - field => 'branchcode', - type => 'varchar(10)', - null => 'NULL', - key => '', - default => '', - extra => '', + field => 'B_city', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', }, { - field => 'issuedate', - type => 'date', - null => '', - key => '', - default => '0000-00-00', - extra => '', + field => 'city', + type => 'mediumtext', + null => 'NOT NULL', + default => '', + extra => '', }, - ], - issuingrules => [ { - field => 'categorycode', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', - extra => '', + field => 'fax', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', }, { - field => 'branchcode', - type => 'varchar(10)', - null => 'NOT NULL', + field => 'B_phone', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'contactname', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'opacnote', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'borrowernotes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'sex', + type => 'varchar(1)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + ], + + biblioitems => [ + { + field => 'itemtype', + type => 'varchar(10)', + null => 'NOT NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'lcsort', + type => 'varchar(25)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'ccode', + type => 'varchar(4)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'dewey', + type => 'varchar(30)', + null => 'null', + default => '', + extra => '', + }, + { + field => 'publicationyear', + type => 'text', + null => 'null', + default => '', + extra => '', + }, + { + field => 'collectiontitle', + type => 'mediumtext', + null => 'null', + default => '', + extra => '', + after => 'volumeddesc', + }, + { + field => 'collectionissn', + type => 'text', + null => 'null', + default => '', + extra => '', + after => 'collectiontitle', + }, + { + field => 'collectionvolume', + type => 'mediumtext', + null => 'null', + default => '', + extra => '', + after => 'collectionissn', + }, + { + field => 'editionstatement', + type => 'text', + null => 'null', + default => '', + extra => '', + after => 'collectionvolume', + }, + { + field => 'editionresponsibility', + type => 'text', + null => 'null', + default => '', + extra => '', + after => 'editionstatement', + }, + { + field => 'volume', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'number', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + ], + + biblio => [ + { + field => 'author', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'title', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'unititle', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'seriestitle', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'abstract', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'frameworkcode', + type => 'varchar(4)', + null => 'NOT NULL', + default => "''", + extra => '', + after => 'biblionumber', + }, + ], + + deletedbiblio => [ + { + field => 'author', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'title', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'unititle', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'seriestitle', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'abstract', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'frameworkcode', + type => 'varchar(4)', + null => 'NOT NULL', + default => "''", + extra => '', + after => 'biblionumber', + }, + ], + deletedbiblioitems => [ + { + field => 'itemtype', + type => 'varchar(10)', + null => 'NOT NULL', + default => '', + extra => '', + }, + { + field => 'dewey', + type => 'varchar(30)', + null => 'null', + default => '', + extra => '', + }, + { + field => 'itemtype', + type => 'varchar(10)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + { + field => 'volume', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'number', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + ], + + bookshelf => [ + { + field => 'shelfname', + type => 'varchar(255)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + { + field => 'owner', + type => 'varchar(80)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + { + field => 'category', + type => 'varchar(1)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + ], + + branchcategories => [ + { + field => 'codedescription', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + ], + + branches => [ + { + field => 'branchip', + type => 'varchar(15)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'branchprinter', + type => 'varchar(100)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'branchname', + type => 'mediumtext', + null => 'NOT NULL', + default => '', + extra => '', + }, + { + field => 'branchaddress1', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'branchaddress2', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'branchaddress3', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'branchphone', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'branchfax', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'branchemail', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + ], + + branchrelations => [ + { + field => 'branchcode', + type => 'VARCHAR(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'categorycode', + type => 'VARCHAR(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + } + ], + + branchtransfers =>[ + { + field => 'frombranch', + type => 'VARCHAR(10)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'tobranch', + type => 'VARCHAR(10)', + null => 'NOT NULL', + key => '', + default => "''", + }, + { + field => 'comments', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + }, + ], + + categories => [ + { + field => 'category_type', + type => 'varchar(1)', + null => 'NOT NULL', + key => '', + default => 'A', + extra => '', + }, + { + field => 'categorycode', + type => 'varchar(10)', + null => 'NOT NULL', + key => 'PRI', + default => "''", + extra => '', + }, + { + field => 'description', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + ], + + deletedborrowers => [ + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'categorycode', + type => 'varchar(2)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + { + field => 'B_phone', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'borrowernotes', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'contactname', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'B_city', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'B_zipcode', + type => 'varchar(25)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + { + field => 'zipcode', + type => 'varchar(25)', + null => 'NULL', + default => 'NULL', + extra => '', + after => 'city', + }, + { + field => 'email', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'address', + type => 'mediumtext', + null => 'NOT NULL', + default => '', + extra => '', + }, + { + field => 'fax', + type => 'mediumtext', + null => 'NULL', + default => '', + extra => '', + }, + { + field => 'city', + type => 'mediumtext', + null => 'NOT NULL', + default => '', + extra => '', + }, + { field => 'surname', + type => 'mediumtext', + null => 'NOT NULL', + }, + { field => 'firstname', + type => 'text', + null => 'NULL', + }, + { field => 'initials', + type => 'text', + null => 'NULL', + }, + { field => 'title', + type => 'mediumtext', + null => 'NULL', + }, + { field => 'othernames', + type => 'mediumtext', + null => 'NULL', + }, + { field => 'B_email', + type => 'text', + null => 'NULL', + after => 'B_zipcode', + }, + { + field => 'streetnumber', # street number (hidden if streettable table is empty) + type => 'varchar(10)', + null => 'NULL', + default => 'NULL', + after => 'initials', + }, + { + field => 'streettype', # street table, list builded from a system table + type => 'varchar(50)', + null => 'NULL', + default => 'NULL', + after => 'streetnumber', + }, + { field => 'phone', + type => 'text', + null => 'NULL', + }, + { + field => 'B_streetnumber', # street number (hidden if streettable table is empty) + type => 'varchar(10)', + null => 'NULL', + after => 'fax', + }, + { + field => 'B_streettype', # street table, list builded from a system table + type => 'varchar(50)', + null => 'NULL', + after => 'B_streetnumber', + }, + { + field => 'phonepro', + type => 'text', + null => 'NULL', + after => 'fax', + }, + { + field => 'address2', # complement address + type => 'text', + null => 'NULL', + after => 'address', + }, + { + field => 'emailpro', + type => 'text', + null => 'NULL', + after => 'fax', + }, + { + field => 'contactfirstname', # contact's firstname + type => 'text', + null => 'NULL', + after => 'contactname', + }, + { + field => 'contacttitle', # contact's title + type => 'text', + null => 'NULL', + after => 'contactfirstname', + }, + { + field => 'sex', + type => 'varchar(1)', + null => 'NULL', + default => 'NULL', + extra => '', + }, + ], + + issues => [ + { + field => 'borrowernumber', + type => 'int(11)', + null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed + key => '', + default => '', + extra => '', + }, + { + field => 'itemnumber', + type => 'int(11)', + null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed + key => '', + default => '', + extra => '', + }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'issuedate', + type => 'date', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'return', + type => 'varchar(4)', + null => 'NULL', + key => '', + default => 'NULL', + extra => '', + }, + { + field => 'issuingbranch', + type => 'varchar(18)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + ], + issuingrules => [ + { + field => 'categorycode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'branchcode', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + { + field => 'itemtype', + type => 'varchar(10)', + null => 'NOT NULL', + default => "''", + extra => '', + }, + ], + + items => [ + { + field => 'onloan', + type => 'date', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'cutterextra', + type => 'varchar(45)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'homebranch', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'holdingbranch', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'itype', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'paidfor', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'itemnotes', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + ], + + deleteditems => [ + { + field => 'paidfor', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + { + field => 'itemnotes', + type => 'mediumtext', + null => 'NULL', + key => '', + default => '', + extra => '', + }, + ], + + itemtypes => [ + { + field => 'itemtype', + type => 'varchar(10)', + default => "''", + null => 'NOT NULL', + key => 'PRI', + extra => 'UNIQUE', + }, + { + field => 'description', + type => 'MEDIUMTEXT', + null => 'NULL', + key => '', + extra => '', + }, + { + field => 'summary', + type => 'TEXT', + null => 'NULL', + key => '', + extra => '', + }, + ], + marc_breeding => [ + { + field => 'marc', + type => 'LONGBLOB', + null => 'NULL', + key => '', + extra => '', + } + ], + marc_subfield_structure => [ + { + field => 'defaultvalue', + type => 'TEXT', + null => 'NULL', + key => '', + extra => '', + }, + { + field => 'authtypecode', + type => 'varchar(20)', + null => 'NULL', + key => '', + default => 'NULL', + extra => '', + }, + { + field => 'tagfield', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'tagsubfield', + type => 'varchar(1)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'authorised_value', + type => 'varchar(20)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', + }, + { + field => 'seealso', + type => 'varchar(1100)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', + }, + ], + + marc_tag_structure => [ + { + field => 'tagfield', + type => 'varchar(3)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'liblibrarian', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'libopac', + type => 'varchar(255)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'authorised_value', + type => 'varchar(10)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', + }, + { + field => 'frameworkcode', + type => 'varchar(4)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + ], + + opac_news => [ + { + field => 'expirationdate', + type => 'date', + null => 'null', + key => '', + extra => '', + }, + { + field => 'number', + type => 'int(11)', + null => 'NULL', + key => '', default => '', extra => '', }, + ], + + printers => [ + { + field => 'printername', + type => 'varchar(40)', + null => 'NOT NULL', + key => '', + default => "''", + extra => '', + }, + { + field => 'printqueue', + type => 'varchar(20)', + null => 'NULL', + key => '', + default => "NULL", + extra => '', + }, { - field => 'itemtype', - type => 'varchar(10)', - null => 'NOT NULL', - default => '', + field => 'printtype', + type => 'varchar(20)', + null => 'NULL', + key => '', + default => "NULL", extra => '', }, ], - items => [ + reserveconstraints => [ { - field => 'onloan', + field => 'reservedate', type => 'date', null => 'NULL', key => '', - default => '0000-00-00', + default => 'NULL', extra => '', }, + ], + + reserves => [ { - field => 'cutterextra', - type => 'varchar(45)', + field => 'waitingdate', + type => 'date', null => 'NULL', key => '', default => '', extra => '', }, { - field => 'homebranch', - type => 'varchar(10)', + field => 'reservedate', + type => 'date', null => 'NULL', key => '', default => '', extra => '', }, { - field => 'holdingbranch', - type => 'varchar(10)', + field => 'constrainttype', + type => 'varchar(1)', null => 'NULL', key => '', - default => '', + default => 'NULL', extra => '', + after => 'biblionumber', }, { - field => 'itype', + field => 'branchcode', type => 'varchar(10)', null => 'NULL', key => '', default => '', extra => '', }, - ], - itemtypes => [ - { - field => 'itemtype', - type => 'varchar(10)', - default => '', - null => 'NOT NULL', - key => 'PRI', - extra => 'UNIQUE', - }, - { - field => 'summary', - type => 'TEXT', - null => 'NULL', - key => '', - extra => '', - }, - ], - marc_breeding => [ - { - field => 'marc', - type => 'LONGBLOB', - null => 'NULL', - key => '', - extra => '', - } - ], - marc_subfield_structure => [ - { - field => 'defaultvalue', - type => 'TEXT', - null => 'NULL', - key => '', - extra => '', - } - ], - opac_news => [ - { - field => 'expirationdate', - type => 'date', - null => 'null', - key => '', - extra => '', - }, { - field => 'number', - type => 'int(11)', + field => 'reservenotes', + type => 'mediumtext', null => 'NULL', - key => '', - default => '0', - extra => '', + key => '', + default => '', + extra => '', }, - ], - reserves => [ { - field => 'waitingdate', - type => 'date', + field => 'found', + type => 'varchar(1)', null => 'NULL', key => '', default => '', extra => '', }, ], + serial => [ + { + field => 'planneddate', + type => 'DATE', + null => 'NULL', + key => '', + default => 'NULL', + extra => '', + }, { field => 'notes', type => 'TEXT', null => 'NULL', key => '', default => '', - extra => '' + extra => '', + after => 'planneddate', }, ], + shelfcontents => [ { field => 'dateadded', @@ -1178,19 +2360,86 @@ my $DBversion = "3.00.00.000"; null => 'NULL', }, ], + statistics => [ { field => 'branch', type => 'varchar(10)', null => 'NOT NULL', }, + { + field => 'datetime', + type => 'datetime', + null => 'NULL', + default => 'NULL', + }, { field => 'itemtype', type => 'varchar(10)', - null => 'NOT NULL', + null => 'NULL', + }, + { + field => 'other', + type => 'mediumtext', + null => 'NULL', + }, + ], + + subscription => [ + { + field => 'startdate', + type => 'date', + null => 'NULL', + key => '' , + default => 'NULL', + extra => '', + }, + { + field => 'notes', + type => 'mediumtext', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, + { + field => 'monthlength', + type => 'int(11)', + null => 'NULL', + key => '' , + default => '0', + extra => '', + }, + ], + + subscriptionhistory => [ + { + field => 'histstartdate', + type => 'date', + null => 'NULL', + key => '' , + default => 'NULL', + extra => '', + }, + { + field => 'enddate', + type => 'date', + null => 'NULL', + key => '' , + default => 'NULL', + extra => '', }, ], + systempreferences => [ + { + field => 'options', + type => 'mediumtext', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, { field => 'value', type => 'text', @@ -1216,7 +2465,43 @@ my $DBversion = "3.00.00.000"; key => '' , default => '', extra => '', - } + }, + { + field => 'note', + type => 'mediumtext', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, + ], + userflags => [ + { + field => 'flag', + type => 'varchar(30)', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, + { + field => 'flagdesc', + type => 'varchar(255)', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, + ], + z3950servers => [ + { + field => 'name', + type => 'mediumtext', + null => 'NULL', + key => '' , + default => '', + extra => '', + }, ], ); @@ -1225,6 +2510,11 @@ my $DBversion = "3.00.00.000"; # { indexname => 'index detail' # } # ], + accountoffsets => [ + { indexname => 'accountoffsets_ibfk_1', + content => 'borrowernumber', + }, + ], aqbooksellers => [ { indexname => 'PRIMARY', content => 'id', @@ -1257,6 +2547,14 @@ my $DBversion = "3.00.00.000"; content => 'publishercode', }, ], + borrowers => [ + { + indexname => 'borrowernumber', + content => 'borrowernumber', + type => 'PRI', + force => 1, + } + ], branches => [ { indexname => 'branchcode', @@ -1295,6 +2593,16 @@ my $DBversion = "3.00.00.000"; content => 'categorycode', } ], + issuingrules => [ + { + indexname => 'categorycode', + content => 'categorycode', + }, + { + indexname => 'itemtype', + content => 'itemtype', + }, + ], items => [ { indexname => 'homebranch', content => 'homebranch', @@ -1350,7 +2658,7 @@ my $DBversion = "3.00.00.000"; ], shelfcontents => [ { key => 'shelfnumber', - foreigntable => 'virtualshelf', + foreigntable => 'bookshelf', foreignkey => 'shelfnumber', onUpdate => 'CASCADE', onDelete => 'CASCADE', @@ -1371,12 +2679,6 @@ my $DBversion = "3.00.00.000"; onUpdate => 'CASCADE', onDelete => 'CASCADE', }, - { key => 'itemtype', - foreigntable => 'itemtypes', - foreignkey => 'itemtype', - onUpdate => 'CASCADE', - onDelete => 'RESTRICT', - }, ], items => [ { key => 'biblioitemnumber', @@ -1524,21 +2826,6 @@ my $DBversion = "3.00.00.000"; onDelete => 'RESTRICT', }, ], - deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted - # but prevent deleting a branch as soon as it has 1 borrower ! - { key => 'categorycode', - foreigntable => 'categories', - foreignkey => 'categorycode', - onUpdate => 'RESTRICT', - onDelete => 'RESTRICT', - }, - { key => 'branchcode', - foreigntable => 'branches', - foreignkey => 'branchcode', - onUpdate => 'RESTRICT', - onDelete => 'RESTRICT', - }, - ], accountlines => [ { key => 'borrowernumber', foreigntable => 'borrowers', @@ -1553,6 +2840,14 @@ my $DBversion = "3.00.00.000"; onDelete => 'SET NULL', }, ], + accountoffsets => [ + { key => 'borrowernumber', + foreigntable => 'borrowers', + foreignkey => 'borrowernumber', + onUpdate => 'CASCADE', + onDelete => 'CASCADE', + }, + ], auth_tag_structure => [ { key => 'authtypecode', foreigntable => 'auth_types', @@ -1726,7 +3021,7 @@ my $DBversion = "3.00.00.000"; next if $table->{Name} eq 'auth_subfield_table'; if ($table->{Engine} ne 'InnoDB') { print "moving $table->{Name} to InnoDB\n"; - $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb"); + $dbh->do("ALTER TABLE $table->{Name} ENGINE = innodb"); } unless ($table->{Collation} =~ /^utf8/) { print "moving $table->{Name} to utf8\n"; @@ -1736,7 +3031,51 @@ my $DBversion = "3.00.00.000"; } else { } } + + # list of columns that must exist for %column_change to be + # processed without error, but which do not necessarily exist + # in all 2.2 databases + my %required_prereq_fields = ( + deletedborrowers => [ + [ 'textmessaging', 'mediumtext AFTER faxnumber' ], + [ 'password', 'varchar(30) default NULL' ], + [ 'flags', 'int(11) default NULL' ], + [ 'userid', 'varchar(30) default NULL' ], + [ 'homezipcode', 'varchar(25) default NULL' ], + [ 'zipcode', 'varchar(25) default NULL' ], + [ 'sort1', 'varchar(80) default NULL' ], + [ 'sort2', 'varchar(80) default NULL' ], + ], + ); + + foreach $table ( keys %required_prereq_fields ) { + print "Check table $table\n" if $debug and not $silent; + $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 my $entry ( @{ $required_prereq_fields{$table} } ) { + ($column, $type) = @{ $entry }; + print " Check column $column [$type]\n" if $debug and not $silent; + if ( !$types{$column} ) { + # column doesn't exist + print "Adding $column field to $table table...\n" unless $silent; + $query = "alter table $table + add column $column " . $type; + 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 my $table (keys %column_change) { $sth = $dbh->prepare("show columns from $table"); @@ -1789,7 +3128,7 @@ my $DBversion = "3.00.00.000"; # Enter here the line you want to remove from DB. my %linetodelete = ( # table name => where clause. - userflags => "bit = 8", # delete the 'reserveforself' flags + userflags => [ "bit = 8" ], # delete the 'reserveforself' flags ); # %linetodelete @@ -1861,7 +3200,7 @@ my $DBversion = "3.00.00.000"; } # foreach column } # foreach table - foreach $table ( keys %fielddefinitions ) { + foreach $table ( sort keys %fielddefinitions ) { print "Check table $table\n" if $debug; $sth = $dbh->prepare("show columns from $table"); $sth->execute(); @@ -1891,6 +3230,7 @@ my $DBversion = "3.00.00.000"; unless ( $type eq $def->{type} && $null eq $def->{null} && $key eq $def->{key} + && $default eq $def->{default} && $extra eq $def->{extra} ) { if ( $null eq '' ) { @@ -1906,7 +3246,7 @@ my $DBversion = "3.00.00.000"; # if it's a new column use "add", if it's an old one, use "change". my $action; if ($definitions->{$field}->{type}) { - $action="change $field" + $action="change `$field`" } else { $action="add"; } @@ -1915,24 +3255,27 @@ my $DBversion = "3.00.00.000"; my $query; if ($key ne 'PRIMARY KEY') { # warn "alter table $table $action $field $type $null $key $extra default $default $after"; - $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after"; + $query = "alter table $table $action `$field` $type $null $key $extra ". + GetDefaultClause($default)." $after"; } else { # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after"; # something strange : for indexes UNIQUE, they are reported as primary key here. # but if you try to run with drop primary key, it fails. # thus, we run the query twice, one will fail, one will succeed. # strange... - $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after"; - $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after"; + $query="alter table $table drop primary key, $action `$field` $type $null $key $extra ". + GetDefaultClause($default)." $after"; + $query="alter table $table $action `$field` $type $null $key $extra ". + GetDefaultClause($default)." $after"; } - $dbh->do($query); + $dbh->do($query) or warn "Error while executing: $query"; } } } print "removing some unused data...\n"; foreach my $table ( keys %linetodelete ) { - foreach my $where ( @{linetodelete{$table}} ){ + foreach my $where ( @{$linetodelete{$table}} ){ print "DELETE FROM ".$table." where ".$where; print "\n"; my $sth = $dbh->prepare("DELETE FROM $table where $where"); @@ -1983,6 +3326,12 @@ my $DBversion = "3.00.00.000"; print "Updating publisheddate\n"; $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL"); } + # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it. + # now, let's get rid of 000-00-00's. + + $dbh->do("update serial set publisheddate=NULL where publisheddate = 0"); + $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0"); + foreach my $table ( keys %tabledata ) { print "Checking for data required in table $table...\n" unless $silent; my $tablerows = $tabledata{$table}; @@ -2046,12 +3395,12 @@ my $DBversion = "3.00.00.000"; my $tablerows = $indexes{$table}; foreach my $row (@$tablerows) { my $key_name=$row->{indexname}; - if ($existingindexes{$key_name} eq 1) { + if ($existingindexes{$key_name} eq 1 and not $row->{force}) { # print "$key_name existing"; } else { print "\tCreating index $key_name in $table\n"; my $sql; - if ($row->{indexname} eq 'PRIMARY') { + if ($row->{indexname} eq 'PRIMARY' or $row->{type} eq 'PRI') { $sql = "alter table $table ADD PRIMARY KEY ($row->{content})"; } else { $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}"; @@ -2066,7 +3415,7 @@ my $DBversion = "3.00.00.000"; # check foreign keys and create them when needed # print "Checking for foreign keys required...\n" unless $silent; - foreach my $table ( keys %foreign_keys ) { + foreach my $table ( sort keys %foreign_keys ) { # # read all indexes from $table # @@ -2093,7 +3442,7 @@ my $DBversion = "3.00.00.000"; $dbh->do($sql); if ($dbh->err) { print "==================== - An error occured during : + An error occurred during : \t$sql It probably means there is something wrong in your DB : a row ($table.$row->{key}) refers to a value in $row->{foreigntable}.$row->{foreignkey} that does not exist. solve the problem and run updater again (or just the previous SQL statement). You can find those values with select @@ -2151,7 +3500,7 @@ my $DBversion = "3.00.00.000"; # at last, remove useless fields foreach $table ( keys %uselessfields ) { - my @fields = split /,/,$uselessfields{$table}; + my @fields = split (/,/,$uselessfields{$table}); my $fields; my $exists; foreach my $fieldtodrop (@fields) { @@ -2177,17 +3526,38 @@ my $DBversion = "3.00.00.000"; # $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;"); $sth->execute; - $sth->finish; + + # drop extra key on borrowers.borrowernumber + $dbh->do("ALTER TABLE borrowers DROP KEY borrowernumber"); + + # update enrolmentperiod + $dbh->do("UPDATE categories SET enrolmentperiod = enrolmentperiod * 12"); + print "upgrade to Koha 3.0 done\n"; SetVersion ($DBversion); +=head1 GetDefaultClause -=item TransformToNum +Generate a default clause (for an ALTER TABLE command) + +=cut + +sub GetDefaultClause { + my $default = shift; + + return "" unless defined $default; + return "" if $default eq ''; + return "default ''" if $default eq "''"; + return "default NULL" if $default eq "NULL"; + return "default " . $dbh->quote($default); +} + +=head1 TransformToNum + +Transform the Koha version from a 4 parts string +to a number, with just 1. - Transform the Koha version from a 4 parts string - to a number, with just 1 . - =cut sub TransformToNum { @@ -2197,8 +3567,10 @@ sub TransformToNum { return $version; } -=item SetVersion - set the DBversion in the systempreferences +=head1 SetVersion + +set the DBversion in the systempreferences + =cut sub SetVersion {