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.
24 use MARC::File::XML ( BinaryEncoding => 'utf8' );
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
46 my $dbh = C4::Context->dbh;
47 print "connected to your DB. Checking & modifying it\n" unless $silent;
48 $|=1; # flushes output
53 # Tables to add if they don't exist
56 `timestamp` TIMESTAMP NOT NULL ,
57 `user` INT( 11 ) NOT NULL ,
58 `module` TEXT default '',
59 `action` TEXT default '' ,
60 `object` INT(11) NULL ,
61 `info` TEXT default '' ,
62 PRIMARY KEY ( `timestamp` , `user` )
65 module varchar(20) NOT NULL default '',
66 code varchar(20) NOT NULL default '',
67 name varchar(100) NOT NULL default '',
68 title varchar(200) NOT NULL default '',
70 PRIMARY KEY (module,code)
73 alertid int(11) NOT NULL auto_increment,
74 borrowernumber int(11) NOT NULL default '0',
75 type varchar(10) NOT NULL default '',
76 externalid varchar(20) NOT NULL default '',
77 PRIMARY KEY (alertid),
78 KEY borrowernumber (borrowernumber),
79 KEY type (type,externalid)
82 `idnew` int(10) unsigned NOT NULL auto_increment,
83 `title` varchar(250) NOT NULL default '',
85 `lang` varchar(4) NOT NULL default '',
86 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
89 repeatable_holidays => "(
90 `id` int(11) NOT NULL auto_increment,
91 `branchcode` varchar(4) NOT NULL default '',
92 `weekday` smallint(6) default NULL,
93 `day` smallint(6) default NULL,
94 `month` smallint(6) default NULL,
95 `title` varchar(50) NOT NULL default '',
96 `description` text NOT NULL,
99 special_holidays => "(
100 `id` int(11) NOT NULL auto_increment,
101 `branchcode` varchar(4) NOT NULL default '',
102 `day` smallint(6) NOT NULL default '0',
103 `month` smallint(6) NOT NULL default '0',
104 `year` smallint(6) NOT NULL default '0',
105 `isexception` smallint(1) NOT NULL default '1',
106 `title` varchar(50) NOT NULL default '',
107 `description` text NOT NULL,
110 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
111 `categorycode` char(2) NOT NULL default '',
112 `delay1` int(4) default '0',
113 `letter1` varchar(20) default NULL,
114 `debarred1` char(1) default '0',
115 `delay2` int(4) default '0',
116 `debarred2` char(1) default '0',
117 `letter2` varchar(20) default NULL,
118 `delay3` int(4) default '0',
119 `letter3` varchar(20) default NULL,
120 `debarred3` int(1) default '0',
121 PRIMARY KEY (`branchcode`,`categorycode`)
123 cities => "(`cityid` int auto_increment,
124 `city_name` char(100) NOT NULL,
125 `city_zipcode` char(20),
126 PRIMARY KEY (`cityid`)
128 roadtype => "(`roadtypeid` int auto_increment,
129 `road_type` char(100) NOT NULL,
130 PRIMARY KEY (`roadtypeid`)
134 labelid int(11) NOT NULL auto_increment,
135 itemnumber varchar(100) NOT NULL default '',
136 timestamp timestamp(14) NOT NULL,
137 PRIMARY KEY (labelid)
141 id int(4) NOT NULL auto_increment,
142 barcodetype char(100) default '',
143 title tinyint(1) default '0',
144 isbn tinyint(1) default '0',
145 itemtype tinyint(1) default '0',
146 barcode tinyint(1) default '0',
147 dewey tinyint(1) default '0',
148 class tinyint(1) default '0',
149 author tinyint(1) default '0',
150 papertype char(100) default '',
151 startrow int(2) default NULL,
155 reviewid integer NOT NULL auto_increment,
156 borrowernumber integer,
157 biblionumber integer,
160 datereviewed datetime,
161 PRIMARY KEY (reviewid)
163 subscriptionroutinglist=>"(
164 routingid integer NOT NULL auto_increment,
165 borrowernumber integer,
167 subscriptionid integer,
168 PRIMARY KEY (routingid)
172 notify_id int(11) NOT NULL default '0',
173 `borrowernumber` int(11) NOT NULL default '0',
174 `itemnumber` int(11) NOT NULL default '0',
175 `notify_date` date NOT NULL default '0000-00-00',
176 `notify_send_date` date default NULL,
177 `notify_level` int(1) NOT NULL default '0',
178 `method` varchar(20) NOT NULL default ''
182 `charge_id` varchar(5) NOT NULL default '',
183 `description` text NOT NULL,
184 `amount` decimal(28,6) NOT NULL default '0.000000',
185 `min` int(4) NOT NULL default '0',
186 `max` int(4) NOT NULL default '0',
187 `level` int(1) NOT NULL default '0',
188 PRIMARY KEY (`charge_id`)
191 `entry` varchar(255) NOT NULL default '',
192 `weight` bigint(20) NOT NULL default '0',
193 PRIMARY KEY (`entry`)
197 `id` int NOT NULL auto_increment,
198 `biblio_auth_number` int NOT NULL,
199 `operation` char(20) NOT NULL,
200 `server` char(20) NOT NULL ,
202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
206 my %requirefields = (
207 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date NOT NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(12) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''},
208 itemtypes => { 'imageurl' => 'char(200) NULL'},
209 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
210 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
211 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
212 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => 'VARCHAR(45) NOT NULL', 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'},
213 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
214 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
215 statistics => { 'associatedborrower' => 'integer'},
216 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
217 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
218 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
220 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
222 # tablename => { 'field' => 'fieldtype' },
225 # Enter here the table to delete.
226 my @TableToDelete = qw(
233 my %uselessfields = (
234 # tablename => "field1,field2",
235 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
236 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
238 # the other hash contains other actions that can't be done elsewhere. they are done
239 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
241 # The tabledata hash contains data that should be in the tables.
242 # The uniquefieldrequired hash entry is used to determine which (if any) fields
243 # must not exist in the table for this row to be inserted. If the
244 # uniquefieldrequired entry is already in the table, the existing data is not
245 # modified, unless the forceupdate hash entry is also set. Fields in the
246 # anonymous "forceupdate" hash will be forced to be updated to the default
247 # values given in the %tabledata hash.
251 # { uniquefielrequired => 'fieldname', # the primary key in the table
252 # fieldname => fieldvalue,
253 # fieldname2 => fieldvalue2,
256 systempreferences => [
258 uniquefieldrequired => 'variable',
259 variable => 'useDaysMode',
261 forceupdate => { 'explanation' => 1,
263 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
265 options => 'Calendar|Days'
268 uniquefieldrequired => 'variable',
269 variable => 'BorrowersTitles',
270 value => 'Mr|Mrs|Miss|Ms',
271 forceupdate => { 'explanation' => 1,
273 explanation => 'List all Titles for borrowers',
277 uniquefieldrequired => 'variable',
278 variable => 'BorrowerMandatoryField',
279 value => 'cardnumber|surname|address',
280 forceupdate => { 'explanation' => 1,
282 explanation => 'List all mandatory fields for borrowers',
286 uniquefieldrequired => 'variable',
287 variable => 'borrowerRelationship',
288 value => 'father|mother,grand-mother',
289 forceupdate => { 'explanation' => 1,
291 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
295 uniquefieldrequired => 'variable',
296 variable => 'ReservesMaxPickUpDelay',
298 forceupdate => { 'explanation' => 1,
300 explanation => 'Maximum delay to pick up a reserved document',
304 uniquefieldrequired => 'variable',
305 variable => 'TransfersMaxDaysWarning',
307 forceupdate => { 'explanation' => 1,
309 explanation => 'Max delay before considering the transfer has potentialy a problem',
313 uniquefieldrequired => 'variable',
314 variable => 'memberofinstitution',
316 forceupdate => { 'explanation' => 1,
318 explanation => 'Are your patrons members of institutions',
322 uniquefieldrequired => 'variable',
323 variable => 'ReadingHistory',
325 forceupdate => { 'explanation' => 1,
327 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
331 uniquefieldrequired => 'variable',
332 variable => 'IssuingInProcess',
334 forceupdate => { 'explanation' => 1,
336 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
340 uniquefieldrequired => 'variable',
341 variable => 'AutomaticItemReturn',
343 forceupdate => { 'explanation' => 1,
345 explanation => 'This Variable allow or not to return automaticly to his homebranch',
349 uniquefieldrequired => 'variable',
350 variable => 'reviewson',
352 forceupdate => { 'explanation' => 1,
354 explanation => 'Allows patrons to submit reviews from the opac',
358 uniquefieldrequired => 'variable',
359 variable => 'intranet_includes',
361 forceupdate => { 'explanation' => 1,
363 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
367 uniquefieldrequired => 'variable',
368 variable => 'AutoLocation',
370 forceupdate => { 'explanation' => 1,
372 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
376 uniquefieldrequired => 'variable',
377 variable => 'serialsadditems',
383 explanation => 'If set, a new item will be automatically added when receiving an issue',
387 uniquefieldrequired => 'variable',
388 variable => 'expandedSearchOption',
394 explanation => 'search among marc field',
398 uniquefieldrequired => 'variable',
399 variable => 'RequestOnOpac',
401 forceupdate => { 'explanation' => 1,
403 explanation => 'option to allow reserves on opac',
407 uniquefieldrequired => 'variable',
408 variable => 'OpacCloud',
410 forceupdate => { 'explanation' => 1,
412 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
416 uniquefieldrequired => 'variable',
417 variable => 'OpacBrowser',
419 forceupdate => { 'explanation' => 1,
421 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
425 uniquefieldrequired => 'variable',
426 variable => 'OpacTopissue',
428 forceupdate => { 'explanation' => 1,
430 explanation => 'Enable / Disable the top issue link on OPAC',
434 uniquefieldrequired => 'variable',
435 variable => 'OpacAuthorities',
437 forceupdate => { 'explanation' => 1,
439 explanation => 'Enable / Disable the search authority link on OPAC',
443 uniquefieldrequired => 'variable',
444 variable => 'CataloguingLog',
446 forceupdate => {'explanation' => 1, 'type' => 1},
447 explanation => 'Active this if you want to log cataloguing action.',
451 uniquefieldrequired => 'variable',
452 variable => 'BorrowersLog',
454 forceupdate => {'explanation' => 1, 'type' => 1},
455 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
459 uniquefieldrequired => 'variable',
460 variable => 'SubscriptionLog',
462 forceupdate => {'explanation' => 1, 'type' => 1},
463 explanation => 'Active this if you want to log Subscription action',
467 uniquefieldrequired => 'variable',
468 variable => 'IssueLog',
470 forceupdate => {'explanation' => 1, 'type' => 1},
471 explanation => 'Active this if you want to log issue.',
475 uniquefieldrequired => 'variable',
476 variable => 'ReturnLog',
478 forceupdate => {'explanation' => 1, 'type' => 1},
479 explanation => 'Active this if you want to log the circulation return',
483 uniquefieldrequired => 'variable',
484 variable => 'Version',
486 forceupdate => {'explanation' => 1, 'type' => 1},
487 explanation => 'Koha Version',
491 uniquefieldrequired => 'variable',
492 variable => 'LetterLog',
494 forceupdate => {'explanation' => 1, 'type' => 1},
495 explanation => 'Active this if you want to log all the letter sent',
499 uniquefieldrequired => 'variable',
500 variable => 'FinesLog',
502 forceupdate => {'explanation' => 1, 'type' => 1},
503 explanation => 'Active this if you want to log fines',
507 uniquefieldrequired => 'variable',
508 variable => 'NoZebra',
510 forceupdate => {'explanation' => 1, 'type' => 1},
511 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
515 uniquefieldrequired => 'variable',
516 variable => 'NoZebraIndexes',
518 forceupdate => {'explanation' => 1, 'type' => 1},
519 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
523 uniquefieldrequired => 'variable',
524 variable => 'uppercasesurnames',
526 forceupdate => {'explanation' => 1, 'type' => 1},
527 explanation => "Force Surnames to be uppercase",
533 uniquefieldrequired => 'bit',
535 flag => 'editauthorities',
536 flagdesc => 'allow to edit authorities',
540 uniquefieldrequired => 'bit',
543 flagdesc => 'allow to manage serials subscriptions',
547 uniquefieldrequired => 'bit',
550 flagdesc => 'allow to access to the reports module',
554 authorised_values => [
556 uniquefieldrequired => 'id',
557 category => 'SUGGEST',
558 authorised_value => 'Not enough budget',
559 lib => 'This book it too much expensive',
564 my %fielddefinitions = (
566 # { field => 'fieldname',
567 # type => 'fieldtype',
575 field => 'booksellerid',
590 extra => 'auto_increment',
593 field => 'listprice',
594 type => 'varchar(10)',
601 field => 'invoiceprice',
602 type => 'varchar(10)',
612 field => 'notify_id',
620 field => 'notify_level',
631 { field => 'firstname',
635 { field => 'initials',
639 { field => 'B_email',
642 after => 'B_zipcode',
645 field => 'streetnumber', # street number (hidden if streettable table is empty)
651 field => 'streettype', # street table, list builded from a system table
654 after => 'streetnumber',
661 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
667 field => 'B_streettype', # street table, list builded from a system table
670 after => 'B_streetnumber',
679 field => 'address2', # complement address
691 field => 'contactfirstname', # contact's firstname
694 after => 'contactname',
697 field => 'contacttitle', # contact's title
700 after => 'contactfirstname',
703 field => 'branchcode',
704 type => 'varchar(10)',
710 field => 'categorycode',
711 type => 'varchar(10)',
721 type => 'varchar(25)',
729 type => 'varchar(4)',
737 type => 'varchar(30)',
743 field => 'publicationyear',
750 field => 'collectiontitle',
751 type => 'mediumtext',
757 field => 'collectionissn',
758 type => 'mediumtext',
764 field => 'collectionvolume',
765 type => 'mediumtext',
771 field => 'editionstatement',
778 field => 'editionresponsability',
786 deletedbiblioitems => [
789 type => 'varchar(30)',
798 type => 'varchar(15)',
805 field => 'branchprinter',
806 type => 'varchar(100)',
813 field => 'branchcode',
814 type => 'varchar(10)',
822 field => 'frombranch',
823 type => 'VARCHAR(10)',
831 type => 'VARCHAR(10)',
840 field => 'category_type',
848 field => 'categorycode',
849 type => 'varchar(10)',
857 deletedborrowers => [
858 { field => 'firstname',
862 { field => 'initials',
866 { field => 'B_email',
869 after => 'B_zipcode',
872 field => 'streetnumber', # street number (hidden if streettable table is empty)
878 field => 'streettype', # street table, list builded from a system table
881 after => 'streetnumber',
888 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
894 field => 'B_streettype', # street table, list builded from a system table
897 after => 'B_streetnumber',
906 field => 'address2', # complement address
918 field => 'contactfirstname', # contact's firstname
921 after => 'contactname',
924 field => 'contacttitle', # contact's title
927 after => 'contactfirstname',
933 field => 'borrowernumber',
935 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
941 field => 'itemnumber',
943 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
949 field => 'branchcode',
950 type => 'varchar(10)',
957 field => 'issuedate',
961 default => '0000-00-00',
972 default => '0000-00-00',
976 field => 'cutterextra',
977 type => 'varchar(45)',
984 field => 'issue_date',
992 field => 'homebranch',
993 type => 'varchar(10)',
1000 field => 'holdingbranch',
1001 type => 'varchar(10)',
1009 type => 'varchar(10)',
1018 field => 'itemtype',
1019 type => 'varchar(10)',
1042 marc_subfield_structure => [
1044 field => 'defaultvalue',
1053 field => 'expirationdate',
1070 field => 'waitingdate',
1090 field => 'dateadded',
1091 type => 'timestamp',
1095 systempreferences => [
1105 field => 'explanation',
1127 # { indexname => 'index detail'
1131 { indexname => 'PRIMARY',
1137 { indexname => 'booksellerid',
1138 content => 'booksellerid',
1142 { indexname => 'basketno',
1143 content => 'basketno',
1146 aqorderbreakdown => [
1147 { indexname => 'ordernumber',
1148 content => 'ordernumber',
1150 { indexname => 'bookfundid',
1151 content => 'bookfundid',
1155 { indexname => 'isbn',
1158 { indexname => 'publishercode',
1159 content => 'publishercode',
1164 indexname => 'branchcode',
1165 content => 'branchcode',
1169 branchrelations => [
1171 indexname => 'PRIMARY',
1172 content => 'categorycode',
1176 branchrelations => [
1177 { indexname => 'PRIMARY',
1178 content => 'branchcode,categorycode',
1181 { indexname => 'branchcode',
1182 content => 'branchcode',
1184 { indexname => 'categorycode',
1185 content => 'categorycode',
1189 { indexname => 'PRIMARY',
1190 content => 'currency',
1196 indexname => 'categorycode',
1197 content => 'categorycode',
1201 { indexname => 'homebranch',
1202 content => 'homebranch',
1204 { indexname => 'holdingbranch',
1205 content => 'holdingbranch',
1210 indexname => 'itemtype',
1211 content => 'itemtype',
1215 { indexname => 'shelfnumber',
1216 content => 'shelfnumber',
1218 { indexname => 'itemnumber',
1219 content => 'itemnumber',
1223 { indexname => 'PRIMARY',
1230 my %foreign_keys = (
1232 # { key => 'the key in table' (must be indexed)
1233 # foreigntable => 'the foreigntable name', # (the parent)
1234 # foreignkey => 'the foreign key column(s)' # (in the parent)
1235 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1236 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1239 branchrelations => [
1240 { key => 'branchcode',
1241 foreigntable => 'branches',
1242 foreignkey => 'branchcode',
1243 onUpdate => 'CASCADE',
1244 onDelete => 'CASCADE',
1246 { key => 'categorycode',
1247 foreigntable => 'branchcategories',
1248 foreignkey => 'categorycode',
1249 onUpdate => 'CASCADE',
1250 onDelete => 'CASCADE',
1254 { key => 'shelfnumber',
1255 foreigntable => 'virtualshelf',
1256 foreignkey => 'shelfnumber',
1257 onUpdate => 'CASCADE',
1258 onDelete => 'CASCADE',
1260 { key => 'itemnumber',
1261 foreigntable => 'items',
1262 foreignkey => 'itemnumber',
1263 onUpdate => 'CASCADE',
1264 onDelete => 'CASCADE',
1267 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1268 # easily deleted, but branches/itemtype not too easy to empty...
1270 { key => 'biblionumber',
1271 foreigntable => 'biblio',
1272 foreignkey => 'biblionumber',
1273 onUpdate => 'CASCADE',
1274 onDelete => 'CASCADE',
1276 { key => 'itemtype',
1277 foreigntable => 'itemtypes',
1278 foreignkey => 'itemtype',
1279 onUpdate => 'CASCADE',
1280 onDelete => 'RESTRICT',
1284 { key => 'biblioitemnumber',
1285 foreigntable => 'biblioitems',
1286 foreignkey => 'biblioitemnumber',
1287 onUpdate => 'CASCADE',
1288 onDelete => 'CASCADE',
1290 { key => 'homebranch',
1291 foreigntable => 'branches',
1292 foreignkey => 'branchcode',
1293 onUpdate => 'CASCADE',
1294 onDelete => 'RESTRICT',
1296 { key => 'holdingbranch',
1297 foreigntable => 'branches',
1298 foreignkey => 'branchcode',
1299 onUpdate => 'CASCADE',
1300 onDelete => 'RESTRICT',
1304 { key => 'booksellerid',
1305 foreigntable => 'aqbooksellers',
1307 onUpdate => 'CASCADE',
1308 onDelete => 'RESTRICT',
1312 { key => 'basketno',
1313 foreigntable => 'aqbasket',
1314 foreignkey => 'basketno',
1315 onUpdate => 'CASCADE',
1316 onDelete => 'CASCADE',
1318 { key => 'biblionumber',
1319 foreigntable => 'biblio',
1320 foreignkey => 'biblionumber',
1321 onUpdate => 'SET NULL',
1322 onDelete => 'SET NULL',
1326 { key => 'listprice',
1327 foreigntable => 'currency',
1328 foreignkey => 'currency',
1329 onUpdate => 'CASCADE',
1330 onDelete => 'CASCADE',
1332 { key => 'invoiceprice',
1333 foreigntable => 'currency',
1334 foreignkey => 'currency',
1335 onUpdate => 'CASCADE',
1336 onDelete => 'CASCADE',
1339 aqorderbreakdown => [
1340 { key => 'ordernumber',
1341 foreigntable => 'aqorders',
1342 foreignkey => 'ordernumber',
1343 onUpdate => 'CASCADE',
1344 onDelete => 'CASCADE',
1346 { key => 'bookfundid',
1347 foreigntable => 'aqbookfund',
1348 foreignkey => 'bookfundid',
1349 onUpdate => 'CASCADE',
1350 onDelete => 'CASCADE',
1353 branchtransfers => [
1354 { key => 'frombranch',
1355 foreigntable => 'branches',
1356 foreignkey => 'branchcode',
1357 onUpdate => 'CASCADE',
1358 onDelete => 'CASCADE',
1360 { key => 'tobranch',
1361 foreigntable => 'branches',
1362 foreignkey => 'branchcode',
1363 onUpdate => 'CASCADE',
1364 onDelete => 'CASCADE',
1366 { key => 'itemnumber',
1367 foreigntable => 'items',
1368 foreignkey => 'itemnumber',
1369 onUpdate => 'CASCADE',
1370 onDelete => 'CASCADE',
1374 { key => 'categorycode',
1375 foreigntable => 'categories',
1376 foreignkey => 'categorycode',
1377 onUpdate => 'CASCADE',
1378 onDelete => 'CASCADE',
1380 { key => 'itemtype',
1381 foreigntable => 'itemtypes',
1382 foreignkey => 'itemtype',
1383 onUpdate => 'CASCADE',
1384 onDelete => 'CASCADE',
1387 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1389 { key => 'borrowernumber',
1390 foreigntable => 'borrowers',
1391 foreignkey => 'borrowernumber',
1392 onUpdate => 'SET NULL',
1393 onDelete => 'SET NULL',
1395 { key => 'itemnumber',
1396 foreigntable => 'items',
1397 foreignkey => 'itemnumber',
1398 onUpdate => 'SET NULL',
1399 onDelete => 'SET NULL',
1403 { key => 'borrowernumber',
1404 foreigntable => 'borrowers',
1405 foreignkey => 'borrowernumber',
1406 onUpdate => 'CASCADE',
1407 onDelete => 'CASCADE',
1409 { key => 'biblionumber',
1410 foreigntable => 'biblio',
1411 foreignkey => 'biblionumber',
1412 onUpdate => 'CASCADE',
1413 onDelete => 'CASCADE',
1415 { key => 'itemnumber',
1416 foreigntable => 'items',
1417 foreignkey => 'itemnumber',
1418 onUpdate => 'CASCADE',
1419 onDelete => 'CASCADE',
1421 { key => 'branchcode',
1422 foreigntable => 'branches',
1423 foreignkey => 'branchcode',
1424 onUpdate => 'CASCADE',
1425 onDelete => 'CASCADE',
1428 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1429 # but prevent deleting a branch as soon as it has 1 borrower !
1430 { key => 'categorycode',
1431 foreigntable => 'categories',
1432 foreignkey => 'categorycode',
1433 onUpdate => 'RESTRICT',
1434 onDelete => 'RESTRICT',
1436 { key => 'branchcode',
1437 foreigntable => 'branches',
1438 foreignkey => 'branchcode',
1439 onUpdate => 'RESTRICT',
1440 onDelete => 'RESTRICT',
1443 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1444 # but prevent deleting a branch as soon as it has 1 borrower !
1445 { key => 'categorycode',
1446 foreigntable => 'categories',
1447 foreignkey => 'categorycode',
1448 onUpdate => 'RESTRICT',
1449 onDelete => 'RESTRICT',
1451 { key => 'branchcode',
1452 foreigntable => 'branches',
1453 foreignkey => 'branchcode',
1454 onUpdate => 'RESTRICT',
1455 onDelete => 'RESTRICT',
1459 { key => 'borrowernumber',
1460 foreigntable => 'borrowers',
1461 foreignkey => 'borrowernumber',
1462 onUpdate => 'CASCADE',
1463 onDelete => 'CASCADE',
1465 { key => 'itemnumber',
1466 foreigntable => 'items',
1467 foreignkey => 'itemnumber',
1468 onUpdate => 'SET NULL',
1469 onDelete => 'SET NULL',
1472 auth_tag_structure => [
1473 { key => 'authtypecode',
1474 foreigntable => 'auth_types',
1475 foreignkey => 'authtypecode',
1476 onUpdate => 'CASCADE',
1477 onDelete => 'CASCADE',
1480 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1485 my %column_change = (
1489 from => 'emailaddress',
1494 from => 'streetaddress',
1496 after => 'initials',
1499 from => 'faxnumber',
1504 from => 'textmessaging',
1510 to => 'contactnote',
1511 after => 'opacnote',
1514 from => 'physstreet',
1519 from => 'streetcity',
1521 after => 'B_address',
1534 from => 'homezipcode',
1541 after => 'B_zipcode',
1546 after => 'dateenrolled',
1549 from => 'guarantor',
1550 to => 'guarantorid',
1551 after => 'contactname',
1554 from => 'altrelationship',
1555 to => 'relationship',
1556 after => 'borrowernotes',
1560 deletedborrowers => [
1562 from => 'emailaddress',
1567 from => 'streetaddress',
1569 after => 'initials',
1572 from => 'faxnumber',
1577 from => 'textmessaging',
1583 to => 'contactnote',
1584 after => 'opacnote',
1587 from => 'physstreet',
1592 from => 'streetcity',
1594 after => 'B_address',
1607 from => 'homezipcode',
1614 after => 'B_zipcode',
1619 after => 'dateenrolled',
1622 from => 'guarantor',
1623 to => 'guarantorid',
1624 after => 'contactname',
1627 from => 'altrelationship',
1628 to => 'relationship',
1629 after => 'borrowernotes',
1635 # MOVE all tables TO UTF-8 and innoDB
1636 $sth = $dbh->prepare("show table status");
1638 while ( my $table = $sth->fetchrow_hashref ) {
1639 next if $table->{Name} eq 'marc_word';
1640 next if $table->{Name} eq 'marc_subfield_table';
1641 next if $table->{Name} eq 'auth_word';
1642 next if $table->{Name} eq 'auth_subfield_table';
1643 if ($table->{Engine} ne 'InnoDB') {
1644 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1645 print "moving $table->{Name} to InnoDB\n";
1647 unless ($table->{Collation} =~ /^utf8/) {
1648 print "moving $table->{Name} to utf8\n";
1649 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1650 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1651 # 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 !
1657 foreach my $table (keys %column_change) {
1658 $sth = $dbh->prepare("show columns from $table");
1661 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1663 $types{$column}->{type} ="$type";
1664 $types{$column}->{null} = "$null";
1665 $types{$column}->{key} = "$key";
1666 $types{$column}->{default} = "$default";
1667 $types{$column}->{extra} = "$extra";
1669 my $tablerows = $column_change{$table};
1670 foreach my $row ( @$tablerows ) {
1671 if ($types{$row->{from}}->{type}) {
1672 print "altering $table $row->{from} to $row->{to}\n";
1673 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1674 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1676 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1677 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1678 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1679 "$types{$row->{from}}->{extra} after $row->{after} ";
1686 # Enter here the field you want to delete from DB.
1687 # FIXME :: there is a %uselessfield before which seems doing the same things.
1688 my %fieldtodelete = (
1689 # tablename => [fieldname1,fieldname2,...]
1693 print "removing some unused fields...\n";
1694 foreach my $table ( keys %fieldtodelete ) {
1695 foreach my $field ( @{$fieldtodelete{$table}} ){
1696 print "removing ".$field." from ".$table;
1697 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1700 print "Error : $sth->errstr \n";
1705 # Enter here the line you want to remove from DB.
1706 my %linetodelete = (
1707 # table name => where clause.
1708 userflags => "bit = 8", # delete the 'reserveforself' flags
1712 #-------------------
1717 # Get version of MySQL database engine.
1718 my $mysqlversion = `mysqld --version`;
1719 $mysqlversion =~ /Ver (\S*) /;
1721 if ( $mysqlversion ge '3.23' ) {
1722 print "Could convert to MyISAM database tables...\n" unless $silent;
1725 #---------------------------------
1728 # Collect all tables into a list
1729 $sth = $dbh->prepare("show tables");
1731 while ( my ($table) = $sth->fetchrow ) {
1732 $existingtables{$table} = 1;
1736 # Now add any missing tables
1737 foreach $table ( keys %requiretables ) {
1738 unless ( $existingtables{$table} ) {
1739 print "Adding $table table...\n" unless $silent;
1740 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1743 print "Error : $sth->errstr \n";
1749 #---------------------------------
1752 foreach $table ( keys %requirefields ) {
1753 print "Check table $table\n" if $debug and not $silent;
1754 $sth = $dbh->prepare("show columns from $table");
1757 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1759 $types{$column} = $type;
1761 foreach $column ( keys %{ $requirefields{$table} } ) {
1762 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1763 if ( !$types{$column} ) {
1765 # column doesn't exist
1766 print "Adding $column field to $table table...\n" unless $silent;
1767 $query = "alter table $table
1768 add column $column " . $requirefields{$table}->{$column};
1769 print "Execute: $query\n" if $debug;
1770 my $sti = $dbh->prepare($query);
1773 print "**Error : $sti->errstr \n";
1780 foreach $table ( keys %fielddefinitions ) {
1781 print "Check table $table\n" if $debug;
1782 $sth = $dbh->prepare("show columns from $table");
1785 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1787 $definitions->{$column}->{type} = $type;
1788 $definitions->{$column}->{null} = $null;
1789 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1790 $definitions->{$column}->{key} = $key;
1791 $definitions->{$column}->{default} = $default;
1792 $definitions->{$column}->{extra} = $extra;
1794 my $fieldrow = $fielddefinitions{$table};
1795 foreach my $row (@$fieldrow) {
1796 my $field = $row->{field};
1797 my $type = $row->{type};
1798 my $null = $row->{null};
1799 # $null = 'YES' if $row->{null} eq 'NULL';
1800 my $key = $row->{key};
1801 my $default = $row->{default};
1802 # $default="''" unless $default;
1803 my $extra = $row->{extra};
1804 my $def = $definitions->{$field};
1805 my $after = ($row->{after}?" after ".$row->{after}:"");
1807 unless ( $type eq $def->{type}
1808 && $null eq $def->{null}
1809 && $key eq $def->{key}
1810 && $extra eq $def->{extra} )
1812 if ( $null eq '' ) {
1815 if ( $key eq 'PRI' ) {
1816 $key = 'PRIMARY KEY';
1818 unless ( $extra eq 'auto_increment' ) {
1822 # if it's a new column use "add", if it's an old one, use "change".
1824 if ($definitions->{$field}->{type}) {
1825 $action="change $field"
1829 # if it's a primary key, drop the previous pk, before altering the table
1830 print " alter or create $field in $table\n" unless $silent;
1832 if ($key ne 'PRIMARY KEY') {
1833 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1834 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1836 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1837 # something strange : for indexes UNIQUE, they are reported as primary key here.
1838 # but if you try to run with drop primary key, it fails.
1839 # thus, we run the query twice, one will fail, one will succeed.
1841 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1842 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1849 print "removing some unused data...\n";
1850 foreach my $table ( keys %linetodelete ) {
1851 foreach my $where ( @{linetodelete{$table}} ){
1852 print "DELETE FROM ".$table." where ".$where;
1854 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1857 print "Error : $sth->errstr \n";
1862 # Populate tables with required data
1864 # synch table and deletedtable.
1865 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1866 my %deletedborrowers;
1867 print "synch'ing $table and deleted$table\n";
1868 $sth = $dbh->prepare("show columns from deleted$table");
1870 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1871 $deletedborrowers{$column}=1;
1873 $sth = $dbh->prepare("show columns from $table");
1876 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1877 unless ($deletedborrowers{$column}) {
1878 my $newcol="alter table deleted$table add $column $type";
1879 if ($null eq 'YES') {
1880 $newcol .= " NULL ";
1882 $newcol .= " NOT NULL ";
1884 $newcol .= "default ".$dbh->quote($default) if $default;
1885 $newcol .= " after $previous" if $previous;
1887 print "creating column $column\n";
1893 # update publisheddate
1895 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1897 my ($emptypublished) = $sth->fetchrow;
1898 if ($emptypublished) {
1899 print "Updating publisheddate\n";
1900 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1902 foreach my $table ( keys %tabledata ) {
1903 print "Checking for data required in table $table...\n" unless $silent;
1904 my $tablerows = $tabledata{$table};
1905 foreach my $row (@$tablerows) {
1906 my $uniquefieldrequired = $row->{uniquefieldrequired};
1907 my $uniquevalue = $row->{$uniquefieldrequired};
1908 my $forceupdate = $row->{forceupdate};
1911 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1913 $sth->execute($uniquevalue);
1915 foreach my $field (keys %$forceupdate) {
1916 if ($forceupdate->{$field}) {
1917 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1918 $sth->execute($row->{$field}, $uniquevalue);
1922 print "Adding row to $table: " unless $silent;
1926 foreach my $field ( keys %$row ) {
1927 next if $field eq 'uniquefieldrequired';
1928 next if $field eq 'forceupdate';
1929 my $value = $row->{$field};
1930 push @values, $value;
1931 print " $field => $value" unless $silent;
1932 $fieldlist .= "$field,";
1933 $placeholders .= "?,";
1935 print "\n" unless $silent;
1936 $fieldlist =~ s/,$//;
1937 $placeholders =~ s/,$//;
1938 print "insert into $table ($fieldlist) values ($placeholders)";
1941 "insert into $table ($fieldlist) values ($placeholders)");
1942 $sth->execute(@values);
1948 # check indexes and create them when needed
1950 print "Checking for index required...\n" unless $silent;
1951 foreach my $table ( keys %indexes ) {
1953 # read all indexes from $table
1955 $sth = $dbh->prepare("show index from $table");
1957 my %existingindexes;
1958 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
1959 $existingindexes{$key_name} = 1;
1961 # read indexes to check
1962 my $tablerows = $indexes{$table};
1963 foreach my $row (@$tablerows) {
1964 my $key_name=$row->{indexname};
1965 if ($existingindexes{$key_name} eq 1) {
1966 # print "$key_name existing";
1968 print "\tCreating index $key_name in $table\n";
1970 if ($row->{indexname} eq 'PRIMARY') {
1971 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
1973 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
1976 print "Error $sql : $dbh->err \n" if $dbh->err;
1982 # check foreign keys and create them when needed
1984 print "Checking for foreign keys required...\n" unless $silent;
1985 foreach my $table ( keys %foreign_keys ) {
1987 # read all indexes from $table
1989 $sth = $dbh->prepare("show table status like '$table'");
1991 my $stat = $sth->fetchrow_hashref;
1992 # read indexes to check
1993 my $tablerows = $foreign_keys{$table};
1994 foreach my $row (@$tablerows) {
1995 my $foreign_table=$row->{foreigntable};
1996 if ($stat->{'Comment'} =~/$foreign_table/) {
1997 # print "$foreign_table existing\n";
1999 print "\tCreating foreign key $foreign_table in $table\n";
2000 # first, drop any orphan value in child table
2001 if ($row->{onDelete} ne "RESTRICT") {
2002 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2004 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2006 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2007 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2008 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2011 print "====================
2012 An error occured during :
2014 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).
2015 You can find those values with select
2016 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2017 ====================\n
2023 # now drop useless tables
2024 foreach $table ( @TableToDelete ) {
2025 if ( $existingtables{$table} ) {
2026 print "Dropping unused table $table\n" if $debug and not $silent;
2027 $dbh->do("drop table $table");
2029 print "Error : $dbh->errstr \n";
2038 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2041 # 1st, get how many biblio we will have to do...
2042 $sth = $dbh->prepare('select count(*) from marc_biblio');
2044 my ($totaltodo) = $sth->fetchrow;
2046 $sth = $dbh->prepare("show columns from biblio");
2049 my $bibliofwexist=0;
2050 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2051 $bibliofwexist=1 if $column eq 'frameworkcode';
2053 unless ($bibliofwexist) {
2054 print "moving biblioframework to biblio table\n";
2055 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2056 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2058 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2060 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2061 $sth_update->execute($frameworkcode,$biblionumber);
2063 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2068 # at last, remove useless fields
2069 foreach $table ( keys %uselessfields ) {
2070 my @fields = split /,/,$uselessfields{$table};
2073 foreach my $fieldtodrop (@fields) {
2074 $fieldtodrop =~ s/\t//g;
2075 $fieldtodrop =~ s/\n//g;
2077 $sth = $dbh->prepare("show columns from $table");
2079 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2081 $exists =1 if ($column eq $fieldtodrop);
2084 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2085 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2092 # Changing aqbookfund's primary key
2094 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2102 # Revision 1.172 2007/07/19 10:21:22 hdl
2103 # Adding some new fields to biblioitems:
2109 # Changing publicationyear to text.
2111 # Revision 1.171 2007/07/02 02:30:16 rangi
2112 # Fix for bug 1296, making surnames uppercase a systems preference
2114 # Revision 1.170 2007/06/26 13:25:37 tipaul
2115 # removing some useless tables from updatedatabase
2117 # Revision 1.169 2007/06/26 09:23:26 tipaul
2118 # improving OpacBrowser systempref explanation
2120 # Revision 1.168 2007/06/25 15:02:31 tipaul
2121 # missing field declaration
2123 # Revision 1.167 2007/06/15 13:09:08 toins
2124 # bugfix : bibliotitems.dewey & deletedbiblioitems.dewey mustn't be double(8,6).
2126 # Revision 1.166 2007/06/08 09:40:12 toins
2127 # bug fix : items.homebranch must be VARCHAR(10)
2129 # Revision 1.165 2007/05/23 16:33:10 tipaul
2130 # skip move to innoDB for the 4 22 tables, that are used to store MARC records, are useless in Koha 3.0 The process is very very long, so the updatedatabase should speed up a lot (by long I mean 1 hour on my Dual core with SCSI disk, for a 50 000 biblios long table
2132 # Revision 1.164 2007/05/04 16:24:09 tipaul
2133 # various bugfixes on parameters modules + adding default NoZebraIndexes systempreference if it's empty
2135 # Revision 1.163 2007/05/02 16:44:31 tipaul
2136 # NoZebra SQL index management :
2137 # * adding 3 subs in Biblio.pm
2138 # - GetNoZebraIndexes, that get the index structure in a new systempreference (added with this commit)
2139 # - _DelBiblioNoZebra, that retrieve all index entries for a biblio and remove in a variable the biblio reference
2140 # - _AddBiblioNoZebra, that add index entries for a biblio.
2141 # Note that the 2 _Add and _Del subs work only in a hash variable, to speed up things in case of a modif (ie : delete+add). The effective SQL update is done in the ModZebra sub (that existed before, and dealed with zebra index).
2142 # I think the code has to be more deeply tested, but it works at least partially.
2144 # Revision 1.162 2007/04/30 16:16:50 tipaul
2145 # bugfix for updatedatabase : when there is no default value (NULL fields) + removing bibliothesaurus table+adding NoZebra systempref (False by default)
2147 # Revision 1.161 2007/04/13 16:27:55 hdl
2148 # Adding Version variable to systempreferences.
2150 # Revision 1.160 2007/03/19 18:35:13 toins
2151 # - adding default value in marc_subfield_structure.
2152 # - now marc_subfields_structure displays subfields in tab view.
2154 # Revision 1.159 2007/03/16 01:25:09 kados
2155 # Using my precrash CVS copy I did the following:
2157 # cvs -z3 -d:ext:kados@cvs.savannah.nongnu.org:/sources/koha co -P koha
2158 # find koha.precrash -type d -name "CVS" -exec rm -v {} \;
2159 # cp -r koha.precrash/* koha/
2163 # This should in theory put us right back where we were before the crash
2165 # Revision 1.159 2007/03/12 17:52:30 rych
2166 # add pri key to userflags
2168 # Revision 1.158 2007/03/09 15:14:57 tipaul
2169 # rel_3_0 moved to HEAD
2171 # Revision 1.157.2.56 2007/01/31 16:22:54 btoumi
2172 # -add possibility to use isbn with length of 13 characters
2173 # for Import datas in the reservoir.
2174 # -modify isbn field in marc_breeding table (varchar 13)
2175 # -add isbn filter (no - )when u read a notice from reservoir
2176 # -add filter to have right field 100
2178 # Revision 1.157.2.55 2007/01/30 10:50:19 tipaul
2179 # adding 2 usefull indexes to biblioitems table
2181 # Revision 1.157.2.54 2007/01/29 16:45:52 toins
2182 # * adding a new default authorised value : SUGGEST.
2183 # SUGGEST give some reasons to accept or reject a suggestion.
2185 # * default value for borrowersMandatoryfield syspref is now "cardnumber|surname|adress"
2187 # Revision 1.157.2.53 2007/01/26 20:48:37 hdl
2188 # Serials management : Bugfixes + improvements.
2189 # - Partial dates are now managed
2190 # - next Date Calculation with irregularity tested for 1 week and 1 month.
2191 # - manage if subscription is abouttoexpire or expired.
2192 # - Adding some information on serials pages about subscription.
2193 # - Managing irregularity with numbers.
2194 # - Adding Internal Notes in subscription management.
2195 # - Repeating Button above pages.
2197 # Please run Updatedatabase to change irregularity and add internalnotes field to subscription
2199 # Revision 1.157.2.52 2007/01/24 13:57:26 tipaul
2200 # - setting supplierid to auto_increment (HDL : could you check that is works, i'm not 100% sure)
2201 # - removing 22 -> 30 marc_subfield_table -> marcxml stuff, it's now in misc/migration_tools/22_to_30/
2203 # Revision 1.157.2.51 2007/01/18 09:58:45 tipaul
2204 # defaulting NOT NULL fields (to '')
2206 # Revision 1.157.2.50 2007/01/18 09:39:21 tipaul
2207 # issuedate must be defaulted with ' '
2209 # Revision 1.157.2.49 2007/01/18 09:37:30 tipaul
2210 # removing 2 field definitions that were here twice
2212 # Revision 1.157.2.48 2007/01/15 09:55:40 toins
2213 # adding a new logging systempref : FinesLog.
2215 # Revision 1.157.2.47 2007/01/12 18:09:49 toins
2218 # Revision 1.157.2.46 2007/01/11 14:35:39 tipaul
2219 # adding Opac Browser feature : the build_browser_and_cloud.pl script will :
2220 # - fill the browser table, that enable browsing, digit by digit of a given category, the catalogue. A complete dewey classification is provided in the script, active only for french libraries, of course (although, for instance, the script check that the catalogue is in english for developping convenience)
2221 # - fill the tags table, that contains the subject cloud.
2223 # The cloud part is a copy of the previous build_tags.pl script that can be deleted : those 2 scripts require to parse all the catalogue to extract interesting data, so they are long. It's useless to parse the catalogue twice !
2225 # The commit also add the systempreference to hide/show the OpacBrowse in database & in systempref management script.
2227 # IMPROVEMENTS to do :
2228 # - the script that builds the tables can be improved to update only last week biblios (at the price of a small error in value links, but it's not a problem).
2229 # - add, in parameters section, a place to edit browser descriptions. The build script has to be updated to to avoid deleting existing browser descriptions.
2231 # Revision 1.157.2.45 2007/01/10 16:52:52 toins
2232 # Value for Log Features syspref are set to 0 by default.
2234 # Revision 1.157.2.44 2007/01/10 16:31:15 toins
2235 # new systems preferences :
2236 # - CataloguingLog (log the update/creation/deletion of a notice if set to 1)
2237 # - BorrowersLog ( idem for borrowers )
2238 # - IssueLog (log all issue if set to 1)
2239 # - ReturnLog (log all return if set to 1)
2240 # - SusbcriptionLog (log all creation/deletion/update of a subcription)
2242 # All of theses are in a new tab called 'LOGFeatures' in systempreferences.pl
2244 # Revision 1.157.2.43 2007/01/10 14:13:17 toins
2245 # opac_news.displayed is replaced by opac_news.number.
2246 # This field say how are ordered the news on the template.
2248 # Revision 1.157.2.42 2007/01/09 14:09:01 toins
2249 # 2 field added to opac_news.('expirationdate' and 'displayed').