5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
33 %existingtables, # tables already in database
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
48 my $DBversion = "3.00.00.000";
49 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
50 # Tables to add if they don't exist
53 `timestamp` TIMESTAMP NOT NULL ,
54 `user` INT( 11 ) NOT NULL default '0' ,
55 `module` TEXT default '',
56 `action` TEXT default '' ,
57 `object` INT(11) NULL ,
58 `info` TEXT default '' ,
59 PRIMARY KEY ( `timestamp` , `user` )
62 module varchar(20) NOT NULL default '',
63 code varchar(20) NOT NULL default '',
64 name varchar(100) NOT NULL default '',
65 title varchar(200) NOT NULL default '',
67 PRIMARY KEY (module,code)
70 alertid int(11) NOT NULL auto_increment,
71 borrowernumber int(11) NOT NULL default '0',
72 type varchar(10) NOT NULL default '',
73 externalid varchar(20) NOT NULL default '',
74 PRIMARY KEY (alertid),
75 KEY borrowernumber (borrowernumber),
76 KEY type (type,externalid)
79 `idnew` int(10) unsigned NOT NULL auto_increment,
80 `title` varchar(250) NOT NULL default '',
82 `lang` varchar(4) NOT NULL default '',
83 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
86 repeatable_holidays => "(
87 `id` int(11) NOT NULL auto_increment,
88 `branchcode` varchar(10) NOT NULL default '',
89 `weekday` smallint(6) default NULL,
90 `day` smallint(6) default NULL,
91 `month` smallint(6) default NULL,
92 `title` varchar(50) NOT NULL default '',
93 `description` text NOT NULL,
96 special_holidays => "(
97 `id` int(11) NOT NULL auto_increment,
98 `branchcode` varchar(10) NOT NULL default '',
99 `day` smallint(6) NOT NULL default '0',
100 `month` smallint(6) NOT NULL default '0',
101 `year` smallint(6) NOT NULL default '0',
102 `isexception` smallint(1) NOT NULL default '1',
103 `title` varchar(50) NOT NULL default '',
104 `description` text NOT NULL,
107 overduerules =>"(`branchcode` varchar(10) NOT NULL default '',
108 `categorycode` varchar(2) NOT NULL default '',
109 `delay1` int(4) default '0',
110 `letter1` varchar(20) default NULL,
111 `debarred1` varchar(1) default '0',
112 `delay2` int(4) default '0',
113 `debarred2` varchar(1) default '0',
114 `letter2` varchar(20) default NULL,
115 `delay3` int(4) default '0',
116 `letter3` varchar(20) default NULL,
117 `debarred3` int(1) default '0',
118 PRIMARY KEY (`branchcode`,`categorycode`)
120 cities => "(`cityid` int auto_increment,
121 `city_name` varchar(100) NOT NULL default '',
122 `city_zipcode` varchar(20),
123 PRIMARY KEY (`cityid`)
125 roadtype => "(`roadtypeid` int auto_increment,
126 `road_type` varchar(100) NOT NULL default '',
127 PRIMARY KEY (`roadtypeid`)
131 labelid int(11) NOT NULL auto_increment,
132 batch_id varchar(10) NOT NULL default '1',
133 itemnumber varchar(100) NOT NULL default '',
134 timestamp timestamp(14) NOT NULL,
135 PRIMARY KEY (labelid)
139 id int(4) NOT NULL auto_increment,
140 barcodetype char(100) default '',
141 title int(1) default '0',
142 subtitle int(1) default '0',
143 itemtype int(1) default '0',
144 barcode int(1) default '0',
145 dewey int(1) default '0',
146 class int(1) default '0',
147 subclass int(1) default '0',
148 itemcallnumber int(1) default '0',
149 author int(1) default '0',
150 issn int(1) default '0',
151 isbn int(1) default '0',
152 startlabel int(2) NOT NULL default '1',
153 printingtype char(32) default 'BAR',
154 layoutname char(20) NOT NULL default 'TEST',
155 guidebox int(1) default '0',
156 active tinyint(1) default '1',
157 fonttype char(10) collate utf8_unicode_ci default NULL,
158 ccode char(4) collate utf8_unicode_ci default NULL,
159 callnum_split int(1) default NULL,
160 text_justify char(1) collate utf8_unicode_ci default NULL,
164 reviewid integer NOT NULL auto_increment,
165 borrowernumber integer,
166 biblionumber integer,
169 datereviewed datetime,
170 PRIMARY KEY (reviewid)
172 subscriptionroutinglist=>"(
173 routingid integer NOT NULL auto_increment,
174 borrowernumber integer,
176 subscriptionid integer,
177 PRIMARY KEY (routingid)
181 notify_id int(11) NOT NULL default '0',
182 `borrowernumber` int(11) NOT NULL default '0',
183 `itemnumber` int(11) NOT NULL default '0',
184 `notify_date` date default NULL,
185 `notify_send_date` date default NULL,
186 `notify_level` int(1) NOT NULL default '0',
187 `method` varchar(20) NOT NULL default ''
191 `charge_id` varchar(5) NOT NULL default '',
192 `description` text NOT NULL,
193 `amount` decimal(28,6) NOT NULL default '0.000000',
194 `min` int(4) NOT NULL default '0',
195 `max` int(4) NOT NULL default '0',
196 `level` int(1) NOT NULL default '0',
197 PRIMARY KEY (`charge_id`)
200 `entry` varchar(255) NOT NULL default '',
201 `weight` bigint(20) NOT NULL default '0',
202 PRIMARY KEY (`entry`)
206 `id` int NOT NULL auto_increment,
207 `biblio_auth_number` int(11) NOT NULL default '0',
208 `operation` char(20) NOT NULL default '',
209 `server` char(20) NOT NULL default '',
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
215 my %requirefields = (
216 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 \'\''},
217 itemtypes => { 'imageurl' => 'varchar(200) NULL'},
218 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
219 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
220 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
221 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'},
222 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
223 serial =>{ 'publisheddate' => 'date AFTER planneddate', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
224 statistics => { 'associatedborrower' => 'integer'},
225 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
226 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
227 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
229 issues =>{ 'issuedate'=>"date NULL default NULL", },
231 # tablename => { 'field' => 'fieldtype' },
234 # Enter here the table to delete.
235 my @TableToDelete = qw(
242 my %uselessfields = (
243 # tablename => "field1,field2",
244 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
245 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
246 items => "multivolumepart,multivolume,binding",
247 deleteditems => "multivolumepart,multivolume,binding",
249 # the other hash contains other actions that can't be done elsewhere. they are done
250 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
252 # The tabledata hash contains data that should be in the tables.
253 # The uniquefieldrequired hash entry is used to determine which (if any) fields
254 # must not exist in the table for this row to be inserted. If the
255 # uniquefieldrequired entry is already in the table, the existing data is not
256 # modified, unless the forceupdate hash entry is also set. Fields in the
257 # anonymous "forceupdate" hash will be forced to be updated to the default
258 # values given in the %tabledata hash.
262 # { uniquefielrequired => 'fieldname', # the primary key in the table
263 # fieldname => fieldvalue,
264 # fieldname2 => fieldvalue2,
267 systempreferences => [
269 uniquefieldrequired => 'variable',
270 variable => 'useDaysMode',
272 forceupdate => { 'explanation' => 1,
274 explanation => 'Choose the method for calculating due date: select Calendar to use the holidays module, and Days to ignore the holidays module',
276 options => 'Calendar|Days|Datedue'
279 uniquefieldrequired => 'variable',
280 variable => 'DebugLevel',
282 forceupdate => { 'explanation' => 1,
284 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
289 uniquefieldrequired => 'variable',
290 variable => 'BorrowersTitles',
291 value => 'Mr|Mrs|Miss|Ms',
292 forceupdate => { 'explanation' => 1,
294 explanation => 'List all Titles for borrowers',
298 uniquefieldrequired => 'variable',
299 variable => 'BorrowerMandatoryField',
300 value => 'cardnumber|surname|address',
301 forceupdate => { 'explanation' => 1,
303 explanation => 'List all mandatory fields for borrowers',
307 uniquefieldrequired => 'variable',
308 variable => 'borrowerRelationship',
309 value => 'father|mother,grand-mother',
310 forceupdate => { 'explanation' => 1,
312 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
316 uniquefieldrequired => 'variable',
317 variable => 'ReservesMaxPickUpDelay',
319 forceupdate => { 'explanation' => 1,
321 explanation => 'Maximum delay to pick up a reserved document',
325 uniquefieldrequired => 'variable',
326 variable => 'TransfersMaxDaysWarning',
328 forceupdate => { 'explanation' => 1,
330 explanation => 'Max delay before considering the transfer has potentialy a problem',
334 uniquefieldrequired => 'variable',
335 variable => 'memberofinstitution',
337 forceupdate => { 'explanation' => 1,
339 explanation => 'Are your patrons members of institutions',
343 uniquefieldrequired => 'variable',
344 variable => 'ReadingHistory',
346 forceupdate => { 'explanation' => 1,
348 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
352 uniquefieldrequired => 'variable',
353 variable => 'IssuingInProcess',
355 forceupdate => { 'explanation' => 1,
357 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
361 uniquefieldrequired => 'variable',
362 variable => 'AutomaticItemReturn',
364 forceupdate => { 'explanation' => 1,
366 explanation => 'This Variable allow or not to return automaticly to his homebranch',
370 uniquefieldrequired => 'variable',
371 variable => 'reviewson',
373 forceupdate => { 'explanation' => 1,
375 explanation => 'Allows patrons to submit reviews from the opac',
379 uniquefieldrequired => 'variable',
380 variable => 'intranet_includes',
382 forceupdate => { 'explanation' => 1,
384 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
388 uniquefieldrequired => 'variable',
389 variable => 'AutoLocation',
391 forceupdate => { 'explanation' => 1,
393 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
397 uniquefieldrequired => 'variable',
398 variable => 'serialsadditems',
404 explanation => 'If set, a new item will be automatically added when receiving an issue',
408 uniquefieldrequired => 'variable',
409 variable => 'expandedSearchOption',
415 explanation => 'search among marc field',
419 uniquefieldrequired => 'variable',
420 variable => 'RequestOnOpac',
422 forceupdate => { 'explanation' => 1,
424 explanation => 'option to allow reserves on opac',
428 uniquefieldrequired => 'variable',
429 variable => 'OpacCloud',
431 forceupdate => { 'explanation' => 1,
433 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
437 uniquefieldrequired => 'variable',
438 variable => 'OpacBrowser',
440 forceupdate => { 'explanation' => 1,
442 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
446 uniquefieldrequired => 'variable',
447 variable => 'OpacTopissue',
449 forceupdate => { 'explanation' => 1,
451 explanation => 'If ON, enables the \'most popular items\' link on OPAC. Warning, this is an EXPERIMENTAL feature, turning ON may overload your server',
455 uniquefieldrequired => 'variable',
456 variable => 'OpacAuthorities',
458 forceupdate => { 'explanation' => 1,
460 explanation => 'Enable / Disable the search authority link on OPAC',
464 uniquefieldrequired => 'variable',
465 variable => 'CataloguingLog',
467 forceupdate => {'explanation' => 1, 'type' => 1},
468 explanation => 'Active this if you want to log cataloguing action.',
472 uniquefieldrequired => 'variable',
473 variable => 'BorrowersLog',
475 forceupdate => {'explanation' => 1, 'type' => 1},
476 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
480 uniquefieldrequired => 'variable',
481 variable => 'SubscriptionLog',
483 forceupdate => {'explanation' => 1, 'type' => 1},
484 explanation => 'Active this if you want to log Subscription action',
488 uniquefieldrequired => 'variable',
489 variable => 'IssueLog',
491 forceupdate => {'explanation' => 1, 'type' => 1},
492 explanation => 'Active this if you want to log issue.',
496 uniquefieldrequired => 'variable',
497 variable => 'ReturnLog',
499 forceupdate => {'explanation' => 1, 'type' => 1},
500 explanation => 'Active this if you want to log the circulation return',
504 uniquefieldrequired => 'variable',
505 variable => 'Version',
507 forceupdate => {'explanation' => 1, 'type' => 1},
508 explanation => 'Koha Version',
512 uniquefieldrequired => 'variable',
513 variable => 'LetterLog',
515 forceupdate => {'explanation' => 1, 'type' => 1},
516 explanation => 'Active this if you want to log all the letter sent',
520 uniquefieldrequired => 'variable',
521 variable => 'FinesLog',
523 forceupdate => {'explanation' => 1, 'type' => 1},
524 explanation => 'Active this if you want to log fines',
528 uniquefieldrequired => 'variable',
529 variable => 'NoZebra',
531 forceupdate => {'explanation' => 1, 'type' => 1},
532 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
536 uniquefieldrequired => 'variable',
537 variable => 'NoZebraIndexes',
539 forceupdate => {'explanation' => 1, 'type' => 1},
540 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
544 uniquefieldrequired => 'variable',
545 variable => 'uppercasesurnames',
547 forceupdate => {'explanation' => 1, 'type' => 1},
548 explanation => "Force Surnames to be uppercase",
554 uniquefieldrequired => 'bit',
556 flag => 'editauthorities',
557 flagdesc => 'allow to edit authorities',
561 uniquefieldrequired => 'bit',
564 flagdesc => 'allow to manage serials subscriptions',
568 uniquefieldrequired => 'bit',
571 flagdesc => 'allow to access to the reports module',
575 authorised_values => [
577 uniquefieldrequired => 'id',
578 category => 'SUGGEST',
579 authorised_value => 'Not enough budget',
580 lib => 'This book it too much expensive',
585 my %fielddefinitions = (
587 # { field => 'fieldname',
588 # type => 'fieldtype',
596 field => 'booksellerid',
604 field => 'booksellerinvoicenumber',
605 type => 'mediumtext',
614 field => 'bookfundid',
615 type => 'varchar(10)',
622 field => 'branchcode',
623 type => 'varchar(10)',
630 field => 'bookfundname',
631 type => 'mediumtext',
636 after => 'bookfundid',
647 extra => 'auto_increment',
651 type => 'varchar(3)',
658 field => 'listprice',
659 type => 'varchar(10)',
666 field => 'invoiceprice',
667 type => 'varchar(10)',
674 field => 'invoicedisc',
675 type => 'float(6,4)',
683 type => 'mediumtext',
691 type => 'mediumtext',
699 type => 'mediumtext',
707 type => 'mediumtext',
714 field => 'accountnumber',
715 type => 'mediumtext',
722 field => 'othersupplier',
723 type => 'mediumtext',
730 field => 'specialty',
731 type => 'mediumtext',
738 field => 'booksellerfax',
739 type => 'mediumtext',
747 type => 'mediumtext',
754 field => 'bookselleremail',
755 type => 'mediumtext',
762 field => 'booksellerurl',
763 type => 'mediumtext',
770 field => 'contnotes',
771 type => 'mediumtext',
779 type => 'mediumtext',
789 field => 'bookfundid',
790 type => 'varchar(10)',
797 field => 'branchcode',
798 type => 'varchar(10)',
806 aqorderbreakdown => [
808 field => 'bookfundid',
809 type => 'varchar(10)',
816 field => 'branchcode',
817 type => 'varchar(10)',
827 field => 'ordernumber',
835 field => 'deliverycomments',
836 type => 'mediumtext',
847 type => 'mediumtext',
855 type => 'varchar(3)',
862 field => 'booksellerinvoicenumber',
863 type => 'mediumtext',
871 type => 'mediumtext',
878 field => 'supplierreference',
879 type => 'mediumtext',
886 field => 'purchaseordernumber',
887 type => 'mediumtext',
897 field => 'notify_id',
905 field => 'notify_level',
913 field => 'accountno',
914 type => 'smallint(6)',
921 field => 'description',
922 type => 'mediumtext',
927 type => 'mediumtext',
935 field => 'authtypecode',
936 type => 'varchar(10)',
943 field => 'datecreated',
951 field => 'origincode',
952 type => 'varchar(20)',
959 field => 'authtrees',
960 type => 'mediumtext',
965 after => 'origincode',
969 auth_subfield_structure => [
971 field => 'authtypecode',
972 type => 'varchar(10)',
980 type => 'varchar(3)',
987 field => 'tagsubfield',
988 type => 'varchar(1)',
995 field => 'liblibrarian',
996 type => 'varchar(255)',
1004 type => 'varchar(255)',
1011 field => 'authorised_value',
1012 type => 'varchar(10)',
1019 field => 'value_builder',
1020 type => 'varchar(80)',
1028 type => 'varchar(255)',
1035 field => 'kohafield',
1036 type => 'varchar(45)',
1043 field => 'frameworkcode',
1044 type => 'varchar(8)',
1052 auth_tag_structure => [
1054 field => 'authtypecode',
1055 type => 'varchar(10)',
1062 field => 'tagfield',
1063 type => 'varchar(3)',
1070 field => 'liblibrarian',
1071 type => 'varchar(255)',
1079 type => 'varchar(255)',
1086 field => 'authorised_value',
1087 type => 'varchar(10)',
1097 field => 'auth_tag_to_report',
1098 type => 'varchar(3)',
1106 type => 'mediumtext',
1114 authorised_values => [
1116 field => 'category',
1117 type => 'varchar(10)',
1124 field => 'authorised_value',
1125 type => 'varchar(80)',
1133 type => 'varchar(80)',
1141 biblio_framework => [
1143 field => 'frameworkcode',
1144 type => 'varchar(4)',
1151 field => 'frameworktext',
1152 type => 'varchar(255)',
1162 field => 'cardnumber',
1163 type => 'varchar(16)',
1169 { field => 'surname',
1170 type => 'mediumtext',
1173 { field => 'firstname',
1178 type => 'mediumtext',
1181 { field => 'othernames',
1182 type => 'mediumtext',
1185 { field => 'initials',
1189 { field => 'B_email',
1192 after => 'B_zipcode',
1195 field => 'streetnumber', # street number (hidden if streettable table is empty)
1196 type => 'varchar(10)',
1198 after => 'initials',
1201 field => 'streettype', # street table, list builded from a system table
1202 type => 'varchar(50)',
1204 after => 'streetnumber',
1211 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1212 type => 'varchar(10)',
1217 field => 'B_streettype', # street table, list builded from a system table
1218 type => 'varchar(50)',
1220 after => 'B_streetnumber',
1223 field => 'phonepro',
1229 field => 'address2', # complement address
1235 field => 'emailpro',
1241 field => 'contactfirstname', # contact's firstname
1244 after => 'contactname',
1247 field => 'contacttitle', # contact's title
1250 after => 'contactfirstname',
1253 field => 'branchcode',
1254 type => 'varchar(10)',
1260 field => 'categorycode',
1261 type => 'varchar(10)',
1268 type => 'mediumtext',
1275 type => 'mediumtext',
1282 type => 'mediumtext',
1289 type => 'mediumtext',
1296 type => 'mediumtext',
1303 type => 'mediumtext',
1309 field => 'contactname',
1310 type => 'mediumtext',
1316 field => 'opacnote',
1317 type => 'mediumtext',
1323 field => 'borrowernotes',
1324 type => 'mediumtext',
1331 type => 'varchar(1)',
1340 field => 'itemtype',
1341 type => 'varchar(10)',
1349 type => 'varchar(25)',
1357 type => 'varchar(4)',
1365 type => 'varchar(30)',
1371 field => 'publicationyear',
1378 field => 'collectiontitle',
1379 type => 'mediumtext',
1383 after => 'volumeddesc',
1386 field => 'collectionissn',
1391 after => 'collectiontitle',
1394 field => 'collectionvolume',
1395 type => 'mediumtext',
1399 after => 'collectionissn',
1402 field => 'editionstatement',
1407 after => 'collectionvolume',
1410 field => 'editionresponsibility',
1415 after => 'editionstatement',
1419 type => 'mediumtext',
1426 type => 'mediumtext',
1433 type => 'mediumtext',
1443 type => 'mediumtext',
1450 type => 'mediumtext',
1456 field => 'unititle',
1457 type => 'mediumtext',
1463 field => 'seriestitle',
1464 type => 'mediumtext',
1470 field => 'abstract',
1471 type => 'mediumtext',
1478 type => 'mediumtext',
1484 field => 'frameworkcode',
1485 type => 'varchar(4)',
1489 after => 'biblionumber',
1496 type => 'mediumtext',
1503 type => 'mediumtext',
1509 field => 'unititle',
1510 type => 'mediumtext',
1516 field => 'seriestitle',
1517 type => 'mediumtext',
1523 field => 'abstract',
1524 type => 'mediumtext',
1531 type => 'mediumtext',
1537 field => 'frameworkcode',
1538 type => 'varchar(4)',
1542 after => 'biblionumber',
1545 deletedbiblioitems => [
1547 field => 'itemtype',
1548 type => 'varchar(10)',
1555 type => 'varchar(30)',
1561 field => 'itemtype',
1562 type => 'varchar(10)',
1569 type => 'mediumtext',
1576 type => 'mediumtext',
1583 type => 'mediumtext',
1592 field => 'shelfname',
1593 type => 'varchar(255)',
1600 type => 'varchar(80)',
1606 field => 'category',
1607 type => 'varchar(1)',
1614 branchcategories => [
1616 field => 'codedescription',
1617 type => 'mediumtext',
1626 field => 'branchip',
1627 type => 'varchar(15)',
1634 field => 'branchprinter',
1635 type => 'varchar(100)',
1642 field => 'branchcode',
1643 type => 'varchar(10)',
1649 field => 'branchname',
1650 type => 'mediumtext',
1656 field => 'branchaddress1',
1657 type => 'mediumtext',
1663 field => 'branchaddress2',
1664 type => 'mediumtext',
1670 field => 'branchaddress3',
1671 type => 'mediumtext',
1677 field => 'branchphone',
1678 type => 'mediumtext',
1684 field => 'branchfax',
1685 type => 'mediumtext',
1691 field => 'branchemail',
1692 type => 'mediumtext',
1699 branchrelations => [
1701 field => 'branchcode',
1702 type => 'VARCHAR(10)',
1709 field => 'categorycode',
1710 type => 'VARCHAR(10)',
1720 field => 'frombranch',
1721 type => 'VARCHAR(10)',
1728 field => 'tobranch',
1729 type => 'VARCHAR(10)',
1735 field => 'comments',
1736 type => 'mediumtext',
1745 field => 'category_type',
1746 type => 'varchar(1)',
1753 field => 'categorycode',
1754 type => 'varchar(10)',
1761 field => 'description',
1762 type => 'mediumtext',
1770 deletedborrowers => [
1772 field => 'branchcode',
1773 type => 'varchar(10)',
1779 field => 'categorycode',
1780 type => 'varchar(2)',
1787 type => 'mediumtext',
1793 field => 'borrowernotes',
1794 type => 'mediumtext',
1800 field => 'contactname',
1801 type => 'mediumtext',
1808 type => 'mediumtext',
1814 field => 'B_zipcode',
1815 type => 'varchar(25)',
1822 type => 'varchar(25)',
1830 type => 'mediumtext',
1837 type => 'mediumtext',
1844 type => 'mediumtext',
1851 type => 'mediumtext',
1856 { field => 'surname',
1857 type => 'mediumtext',
1860 { field => 'firstname',
1864 { field => 'initials',
1869 type => 'mediumtext',
1872 { field => 'othernames',
1873 type => 'mediumtext',
1876 { field => 'B_email',
1879 after => 'B_zipcode',
1882 field => 'streetnumber', # street number (hidden if streettable table is empty)
1883 type => 'varchar(10)',
1886 after => 'initials',
1889 field => 'streettype', # street table, list builded from a system table
1890 type => 'varchar(50)',
1893 after => 'streetnumber',
1900 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1901 type => 'varchar(10)',
1906 field => 'B_streettype', # street table, list builded from a system table
1907 type => 'varchar(50)',
1909 after => 'B_streetnumber',
1912 field => 'phonepro',
1918 field => 'address2', # complement address
1924 field => 'emailpro',
1930 field => 'contactfirstname', # contact's firstname
1933 after => 'contactname',
1936 field => 'contacttitle', # contact's title
1939 after => 'contactfirstname',
1943 type => 'varchar(1)',
1952 field => 'borrowernumber',
1954 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1960 field => 'itemnumber',
1962 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1968 field => 'branchcode',
1969 type => 'varchar(10)',
1976 field => 'issuedate',
1985 type => 'varchar(4)',
1992 field => 'issuingbranch',
1993 type => 'varchar(18)',
2002 field => 'categorycode',
2003 type => 'varchar(10)',
2009 field => 'branchcode',
2010 type => 'varchar(10)',
2016 field => 'itemtype',
2017 type => 'varchar(10)',
2034 field => 'cutterextra',
2035 type => 'varchar(45)',
2042 field => 'homebranch',
2043 type => 'varchar(10)',
2050 field => 'holdingbranch',
2051 type => 'varchar(10)',
2059 type => 'varchar(10)',
2067 type => 'mediumtext',
2074 field => 'itemnotes',
2075 type => 'mediumtext',
2086 type => 'mediumtext',
2093 field => 'itemnotes',
2094 type => 'mediumtext',
2104 field => 'itemtype',
2105 type => 'varchar(10)',
2112 field => 'description',
2113 type => 'MEDIUMTEXT',
2135 marc_subfield_structure => [
2137 field => 'defaultvalue',
2144 field => 'authtypecode',
2145 type => 'varchar(20)',
2152 field => 'tagfield',
2153 type => 'varchar(3)',
2160 field => 'tagsubfield',
2161 type => 'varchar(1)',
2168 field => 'authorised_value',
2169 type => 'varchar(20)',
2177 type => 'varchar(1100)',
2185 marc_tag_structure => [
2187 field => 'tagfield',
2188 type => 'varchar(3)',
2195 field => 'liblibrarian',
2196 type => 'varchar(255)',
2204 type => 'varchar(255)',
2211 field => 'authorised_value',
2212 type => 'varchar(10)',
2219 field => 'frameworkcode',
2220 type => 'varchar(4)',
2230 field => 'expirationdate',
2248 field => 'printername',
2249 type => 'varchar(40)',
2256 field => 'printqueue',
2257 type => 'varchar(20)',
2264 field => 'printtype',
2265 type => 'varchar(20)',
2273 reserveconstraints => [
2275 field => 'reservedate',
2286 field => 'waitingdate',
2294 field => 'reservedate',
2302 field => 'constrainttype',
2303 type => 'varchar(1)',
2308 after => 'biblionumber',
2311 field => 'branchcode',
2312 type => 'varchar(10)',
2319 field => 'reservenotes',
2320 type => 'mediumtext',
2328 type => 'varchar(1)',
2338 field => 'planneddate',
2352 after => 'planneddate',
2358 field => 'dateadded',
2359 type => 'timestamp',
2367 type => 'varchar(10)',
2371 field => 'datetime',
2377 field => 'itemtype',
2378 type => 'varchar(10)',
2383 type => 'mediumtext',
2390 field => 'startdate',
2399 type => 'mediumtext',
2406 field => 'monthlength',
2415 subscriptionhistory => [
2417 field => 'histstartdate',
2434 systempreferences => [
2437 type => 'mediumtext',
2452 field => 'explanation',
2471 type => 'mediumtext',
2481 type => 'varchar(30)',
2488 field => 'flagdesc',
2489 type => 'varchar(255)',
2499 type => 'mediumtext',
2510 # { indexname => 'index detail'
2514 { indexname => 'accountoffsets_ibfk_1',
2515 content => 'borrowernumber',
2519 { indexname => 'PRIMARY',
2525 { indexname => 'booksellerid',
2526 content => 'booksellerid',
2530 { indexname => 'basketno',
2531 content => 'basketno',
2534 aqorderbreakdown => [
2535 { indexname => 'ordernumber',
2536 content => 'ordernumber',
2538 { indexname => 'bookfundid',
2539 content => 'bookfundid',
2543 { indexname => 'isbn',
2546 { indexname => 'publishercode',
2547 content => 'publishercode',
2552 indexname => 'borrowernumber',
2553 content => 'borrowernumber',
2560 indexname => 'branchcode',
2561 content => 'branchcode',
2565 branchrelations => [
2567 indexname => 'PRIMARY',
2568 content => 'categorycode',
2572 branchrelations => [
2573 { indexname => 'PRIMARY',
2574 content => 'branchcode,categorycode',
2577 { indexname => 'branchcode',
2578 content => 'branchcode',
2580 { indexname => 'categorycode',
2581 content => 'categorycode',
2585 { indexname => 'PRIMARY',
2586 content => 'currency',
2592 indexname => 'categorycode',
2593 content => 'categorycode',
2598 indexname => 'categorycode',
2599 content => 'categorycode',
2602 indexname => 'itemtype',
2603 content => 'itemtype',
2607 { indexname => 'homebranch',
2608 content => 'homebranch',
2610 { indexname => 'holdingbranch',
2611 content => 'holdingbranch',
2616 indexname => 'itemtype',
2617 content => 'itemtype',
2621 { indexname => 'shelfnumber',
2622 content => 'shelfnumber',
2624 { indexname => 'itemnumber',
2625 content => 'itemnumber',
2629 { indexname => 'PRIMARY',
2636 my %foreign_keys = (
2638 # { key => 'the key in table' (must be indexed)
2639 # foreigntable => 'the foreigntable name', # (the parent)
2640 # foreignkey => 'the foreign key column(s)' # (in the parent)
2641 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2642 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2645 branchrelations => [
2646 { key => 'branchcode',
2647 foreigntable => 'branches',
2648 foreignkey => 'branchcode',
2649 onUpdate => 'CASCADE',
2650 onDelete => 'CASCADE',
2652 { key => 'categorycode',
2653 foreigntable => 'branchcategories',
2654 foreignkey => 'categorycode',
2655 onUpdate => 'CASCADE',
2656 onDelete => 'CASCADE',
2660 { key => 'shelfnumber',
2661 foreigntable => 'bookshelf',
2662 foreignkey => 'shelfnumber',
2663 onUpdate => 'CASCADE',
2664 onDelete => 'CASCADE',
2666 { key => 'itemnumber',
2667 foreigntable => 'items',
2668 foreignkey => 'itemnumber',
2669 onUpdate => 'CASCADE',
2670 onDelete => 'CASCADE',
2673 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
2674 # easily deleted, but branches/itemtype not too easy to empty...
2676 { key => 'biblionumber',
2677 foreigntable => 'biblio',
2678 foreignkey => 'biblionumber',
2679 onUpdate => 'CASCADE',
2680 onDelete => 'CASCADE',
2684 { key => 'biblioitemnumber',
2685 foreigntable => 'biblioitems',
2686 foreignkey => 'biblioitemnumber',
2687 onUpdate => 'CASCADE',
2688 onDelete => 'CASCADE',
2690 { key => 'homebranch',
2691 foreigntable => 'branches',
2692 foreignkey => 'branchcode',
2693 onUpdate => 'CASCADE',
2694 onDelete => 'RESTRICT',
2696 { key => 'holdingbranch',
2697 foreigntable => 'branches',
2698 foreignkey => 'branchcode',
2699 onUpdate => 'CASCADE',
2700 onDelete => 'RESTRICT',
2704 { key => 'booksellerid',
2705 foreigntable => 'aqbooksellers',
2707 onUpdate => 'CASCADE',
2708 onDelete => 'RESTRICT',
2712 { key => 'basketno',
2713 foreigntable => 'aqbasket',
2714 foreignkey => 'basketno',
2715 onUpdate => 'CASCADE',
2716 onDelete => 'CASCADE',
2718 { key => 'biblionumber',
2719 foreigntable => 'biblio',
2720 foreignkey => 'biblionumber',
2721 onUpdate => 'SET NULL',
2722 onDelete => 'SET NULL',
2726 { key => 'listprice',
2727 foreigntable => 'currency',
2728 foreignkey => 'currency',
2729 onUpdate => 'CASCADE',
2730 onDelete => 'CASCADE',
2732 { key => 'invoiceprice',
2733 foreigntable => 'currency',
2734 foreignkey => 'currency',
2735 onUpdate => 'CASCADE',
2736 onDelete => 'CASCADE',
2739 aqorderbreakdown => [
2740 { key => 'ordernumber',
2741 foreigntable => 'aqorders',
2742 foreignkey => 'ordernumber',
2743 onUpdate => 'CASCADE',
2744 onDelete => 'CASCADE',
2746 { key => 'bookfundid',
2747 foreigntable => 'aqbookfund',
2748 foreignkey => 'bookfundid',
2749 onUpdate => 'CASCADE',
2750 onDelete => 'CASCADE',
2753 branchtransfers => [
2754 { key => 'frombranch',
2755 foreigntable => 'branches',
2756 foreignkey => 'branchcode',
2757 onUpdate => 'CASCADE',
2758 onDelete => 'CASCADE',
2760 { key => 'tobranch',
2761 foreigntable => 'branches',
2762 foreignkey => 'branchcode',
2763 onUpdate => 'CASCADE',
2764 onDelete => 'CASCADE',
2766 { key => 'itemnumber',
2767 foreigntable => 'items',
2768 foreignkey => 'itemnumber',
2769 onUpdate => 'CASCADE',
2770 onDelete => 'CASCADE',
2773 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
2775 { key => 'borrowernumber',
2776 foreigntable => 'borrowers',
2777 foreignkey => 'borrowernumber',
2778 onUpdate => 'SET NULL',
2779 onDelete => 'SET NULL',
2781 { key => 'itemnumber',
2782 foreigntable => 'items',
2783 foreignkey => 'itemnumber',
2784 onUpdate => 'SET NULL',
2785 onDelete => 'SET NULL',
2789 { key => 'borrowernumber',
2790 foreigntable => 'borrowers',
2791 foreignkey => 'borrowernumber',
2792 onUpdate => 'CASCADE',
2793 onDelete => 'CASCADE',
2795 { key => 'biblionumber',
2796 foreigntable => 'biblio',
2797 foreignkey => 'biblionumber',
2798 onUpdate => 'CASCADE',
2799 onDelete => 'CASCADE',
2801 { key => 'itemnumber',
2802 foreigntable => 'items',
2803 foreignkey => 'itemnumber',
2804 onUpdate => 'CASCADE',
2805 onDelete => 'CASCADE',
2807 { key => 'branchcode',
2808 foreigntable => 'branches',
2809 foreignkey => 'branchcode',
2810 onUpdate => 'CASCADE',
2811 onDelete => 'CASCADE',
2814 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
2815 # but prevent deleting a branch as soon as it has 1 borrower !
2816 { key => 'categorycode',
2817 foreigntable => 'categories',
2818 foreignkey => 'categorycode',
2819 onUpdate => 'RESTRICT',
2820 onDelete => 'RESTRICT',
2822 { key => 'branchcode',
2823 foreigntable => 'branches',
2824 foreignkey => 'branchcode',
2825 onUpdate => 'RESTRICT',
2826 onDelete => 'RESTRICT',
2830 { key => 'borrowernumber',
2831 foreigntable => 'borrowers',
2832 foreignkey => 'borrowernumber',
2833 onUpdate => 'CASCADE',
2834 onDelete => 'CASCADE',
2836 { key => 'itemnumber',
2837 foreigntable => 'items',
2838 foreignkey => 'itemnumber',
2839 onUpdate => 'SET NULL',
2840 onDelete => 'SET NULL',
2844 { key => 'borrowernumber',
2845 foreigntable => 'borrowers',
2846 foreignkey => 'borrowernumber',
2847 onUpdate => 'CASCADE',
2848 onDelete => 'CASCADE',
2851 auth_tag_structure => [
2852 { key => 'authtypecode',
2853 foreigntable => 'auth_types',
2854 foreignkey => 'authtypecode',
2855 onUpdate => 'CASCADE',
2856 onDelete => 'CASCADE',
2859 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
2864 my %column_change = (
2868 from => 'emailaddress',
2873 from => 'streetaddress',
2875 after => 'initials',
2878 from => 'faxnumber',
2883 from => 'textmessaging',
2889 to => 'contactnote',
2890 after => 'opacnote',
2893 from => 'physstreet',
2898 from => 'streetcity',
2900 after => 'B_address',
2913 from => 'homezipcode',
2920 after => 'B_zipcode',
2925 after => 'dateenrolled',
2928 from => 'guarantor',
2929 to => 'guarantorid',
2930 after => 'contactname',
2933 from => 'altrelationship',
2934 to => 'relationship',
2935 after => 'borrowernotes',
2939 deletedborrowers => [
2941 from => 'emailaddress',
2946 from => 'streetaddress',
2948 after => 'initials',
2951 from => 'faxnumber',
2956 from => 'textmessaging',
2962 to => 'contactnote',
2963 after => 'opacnote',
2966 from => 'physstreet',
2971 from => 'streetcity',
2973 after => 'B_address',
2986 from => 'homezipcode',
2993 after => 'B_zipcode',
2998 after => 'dateenrolled',
3001 from => 'guarantor',
3002 to => 'guarantorid',
3003 after => 'contactname',
3006 from => 'altrelationship',
3007 to => 'relationship',
3008 after => 'borrowernotes',
3014 # MOVE all tables TO UTF-8 and innoDB
3015 $sth = $dbh->prepare("show table status");
3017 while ( my $table = $sth->fetchrow_hashref ) {
3018 next if $table->{Name} eq 'marc_word';
3019 next if $table->{Name} eq 'marc_subfield_table';
3020 next if $table->{Name} eq 'auth_word';
3021 next if $table->{Name} eq 'auth_subfield_table';
3022 if ($table->{Engine} ne 'InnoDB') {
3023 print "moving $table->{Name} to InnoDB\n";
3024 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
3026 unless ($table->{Collation} =~ /^utf8/) {
3027 print "moving $table->{Name} to utf8\n";
3028 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
3029 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
3030 # 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 !
3035 # list of columns that must exist for %column_change to be
3036 # processed without error, but which do not necessarily exist
3037 # in all 2.2 databases
3038 my %required_prereq_fields = (
3039 deletedborrowers => [
3040 [ 'textmessaging', 'mediumtext AFTER faxnumber' ],
3041 [ 'password', 'varchar(30) default NULL' ],
3042 [ 'flags', 'int(11) default NULL' ],
3043 [ 'userid', 'varchar(30) default NULL' ],
3044 [ 'homezipcode', 'varchar(25) default NULL' ],
3045 [ 'zipcode', 'varchar(25) default NULL' ],
3046 [ 'sort1', 'varchar(80) default NULL' ],
3047 [ 'sort2', 'varchar(80) default NULL' ],
3051 foreach $table ( keys %required_prereq_fields ) {
3052 print "Check table $table\n" if $debug and not $silent;
3053 $sth = $dbh->prepare("show columns from $table");
3056 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3058 $types{$column} = $type;
3060 foreach my $entry ( @{ $required_prereq_fields{$table} } ) {
3061 ($column, $type) = @{ $entry };
3062 print " Check column $column [$type]\n" if $debug and not $silent;
3063 if ( !$types{$column} ) {
3065 # column doesn't exist
3066 print "Adding $column field to $table table...\n" unless $silent;
3067 $query = "alter table $table
3068 add column $column " . $type;
3069 print "Execute: $query\n" if $debug;
3070 my $sti = $dbh->prepare($query);
3073 print "**Error : $sti->errstr \n";
3080 foreach my $table (keys %column_change) {
3081 $sth = $dbh->prepare("show columns from $table");
3084 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3086 $types{$column}->{type} ="$type";
3087 $types{$column}->{null} = "$null";
3088 $types{$column}->{key} = "$key";
3089 $types{$column}->{default} = "$default";
3090 $types{$column}->{extra} = "$extra";
3092 my $tablerows = $column_change{$table};
3093 foreach my $row ( @$tablerows ) {
3094 if ($types{$row->{from}}->{type}) {
3095 print "altering $table $row->{from} to $row->{to}\n";
3096 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
3097 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
3099 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
3100 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
3101 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
3102 "$types{$row->{from}}->{extra} after $row->{after} ";
3109 # Enter here the field you want to delete from DB.
3110 # FIXME :: there is a %uselessfield before which seems doing the same things.
3111 my %fieldtodelete = (
3112 # tablename => [fieldname1,fieldname2,...]
3116 print "removing some unused fields...\n";
3117 foreach my $table ( keys %fieldtodelete ) {
3118 foreach my $field ( @{$fieldtodelete{$table}} ){
3119 print "removing ".$field." from ".$table;
3120 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
3123 print "Error : $sth->errstr \n";
3128 # Enter here the line you want to remove from DB.
3129 my %linetodelete = (
3130 # table name => where clause.
3131 userflags => [ "bit = 8" ], # delete the 'reserveforself' flags
3135 #-------------------
3140 # Get version of MySQL database engine.
3141 my $mysqlversion = `mysqld --version`;
3142 $mysqlversion =~ /Ver (\S*) /;
3144 if ( $mysqlversion ge '3.23' ) {
3145 print "Could convert to MyISAM database tables...\n" unless $silent;
3148 #---------------------------------
3151 # Collect all tables into a list
3152 $sth = $dbh->prepare("show tables");
3154 while ( my ($table) = $sth->fetchrow ) {
3155 $existingtables{$table} = 1;
3159 # Now add any missing tables
3160 foreach $table ( keys %requiretables ) {
3161 unless ( $existingtables{$table} ) {
3162 print "Adding $table table...\n" unless $silent;
3163 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
3166 print "Error : $sth->errstr \n";
3172 #---------------------------------
3175 foreach $table ( keys %requirefields ) {
3176 print "Check table $table\n" if $debug and not $silent;
3177 $sth = $dbh->prepare("show columns from $table");
3180 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3182 $types{$column} = $type;
3184 foreach $column ( keys %{ $requirefields{$table} } ) {
3185 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
3186 if ( !$types{$column} ) {
3188 # column doesn't exist
3189 print "Adding $column field to $table table...\n" unless $silent;
3190 $query = "alter table $table
3191 add column $column " . $requirefields{$table}->{$column};
3192 print "Execute: $query\n" if $debug;
3193 my $sti = $dbh->prepare($query);
3196 print "**Error : $sti->errstr \n";
3203 foreach $table ( sort keys %fielddefinitions ) {
3204 print "Check table $table\n" if $debug;
3205 $sth = $dbh->prepare("show columns from $table");
3208 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3210 $definitions->{$column}->{type} = $type;
3211 $definitions->{$column}->{null} = $null;
3212 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
3213 $definitions->{$column}->{key} = $key;
3214 $definitions->{$column}->{default} = $default;
3215 $definitions->{$column}->{extra} = $extra;
3217 my $fieldrow = $fielddefinitions{$table};
3218 foreach my $row (@$fieldrow) {
3219 my $field = $row->{field};
3220 my $type = $row->{type};
3221 my $null = $row->{null};
3222 # $null = 'YES' if $row->{null} eq 'NULL';
3223 my $key = $row->{key};
3224 my $default = $row->{default};
3225 # $default="''" unless $default;
3226 my $extra = $row->{extra};
3227 my $def = $definitions->{$field};
3228 my $after = ($row->{after}?" after ".$row->{after}:"");
3230 unless ( $type eq $def->{type}
3231 && $null eq $def->{null}
3232 && $key eq $def->{key}
3233 && $default eq $def->{default}
3234 && $extra eq $def->{extra} )
3236 if ( $null eq '' ) {
3239 if ( $key eq 'PRI' ) {
3240 $key = 'PRIMARY KEY';
3242 unless ( $extra eq 'auto_increment' ) {
3246 # if it's a new column use "add", if it's an old one, use "change".
3248 if ($definitions->{$field}->{type}) {
3249 $action="change `$field`"
3253 # if it's a primary key, drop the previous pk, before altering the table
3254 print " alter or create $field in $table\n" unless $silent;
3256 if ($key ne 'PRIMARY KEY') {
3257 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
3258 $query = "alter table $table $action `$field` $type $null $key $extra ".
3259 GetDefaultClause($default)." $after";
3261 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
3262 # something strange : for indexes UNIQUE, they are reported as primary key here.
3263 # but if you try to run with drop primary key, it fails.
3264 # thus, we run the query twice, one will fail, one will succeed.
3266 $query="alter table $table drop primary key, $action `$field` $type $null $key $extra ".
3267 GetDefaultClause($default)." $after";
3268 $query="alter table $table $action `$field` $type $null $key $extra ".
3269 GetDefaultClause($default)." $after";
3271 $dbh->do($query) or warn "Error while executing: $query";
3276 print "removing some unused data...\n";
3277 foreach my $table ( keys %linetodelete ) {
3278 foreach my $where ( @{$linetodelete{$table}} ){
3279 print "DELETE FROM ".$table." where ".$where;
3281 my $sth = $dbh->prepare("DELETE FROM $table where $where");
3284 print "Error : $sth->errstr \n";
3289 # Populate tables with required data
3291 # synch table and deletedtable.
3292 foreach my $table (('borrowers','items','biblio','biblioitems')) {
3293 my %deletedborrowers;
3294 print "synch'ing $table and deleted$table\n";
3295 $sth = $dbh->prepare("show columns from deleted$table");
3297 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3298 $deletedborrowers{$column}=1;
3300 $sth = $dbh->prepare("show columns from $table");
3303 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3304 unless ($deletedborrowers{$column}) {
3305 my $newcol="alter table deleted$table add $column $type";
3306 if ($null eq 'YES') {
3307 $newcol .= " NULL ";
3309 $newcol .= " NOT NULL ";
3311 $newcol .= "default ".$dbh->quote($default) if $default;
3312 $newcol .= " after $previous" if $previous;
3314 print "creating column $column\n";
3320 # update publisheddate
3322 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
3324 my ($emptypublished) = $sth->fetchrow;
3325 if ($emptypublished) {
3326 print "Updating publisheddate\n";
3327 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
3329 # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it.
3330 # now, let's get rid of 000-00-00's.
3332 $dbh->do("update serial set publisheddate=NULL where publisheddate = 0");
3333 $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0");
3335 foreach my $table ( keys %tabledata ) {
3336 print "Checking for data required in table $table...\n" unless $silent;
3337 my $tablerows = $tabledata{$table};
3338 foreach my $row (@$tablerows) {
3339 my $uniquefieldrequired = $row->{uniquefieldrequired};
3340 my $uniquevalue = $row->{$uniquefieldrequired};
3341 my $forceupdate = $row->{forceupdate};
3344 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
3346 $sth->execute($uniquevalue);
3348 foreach my $field (keys %$forceupdate) {
3349 if ($forceupdate->{$field}) {
3350 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
3351 $sth->execute($row->{$field}, $uniquevalue);
3355 print "Adding row to $table: " unless $silent;
3359 foreach my $field ( keys %$row ) {
3360 next if $field eq 'uniquefieldrequired';
3361 next if $field eq 'forceupdate';
3362 my $value = $row->{$field};
3363 push @values, $value;
3364 print " $field => $value" unless $silent;
3365 $fieldlist .= "$field,";
3366 $placeholders .= "?,";
3368 print "\n" unless $silent;
3369 $fieldlist =~ s/,$//;
3370 $placeholders =~ s/,$//;
3371 print "insert into $table ($fieldlist) values ($placeholders)";
3374 "insert into $table ($fieldlist) values ($placeholders)");
3375 $sth->execute(@values);
3381 # check indexes and create them when needed
3383 print "Checking for index required...\n" unless $silent;
3384 foreach my $table ( keys %indexes ) {
3386 # read all indexes from $table
3388 $sth = $dbh->prepare("show index from $table");
3390 my %existingindexes;
3391 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
3392 $existingindexes{$key_name} = 1;
3394 # read indexes to check
3395 my $tablerows = $indexes{$table};
3396 foreach my $row (@$tablerows) {
3397 my $key_name=$row->{indexname};
3398 if ($existingindexes{$key_name} eq 1 and not $row->{force}) {
3399 # print "$key_name existing";
3401 print "\tCreating index $key_name in $table\n";
3403 if ($row->{indexname} eq 'PRIMARY' or $row->{type} eq 'PRI') {
3404 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
3406 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
3409 print "Error $sql : $dbh->err \n" if $dbh->err;
3415 # check foreign keys and create them when needed
3417 print "Checking for foreign keys required...\n" unless $silent;
3418 foreach my $table ( sort keys %foreign_keys ) {
3420 # read all indexes from $table
3422 $sth = $dbh->prepare("show table status like '$table'");
3424 my $stat = $sth->fetchrow_hashref;
3425 # read indexes to check
3426 my $tablerows = $foreign_keys{$table};
3427 foreach my $row (@$tablerows) {
3428 my $foreign_table=$row->{foreigntable};
3429 if ($stat->{'Comment'} =~/$foreign_table/) {
3430 # print "$foreign_table existing\n";
3432 print "\tCreating foreign key $foreign_table in $table\n";
3433 # first, drop any orphan value in child table
3434 if ($row->{onDelete} ne "RESTRICT") {
3435 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
3437 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
3439 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
3440 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
3441 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
3444 print "====================
3445 An error occured during :
3447 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).
3448 You can find those values with select
3449 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
3450 ====================\n
3456 # now drop useless tables
3457 foreach $table ( @TableToDelete ) {
3458 if ( $existingtables{$table} ) {
3459 print "Dropping unused table $table\n" if $debug and not $silent;
3460 $dbh->do("drop table $table");
3462 print "Error : $dbh->errstr \n";
3471 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
3474 # 1st, get how many biblio we will have to do...
3475 $sth = $dbh->prepare('select count(*) from marc_biblio');
3477 my ($totaltodo) = $sth->fetchrow;
3479 $sth = $dbh->prepare("show columns from biblio");
3482 my $bibliofwexist=0;
3483 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
3484 $bibliofwexist=1 if $column eq 'frameworkcode';
3486 unless ($bibliofwexist) {
3487 print "moving biblioframework to biblio table\n";
3488 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
3489 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
3491 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
3493 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
3494 $sth_update->execute($frameworkcode,$biblionumber);
3496 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
3501 # at last, remove useless fields
3502 foreach $table ( keys %uselessfields ) {
3503 my @fields = split /,/,$uselessfields{$table};
3506 foreach my $fieldtodrop (@fields) {
3507 $fieldtodrop =~ s/\t//g;
3508 $fieldtodrop =~ s/\n//g;
3510 $sth = $dbh->prepare("show columns from $table");
3512 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3514 $exists =1 if ($column eq $fieldtodrop);
3517 print "deleting $fieldtodrop field in $table...\n" unless $silent;
3518 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
3525 # Changing aqbookfund's primary key
3527 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
3530 # drop extra key on borrowers.borrowernumber
3531 $dbh->do("ALTER TABLE borrowers DROP KEY borrowernumber");
3534 print "upgrade to Koha 3.0 done\n";
3535 SetVersion ($DBversion);
3538 =item GetDefaultClause
3540 Generate a default clause (for an ALTER TABLE command)
3543 sub GetDefaultClause {
3544 my $default = shift;
3546 return "" unless defined $default;
3547 return "" if $default eq '';
3548 return "default ''" if $default eq "''";
3549 return "default NULL" if $default eq "NULL";
3550 return "default " . $dbh->quote($default);
3553 =item TransformToNum
3555 Transform the Koha version from a 4 parts string
3556 to a number, with just 1 .
3560 sub TransformToNum {
3561 my $version = shift;
3562 # remove the 3 last . to have a Perl number
3563 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3568 set the DBversion in the systempreferences
3572 my $kohaversion = TransformToNum(shift);
3573 if (C4::Context->preference('Version')) {
3574 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3575 $finish->execute($kohaversion);
3577 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')");
3578 $finish->execute($kohaversion);
3583 # Revision 1.172 2007/07/19 10:21:22 hdl