-#-------------------
-# Defines
-
-# Tables to add if they don't exist
-my %requiretables = (
- categorytable => "(categorycode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (categorycode)
- )",
- subcategorytable => "(subcategorycode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (subcategorycode)
- )",
- mediatypetable => "(mediatypecode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (mediatypecode)
- )",
- action_logs => "(
- `timestamp` TIMESTAMP NOT NULL ,
- `user` INT( 11 ) NOT NULL ,
- `module` TEXT default '',
- `action` TEXT default '' ,
- `object` INT(11) default '' ,
- `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),
- )",
-
-);
-
-my %requirefields = (
- subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'},
- itemtypes => { 'imageurl' => 'char(200) NULL'},
- aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
- aqbudget => { 'branchcode' => 'varchar(4) NULL'},
-# tablename => { 'field' => 'fieldtype' },
-);
-
-my %dropable_table = (
- sessionqueries => 'sessionqueries',
- marcrecorddone => 'marcrecorddone',
- users => 'users',
- itemsprices => 'itemsprices',
- biblioanalysis => 'biblioanalysis',
- borexp => 'borexp',
-# tablename => 'tablename',
-);
-
-my %uselessfields = (
-# tablename => "field1,field2",
- borrowers => "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 => 'Activate_Log',
- value => 'On',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Turn Log Actions on DB On an Off',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'IndependantBranches',
- value => 0,
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Turn Branch independancy management On an Off',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'ReturnBeforeExpiry',
- value => 'Off',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'opacstylesheet',
- value => '',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enter a complete URL to use an alternate stylesheet in OPAC',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'opacsmallimage',
- value => '',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enter a complete URL to an image, will be on top/left instead of the Koha logo',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'opaclargeimage',
- value => '',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enter a complete URL to an image, will be on the main page, instead of the Koha logo',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'delimiter',
- value => ';',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'separator for reports exported to spreadsheet',
- type => 'free',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'MIME',
- value => 'OPENOFFICE.ORG',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1},
- explanation => 'Define the default application for report exportations into files',
- type => 'Choice',
- options => 'EXCEL|OPENOFFICE.ORG'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'Delimiter',
- value => ';',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1},
- explanation => 'Define the default separator character for report exportations into files',
- type => 'Choice',
- options => ';|tabulation|,|/|\|#'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'SubscriptionHistory',
- value => ';',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1},
- explanation => 'Define the information level for serials history in OPAC',
- type => 'Choice',
- options => 'simplified|full'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'hidelostitems',
- value => 'No',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'show or hide "lost" items in OPAC.',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'IndependantBranches',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Turn Branch independancy management On an Off',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'ReturnBeforeExpiry',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'Disable_Dictionary',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Disables Dictionary buttons if set to yes',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'hide_marc',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'hide marc specific datas like subfield code & indicators to library',
- type => 'YesNo',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'NotifyBorrowerDeparture',
- value => '0',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Delay before expiry where a notice is sent when issuing',
- type => 'Integer',
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'OpacPasswordChange',
- value => '1',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
- type => 'YesNo',
- },
- {
- 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 => 'borrowerMandatoryField',
- value => 'zipcode|surname',
- 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',
- },
- ],
-
-);
-
-my %fielddefinitions = (
-# fieldname => [
-# { field => 'fieldname',
-# type => 'fieldtype',
-# null => '',
-# key => '',
-# default => ''
-# },
-# ],
- serial => [
- {
- field => 'notes',
- type => 'TEXT',
- null => 'NULL',
- key => '',
- default => '',
- extra => ''
- },
- ],
- aqbasket => [
- {
- field => 'booksellerid',
- type => 'int(11)',
- null => 'NOT NULL',
- key => '',
- default => '1',
- extra => '',
- },
- ],
- aqbooksellers => [
- {
- field => 'listprice',
- type => 'varchar(10)',
- null => 'NULL',
- key => '',
- default => '',
- extra => '',
- },
- {
- field => 'invoiceprice',
- type => 'varchar(10)',
- null => 'NULL',
- key => '',
- default => '',
- 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 => '',
- },
- ],
- borrowers => [
- { 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 => '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',
- },
- ],
- categories => [
- {
- field => 'category_type',
- type => 'char(1)',
- null => 'NOT NULL',
- key => '',
- default => 'A',
- extra => '',
- },
- ],
-
-);
-
-my %indexes = (
-# table => [
-# { indexname => 'index detail'
-# }
-# ],
- shelfcontents => [
- { indexname => 'shelfnumber',
- content => 'shelfnumber',
- },
- { indexname => 'itemnumber',
- content => 'itemnumber',
- }
- ],
- bibliosubject => [
- { indexname => 'biblionumber',
- content => 'biblionumber',
- }
- ],
- items => [
- { indexname => 'homebranch',
- content => 'homebranch',
- },
- { indexname => 'holdingbranch',
- content => 'holdingbranch',
- }
- ],
- aqbooksellers => [
- { indexname => 'PRIMARY',
- content => 'id',
- type => 'PRIMARY',
- }
- ],
- aqbasket => [
- { indexname => 'booksellerid',
- content => 'booksellerid',
- },
- ],
- aqorders => [
- { indexname => 'basketno',
- content => 'basketno',
- },
- ],
- aqorderbreakdown => [
- { indexname => 'ordernumber',
- content => 'ordernumber',
- },
- { indexname => 'bookfundid',
- content => 'bookfundid',
- },
- ],
- currency => [
- { indexname => 'PRIMARY',
- content => 'currency',
- type => 'PRIMARY',
- }
- ],
-);
-
-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',
-# }
-# ],
- shelfcontents => [
- { key => 'shelfnumber',
- foreigntable => 'bookshelf',
- 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',
- },
- ],
- additionalauthors => [
- { key => 'biblionumber',
- foreigntable => 'biblio',
- foreignkey => 'biblionumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- bibliosubject => [
- { key => 'biblionumber',
- foreigntable => 'biblio',
- foreignkey => 'biblionumber',
- onUpdate => 'CASCADE',
- onDelete => 'CASCADE',
- },
- ],
- 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',
- },
- ],
- 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 => 'textmessaging',
- to => 'opacnotes',
- after => 'flags',
- },
- {
- from => 'altnotes',
- to => 'contactnotes',
- after => 'opacnotes',
- },
- {
- from => 'altrelationship',
- to => 'relationship',
- after => 'borrowernotes',
- },
- ],
- );
-
-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);
- }
- }