5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha-community.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.
15 #use warnings; FIXME - Bug 2505
24 use MARC::File::XML ( BinaryEncoding => 'utf8' );
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
46 my $dbh = C4::Context->dbh;
47 $|=1; # flushes output
49 my $DBversion = "3.00.00.000";
50 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
51 # Tables to add if they don't exist
54 `timestamp` TIMESTAMP NOT NULL ,
55 `user` INT( 11 ) NOT NULL default '0' ,
56 `module` TEXT default '',
57 `action` TEXT default '' ,
58 `object` INT(11) NULL ,
59 `info` TEXT default '' ,
60 PRIMARY KEY ( `timestamp` , `user` )
63 module varchar(20) NOT NULL default '',
64 code varchar(20) NOT NULL default '',
65 name varchar(100) NOT NULL default '',
66 title varchar(200) NOT NULL default '',
68 PRIMARY KEY (module,code)
71 alertid int(11) NOT NULL auto_increment,
72 borrowernumber int(11) NOT NULL default '0',
73 type varchar(10) NOT NULL default '',
74 externalid varchar(20) NOT NULL default '',
75 PRIMARY KEY (alertid),
76 KEY borrowernumber (borrowernumber),
77 KEY type (type,externalid)
80 `idnew` int(10) unsigned NOT NULL auto_increment,
81 `title` varchar(250) NOT NULL default '',
83 `lang` varchar(4) NOT NULL default '',
84 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
87 repeatable_holidays => "(
88 `id` int(11) NOT NULL auto_increment,
89 `branchcode` varchar(10) NOT NULL default '',
90 `weekday` smallint(6) default NULL,
91 `day` smallint(6) default NULL,
92 `month` smallint(6) default NULL,
93 `title` varchar(50) NOT NULL default '',
94 `description` text NOT NULL,
97 special_holidays => "(
98 `id` int(11) NOT NULL auto_increment,
99 `branchcode` varchar(10) NOT NULL default '',
100 `day` smallint(6) NOT NULL default '0',
101 `month` smallint(6) NOT NULL default '0',
102 `year` smallint(6) NOT NULL default '0',
103 `isexception` smallint(1) NOT NULL default '1',
104 `title` varchar(50) NOT NULL default '',
105 `description` text NOT NULL,
108 overduerules =>"(`branchcode` varchar(10) NOT NULL default '',
109 `categorycode` varchar(2) NOT NULL default '',
110 `delay1` int(4) default '0',
111 `letter1` varchar(20) default NULL,
112 `debarred1` varchar(1) default '0',
113 `delay2` int(4) default '0',
114 `debarred2` varchar(1) default '0',
115 `letter2` varchar(20) default NULL,
116 `delay3` int(4) default '0',
117 `letter3` varchar(20) default NULL,
118 `debarred3` int(1) default '0',
119 PRIMARY KEY (`branchcode`,`categorycode`)
121 cities => "(`cityid` int auto_increment,
122 `city_name` varchar(100) NOT NULL default '',
123 `city_zipcode` varchar(20),
124 PRIMARY KEY (`cityid`)
126 roadtype => "(`roadtypeid` int auto_increment,
127 `road_type` varchar(100) NOT NULL default '',
128 PRIMARY KEY (`roadtypeid`)
132 labelid int(11) NOT NULL auto_increment,
133 batch_id varchar(10) NOT NULL default '1',
134 itemnumber varchar(100) NOT NULL default '',
135 timestamp timestamp(14) NOT NULL,
136 PRIMARY KEY (labelid)
140 id int(4) NOT NULL auto_increment,
141 barcodetype char(100) default '',
142 title int(1) default '0',
143 subtitle int(1) default '0',
144 itemtype int(1) default '0',
145 barcode int(1) default '0',
146 dewey int(1) default '0',
147 class int(1) default '0',
148 subclass int(1) default '0',
149 itemcallnumber int(1) default '0',
150 author int(1) default '0',
151 issn int(1) default '0',
152 isbn int(1) default '0',
153 startlabel int(2) NOT NULL default '1',
154 printingtype char(32) default 'BAR',
155 layoutname char(20) NOT NULL default 'TEST',
156 guidebox int(1) default '0',
157 active tinyint(1) default '1',
158 fonttype char(10) collate utf8_unicode_ci default NULL,
159 ccode char(4) collate utf8_unicode_ci default NULL,
160 callnum_split int(1) default NULL,
161 text_justify char(1) collate utf8_unicode_ci default NULL,
165 reviewid integer NOT NULL auto_increment,
166 borrowernumber integer,
167 biblionumber integer,
170 datereviewed datetime,
171 PRIMARY KEY (reviewid)
173 subscriptionroutinglist=>"(
174 routingid integer NOT NULL auto_increment,
175 borrowernumber integer,
177 subscriptionid integer,
178 PRIMARY KEY (routingid)
182 notify_id int(11) NOT NULL default '0',
183 `borrowernumber` int(11) NOT NULL default '0',
184 `itemnumber` int(11) NOT NULL default '0',
185 `notify_date` date default NULL,
186 `notify_send_date` date default NULL,
187 `notify_level` int(1) NOT NULL default '0',
188 `method` varchar(20) NOT NULL default ''
192 `charge_id` varchar(5) NOT NULL default '',
193 `description` text NOT NULL,
194 `amount` decimal(28,6) NOT NULL default '0.000000',
195 `min` int(4) NOT NULL default '0',
196 `max` int(4) NOT NULL default '0',
197 `level` int(1) NOT NULL default '0',
198 PRIMARY KEY (`charge_id`)
201 `entry` varchar(255) NOT NULL default '',
202 `weight` bigint(20) NOT NULL default '0',
203 PRIMARY KEY (`entry`)
207 `id` int NOT NULL auto_increment,
208 `biblio_auth_number` int(11) NOT NULL default '0',
209 `operation` char(20) NOT NULL default '',
210 `server` char(20) NOT NULL default '',
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
216 my %requirefields = (
217 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 \'\''},
218 itemtypes => { 'imageurl' => 'varchar(200) NULL'},
219 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
220 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
221 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
222 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'},
223 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
224 serial =>{ 'publisheddate' => 'date AFTER planneddate', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
225 statistics => { 'associatedborrower' => 'integer'},
226 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
227 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
228 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
230 issues =>{ 'issuedate'=>"date NULL default NULL", },
232 # tablename => { 'field' => 'fieldtype' },
235 # Enter here the table to delete.
236 my @TableToDelete = qw(
243 my %uselessfields = (
244 # tablename => "field1,field2",
245 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
246 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
247 items => "multivolumepart,multivolume,binding",
248 deleteditems => "multivolumepart,multivolume,binding",
250 # the other hash contains other actions that can't be done elsewhere. they are done
251 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
253 # The tabledata hash contains data that should be in the tables.
254 # The uniquefieldrequired hash entry is used to determine which (if any) fields
255 # must not exist in the table for this row to be inserted. If the
256 # uniquefieldrequired entry is already in the table, the existing data is not
257 # modified, unless the forceupdate hash entry is also set. Fields in the
258 # anonymous "forceupdate" hash will be forced to be updated to the default
259 # values given in the %tabledata hash.
263 # { uniquefielrequired => 'fieldname', # the primary key in the table
264 # fieldname => fieldvalue,
265 # fieldname2 => fieldvalue2,
268 systempreferences => [
270 uniquefieldrequired => 'variable',
271 variable => 'useDaysMode',
273 forceupdate => { 'explanation' => 1,
275 explanation => 'Choose the method for calculating due date: select Calendar to use the holidays module, and Days to ignore the holidays module',
277 options => 'Calendar|Days|Datedue'
280 uniquefieldrequired => 'variable',
281 variable => 'DebugLevel',
283 forceupdate => { 'explanation' => 1,
285 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
290 uniquefieldrequired => 'variable',
291 variable => 'BorrowersTitles',
292 value => 'Mr|Mrs|Miss|Ms',
293 forceupdate => { 'explanation' => 1,
295 explanation => 'List all Titles for borrowers',
299 uniquefieldrequired => 'variable',
300 variable => 'BorrowerMandatoryField',
301 value => 'cardnumber|surname|address',
302 forceupdate => { 'explanation' => 1,
304 explanation => 'List all mandatory fields for borrowers',
308 uniquefieldrequired => 'variable',
309 variable => 'borrowerRelationship',
310 value => 'father|mother,grand-mother',
311 forceupdate => { 'explanation' => 1,
313 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
317 uniquefieldrequired => 'variable',
318 variable => 'ReservesMaxPickUpDelay',
320 forceupdate => { 'explanation' => 1,
322 explanation => 'Maximum delay to pick up a reserved document',
326 uniquefieldrequired => 'variable',
327 variable => 'TransfersMaxDaysWarning',
329 forceupdate => { 'explanation' => 1,
331 explanation => 'Max delay before considering the transfer has potentialy a problem',
335 uniquefieldrequired => 'variable',
336 variable => 'memberofinstitution',
338 forceupdate => { 'explanation' => 1,
340 explanation => 'Are your patrons members of institutions',
344 uniquefieldrequired => 'variable',
345 variable => 'ReadingHistory',
347 forceupdate => { 'explanation' => 1,
349 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
353 uniquefieldrequired => 'variable',
354 variable => 'IssuingInProcess',
356 forceupdate => { 'explanation' => 1,
358 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
362 uniquefieldrequired => 'variable',
363 variable => 'AutomaticItemReturn',
365 forceupdate => { 'explanation' => 1,
367 explanation => 'This Variable allow or not to return automaticly to his homebranch',
371 uniquefieldrequired => 'variable',
372 variable => 'reviewson',
374 forceupdate => { 'explanation' => 1,
376 explanation => 'Allows patrons to submit reviews from the opac',
380 uniquefieldrequired => 'variable',
381 variable => 'intranet_includes',
383 forceupdate => { 'explanation' => 1,
385 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
389 uniquefieldrequired => 'variable',
390 variable => 'AutoLocation',
392 forceupdate => { 'explanation' => 1,
394 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
398 uniquefieldrequired => 'variable',
399 variable => 'serialsadditems',
405 explanation => 'If set, a new item will be automatically added when receiving an issue',
409 uniquefieldrequired => 'variable',
410 variable => 'expandedSearchOption',
416 explanation => 'search among marc field',
420 uniquefieldrequired => 'variable',
421 variable => 'RequestOnOpac',
423 forceupdate => { 'explanation' => 1,
425 explanation => 'option to allow reserves on opac',
429 uniquefieldrequired => 'variable',
430 variable => 'OpacCloud',
432 forceupdate => { 'explanation' => 1,
434 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
438 uniquefieldrequired => 'variable',
439 variable => 'OpacBrowser',
441 forceupdate => { 'explanation' => 1,
443 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
447 uniquefieldrequired => 'variable',
448 variable => 'OpacTopissue',
450 forceupdate => { 'explanation' => 1,
452 explanation => 'If ON, enables the \'most popular items\' link on OPAC. Warning, this is an EXPERIMENTAL feature, turning ON may overload your server',
456 uniquefieldrequired => 'variable',
457 variable => 'OpacAuthorities',
459 forceupdate => { 'explanation' => 1,
461 explanation => 'Enable / Disable the search authority link on OPAC',
465 uniquefieldrequired => 'variable',
466 variable => 'CataloguingLog',
468 forceupdate => {'explanation' => 1, 'type' => 1},
469 explanation => 'Active this if you want to log cataloguing action.',
473 uniquefieldrequired => 'variable',
474 variable => 'BorrowersLog',
476 forceupdate => {'explanation' => 1, 'type' => 1},
477 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
481 uniquefieldrequired => 'variable',
482 variable => 'SubscriptionLog',
484 forceupdate => {'explanation' => 1, 'type' => 1},
485 explanation => 'Active this if you want to log Subscription action',
489 uniquefieldrequired => 'variable',
490 variable => 'IssueLog',
492 forceupdate => {'explanation' => 1, 'type' => 1},
493 explanation => 'Active this if you want to log issue.',
497 uniquefieldrequired => 'variable',
498 variable => 'ReturnLog',
500 forceupdate => {'explanation' => 1, 'type' => 1},
501 explanation => 'Active this if you want to log the circulation return',
505 uniquefieldrequired => 'variable',
506 variable => 'Version',
508 forceupdate => {'explanation' => 1, 'type' => 1},
509 explanation => 'Koha Version',
513 uniquefieldrequired => 'variable',
514 variable => 'LetterLog',
516 forceupdate => {'explanation' => 1, 'type' => 1},
517 explanation => 'Active this if you want to log all the letter sent',
521 uniquefieldrequired => 'variable',
522 variable => 'FinesLog',
524 forceupdate => {'explanation' => 1, 'type' => 1},
525 explanation => 'Active this if you want to log fines',
529 uniquefieldrequired => 'variable',
530 variable => 'NoZebra',
532 forceupdate => {'explanation' => 1, 'type' => 1},
533 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
537 uniquefieldrequired => 'variable',
538 variable => 'NoZebraIndexes',
540 forceupdate => {'explanation' => 1, 'type' => 1},
541 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
545 uniquefieldrequired => 'variable',
546 variable => 'uppercasesurnames',
548 forceupdate => {'explanation' => 1, 'type' => 1},
549 explanation => "Force Surnames to be uppercase",
555 uniquefieldrequired => 'bit',
557 flag => 'editauthorities',
558 flagdesc => 'allow to edit authorities',
562 uniquefieldrequired => 'bit',
565 flagdesc => 'allow to manage serials subscriptions',
569 uniquefieldrequired => 'bit',
572 flagdesc => 'allow to access to the reports module',
576 authorised_values => [
578 uniquefieldrequired => 'id',
579 category => 'SUGGEST',
580 authorised_value => 'Not enough budget',
581 lib => 'This book it too much expensive',
586 my %fielddefinitions = (
588 # { field => 'fieldname',
589 # type => 'fieldtype',
597 field => 'booksellerid',
605 field => 'booksellerinvoicenumber',
606 type => 'mediumtext',
615 field => 'bookfundid',
616 type => 'varchar(10)',
623 field => 'branchcode',
624 type => 'varchar(10)',
631 field => 'bookfundname',
632 type => 'mediumtext',
637 after => 'bookfundid',
648 extra => 'auto_increment',
652 type => 'varchar(3)',
659 field => 'listprice',
660 type => 'varchar(10)',
667 field => 'invoiceprice',
668 type => 'varchar(10)',
675 field => 'invoicedisc',
676 type => 'float(6,4)',
684 type => 'mediumtext',
692 type => 'mediumtext',
700 type => 'mediumtext',
708 type => 'mediumtext',
715 field => 'accountnumber',
716 type => 'mediumtext',
723 field => 'othersupplier',
724 type => 'mediumtext',
731 field => 'specialty',
732 type => 'mediumtext',
739 field => 'booksellerfax',
740 type => 'mediumtext',
748 type => 'mediumtext',
755 field => 'bookselleremail',
756 type => 'mediumtext',
763 field => 'booksellerurl',
764 type => 'mediumtext',
771 field => 'contnotes',
772 type => 'mediumtext',
780 type => 'mediumtext',
790 field => 'bookfundid',
791 type => 'varchar(10)',
798 field => 'branchcode',
799 type => 'varchar(10)',
807 aqorderbreakdown => [
809 field => 'bookfundid',
810 type => 'varchar(10)',
817 field => 'branchcode',
818 type => 'varchar(10)',
828 field => 'ordernumber',
836 field => 'deliverycomments',
837 type => 'mediumtext',
848 type => 'mediumtext',
856 type => 'varchar(3)',
863 field => 'booksellerinvoicenumber',
864 type => 'mediumtext',
872 type => 'mediumtext',
879 field => 'supplierreference',
880 type => 'mediumtext',
887 field => 'purchaseordernumber',
888 type => 'mediumtext',
898 field => 'notify_id',
906 field => 'notify_level',
914 field => 'accountno',
915 type => 'smallint(6)',
922 field => 'description',
923 type => 'mediumtext',
928 type => 'mediumtext',
936 field => 'authtypecode',
937 type => 'varchar(10)',
944 field => 'datecreated',
952 field => 'origincode',
953 type => 'varchar(20)',
960 field => 'authtrees',
961 type => 'mediumtext',
966 after => 'origincode',
970 auth_subfield_structure => [
972 field => 'authtypecode',
973 type => 'varchar(10)',
981 type => 'varchar(3)',
988 field => 'tagsubfield',
989 type => 'varchar(1)',
996 field => 'liblibrarian',
997 type => 'varchar(255)',
1005 type => 'varchar(255)',
1012 field => 'authorised_value',
1013 type => 'varchar(10)',
1020 field => 'value_builder',
1021 type => 'varchar(80)',
1029 type => 'varchar(255)',
1036 field => 'kohafield',
1037 type => 'varchar(45)',
1044 field => 'frameworkcode',
1045 type => 'varchar(8)',
1053 auth_tag_structure => [
1055 field => 'authtypecode',
1056 type => 'varchar(10)',
1063 field => 'tagfield',
1064 type => 'varchar(3)',
1071 field => 'liblibrarian',
1072 type => 'varchar(255)',
1080 type => 'varchar(255)',
1087 field => 'authorised_value',
1088 type => 'varchar(10)',
1098 field => 'auth_tag_to_report',
1099 type => 'varchar(3)',
1107 type => 'mediumtext',
1115 authorised_values => [
1117 field => 'category',
1118 type => 'varchar(10)',
1125 field => 'authorised_value',
1126 type => 'varchar(80)',
1134 type => 'varchar(80)',
1142 biblio_framework => [
1144 field => 'frameworkcode',
1145 type => 'varchar(4)',
1152 field => 'frameworktext',
1153 type => 'varchar(255)',
1163 field => 'cardnumber',
1164 type => 'varchar(16)',
1170 { field => 'surname',
1171 type => 'mediumtext',
1174 { field => 'firstname',
1179 type => 'mediumtext',
1182 { field => 'othernames',
1183 type => 'mediumtext',
1186 { field => 'initials',
1190 { field => 'B_email',
1193 after => 'B_zipcode',
1196 field => 'streetnumber', # street number (hidden if streettable table is empty)
1197 type => 'varchar(10)',
1199 after => 'initials',
1202 field => 'streettype', # street table, list builded from a system table
1203 type => 'varchar(50)',
1205 after => 'streetnumber',
1212 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1213 type => 'varchar(10)',
1218 field => 'B_streettype', # street table, list builded from a system table
1219 type => 'varchar(50)',
1221 after => 'B_streetnumber',
1224 field => 'phonepro',
1230 field => 'address2', # complement address
1236 field => 'emailpro',
1242 field => 'contactfirstname', # contact's firstname
1245 after => 'contactname',
1248 field => 'contacttitle', # contact's title
1251 after => 'contactfirstname',
1254 field => 'branchcode',
1255 type => 'varchar(10)',
1261 field => 'categorycode',
1262 type => 'varchar(10)',
1269 type => 'mediumtext',
1276 type => 'mediumtext',
1283 type => 'mediumtext',
1290 type => 'mediumtext',
1297 type => 'mediumtext',
1304 type => 'mediumtext',
1310 field => 'contactname',
1311 type => 'mediumtext',
1317 field => 'opacnote',
1318 type => 'mediumtext',
1324 field => 'borrowernotes',
1325 type => 'mediumtext',
1332 type => 'varchar(1)',
1341 field => 'itemtype',
1342 type => 'varchar(10)',
1350 type => 'varchar(25)',
1358 type => 'varchar(4)',
1366 type => 'varchar(30)',
1372 field => 'publicationyear',
1379 field => 'collectiontitle',
1380 type => 'mediumtext',
1384 after => 'volumeddesc',
1387 field => 'collectionissn',
1392 after => 'collectiontitle',
1395 field => 'collectionvolume',
1396 type => 'mediumtext',
1400 after => 'collectionissn',
1403 field => 'editionstatement',
1408 after => 'collectionvolume',
1411 field => 'editionresponsibility',
1416 after => 'editionstatement',
1420 type => 'mediumtext',
1427 type => 'mediumtext',
1434 type => 'mediumtext',
1444 type => 'mediumtext',
1451 type => 'mediumtext',
1457 field => 'unititle',
1458 type => 'mediumtext',
1464 field => 'seriestitle',
1465 type => 'mediumtext',
1471 field => 'abstract',
1472 type => 'mediumtext',
1479 type => 'mediumtext',
1485 field => 'frameworkcode',
1486 type => 'varchar(4)',
1490 after => 'biblionumber',
1497 type => 'mediumtext',
1504 type => 'mediumtext',
1510 field => 'unititle',
1511 type => 'mediumtext',
1517 field => 'seriestitle',
1518 type => 'mediumtext',
1524 field => 'abstract',
1525 type => 'mediumtext',
1532 type => 'mediumtext',
1538 field => 'frameworkcode',
1539 type => 'varchar(4)',
1543 after => 'biblionumber',
1546 deletedbiblioitems => [
1548 field => 'itemtype',
1549 type => 'varchar(10)',
1556 type => 'varchar(30)',
1562 field => 'itemtype',
1563 type => 'varchar(10)',
1570 type => 'mediumtext',
1577 type => 'mediumtext',
1584 type => 'mediumtext',
1593 field => 'shelfname',
1594 type => 'varchar(255)',
1601 type => 'varchar(80)',
1607 field => 'category',
1608 type => 'varchar(1)',
1615 branchcategories => [
1617 field => 'codedescription',
1618 type => 'mediumtext',
1627 field => 'branchip',
1628 type => 'varchar(15)',
1635 field => 'branchprinter',
1636 type => 'varchar(100)',
1643 field => 'branchcode',
1644 type => 'varchar(10)',
1650 field => 'branchname',
1651 type => 'mediumtext',
1657 field => 'branchaddress1',
1658 type => 'mediumtext',
1664 field => 'branchaddress2',
1665 type => 'mediumtext',
1671 field => 'branchaddress3',
1672 type => 'mediumtext',
1678 field => 'branchphone',
1679 type => 'mediumtext',
1685 field => 'branchfax',
1686 type => 'mediumtext',
1692 field => 'branchemail',
1693 type => 'mediumtext',
1700 branchrelations => [
1702 field => 'branchcode',
1703 type => 'VARCHAR(10)',
1710 field => 'categorycode',
1711 type => 'VARCHAR(10)',
1721 field => 'frombranch',
1722 type => 'VARCHAR(10)',
1729 field => 'tobranch',
1730 type => 'VARCHAR(10)',
1736 field => 'comments',
1737 type => 'mediumtext',
1746 field => 'category_type',
1747 type => 'varchar(1)',
1754 field => 'categorycode',
1755 type => 'varchar(10)',
1762 field => 'description',
1763 type => 'mediumtext',
1771 deletedborrowers => [
1773 field => 'branchcode',
1774 type => 'varchar(10)',
1780 field => 'categorycode',
1781 type => 'varchar(2)',
1788 type => 'mediumtext',
1794 field => 'borrowernotes',
1795 type => 'mediumtext',
1801 field => 'contactname',
1802 type => 'mediumtext',
1809 type => 'mediumtext',
1815 field => 'B_zipcode',
1816 type => 'varchar(25)',
1823 type => 'varchar(25)',
1831 type => 'mediumtext',
1838 type => 'mediumtext',
1845 type => 'mediumtext',
1852 type => 'mediumtext',
1857 { field => 'surname',
1858 type => 'mediumtext',
1861 { field => 'firstname',
1865 { field => 'initials',
1870 type => 'mediumtext',
1873 { field => 'othernames',
1874 type => 'mediumtext',
1877 { field => 'B_email',
1880 after => 'B_zipcode',
1883 field => 'streetnumber', # street number (hidden if streettable table is empty)
1884 type => 'varchar(10)',
1887 after => 'initials',
1890 field => 'streettype', # street table, list builded from a system table
1891 type => 'varchar(50)',
1894 after => 'streetnumber',
1901 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1902 type => 'varchar(10)',
1907 field => 'B_streettype', # street table, list builded from a system table
1908 type => 'varchar(50)',
1910 after => 'B_streetnumber',
1913 field => 'phonepro',
1919 field => 'address2', # complement address
1925 field => 'emailpro',
1931 field => 'contactfirstname', # contact's firstname
1934 after => 'contactname',
1937 field => 'contacttitle', # contact's title
1940 after => 'contactfirstname',
1944 type => 'varchar(1)',
1953 field => 'borrowernumber',
1955 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1961 field => 'itemnumber',
1963 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1969 field => 'branchcode',
1970 type => 'varchar(10)',
1977 field => 'issuedate',
1986 type => 'varchar(4)',
1993 field => 'issuingbranch',
1994 type => 'varchar(18)',
2003 field => 'categorycode',
2004 type => 'varchar(10)',
2010 field => 'branchcode',
2011 type => 'varchar(10)',
2017 field => 'itemtype',
2018 type => 'varchar(10)',
2035 field => 'cutterextra',
2036 type => 'varchar(45)',
2043 field => 'homebranch',
2044 type => 'varchar(10)',
2051 field => 'holdingbranch',
2052 type => 'varchar(10)',
2060 type => 'varchar(10)',
2068 type => 'mediumtext',
2075 field => 'itemnotes',
2076 type => 'mediumtext',
2087 type => 'mediumtext',
2094 field => 'itemnotes',
2095 type => 'mediumtext',
2105 field => 'itemtype',
2106 type => 'varchar(10)',
2113 field => 'description',
2114 type => 'MEDIUMTEXT',
2136 marc_subfield_structure => [
2138 field => 'defaultvalue',
2145 field => 'authtypecode',
2146 type => 'varchar(20)',
2153 field => 'tagfield',
2154 type => 'varchar(3)',
2161 field => 'tagsubfield',
2162 type => 'varchar(1)',
2169 field => 'authorised_value',
2170 type => 'varchar(20)',
2178 type => 'varchar(1100)',
2186 marc_tag_structure => [
2188 field => 'tagfield',
2189 type => 'varchar(3)',
2196 field => 'liblibrarian',
2197 type => 'varchar(255)',
2205 type => 'varchar(255)',
2212 field => 'authorised_value',
2213 type => 'varchar(10)',
2220 field => 'frameworkcode',
2221 type => 'varchar(4)',
2231 field => 'expirationdate',
2249 field => 'printername',
2250 type => 'varchar(40)',
2257 field => 'printqueue',
2258 type => 'varchar(20)',
2265 field => 'printtype',
2266 type => 'varchar(20)',
2274 reserveconstraints => [
2276 field => 'reservedate',
2287 field => 'waitingdate',
2295 field => 'reservedate',
2303 field => 'constrainttype',
2304 type => 'varchar(1)',
2309 after => 'biblionumber',
2312 field => 'branchcode',
2313 type => 'varchar(10)',
2320 field => 'reservenotes',
2321 type => 'mediumtext',
2329 type => 'varchar(1)',
2339 field => 'planneddate',
2353 after => 'planneddate',
2359 field => 'dateadded',
2360 type => 'timestamp',
2368 type => 'varchar(10)',
2372 field => 'datetime',
2378 field => 'itemtype',
2379 type => 'varchar(10)',
2384 type => 'mediumtext',
2391 field => 'startdate',
2400 type => 'mediumtext',
2407 field => 'monthlength',
2416 subscriptionhistory => [
2418 field => 'histstartdate',
2435 systempreferences => [
2438 type => 'mediumtext',
2453 field => 'explanation',
2472 type => 'mediumtext',
2482 type => 'varchar(30)',
2489 field => 'flagdesc',
2490 type => 'varchar(255)',
2500 type => 'mediumtext',
2511 # { indexname => 'index detail'
2515 { indexname => 'accountoffsets_ibfk_1',
2516 content => 'borrowernumber',
2520 { indexname => 'PRIMARY',
2526 { indexname => 'booksellerid',
2527 content => 'booksellerid',
2531 { indexname => 'basketno',
2532 content => 'basketno',
2535 aqorderbreakdown => [
2536 { indexname => 'ordernumber',
2537 content => 'ordernumber',
2539 { indexname => 'bookfundid',
2540 content => 'bookfundid',
2544 { indexname => 'isbn',
2547 { indexname => 'publishercode',
2548 content => 'publishercode',
2553 indexname => 'borrowernumber',
2554 content => 'borrowernumber',
2561 indexname => 'branchcode',
2562 content => 'branchcode',
2566 branchrelations => [
2568 indexname => 'PRIMARY',
2569 content => 'categorycode',
2573 branchrelations => [
2574 { indexname => 'PRIMARY',
2575 content => 'branchcode,categorycode',
2578 { indexname => 'branchcode',
2579 content => 'branchcode',
2581 { indexname => 'categorycode',
2582 content => 'categorycode',
2586 { indexname => 'PRIMARY',
2587 content => 'currency',
2593 indexname => 'categorycode',
2594 content => 'categorycode',
2599 indexname => 'categorycode',
2600 content => 'categorycode',
2603 indexname => 'itemtype',
2604 content => 'itemtype',
2608 { indexname => 'homebranch',
2609 content => 'homebranch',
2611 { indexname => 'holdingbranch',
2612 content => 'holdingbranch',
2617 indexname => 'itemtype',
2618 content => 'itemtype',
2622 { indexname => 'shelfnumber',
2623 content => 'shelfnumber',
2625 { indexname => 'itemnumber',
2626 content => 'itemnumber',
2630 { indexname => 'PRIMARY',
2637 my %foreign_keys = (
2639 # { key => 'the key in table' (must be indexed)
2640 # foreigntable => 'the foreigntable name', # (the parent)
2641 # foreignkey => 'the foreign key column(s)' # (in the parent)
2642 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2643 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2646 branchrelations => [
2647 { key => 'branchcode',
2648 foreigntable => 'branches',
2649 foreignkey => 'branchcode',
2650 onUpdate => 'CASCADE',
2651 onDelete => 'CASCADE',
2653 { key => 'categorycode',
2654 foreigntable => 'branchcategories',
2655 foreignkey => 'categorycode',
2656 onUpdate => 'CASCADE',
2657 onDelete => 'CASCADE',
2661 { key => 'shelfnumber',
2662 foreigntable => 'bookshelf',
2663 foreignkey => 'shelfnumber',
2664 onUpdate => 'CASCADE',
2665 onDelete => 'CASCADE',
2667 { key => 'itemnumber',
2668 foreigntable => 'items',
2669 foreignkey => 'itemnumber',
2670 onUpdate => 'CASCADE',
2671 onDelete => 'CASCADE',
2674 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
2675 # easily deleted, but branches/itemtype not too easy to empty...
2677 { key => 'biblionumber',
2678 foreigntable => 'biblio',
2679 foreignkey => 'biblionumber',
2680 onUpdate => 'CASCADE',
2681 onDelete => 'CASCADE',
2685 { key => 'biblioitemnumber',
2686 foreigntable => 'biblioitems',
2687 foreignkey => 'biblioitemnumber',
2688 onUpdate => 'CASCADE',
2689 onDelete => 'CASCADE',
2691 { key => 'homebranch',
2692 foreigntable => 'branches',
2693 foreignkey => 'branchcode',
2694 onUpdate => 'CASCADE',
2695 onDelete => 'RESTRICT',
2697 { key => 'holdingbranch',
2698 foreigntable => 'branches',
2699 foreignkey => 'branchcode',
2700 onUpdate => 'CASCADE',
2701 onDelete => 'RESTRICT',
2705 { key => 'booksellerid',
2706 foreigntable => 'aqbooksellers',
2708 onUpdate => 'CASCADE',
2709 onDelete => 'RESTRICT',
2713 { key => 'basketno',
2714 foreigntable => 'aqbasket',
2715 foreignkey => 'basketno',
2716 onUpdate => 'CASCADE',
2717 onDelete => 'CASCADE',
2719 { key => 'biblionumber',
2720 foreigntable => 'biblio',
2721 foreignkey => 'biblionumber',
2722 onUpdate => 'SET NULL',
2723 onDelete => 'SET NULL',
2727 { key => 'listprice',
2728 foreigntable => 'currency',
2729 foreignkey => 'currency',
2730 onUpdate => 'CASCADE',
2731 onDelete => 'CASCADE',
2733 { key => 'invoiceprice',
2734 foreigntable => 'currency',
2735 foreignkey => 'currency',
2736 onUpdate => 'CASCADE',
2737 onDelete => 'CASCADE',
2740 aqorderbreakdown => [
2741 { key => 'ordernumber',
2742 foreigntable => 'aqorders',
2743 foreignkey => 'ordernumber',
2744 onUpdate => 'CASCADE',
2745 onDelete => 'CASCADE',
2747 { key => 'bookfundid',
2748 foreigntable => 'aqbookfund',
2749 foreignkey => 'bookfundid',
2750 onUpdate => 'CASCADE',
2751 onDelete => 'CASCADE',
2754 branchtransfers => [
2755 { key => 'frombranch',
2756 foreigntable => 'branches',
2757 foreignkey => 'branchcode',
2758 onUpdate => 'CASCADE',
2759 onDelete => 'CASCADE',
2761 { key => 'tobranch',
2762 foreigntable => 'branches',
2763 foreignkey => 'branchcode',
2764 onUpdate => 'CASCADE',
2765 onDelete => 'CASCADE',
2767 { key => 'itemnumber',
2768 foreigntable => 'items',
2769 foreignkey => 'itemnumber',
2770 onUpdate => 'CASCADE',
2771 onDelete => 'CASCADE',
2774 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
2776 { key => 'borrowernumber',
2777 foreigntable => 'borrowers',
2778 foreignkey => 'borrowernumber',
2779 onUpdate => 'SET NULL',
2780 onDelete => 'SET NULL',
2782 { key => 'itemnumber',
2783 foreigntable => 'items',
2784 foreignkey => 'itemnumber',
2785 onUpdate => 'SET NULL',
2786 onDelete => 'SET NULL',
2790 { key => 'borrowernumber',
2791 foreigntable => 'borrowers',
2792 foreignkey => 'borrowernumber',
2793 onUpdate => 'CASCADE',
2794 onDelete => 'CASCADE',
2796 { key => 'biblionumber',
2797 foreigntable => 'biblio',
2798 foreignkey => 'biblionumber',
2799 onUpdate => 'CASCADE',
2800 onDelete => 'CASCADE',
2802 { key => 'itemnumber',
2803 foreigntable => 'items',
2804 foreignkey => 'itemnumber',
2805 onUpdate => 'CASCADE',
2806 onDelete => 'CASCADE',
2808 { key => 'branchcode',
2809 foreigntable => 'branches',
2810 foreignkey => 'branchcode',
2811 onUpdate => 'CASCADE',
2812 onDelete => 'CASCADE',
2815 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
2816 # but prevent deleting a branch as soon as it has 1 borrower !
2817 { key => 'categorycode',
2818 foreigntable => 'categories',
2819 foreignkey => 'categorycode',
2820 onUpdate => 'RESTRICT',
2821 onDelete => 'RESTRICT',
2823 { key => 'branchcode',
2824 foreigntable => 'branches',
2825 foreignkey => 'branchcode',
2826 onUpdate => 'RESTRICT',
2827 onDelete => 'RESTRICT',
2831 { key => 'borrowernumber',
2832 foreigntable => 'borrowers',
2833 foreignkey => 'borrowernumber',
2834 onUpdate => 'CASCADE',
2835 onDelete => 'CASCADE',
2837 { key => 'itemnumber',
2838 foreigntable => 'items',
2839 foreignkey => 'itemnumber',
2840 onUpdate => 'SET NULL',
2841 onDelete => 'SET NULL',
2845 { key => 'borrowernumber',
2846 foreigntable => 'borrowers',
2847 foreignkey => 'borrowernumber',
2848 onUpdate => 'CASCADE',
2849 onDelete => 'CASCADE',
2852 auth_tag_structure => [
2853 { key => 'authtypecode',
2854 foreigntable => 'auth_types',
2855 foreignkey => 'authtypecode',
2856 onUpdate => 'CASCADE',
2857 onDelete => 'CASCADE',
2860 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
2865 my %column_change = (
2869 from => 'emailaddress',
2874 from => 'streetaddress',
2876 after => 'initials',
2879 from => 'faxnumber',
2884 from => 'textmessaging',
2890 to => 'contactnote',
2891 after => 'opacnote',
2894 from => 'physstreet',
2899 from => 'streetcity',
2901 after => 'B_address',
2914 from => 'homezipcode',
2921 after => 'B_zipcode',
2926 after => 'dateenrolled',
2929 from => 'guarantor',
2930 to => 'guarantorid',
2931 after => 'contactname',
2934 from => 'altrelationship',
2935 to => 'relationship',
2936 after => 'borrowernotes',
2940 deletedborrowers => [
2942 from => 'emailaddress',
2947 from => 'streetaddress',
2949 after => 'initials',
2952 from => 'faxnumber',
2957 from => 'textmessaging',
2963 to => 'contactnote',
2964 after => 'opacnote',
2967 from => 'physstreet',
2972 from => 'streetcity',
2974 after => 'B_address',
2987 from => 'homezipcode',
2994 after => 'B_zipcode',
2999 after => 'dateenrolled',
3002 from => 'guarantor',
3003 to => 'guarantorid',
3004 after => 'contactname',
3007 from => 'altrelationship',
3008 to => 'relationship',
3009 after => 'borrowernotes',
3015 # MOVE all tables TO UTF-8 and innoDB
3016 $sth = $dbh->prepare("show table status");
3018 while ( my $table = $sth->fetchrow_hashref ) {
3019 next if $table->{Name} eq 'marc_word';
3020 next if $table->{Name} eq 'marc_subfield_table';
3021 next if $table->{Name} eq 'auth_word';
3022 next if $table->{Name} eq 'auth_subfield_table';
3023 if ($table->{Engine} ne 'InnoDB') {
3024 print "moving $table->{Name} to InnoDB\n";
3025 $dbh->do("ALTER TABLE $table->{Name} ENGINE = innodb");
3027 unless ($table->{Collation} =~ /^utf8/) {
3028 print "moving $table->{Name} to utf8\n";
3029 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
3030 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
3031 # 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 !
3036 # list of columns that must exist for %column_change to be
3037 # processed without error, but which do not necessarily exist
3038 # in all 2.2 databases
3039 my %required_prereq_fields = (
3040 deletedborrowers => [
3041 [ 'textmessaging', 'mediumtext AFTER faxnumber' ],
3042 [ 'password', 'varchar(30) default NULL' ],
3043 [ 'flags', 'int(11) default NULL' ],
3044 [ 'userid', 'varchar(30) default NULL' ],
3045 [ 'homezipcode', 'varchar(25) default NULL' ],
3046 [ 'zipcode', 'varchar(25) default NULL' ],
3047 [ 'sort1', 'varchar(80) default NULL' ],
3048 [ 'sort2', 'varchar(80) default NULL' ],
3052 foreach $table ( keys %required_prereq_fields ) {
3053 print "Check table $table\n" if $debug and not $silent;
3054 $sth = $dbh->prepare("show columns from $table");
3057 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3059 $types{$column} = $type;
3061 foreach my $entry ( @{ $required_prereq_fields{$table} } ) {
3062 ($column, $type) = @{ $entry };
3063 print " Check column $column [$type]\n" if $debug and not $silent;
3064 if ( !$types{$column} ) {
3066 # column doesn't exist
3067 print "Adding $column field to $table table...\n" unless $silent;
3068 $query = "alter table $table
3069 add column $column " . $type;
3070 print "Execute: $query\n" if $debug;
3071 my $sti = $dbh->prepare($query);
3074 print "**Error : $sti->errstr \n";
3081 foreach my $table (keys %column_change) {
3082 $sth = $dbh->prepare("show columns from $table");
3085 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3087 $types{$column}->{type} ="$type";
3088 $types{$column}->{null} = "$null";
3089 $types{$column}->{key} = "$key";
3090 $types{$column}->{default} = "$default";
3091 $types{$column}->{extra} = "$extra";
3093 my $tablerows = $column_change{$table};
3094 foreach my $row ( @$tablerows ) {
3095 if ($types{$row->{from}}->{type}) {
3096 print "altering $table $row->{from} to $row->{to}\n";
3097 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
3098 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
3100 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
3101 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
3102 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
3103 "$types{$row->{from}}->{extra} after $row->{after} ";
3110 # Enter here the field you want to delete from DB.
3111 # FIXME :: there is a %uselessfield before which seems doing the same things.
3112 my %fieldtodelete = (
3113 # tablename => [fieldname1,fieldname2,...]
3117 print "removing some unused fields...\n";
3118 foreach my $table ( keys %fieldtodelete ) {
3119 foreach my $field ( @{$fieldtodelete{$table}} ){
3120 print "removing ".$field." from ".$table;
3121 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
3124 print "Error : $sth->errstr \n";
3129 # Enter here the line you want to remove from DB.
3130 my %linetodelete = (
3131 # table name => where clause.
3132 userflags => [ "bit = 8" ], # delete the 'reserveforself' flags
3136 #-------------------
3141 # Get version of MySQL database engine.
3142 my $mysqlversion = `mysqld --version`;
3143 $mysqlversion =~ /Ver (\S*) /;
3145 if ( $mysqlversion ge '3.23' ) {
3146 print "Could convert to MyISAM database tables...\n" unless $silent;
3149 #---------------------------------
3152 # Collect all tables into a list
3153 $sth = $dbh->prepare("show tables");
3155 while ( my ($table) = $sth->fetchrow ) {
3156 $existingtables{$table} = 1;
3160 # Now add any missing tables
3161 foreach $table ( keys %requiretables ) {
3162 unless ( $existingtables{$table} ) {
3163 print "Adding $table table...\n" unless $silent;
3164 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
3167 print "Error : $sth->errstr \n";
3173 #---------------------------------
3176 foreach $table ( keys %requirefields ) {
3177 print "Check table $table\n" if $debug and not $silent;
3178 $sth = $dbh->prepare("show columns from $table");
3181 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3183 $types{$column} = $type;
3185 foreach $column ( keys %{ $requirefields{$table} } ) {
3186 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
3187 if ( !$types{$column} ) {
3189 # column doesn't exist
3190 print "Adding $column field to $table table...\n" unless $silent;
3191 $query = "alter table $table
3192 add column $column " . $requirefields{$table}->{$column};
3193 print "Execute: $query\n" if $debug;
3194 my $sti = $dbh->prepare($query);
3197 print "**Error : $sti->errstr \n";
3204 foreach $table ( sort keys %fielddefinitions ) {
3205 print "Check table $table\n" if $debug;
3206 $sth = $dbh->prepare("show columns from $table");
3209 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3211 $definitions->{$column}->{type} = $type;
3212 $definitions->{$column}->{null} = $null;
3213 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
3214 $definitions->{$column}->{key} = $key;
3215 $definitions->{$column}->{default} = $default;
3216 $definitions->{$column}->{extra} = $extra;
3218 my $fieldrow = $fielddefinitions{$table};
3219 foreach my $row (@$fieldrow) {
3220 my $field = $row->{field};
3221 my $type = $row->{type};
3222 my $null = $row->{null};
3223 # $null = 'YES' if $row->{null} eq 'NULL';
3224 my $key = $row->{key};
3225 my $default = $row->{default};
3226 # $default="''" unless $default;
3227 my $extra = $row->{extra};
3228 my $def = $definitions->{$field};
3229 my $after = ($row->{after}?" after ".$row->{after}:"");
3231 unless ( $type eq $def->{type}
3232 && $null eq $def->{null}
3233 && $key eq $def->{key}
3234 && $default eq $def->{default}
3235 && $extra eq $def->{extra} )
3237 if ( $null eq '' ) {
3240 if ( $key eq 'PRI' ) {
3241 $key = 'PRIMARY KEY';
3243 unless ( $extra eq 'auto_increment' ) {
3247 # if it's a new column use "add", if it's an old one, use "change".
3249 if ($definitions->{$field}->{type}) {
3250 $action="change `$field`"
3254 # if it's a primary key, drop the previous pk, before altering the table
3255 print " alter or create $field in $table\n" unless $silent;
3257 if ($key ne 'PRIMARY KEY') {
3258 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
3259 $query = "alter table $table $action `$field` $type $null $key $extra ".
3260 GetDefaultClause($default)." $after";
3262 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
3263 # something strange : for indexes UNIQUE, they are reported as primary key here.
3264 # but if you try to run with drop primary key, it fails.
3265 # thus, we run the query twice, one will fail, one will succeed.
3267 $query="alter table $table drop primary key, $action `$field` $type $null $key $extra ".
3268 GetDefaultClause($default)." $after";
3269 $query="alter table $table $action `$field` $type $null $key $extra ".
3270 GetDefaultClause($default)." $after";
3272 $dbh->do($query) or warn "Error while executing: $query";
3277 print "removing some unused data...\n";
3278 foreach my $table ( keys %linetodelete ) {
3279 foreach my $where ( @{$linetodelete{$table}} ){
3280 print "DELETE FROM ".$table." where ".$where;
3282 my $sth = $dbh->prepare("DELETE FROM $table where $where");
3285 print "Error : $sth->errstr \n";
3290 # Populate tables with required data
3292 # synch table and deletedtable.
3293 foreach my $table (('borrowers','items','biblio','biblioitems')) {
3294 my %deletedborrowers;
3295 print "synch'ing $table and deleted$table\n";
3296 $sth = $dbh->prepare("show columns from deleted$table");
3298 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3299 $deletedborrowers{$column}=1;
3301 $sth = $dbh->prepare("show columns from $table");
3304 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3305 unless ($deletedborrowers{$column}) {
3306 my $newcol="alter table deleted$table add $column $type";
3307 if ($null eq 'YES') {
3308 $newcol .= " NULL ";
3310 $newcol .= " NOT NULL ";
3312 $newcol .= "default ".$dbh->quote($default) if $default;
3313 $newcol .= " after $previous" if $previous;
3315 print "creating column $column\n";
3321 # update publisheddate
3323 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
3325 my ($emptypublished) = $sth->fetchrow;
3326 if ($emptypublished) {
3327 print "Updating publisheddate\n";
3328 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
3330 # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it.
3331 # now, let's get rid of 000-00-00's.
3333 $dbh->do("update serial set publisheddate=NULL where publisheddate = 0");
3334 $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0");
3336 foreach my $table ( keys %tabledata ) {
3337 print "Checking for data required in table $table...\n" unless $silent;
3338 my $tablerows = $tabledata{$table};
3339 foreach my $row (@$tablerows) {
3340 my $uniquefieldrequired = $row->{uniquefieldrequired};
3341 my $uniquevalue = $row->{$uniquefieldrequired};
3342 my $forceupdate = $row->{forceupdate};
3345 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
3347 $sth->execute($uniquevalue);
3349 foreach my $field (keys %$forceupdate) {
3350 if ($forceupdate->{$field}) {
3351 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
3352 $sth->execute($row->{$field}, $uniquevalue);
3356 print "Adding row to $table: " unless $silent;
3360 foreach my $field ( keys %$row ) {
3361 next if $field eq 'uniquefieldrequired';
3362 next if $field eq 'forceupdate';
3363 my $value = $row->{$field};
3364 push @values, $value;
3365 print " $field => $value" unless $silent;
3366 $fieldlist .= "$field,";
3367 $placeholders .= "?,";
3369 print "\n" unless $silent;
3370 $fieldlist =~ s/,$//;
3371 $placeholders =~ s/,$//;
3372 print "insert into $table ($fieldlist) values ($placeholders)";
3375 "insert into $table ($fieldlist) values ($placeholders)");
3376 $sth->execute(@values);
3382 # check indexes and create them when needed
3384 print "Checking for index required...\n" unless $silent;
3385 foreach my $table ( keys %indexes ) {
3387 # read all indexes from $table
3389 $sth = $dbh->prepare("show index from $table");
3391 my %existingindexes;
3392 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
3393 $existingindexes{$key_name} = 1;
3395 # read indexes to check
3396 my $tablerows = $indexes{$table};
3397 foreach my $row (@$tablerows) {
3398 my $key_name=$row->{indexname};
3399 if ($existingindexes{$key_name} eq 1 and not $row->{force}) {
3400 # print "$key_name existing";
3402 print "\tCreating index $key_name in $table\n";
3404 if ($row->{indexname} eq 'PRIMARY' or $row->{type} eq 'PRI') {
3405 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
3407 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
3410 print "Error $sql : $dbh->err \n" if $dbh->err;
3416 # check foreign keys and create them when needed
3418 print "Checking for foreign keys required...\n" unless $silent;
3419 foreach my $table ( sort keys %foreign_keys ) {
3421 # read all indexes from $table
3423 $sth = $dbh->prepare("show table status like '$table'");
3425 my $stat = $sth->fetchrow_hashref;
3426 # read indexes to check
3427 my $tablerows = $foreign_keys{$table};
3428 foreach my $row (@$tablerows) {
3429 my $foreign_table=$row->{foreigntable};
3430 if ($stat->{'Comment'} =~/$foreign_table/) {
3431 # print "$foreign_table existing\n";
3433 print "\tCreating foreign key $foreign_table in $table\n";
3434 # first, drop any orphan value in child table
3435 if ($row->{onDelete} ne "RESTRICT") {
3436 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
3438 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
3440 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
3441 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
3442 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
3445 print "====================
3446 An error occured during :
3448 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).
3449 You can find those values with select
3450 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
3451 ====================\n
3457 # now drop useless tables
3458 foreach $table ( @TableToDelete ) {
3459 if ( $existingtables{$table} ) {
3460 print "Dropping unused table $table\n" if $debug and not $silent;
3461 $dbh->do("drop table $table");
3463 print "Error : $dbh->errstr \n";
3472 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
3475 # 1st, get how many biblio we will have to do...
3476 $sth = $dbh->prepare('select count(*) from marc_biblio');
3478 my ($totaltodo) = $sth->fetchrow;
3480 $sth = $dbh->prepare("show columns from biblio");
3483 my $bibliofwexist=0;
3484 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
3485 $bibliofwexist=1 if $column eq 'frameworkcode';
3487 unless ($bibliofwexist) {
3488 print "moving biblioframework to biblio table\n";
3489 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
3490 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
3492 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
3494 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
3495 $sth_update->execute($frameworkcode,$biblionumber);
3497 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
3502 # at last, remove useless fields
3503 foreach $table ( keys %uselessfields ) {
3504 my @fields = split (/,/,$uselessfields{$table});
3507 foreach my $fieldtodrop (@fields) {
3508 $fieldtodrop =~ s/\t//g;
3509 $fieldtodrop =~ s/\n//g;
3511 $sth = $dbh->prepare("show columns from $table");
3513 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3515 $exists =1 if ($column eq $fieldtodrop);
3518 print "deleting $fieldtodrop field in $table...\n" unless $silent;
3519 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
3526 # Changing aqbookfund's primary key
3528 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
3532 # drop extra key on borrowers.borrowernumber
3533 $dbh->do("ALTER TABLE borrowers DROP KEY borrowernumber");
3535 # update enrolmentperiod
3536 $dbh->do("UPDATE categories SET enrolmentperiod = enrolmentperiod * 12");
3538 print "upgrade to Koha 3.0 done\n";
3539 SetVersion ($DBversion);
3541 =head1 GetDefaultClause
3543 Generate a default clause (for an ALTER TABLE command)
3547 sub GetDefaultClause {
3548 my $default = shift;
3550 return "" unless defined $default;
3551 return "" if $default eq '';
3552 return "default ''" if $default eq "''";
3553 return "default NULL" if $default eq "NULL";
3554 return "default " . $dbh->quote($default);
3557 =head1 TransformToNum
3559 Transform the Koha version from a 4 parts string
3560 to a number, with just 1.
3564 sub TransformToNum {
3565 my $version = shift;
3566 # remove the 3 last . to have a Perl number
3567 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3573 set the DBversion in the systempreferences
3578 my $kohaversion = TransformToNum(shift);
3579 if (C4::Context->preference('Version')) {
3580 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3581 $finish->execute($kohaversion);
3583 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')");
3584 $finish->execute($kohaversion);
3589 # Revision 1.172 2007/07/19 10:21:22 hdl