#!/usr/bin/perl
-# $Id$
# Database Updater
# This script checks for required updates to the database.
my $dbh = C4::Context->dbh;
$|=1; # flushes output
-my $DBversion = "3.00.00.000";
-# if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
-if (C4::Context->preference("Version") < TransformToNum($DBversion) ) {
- # Tables to add if they don't exist
- my %requiretables = (
- action_logs => "(
- `timestamp` TIMESTAMP NOT NULL ,
- `user` INT( 11 ) NOT NULL default '0' ,
- `module` TEXT default '',
- `action` TEXT default '' ,
- `object` INT(11) NULL ,
- `info` TEXT default '' ,
- PRIMARY KEY ( `timestamp` , `user` )
- )",
- letter => "(
- module varchar(20) NOT NULL default '',
- code varchar(20) NOT NULL default '',
- name varchar(100) NOT NULL default '',
- title varchar(200) NOT NULL default '',
- content text,
- PRIMARY KEY (module,code)
- )",
- alert =>"(
- alertid int(11) NOT NULL auto_increment,
- borrowernumber int(11) NOT NULL default '0',
- type varchar(10) NOT NULL default '',
- externalid varchar(20) NOT NULL default '',
- PRIMARY KEY (alertid),
- KEY borrowernumber (borrowernumber),
- KEY type (type,externalid)
- )",
- opac_news => "(
- `idnew` int(10) unsigned NOT NULL auto_increment,
- `title` varchar(250) NOT NULL default '',
- `new` text NOT NULL,
- `lang` varchar(4) NOT NULL default '',
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
- PRIMARY KEY (`idnew`)
- )",
- repeatable_holidays => "(
- `id` int(11) NOT NULL auto_increment,
- `branchcode` varchar(4) NOT NULL default '',
- `weekday` smallint(6) default NULL,
- `day` smallint(6) default NULL,
- `month` smallint(6) default NULL,
- `title` varchar(50) NOT NULL default '',
- `description` text NOT NULL,
- PRIMARY KEY (`id`)
- )",
- special_holidays => "(
- `id` int(11) NOT NULL auto_increment,
- `branchcode` varchar(4) NOT NULL default '',
- `day` smallint(6) NOT NULL default '0',
- `month` smallint(6) NOT NULL default '0',
- `year` smallint(6) NOT NULL default '0',
- `isexception` smallint(1) NOT NULL default '1',
- `title` varchar(50) NOT NULL default '',
- `description` text NOT NULL,
- PRIMARY KEY (`id`)
- )",
- overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
- `categorycode` char(2) NOT NULL default '',
- `delay1` int(4) default '0',
- `letter1` varchar(20) default NULL,
- `debarred1` char(1) default '0',
- `delay2` int(4) default '0',
- `debarred2` char(1) default '0',
- `letter2` varchar(20) default NULL,
- `delay3` int(4) default '0',
- `letter3` varchar(20) default NULL,
- `debarred3` int(1) default '0',
- PRIMARY KEY (`branchcode`,`categorycode`)
- )",
- cities => "(`cityid` int auto_increment,
- `city_name` char(100) NOT NULL,
- `city_zipcode` char(20),
- PRIMARY KEY (`cityid`)
- )",
- roadtype => "(`roadtypeid` int auto_increment,
- `road_type` char(100) NOT NULL,
- PRIMARY KEY (`roadtypeid`)
- )",
-
- labels => "(
- labelid int(11) NOT NULL auto_increment,
- itemnumber varchar(100) NOT NULL default '',
- timestamp timestamp(14) NOT NULL,
- PRIMARY KEY (labelid)
- )",
-
- 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,
- PRIMARY KEY (id)
- )",
- reviews => "(
- reviewid integer NOT NULL auto_increment,
- borrowernumber integer,
- biblionumber integer,
- review text,
- approved tinyint,
- datereviewed datetime,
- PRIMARY KEY (reviewid)
- )",
- subscriptionroutinglist=>"(
- routingid integer NOT NULL auto_increment,
- borrowernumber integer,
- ranking integer,
- subscriptionid integer,
- PRIMARY KEY (routingid)
- )",
-
- notifys => "(
- 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_send_date` date default NULL,
- `notify_level` int(1) NOT NULL default '0',
- `method` varchar(20) NOT NULL default ''
- )",
-
- charges => "(
- `charge_id` varchar(5) NOT NULL default '',
- `description` text NOT NULL,
- `amount` decimal(28,6) NOT NULL default '0.000000',
- `min` int(4) NOT NULL default '0',
- `max` int(4) NOT NULL default '0',
- `level` int(1) NOT NULL default '0',
- PRIMARY KEY (`charge_id`)
- )",
- tags => "(
- `entry` varchar(255) NOT NULL default '',
- `weight` bigint(20) NOT NULL default '0',
- PRIMARY KEY (`entry`)
- )
- ",
- zebraqueue => "(
- `id` int NOT NULL auto_increment,
- `biblio_auth_number` int NOT NULL,
- `operation` char(20) NOT NULL,
- `server` char(20) NOT NULL ,
- 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'},
- 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) NOT NULL', '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',},
- 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'", },
-
- # tablename => { 'field' => 'fieldtype' },
- );
-
- # Enter here the table to delete.
- my @TableToDelete = qw(
- additionalauthors
- bibliosubject
- bibliosubtitle
- bibliothesaurus
- );
-
- my %uselessfields = (
- # tablename => "field1,field2",
- borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
- deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
- );
- # 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)
-
- # 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, unless the forceupdate hash entry is also set. Fields in the
- # anonymous "forceupdate" hash will be forced to be updated to the default
- # values given in the %tabledata hash.
-
- my %tabledata = (
- # tablename => [
- # { uniquefielrequired => 'fieldname', # the primary key in the table
- # fieldname => fieldvalue,
- # fieldname2 => fieldvalue2,
- # },
- # ],
- systempreferences => [
- {
- uniquefieldrequired => 'variable',
- variable => 'useDaysMode',
- value => 'Calendar',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
- type => 'Choice',
- options => 'Calendar|Days'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'DebugLevel',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
- type => 'Choice',
- options => '0|1|2'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'BorrowersTitles',
- value => 'Mr|Mrs|Miss|Ms',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'List all Titles for borrowers',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'BorrowerMandatoryField',
- value => 'cardnumber|surname|address',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'List all mandatory fields for borrowers',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'borrowerRelationship',
- value => 'father|mother,grand-mother',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'ReservesMaxPickUpDelay',
- value => '10',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Maximum delay to pick up a reserved document',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'TransfersMaxDaysWarning',
- value => '3',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Max delay before considering the transfer has potentialy a problem',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'memberofinstitution',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Are your patrons members of institutions',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'ReadingHistory',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Allow reading record info retrievable from issues and oldissues tables',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'IssuingInProcess',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'AutomaticItemReturn',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'This Variable allow or not to return automaticly to his homebranch',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'reviewson',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Allows patrons to submit reviews from the opac',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'intranet_includes',
- value => 'includes',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
- type => 'Free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'AutoLocation',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'serialsadditems',
- value => '0',
- forceupdate => {
- 'explanation' => 1,
- 'type' => 1
- },
- explanation => 'If set, a new item will be automatically added when receiving an issue',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'expandedSearchOption',
- value => '0',
- forceupdate => {
- 'explanation' => 1,
- 'type' => 1
- },
- explanation => 'search among marc field',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'RequestOnOpac',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'option to allow reserves on opac',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'OpacCloud',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'OpacBrowser',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'OpacTopissue',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enable / Disable the top issue link on OPAC',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'OpacAuthorities',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enable / Disable the search authority link on OPAC',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'CataloguingLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log cataloguing action.',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'BorrowersLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'SubscriptionLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log Subscription action',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'IssueLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log issue.',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'ReturnLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log the circulation return',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'Version',
- value => '3.0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Koha Version',
- type => 'Free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'LetterLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log all the letter sent',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'FinesLog',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want to log fines',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'NoZebra',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'NoZebraIndexes',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
- type => 'Free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'uppercasesurnames',
- value => '0',
- forceupdate => {'explanation' => 1, 'type' => 1},
- explanation => "Force Surnames to be uppercase",
- type => 'YesNo',
- },
- ],
- userflags => [
- {
- uniquefieldrequired => 'bit',
- bit => '14',
- flag => 'editauthorities',
- flagdesc => 'allow to edit authorities',
- defaulton => '0',
- },
- {
- uniquefieldrequired => 'bit',
- bit => '15',
- flag => 'serials',
- flagdesc => 'allow to manage serials subscriptions',
- defaulton => '0',
- },
- {
- uniquefieldrequired => 'bit',
- bit => '16',
- flag => 'reports',
- flagdesc => 'allow to access to the reports module',
- defaulton => '0',
- },
- ],
- authorised_values => [
- {
- uniquefieldrequired => 'id',
- category => 'SUGGEST',
- authorised_value => 'Not enough budget',
- lib => 'This book it too much expensive',
- }
- ],
- );
-
- my %fielddefinitions = (
- # fieldname => [
- # { field => 'fieldname',
- # type => 'fieldtype',
- # null => '',
- # key => '',
- # default => ''
- # },
- # ],
- aqbasket => [
- {
- field => 'booksellerid',
- type => 'int(11)',
- null => 'NOT NULL',
- key => '',
- default => '1',
- extra => '',
- },
- ],
- aqbookfund => [
- {
- field => 'bookfundid',
- type => 'varchar(5)',
- null => 'NOT NULL',
- key => 'PRI',
- default => '',
- extra => '',
- },
- ],
-
- aqbooksellers => [
- {
- field => 'id',
- type => 'int(11)',
- null => 'NOT NULL',
- key => 'PRI',
- default => '',
- extra => 'auto_increment',
- },
- {
- field => 'listprice',
- type => 'varchar(10)',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'invoiceprice',
- type => 'varchar(10)',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'invoicedisc',
- type => 'float(6,4)',
- null => 'NULL',
- key => '',
- default => 'NULL',
- extra => '',
- },
-
- ],
-
- aqbudget => [
- {
- field => 'bookfundid',
- type => 'varchar(5)',
- null => 'NOT NULL',
- key => '',
- default => '',
- exra => '',
- },
- ],
-
- accountlines => [
- {
- field => 'notify_id',
- type => 'int(11)',
- null => 'NOT NULL',
- key => '',
- default => '0',
- extra => '',
- },
- {
- field => 'notify_level',
- type => 'int(2)',
- null => 'NOT NULL',
- key => '',
- default => '0',
- extra => '',
- },
- {
- field => 'accountno',
- type => 'smallint(6)',
- null => 'NOT NULL',
- key => '',
- default => '0',
- extra => '',
- },
- {
- field => 'description',
- type => 'mediumtext',
- null => 'NULL',
- },
- {
- field => 'dispute',
- type => 'mediumtext',
- null => 'NULL',
- },
-
- ],
-
- borrowers => [
- { field => 'firstname',
- type => 'text',
- null => 'NULL',
- },
- { field => 'initials',
- type => 'text',
- null => 'NULL',
- },
- { field => 'B_email',
- type => 'text',
- null => 'NULL',
- after => 'B_zipcode',
- },
- {
- field => 'streetnumber', # street number (hidden if streettable table is empty)
- type => 'char(10)',
- null => 'NULL',
- after => 'initials',
- },
- {
- field => 'streettype', # street table, list builded from a system table
- type => 'char(50)',
- null => 'NULL',
- after => 'streetnumber',
- },
- { field => 'phone',
- type => 'text',
- null => 'NULL',
- },
- {
- field => 'B_streetnumber', # street number (hidden if streettable table is empty)
- type => 'char(10)',
- null => 'NULL',
- after => 'fax',
- },
- {
- field => 'B_streettype', # street table, list builded from a system table
- type => 'char(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 => 'branchcode',
- type => 'varchar(10)',
- null => 'NOT NULL',
- default => '',
- extra => '',
- },
- {
- field => 'categorycode',
- type => 'varchar(10)',
- null => 'NOT NULL',
- default => '',
- extra => '',
- }
- ],
-
- biblioitems => [
- {
- 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 => '',
- },
- {
- field => 'collectionissn',
- type => 'mediumtext',
- null => 'null',
- default => '',
- extra => '',
- },
- {
- field => 'collectionvolume',
- type => 'mediumtext',
- null => 'null',
- default => '',
- extra => '',
- },
- {
- field => 'editionstatement',
- type => 'text',
- null => 'null',
- default => '',
- extra => '',
- },
- {
- field => 'editionresponsability',
- type => 'text',
- null => 'null',
- default => '',
- extra => '',
- },
-
- ],
- deletedbiblioitems => [
- {
- field => 'dewey',
- type => 'varchar(30)',
- 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 => '',
- }
- ],
- branchtransfers =>[
- {
- field => 'frombranch',
- type => 'VARCHAR(10)',
- null => 'NOT NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'tobranch',
- type => 'VARCHAR(10)',
- null => 'NOT NULL',
- key => '',
- default => '',
- }
- ],
-
- categories => [
- {
- field => 'category_type',
- type => 'char(1)',
- null => 'NOT NULL',
- key => '',
- default => 'A',
- extra => '',
- },
- {
- field => 'categorycode',
- type => 'varchar(10)',
- null => 'NOT NULL',
- key => 'PRI',
- default => '',
- extra => '',
- },
- ],
-
- deletedborrowers => [
- { field => 'firstname',
- type => 'text',
- null => 'NULL',
- },
- { field => 'initials',
- type => 'text',
- null => 'NULL',
- },
- { field => 'B_email',
- type => 'text',
- null => 'NULL',
- after => 'B_zipcode',
- },
- {
- field => 'streetnumber', # street number (hidden if streettable table is empty)
- type => 'char(10)',
- null => 'NULL',
- after => 'initials',
- },
- {
- field => 'streettype', # street table, list builded from a system table
- type => 'char(50)',
- null => 'NULL',
- after => 'streetnumber',
- },
- { field => 'phone',
- type => 'text',
- null => 'NULL',
- },
- {
- field => 'B_streetnumber', # street number (hidden if streettable table is empty)
- type => 'char(10)',
- null => 'NULL',
- after => 'fax',
- },
- {
- field => 'B_streettype', # street table, list builded from a system table
- type => 'char(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',
- },
- ],
-
- 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 => '',
- key => '',
- default => '0000-00-00',
- extra => '',
- },
- ],
-
- items => [
- {
- field => 'onloan',
- type => 'date',
- null => 'NULL',
- key => '',
- default => '0000-00-00',
- extra => '',
- },
- {
- field => 'cutterextra',
- type => 'varchar(45)',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'issue_date',
- type => 'date',
- 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 => '',
- },
- ],
- 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)',
- null => 'NULL',
- key => '',
- default => '0',
- extra => '',
- },
- ],
- reserves => [
- {
- field => 'waitingdate',
- type => 'date',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- ],
- serial => [
- {
- field => 'notes',
- type => 'TEXT',
- null => 'NULL',
- key => '',
- default => '',
- extra => ''
- },
- ],
- shelfcontents => [
- {
- field => 'dateadded',
- type => 'timestamp',
- null => 'NULL',
- },
- ],
- systempreferences => [
- {
- field => 'value',
- type => 'text',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'explanation',
- type => 'text',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- ],
- suggestions => [
- {
- field => 'reason',
- type => 'text',
- null => 'NULL',
- key => '' ,
- default => '',
- extra => '',
- }
- ],
- );
-
- my %indexes = (
- # table => [
- # { indexname => 'index detail'
- # }
- # ],
- aqbooksellers => [
- { indexname => 'PRIMARY',
- content => 'id',
- type => 'PRI',
- }
- ],
- aqbasket => [
- { indexname => 'booksellerid',
- content => 'booksellerid',
- },
- ],
- aqorders => [
- { indexname => 'basketno',
- content => 'basketno',
- },
- ],
- aqorderbreakdown => [
- { indexname => 'ordernumber',
- content => 'ordernumber',
- },
- { indexname => 'bookfundid',
- content => 'bookfundid',
- },
- ],
- biblioitems => [
- { indexname => 'isbn',
- content => 'isbn',
- },
- { indexname => 'publishercode',
- content => 'publishercode',
- },
- ],
- branches => [
- {
- indexname => 'branchcode',
- content => 'branchcode',
- type => 'PRI',
- }
- ],
- branchrelations => [
- {
- indexname => 'PRIMARY',
- content => 'categorycode',
- type => 'PRI',
- }
- ],
- branchrelations => [
- { indexname => 'PRIMARY',
- content => 'branchcode,categorycode',
- type => 'PRI',
- },
- { indexname => 'branchcode',
- content => 'branchcode',
- },
- { indexname => 'categorycode',
- content => 'categorycode',
- }
- ],
- currency => [
- { indexname => 'PRIMARY',
- content => 'currency',
- type => 'PRI',
- }
- ],
- categories => [
- {
- indexname => 'categorycode',
- content => 'categorycode',
- }
- ],
- items => [
- { indexname => 'homebranch',
- content => 'homebranch',
- },
- { indexname => 'holdingbranch',
- content => 'holdingbranch',
- }
- ],
- itemtypes => [
- {
- indexname => 'itemtype',
- content => 'itemtype',
- }
- ],
- shelfcontents => [
- { indexname => 'shelfnumber',
- content => 'shelfnumber',
- },
- { indexname => 'itemnumber',
- content => 'itemnumber',
- }
- ],
- userflags => [
- { indexname => 'PRIMARY',
- content => 'bit',
- type => 'PRI',
- }
- ]
- );
-
- my %foreign_keys = (
- # table => [
- # { key => 'the key in table' (must be indexed)
- # foreigntable => 'the foreigntable name', # (the parent)
- # foreignkey => 'the foreign key column(s)' # (in the parent)
- # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
- # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
- # }
- # ],
- branchrelations => [
- { key => 'branchcode',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'categorycode',
- foreigntable => 'branchcategories',
- foreignkey => 'categorycode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- shelfcontents => [
- { key => 'shelfnumber',
- foreigntable => 'virtualshelf',
- foreignkey => 'shelfnumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemnumber',
- foreigntable => 'items',
- foreignkey => 'itemnumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
- # easily deleted, but branches/itemtype not too easy to empty...
- biblioitems => [
- { key => 'biblionumber',
- foreigntable => 'biblio',
- foreignkey => 'biblionumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemtype',
- foreigntable => 'itemtypes',
- foreignkey => 'itemtype',
- onUpdate => 'CASCADE',
- onDelete => 'RESTRICT',
- },
- ],
- items => [
- { key => 'biblioitemnumber',
- foreigntable => 'biblioitems',
- foreignkey => 'biblioitemnumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'homebranch',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'RESTRICT',
- },
- { key => 'holdingbranch',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'RESTRICT',
- },
- ],
- aqbasket => [
- { key => 'booksellerid',
- foreigntable => 'aqbooksellers',
- foreignkey => 'id',
- onUpdate => 'CASCADE',
- onDelete => 'RESTRICT',
- },
- ],
- aqorders => [
- { key => 'basketno',
- foreigntable => 'aqbasket',
- foreignkey => 'basketno',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'biblionumber',
- foreigntable => 'biblio',
- foreignkey => 'biblionumber',
- onUpdate => 'SET NULL',
- onDelete => 'SET NULL',
- },
- ],
- aqbooksellers => [
- { key => 'listprice',
- foreigntable => 'currency',
- foreignkey => 'currency',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'invoiceprice',
- foreigntable => 'currency',
- foreignkey => 'currency',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- aqorderbreakdown => [
- { key => 'ordernumber',
- foreigntable => 'aqorders',
- foreignkey => 'ordernumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'bookfundid',
- foreigntable => 'aqbookfund',
- foreignkey => 'bookfundid',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- branchtransfers => [
- { key => 'frombranch',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'tobranch',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemnumber',
- foreigntable => 'items',
- foreignkey => 'itemnumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- issuingrules => [
- { key => 'categorycode',
- foreigntable => 'categories',
- foreignkey => 'categorycode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemtype',
- foreigntable => 'itemtypes',
- foreignkey => 'itemtype',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
- # for stat purposes
- { key => 'borrowernumber',
- foreigntable => 'borrowers',
- foreignkey => 'borrowernumber',
- onUpdate => 'SET NULL',
- onDelete => 'SET NULL',
- },
- { key => 'itemnumber',
- foreigntable => 'items',
- foreignkey => 'itemnumber',
- onUpdate => 'SET NULL',
- onDelete => 'SET NULL',
- },
- ],
- reserves => [
- { key => 'borrowernumber',
- foreigntable => 'borrowers',
- foreignkey => 'borrowernumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'biblionumber',
- foreigntable => 'biblio',
- foreignkey => 'biblionumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemnumber',
- foreigntable => 'items',
- foreignkey => 'itemnumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'branchcode',
- foreigntable => 'branches',
- foreignkey => 'branchcode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- borrowers => [ # 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',
- },
- ],
- 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',
- foreignkey => 'borrowernumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- { key => 'itemnumber',
- foreigntable => 'items',
- foreignkey => 'itemnumber',
- onUpdate => 'SET NULL',
- onDelete => 'SET NULL',
- },
- ],
- auth_tag_structure => [
- { key => 'authtypecode',
- foreigntable => 'auth_types',
- foreignkey => 'authtypecode',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
- );
-
-
- # column changes
- my %column_change = (
- # table
- borrowers => [
- {
- from => 'emailaddress',
- to => 'email',
- after => 'city',
- },
- {
- from => 'streetaddress',
- to => 'address',
- after => 'initials',
- },
- {
- from => 'faxnumber',
- to => 'fax',
- after => 'phone',
- },
- {
- from => 'textmessaging',
- to => 'opacnote',
- after => 'userid',
- },
- {
- from => 'altnotes',
- to => 'contactnote',
- after => 'opacnote',
- },
- {
- from => 'physstreet',
- to => 'B_address',
- after => 'fax',
- },
- {
- from => 'streetcity',
- to => 'B_city',
- after => 'B_address',
- },
- {
- from => 'phoneday',
- to => 'mobile',
- after => 'phone',
- },
- {
- from => 'zipcode',
- to => 'zipcode',
- after => 'city',
- },
- {
- from => 'homezipcode',
- to => 'B_zipcode',
- after => 'B_city',
- },
- {
- from => 'altphone',
- to => 'B_phone',
- after => 'B_zipcode',
- },
- {
- from => 'expiry',
- to => 'dateexpiry',
- after => 'dateenrolled',
- },
- {
- from => 'guarantor',
- to => 'guarantorid',
- after => 'contactname',
- },
- {
- from => 'altrelationship',
- to => 'relationship',
- after => 'borrowernotes',
- },
- ],
-
- deletedborrowers => [
- {
- from => 'emailaddress',
- to => 'email',
- after => 'city',
- },
- {
- from => 'streetaddress',
- to => 'address',
- after => 'initials',
- },
- {
- from => 'faxnumber',
- to => 'fax',
- after => 'phone',
- },
- {
- from => 'textmessaging',
- to => 'opacnote',
- after => 'userid',
- },
- {
- from => 'altnotes',
- to => 'contactnote',
- after => 'opacnote',
- },
- {
- from => 'physstreet',
- to => 'B_address',
- after => 'fax',
- },
- {
- from => 'streetcity',
- to => 'B_city',
- after => 'B_address',
- },
- {
- from => 'phoneday',
- to => 'mobile',
- after => 'phone',
- },
- {
- from => 'zipcode',
- to => 'zipcode',
- after => 'city',
- },
- {
- from => 'homezipcode',
- to => 'B_zipcode',
- after => 'B_city',
- },
- {
- from => 'altphone',
- to => 'B_phone',
- after => 'B_zipcode',
- },
- {
- from => 'expiry',
- to => 'dateexpiry',
- after => 'dateenrolled',
- },
- {
- from => 'guarantor',
- to => 'guarantorid',
- after => 'contactname',
- },
- {
- from => 'altrelationship',
- to => 'relationship',
- after => 'borrowernotes',
- },
- ],
- );
-
-
- # MOVE all tables TO UTF-8 and innoDB
- $sth = $dbh->prepare("show table status");
- $sth->execute;
- while ( my $table = $sth->fetchrow_hashref ) {
- next if $table->{Name} eq 'marc_word';
- next if $table->{Name} eq 'marc_subfield_table';
- next if $table->{Name} eq 'auth_word';
- 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");
- }
- unless ($table->{Collation} =~ /^utf8/) {
- print "moving $table->{Name} to utf8\n";
- $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
- $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
- # FIXME : maybe a ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 would be better, def char set seems to work fine. If any problem encountered, let's try with convert !
- } else {
- }
- }
-
-
- foreach my $table (keys %column_change) {
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute();
- undef %types;
- while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
- {
- $types{$column}->{type} ="$type";
- $types{$column}->{null} = "$null";
- $types{$column}->{key} = "$key";
- $types{$column}->{default} = "$default";
- $types{$column}->{extra} = "$extra";
- } # while
- my $tablerows = $column_change{$table};
- foreach my $row ( @$tablerows ) {
- if ($types{$row->{from}}->{type}) {
- print "altering $table $row->{from} to $row->{to}\n";
- # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
- # alter table `borrowers` change `faxnumber` `fax` type text null after phone
- my $sql =
- "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
- ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
- ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
- "$types{$row->{from}}->{extra} after $row->{after} ";
- # print "$sql";
- $dbh->do($sql);
- }
- }
- }
-
- # Enter here the field you want to delete from DB.
- # FIXME :: there is a %uselessfield before which seems doing the same things.
- my %fieldtodelete = (
- # tablename => [fieldname1,fieldname2,...]
-
- ); # %fielddelete
-
- print "removing some unused fields...\n";
- foreach my $table ( keys %fieldtodelete ) {
- foreach my $field ( @{$fieldtodelete{$table}} ){
- print "removing ".$field." from ".$table;
- my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
- $sth->execute;
- if ( $sth->err ) {
- print "Error : $sth->errstr \n";
- }
- }
- }
-
- # Enter here the line you want to remove from DB.
- my %linetodelete = (
- # table name => where clause.
- userflags => "bit = 8", # delete the 'reserveforself' flags
-
- ); # %linetodelete
-
- #-------------------
- # 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" unless $silent;
- }
-
- #---------------------------------
- # Tables
-
- # Collect all tables into a list
- $sth = $dbh->prepare("show tables");
- $sth->execute;
- while ( my ($table) = $sth->fetchrow ) {
- $existingtables{$table} = 1;
- }
-
-
- # Now add any missing tables
- foreach $table ( keys %requiretables ) {
- unless ( $existingtables{$table} ) {
- print "Adding $table table...\n" unless $silent;
- 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
-
- #---------------------------------
- # Columns
-
- foreach $table ( keys %requirefields ) {
- 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 $column ( keys %{ $requirefields{$table} } ) {
- print " Check column $column [$types{$column}]\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 " . $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}->{null} = 'NULL' if $null eq 'YES';
- $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};
- # $null = 'YES' if $row->{null} eq 'NULL';
- my $key = $row->{key};
- my $default = $row->{default};
- # $default="''" unless $default;
- my $extra = $row->{extra};
- my $def = $definitions->{$field};
- my $after = ($row->{after}?" after ".$row->{after}:"");
-
- unless ( $type eq $def->{type}
- && $null eq $def->{null}
- && $key eq $def->{key}
- && $extra eq $def->{extra} )
- {
- if ( $null eq '' ) {
- $null = 'NOT NULL';
- }
- if ( $key eq 'PRI' ) {
- $key = 'PRIMARY KEY';
- }
- unless ( $extra eq 'auto_increment' ) {
- $extra = '';
- }
-
- # 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"
- } else {
- $action="add";
- }
- # if it's a primary key, drop the previous pk, before altering the table
- print " alter or create $field in $table\n" unless $silent;
- 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";
- } 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";
- }
- $dbh->do($query);
- }
- }
- }
-
- print "removing some unused data...\n";
- foreach my $table ( keys %linetodelete ) {
- foreach my $where ( @{linetodelete{$table}} ){
- print "DELETE FROM ".$table." where ".$where;
- print "\n";
- my $sth = $dbh->prepare("DELETE FROM $table where $where");
- $sth->execute;
- if ( $sth->err ) {
- print "Error : $sth->errstr \n";
- }
- }
- }
-
- # Populate tables with required data
-
- # synch table and deletedtable.
- foreach my $table (('borrowers','items','biblio','biblioitems')) {
- my %deletedborrowers;
- print "synch'ing $table and deleted$table\n";
- $sth = $dbh->prepare("show columns from deleted$table");
- $sth->execute;
- while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
- $deletedborrowers{$column}=1;
- }
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute;
- my $previous;
- while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
- unless ($deletedborrowers{$column}) {
- my $newcol="alter table deleted$table add $column $type";
- if ($null eq 'YES') {
- $newcol .= " NULL ";
- } else {
- $newcol .= " NOT NULL ";
- }
- $newcol .= "default ".$dbh->quote($default) if $default;
- $newcol .= " after $previous" if $previous;
- $previous=$column;
- print "creating column $column\n";
- $dbh->do($newcol);
- }
- }
- }
- #
- # update publisheddate
- #
- $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
- $sth->execute;
- my ($emptypublished) = $sth->fetchrow;
- if ($emptypublished) {
- print "Updating publisheddate\n";
- $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
- }
- foreach my $table ( keys %tabledata ) {
- print "Checking for data required in table $table...\n" unless $silent;
- my $tablerows = $tabledata{$table};
- foreach my $row (@$tablerows) {
- my $uniquefieldrequired = $row->{uniquefieldrequired};
- my $uniquevalue = $row->{$uniquefieldrequired};
- my $forceupdate = $row->{forceupdate};
- my $sth =
- $dbh->prepare(
- "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
- );
- $sth->execute($uniquevalue);
- if ($sth->rows) {
- foreach my $field (keys %$forceupdate) {
- if ($forceupdate->{$field}) {
- my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
- $sth->execute($row->{$field}, $uniquevalue);
- }
- }
- } else {
- print "Adding row to $table: " unless $silent;
- my @values;
- my $fieldlist;
- my $placeholders;
- foreach my $field ( keys %$row ) {
- next if $field eq 'uniquefieldrequired';
- next if $field eq 'forceupdate';
- my $value = $row->{$field};
- push @values, $value;
- print " $field => $value" unless $silent;
- $fieldlist .= "$field,";
- $placeholders .= "?,";
- }
- print "\n" unless $silent;
- $fieldlist =~ s/,$//;
- $placeholders =~ s/,$//;
- print "insert into $table ($fieldlist) values ($placeholders)";
- my $sth =
- $dbh->prepare(
- "insert into $table ($fieldlist) values ($placeholders)");
- $sth->execute(@values);
- }
- }
- }
-
- #
- # check indexes and create them when needed
- #
- print "Checking for index required...\n" unless $silent;
- foreach my $table ( keys %indexes ) {
- #
- # read all indexes from $table
- #
- $sth = $dbh->prepare("show index from $table");
- $sth->execute;
- my %existingindexes;
- while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
- $existingindexes{$key_name} = 1;
- }
- # read indexes to check
- my $tablerows = $indexes{$table};
- foreach my $row (@$tablerows) {
- my $key_name=$row->{indexname};
- if ($existingindexes{$key_name} eq 1) {
- # print "$key_name existing";
- } else {
- print "\tCreating index $key_name in $table\n";
- my $sql;
- if ($row->{indexname} eq 'PRIMARY') {
- $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
- } else {
- $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
- }
- $dbh->do($sql);
- print "Error $sql : $dbh->err \n" if $dbh->err;
- }
- }
- }
-
- #
- # check foreign keys and create them when needed
- #
- print "Checking for foreign keys required...\n" unless $silent;
- foreach my $table ( keys %foreign_keys ) {
- #
- # read all indexes from $table
- #
- $sth = $dbh->prepare("show table status like '$table'");
- $sth->execute;
- my $stat = $sth->fetchrow_hashref;
- # read indexes to check
- my $tablerows = $foreign_keys{$table};
- foreach my $row (@$tablerows) {
- my $foreign_table=$row->{foreigntable};
- if ($stat->{'Comment'} =~/$foreign_table/) {
- # print "$foreign_table existing\n";
- } else {
- print "\tCreating foreign key $foreign_table in $table\n";
- # first, drop any orphan value in child table
- if ($row->{onDelete} ne "RESTRICT") {
- my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
- $dbh->do($sql);
- print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
- }
- my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
- $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
- $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
- $dbh->do($sql);
- if ($dbh->err) {
- print "====================
- An error occured 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
- \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
- ====================\n
- ";
- }
- }
- }
- }
- # now drop useless tables
- foreach $table ( @TableToDelete ) {
- if ( $existingtables{$table} ) {
- print "Dropping unused table $table\n" if $debug and not $silent;
- $dbh->do("drop table $table");
- if ( $dbh->err ) {
- print "Error : $dbh->errstr \n";
- }
- }
- }
-
- #
- # SPECIFIC STUFF
- #
- #
- # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
- #
-
- # 1st, get how many biblio we will have to do...
- $sth = $dbh->prepare('select count(*) from marc_biblio');
- $sth->execute;
- my ($totaltodo) = $sth->fetchrow;
-
- $sth = $dbh->prepare("show columns from biblio");
- $sth->execute();
- my $definitions;
- my $bibliofwexist=0;
- while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
- $bibliofwexist=1 if $column eq 'frameworkcode';
- }
- unless ($bibliofwexist) {
- print "moving biblioframework to biblio table\n";
- $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
- $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
- $sth->execute;
- my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
- my $totaldone=0;
- while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
- $sth_update->execute($frameworkcode,$biblionumber);
- $totaldone++;
- print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
- }
- print "\rdone\n";
- }
-
- # at last, remove useless fields
- foreach $table ( keys %uselessfields ) {
- my @fields = split /,/,$uselessfields{$table};
- my $fields;
- my $exists;
- foreach my $fieldtodrop (@fields) {
- $fieldtodrop =~ s/\t//g;
- $fieldtodrop =~ s/\n//g;
- $exists =0;
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute;
- while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
- {
- $exists =1 if ($column eq $fieldtodrop);
- }
- if ($exists) {
- print "deleting $fieldtodrop field in $table...\n" unless $silent;
- my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
- $sth->execute;
- }
- }
- } # foreach
-
- #
- # Changing aqbookfund's primary key
- #
- $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
- $sth->execute;
-
- $sth->finish;
- print "upgrade to Koha 3.0 done\n";
- SetVersion ($DBversion);
-} # 3.0000000
-
-
-
=item
Deal with virtualshelves
=cut
-$DBversion = "3.00.00.001";
+my $DBversion = "3.00.00.001";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
# update virtualshelves table to
#
SetVersion ($DBversion);
}
+$DBversion = "3.00.00.007";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
+ print "Upgrade to $DBversion done (set SessionStorage variable)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.008";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
+ $dbh->do("UPDATE biblio SET datecreated=timestamp");
+ print "Upgrade to $DBversion done (biblio creation date)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.009";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+
+ # Create backups of call number columns
+ # in case default migration needs to be customized
+ #
+ # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
+ # after call numbers have been transformed to the new structure
+ #
+ # Not bothering to do the same with deletedbiblioitems -- assume
+ # default is good enough.
+ $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
+ SELECT `biblioitemnumber`, `biblionumber`,
+ `classification`, `dewey`, `subclass`,
+ `lcsort`, `ccode`
+ FROM `biblioitems`");
+
+ # biblioitems changes
+ $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
+ ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
+ ADD `totalissues` INT(10) AFTER `cn_sort`");
+
+ # default mapping of call number columns:
+ # cn_class = concatentation of classification + dewey,
+ # trimmed to fit -- assumes that most users do not
+ # populate both classification and dewey in a single record
+ # cn_item = subclass
+ # cn_source = left null
+ # cn_sort = lcsort
+ #
+ # After upgrade, cn_sort will have to be set based on whatever
+ # default call number scheme user sets as a preference. Misc
+ # script will be added at some point to do that.
+ #
+ $dbh->do("UPDATE `biblioitems`
+ SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
+ cn_item = subclass,
+ `cn_sort` = `lcsort`
+ ");
+
+ # Now drop the old call number columns
+ $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
+ DROP COLUMN `dewey`,
+ DROP COLUMN `subclass`,
+ DROP COLUMN `lcsort`,
+ DROP COLUMN `ccode`");
+
+ # deletedbiblio changes
+ $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
+ DROP COLUMN `marc`,
+ ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
+ $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
+
+ # deletedbiblioitems changes
+ $dbh->do("ALTER TABLE `deletedbiblioitems`
+ MODIFY `publicationyear` TEXT,
+ CHANGE `volumeddesc` `volumedesc` TEXT,
+ MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
+ MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
+ MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
+ MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
+ MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
+ MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
+ MODIFY `marc` BLOB,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
+ ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
+ ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
+ ADD `totalissues` INT(10) AFTER `cn_sort`,
+ ADD KEY `isbn` (`isbn`),
+ ADD KEY `publishercode` (`publishercode`)
+ ");
+
+ $dbh->do("UPDATE `deletedbiblioitems`
+ SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
+ `cn_item` = `subclass`,
+ `cn_sort` = `lcsort`
+ ");
+ $dbh->do("ALTER TABLE `deletedbiblioitems`
+ DROP COLUMN `classification`,
+ DROP COLUMN `dewey`,
+ DROP COLUMN `subclass`,
+ DROP COLUMN `lcsort`,
+ DROP COLUMN `ccode`
+ ");
+
+ # deleteditems changes
+ $dbh->do("ALTER TABLE `deleteditems`
+ MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
+ MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
+ MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
+ DROP `bulk`,
+ MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
+ MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
+ DROP `interim`,
+ MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
+ DROP `cutterextra`,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
+ ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
+ MODIFY `marc` LONGBLOB AFTER `uri`,
+ DROP KEY `barcode`,
+ DROP KEY `itembarcodeidx`,
+ DROP KEY `itembinoidx`,
+ DROP KEY `itembibnoidx`,
+ ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
+ ADD KEY `delitembinoidx` (`biblioitemnumber`),
+ ADD KEY `delitembibnoidx` (`biblionumber`),
+ ADD KEY `delhomebranch` (`homebranch`),
+ ADD KEY `delholdingbranch` (`holdingbranch`)");
+ $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
+ $dbh->do("ALTER TABLE deleteditems DROP `itype`");
+ $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
+
+ # items changes
+ $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
+ ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
+ ");
+ $dbh->do("ALTER TABLE `items`
+ DROP KEY `itembarcodeidx`,
+ ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
+
+ # map items.itype to items.ccode and
+ # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
+ # will have to be subsequently updated per user's default
+ # classification scheme
+ $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
+ `ccode` = `itype`");
+
+ $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
+ DROP `itype`");
+
+ print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.010";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
+ print "Upgrade to $DBversion done (userid index added)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.011";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
+ $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
+ $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
+ $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
+ $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
+ print "Upgrade to $DBversion done (added branchcategory type)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.012";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE `class_sort_rules` (
+ `class_sort_rule` varchar(10) NOT NULL default '',
+ `description` mediumtext,
+ `sort_routine` varchar(30) NOT NULL default '',
+ PRIMARY KEY (`class_sort_rule`),
+ UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `class_sources` (
+ `cn_source` varchar(10) NOT NULL default '',
+ `description` mediumtext,
+ `used` tinyint(4) NOT NULL default 0,
+ `class_sort_rule` varchar(10) NOT NULL default '',
+ PRIMARY KEY (`cn_source`),
+ UNIQUE KEY `cn_source_idx` (`cn_source`),
+ KEY `used_idx` (`used`),
+ CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
+ REFERENCES `class_sort_rules` (`class_sort_rule`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
+ VALUES('DefaultClassificationSource','ddc',
+ 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
+ $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
+ ('dewey', 'Default filing rules for DDC', 'Dewey'),
+ ('lcc', 'Default filing rules for LCC', 'LCC'),
+ ('generic', 'Generic call number filing rules', 'Generic')");
+ $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
+ ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
+ ('lcc', 'Library of Congress Classification', 1, 'lcc'),
+ ('udc', 'Universal Decimal Classification', 0, 'generic'),
+ ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
+ ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
+ print "Upgrade to $DBversion done (classification sources added)\n";
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.013";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE `import_batches` (
+ `import_batch_id` int(11) NOT NULL auto_increment,
+ `template_id` int(11) default NULL,
+ `branchcode` varchar(10) default NULL,
+ `num_biblios` int(11) NOT NULL default 0,
+ `num_items` int(11) NOT NULL default 0,
+ `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
+ `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
+ `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
+ `file_name` varchar(100),
+ `comments` mediumtext,
+ PRIMARY KEY (`import_batch_id`),
+ KEY `branchcode` (`branchcode`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_records` (
+ `import_record_id` int(11) NOT NULL auto_increment,
+ `import_batch_id` int(11) NOT NULL,
+ `branchcode` varchar(10) default NULL,
+ `record_sequence` int(11) NOT NULL default 0,
+ `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `import_date` DATE default NULL,
+ `marc` longblob NOT NULL,
+ `marcxml` longtext NOT NULL,
+ `marcxml_old` longtext NOT NULL,
+ `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
+ `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
+ `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
+ `import_error` mediumtext,
+ `encoding` varchar(40) NOT NULL default '',
+ `z3950random` varchar(40) default NULL,
+ PRIMARY KEY (`import_record_id`),
+ CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
+ REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `branchcode` (`branchcode`),
+ KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_record_matches` (
+ `import_record_id` int(11) NOT NULL,
+ `candidate_match_id` int(11) NOT NULL,
+ `score` int(11) NOT NULL default 0,
+ CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `record_score` (`import_record_id`, `score`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_biblios` (
+ `import_record_id` int(11) NOT NULL,
+ `matched_biblionumber` int(11) default NULL,
+ `control_number` varchar(25) default NULL,
+ `original_source` varchar(25) default NULL,
+ `title` varchar(128) default NULL,
+ `author` varchar(80) default NULL,
+ `isbn` varchar(14) default NULL,
+ `issn` varchar(9) default NULL,
+ `has_items` tinyint(1) NOT NULL default 0,
+ CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `matched_biblionumber` (`matched_biblionumber`),
+ KEY `title` (`title`),
+ KEY `isbn` (`isbn`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_items` (
+ `import_items_id` int(11) NOT NULL auto_increment,
+ `import_record_id` int(11) NOT NULL,
+ `itemnumber` int(11) default NULL,
+ `branchcode` varchar(10) default NULL,
+ `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
+ `marcxml` longtext NOT NULL,
+ `import_error` mediumtext,
+ PRIMARY KEY (`import_items_id`),
+ CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `itemnumber` (`itemnumber`),
+ KEY `branchcode` (`branchcode`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.014";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
+ print "Upgrade to $DBversion done (userid index added)\n";
+ SetVersion ($DBversion);
+}
=item DropAllForeignKeys($table)
}
exit;
-# $Log$
# Revision 1.172 2007/07/19 10:21:22 hdl
projects / koha_fer / blobdiff