+ 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 )