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.
17 use Getopt::Long qw( GetOptions );
21 use MARC::File::XML ( BinaryEncoding => 'utf8' );
23 # FIXME - The user might be installing a new database, so can't rely
24 # on /etc/koha.conf anyway.
31 %existingtables, # tables already in database
35 $type, $null, $key, $default, $extra,
42 my $dbh = C4::Context->dbh;
43 $|=1; # flushes output
45 my $DBversion = "3.00.00.000";
46 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
47 # Tables to add if they don't exist
50 `timestamp` TIMESTAMP NOT NULL ,
51 `user` INT( 11 ) NOT NULL default '0' ,
52 `module` TEXT default '',
53 `action` TEXT default '' ,
54 `object` INT(11) NULL ,
55 `info` TEXT default '' ,
56 PRIMARY KEY ( `timestamp` , `user` )
59 module varchar(20) NOT NULL default '',
60 code varchar(20) NOT NULL default '',
61 name varchar(100) NOT NULL default '',
62 title varchar(200) NOT NULL default '',
64 PRIMARY KEY (module,code)
67 alertid int(11) NOT NULL auto_increment,
68 borrowernumber int(11) NOT NULL default '0',
69 type varchar(10) NOT NULL default '',
70 externalid varchar(20) NOT NULL default '',
71 PRIMARY KEY (alertid),
72 KEY borrowernumber (borrowernumber),
73 KEY type (type,externalid)
76 `idnew` int(10) unsigned NOT NULL auto_increment,
77 `title` varchar(250) NOT NULL default '',
79 `lang` varchar(4) NOT NULL default '',
80 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
83 repeatable_holidays => "(
84 `id` int(11) NOT NULL auto_increment,
85 `branchcode` varchar(10) NOT NULL default '',
86 `weekday` smallint(6) default NULL,
87 `day` smallint(6) default NULL,
88 `month` smallint(6) default NULL,
89 `title` varchar(50) NOT NULL default '',
90 `description` text NOT NULL,
93 special_holidays => "(
94 `id` int(11) NOT NULL auto_increment,
95 `branchcode` varchar(10) NOT NULL default '',
96 `day` smallint(6) NOT NULL default '0',
97 `month` smallint(6) NOT NULL default '0',
98 `year` smallint(6) NOT NULL default '0',
99 `isexception` smallint(1) NOT NULL default '1',
100 `title` varchar(50) NOT NULL default '',
101 `description` text NOT NULL,
104 overduerules =>"(`branchcode` varchar(10) NOT NULL default '',
105 `categorycode` varchar(2) NOT NULL default '',
106 `delay1` int(4) default '0',
107 `letter1` varchar(20) default NULL,
108 `debarred1` varchar(1) default '0',
109 `delay2` int(4) default '0',
110 `debarred2` varchar(1) default '0',
111 `letter2` varchar(20) default NULL,
112 `delay3` int(4) default '0',
113 `letter3` varchar(20) default NULL,
114 `debarred3` int(1) default '0',
115 PRIMARY KEY (`branchcode`,`categorycode`)
117 cities => "(`cityid` int auto_increment,
118 `city_name` varchar(100) NOT NULL default '',
119 `city_zipcode` varchar(20),
120 PRIMARY KEY (`cityid`)
122 roadtype => "(`roadtypeid` int auto_increment,
123 `road_type` varchar(100) NOT NULL default '',
124 PRIMARY KEY (`roadtypeid`)
128 labelid int(11) NOT NULL auto_increment,
129 batch_id varchar(10) NOT NULL default '1',
130 itemnumber varchar(100) NOT NULL default '',
131 timestamp timestamp(14) NOT NULL,
132 PRIMARY KEY (labelid)
136 id int(4) NOT NULL auto_increment,
137 barcodetype char(100) default '',
138 title int(1) default '0',
139 subtitle int(1) default '0',
140 itemtype int(1) default '0',
141 barcode int(1) default '0',
142 dewey int(1) default '0',
143 class int(1) default '0',
144 subclass int(1) default '0',
145 itemcallnumber int(1) default '0',
146 author int(1) default '0',
147 issn int(1) default '0',
148 isbn int(1) default '0',
149 startlabel int(2) NOT NULL default '1',
150 printingtype char(32) default 'BAR',
151 layoutname char(20) NOT NULL default 'TEST',
152 guidebox int(1) default '0',
153 active tinyint(1) default '1',
154 fonttype char(10) collate utf8_unicode_ci default NULL,
155 ccode char(4) collate utf8_unicode_ci default NULL,
156 callnum_split int(1) default NULL,
157 text_justify char(1) collate utf8_unicode_ci default NULL,
161 reviewid integer NOT NULL auto_increment,
162 borrowernumber integer,
163 biblionumber integer,
166 datereviewed datetime,
167 PRIMARY KEY (reviewid)
169 subscriptionroutinglist=>"(
170 routingid integer NOT NULL auto_increment,
171 borrowernumber integer,
173 subscriptionid integer,
174 PRIMARY KEY (routingid)
178 notify_id int(11) NOT NULL default '0',
179 `borrowernumber` int(11) NOT NULL default '0',
180 `itemnumber` int(11) NOT NULL default '0',
181 `notify_date` date default NULL,
182 `notify_send_date` date default NULL,
183 `notify_level` int(1) NOT NULL default '0',
184 `method` varchar(20) NOT NULL default ''
188 `charge_id` varchar(5) NOT NULL default '',
189 `description` text NOT NULL,
190 `amount` decimal(28,6) NOT NULL default '0.000000',
191 `min` int(4) NOT NULL default '0',
192 `max` int(4) NOT NULL default '0',
193 `level` int(1) NOT NULL default '0',
194 PRIMARY KEY (`charge_id`)
197 `entry` varchar(255) NOT NULL default '',
198 `weight` bigint(20) NOT NULL default '0',
199 PRIMARY KEY (`entry`)
203 `id` int NOT NULL auto_increment,
204 `biblio_auth_number` int(11) NOT NULL default '0',
205 `operation` char(20) NOT NULL default '',
206 `server` char(20) NOT NULL default '',
208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
212 my %requirefields = (
213 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 \'\''},
214 itemtypes => { 'imageurl' => 'varchar(200) NULL'},
215 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
216 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
217 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
218 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'},
219 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
220 serial =>{ 'publisheddate' => 'date AFTER planneddate', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
221 statistics => { 'associatedborrower' => 'integer'},
222 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
223 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
224 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
226 issues =>{ 'issuedate'=>"date NULL default NULL", },
228 # tablename => { 'field' => 'fieldtype' },
231 # Enter here the table to delete.
232 my @TableToDelete = qw(
239 my %uselessfields = (
240 # tablename => "field1,field2",
241 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
242 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
243 items => "multivolumepart,multivolume,binding",
244 deleteditems => "multivolumepart,multivolume,binding",
246 # the other hash contains other actions that can't be done elsewhere. they are done
247 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
249 # The tabledata hash contains data that should be in the tables.
250 # The uniquefieldrequired hash entry is used to determine which (if any) fields
251 # must not exist in the table for this row to be inserted. If the
252 # uniquefieldrequired entry is already in the table, the existing data is not
253 # modified, unless the forceupdate hash entry is also set. Fields in the
254 # anonymous "forceupdate" hash will be forced to be updated to the default
255 # values given in the %tabledata hash.
259 # { uniquefielrequired => 'fieldname', # the primary key in the table
260 # fieldname => fieldvalue,
261 # fieldname2 => fieldvalue2,
264 systempreferences => [
266 uniquefieldrequired => 'variable',
267 variable => 'useDaysMode',
269 forceupdate => { 'explanation' => 1,
271 explanation => 'Choose the method for calculating due date: select Calendar to use the holidays module, and Days to ignore the holidays module',
273 options => 'Calendar|Days|Datedue'
276 uniquefieldrequired => 'variable',
277 variable => 'DebugLevel',
279 forceupdate => { 'explanation' => 1,
281 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
286 uniquefieldrequired => 'variable',
287 variable => 'BorrowersTitles',
288 value => 'Mr|Mrs|Miss|Ms',
289 forceupdate => { 'explanation' => 1,
291 explanation => 'List all Titles for borrowers',
295 uniquefieldrequired => 'variable',
296 variable => 'BorrowerMandatoryField',
297 value => 'cardnumber|surname|address',
298 forceupdate => { 'explanation' => 1,
300 explanation => 'List all mandatory fields for borrowers',
304 uniquefieldrequired => 'variable',
305 variable => 'borrowerRelationship',
306 value => 'father|mother,grand-mother',
307 forceupdate => { 'explanation' => 1,
309 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
313 uniquefieldrequired => 'variable',
314 variable => 'ReservesMaxPickUpDelay',
316 forceupdate => { 'explanation' => 1,
318 explanation => 'Maximum delay to pick up a reserved document',
322 uniquefieldrequired => 'variable',
323 variable => 'TransfersMaxDaysWarning',
325 forceupdate => { 'explanation' => 1,
327 explanation => 'Max delay before considering the transfer has potentialy a problem',
331 uniquefieldrequired => 'variable',
332 variable => 'memberofinstitution',
334 forceupdate => { 'explanation' => 1,
336 explanation => 'Are your patrons members of institutions',
340 uniquefieldrequired => 'variable',
341 variable => 'ReadingHistory',
343 forceupdate => { 'explanation' => 1,
345 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
349 uniquefieldrequired => 'variable',
350 variable => 'IssuingInProcess',
352 forceupdate => { 'explanation' => 1,
354 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
358 uniquefieldrequired => 'variable',
359 variable => 'AutomaticItemReturn',
361 forceupdate => { 'explanation' => 1,
363 explanation => 'This Variable allow or not to return automaticly to his homebranch',
367 uniquefieldrequired => 'variable',
368 variable => 'reviewson',
370 forceupdate => { 'explanation' => 1,
372 explanation => 'Allows patrons to submit reviews from the opac',
376 uniquefieldrequired => 'variable',
377 variable => 'intranet_includes',
379 forceupdate => { 'explanation' => 1,
381 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
385 uniquefieldrequired => 'variable',
386 variable => 'AutoLocation',
388 forceupdate => { 'explanation' => 1,
390 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
394 uniquefieldrequired => 'variable',
395 variable => 'serialsadditems',
401 explanation => 'If set, a new item will be automatically added when receiving an issue',
405 uniquefieldrequired => 'variable',
406 variable => 'expandedSearchOption',
412 explanation => 'search among marc field',
416 uniquefieldrequired => 'variable',
417 variable => 'OPACHoldRequests',
419 forceupdate => { 'explanation' => 1,
421 explanation => 'option to allow reserves on opac',
425 uniquefieldrequired => 'variable',
426 variable => 'OpacCloud',
428 forceupdate => { 'explanation' => 1,
430 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
434 uniquefieldrequired => 'variable',
435 variable => 'OpacBrowser',
437 forceupdate => { 'explanation' => 1,
439 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
443 uniquefieldrequired => 'variable',
444 variable => 'OpacTopissue',
446 forceupdate => { 'explanation' => 1,
448 explanation => 'If ON, enables the \'most popular items\' link on OPAC. Warning, this is an EXPERIMENTAL feature, turning ON may overload your server',
452 uniquefieldrequired => 'variable',
453 variable => 'OpacAuthorities',
455 forceupdate => { 'explanation' => 1,
457 explanation => 'Enable / Disable the search authority link on OPAC',
461 uniquefieldrequired => 'variable',
462 variable => 'CataloguingLog',
464 forceupdate => {'explanation' => 1, 'type' => 1},
465 explanation => 'Active this if you want to log cataloguing action.',
469 uniquefieldrequired => 'variable',
470 variable => 'BorrowersLog',
472 forceupdate => {'explanation' => 1, 'type' => 1},
473 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
477 uniquefieldrequired => 'variable',
478 variable => 'SubscriptionLog',
480 forceupdate => {'explanation' => 1, 'type' => 1},
481 explanation => 'Active this if you want to log Subscription action',
485 uniquefieldrequired => 'variable',
486 variable => 'IssueLog',
488 forceupdate => {'explanation' => 1, 'type' => 1},
489 explanation => 'Active this if you want to log issue.',
493 uniquefieldrequired => 'variable',
494 variable => 'ReturnLog',
496 forceupdate => {'explanation' => 1, 'type' => 1},
497 explanation => 'Active this if you want to log the circulation return',
501 uniquefieldrequired => 'variable',
502 variable => 'Version',
504 forceupdate => {'explanation' => 1, 'type' => 1},
505 explanation => 'Koha Version',
509 uniquefieldrequired => 'variable',
510 variable => 'LetterLog',
512 forceupdate => {'explanation' => 1, 'type' => 1},
513 explanation => 'Active this if you want to log all the letter sent',
517 uniquefieldrequired => 'variable',
518 variable => 'FinesLog',
520 forceupdate => {'explanation' => 1, 'type' => 1},
521 explanation => 'Active this if you want to log fines',
525 uniquefieldrequired => 'variable',
526 variable => 'NoZebra',
528 forceupdate => {'explanation' => 1, 'type' => 1},
529 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
533 uniquefieldrequired => 'variable',
534 variable => 'NoZebraIndexes',
536 forceupdate => {'explanation' => 1, 'type' => 1},
537 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
541 uniquefieldrequired => 'variable',
542 variable => 'uppercasesurnames',
544 forceupdate => {'explanation' => 1, 'type' => 1},
545 explanation => "Force Surnames to be uppercase",
551 uniquefieldrequired => 'bit',
553 flag => 'editauthorities',
554 flagdesc => 'allow to edit authorities',
558 uniquefieldrequired => 'bit',
561 flagdesc => 'allow to manage serials subscriptions',
565 uniquefieldrequired => 'bit',
568 flagdesc => 'allow to access to the reports module',
572 authorised_values => [
574 uniquefieldrequired => 'id',
575 category => 'SUGGEST',
576 authorised_value => 'Not enough budget',
577 lib => 'This book it too much expensive',
582 my %fielddefinitions = (
584 # { field => 'fieldname',
585 # type => 'fieldtype',
593 field => 'booksellerid',
601 field => 'booksellerinvoicenumber',
602 type => 'mediumtext',
611 field => 'bookfundid',
612 type => 'varchar(10)',
619 field => 'branchcode',
620 type => 'varchar(10)',
627 field => 'bookfundname',
628 type => 'mediumtext',
633 after => 'bookfundid',
644 extra => 'auto_increment',
648 type => 'varchar(3)',
655 field => 'listprice',
656 type => 'varchar(10)',
663 field => 'invoiceprice',
664 type => 'varchar(10)',
671 field => 'invoicedisc',
672 type => 'float(6,4)',
680 type => 'mediumtext',
688 type => 'mediumtext',
696 type => 'mediumtext',
704 type => 'mediumtext',
711 field => 'accountnumber',
712 type => 'mediumtext',
719 field => 'othersupplier',
720 type => 'mediumtext',
727 field => 'specialty',
728 type => 'mediumtext',
735 field => 'booksellerfax',
736 type => 'mediumtext',
744 type => 'mediumtext',
751 field => 'bookselleremail',
752 type => 'mediumtext',
759 field => 'booksellerurl',
760 type => 'mediumtext',
767 field => 'contnotes',
768 type => 'mediumtext',
776 type => 'mediumtext',
786 field => 'bookfundid',
787 type => 'varchar(10)',
794 field => 'branchcode',
795 type => 'varchar(10)',
803 aqorderbreakdown => [
805 field => 'bookfundid',
806 type => 'varchar(10)',
813 field => 'branchcode',
814 type => 'varchar(10)',
824 field => 'ordernumber',
832 field => 'deliverycomments',
833 type => 'mediumtext',
844 type => 'mediumtext',
852 type => 'varchar(3)',
859 field => 'booksellerinvoicenumber',
860 type => 'mediumtext',
868 type => 'mediumtext',
875 field => 'supplierreference',
876 type => 'mediumtext',
883 field => 'purchaseordernumber',
884 type => 'mediumtext',
894 field => 'notify_id',
902 field => 'notify_level',
910 field => 'accountno',
911 type => 'smallint(6)',
918 field => 'description',
919 type => 'mediumtext',
924 type => 'mediumtext',
932 field => 'authtypecode',
933 type => 'varchar(10)',
940 field => 'datecreated',
948 field => 'origincode',
949 type => 'varchar(20)',
956 field => 'authtrees',
957 type => 'mediumtext',
962 after => 'origincode',
966 auth_subfield_structure => [
968 field => 'authtypecode',
969 type => 'varchar(10)',
977 type => 'varchar(3)',
984 field => 'tagsubfield',
985 type => 'varchar(1)',
992 field => 'liblibrarian',
993 type => 'varchar(255)',
1001 type => 'varchar(255)',
1008 field => 'authorised_value',
1009 type => 'varchar(10)',
1016 field => 'value_builder',
1017 type => 'varchar(80)',
1025 type => 'varchar(255)',
1032 field => 'kohafield',
1033 type => 'varchar(45)',
1040 field => 'frameworkcode',
1041 type => 'varchar(8)',
1049 auth_tag_structure => [
1051 field => 'authtypecode',
1052 type => 'varchar(10)',
1059 field => 'tagfield',
1060 type => 'varchar(3)',
1067 field => 'liblibrarian',
1068 type => 'varchar(255)',
1076 type => 'varchar(255)',
1083 field => 'authorised_value',
1084 type => 'varchar(10)',
1094 field => 'auth_tag_to_report',
1095 type => 'varchar(3)',
1103 type => 'mediumtext',
1111 authorised_values => [
1113 field => 'category',
1114 type => 'varchar(10)',
1121 field => 'authorised_value',
1122 type => 'varchar(80)',
1130 type => 'varchar(80)',
1138 biblio_framework => [
1140 field => 'frameworkcode',
1141 type => 'varchar(4)',
1148 field => 'frameworktext',
1149 type => 'varchar(255)',
1159 field => 'cardnumber',
1160 type => 'varchar(16)',
1166 { field => 'surname',
1167 type => 'mediumtext',
1170 { field => 'firstname',
1175 type => 'mediumtext',
1178 { field => 'othernames',
1179 type => 'mediumtext',
1182 { field => 'initials',
1186 { field => 'B_email',
1189 after => 'B_zipcode',
1192 field => 'streetnumber', # street number (hidden if streettable table is empty)
1193 type => 'varchar(10)',
1195 after => 'initials',
1198 field => 'streettype', # street table, list builded from a system table
1199 type => 'varchar(50)',
1201 after => 'streetnumber',
1208 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1209 type => 'varchar(10)',
1214 field => 'B_streettype', # street table, list builded from a system table
1215 type => 'varchar(50)',
1217 after => 'B_streetnumber',
1220 field => 'phonepro',
1226 field => 'address2', # complement address
1232 field => 'emailpro',
1238 field => 'contactfirstname', # contact's firstname
1241 after => 'contactname',
1244 field => 'contacttitle', # contact's title
1247 after => 'contactfirstname',
1250 field => 'branchcode',
1251 type => 'varchar(10)',
1257 field => 'categorycode',
1258 type => 'varchar(10)',
1265 type => 'mediumtext',
1272 type => 'mediumtext',
1279 type => 'mediumtext',
1286 type => 'mediumtext',
1293 type => 'mediumtext',
1300 type => 'mediumtext',
1306 field => 'contactname',
1307 type => 'mediumtext',
1313 field => 'opacnote',
1314 type => 'mediumtext',
1320 field => 'borrowernotes',
1321 type => 'mediumtext',
1328 type => 'varchar(1)',
1337 field => 'itemtype',
1338 type => 'varchar(10)',
1346 type => 'varchar(25)',
1354 type => 'varchar(4)',
1362 type => 'varchar(30)',
1368 field => 'publicationyear',
1375 field => 'collectiontitle',
1376 type => 'mediumtext',
1380 after => 'volumeddesc',
1383 field => 'collectionissn',
1388 after => 'collectiontitle',
1391 field => 'collectionvolume',
1392 type => 'mediumtext',
1396 after => 'collectionissn',
1399 field => 'editionstatement',
1404 after => 'collectionvolume',
1407 field => 'editionresponsibility',
1412 after => 'editionstatement',
1416 type => 'mediumtext',
1423 type => 'mediumtext',
1430 type => 'mediumtext',
1440 type => 'mediumtext',
1447 type => 'mediumtext',
1453 field => 'unititle',
1454 type => 'mediumtext',
1460 field => 'seriestitle',
1461 type => 'mediumtext',
1467 field => 'abstract',
1468 type => 'mediumtext',
1475 type => 'mediumtext',
1481 field => 'frameworkcode',
1482 type => 'varchar(4)',
1486 after => 'biblionumber',
1493 type => 'mediumtext',
1500 type => 'mediumtext',
1506 field => 'unititle',
1507 type => 'mediumtext',
1513 field => 'seriestitle',
1514 type => 'mediumtext',
1520 field => 'abstract',
1521 type => 'mediumtext',
1528 type => 'mediumtext',
1534 field => 'frameworkcode',
1535 type => 'varchar(4)',
1539 after => 'biblionumber',
1542 deletedbiblioitems => [
1544 field => 'itemtype',
1545 type => 'varchar(10)',
1552 type => 'varchar(30)',
1558 field => 'itemtype',
1559 type => 'varchar(10)',
1566 type => 'mediumtext',
1573 type => 'mediumtext',
1580 type => 'mediumtext',
1589 field => 'shelfname',
1590 type => 'varchar(255)',
1597 type => 'varchar(80)',
1603 field => 'category',
1604 type => 'varchar(1)',
1611 branchcategories => [
1613 field => 'codedescription',
1614 type => 'mediumtext',
1623 field => 'branchip',
1624 type => 'varchar(15)',
1631 field => 'branchprinter',
1632 type => 'varchar(100)',
1639 field => 'branchcode',
1640 type => 'varchar(10)',
1646 field => 'branchname',
1647 type => 'mediumtext',
1653 field => 'branchaddress1',
1654 type => 'mediumtext',
1660 field => 'branchaddress2',
1661 type => 'mediumtext',
1667 field => 'branchaddress3',
1668 type => 'mediumtext',
1674 field => 'branchphone',
1675 type => 'mediumtext',
1681 field => 'branchfax',
1682 type => 'mediumtext',
1688 field => 'branchemail',
1689 type => 'mediumtext',
1696 branchrelations => [
1698 field => 'branchcode',
1699 type => 'VARCHAR(10)',
1706 field => 'categorycode',
1707 type => 'VARCHAR(10)',
1717 field => 'frombranch',
1718 type => 'VARCHAR(10)',
1725 field => 'tobranch',
1726 type => 'VARCHAR(10)',
1732 field => 'comments',
1733 type => 'mediumtext',
1742 field => 'category_type',
1743 type => 'varchar(1)',
1750 field => 'categorycode',
1751 type => 'varchar(10)',
1758 field => 'description',
1759 type => 'mediumtext',
1767 deletedborrowers => [
1769 field => 'branchcode',
1770 type => 'varchar(10)',
1776 field => 'categorycode',
1777 type => 'varchar(2)',
1784 type => 'mediumtext',
1790 field => 'borrowernotes',
1791 type => 'mediumtext',
1797 field => 'contactname',
1798 type => 'mediumtext',
1805 type => 'mediumtext',
1811 field => 'B_zipcode',
1812 type => 'varchar(25)',
1819 type => 'varchar(25)',
1827 type => 'mediumtext',
1834 type => 'mediumtext',
1841 type => 'mediumtext',
1848 type => 'mediumtext',
1853 { field => 'surname',
1854 type => 'mediumtext',
1857 { field => 'firstname',
1861 { field => 'initials',
1866 type => 'mediumtext',
1869 { field => 'othernames',
1870 type => 'mediumtext',
1873 { field => 'B_email',
1876 after => 'B_zipcode',
1879 field => 'streetnumber', # street number (hidden if streettable table is empty)
1880 type => 'varchar(10)',
1883 after => 'initials',
1886 field => 'streettype', # street table, list builded from a system table
1887 type => 'varchar(50)',
1890 after => 'streetnumber',
1897 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1898 type => 'varchar(10)',
1903 field => 'B_streettype', # street table, list builded from a system table
1904 type => 'varchar(50)',
1906 after => 'B_streetnumber',
1909 field => 'phonepro',
1915 field => 'address2', # complement address
1921 field => 'emailpro',
1927 field => 'contactfirstname', # contact's firstname
1930 after => 'contactname',
1933 field => 'contacttitle', # contact's title
1936 after => 'contactfirstname',
1940 type => 'varchar(1)',
1949 field => 'borrowernumber',
1951 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1957 field => 'itemnumber',
1959 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1965 field => 'branchcode',
1966 type => 'varchar(10)',
1973 field => 'issuedate',
1982 type => 'varchar(4)',
1989 field => 'issuingbranch',
1990 type => 'varchar(18)',
1999 field => 'categorycode',
2000 type => 'varchar(10)',
2006 field => 'branchcode',
2007 type => 'varchar(10)',
2013 field => 'itemtype',
2014 type => 'varchar(10)',
2031 field => 'cutterextra',
2032 type => 'varchar(45)',
2039 field => 'homebranch',
2040 type => 'varchar(10)',
2047 field => 'holdingbranch',
2048 type => 'varchar(10)',
2056 type => 'varchar(10)',
2064 type => 'mediumtext',
2071 field => 'itemnotes',
2072 type => 'mediumtext',
2083 type => 'mediumtext',
2090 field => 'itemnotes',
2091 type => 'mediumtext',
2101 field => 'itemtype',
2102 type => 'varchar(10)',
2109 field => 'description',
2110 type => 'MEDIUMTEXT',
2132 marc_subfield_structure => [
2134 field => 'defaultvalue',
2141 field => 'authtypecode',
2142 type => 'varchar(20)',
2149 field => 'tagfield',
2150 type => 'varchar(3)',
2157 field => 'tagsubfield',
2158 type => 'varchar(1)',
2165 field => 'authorised_value',
2166 type => 'varchar(20)',
2174 type => 'varchar(1100)',
2182 marc_tag_structure => [
2184 field => 'tagfield',
2185 type => 'varchar(3)',
2192 field => 'liblibrarian',
2193 type => 'varchar(255)',
2201 type => 'varchar(255)',
2208 field => 'authorised_value',
2209 type => 'varchar(10)',
2216 field => 'frameworkcode',
2217 type => 'varchar(4)',
2227 field => 'expirationdate',
2245 field => 'printername',
2246 type => 'varchar(40)',
2253 field => 'printqueue',
2254 type => 'varchar(20)',
2261 field => 'printtype',
2262 type => 'varchar(20)',
2270 reserveconstraints => [
2272 field => 'reservedate',
2283 field => 'waitingdate',
2291 field => 'reservedate',
2299 field => 'constrainttype',
2300 type => 'varchar(1)',
2305 after => 'biblionumber',
2308 field => 'branchcode',
2309 type => 'varchar(10)',
2316 field => 'reservenotes',
2317 type => 'mediumtext',
2325 type => 'varchar(1)',
2335 field => 'planneddate',
2349 after => 'planneddate',
2355 field => 'dateadded',
2356 type => 'timestamp',
2364 type => 'varchar(10)',
2368 field => 'datetime',
2374 field => 'itemtype',
2375 type => 'varchar(10)',
2380 type => 'mediumtext',
2387 field => 'startdate',
2396 type => 'mediumtext',
2403 field => 'monthlength',
2412 subscriptionhistory => [
2414 field => 'histstartdate',
2431 systempreferences => [
2434 type => 'mediumtext',
2449 field => 'explanation',
2468 type => 'mediumtext',
2478 type => 'varchar(30)',
2485 field => 'flagdesc',
2486 type => 'varchar(255)',
2496 type => 'mediumtext',
2507 # { indexname => 'index detail'
2511 { indexname => 'accountoffsets_ibfk_1',
2512 content => 'borrowernumber',
2516 { indexname => 'PRIMARY',
2522 { indexname => 'booksellerid',
2523 content => 'booksellerid',
2527 { indexname => 'basketno',
2528 content => 'basketno',
2531 aqorderbreakdown => [
2532 { indexname => 'ordernumber',
2533 content => 'ordernumber',
2535 { indexname => 'bookfundid',
2536 content => 'bookfundid',
2540 { indexname => 'isbn',
2543 { indexname => 'publishercode',
2544 content => 'publishercode',
2549 indexname => 'borrowernumber',
2550 content => 'borrowernumber',
2557 indexname => 'branchcode',
2558 content => 'branchcode',
2562 branchrelations => [
2564 indexname => 'PRIMARY',
2565 content => 'categorycode',
2569 branchrelations => [
2570 { indexname => 'PRIMARY',
2571 content => 'branchcode,categorycode',
2574 { indexname => 'branchcode',
2575 content => 'branchcode',
2577 { indexname => 'categorycode',
2578 content => 'categorycode',
2582 { indexname => 'PRIMARY',
2583 content => 'currency',
2589 indexname => 'categorycode',
2590 content => 'categorycode',
2595 indexname => 'categorycode',
2596 content => 'categorycode',
2599 indexname => 'itemtype',
2600 content => 'itemtype',
2604 { indexname => 'homebranch',
2605 content => 'homebranch',
2607 { indexname => 'holdingbranch',
2608 content => 'holdingbranch',
2613 indexname => 'itemtype',
2614 content => 'itemtype',
2618 { indexname => 'shelfnumber',
2619 content => 'shelfnumber',
2621 { indexname => 'itemnumber',
2622 content => 'itemnumber',
2626 { indexname => 'PRIMARY',
2633 my %foreign_keys = (
2635 # { key => 'the key in table' (must be indexed)
2636 # foreigntable => 'the foreigntable name', # (the parent)
2637 # foreignkey => 'the foreign key column(s)' # (in the parent)
2638 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2639 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2642 branchrelations => [
2643 { key => 'branchcode',
2644 foreigntable => 'branches',
2645 foreignkey => 'branchcode',
2646 onUpdate => 'CASCADE',
2647 onDelete => 'CASCADE',
2649 { key => 'categorycode',
2650 foreigntable => 'branchcategories',
2651 foreignkey => 'categorycode',
2652 onUpdate => 'CASCADE',
2653 onDelete => 'CASCADE',
2657 { key => 'shelfnumber',
2658 foreigntable => 'bookshelf',
2659 foreignkey => 'shelfnumber',
2660 onUpdate => 'CASCADE',
2661 onDelete => 'CASCADE',
2663 { key => 'itemnumber',
2664 foreigntable => 'items',
2665 foreignkey => 'itemnumber',
2666 onUpdate => 'CASCADE',
2667 onDelete => 'CASCADE',
2670 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
2671 # easily deleted, but branches/itemtype not too easy to empty...
2673 { key => 'biblionumber',
2674 foreigntable => 'biblio',
2675 foreignkey => 'biblionumber',
2676 onUpdate => 'CASCADE',
2677 onDelete => 'CASCADE',
2681 { key => 'biblioitemnumber',
2682 foreigntable => 'biblioitems',
2683 foreignkey => 'biblioitemnumber',
2684 onUpdate => 'CASCADE',
2685 onDelete => 'CASCADE',
2687 { key => 'homebranch',
2688 foreigntable => 'branches',
2689 foreignkey => 'branchcode',
2690 onUpdate => 'CASCADE',
2691 onDelete => 'RESTRICT',
2693 { key => 'holdingbranch',
2694 foreigntable => 'branches',
2695 foreignkey => 'branchcode',
2696 onUpdate => 'CASCADE',
2697 onDelete => 'RESTRICT',
2701 { key => 'booksellerid',
2702 foreigntable => 'aqbooksellers',
2704 onUpdate => 'CASCADE',
2705 onDelete => 'RESTRICT',
2709 { key => 'basketno',
2710 foreigntable => 'aqbasket',
2711 foreignkey => 'basketno',
2712 onUpdate => 'CASCADE',
2713 onDelete => 'CASCADE',
2715 { key => 'biblionumber',
2716 foreigntable => 'biblio',
2717 foreignkey => 'biblionumber',
2718 onUpdate => 'SET NULL',
2719 onDelete => 'SET NULL',
2723 { key => 'listprice',
2724 foreigntable => 'currency',
2725 foreignkey => 'currency',
2726 onUpdate => 'CASCADE',
2727 onDelete => 'CASCADE',
2729 { key => 'invoiceprice',
2730 foreigntable => 'currency',
2731 foreignkey => 'currency',
2732 onUpdate => 'CASCADE',
2733 onDelete => 'CASCADE',
2736 aqorderbreakdown => [
2737 { key => 'ordernumber',
2738 foreigntable => 'aqorders',
2739 foreignkey => 'ordernumber',
2740 onUpdate => 'CASCADE',
2741 onDelete => 'CASCADE',
2743 { key => 'bookfundid',
2744 foreigntable => 'aqbookfund',
2745 foreignkey => 'bookfundid',
2746 onUpdate => 'CASCADE',
2747 onDelete => 'CASCADE',
2750 branchtransfers => [
2751 { key => 'frombranch',
2752 foreigntable => 'branches',
2753 foreignkey => 'branchcode',
2754 onUpdate => 'CASCADE',
2755 onDelete => 'CASCADE',
2757 { key => 'tobranch',
2758 foreigntable => 'branches',
2759 foreignkey => 'branchcode',
2760 onUpdate => 'CASCADE',
2761 onDelete => 'CASCADE',
2763 { key => 'itemnumber',
2764 foreigntable => 'items',
2765 foreignkey => 'itemnumber',
2766 onUpdate => 'CASCADE',
2767 onDelete => 'CASCADE',
2770 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
2772 { key => 'borrowernumber',
2773 foreigntable => 'borrowers',
2774 foreignkey => 'borrowernumber',
2775 onUpdate => 'SET NULL',
2776 onDelete => 'SET NULL',
2778 { key => 'itemnumber',
2779 foreigntable => 'items',
2780 foreignkey => 'itemnumber',
2781 onUpdate => 'SET NULL',
2782 onDelete => 'SET NULL',
2786 { key => 'borrowernumber',
2787 foreigntable => 'borrowers',
2788 foreignkey => 'borrowernumber',
2789 onUpdate => 'CASCADE',
2790 onDelete => 'CASCADE',
2792 { key => 'biblionumber',
2793 foreigntable => 'biblio',
2794 foreignkey => 'biblionumber',
2795 onUpdate => 'CASCADE',
2796 onDelete => 'CASCADE',
2798 { key => 'itemnumber',
2799 foreigntable => 'items',
2800 foreignkey => 'itemnumber',
2801 onUpdate => 'CASCADE',
2802 onDelete => 'CASCADE',
2804 { key => 'branchcode',
2805 foreigntable => 'branches',
2806 foreignkey => 'branchcode',
2807 onUpdate => 'CASCADE',
2808 onDelete => 'CASCADE',
2811 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
2812 # but prevent deleting a branch as soon as it has 1 borrower !
2813 { key => 'categorycode',
2814 foreigntable => 'categories',
2815 foreignkey => 'categorycode',
2816 onUpdate => 'RESTRICT',
2817 onDelete => 'RESTRICT',
2819 { key => 'branchcode',
2820 foreigntable => 'branches',
2821 foreignkey => 'branchcode',
2822 onUpdate => 'RESTRICT',
2823 onDelete => 'RESTRICT',
2827 { key => 'borrowernumber',
2828 foreigntable => 'borrowers',
2829 foreignkey => 'borrowernumber',
2830 onUpdate => 'CASCADE',
2831 onDelete => 'CASCADE',
2833 { key => 'itemnumber',
2834 foreigntable => 'items',
2835 foreignkey => 'itemnumber',
2836 onUpdate => 'SET NULL',
2837 onDelete => 'SET NULL',
2841 { key => 'borrowernumber',
2842 foreigntable => 'borrowers',
2843 foreignkey => 'borrowernumber',
2844 onUpdate => 'CASCADE',
2845 onDelete => 'CASCADE',
2848 auth_tag_structure => [
2849 { key => 'authtypecode',
2850 foreigntable => 'auth_types',
2851 foreignkey => 'authtypecode',
2852 onUpdate => 'CASCADE',
2853 onDelete => 'CASCADE',
2856 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
2861 my %column_change = (
2865 from => 'emailaddress',
2870 from => 'streetaddress',
2872 after => 'initials',
2875 from => 'faxnumber',
2880 from => 'textmessaging',
2886 to => 'contactnote',
2887 after => 'opacnote',
2890 from => 'physstreet',
2895 from => 'streetcity',
2897 after => 'B_address',
2910 from => 'homezipcode',
2917 after => 'B_zipcode',
2922 after => 'dateenrolled',
2925 from => 'guarantor',
2926 to => 'guarantorid',
2927 after => 'contactname',
2930 from => 'altrelationship',
2931 to => 'relationship',
2932 after => 'borrowernotes',
2936 deletedborrowers => [
2938 from => 'emailaddress',
2943 from => 'streetaddress',
2945 after => 'initials',
2948 from => 'faxnumber',
2953 from => 'textmessaging',
2959 to => 'contactnote',
2960 after => 'opacnote',
2963 from => 'physstreet',
2968 from => 'streetcity',
2970 after => 'B_address',
2983 from => 'homezipcode',
2990 after => 'B_zipcode',
2995 after => 'dateenrolled',
2998 from => 'guarantor',
2999 to => 'guarantorid',
3000 after => 'contactname',
3003 from => 'altrelationship',
3004 to => 'relationship',
3005 after => 'borrowernotes',
3011 # MOVE all tables TO UTF-8 and innoDB
3012 $sth = $dbh->prepare("show table status");
3014 while ( my $table = $sth->fetchrow_hashref ) {
3015 next if $table->{Name} eq 'marc_word';
3016 next if $table->{Name} eq 'marc_subfield_table';
3017 next if $table->{Name} eq 'auth_word';
3018 next if $table->{Name} eq 'auth_subfield_table';
3019 if ($table->{Engine} ne 'InnoDB') {
3020 print "moving $table->{Name} to InnoDB\n";
3021 $dbh->do("ALTER TABLE $table->{Name} ENGINE = innodb");
3023 unless ($table->{Collation} =~ /^utf8/) {
3024 print "moving $table->{Name} to utf8\n";
3025 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
3026 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
3027 # 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 !
3032 # list of columns that must exist for %column_change to be
3033 # processed without error, but which do not necessarily exist
3034 # in all 2.2 databases
3035 my %required_prereq_fields = (
3036 deletedborrowers => [
3037 [ 'textmessaging', 'mediumtext AFTER faxnumber' ],
3038 [ 'password', 'varchar(30) default NULL' ],
3039 [ 'flags', 'int(11) default NULL' ],
3040 [ 'userid', 'varchar(30) default NULL' ],
3041 [ 'homezipcode', 'varchar(25) default NULL' ],
3042 [ 'zipcode', 'varchar(25) default NULL' ],
3043 [ 'sort1', 'varchar(80) default NULL' ],
3044 [ 'sort2', 'varchar(80) default NULL' ],
3048 foreach my $table ( keys %required_prereq_fields ) {
3049 print "Check table $table\n" if $debug and not $silent;
3050 $sth = $dbh->prepare("show columns from $table");
3053 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3055 $types{$column} = $type;
3057 foreach my $entry ( @{ $required_prereq_fields{$table} } ) {
3058 ($column, $type) = @{ $entry };
3059 print " Check column $column [$type]\n" if $debug and not $silent;
3060 if ( !$types{$column} ) {
3062 # column doesn't exist
3063 print "Adding $column field to $table table...\n" unless $silent;
3064 $query = "alter table $table
3065 add column $column " . $type;
3066 print "Execute: $query\n" if $debug;
3067 my $sti = $dbh->prepare($query);
3070 print "**Error : $sti->errstr \n";
3077 foreach my $table (keys %column_change) {
3078 $sth = $dbh->prepare("show columns from $table");
3081 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3083 $types{$column}->{type} ="$type";
3084 $types{$column}->{null} = "$null";
3085 $types{$column}->{key} = "$key";
3086 $types{$column}->{default} = "$default";
3087 $types{$column}->{extra} = "$extra";
3089 my $tablerows = $column_change{$table};
3090 foreach my $row ( @$tablerows ) {
3091 if ($types{$row->{from}}->{type}) {
3092 print "altering $table $row->{from} to $row->{to}\n";
3093 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
3094 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
3096 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
3097 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
3098 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
3099 "$types{$row->{from}}->{extra} after $row->{after} ";
3106 # Enter here the field you want to delete from DB.
3107 # FIXME :: there is a %uselessfield before which seems doing the same things.
3108 my %fieldtodelete = (
3109 # tablename => [fieldname1,fieldname2,...]
3113 print "removing some unused fields...\n";
3114 foreach my $table ( keys %fieldtodelete ) {
3115 foreach my $field ( @{$fieldtodelete{$table}} ){
3116 print "removing ".$field." from ".$table;
3117 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
3120 print "Error : $sth->errstr \n";
3125 # Enter here the line you want to remove from DB.
3126 my %linetodelete = (
3127 # table name => where clause.
3128 userflags => [ "bit = 8" ], # delete the 'reserveforself' flags
3132 #-------------------
3137 # Get version of MySQL database engine.
3138 my $mysqlversion = `mysqld --version`;
3139 $mysqlversion =~ /Ver (\S*) /;
3141 if ( $mysqlversion ge '3.23' ) {
3142 print "Could convert to MyISAM database tables...\n" unless $silent;
3145 #---------------------------------
3148 # Collect all tables into a list
3149 $sth = $dbh->prepare("show tables");
3151 while ( my ($table) = $sth->fetchrow ) {
3152 $existingtables{$table} = 1;
3156 # Now add any missing tables
3157 foreach my $table ( keys %requiretables ) {
3158 unless ( $existingtables{$table} ) {
3159 print "Adding $table table...\n" unless $silent;
3160 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
3163 print "Error : $sth->errstr \n";
3169 #---------------------------------
3172 foreach my $table ( keys %requirefields ) {
3173 print "Check table $table\n" if $debug and not $silent;
3174 $sth = $dbh->prepare("show columns from $table");
3177 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3179 $types{$column} = $type;
3181 foreach my $column ( keys %{ $requirefields{$table} } ) {
3182 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
3183 if ( !$types{$column} ) {
3185 # column doesn't exist
3186 print "Adding $column field to $table table...\n" unless $silent;
3187 $query = "alter table $table
3188 add column $column " . $requirefields{$table}->{$column};
3189 print "Execute: $query\n" if $debug;
3190 my $sti = $dbh->prepare($query);
3193 print "**Error : $sti->errstr \n";
3200 foreach my $table ( sort keys %fielddefinitions ) {
3201 print "Check table $table\n" if $debug;
3202 $sth = $dbh->prepare("show columns from $table");
3205 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3207 $definitions->{$column}->{type} = $type;
3208 $definitions->{$column}->{null} = $null;
3209 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
3210 $definitions->{$column}->{key} = $key;
3211 $definitions->{$column}->{default} = $default;
3212 $definitions->{$column}->{extra} = $extra;
3214 my $fieldrow = $fielddefinitions{$table};
3215 foreach my $row (@$fieldrow) {
3216 my $field = $row->{field};
3217 my $type = $row->{type};
3218 my $null = $row->{null};
3219 # $null = 'YES' if $row->{null} eq 'NULL';
3220 my $key = $row->{key};
3221 my $default = $row->{default};
3222 # $default="''" unless $default;
3223 my $extra = $row->{extra};
3224 my $def = $definitions->{$field};
3225 my $after = ($row->{after}?" after ".$row->{after}:"");
3227 unless ( $type eq $def->{type}
3228 && $null eq $def->{null}
3229 && $key eq $def->{key}
3230 && $default eq $def->{default}
3231 && $extra eq $def->{extra} )
3233 if ( $null eq '' ) {
3236 if ( $key eq 'PRI' ) {
3237 $key = 'PRIMARY KEY';
3239 unless ( $extra eq 'auto_increment' ) {
3243 # if it's a new column use "add", if it's an old one, use "change".
3245 if ($definitions->{$field}->{type}) {
3246 $action="change `$field`"
3250 # if it's a primary key, drop the previous pk, before altering the table
3251 print " alter or create $field in $table\n" unless $silent;
3253 if ($key ne 'PRIMARY KEY') {
3254 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
3255 $query = "alter table $table $action `$field` $type $null $key $extra ".
3256 GetDefaultClause($default)." $after";
3258 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
3259 # something strange : for indexes UNIQUE, they are reported as primary key here.
3260 # but if you try to run with drop primary key, it fails.
3261 # thus, we run the query twice, one will fail, one will succeed.
3263 $query="alter table $table drop primary key, $action `$field` $type $null $key $extra ".
3264 GetDefaultClause($default)." $after";
3265 $query="alter table $table $action `$field` $type $null $key $extra ".
3266 GetDefaultClause($default)." $after";
3268 $dbh->do($query) or warn "Error while executing: $query";
3273 print "removing some unused data...\n";
3274 foreach my $table ( keys %linetodelete ) {
3275 foreach my $where ( @{$linetodelete{$table}} ){
3276 print "DELETE FROM ".$table." where ".$where;
3278 my $sth = $dbh->prepare("DELETE FROM $table where $where");
3281 print "Error : $sth->errstr \n";
3286 # Populate tables with required data
3288 # synch table and deletedtable.
3289 foreach my $table (('borrowers','items','biblio','biblioitems')) {
3290 my %deletedborrowers;
3291 print "synch'ing $table and deleted$table\n";
3292 $sth = $dbh->prepare("show columns from deleted$table");
3294 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3295 $deletedborrowers{$column}=1;
3297 $sth = $dbh->prepare("show columns from $table");
3300 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3301 unless ($deletedborrowers{$column}) {
3302 my $newcol="alter table deleted$table add $column $type";
3303 if ($null eq 'YES') {
3304 $newcol .= " NULL ";
3306 $newcol .= " NOT NULL ";
3308 $newcol .= "default ".$dbh->quote($default) if $default;
3309 $newcol .= " after $previous" if $previous;
3311 print "creating column $column\n";
3317 # update publisheddate
3319 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
3321 my ($emptypublished) = $sth->fetchrow;
3322 if ($emptypublished) {
3323 print "Updating publisheddate\n";
3324 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
3326 # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it.
3327 # now, let's get rid of 000-00-00's.
3329 $dbh->do("update serial set publisheddate=NULL where publisheddate = 0");
3330 $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0");
3332 foreach my $table ( keys %tabledata ) {
3333 print "Checking for data required in table $table...\n" unless $silent;
3334 my $tablerows = $tabledata{$table};
3335 foreach my $row (@$tablerows) {
3336 my $uniquefieldrequired = $row->{uniquefieldrequired};
3337 my $uniquevalue = $row->{$uniquefieldrequired};
3338 my $forceupdate = $row->{forceupdate};
3341 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
3343 $sth->execute($uniquevalue);
3345 foreach my $field (keys %$forceupdate) {
3346 if ($forceupdate->{$field}) {
3347 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
3348 $sth->execute($row->{$field}, $uniquevalue);
3352 print "Adding row to $table: " unless $silent;
3356 foreach my $field ( keys %$row ) {
3357 next if $field eq 'uniquefieldrequired';
3358 next if $field eq 'forceupdate';
3359 my $value = $row->{$field};
3360 push @values, $value;
3361 print " $field => $value" unless $silent;
3362 $fieldlist .= "$field,";
3363 $placeholders .= "?,";
3365 print "\n" unless $silent;
3366 $fieldlist =~ s/,$//;
3367 $placeholders =~ s/,$//;
3368 print "insert into $table ($fieldlist) values ($placeholders)";
3371 "insert into $table ($fieldlist) values ($placeholders)");
3372 $sth->execute(@values);
3378 # check indexes and create them when needed
3380 print "Checking for index required...\n" unless $silent;
3381 foreach my $table ( keys %indexes ) {
3383 # read all indexes from $table
3385 $sth = $dbh->prepare("show index from $table");
3387 my %existingindexes;
3388 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
3389 $existingindexes{$key_name} = 1;
3391 # read indexes to check
3392 my $tablerows = $indexes{$table};
3393 foreach my $row (@$tablerows) {
3394 my $key_name=$row->{indexname};
3395 if ($existingindexes{$key_name} eq 1 and not $row->{force}) {
3396 # print "$key_name existing";
3398 print "\tCreating index $key_name in $table\n";
3400 if ($row->{indexname} eq 'PRIMARY' or $row->{type} eq 'PRI') {
3401 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
3403 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
3406 print "Error $sql : $dbh->err \n" if $dbh->err;
3412 # check foreign keys and create them when needed
3414 print "Checking for foreign keys required...\n" unless $silent;
3415 foreach my $table ( sort keys %foreign_keys ) {
3417 # read all indexes from $table
3419 $sth = $dbh->prepare("show table status like '$table'");
3421 my $stat = $sth->fetchrow_hashref;
3422 # read indexes to check
3423 my $tablerows = $foreign_keys{$table};
3424 foreach my $row (@$tablerows) {
3425 my $foreign_table=$row->{foreigntable};
3426 if ($stat->{'Comment'} =~/$foreign_table/) {
3427 # print "$foreign_table existing\n";
3429 print "\tCreating foreign key $foreign_table in $table\n";
3430 # first, drop any orphan value in child table
3431 if ($row->{onDelete} ne "RESTRICT") {
3432 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
3434 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
3436 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
3437 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
3438 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
3441 print "====================
3442 An error occurred during :
3444 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).
3445 You can find those values with select
3446 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
3447 ====================\n
3453 # now drop useless tables
3454 foreach my $table ( @TableToDelete ) {
3455 if ( $existingtables{$table} ) {
3456 print "Dropping unused table $table\n" if $debug and not $silent;
3457 $dbh->do("drop table $table");
3459 print "Error : $dbh->errstr \n";
3468 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
3471 # 1st, get how many biblio we will have to do...
3472 $sth = $dbh->prepare('select count(*) from marc_biblio');
3474 my ($totaltodo) = $sth->fetchrow;
3476 $sth = $dbh->prepare("show columns from biblio");
3479 my $bibliofwexist=0;
3480 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
3481 $bibliofwexist=1 if $column eq 'frameworkcode';
3483 unless ($bibliofwexist) {
3484 print "moving biblioframework to biblio table\n";
3485 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
3486 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
3488 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
3490 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
3491 $sth_update->execute($frameworkcode,$biblionumber);
3493 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
3498 # at last, remove useless fields
3499 foreach my $table ( keys %uselessfields ) {
3500 my @fields = split (/,/,$uselessfields{$table});
3502 foreach my $fieldtodrop (@fields) {
3503 $fieldtodrop =~ s/\t//g;
3504 $fieldtodrop =~ s/\n//g;
3506 $sth = $dbh->prepare("show columns from $table");
3508 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3510 $exists =1 if ($column eq $fieldtodrop);
3513 print "deleting $fieldtodrop field in $table...\n" unless $silent;
3514 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
3521 # Changing aqbookfund's primary key
3523 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
3527 # drop extra key on borrowers.borrowernumber
3528 $dbh->do("ALTER TABLE borrowers DROP KEY borrowernumber");
3530 # update enrolmentperiod
3531 $dbh->do("UPDATE categories SET enrolmentperiod = enrolmentperiod * 12");
3533 print "upgrade to Koha 3.0 done\n";
3534 SetVersion ($DBversion);
3536 =head1 GetDefaultClause
3538 Generate a default clause (for an ALTER TABLE command)
3542 sub GetDefaultClause {
3543 my $default = shift;
3545 return "" unless defined $default;
3546 return "" if $default eq '';
3547 return "default ''" if $default eq "''";
3548 return "default NULL" if $default eq "NULL";
3549 return "default " . $dbh->quote($default);
3552 =head1 TransformToNum
3554 Transform the Koha version from a 4 parts string
3555 to a number, with just 1.
3559 sub TransformToNum {
3560 my $version = shift;
3561 # remove the 3 last . to have a Perl number
3562 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3568 set the DBversion in the systempreferences
3573 my $kohaversion = TransformToNum(shift);
3574 if (C4::Context->preference('Version')) {
3575 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3576 $finish->execute($kohaversion);
3578 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')");
3579 $finish->execute($kohaversion);
3584 # Revision 1.172 2007/07/19 10:21:22 hdl