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
55 categorytable => "(categorycode char(5) NOT NULL default '',
56 description text default '',
57 itemtypecodes text default '',
58 PRIMARY KEY (categorycode)
60 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
61 description text default '',
62 itemtypecodes text default '',
63 PRIMARY KEY (subcategorycode)
65 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
66 description text default '',
67 itemtypecodes text default '',
68 PRIMARY KEY (mediatypecode)
71 `timestamp` TIMESTAMP NOT NULL ,
72 `user` INT( 11 ) NOT NULL ,
73 `module` TEXT default '',
74 `action` TEXT default '' ,
75 `object` INT(11) default '' ,
76 `info` TEXT default '' ,
77 PRIMARY KEY ( `timestamp` , `user` )
80 module varchar(20) NOT NULL default '',
81 code varchar(20) NOT NULL default '',
82 name varchar(100) NOT NULL default '',
83 title varchar(200) NOT NULL default '',
85 PRIMARY KEY (module,code)
88 alertid int(11) NOT NULL auto_increment,
89 borrowernumber int(11) NOT NULL default '0',
90 type varchar(10) NOT NULL default '',
91 externalid varchar(20) NOT NULL default '',
92 PRIMARY KEY (alertid),
93 KEY borrowernumber (borrowernumber),
94 KEY type (type,externalid)
97 `idnew` int(10) unsigned NOT NULL auto_increment,
98 `title` varchar(250) NOT NULL default '',
100 `lang` varchar(4) NOT NULL default '',
101 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
102 PRIMARY KEY (`idnew`)
104 repeatable_holidays => "(
105 `id` int(11) NOT NULL auto_increment,
106 `branchcode` varchar(4) NOT NULL default '',
107 `weekday` smallint(6) default NULL,
108 `day` smallint(6) default NULL,
109 `month` smallint(6) default NULL,
110 `title` varchar(50) NOT NULL default '',
111 `description` text NOT NULL,
114 special_holidays => "(
115 `id` int(11) NOT NULL auto_increment,
116 `branchcode` varchar(4) NOT NULL default '',
117 `day` smallint(6) NOT NULL default '0',
118 `month` smallint(6) NOT NULL default '0',
119 `year` smallint(6) NOT NULL default '0',
120 `isexception` smallint(1) NOT NULL default '1',
121 `title` varchar(50) NOT NULL default '',
122 `description` text NOT NULL,
125 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
126 `categorycode` char(2) NOT NULL default '',
127 `delay1` int(4) default '0',
128 `letter1` varchar(20) default NULL,
129 `debarred1` char(1) default '0',
130 `delay2` int(4) default '0',
131 `debarred2` char(1) default '0',
132 `letter2` varchar(20) default NULL,
133 `delay3` int(4) default '0',
134 `letter3` varchar(20) default NULL,
135 `debarred3` int(1) default '0',
136 PRIMARY KEY (`branchcode`,`categorycode`)
138 cities => "(`cityid` int auto_increment,
139 `city_name` char(100) NOT NULL,
140 `city_zipcode` char(20),
141 PRIMARY KEY (`cityid`)
143 roadtype => "(`roadtypeid` int auto_increment,
144 `road_type` char(100) NOT NULL,
145 PRIMARY KEY (`roadtypeid`)
149 labelid int(11) NOT NULL auto_increment,
150 itemnumber varchar(100) NOT NULL default '',
151 timestamp timestamp(14) NOT NULL,
152 PRIMARY KEY (labelid)
156 id int(4) NOT NULL auto_increment,
157 barcodetype char(100) default '',
158 title tinyint(1) default '0',
159 isbn tinyint(1) default '0',
160 itemtype tinyint(1) default '0',
161 barcode tinyint(1) default '0',
162 dewey tinyint(1) default '0',
163 class tinyint(1) default '0',
164 author tinyint(1) default '0',
165 papertype char(100) default '',
166 startrow int(2) default NULL,
170 reviewid integer NOT NULL auto_increment,
171 borrowernumber integer,
172 biblionumber integer,
175 datereviewed datetime,
176 PRIMARY KEY (reviewid)
178 borrowers_to_borrowers => "(
182 subscriptionroutinglist=>"(
183 routingid integer NOT NULL auto_increment,
184 borrowernumber integer,
186 subscriptionid integer,
187 PRIMARY KEY (routingid)
191 notify_id int(11) NOT NULL default '0',
192 `borrowernumber` int(11) NOT NULL default '0',
193 `itemnumber` int(11) NOT NULL default '0',
194 `notify_date` date NOT NULL default '0000-00-00',
195 `notify_send_date` date default NULL,
196 `notify_level` int(1) NOT NULL default '0',
197 `method` varchar(20) NOT NULL default ''
201 `charge_id` varchar(5) NOT NULL default '',
202 `description` text NOT NULL,
203 `amount` decimal(28,6) NOT NULL default '0.000000',
204 `min` int(4) NOT NULL default '0',
205 `max` int(4) NOT NULL default '0',
206 `level` int(1) NOT NULL default '0',
207 PRIMARY KEY (`charge_id`)
210 `entry` varchar(255) NOT NULL default '',
211 `weight` bigint(20) NOT NULL default '0',
212 PRIMARY KEY (`entry`)
216 `id` int NOT NULL auto_increment,
217 `biblio_auth_number` int NOT NULL,
218 `operation` char(20) NOT NULL,
219 `server` char(20) NOT NULL ,
221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
225 my %requirefields = (
226 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 \'\''},
227 itemtypes => { 'imageurl' => 'char(200) NULL'},
228 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
229 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
230 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
231 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'},
232 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
233 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
234 statistics => { 'associatedborrower' => 'integer'},
235 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
236 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
237 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
239 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
241 # tablename => { 'field' => 'fieldtype' },
244 # Enter here the table to delete.
245 my @TableToDelete = qw(
251 my %uselessfields = (
252 # tablename => "field1,field2",
253 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
254 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
256 # the other hash contains other actions that can't be done elsewhere. they are done
257 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
259 # The tabledata hash contains data that should be in the tables.
260 # The uniquefieldrequired hash entry is used to determine which (if any) fields
261 # must not exist in the table for this row to be inserted. If the
262 # uniquefieldrequired entry is already in the table, the existing data is not
263 # modified, unless the forceupdate hash entry is also set. Fields in the
264 # anonymous "forceupdate" hash will be forced to be updated to the default
265 # values given in the %tabledata hash.
269 # { uniquefielrequired => 'fieldname', # the primary key in the table
270 # fieldname => fieldvalue,
271 # fieldname2 => fieldvalue2,
274 systempreferences => [
276 uniquefieldrequired => 'variable',
277 variable => 'useDaysMode',
279 forceupdate => { 'explanation' => 1,
281 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
283 options => 'Calendar|Days'
286 uniquefieldrequired => 'variable',
287 variable => 'BorrowersTitles',
288 value => 'Mr|Mrs|Miss|Ms',
289 forceupdate => { 'explanation' => 1,
291 explanation => 'List all Titles for borrowers',
295 uniquefieldrequired => 'variable',
296 variable => 'BorrowerMandatoryField',
297 value => 'cardnumber|surname|address',
298 forceupdate => { 'explanation' => 1,
300 explanation => 'List all mandatory fields for borrowers',
304 uniquefieldrequired => 'variable',
305 variable => 'borrowerRelationship',
306 value => 'father|mother,grand-mother',
307 forceupdate => { 'explanation' => 1,
309 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
313 uniquefieldrequired => 'variable',
314 variable => 'ReservesMaxPickUpDelay',
316 forceupdate => { 'explanation' => 1,
318 explanation => 'Maximum delay to pick up a reserved document',
322 uniquefieldrequired => 'variable',
323 variable => 'TransfersMaxDaysWarning',
325 forceupdate => { 'explanation' => 1,
327 explanation => 'Max delay before considering the transfer has potentialy a problem',
331 uniquefieldrequired => 'variable',
332 variable => 'memberofinstitution',
334 forceupdate => { 'explanation' => 1,
336 explanation => 'Are your patrons members of institutions',
340 uniquefieldrequired => 'variable',
341 variable => 'ReadingHistory',
343 forceupdate => { 'explanation' => 1,
345 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
349 uniquefieldrequired => 'variable',
350 variable => 'IssuingInProcess',
352 forceupdate => { 'explanation' => 1,
354 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
358 uniquefieldrequired => 'variable',
359 variable => 'AutomaticItemReturn',
361 forceupdate => { 'explanation' => 1,
363 explanation => 'This Variable allow or not to return automaticly to his homebranch',
367 uniquefieldrequired => 'variable',
368 variable => 'reviewson',
370 forceupdate => { 'explanation' => 1,
372 explanation => 'Allows patrons to submit reviews from the opac',
376 uniquefieldrequired => 'variable',
377 variable => 'intranet_includes',
379 forceupdate => { 'explanation' => 1,
381 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
385 uniquefieldrequired => 'variable',
386 variable => 'AutoLocation',
388 forceupdate => { 'explanation' => 1,
390 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
394 uniquefieldrequired => 'variable',
395 variable => 'serialsadditems',
401 explanation => 'If set, a new item will be automatically added when receiving an issue',
405 uniquefieldrequired => 'variable',
406 variable => 'expandedSearchOption',
412 explanation => 'search among marc field',
416 uniquefieldrequired => 'variable',
417 variable => 'RequestOnOpac',
419 forceupdate => { 'explanation' => 1,
421 explanation => 'option to allow reserves on opac',
425 uniquefieldrequired => 'variable',
426 variable => 'OpacCloud',
428 forceupdate => { 'explanation' => 1,
430 explanation => 'Enable / Disable cloud link on OPAC',
434 uniquefieldrequired => 'variable',
435 variable => 'OpacBrowser',
437 forceupdate => { 'explanation' => 1,
439 explanation => 'Enable/Disable browser link on OPAC (needs to set misc/cronjob/build_browser.pl)',
443 uniquefieldrequired => 'variable',
444 variable => 'OpacTopissue',
446 forceupdate => { 'explanation' => 1,
448 explanation => 'Enable / Disable the top issue link on OPAC',
452 uniquefieldrequired => 'variable',
453 variable => 'OpacAuthorities',
455 forceupdate => { 'explanation' => 1,
457 explanation => 'Enable / Disable the search authority link on OPAC',
461 uniquefieldrequired => 'variable',
462 variable => 'CataloguingLog',
464 forceupdate => {'explanation' => 1, 'type' => 1},
465 explanation => 'Active this if you want to log cataloguing action.',
469 uniquefieldrequired => 'variable',
470 variable => 'BorrowersLog',
472 forceupdate => {'explanation' => 1, 'type' => 1},
473 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
477 uniquefieldrequired => 'variable',
478 variable => 'SubscriptionLog',
480 forceupdate => {'explanation' => 1, 'type' => 1},
481 explanation => 'Active this if you want to log Subscription action',
485 uniquefieldrequired => 'variable',
486 variable => 'IssueLog',
488 forceupdate => {'explanation' => 1, 'type' => 1},
489 explanation => 'Active this if you want to log issue.',
493 uniquefieldrequired => 'variable',
494 variable => 'ReturnLog',
496 forceupdate => {'explanation' => 1, 'type' => 1},
497 explanation => 'Active this if you want to log the circulation return',
501 uniquefieldrequired => 'variable',
502 variable => 'Version',
504 forceupdate => {'explanation' => 1, 'type' => 1},
505 explanation => 'Koha Version',
509 uniquefieldrequired => 'variable',
510 variable => 'LetterLog',
512 forceupdate => {'explanation' => 1, 'type' => 1},
513 explanation => 'Active this if you want to log all the letter sent',
517 uniquefieldrequired => 'variable',
518 variable => 'FinesLog',
520 forceupdate => {'explanation' => 1, 'type' => 1},
521 explanation => 'Active this if you want to log fines',
527 uniquefieldrequired => 'bit',
529 flag => 'editauthorities',
530 flagdesc => 'allow to edit authorities',
534 uniquefieldrequired => 'bit',
537 flagdesc => 'allow to manage serials subscriptions',
541 uniquefieldrequired => 'bit',
544 flagdesc => 'allow to access to the reports module',
548 authorised_values => [
550 uniquefieldrequired => 'id',
551 category => 'SUGGEST',
552 authorised_value => 'Not enoug budget',
553 lib => 'This book it too much expensive',
558 my %fielddefinitions = (
560 # { field => 'fieldname',
561 # type => 'fieldtype',
569 field => 'booksellerid',
584 extra => 'auto_increment',
587 field => 'listprice',
588 type => 'varchar(10)',
595 field => 'invoiceprice',
596 type => 'varchar(10)',
606 field => 'notify_id',
614 field => 'notify_level',
625 { field => 'firstname',
629 { field => 'initials',
633 { field => 'B_email',
636 after => 'B_zipcode',
639 field => 'streetnumber', # street number (hidden if streettable table is empty)
645 field => 'streettype', # street table, list builded from a system table
648 after => 'streetnumber',
655 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
661 field => 'B_streettype', # street table, list builded from a system table
664 after => 'B_streetnumber',
673 field => 'address2', # complement address
685 field => 'contactfirstname', # contact's firstname
688 after => 'contactname',
691 field => 'contacttitle', # contact's title
694 after => 'contactfirstname',
697 field => 'branchcode',
698 type => 'varchar(10)',
704 field => 'categorycode',
705 type => 'varchar(10)',
715 type => 'varchar(25)',
723 type => 'varchar(4)',
733 type => 'varchar(15)',
740 field => 'branchprinter',
741 type => 'varchar(100)',
748 field => 'branchcode',
749 type => 'varchar(10)',
757 field => 'frombranch',
758 type => 'VARCHAR(10)',
766 type => 'VARCHAR(10)',
775 field => 'category_type',
783 field => 'categorycode',
784 type => 'varchar(10)',
792 deletedborrowers => [
793 { field => 'firstname',
797 { field => 'initials',
801 { field => 'B_email',
804 after => 'B_zipcode',
807 field => 'streetnumber', # street number (hidden if streettable table is empty)
813 field => 'streettype', # street table, list builded from a system table
816 after => 'streetnumber',
823 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
829 field => 'B_streettype', # street table, list builded from a system table
832 after => 'B_streetnumber',
841 field => 'address2', # complement address
853 field => 'contactfirstname', # contact's firstname
856 after => 'contactname',
859 field => 'contacttitle', # contact's title
862 after => 'contactfirstname',
868 field => 'borrowernumber',
870 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
876 field => 'itemnumber',
878 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
884 field => 'branchcode',
885 type => 'varchar(10)',
892 field => 'issuedate',
896 default => '0000-00-00',
907 default => '0000-00-00',
911 field => 'cutterextra',
912 type => 'varchar(45)',
919 field => 'issue_date',
927 field => 'holdingbranch',
928 type => 'varchar(10)',
936 type => 'varchar(10)',
946 type => 'varchar(10)',
960 marc_subfield_structure => [
962 field => 'defaultvalue',
971 field => 'expirationdate',
988 field => 'waitingdate',
1008 field => 'dateadded',
1009 type => 'timestamp',
1013 systempreferences => [
1023 field => 'explanation',
1045 # { indexname => 'index detail'
1049 { indexname => 'PRIMARY',
1055 { indexname => 'booksellerid',
1056 content => 'booksellerid',
1060 { indexname => 'basketno',
1061 content => 'basketno',
1064 aqorderbreakdown => [
1065 { indexname => 'ordernumber',
1066 content => 'ordernumber',
1068 { indexname => 'bookfundid',
1069 content => 'bookfundid',
1073 { indexname => 'isbn',
1076 { indexname => 'publishercode',
1077 content => 'publishercode',
1082 indexname => 'branchcode',
1083 content => 'branchcode',
1088 { indexname => 'PRIMARY',
1089 content => 'currency',
1095 indexname => 'categorycode',
1096 content => 'categorycode',
1100 { indexname => 'homebranch',
1101 content => 'homebranch',
1103 { indexname => 'holdingbranch',
1104 content => 'holdingbranch',
1109 indexname => 'itemtype',
1110 content => 'itemtype',
1114 { indexname => 'shelfnumber',
1115 content => 'shelfnumber',
1117 { indexname => 'itemnumber',
1118 content => 'itemnumber',
1122 { indexname => 'PRIMARY',
1129 my %foreign_keys = (
1131 # { key => 'the key in table' (must be indexed)
1132 # foreigntable => 'the foreigntable name', # (the parent)
1133 # foreignkey => 'the foreign key column(s)' # (in the parent)
1134 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1135 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1139 { key => 'shelfnumber',
1140 foreigntable => 'bookshelf',
1141 foreignkey => 'shelfnumber',
1142 onUpdate => 'CASCADE',
1143 onDelete => 'CASCADE',
1145 { key => 'itemnumber',
1146 foreigntable => 'items',
1147 foreignkey => 'itemnumber',
1148 onUpdate => 'CASCADE',
1149 onDelete => 'CASCADE',
1152 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1153 # easily deleted, but branches/itemtype not too easy to empty...
1155 { key => 'biblionumber',
1156 foreigntable => 'biblio',
1157 foreignkey => 'biblionumber',
1158 onUpdate => 'CASCADE',
1159 onDelete => 'CASCADE',
1161 { key => 'itemtype',
1162 foreigntable => 'itemtypes',
1163 foreignkey => 'itemtype',
1164 onUpdate => 'CASCADE',
1165 onDelete => 'RESTRICT',
1169 { key => 'biblioitemnumber',
1170 foreigntable => 'biblioitems',
1171 foreignkey => 'biblioitemnumber',
1172 onUpdate => 'CASCADE',
1173 onDelete => 'CASCADE',
1175 { key => 'homebranch',
1176 foreigntable => 'branches',
1177 foreignkey => 'branchcode',
1178 onUpdate => 'CASCADE',
1179 onDelete => 'RESTRICT',
1181 { key => 'holdingbranch',
1182 foreigntable => 'branches',
1183 foreignkey => 'branchcode',
1184 onUpdate => 'CASCADE',
1185 onDelete => 'RESTRICT',
1189 { key => 'booksellerid',
1190 foreigntable => 'aqbooksellers',
1192 onUpdate => 'CASCADE',
1193 onDelete => 'RESTRICT',
1197 { key => 'basketno',
1198 foreigntable => 'aqbasket',
1199 foreignkey => 'basketno',
1200 onUpdate => 'CASCADE',
1201 onDelete => 'CASCADE',
1203 { key => 'biblionumber',
1204 foreigntable => 'biblio',
1205 foreignkey => 'biblionumber',
1206 onUpdate => 'SET NULL',
1207 onDelete => 'SET NULL',
1211 { key => 'listprice',
1212 foreigntable => 'currency',
1213 foreignkey => 'currency',
1214 onUpdate => 'CASCADE',
1215 onDelete => 'CASCADE',
1217 { key => 'invoiceprice',
1218 foreigntable => 'currency',
1219 foreignkey => 'currency',
1220 onUpdate => 'CASCADE',
1221 onDelete => 'CASCADE',
1224 aqorderbreakdown => [
1225 { key => 'ordernumber',
1226 foreigntable => 'aqorders',
1227 foreignkey => 'ordernumber',
1228 onUpdate => 'CASCADE',
1229 onDelete => 'CASCADE',
1231 { key => 'bookfundid',
1232 foreigntable => 'aqbookfund',
1233 foreignkey => 'bookfundid',
1234 onUpdate => 'CASCADE',
1235 onDelete => 'CASCADE',
1238 branchtransfers => [
1239 { key => 'frombranch',
1240 foreigntable => 'branches',
1241 foreignkey => 'branchcode',
1242 onUpdate => 'CASCADE',
1243 onDelete => 'CASCADE',
1245 { key => 'tobranch',
1246 foreigntable => 'branches',
1247 foreignkey => 'branchcode',
1248 onUpdate => 'CASCADE',
1249 onDelete => 'CASCADE',
1251 { key => 'itemnumber',
1252 foreigntable => 'items',
1253 foreignkey => 'itemnumber',
1254 onUpdate => 'CASCADE',
1255 onDelete => 'CASCADE',
1259 { key => 'categorycode',
1260 foreigntable => 'categories',
1261 foreignkey => 'categorycode',
1262 onUpdate => 'CASCADE',
1263 onDelete => 'CASCADE',
1265 { key => 'itemtype',
1266 foreigntable => 'itemtypes',
1267 foreignkey => 'itemtype',
1268 onUpdate => 'CASCADE',
1269 onDelete => 'CASCADE',
1272 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1274 { key => 'borrowernumber',
1275 foreigntable => 'borrowers',
1276 foreignkey => 'borrowernumber',
1277 onUpdate => 'SET NULL',
1278 onDelete => 'SET NULL',
1280 { key => 'itemnumber',
1281 foreigntable => 'items',
1282 foreignkey => 'itemnumber',
1283 onUpdate => 'SET NULL',
1284 onDelete => 'SET NULL',
1288 { key => 'borrowernumber',
1289 foreigntable => 'borrowers',
1290 foreignkey => 'borrowernumber',
1291 onUpdate => 'CASCADE',
1292 onDelete => 'CASCADE',
1294 { key => 'biblionumber',
1295 foreigntable => 'biblio',
1296 foreignkey => 'biblionumber',
1297 onUpdate => 'CASCADE',
1298 onDelete => 'CASCADE',
1300 { key => 'itemnumber',
1301 foreigntable => 'items',
1302 foreignkey => 'itemnumber',
1303 onUpdate => 'CASCADE',
1304 onDelete => 'CASCADE',
1306 { key => 'branchcode',
1307 foreigntable => 'branches',
1308 foreignkey => 'branchcode',
1309 onUpdate => 'CASCADE',
1310 onDelete => 'CASCADE',
1313 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1314 # but prevent deleting a branch as soon as it has 1 borrower !
1315 { key => 'categorycode',
1316 foreigntable => 'categories',
1317 foreignkey => 'categorycode',
1318 onUpdate => 'RESTRICT',
1319 onDelete => 'RESTRICT',
1321 { key => 'branchcode',
1322 foreigntable => 'branches',
1323 foreignkey => 'branchcode',
1324 onUpdate => 'RESTRICT',
1325 onDelete => 'RESTRICT',
1328 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1329 # but prevent deleting a branch as soon as it has 1 borrower !
1330 { key => 'categorycode',
1331 foreigntable => 'categories',
1332 foreignkey => 'categorycode',
1333 onUpdate => 'RESTRICT',
1334 onDelete => 'RESTRICT',
1336 { key => 'branchcode',
1337 foreigntable => 'branches',
1338 foreignkey => 'branchcode',
1339 onUpdate => 'RESTRICT',
1340 onDelete => 'RESTRICT',
1344 { key => 'borrowernumber',
1345 foreigntable => 'borrowers',
1346 foreignkey => 'borrowernumber',
1347 onUpdate => 'CASCADE',
1348 onDelete => 'CASCADE',
1350 { key => 'itemnumber',
1351 foreigntable => 'items',
1352 foreignkey => 'itemnumber',
1353 onUpdate => 'SET NULL',
1354 onDelete => 'SET NULL',
1357 auth_tag_structure => [
1358 { key => 'authtypecode',
1359 foreigntable => 'auth_types',
1360 foreignkey => 'authtypecode',
1361 onUpdate => 'CASCADE',
1362 onDelete => 'CASCADE',
1365 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1370 my %column_change = (
1374 from => 'emailaddress',
1379 from => 'streetaddress',
1381 after => 'initials',
1384 from => 'faxnumber',
1389 from => 'textmessaging',
1395 to => 'contactnote',
1396 after => 'opacnote',
1399 from => 'physstreet',
1404 from => 'streetcity',
1406 after => 'B_address',
1419 from => 'homezipcode',
1426 after => 'B_zipcode',
1431 after => 'dateenrolled',
1434 from => 'guarantor',
1435 to => 'guarantorid',
1436 after => 'contactname',
1439 from => 'altrelationship',
1440 to => 'relationship',
1441 after => 'borrowernotes',
1445 deletedborrowers => [
1447 from => 'emailaddress',
1452 from => 'streetaddress',
1454 after => 'initials',
1457 from => 'faxnumber',
1462 from => 'textmessaging',
1468 to => 'contactnote',
1469 after => 'opacnote',
1472 from => 'physstreet',
1477 from => 'streetcity',
1479 after => 'B_address',
1492 from => 'homezipcode',
1499 after => 'B_zipcode',
1504 after => 'dateenrolled',
1507 from => 'guarantor',
1508 to => 'guarantorid',
1509 after => 'contactname',
1512 from => 'altrelationship',
1513 to => 'relationship',
1514 after => 'borrowernotes',
1520 # MOVE all tables TO UTF-8 and innoDB
1521 $sth = $dbh->prepare("show table status");
1523 while ( my $table = $sth->fetchrow_hashref ) {
1524 if ($table->{Engine} ne 'InnoDB') {
1525 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1526 print "moving $table->{Name} to InnoDB\n";
1528 next if $table->{Name} eq 'marc_word';
1529 next if $table->{Name} eq 'marc_subfield_table';
1530 next if $table->{Name} eq 'auth_word';
1531 next if $table->{Name} eq 'auth_subfield_table';
1532 unless ($table->{Collation} =~ /^utf8/) {
1533 print "moving $table->{Name} to utf8\n";
1534 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1535 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1536 # 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 !
1542 foreach my $table (keys %column_change) {
1543 $sth = $dbh->prepare("show columns from $table");
1546 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1548 $types{$column}->{type} ="$type";
1549 $types{$column}->{null} = "$null";
1550 $types{$column}->{key} = "$key";
1551 $types{$column}->{default} = "$default";
1552 $types{$column}->{extra} = "$extra";
1554 my $tablerows = $column_change{$table};
1555 foreach my $row ( @$tablerows ) {
1556 if ($types{$row->{from}}->{type}) {
1557 print "altering $table $row->{from} to $row->{to}\n";
1558 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1559 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1561 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1562 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1563 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1564 "$types{$row->{from}}->{extra} after $row->{after} ";
1571 # Enter here the field you want to delete from DB.
1572 # FIXME :: there is a %uselessfield before which seems doing the same things.
1573 my %fieldtodelete = (
1574 # tablename => [fieldname1,fieldname2,...]
1578 print "removing some unused fields...\n";
1579 foreach my $table ( keys %fieldtodelete ) {
1580 foreach my $field ( @{$fieldtodelete{$table}} ){
1581 print "removing ".$field." from ".$table;
1582 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1585 print "Error : $sth->errstr \n";
1590 # Enter here the line you want to remove from DB.
1591 my %linetodelete = (
1592 # table name => where clause.
1593 userflags => "bit = 8", # delete the 'reserveforself' flags
1597 #-------------------
1602 # Get version of MySQL database engine.
1603 my $mysqlversion = `mysqld --version`;
1604 $mysqlversion =~ /Ver (\S*) /;
1606 if ( $mysqlversion ge '3.23' ) {
1607 print "Could convert to MyISAM database tables...\n" unless $silent;
1610 #---------------------------------
1613 # Collect all tables into a list
1614 $sth = $dbh->prepare("show tables");
1616 while ( my ($table) = $sth->fetchrow ) {
1617 $existingtables{$table} = 1;
1621 # Now add any missing tables
1622 foreach $table ( keys %requiretables ) {
1623 unless ( $existingtables{$table} ) {
1624 print "Adding $table table...\n" unless $silent;
1625 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1628 print "Error : $sth->errstr \n";
1634 #---------------------------------
1637 foreach $table ( keys %requirefields ) {
1638 print "Check table $table\n" if $debug and not $silent;
1639 $sth = $dbh->prepare("show columns from $table");
1642 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1644 $types{$column} = $type;
1646 foreach $column ( keys %{ $requirefields{$table} } ) {
1647 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1648 if ( !$types{$column} ) {
1650 # column doesn't exist
1651 print "Adding $column field to $table table...\n" unless $silent;
1652 $query = "alter table $table
1653 add column $column " . $requirefields{$table}->{$column};
1654 print "Execute: $query\n" if $debug;
1655 my $sti = $dbh->prepare($query);
1658 print "**Error : $sti->errstr \n";
1665 foreach $table ( keys %fielddefinitions ) {
1666 print "Check table $table\n" if $debug;
1667 $sth = $dbh->prepare("show columns from $table");
1670 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1672 $definitions->{$column}->{type} = $type;
1673 $definitions->{$column}->{null} = $null;
1674 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1675 $definitions->{$column}->{key} = $key;
1676 $definitions->{$column}->{default} = $default;
1677 $definitions->{$column}->{extra} = $extra;
1679 my $fieldrow = $fielddefinitions{$table};
1680 foreach my $row (@$fieldrow) {
1681 my $field = $row->{field};
1682 my $type = $row->{type};
1683 my $null = $row->{null};
1684 # $null = 'YES' if $row->{null} eq 'NULL';
1685 my $key = $row->{key};
1686 my $default = $row->{default};
1687 # $default="''" unless $default;
1688 my $extra = $row->{extra};
1689 my $def = $definitions->{$field};
1690 my $after = ($row->{after}?" after ".$row->{after}:"");
1692 unless ( $type eq $def->{type}
1693 && $null eq $def->{null}
1694 && $key eq $def->{key}
1695 && $extra eq $def->{extra} )
1697 if ( $null eq '' ) {
1700 if ( $key eq 'PRI' ) {
1701 $key = 'PRIMARY KEY';
1703 unless ( $extra eq 'auto_increment' ) {
1707 # if it's a new column use "add", if it's an old one, use "change".
1709 if ($definitions->{$field}->{type}) {
1710 $action="change $field"
1714 # if it's a primary key, drop the previous pk, before altering the table
1716 if ($key ne 'PRIMARY KEY') {
1717 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1718 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ? $after");
1720 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1721 # something strange : for indexes UNIQUE, they are reported as primary key here.
1722 # but if you try to run with drop primary key, it fails.
1723 # thus, we run the query twice, one will fail, one will succeed.
1725 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ? $after");
1726 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ? $after");
1728 # ALTER TABLE `borrowers` CHANGE `branchcode` `branchcode` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
1729 print " alter or create $field in $table\n" unless $silent;
1730 $sth->execute($default);
1735 print "removing some unused data...\n";
1736 foreach my $table ( keys %linetodelete ) {
1737 foreach my $where ( @{linetodelete{$table}} ){
1738 print "DELETE FROM ".$table." where ".$where;
1740 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1743 print "Error : $sth->errstr \n";
1748 # Populate tables with required data
1750 # synch table and deletedtable.
1751 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1752 my %deletedborrowers;
1753 print "synch'ing $table and deleted$table\n";
1754 $sth = $dbh->prepare("show columns from deleted$table");
1756 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1757 $deletedborrowers{$column}=1;
1759 $sth = $dbh->prepare("show columns from $table");
1762 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1763 unless ($deletedborrowers{$column}) {
1764 my $newcol="alter table deleted$table add $column $type";
1765 if ($null eq 'YES') {
1766 $newcol .= " NULL ";
1768 $newcol .= " NOT NULL ";
1770 $newcol .= "default ".$dbh->quote($default) if $default;
1771 $newcol .= " after $previous" if $previous;
1773 print "creating column $column\n";
1779 # update publisheddate
1781 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1783 my ($emptypublished) = $sth->fetchrow;
1784 if ($emptypublished) {
1785 print "Updating publisheddate\n";
1786 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1788 foreach my $table ( keys %tabledata ) {
1789 print "Checking for data required in table $table...\n" unless $silent;
1790 my $tablerows = $tabledata{$table};
1791 foreach my $row (@$tablerows) {
1792 my $uniquefieldrequired = $row->{uniquefieldrequired};
1793 my $uniquevalue = $row->{$uniquefieldrequired};
1794 my $forceupdate = $row->{forceupdate};
1797 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1799 $sth->execute($uniquevalue);
1801 foreach my $field (keys %$forceupdate) {
1802 if ($forceupdate->{$field}) {
1803 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1804 $sth->execute($row->{$field}, $uniquevalue);
1808 print "Adding row to $table: " unless $silent;
1812 foreach my $field ( keys %$row ) {
1813 next if $field eq 'uniquefieldrequired';
1814 next if $field eq 'forceupdate';
1815 my $value = $row->{$field};
1816 push @values, $value;
1817 print " $field => $value" unless $silent;
1818 $fieldlist .= "$field,";
1819 $placeholders .= "?,";
1821 print "\n" unless $silent;
1822 $fieldlist =~ s/,$//;
1823 $placeholders =~ s/,$//;
1824 print "insert into $table ($fieldlist) values ($placeholders)";
1827 "insert into $table ($fieldlist) values ($placeholders)");
1828 $sth->execute(@values);
1834 # check indexes and create them when needed
1836 print "Checking for index required...\n" unless $silent;
1837 foreach my $table ( keys %indexes ) {
1839 # read all indexes from $table
1841 $sth = $dbh->prepare("show index from $table");
1843 my %existingindexes;
1844 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
1845 $existingindexes{$key_name} = 1;
1847 # read indexes to check
1848 my $tablerows = $indexes{$table};
1849 foreach my $row (@$tablerows) {
1850 my $key_name=$row->{indexname};
1851 if ($existingindexes{$key_name} eq 1) {
1852 # print "$key_name existing";
1854 print "\tCreating index $key_name in $table\n";
1856 if ($row->{indexname} eq 'PRIMARY') {
1857 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
1859 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
1862 print "Error $sql : $dbh->err \n" if $dbh->err;
1868 # check foreign keys and create them when needed
1870 print "Checking for foreign keys required...\n" unless $silent;
1871 foreach my $table ( keys %foreign_keys ) {
1873 # read all indexes from $table
1875 $sth = $dbh->prepare("show table status like '$table'");
1877 my $stat = $sth->fetchrow_hashref;
1878 # read indexes to check
1879 my $tablerows = $foreign_keys{$table};
1880 foreach my $row (@$tablerows) {
1881 my $foreign_table=$row->{foreigntable};
1882 if ($stat->{'Comment'} =~/$foreign_table/) {
1883 # print "$foreign_table existing\n";
1885 print "\tCreating foreign key $foreign_table in $table\n";
1886 # first, drop any orphan value in child table
1887 if ($row->{onDelete} ne "RESTRICT") {
1888 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
1890 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
1892 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
1893 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
1894 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
1897 print "====================
1898 An error occured during :
1900 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).
1901 You can find those values with select
1902 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
1903 ====================\n
1909 # now drop useless tables
1910 foreach $table ( @TableToDelete ) {
1911 if ( $existingtables{$table} ) {
1912 print "Dropping unused table $table\n" if $debug and not $silent;
1913 $dbh->do("drop table $table");
1915 print "Error : $dbh->errstr \n";
1924 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
1927 # 1st, get how many biblio we will have to do...
1928 $sth = $dbh->prepare('select count(*) from marc_biblio');
1930 my ($totaltodo) = $sth->fetchrow;
1932 $sth = $dbh->prepare("show columns from biblio");
1935 my $bibliofwexist=0;
1936 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1937 $bibliofwexist=1 if $column eq 'frameworkcode';
1939 unless ($bibliofwexist) {
1940 print "moving biblioframework to biblio table\n";
1941 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
1942 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
1944 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
1946 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
1947 $sth_update->execute($frameworkcode,$biblionumber);
1949 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1954 # at last, remove useless fields
1955 foreach $table ( keys %uselessfields ) {
1956 my @fields = split /,/,$uselessfields{$table};
1959 foreach my $fieldtodrop (@fields) {
1960 $fieldtodrop =~ s/\t//g;
1961 $fieldtodrop =~ s/\n//g;
1963 $sth = $dbh->prepare("show columns from $table");
1965 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1967 $exists =1 if ($column eq $fieldtodrop);
1970 print "deleting $fieldtodrop field in $table...\n" unless $silent;
1971 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
1978 # Changing aqbookfund's primary key
1980 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
1988 # Revision 1.161 2007/04/13 16:27:55 hdl
1989 # Adding Version variable to systempreferences.
1991 # Revision 1.160 2007/03/19 18:35:13 toins
1992 # - adding default value in marc_subfield_structure.
1993 # - now marc_subfields_structure displays subfields in tab view.
1995 # Revision 1.159 2007/03/16 01:25:09 kados
1996 # Using my precrash CVS copy I did the following:
1998 # cvs -z3 -d:ext:kados@cvs.savannah.nongnu.org:/sources/koha co -P koha
1999 # find koha.precrash -type d -name "CVS" -exec rm -v {} \;
2000 # cp -r koha.precrash/* koha/
2004 # This should in theory put us right back where we were before the crash
2006 # Revision 1.159 2007/03/12 17:52:30 rych
2007 # add pri key to userflags
2009 # Revision 1.158 2007/03/09 15:14:57 tipaul
2010 # rel_3_0 moved to HEAD
2012 # Revision 1.157.2.56 2007/01/31 16:22:54 btoumi
2013 # -add possibility to use isbn with length of 13 characters
2014 # for Import datas in the reservoir.
2015 # -modify isbn field in marc_breeding table (varchar 13)
2016 # -add isbn filter (no - )when u read a notice from reservoir
2017 # -add filter to have right field 100
2019 # Revision 1.157.2.55 2007/01/30 10:50:19 tipaul
2020 # adding 2 usefull indexes to biblioitems table
2022 # Revision 1.157.2.54 2007/01/29 16:45:52 toins
2023 # * adding a new default authorised value : SUGGEST.
2024 # SUGGEST give some reasons to accept or reject a suggestion.
2026 # * default value for borrowersMandatoryfield syspref is now "cardnumber|surname|adress"
2028 # Revision 1.157.2.53 2007/01/26 20:48:37 hdl
2029 # Serials management : Bugfixes + improvements.
2030 # - Partial dates are now managed
2031 # - next Date Calculation with irregularity tested for 1 week and 1 month.
2032 # - manage if subscription is abouttoexpire or expired.
2033 # - Adding some information on serials pages about subscription.
2034 # - Managing irregularity with numbers.
2035 # - Adding Internal Notes in subscription management.
2036 # - Repeating Button above pages.
2038 # Please run Updatedatabase to change irregularity and add internalnotes field to subscription
2040 # Revision 1.157.2.52 2007/01/24 13:57:26 tipaul
2041 # - setting supplierid to auto_increment (HDL : could you check that is works, i'm not 100% sure)
2042 # - removing 22 -> 30 marc_subfield_table -> marcxml stuff, it's now in misc/migration_tools/22_to_30/
2044 # Revision 1.157.2.51 2007/01/18 09:58:45 tipaul
2045 # defaulting NOT NULL fields (to '')
2047 # Revision 1.157.2.50 2007/01/18 09:39:21 tipaul
2048 # issuedate must be defaulted with ' '
2050 # Revision 1.157.2.49 2007/01/18 09:37:30 tipaul
2051 # removing 2 field definitions that were here twice
2053 # Revision 1.157.2.48 2007/01/15 09:55:40 toins
2054 # adding a new logging systempref : FinesLog.
2056 # Revision 1.157.2.47 2007/01/12 18:09:49 toins
2059 # Revision 1.157.2.46 2007/01/11 14:35:39 tipaul
2060 # adding Opac Browser feature : the build_browser_and_cloud.pl script will :
2061 # - 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)
2062 # - fill the tags table, that contains the subject cloud.
2064 # 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 !
2066 # The commit also add the systempreference to hide/show the OpacBrowse in database & in systempref management script.
2068 # IMPROVEMENTS to do :
2069 # - 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).
2070 # - add, in parameters section, a place to edit browser descriptions. The build script has to be updated to to avoid deleting existing browser descriptions.
2072 # Revision 1.157.2.45 2007/01/10 16:52:52 toins
2073 # Value for Log Features syspref are set to 0 by default.
2075 # Revision 1.157.2.44 2007/01/10 16:31:15 toins
2076 # new systems preferences :
2077 # - CataloguingLog (log the update/creation/deletion of a notice if set to 1)
2078 # - BorrowersLog ( idem for borrowers )
2079 # - IssueLog (log all issue if set to 1)
2080 # - ReturnLog (log all return if set to 1)
2081 # - SusbcriptionLog (log all creation/deletion/update of a subcription)
2083 # All of theses are in a new tab called 'LOGFeatures' in systempreferences.pl
2085 # Revision 1.157.2.43 2007/01/10 14:13:17 toins
2086 # opac_news.displayed is replaced by opac_news.number.
2087 # This field say how are ordered the news on the template.
2089 # Revision 1.157.2.42 2007/01/09 14:09:01 toins
2090 # 2 field added to opac_news.('expirationdate' and 'displayed').
2092 # Revision 1.157.2.41 2006/12/22 17:11:33 tipaul
2093 # adding 3 systempreferences for opac features & a new systempref tab where all systempreferences are located
2095 # Revision 1.157.2.40 2006/12/20 16:45:59 tipaul
2097 # - adding a new table : when a biblio is added/modified/ deleted, an entry is entered in this table
2098 # - the zebraqueue_start.pl script read it & does the stuff.
2100 # code coming from head (tumer). it can be run every minut instead of once every day for dev_week code.
2102 # I just have commented the previous code (=real time update) in Biblio.pm, we will be able to reactivate it once indexdata fixes zebra update bug !
2104 # Revision 1.157.2.39 2006/12/20 11:42:17 toins
2105 # adding table "tags"
2107 # Revision 1.157.2.38 2006/12/19 12:06:53 alaurin
2108 # adding a new system preference : RequestOnOpac ;
2110 # adding update database
2112 # Revision 1.157.2.37 2006/12/19 10:49:21 toins
2113 # fix a minor bug in syspref "expandedSearchOption" and adding it on updatedatabase.
2115 # Revision 1.157.2.36 2006/12/13 19:48:09 hdl
2116 # Adding claimdate to serials.
2117 # (Needed to record claimdate
2119 # Revision 1.157.2.35 2006/12/08 15:36:57 hdl
2120 # Adding issuedate to issues table.
2122 # Revision 1.157.2.34 2006/12/07 16:00:41 hdl
2123 # Adding issuedate to table issues.
2124 # Modifying issuedate on issue (Circ2.pm)
2125 # Modifying report issue_avg_stats to take this change into account. (Need TESTING)
2127 # Revision 1.157.2.33 2006/12/06 14:12:18 btoumi
2128 # add BorrowersTitles systempreferences to setup borrowers title
2130 # Revision 1.157.2.32 2006/12/06 13:49:41 toins
2131 # deleting additionalauthors, bibliosubjects, bibliosubtitles.
2133 # Revision 1.157.2.31 2006/12/05 15:07:16 tipaul
2135 # a column (itemtypes.summary) has been added.
2137 # If it is empty, no changes at all.
2138 # In admin/itemtypes.pl, the librarian can go and define what (and how) the record appeard below the title.
2139 # The summary must be entered exactly as the authority summary.
2140 # An example is provided in admin/itemtypes.pl add/modify itemtype.
2142 # This feature had been requested for a while by my librarians. The 2 uses we can imagine are :
2143 # - for websites => show the link directly in the result list, to avoid 1 clic (& there is no need for a size/editor/publicationyear for web sites)
2144 # - for serial publications => show some serial specific informations.
2146 # This commit should do everything that is needed for this feature.
2148 # Revision 1.157.2.30 2006/11/29 11:58:18 toins
2149 # re indenting with space.
2151 # Revision 1.157.2.29 2006/11/24 21:58:35 kados
2152 # changing items.itemtype to items.itype to avoid problems with
2153 # joins with biblioitems. NOTE: I don't think updatedatabase will remove the
2154 # items.itemtype from your db so you must do that manually if you updated
2155 # from an earlier version of updatedatabase.
2157 # Revision 1.157.2.28 2006/11/24 13:54:55 hdl
2158 # Adding serialsadditem
2160 # Revision 1.157.2.27 2006/11/24 11:07:09 alaurin
2163 # Add a new system prefence "AutoLocation"
2164 # this fonction switching activation or not Autolocation, if Yes, the Librarian can't change his location, it's defined by branchip,
2165 # if autolocation is setting to "NO", librarian can change his settings ....
2168 # warn, if autolocation is setting "on", on circulation.pl we don't have anymore the choice to change your library and branchprinter,
2169 # defined on branches : branchip and branchprinter ....
2171 # this function could be improved
2173 # Revision 1.157.2.26 2006/11/23 11:01:06 toins
2174 # branchtransfers.frombranch & branchtransfers.tobranch must be VARCHAR(10)
2176 # Revision 1.157.2.25 2006/11/23 09:05:33 tipaul
2177 # reintroducing move to innoDB (as only innoDB supports extended features like foreign keys)
2179 # Revision 1.157.2.24 2006/11/21 09:15:23 toins
2180 # better userflag description
2182 # Revision 1.157.2.23 2006/11/21 08:51:01 toins
2183 # 2 new userflags: serials & reports.
2185 # Revision 1.157.2.22 2006/11/20 16:59:09 toins
2186 # adding a userflags: 'editauthorities'.
2188 # Revision 1.157.2.21 2006/11/17 10:53:04 hdl
2190 # - subscription detail :
2191 # adding manual history (in subscription table)
2192 # addind subscription summary.
2193 # - menu-serials.inc deleting old link
2194 # - adapting serials-collection.pl
2197 # - Some values are hard coded in subscription-detail
2198 # - subscription-detail.pl relies on subscription-add for edition BUT subscription-add transmits back data to subscription-detail in order to save data back into database. This is a bit odd enough and should not occur.
2199 # - Some more tests on numberlength which doesnot seem to be kept.
2201 # Revision 1.157.2.20 2006/11/15 15:15:50 hdl
2202 # Final First Version for New Facility for subscription management.
2205 # use serials-collection.pl for history display
2206 # and serials-edit.pl for serial edition
2207 # subscription add and detail adds a new branch information to help IndependantBranches Library to manage different subscriptions for a serial
2209 # This is aimed at replacing serials-receive and statecollection.
2211 # Revision 1.157.2.19 2006/11/14 16:28:01 rych
2212 # Adding itemtype field to items
2214 # Revision 1.157.2.18 2006/11/14 16:16:58 rych
2217 # Revision 1.157.2.17 2006/11/14 14:39:31 toins
2218 # * delete the userflags "reservforself" which is unused.
2219 # * some new function not use at the moment.
2221 # Revision 1.157.2.16 2006/11/02 09:27:30 toins
2222 # issue.branchcode must be varchar(10).
2224 # Revision 1.157.2.15 2006/10/31 17:41:51 toins
2225 # items.holdingbranch must be varchar(10)
2227 # Revision 1.157.2.14 2006/10/30 09:41:45 btoumi
2228 # remove auto increment for accountno in accountlines table
2230 # Revision 1.157.2.13 2006/10/20 10:35:05 alaurin
2231 # new program : branchoverdues.pl
2233 # with this program, the librararians will can check , and specify the method of notification of documents in overdue
2235 # little explanation :
2237 # - At first, the datas come from accountlines, generated by accounlines (type 'FU')
2238 # - There is three levels of notification (come from overduerules ....)
2239 # - there is four methods of notification :
2240 # - letter (for us, use an openoffice program ....)
2241 # - Mail (use a batch program)
2242 # - Phone (simple Method, if this method is selected, we consider that the borrower as been notified)
2243 # - Considered Lost (For us the third level)
2245 # - At this time we have some parameters hardcoded (Must be improve later)
2247 # - the choice of methods is hardcoded :
2248 # - for the first overduelevel : three methods : mail,letter,phone
2249 # -For the second overduelevel :only one method : letter
2250 # - For the Third Overdue level : only one method : Considered Lost
2253 # this program will be heavy tested next week ....
2255 # Revision 1.157.2.12 2006/10/19 09:04:07 toins
2256 # itemtypes.itemtype is a primary key.
2258 # Revision 1.157.2.11 2006/10/18 13:31:13 toins
2259 # Borrowers.categorycode must have 10 chars lenght & categories.categorycode must be a primary key.
2261 # Revision 1.157.2.10 2006/10/17 16:18:14 hdl
2262 # Changing primary key in aqbookfund.
2263 # Making it branchcode+aqbookfundid rather than simple aqbookfundid.
2265 # Revision 1.157.2.9 2006/10/16 14:23:47 toins
2266 # Borrowers.branchcode must be varchar(10) too.
2268 # Revision 1.157.2.7 2006/10/11 15:22:23 tipaul
2269 # - adding some missing fields, coming from dev_week :
2270 # * lcsort & ccode in biblioitems table. lcsort is used for loc callnumbers & ccode is used to have a item level circulation rules. Ccode means C<irc>code
2271 # * onloan & issue_date in items table. They are filled by the misc/update_items.pl script, with circulation values. NOW, onloan & issue_date in items are NOT set by circulation, this decision speed up a lot the circulation rate (with the Date::Manip removal). The price for this is to have the status of the item not real time updated in zebra, but that's worth the price.
2272 # * cutterextra in items, that we should ask tumer to understand what it does ;-)
2274 # Revision 1.157.2.6 2006/10/10 11:25:40 btoumi
2275 # add two tables : notifys , charges
2276 # modify accountlines tables add two fields (notify_id and notify_level)
2278 # Revision 1.157.2.5 2006/10/02 09:15:44 hdl
2280 # * synching with NZ-devs on Serials.
2281 # * adding routing lists support,
2282 # * adding serialsadditems support
2283 # * adding publisheddate management
2286 # Management for seasonal serials should be fixed in order to be language independant.
2288 # Revision 1.157.2.4 2006/09/19 07:44:13 btoumi
2289 # bug fix : modify wrong field name BorrowerMandatoryField
2291 # Revision 1.157.2.3 2006/09/18 14:00:24 btoumi
2292 # bug fix :wrond field name for opacnote and contactnote
2294 # Revision 1.157.2.2 2006/09/11 13:24:03 alaurin
2295 # marcxml should be a longtext, some biblios can be more than 65535 char long
2297 # Revision 1.157.2.1 2006/09/04 08:39:14 toins
2298 # sync with rel_2_2.
2300 # Revision 1.157 2006/08/11 10:03:13 tipaul
2301 # the new "includes" features, for personalized templates. Look at koha-devel, i'll write a mail here (& something on the wiki)
2303 # Revision 1.152 2006/06/27 09:26:37 btoumi
2304 # modify (initials,phone ) fields property in borrowers and deletedborrowers table
2306 # Revision 1.151 2006/06/22 10:33:14 btoumi
2307 # sorry i forget deletedborrowers table
2308 # modify firstname field from deletedborrowers table
2310 # Revision 1.149 2006/06/20 22:35:47 rangi
2311 # Code to allow the associated borrowers to work
2313 # Revision 1.148 2006/06/17 22:12:01 rangi
2314 # Adding id field to reviews table
2316 # Revision 1.147 2006/06/17 03:36:41 rangi
2317 # Table definition for the reviews table
2319 # Revision 1.146 2006/06/17 03:29:41 rangi
2320 # Variable to allow librarians to switch reviews on or off
2322 # Revision 1.145 2006/06/16 09:45:02 btoumi
2323 # updatedatabase.pl: add change of borrowers table to deletedborrowers table
2324 # deletemem.pl: delete use of warn function
2326 # Revision 1.144 2006/06/08 15:36:31 alaurin
2327 # Add a new system preference 'AutomaticItemReturn' :
2329 # 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) .
2331 # switch off : the document stay in the holdingbranch ...
2334 # - comment C4::acquisition (not using in request.pl).
2335 # - correcting date in request.pl
2336 # -add the new call of function getbranches in request.pl
2338 # Revision 1.143 2006/06/07 02:02:47 bob_lyon
2339 # merging katipo changes...
2341 # adding new preference IssuingInProcess
2343 # Revision 1.142 2006/06/06 23:42:46 bob_lyon
2344 # Merging Katipo changes...
2346 # Adding new system pref where one can still retrieve a correct reading
2347 # record history if one has moved older data from issues to oldissues table
2348 # to speed up issues speed
2350 # Revision 1.141 2006/06/01 03:18:11 rangi
2351 # Adding a new column to the statistics table
2353 # Revision 1.140 2006/05/22 22:40:45 rangi
2354 # Adding new systempreference allowing for the library to add borrowers to institutions (rest homes, parishes, schools, classes etc).
2356 # Revision 1.139 2006/05/19 19:31:29 tgarip1957
2357 # Added new fields to auth_header and auth_subfield_table to allow ZEBRA use of authorities and new MARC framework like structure.
2358 # 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
2360 # Revision 1.138 2006/05/19 16:51:44 alaurin
2361 # update database for :
2362 # - new feature ip and printer management
2363 # adding two fields in branches table (branchip,branchprinter)
2365 # - 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
2367 # new system preference :
2368 # - ReservesMaxPickUpDelay : Maximum delay to pick up a reserved document
2369 # TransfersMaxDaysWarning : Max delay before considering the transfer as potentialy a problem
2371 # Revision 1.137 2006/04/18 09:36:36 plg
2372 # bug fixed: typo fixed in labels and labels_conf tables creation query.
2374 # Revision 1.136 2006/04/17 21:55:33 sushi
2375 # Added 'labels' and 'labels_conf' tables, for spine lable tool.
2377 # Revision 1.135 2006/04/15 02:37:03 tgarip1957
2378 # Marc record should be set to UTF-8 in leader.Force it.
2379 # XML should be with<record> wrappers
2381 # Revision 1.134 2006/04/14 09:37:29 tipaul
2382 # improvements from SAN Ouest Provence :
2383 # * introducing a category_type into categories. It can be A (adult), C (children), P (Professionnal), I (institution/organisation).
2384 # * each category_type has it's own forms to create members.
2385 # * the borrowers table has been heavily modified (many fields changed), to get something more logic & readable
2386 # * reintroducing guarantor/guanrantee system that is now independant from hardcoded C/A for categories
2387 # * updating templates to fit template rules
2389 # (see mail feb, 17 on koha-devel "new features for borrowers" for more details)
2391 # Revision 1.133 2006/04/13 08:36:42 plg
2392 # new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on
2393 # the system preference prefered date format.
2395 # improvement: book fund list and budget list screen redesigned. Filters on
2396 # each field. Columns are not sortable yet. Using DHTML Calendar to fill date
2397 # fields instead of manual filling. Pagination system. From the book fund
2398 # list, you can reach the budget list, filtered on a book fund, or not. A
2399 # budget can be added only from book fund list screen.
2401 # bug fixed: branchcode was missing in table aqbudget.
2403 # bug fixed: when setting a branchcode to a book fund, all associated budgets
2404 # move to this branchcode.
2406 # modification: when adding/modifying budget/fund, MySQL specific "REPLACE..."
2407 # statements replaced by standard SQL compliant statement.
2409 # bug fixed: when adding/modifying a budget, if the book fund is associated to
2410 # a branch, the branch selection is disabled and set to the book fund branch.
2412 # Revision 1.132 2006/04/06 12:37:05 hdl
2413 # Bugfixing : aqbookfund needed a field.
2415 # Revision 1.131 2006/03/03 17:02:22 tipaul
2416 # commit for holidays and news management.
2417 # (some forgotten files)
2419 # Revision 1.130 2006/03/03 16:35:21 tipaul
2420 # commit for holidays and news management.
2422 # Contrib from Tmer Garip (from Turkey) :
2424 # 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 :
2425 # - single day : only this day is closed
2426 # - repet weekly (like "sunday") : the day is holiday every week
2427 # - repet yearly (like "July, 4") : this day is closed every year.
2429 # You can also put exception :
2430 # - sunday is holiday, but "2006 March, 5th" the library will be open
2432 # 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.
2434 # Revision 1.129 2006/02/27 18:19:33 hdl
2435 # New table used in overduerules.pl tools page.
2437 # Revision 1.128 2006/01/25 15:16:06 tipaul
2439 # * removing useless tables
2440 # * adding useful indexes
2441 # * altering some columns definitions
2442 # * The goal being to have updater working fine for foreign keys.
2444 # 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
2446 # Revision 1.127 2006/01/24 17:57:17 tipaul
2447 # DB improvements : adding foreign keys on some tables. partial stuff done.
2449 # Revision 1.126 2006/01/06 16:39:42 tipaul
2450 # synch'ing head and rel_2_2 (from 2.2.5, including npl templates)
2451 # Seems not to break too many things, but i'm probably wrong here.
2452 # at least, new features/bugfixes from 2.2.5 are here (tested on some features on my head local copy)
2454 # - removing useless directories (koha-html and koha-plucene)
2456 # Revision 1.125 2006/01/04 15:54:55 tipaul
2457 # utf8 is a : go for beta test in HEAD.
2458 # some explanations :
2459 # - 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.
2460 # - *-top.inc will show the pages in utf8
2461 # - 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.
2462 # - using marcxml field and no more the iso2709 raw marc biblioitems.marc field.
2464 # Revision 1.124 2005/10/27 12:09:05 tipaul
2465 # new features for serial module :
2466 # - 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")
2467 # - 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).
2469 # Revision 1.123 2005/10/26 09:13:37 tipaul
2470 # big commit, still breaking things...
2472 # * synch with rel_2_2. Probably the last non manual synch, as rel_2_2 should not be modified deeply.
2473 # * code cleaning (cleaning warnings from perl -w) continued
2475 # Revision 1.122 2005/09/02 14:18:38 tipaul
2476 # new feature : image for itemtypes.
2478 # * run updater/updatedatabase to create imageurl field in itemtypes.
2479 # * 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)
2480 # * go to OPAC, and search something. In the result list, you now have the picture instead of the text itemtype.
2482 # Revision 1.121 2005/08/24 08:49:03 hdl
2483 # Adding a note field in serial table.
2484 # This will allow librarian to mention a note on a peculiar waiting serial number.
2486 # Revision 1.120 2005/08/09 14:10:32 tipaul
2487 # 1st commit to go to zebra.
2488 # don't update your cvs if you want to have a working head...
2490 # this commit contains :
2491 # * 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...
2492 # * 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.
2493 # * other files : get rid of bibid and use biblionumber instead.
2496 # * does not do anything on zebra yet.
2497 # * if you rename marc_subfield_table, you can't search anymore.
2498 # * you can view a biblio & bibliodetails, go to MARC editor, but NOT save any modif.
2499 # * 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 ;-) )
2501 # 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
2502 # 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.
2504 # Revision 1.119 2005/08/04 16:07:58 tipaul
2505 # Synch really broke this script...
2507 # Revision 1.118 2005/08/04 16:02:55 tipaul
2508 # oops... error in synch between 2.2 and head
2510 # Revision 1.117 2005/08/04 14:24:39 tipaul
2511 # synch'ing 2.2 and head
2513 # Revision 1.116 2005/08/04 08:55:54 tipaul
2514 # Letters / alert system, continuing...
2516 # * adding a package Letters.pm, that manages Letters & alerts.
2517 # * 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)
2518 # * 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)
2519 # * 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.
2521 # Note that the system should be generic enough to manage any type of alert.
2522 # 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 ;-) )
2524 # Revision 1.115 2005/08/02 16:15:34 tipaul
2525 # adding 2 fields to letter system :
2526 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
2527 # * title, that will be used as mail subject.
2529 # Revision 1.114 2005/07/28 15:10:13 tipaul
2530 # 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.
2531 # the letter table contains 3 fields :
2532 # * code => the code of the letter
2533 # * name => the complete name of the letter
2534 # * content => the complete text. It's a TEXT field type, so has no limits.
2536 # My next goal now is to work on point 2-I "serial issue alert"
2537 # 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.
2538 # (see mail on koha-devel, 2005/04/07)
2540 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
2542 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
2544 # Revision 1.113 2005/07/28 08:38:41 tipaul
2545 # 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 :
2546 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
2547 # * ReturnBeforeExpiry = no => return date can be after expiry date
2549 # Revision 1.112 2005/07/26 08:19:47 hdl
2550 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
2552 # Revision 1.111 2005/07/25 15:35:38 tipaul
2553 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
2554 # So, the updatedatabase script can highly be cleaned (90% removed).
2555 # Let's play with the new Koha DB structure now ;-)