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