6 # This script checks for required updates to the database.
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
12 # - Would also be a good idea to offer to do a backup at this time...
14 # NOTE: If you do something more than once in here, make it table driven.
25 use MARC::File::XML ( BinaryEncoding => 'utf8' );
27 # FIXME - The user might be installing a new database, so can't rely
28 # on /etc/koha.conf anyway.
35 %existingtables, # tables already in database
39 $type, $null, $key, $default, $extra,
40 $prefitem, # preference item in systempreferences table
44 GetOptions( 's' => \$silent );
45 my $dbh = C4::Context->dbh;
46 print "connected to your DB. Checking & modifying it\n" unless $silent;
47 $|=1; # flushes output
52 # Tables to add if they don't exist
54 categorytable => "(categorycode char(5) NOT NULL default '',
55 description text default '',
56 itemtypecodes text default '',
57 PRIMARY KEY (categorycode)
59 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
60 description text default '',
61 itemtypecodes text default '',
62 PRIMARY KEY (subcategorycode)
64 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
65 description text default '',
66 itemtypecodes text default '',
67 PRIMARY KEY (mediatypecode)
70 `timestamp` TIMESTAMP NOT NULL ,
71 `user` INT( 11 ) NOT NULL ,
72 `module` TEXT default '',
73 `action` TEXT default '' ,
74 `object` INT(11) default '' ,
75 `info` TEXT default '' ,
76 PRIMARY KEY ( `timestamp` , `user` )
79 module varchar(20) NOT NULL default '',
80 code varchar(20) NOT NULL default '',
81 name varchar(100) NOT NULL default '',
82 title varchar(200) NOT NULL default '',
84 PRIMARY KEY (module,code)
87 alertid int(11) NOT NULL auto_increment,
88 borrowernumber int(11) NOT NULL default '0',
89 type varchar(10) NOT NULL default '',
90 externalid varchar(20) NOT NULL default '',
91 PRIMARY KEY (alertid),
92 KEY borrowernumber (borrowernumber),
93 KEY type (type,externalid)
96 `idnew` int(10) unsigned NOT NULL auto_increment,
97 `title` varchar(250) NOT NULL default '',
99 `lang` varchar(4) NOT NULL default '',
100 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
101 PRIMARY KEY (`idnew`)
103 repeatable_holidays => "(
104 `id` int(11) NOT NULL auto_increment,
105 `branchcode` varchar(4) NOT NULL default '',
106 `weekday` smallint(6) default NULL,
107 `day` smallint(6) default NULL,
108 `month` smallint(6) default NULL,
109 `title` varchar(50) NOT NULL default '',
110 `description` text NOT NULL,
113 special_holidays => "(
114 `id` int(11) NOT NULL auto_increment,
115 `branchcode` varchar(4) NOT NULL default '',
116 `day` smallint(6) NOT NULL default '0',
117 `month` smallint(6) NOT NULL default '0',
118 `year` smallint(6) NOT NULL default '0',
119 `isexception` smallint(1) NOT NULL default '1',
120 `title` varchar(50) NOT NULL default '',
121 `description` text NOT NULL,
124 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
125 `categorycode` char(2) NOT NULL default '',
126 `delay1` int(4) default '0',
127 `letter1` varchar(20) default NULL,
128 `debarred1` char(1) default '0',
129 `delay2` int(4) default '0',
130 `debarred2` char(1) default '0',
131 `letter2` varchar(20) default NULL,
132 `delay3` int(4) default '0',
133 `letter3` varchar(20) default NULL,
134 `debarred3` int(1) default '0',
135 PRIMARY KEY (`branchcode`,`categorycode`)
137 cities => "(`cityid` int auto_increment,
138 `city_name` char(100) NOT NULL,
139 `city_zipcode` char(20),
140 PRIMARY KEY (`cityid`)
142 roadtype => "(`roadtypeid` int auto_increment,
143 `road_type` char(100) NOT NULL,
144 PRIMARY KEY (`roadtypeid`)
148 labelid int(11) NOT NULL auto_increment,
149 itemnumber varchar(100) NOT NULL default '',
150 timestamp timestamp(14) NOT NULL,
151 PRIMARY KEY (labelid)
155 id int(4) NOT NULL auto_increment,
156 barcodetype char(100) default '',
157 title tinyint(1) default '0',
158 isbn tinyint(1) default '0',
159 itemtype tinyint(1) default '0',
160 barcode tinyint(1) default '0',
161 dewey tinyint(1) default '0',
162 class tinyint(1) default '0',
163 author tinyint(1) default '0',
164 papertype char(100) default '',
165 startrow int(2) default NULL,
169 reviewid integer NOT NULL auto_increment,
170 borrowernumber integer,
171 biblionumber integer,
174 datereviewed datetime,
175 PRIMARY KEY (reviewid)
177 borrowers_to_borrowers => "(
181 subscriptionroutinglist => "(
182 routingid int(11) NOT NULL auto_increment,
183 subscriptionid int(11) NOT NULL default '0',
184 borrowernumber int(11) NOT NULL default '0',
185 ranking int(11) NOT NULL default '0',
186 numberpattern varchar(100) NULL,
187 PRIMARY KEY (routingid)
192 my %requirefields = (
193 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'},
194 itemtypes => { 'imageurl' => 'char(200) NULL'},
195 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
196 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
197 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
198 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL UNSIGNED ZEROFILL', 'kohafield' => 'VARCHAR(45) NOT NULL', 'linkid' => 'TINYINT(1) NOT NULL UNSIGNED', 'isurl' => 'TINYINT(1) UNSIGNED'},
199 statistics => { 'associatedborrower' => 'integer'},
200 # tablename => { 'field' => 'fieldtype' },
203 my %dropable_table = (
204 sessionqueries => 'sessionqueries',
205 marcrecorddone => 'marcrecorddone',
207 itemsprices => 'itemsprices',
208 biblioanalysis => 'biblioanalysis',
210 # tablename => 'tablename',
213 my %uselessfields = (
214 # tablename => "field1,field2",
215 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
216 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
218 # the other hash contains other actions that can't be done elsewhere. they are done
219 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
221 # The tabledata hash contains data that should be in the tables.
222 # The uniquefieldrequired hash entry is used to determine which (if any) fields
223 # must not exist in the table for this row to be inserted. If the
224 # uniquefieldrequired entry is already in the table, the existing data is not
225 # modified, unless the forceupdate hash entry is also set. Fields in the
226 # anonymous "forceupdate" hash will be forced to be updated to the default
227 # values given in the %tabledata hash.
231 # { uniquefielrequired => 'fieldname', # the primary key in the table
232 # fieldname => fieldvalue,
233 # fieldname2 => fieldvalue2,
236 systempreferences => [
238 uniquefieldrequired => 'variable',
239 variable => 'Activate_Log',
241 forceupdate => { 'explanation' => 1,
243 explanation => 'Turn Log Actions on DB On an Off',
247 uniquefieldrequired => 'variable',
248 variable => 'IndependantBranches',
250 forceupdate => { 'explanation' => 1,
252 explanation => 'Turn Branch independancy management On an Off',
256 uniquefieldrequired => 'variable',
257 variable => 'ReturnBeforeExpiry',
259 forceupdate => { 'explanation' => 1,
261 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
266 uniquefieldrequired => 'variable',
267 variable => 'opacstylesheet',
274 'Enter a complete URL to use an alternate layout stylesheet in OPAC',
278 uniquefieldrequired => 'variable',
279 variable => 'opaccolorstylesheet',
286 'Enter the name of the color stylesheet to use in the OPAC',
290 uniquefieldrequired => 'variable',
291 variable => 'opaclayoutstylesheet',
298 'Enter the name of the layout stylesheet to use in the OPAC',
303 uniquefieldrequired => 'variable',
304 variable => 'opacreadinghistory',
311 'Turn on/off display of Patron Reading History in OPAC',
315 uniquefieldrequired => 'variable',
316 variable => 'opaclanguagesdisplay',
323 'Turn on/off display of Change Language feature on OPAC',
327 uniquefieldrequired => 'variable',
328 variable => 'patronimages',
335 'Turn on/off display of patron images in Intranet and specify a file extension for images',
339 uniquefieldrequired => 'variable',
340 variable => 'intranetstylesheet',
347 'Enter a complete URL to use an alternate layout stylesheet in Intranet',
351 uniquefieldrequired => 'variable',
352 variable => 'intranetcolorstylesheet',
359 'Enter the name of the color stylesheet to use in Intranet',
363 uniquefieldrequired => 'variable',
364 variable => 'opacsmallimage',
371 'Enter a complete URL to an image, will be on top/left instead of the Koha logo',
375 uniquefieldrequired => 'variable',
376 variable => 'opaclargeimage',
383 'Enter a complete URL to an image, will be on the main page, instead of the Koha logo',
387 uniquefieldrequired => 'variable',
388 variable => 'delimiter',
394 explanation => 'separator for reports exported to spreadsheet',
398 uniquefieldrequired => 'variable',
400 value => 'OPENOFFICE.ORG',
407 'Define the default application for report exportations into files',
409 options => 'EXCEL|OPENOFFICE.ORG'
412 uniquefieldrequired => 'variable',
413 variable => 'Delimiter',
421 'Define the default separator character for report exportations into files',
423 options => ';|tabulation|,|/|\|#'
426 uniquefieldrequired => 'variable',
427 variable => 'SubscriptionHistory',
435 'Define the information level for serials history in OPAC',
437 options => 'simplified|full'
440 uniquefieldrequired => 'variable',
441 variable => 'hidelostitems',
447 explanation => 'show or hide "lost" items in OPAC.',
451 uniquefieldrequired => 'variable',
452 variable => 'IndependantBranches',
458 explanation => 'Turn Branch independency management On and Off',
462 uniquefieldrequired => 'variable',
463 variable => 'ReturnBeforeExpiry',
470 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
474 uniquefieldrequired => 'variable',
475 variable => 'Disable_Dictionary',
481 explanation => 'Disables Dictionary buttons if set to yes',
485 uniquefieldrequired => 'variable',
486 variable => 'hide_marc',
493 'hide marc specific datas like subfield code & indicators to library',
497 uniquefieldrequired => 'variable',
498 variable => 'NotifyBorrowerDeparture',
505 'Delay before expiry where a notice is sent when issuing',
509 uniquefieldrequired => 'variable',
510 variable => 'OpacPasswordChange',
517 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
521 uniquefieldrequired => 'variable',
522 variable => 'OpacNav',
529 'Use HTML tabs to add navigational links to the left-hand navigational bar in OPAC',
534 uniquefieldrequired => 'variable',
535 variable => 'IntranetNav',
542 'Use HTML tabs to add navigational links to the left-hand navigational bar in Intranet',
548 uniquefieldrequired => 'variable',
549 variable => 'AnonSuggestions',
557 'Set to anonymous borrowernumber to enable Anonymous suggestions',
561 uniquefieldrequired => 'variable',
562 variable => 'MARCOrgCode',
570 'Your MARC Organization Code - http://www.loc.gov/marc/organizations/orgshome.html',
574 uniquefieldrequired => 'variable',
575 variable => 'AmazonContent',
583 'Turn On Amazon Content - You MUST set AmazonDevKey and AmazonAssocTag if enabled',
587 uniquefieldrequired => 'variable',
588 variable => 'AmazonDevKey',
596 'see: aws-portal.amazon.com/gp/aws/developer/registration/index.html',
600 uniquefieldrequired => 'variable',
601 variable => 'AmazonAssocTag',
609 'see: associates.amazon.com/gp/flex/associates/apply-login.html',
613 uniquefieldrequired => 'variable',
619 variable => 'TemplateEncoding',
620 value => 'iso-8859-1',
621 explanation => 'Specify the encoding to use in Templates',
623 options => 'iso-8859-1|utf-8'
627 uniquefieldrequired => 'variable',
628 variable => 'opaccredits',
636 'Put any HTML Credits at the bottom of the OPAC page',
642 uniquefieldrequired => 'variable',
643 variable => 'opacheader',
645 forceupdate => { 'explanation' => 1,
647 explanation => 'Enter HTML to be included as a custom header in the OPAC',
653 uniquefieldrequired => 'variable',
654 variable => 'IntranetBiblioDefaultView',
656 forceupdate => { 'explanation' => 1,
658 explanation => 'Define the default view of a biblio in the intranet. Can be either normal, marc, or ISBD',
660 options => 'normal|marc|isbd'
664 uniquefieldrequired => 'variable',
665 variable => 'opacbookbag',
667 forceupdate => { 'explanation' => 1,
669 explanation => 'Enable or disable display of biblio basket (book bag)',
674 uniquefieldrequired => 'variable',
675 variable => 'opacuserlogin',
677 forceupdate => { 'explanation' => 1,
679 explanation => 'Enable or disable display of user login features',
684 uniquefieldrequired => 'variable',
685 variable => 'serialsadditems',
693 'If set, a new item will be automatically added when receiving an issue',
698 uniquefieldrequired => 'variable',
699 variable => 'RoutingSerials',
707 'If set, will use alternate serials and routing lists functionality',
712 uniquefieldrequired => 'variable',
713 variable => 'advancedMARCeditor',
721 "If set, the MARC editor won't show you tag/subfields description",
725 uniquefieldrequired => 'variable',
726 variable => 'z3950NormalizeAuthor',
734 "If set, Personnal Authorities will replace authors in biblio.author",
738 uniquefieldrequired => 'variable',
739 variable => 'z3950AuthorAuthFields',
740 value => '701,702,700',
747 "contains the MARC biblio tags of person authorities to fill biblio.author with when importing biblio",
751 uniquefieldrequired => 'variable',
752 variable => 'useDaysMode',
754 forceupdate => { 'explanation' => 1,
756 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
758 options => 'Calendar|Days'
761 uniquefieldrequired => 'variable',
762 variable => 'borrowerMandatoryField',
763 value => 'zipcode|surname',
764 forceupdate => { 'explanation' => 1,
766 explanation => 'List all mandatory fields for borrowers',
770 uniquefieldrequired => 'variable',
771 variable => 'borrowerRelationship',
772 value => 'father|mother,grand-mother',
773 forceupdate => { 'explanation' => 1,
775 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
779 uniquefieldrequired => 'variable',
780 variable => 'ReservesMaxPickUpDelay',
782 forceupdate => { 'explanation' => 1,
784 explanation => 'Maximum delay to pick up a reserved document',
788 uniquefieldrequired => 'variable',
789 variable => 'TransfersMaxDaysWarning',
791 forceupdate => { 'explanation' => 1,
793 explanation => 'Max delay before considering the transfer has potentialy a problem',
797 uniquefieldrequired => 'variable',
798 variable => 'memberofinstitution',
800 forceupdate => { 'explanation' => 1,
802 explanation => 'Are your patrons members of institutions',
806 uniquefieldrequired => 'variable',
807 variable => 'ReadingHistory',
809 forceupdate => { 'explanation' => 1,
811 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
815 uniquefieldrequired => 'variable',
816 variable => 'IssuingInProcess',
818 forceupdate => { 'explanation' => 1,
820 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
824 uniquefieldrequired => 'variable',
825 variable => 'AutomaticItemReturn',
827 forceupdate => { 'explanation' => 1,
829 explanation => 'This Variable allow or not to return automaticly to his homebranch',
833 uniquefieldrequired => 'variable',
834 variable => 'reviewson',
836 forceupdate => { 'explanation' => 1,
838 explanation => 'Allows patrons to submit reviews from the opac',
845 my %fielddefinitions = (
847 # { field => 'fieldname',
848 # type => 'fieldtype',
864 field => 'routingnotes',
872 field => 'claimdate',
883 field => 'firstacquidate',
887 default => '0000-00-00',
891 field => 'numberpattern',
899 field => 'irregularity',
900 type => 'varchar(255)',
907 field => 'hemisphere',
915 field => 'callnumber',
916 type => 'varchar(100)',
926 field => 'booksellerid',
941 extra => 'auto_increment',
944 field => 'listprice',
945 type => 'varchar(10)',
952 field => 'invoiceprice',
953 type => 'varchar(10)',
962 field => 'borrowernumber',
964 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
970 field => 'itemnumber',
972 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
979 { field => 'firstname',
983 { field => 'initials',
987 { field => 'B_email',
990 after => 'B_zipcode',
993 field => 'streetnumber', # street number (hidden if streettable table is empty)
999 field => 'streettype', # street table, list builded from a system table
1002 after => 'streetnumber',
1009 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1015 field => 'B_streettype', # street table, list builded from a system table
1018 after => 'B_streetnumber',
1021 field => 'phonepro',
1027 field => 'address2', # complement address
1033 field => 'emailpro',
1039 field => 'contactfirstname', # contact's firstname
1042 after => 'contactname',
1045 field => 'contacttitle', # contact's title
1048 after => 'contactfirstname',
1052 deletedborrowers => [
1053 { field => 'firstname',
1057 { field => 'initials',
1061 { field => 'B_email',
1064 after => 'B_zipcode',
1067 field => 'streetnumber', # street number (hidden if streettable table is empty)
1070 after => 'initials',
1073 field => 'streettype', # street table, list builded from a system table
1076 after => 'streetnumber',
1083 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1089 field => 'B_streettype', # street table, list builded from a system table
1092 after => 'B_streetnumber',
1095 field => 'phonepro',
1101 field => 'address2', # complement address
1107 field => 'emailpro',
1113 field => 'contactfirstname', # contact's firstname
1116 after => 'contactname',
1119 field => 'contacttitle', # contact's title
1122 after => 'contactfirstname',
1128 field => 'branchip',
1129 type => 'varchar(15)',
1136 field => 'branchprinter',
1137 type => 'varchar(100)',
1146 field => 'category_type',
1156 field => 'waitingdate',
1168 # { indexname => 'index detail'
1172 { indexname => 'shelfnumber',
1173 content => 'shelfnumber',
1175 { indexname => 'itemnumber',
1176 content => 'itemnumber',
1180 { indexname => 'biblionumber',
1181 content => 'biblionumber',
1185 { indexname => 'homebranch',
1186 content => 'homebranch',
1188 { indexname => 'holdingbranch',
1189 content => 'holdingbranch',
1193 { indexname => 'PRIMARY',
1199 { indexname => 'booksellerid',
1200 content => 'booksellerid',
1204 { indexname => 'basketno',
1205 content => 'basketno',
1208 aqorderbreakdown => [
1209 { indexname => 'ordernumber',
1210 content => 'ordernumber',
1212 { indexname => 'bookfundid',
1213 content => 'bookfundid',
1217 { indexname => 'PRIMARY',
1218 content => 'currency',
1224 my %foreign_keys = (
1226 # { key => 'the key in table' (must be indexed)
1227 # foreigntable => 'the foreigntable name', # (the parent)
1228 # foreignkey => 'the foreign key column(s)' # (in the parent)
1229 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1230 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1234 { key => 'shelfnumber',
1235 foreigntable => 'bookshelf',
1236 foreignkey => 'shelfnumber',
1237 onUpdate => 'CASCADE',
1238 onDelete => 'CASCADE',
1240 { key => 'itemnumber',
1241 foreigntable => 'items',
1242 foreignkey => 'itemnumber',
1243 onUpdate => 'CASCADE',
1244 onDelete => 'CASCADE',
1247 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1248 # easily deleted, but branches/itemtype not too easy to empty...
1250 { key => 'biblionumber',
1251 foreigntable => 'biblio',
1252 foreignkey => 'biblionumber',
1253 onUpdate => 'CASCADE',
1254 onDelete => 'CASCADE',
1256 { key => 'itemtype',
1257 foreigntable => 'itemtypes',
1258 foreignkey => 'itemtype',
1259 onUpdate => 'CASCADE',
1260 onDelete => 'RESTRICT',
1264 { key => 'biblioitemnumber',
1265 foreigntable => 'biblioitems',
1266 foreignkey => 'biblioitemnumber',
1267 onUpdate => 'CASCADE',
1268 onDelete => 'CASCADE',
1270 { key => 'homebranch',
1271 foreigntable => 'branches',
1272 foreignkey => 'branchcode',
1273 onUpdate => 'CASCADE',
1274 onDelete => 'RESTRICT',
1276 { key => 'holdingbranch',
1277 foreigntable => 'branches',
1278 foreignkey => 'branchcode',
1279 onUpdate => 'CASCADE',
1280 onDelete => 'RESTRICT',
1283 additionalauthors => [
1284 { key => 'biblionumber',
1285 foreigntable => 'biblio',
1286 foreignkey => 'biblionumber',
1287 onUpdate => 'CASCADE',
1288 onDelete => 'CASCADE',
1292 { key => 'biblionumber',
1293 foreigntable => 'biblio',
1294 foreignkey => 'biblionumber',
1295 onUpdate => 'CASCADE',
1296 onDelete => 'CASCADE',
1300 { key => 'booksellerid',
1301 foreigntable => 'aqbooksellers',
1303 onUpdate => 'CASCADE',
1304 onDelete => 'RESTRICT',
1308 { key => 'basketno',
1309 foreigntable => 'aqbasket',
1310 foreignkey => 'basketno',
1311 onUpdate => 'CASCADE',
1312 onDelete => 'CASCADE',
1314 { key => 'biblionumber',
1315 foreigntable => 'biblio',
1316 foreignkey => 'biblionumber',
1317 onUpdate => 'SET NULL',
1318 onDelete => 'SET NULL',
1322 { key => 'listprice',
1323 foreigntable => 'currency',
1324 foreignkey => 'currency',
1325 onUpdate => 'CASCADE',
1326 onDelete => 'CASCADE',
1328 { key => 'invoiceprice',
1329 foreigntable => 'currency',
1330 foreignkey => 'currency',
1331 onUpdate => 'CASCADE',
1332 onDelete => 'CASCADE',
1335 aqorderbreakdown => [
1336 { key => 'ordernumber',
1337 foreigntable => 'aqorders',
1338 foreignkey => 'ordernumber',
1339 onUpdate => 'CASCADE',
1340 onDelete => 'CASCADE',
1342 { key => 'bookfundid',
1343 foreigntable => 'aqbookfund',
1344 foreignkey => 'bookfundid',
1345 onUpdate => 'CASCADE',
1346 onDelete => 'CASCADE',
1349 branchtransfers => [
1350 { key => 'frombranch',
1351 foreigntable => 'branches',
1352 foreignkey => 'branchcode',
1353 onUpdate => 'CASCADE',
1354 onDelete => 'CASCADE',
1356 { key => 'tobranch',
1357 foreigntable => 'branches',
1358 foreignkey => 'branchcode',
1359 onUpdate => 'CASCADE',
1360 onDelete => 'CASCADE',
1362 { key => 'itemnumber',
1363 foreigntable => 'items',
1364 foreignkey => 'itemnumber',
1365 onUpdate => 'CASCADE',
1366 onDelete => 'CASCADE',
1370 { key => 'categorycode',
1371 foreigntable => 'categories',
1372 foreignkey => 'categorycode',
1373 onUpdate => 'CASCADE',
1374 onDelete => 'CASCADE',
1376 { key => 'itemtype',
1377 foreigntable => 'itemtypes',
1378 foreignkey => 'itemtype',
1379 onUpdate => 'CASCADE',
1380 onDelete => 'CASCADE',
1383 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1385 { key => 'borrowernumber',
1386 foreigntable => 'borrowers',
1387 foreignkey => 'borrowernumber',
1388 onUpdate => 'SET NULL',
1389 onDelete => 'SET NULL',
1391 { key => 'itemnumber',
1392 foreigntable => 'items',
1393 foreignkey => 'itemnumber',
1394 onUpdate => 'SET NULL',
1395 onDelete => 'SET NULL',
1399 { key => 'borrowernumber',
1400 foreigntable => 'borrowers',
1401 foreignkey => 'borrowernumber',
1402 onUpdate => 'CASCADE',
1403 onDelete => 'CASCADE',
1405 { key => 'biblionumber',
1406 foreigntable => 'biblio',
1407 foreignkey => 'biblionumber',
1408 onUpdate => 'CASCADE',
1409 onDelete => 'CASCADE',
1411 { key => 'itemnumber',
1412 foreigntable => 'items',
1413 foreignkey => 'itemnumber',
1414 onUpdate => 'CASCADE',
1415 onDelete => 'CASCADE',
1417 { key => 'branchcode',
1418 foreigntable => 'branches',
1419 foreignkey => 'branchcode',
1420 onUpdate => 'CASCADE',
1421 onDelete => 'CASCADE',
1424 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1425 # but prevent deleting a branch as soon as it has 1 borrower !
1426 { key => 'categorycode',
1427 foreigntable => 'categories',
1428 foreignkey => 'categorycode',
1429 onUpdate => 'RESTRICT',
1430 onDelete => 'RESTRICT',
1432 { key => 'branchcode',
1433 foreigntable => 'branches',
1434 foreignkey => 'branchcode',
1435 onUpdate => 'RESTRICT',
1436 onDelete => 'RESTRICT',
1439 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1440 # but prevent deleting a branch as soon as it has 1 borrower !
1441 { key => 'categorycode',
1442 foreigntable => 'categories',
1443 foreignkey => 'categorycode',
1444 onUpdate => 'RESTRICT',
1445 onDelete => 'RESTRICT',
1447 { key => 'branchcode',
1448 foreigntable => 'branches',
1449 foreignkey => 'branchcode',
1450 onUpdate => 'RESTRICT',
1451 onDelete => 'RESTRICT',
1455 { key => 'borrowernumber',
1456 foreigntable => 'borrowers',
1457 foreignkey => 'borrowernumber',
1458 onUpdate => 'CASCADE',
1459 onDelete => 'CASCADE',
1461 { key => 'itemnumber',
1462 foreigntable => 'items',
1463 foreignkey => 'itemnumber',
1464 onUpdate => 'SET NULL',
1465 onDelete => 'SET NULL',
1468 auth_tag_structure => [
1469 { key => 'authtypecode',
1470 foreigntable => 'auth_types',
1471 foreignkey => 'authtypecode',
1472 onUpdate => 'CASCADE',
1473 onDelete => 'CASCADE',
1476 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1481 my %column_change = (
1485 from => 'emailaddress',
1490 from => 'streetaddress',
1492 after => 'initials',
1495 from => 'faxnumber',
1500 from => 'textmessaging',
1506 to => 'contactnote',
1507 after => 'opacnote',
1510 from => 'physstreet',
1515 from => 'streetcity',
1517 after => 'B_address',
1530 from => 'homezipcode',
1537 after => 'B_zipcode',
1542 after => 'dateenrolled',
1545 from => 'guarantor',
1546 to => 'guarantorid',
1547 after => 'contactname',
1550 from => 'textmessaging',
1556 to => 'contactnotes',
1557 after => 'opacnotes',
1560 from => 'altrelationship',
1561 to => 'relationship',
1562 after => 'borrowernotes',
1566 deletedborrowers => [
1568 from => 'emailaddress',
1573 from => 'streetaddress',
1575 after => 'initials',
1578 from => 'faxnumber',
1583 from => 'textmessaging',
1589 to => 'contactnote',
1590 after => 'opacnote',
1593 from => 'physstreet',
1598 from => 'streetcity',
1600 after => 'B_address',
1613 from => 'homezipcode',
1620 after => 'B_zipcode',
1625 after => 'dateenrolled',
1628 from => 'guarantor',
1629 to => 'guarantorid',
1630 after => 'contactname',
1633 from => 'textmessaging',
1639 to => 'contactnotes',
1640 after => 'opacnotes',
1643 from => 'altrelationship',
1644 to => 'relationship',
1645 after => 'borrowernotes',
1651 foreach my $table (keys %column_change) {
1652 $sth = $dbh->prepare("show columns from $table");
1655 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1657 $types{$column}->{type} ="$type";
1658 $types{$column}->{null} = "$null";
1659 $types{$column}->{key} = "$key";
1660 $types{$column}->{default} = "$default";
1661 $types{$column}->{extra} = "$extra";
1663 my $tablerows = $column_change{$table};
1664 foreach my $row ( @$tablerows ) {
1665 if ($types{$row->{from}}->{type}) {
1666 print "altering $table $row->{from} to $row->{to}\n";
1667 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1668 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1670 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1671 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1672 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1673 "$types{$row->{from}}->{extra} after $row->{after} ";
1680 #-------------------
1685 # Get version of MySQL database engine.
1686 my $mysqlversion = `mysqld --version`;
1687 $mysqlversion =~ /Ver (\S*) /;
1689 if ( $mysqlversion ge '3.23' ) {
1690 print "Could convert to MyISAM database tables...\n" unless $silent;
1693 #---------------------------------
1696 # Collect all tables into a list
1697 $sth = $dbh->prepare("show tables");
1699 while ( my ($table) = $sth->fetchrow ) {
1700 $existingtables{$table} = 1;
1703 # Now add any missing tables
1704 foreach $table ( keys %requiretables ) {
1705 unless ( $existingtables{$table} ) {
1706 print "Adding $table table...\n" unless $silent;
1707 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1710 print "Error : $sth->errstr \n";
1716 # now drop useless tables
1717 foreach $table ( keys %dropable_table ) {
1718 if ( $existingtables{$table} ) {
1719 print "Dropping unused table $table\n" if $debug and not $silent;
1720 $dbh->do("drop table $table");
1722 print "Error : $dbh->errstr \n";
1727 #---------------------------------
1730 foreach $table ( keys %requirefields ) {
1731 print "Check table $table\n" if $debug and not $silent;
1732 $sth = $dbh->prepare("show columns from $table");
1735 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1737 $types{$column} = $type;
1739 foreach $column ( keys %{ $requirefields{$table} } ) {
1740 print " Check column $column [$types{$column}]\n"
1741 if $debug and not $silent;
1742 if ( !$types{$column} ) {
1744 # column doesn't exist
1745 print "Adding $column field to $table table...\n" unless $silent;
1746 $query = "alter table $table
1747 add column $column " . $requirefields{$table}->{$column};
1748 print "Execute: $query\n" if $debug;
1749 my $sti = $dbh->prepare($query);
1752 print "**Error : $sti->errstr \n";
1759 foreach $table ( keys %fielddefinitions ) {
1760 print "Check table $table\n" if $debug;
1761 $sth = $dbh->prepare("show columns from $table");
1764 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1766 $definitions->{$column}->{type} = $type;
1767 $definitions->{$column}->{null} = $null;
1768 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1769 $definitions->{$column}->{key} = $key;
1770 $definitions->{$column}->{default} = $default;
1771 $definitions->{$column}->{extra} = $extra;
1773 my $fieldrow = $fielddefinitions{$table};
1774 foreach my $row (@$fieldrow) {
1775 my $field = $row->{field};
1776 my $type = $row->{type};
1777 my $null = $row->{null};
1778 # $null = 'YES' if $row->{null} eq 'NULL';
1779 my $key = $row->{key};
1780 my $default = $row->{default};
1781 my $null = $row->{null};
1782 # $default="''" unless $default;
1783 my $extra = $row->{extra};
1784 my $def = $definitions->{$field};
1785 my $after = ($row->{after}?" after ".$row->{after}:"");
1787 unless ( $type eq $def->{type}
1788 && $null eq $def->{null}
1789 && $key eq $def->{key}
1790 && $extra eq $def->{extra} )
1792 if ( $null eq '' ) {
1795 if ( $key eq 'PRI' ) {
1796 $key = 'PRIMARY KEY';
1798 unless ( $extra eq 'auto_increment' ) {
1802 # if it's a new column use "add", if it's an old one, use "change".
1804 if ($definitions->{$field}->{type}) {
1805 $action="change $field"
1809 # if it's a primary key, drop the previous pk, before altering the table
1811 my $request = "alter table $table ";
1812 $request.=" drop primary key" if $key eq 'PRIMARY KEY';
1813 $request.= " $action $field $type $null $key $extra ";
1814 $request.= "default ".$dbh->quote($default) if $default;
1815 $request.= " $after";
1816 # print "REQ : $request";
1818 print " alter or create $field in $table\n" unless $silent;
1823 # Populate tables with required data
1825 # synch table and deletedtable.
1826 foreach my $table ( ( 'borrowers', 'items', 'biblio', 'biblioitems' ) ) {
1827 my %deletedborrowers;
1828 print "synch'ing $table\n";
1829 $sth = $dbh->prepare("show columns from deleted$table");
1831 while ( my ( $column, $type, $null, $key, $default, $extra ) =
1834 $deletedborrowers{$column} = 1;
1836 $sth = $dbh->prepare("show columns from $table");
1839 while ( my ( $column, $type, $null, $key, $default, $extra ) =
1842 unless ( $deletedborrowers{$column} ) {
1843 my $newcol = "alter table deleted$table add $column $type";
1844 if ( $null eq 'YES' ) {
1845 $newcol .= " NULL ";
1848 $newcol .= " NOT NULL ";
1850 $newcol .= "default $default" if $default;
1851 $newcol .= " after $previous" if $previous;
1852 $previous = $column;
1853 print "creating column $column\n";
1859 foreach my $table ( keys %tabledata ) {
1860 print "Checking for data required in table $table...\n" unless $silent;
1861 my $tablerows = $tabledata{$table};
1862 foreach my $row (@$tablerows) {
1863 my $uniquefieldrequired = $row->{uniquefieldrequired};
1864 my $uniquevalue = $row->{$uniquefieldrequired};
1865 my $forceupdate = $row->{forceupdate};
1868 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1870 $sth->execute($uniquevalue);
1872 foreach my $field (keys %$forceupdate) {
1873 if ($forceupdate->{$field}) {
1874 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1875 $sth->execute($row->{$field}, $uniquevalue);
1879 print "Adding row to $table: " unless $silent;
1883 foreach my $field ( keys %$row ) {
1884 next if $field eq 'uniquefieldrequired';
1885 next if $field eq 'forceupdate';
1886 my $value = $row->{$field};
1887 push @values, $value;
1888 print " $field => $value" unless $silent;
1889 $fieldlist .= "$field,";
1890 $placeholders .= "?,";
1892 print "\n" unless $silent;
1893 $fieldlist =~ s/,$//;
1894 $placeholders =~ s/,$//;
1897 "insert into $table ($fieldlist) values ($placeholders)");
1898 $sth->execute(@values);
1904 # check indexes and create them when needed
1906 print "Checking for index required...\n" unless $silent;
1907 foreach my $table ( keys %indexes ) {
1909 # read all indexes from $table
1911 $sth = $dbh->prepare("show index from $table");
1913 my %existingindexes;
1914 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
1915 $existingindexes{$key_name} = 1;
1917 # read indexes to check
1918 my $tablerows = $indexes{$table};
1919 foreach my $row (@$tablerows) {
1920 my $key_name=$row->{indexname};
1921 if ($existingindexes{$key_name} eq 1) {
1922 # print "$key_name existing";
1924 print "\tCreating index $key_name in $table\n";
1926 if ($row->{indexname} eq 'PRIMARY') {
1927 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
1929 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
1932 print "Error $sql : $dbh->err \n" if $dbh->err;
1938 # check foreign keys and create them when needed
1940 print "Checking for foreign keys required...\n" unless $silent;
1941 foreach my $table ( keys %foreign_keys ) {
1943 # read all indexes from $table
1945 $sth = $dbh->prepare("show table status like '$table'");
1947 my $stat = $sth->fetchrow_hashref;
1948 # read indexes to check
1949 my $tablerows = $foreign_keys{$table};
1950 foreach my $row (@$tablerows) {
1951 my $foreign_table=$row->{foreigntable};
1952 if ($stat->{'Comment'} =~/$foreign_table/) {
1953 # print "$foreign_table existing\n";
1955 print "\tCreating foreign key $foreign_table in $table\n";
1956 # first, drop any orphan value in child table
1957 if ($row->{onDelete} ne "RESTRICT") {
1958 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
1960 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
1962 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
1963 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
1964 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
1967 print "====================
1968 An error occured during :
1970 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).
1971 You can find those values with select
1972 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
1973 ====================\n
1984 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
1987 # 1st, get how many biblio we will have to do...
1988 $sth = $dbh->prepare('select count(*) from marc_biblio');
1990 my ($totaltodo) = $sth->fetchrow;
1992 $sth = $dbh->prepare("show columns from biblio");
1995 my $bibliofwexist=0;
1996 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1997 $bibliofwexist=1 if $column eq 'frameworkcode';
1999 unless ($bibliofwexist) {
2000 print "moving biblioframework to biblio table\n";
2001 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2002 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2004 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2006 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2007 $sth_update->execute($frameworkcode,$biblionumber);
2009 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2015 # moving MARC data from marc_subfield_table to biblioitems.marc
2017 $sth = $dbh->prepare("show columns from biblioitems");
2021 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2022 $marcdone=1 if ($type eq 'blob' && $column eq 'marc') ;
2024 unless ($marcdone) {
2025 print "moving MARC record to biblioitems table\n";
2026 # changing marc field type
2027 $dbh->do('ALTER TABLE `biblioitems` CHANGE `marc` `marc` BLOB NULL DEFAULT NULL ');
2028 # adding marc xml, just for convenience
2029 $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ');
2030 # moving data from marc_subfield_value to biblio
2031 $sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
2033 my $sth_update = $dbh->prepare('update biblioitems set marc=?, marcxml=? where biblionumber=?');
2035 while (my ($bibid,$biblionumber) = $sth->fetchrow) {
2036 my $record = MARCgetbiblio($dbh,$bibid);
2037 #Force UTF-8 in record leader
2038 $record->encoding('UTF-8');
2039 print $record->as_formatted if ($biblionumber==3902);
2040 $sth_update->execute($record->as_usmarc(),$record->as_xml_record(),$biblionumber);
2042 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2048 # at last, remove useless fields
2049 foreach $table ( keys %uselessfields ) {
2050 my @fields = split /,/, $uselessfields{$table};
2053 foreach my $fieldtodrop (@fields) {
2054 $fieldtodrop =~ s/\t//g;
2055 $fieldtodrop =~ s/\n//g;
2057 $sth = $dbh->prepare("show columns from $table");
2059 while ( my ( $column, $type, $null, $key, $default, $extra ) =
2062 $exists = 1 if ( $column eq $fieldtodrop );
2065 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2066 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2073 # MOVE all tables TO UTF-8 and innoDB
2074 $sth = $dbh->prepare("show table status");
2076 while ( my $table = $sth->fetchrow_hashref ) {
2077 # if ($table->{Engine} ne 'InnoDB') {
2078 # $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
2079 # print "moving $table->{Name} to InnoDB\n";
2081 unless ($table->{Collation} =~ /^utf8/) {
2082 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
2083 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
2084 # 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 !
2085 print "moving $table->{Name} to utf8\n";
2093 # those 2 subs are a copy of Biblio.pm, version 2.2.4
2094 # they are useful only once, for moving from 2.2 to 3.0
2095 # the MARCgetbiblio & MARCgetitem subs in Biblio.pm
2096 # are still here, but uses other tables
2097 # (the ones that are filled by updatedatabase !)
2102 # Returns MARC::Record of the biblio passed in parameter.
2103 my ( $dbh, $bibid ) = @_;
2104 my $record = MARC::Record->new();
2109 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
2110 from marc_subfield_table
2111 where bibid=? order by tag,tagorder,subfieldorder
2116 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
2117 $sth->execute($bibid);
2118 my $prevtagorder = 1;
2119 my $prevtag = 'XXX';
2121 my $field; # for >=10 tags
2122 my $prevvalue; # for <10 tags
2123 while ( my $row = $sth->fetchrow_hashref ) {
2125 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
2126 $sth2->execute( $row->{'valuebloblink'} );
2127 my $row2 = $sth2->fetchrow_hashref;
2129 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
2131 if ( $row->{tagorder} ne $prevtagorder || $row->{tag} ne $prevtag ) {
2132 $previndicator .= " ";
2133 if ( $prevtag < 10 ) {
2134 if ($prevtag ne '000') {
2135 $record->add_fields( ( sprintf "%03s", $prevtag ), $prevvalue ) unless $prevtag eq "XXX"; # ignore the 1st loop
2137 $record->leader(sprintf("%24s",$prevvalue));
2141 $record->add_fields($field) unless $prevtag eq "XXX";
2144 $prevtagorder = $row->{tagorder};
2145 $prevtag = $row->{tag};
2146 $previndicator = $row->{tag_indicator};
2147 if ( $row->{tag} < 10 ) {
2148 $prevvalue = $row->{subfieldvalue};
2151 $field = MARC::Field->new(
2152 ( sprintf "%03s", $prevtag ),
2153 substr( $row->{tag_indicator} . ' ', 0, 1 ),
2154 substr( $row->{tag_indicator} . ' ', 1, 1 ),
2155 $row->{'subfieldcode'},
2156 $row->{'subfieldvalue'}
2161 if ( $row->{tag} < 10 ) {
2162 $record->add_fields( ( sprintf "%03s", $row->{tag} ),
2163 $row->{'subfieldvalue'} );
2166 $field->add_subfields( $row->{'subfieldcode'},
2167 $row->{'subfieldvalue'} );
2169 $prevtag = $row->{tag};
2170 $previndicator = $row->{tag_indicator};
2174 # the last has not been included inside the loop... do it now !
2175 if ( $prevtag ne "XXX" )
2176 { # check that we have found something. Otherwise, prevtag is still XXX and we
2177 # must return an empty record, not make MARC::Record fail because we try to
2178 # create a record with XXX as field :-(
2179 if ( $prevtag < 10 ) {
2180 $record->add_fields( $prevtag, $prevvalue );
2184 # my $field = MARC::Field->new( $prevtag, "", "", %subfieldlist);
2185 $record->add_fields($field);
2193 # Returns MARC::Record of the biblio passed in parameter.
2194 my ( $dbh, $bibid, $itemnumber ) = @_;
2195 my $record = MARC::Record->new();
2197 # search MARC tagorder
2200 "select tagorder from marc_subfield_table,marc_subfield_structure where marc_subfield_table.tag=marc_subfield_structure.tagfield and marc_subfield_table.subfieldcode=marc_subfield_structure.tagsubfield and bibid=? and kohafield='items.itemnumber' and subfieldvalue=?"
2202 $sth2->execute( $bibid, $itemnumber );
2203 my ($tagorder) = $sth2->fetchrow_array();
2205 #---- TODO : the leader is missing
2208 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
2209 from marc_subfield_table
2210 where bibid=? and tagorder=? order by subfieldcode,subfieldorder
2215 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
2216 $sth->execute( $bibid, $tagorder );
2217 while ( my $row = $sth->fetchrow_hashref ) {
2218 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
2219 $sth2->execute( $row->{'valuebloblink'} );
2220 my $row2 = $sth2->fetchrow_hashref;
2222 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
2224 if ( $record->field( $row->{'tag'} ) ) {
2227 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
2228 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
2229 if ( length( $row->{'tag'} ) < 3 ) {
2230 $row->{'tag'} = "0" . $row->{'tag'};
2232 $field = $record->field( $row->{'tag'} );
2235 $field->add_subfields( $row->{'subfieldcode'},
2236 $row->{'subfieldvalue'} );
2237 $record->delete_field($field);
2238 $record->add_fields($field);
2242 if ( length( $row->{'tag'} ) < 3 ) {
2243 $row->{'tag'} = "0" . $row->{'tag'};
2246 MARC::Field->new( $row->{'tag'}, " ", " ",
2247 $row->{'subfieldcode'} => $row->{'subfieldvalue'} );
2248 $record->add_fields($temp);
2259 # Revision 1.156 2006/07/20 04:36:01 bob_lyon
2260 # Merging back in some katipo changes to serials
2262 # Revision 1.155 2006/07/17 12:51:48 toins
2263 # auto_increment id in aqbooksellers
2265 # Revision 1.153 2006/07/04 14:36:52 toins
2266 # Head & rel_2_2 merged
2268 # Revision 1.152 2006/06/27 09:26:37 btoumi
2269 # modify (initials,phone ) fields property in borrowers and deletedborrowers table
2271 # Revision 1.151 2006/06/22 10:33:14 btoumi
2272 # sorry i forget deletedborrowers table
2273 # modify firstname field from deletedborrowers table
2275 # Revision 1.149 2006/06/20 22:35:47 rangi
2276 # Code to allow the associated borrowers to work
2278 # Revision 1.148 2006/06/17 22:12:01 rangi
2279 # Adding id field to reviews table
2281 # Revision 1.147 2006/06/17 03:36:41 rangi
2282 # Table definition for the reviews table
2284 # Revision 1.146 2006/06/17 03:29:41 rangi
2285 # Variable to allow librarians to switch reviews on or off
2287 # Revision 1.145 2006/06/16 09:45:02 btoumi
2288 # updatedatabase.pl: add change of borrowers table to deletedborrowers table
2289 # deletemem.pl: delete use of warn function
2291 # Revision 1.144 2006/06/08 15:36:31 alaurin
2292 # Add a new system preference 'AutomaticItemReturn' :
2294 # if this prefence is switched on: the document returned in another library than homebranch, the system automaticly transfer the document to his homebranch (with notification for librarian in returns.tmpl) .
2296 # switch off : the document stay in the holdingbranch ...
2299 # - comment C4::acquisition (not using in request.pl).
2300 # - correcting date in request.pl
2301 # -add the new call of function getbranches in request.pl
2303 # Revision 1.143 2006/06/07 02:02:47 bob_lyon
2304 # merging katipo changes...
2306 # adding new preference IssuingInProcess
2308 # Revision 1.142 2006/06/06 23:42:46 bob_lyon
2309 # Merging Katipo changes...
2311 # Adding new system pref where one can still retrieve a correct reading
2312 # record history if one has moved older data from issues to oldissues table
2313 # to speed up issues speed
2315 # Revision 1.141 2006/06/01 03:18:11 rangi
2316 # Adding a new column to the statistics table
2318 # Revision 1.140 2006/05/22 22:40:45 rangi
2319 # Adding new systempreference allowing for the library to add borrowers to institutions (rest homes, parishes, schools, classes etc).
2321 # Revision 1.139 2006/05/19 19:31:29 tgarip1957
2322 # Added new fields to auth_header and auth_subfield_table to allow ZEBRA use of authorities and new MARC framework like structure.
2323 # Authority tables are modified to be compatible with new MARC frameworks. This change is part of Authority Linking & Zebra authorities. Requires change in Mysql database. It will break head unless all changes regarding this is implemented. This warning will take place on all commits regarding this
2325 # Revision 1.138 2006/05/19 16:51:44 alaurin
2326 # update database for :
2327 # - new feature ip and printer management
2328 # adding two fields in branches table (branchip,branchprinter)
2330 # - waiting date : adding one field in reserves table(waiting date) to calculate the Maximum delay to pick up a reserved document when it's available
2332 # new system preference :
2333 # - ReservesMaxPickUpDelay : Maximum delay to pick up a reserved document
2334 # TransfersMaxDaysWarning : Max delay before considering the transfer as potentialy a problem
2336 # Revision 1.137 2006/04/18 09:36:36 plg
2337 # bug fixed: typo fixed in labels and labels_conf tables creation query.
2339 # Revision 1.136 2006/04/17 21:55:33 sushi
2340 # Added 'labels' and 'labels_conf' tables, for spine lable tool.
2342 # Revision 1.135 2006/04/15 02:37:03 tgarip1957
2343 # Marc record should be set to UTF-8 in leader.Force it.
2344 # XML should be with<record> wrappers
2346 # Revision 1.134 2006/04/14 09:37:29 tipaul
2347 # improvements from SAN Ouest Provence :
2348 # * introducing a category_type into categories. It can be A (adult), C (children), P (Professionnal), I (institution/organisation).
2349 # * each category_type has it's own forms to create members.
2350 # * the borrowers table has been heavily modified (many fields changed), to get something more logic & readable
2351 # * reintroducing guarantor/guanrantee system that is now independant from hardcoded C/A for categories
2352 # * updating templates to fit template rules
2354 # (see mail feb, 17 on koha-devel "new features for borrowers" for more details)
2356 # Revision 1.133 2006/04/13 08:36:42 plg
2357 # new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on
2358 # the system preference prefered date format.
2360 # improvement: book fund list and budget list screen redesigned. Filters on
2361 # each field. Columns are not sortable yet. Using DHTML Calendar to fill date
2362 # fields instead of manual filling. Pagination system. From the book fund
2363 # list, you can reach the budget list, filtered on a book fund, or not. A
2364 # budget can be added only from book fund list screen.
2366 # bug fixed: branchcode was missing in table aqbudget.
2368 # bug fixed: when setting a branchcode to a book fund, all associated budgets
2369 # move to this branchcode.
2371 # modification: when adding/modifying budget/fund, MySQL specific "REPLACE..."
2372 # statements replaced by standard SQL compliant statement.
2374 # bug fixed: when adding/modifying a budget, if the book fund is associated to
2375 # a branch, the branch selection is disabled and set to the book fund branch.
2377 # Revision 1.132 2006/04/06 12:37:05 hdl
2378 # Bugfixing : aqbookfund needed a field.
2380 # Revision 1.131 2006/03/03 17:02:22 tipaul
2381 # commit for holidays and news management.
2382 # (some forgotten files)
2384 # Revision 1.130 2006/03/03 16:35:21 tipaul
2385 # commit for holidays and news management.
2387 # Contrib from Tümer Garip (from Turkey) :
2389 # in /tools/ the holiday.pl script let you define holidays (days where the library is closed), branch by branch. You can define 3 types of holidays :
2390 # - single day : only this day is closed
2391 # - repet weekly (like "sunday") : the day is holiday every week
2392 # - repet yearly (like "July, 4") : this day is closed every year.
2394 # You can also put exception :
2395 # - sunday is holiday, but "2006 March, 5th" the library will be open
2397 # The holidays are used for return date calculation : the return date is set to the next date where the library is open. A systempreference (useDaysMode) set ON (Calendar) or OFF (Normal) the calendar calculation.
2399 # Revision 1.129 2006/02/27 18:19:33 hdl
2400 # New table used in overduerules.pl tools page.
2402 # Revision 1.128 2006/01/25 15:16:06 tipaul
2404 # * removing useless tables
2405 # * adding useful indexes
2406 # * altering some columns definitions
2407 # * The goal being to have updater working fine for foreign keys.
2409 # For me it's done, let me know if it works for you. You can see an updated schema of the DB (with constraints) on the wiki
2411 # Revision 1.127 2006/01/24 17:57:17 tipaul
2412 # DB improvements : adding foreign keys on some tables. partial stuff done.
2414 # Revision 1.126 2006/01/06 16:39:42 tipaul
2415 # synch'ing head and rel_2_2 (from 2.2.5, including npl templates)
2416 # Seems not to break too many things, but i'm probably wrong here.
2417 # at least, new features/bugfixes from 2.2.5 are here (tested on some features on my head local copy)
2419 # - removing useless directories (koha-html and koha-plucene)
2421 # Revision 1.125 2006/01/04 15:54:55 tipaul
2422 # utf8 is a : go for beta test in HEAD.
2423 # some explanations :
2424 # - updater/updatedatabase => will transform all tables in innoDB (not related to utf8, just to warn you) AND collate them in utf8 / utf8_general_ci. The SQL command is : ALTER TABLE tablename DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci.
2425 # - *-top.inc will show the pages in utf8
2426 # - THE HARD THING : for me, mysql-client and mysql-server were set up to communicate in iso8859-1, whatever the mysql collation ! Thus, pages were improperly shown, as datas were transmitted in iso8859-1 format ! After a full day of investigation, someone on usenet pointed "set NAMES 'utf8'" to explain that I wanted utf8. I could put this in my.cnf, but if I do that, ALL databases will "speak" in utf8, that's not what we want. Thus, I added a line in Context.pm : everytime a DB handle is opened, the communication is set to utf8.
2427 # - using marcxml field and no more the iso2709 raw marc biblioitems.marc field.
2429 # Revision 1.124 2005/10/27 12:09:05 tipaul
2430 # new features for serial module :
2431 # - the last 5 issues are now shown, and their status can be changed (but not reverted to "waited", as there can be only one "waited")
2432 # - the library can create a "distribution list". this paper contains a list of borrowers (selected from the borrower list, or manually entered), and print it for a given issue. once printed, the sheet can be put on the issue and distributed to every reader on the list (one by one).
2434 # Revision 1.123 2005/10/26 09:13:37 tipaul
2435 # big commit, still breaking things...
2437 # * synch with rel_2_2. Probably the last non manual synch, as rel_2_2 should not be modified deeply.
2438 # * code cleaning (cleaning warnings from perl -w) continued
2440 # Revision 1.122 2005/09/02 14:18:38 tipaul
2441 # new feature : image for itemtypes.
2443 # * run updater/updatedatabase to create imageurl field in itemtypes.
2444 # * go to Koha >> parameters >> itemtypes >> modify (or add) an itemtype. You will see around 20 nice images to choose between (thanks to owen). If you prefer your own image, you also can type a complete url (http://www.myserver.lib/path/to/my/image.gif)
2445 # * go to OPAC, and search something. In the result list, you now have the picture instead of the text itemtype.
2447 # Revision 1.121 2005/08/24 08:49:03 hdl
2448 # Adding a note field in serial table.
2449 # This will allow librarian to mention a note on a peculiar waiting serial number.
2451 # Revision 1.120 2005/08/09 14:10:32 tipaul
2452 # 1st commit to go to zebra.
2453 # don't update your cvs if you want to have a working head...
2455 # this commit contains :
2456 # * updater/updatedatabase : get rid with marc_* tables, but DON'T remove them. As a lot of things uses them, it would not be a good idea for instance to drop them. If you really want to play, you can rename them to test head without them but being still able to reintroduce them...
2457 # * Biblio.pm : modify MARCgetbiblio to find the raw marc record in biblioitems.marc field, not from marc_subfield_table, modify MARCfindframeworkcode to find frameworkcode in biblio.frameworkcode, modify some other subs to use biblio.biblionumber & get rid of bibid.
2458 # * other files : get rid of bibid and use biblionumber instead.
2461 # * does not do anything on zebra yet.
2462 # * if you rename marc_subfield_table, you can't search anymore.
2463 # * you can view a biblio & bibliodetails, go to MARC editor, but NOT save any modif.
2464 # * don't try to add a biblio, it would add data poorly... (don't try to delete either, it may work, but that would be a surprise ;-) )
2466 # IMPORTANT NOTE : you need MARC::XML package (http://search.cpan.org/~esummers/MARC-XML-0.7/lib/MARC/File/XML.pm), that requires a recent version of MARC::Record
2467 # Updatedatabase stores the iso2709 data in biblioitems.marc field & an xml version in biblioitems.marcxml Not sure we will keep it when releasing the stable version, but I think it's a good idea to have something readable in sql, at least for development stage.
2469 # Revision 1.119 2005/08/04 16:07:58 tipaul
2470 # Synch really broke this script...
2472 # Revision 1.118 2005/08/04 16:02:55 tipaul
2473 # oops... error in synch between 2.2 and head
2475 # Revision 1.117 2005/08/04 14:24:39 tipaul
2476 # synch'ing 2.2 and head
2478 # Revision 1.116 2005/08/04 08:55:54 tipaul
2479 # Letters / alert system, continuing...
2481 # * adding a package Letters.pm, that manages Letters & alerts.
2482 # * adding feature : it's now possible to define a "letter" for any subscription created. If a letter is defined, users in OPAC can put an alert on the subscription. When an issue is marked "arrived", all users in the alert will recieve a mail (as defined in the "letter"). This last part (= send the mail) is not yet developped. (Should be done this week)
2483 # * adding feature : it's now possible to "put to an alert" in OPAC, for any serial subscription. The alert is stored in a new table, called alert. An alert can be put only if the librarian has activated them in subscription (and they activate it just by choosing a "letter" to sent to borrowers on new issues)
2484 # * adding feature : librarian can see in borrower detail which alerts they have put, and a user can see in opac-detail which alert they have put too.
2486 # Note that the system should be generic enough to manage any type of alert.
2487 # I plan to extend it soon to virtual shelves : a borrower will be able to put an alert on a virtual shelf, to be warned when something is changed in the virtual shelf (mail being sent once a day by cron, or manually by the shelf owner. Anyway, a mail won't be sent on every change, users would be spammed by Koha ;-) )
2489 # Revision 1.115 2005/08/02 16:15:34 tipaul
2490 # adding 2 fields to letter system :
2491 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
2492 # * title, that will be used as mail subject.
2494 # Revision 1.114 2005/07/28 15:10:13 tipaul
2495 # Introducing new "Letters" system : Letters will be used everytime you want to sent something to someone (through mail or paper). For example, sending a mail for overdues use letter that you can put as parameters. Sending a mail to a borrower when a suggestion is validated uses a letter too.
2496 # the letter table contains 3 fields :
2497 # * code => the code of the letter
2498 # * name => the complete name of the letter
2499 # * content => the complete text. It's a TEXT field type, so has no limits.
2501 # My next goal now is to work on point 2-I "serial issue alert"
2502 # With this feature, in serials, a user can subscribe the "issue alert". For every issue arrived/missing, a mail is sent to all subscribers of this list. The mail warns the user that the issue is arrive or missing. Will be in head.
2503 # (see mail on koha-devel, 2005/04/07)
2505 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
2507 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
2509 # Revision 1.113 2005/07/28 08:38:41 tipaul
2510 # For instance, the return date does not rely on the borrower expiration date. A systempref will be added in Koha, to modify return date calculation schema :
2511 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
2512 # * ReturnBeforeExpiry = no => return date can be after expiry date
2514 # Revision 1.112 2005/07/26 08:19:47 hdl
2515 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
2517 # Revision 1.111 2005/07/25 15:35:38 tipaul
2518 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
2519 # So, the updatedatabase script can highly be cleaned (90% removed).
2520 # Let's play with the new Koha DB structure now ;-)