?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Bug 21899: Update MARC21 frameworks to Update 27 (November 2018)
[koha.git] / installer / data / mysql / update22to30.pl
blobd38f080bcccc389b30e41419ac992b0536c2c538
1 #!/usr/bin/perl
2
3
4 # Database Updater
5 # This script checks for required updates to the database.
6
7 # Part of the Koha Library Software www.koha-community.org
8 # Licensed under the GPL.
9
10 # Bugs/ToDo:
11 # - Would also be a good idea to offer to do a backup at this time...
12
13 # NOTE: If you do something more than once in here, make it table driven.
14 use strict;
15
16 # CPAN modules
17 use DBI;
18 use Getopt::Long;
19 # Koha modules
20 use C4::Context;
21
22 use MARC::Record;
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
24
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
27
28 my $debug = 0;
29
30 my (
31 $sth, $sti,
32 $query,
33 %existingtables, # tables already in database
34 %types,
35 $table,
36 $column,
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
39 );
40
41 my $silent;
42 GetOptions(
43 's' =>\$silent
44 );
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
47
48 my $DBversion = "3.00.00.000";
49 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
50 # Tables to add if they don't exist
51 my %requiretables = (
52 action_logs => "(
53 `timestamp` TIMESTAMP NOT NULL ,
54 `user` INT( 11 ) NOT NULL default '0' ,
55 `module` TEXT default '',
56 `action` TEXT default '' ,
57 `object` INT(11) NULL ,
58 `info` TEXT default '' ,
59 PRIMARY KEY ( `timestamp` , `user` )
60 )",
61 letter => "(
62 module varchar(20) NOT NULL default '',
63 code varchar(20) NOT NULL default '',
64 name varchar(100) NOT NULL default '',
65 title varchar(200) NOT NULL default '',
66 content text,
67 PRIMARY KEY (module,code)
68 )",
69 alert =>"(
70 alertid int(11) NOT NULL auto_increment,
71 borrowernumber int(11) NOT NULL default '0',
72 type varchar(10) NOT NULL default '',
73 externalid varchar(20) NOT NULL default '',
74 PRIMARY KEY (alertid),
75 KEY borrowernumber (borrowernumber),
76 KEY type (type,externalid)
77 )",
78 opac_news => "(
79 `idnew` int(10) unsigned NOT NULL auto_increment,
80 `title` varchar(250) NOT NULL default '',
81 `new` text NOT NULL,
82 `lang` varchar(4) NOT NULL default '',
83 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
84 PRIMARY KEY (`idnew`)
85 )",
86 repeatable_holidays => "(
87 `id` int(11) NOT NULL auto_increment,
88 `branchcode` varchar(10) NOT NULL default '',
89 `weekday` smallint(6) default NULL,
90 `day` smallint(6) default NULL,
91 `month` smallint(6) default NULL,
92 `title` varchar(50) NOT NULL default '',
93 `description` text NOT NULL,
94 PRIMARY KEY (`id`)
95 )",
96 special_holidays => "(
97 `id` int(11) NOT NULL auto_increment,
98 `branchcode` varchar(10) NOT NULL default '',
99 `day` smallint(6) NOT NULL default '0',
100 `month` smallint(6) NOT NULL default '0',
101 `year` smallint(6) NOT NULL default '0',
102 `isexception` smallint(1) NOT NULL default '1',
103 `title` varchar(50) NOT NULL default '',
104 `description` text NOT NULL,
105 PRIMARY KEY (`id`)
106 )",
107 overduerules =>"(`branchcode` varchar(10) NOT NULL default '',
108 `categorycode` varchar(2) NOT NULL default '',
109 `delay1` int(4) default '0',
110 `letter1` varchar(20) default NULL,
111 `debarred1` varchar(1) default '0',
112 `delay2` int(4) default '0',
113 `debarred2` varchar(1) default '0',
114 `letter2` varchar(20) default NULL,
115 `delay3` int(4) default '0',
116 `letter3` varchar(20) default NULL,
117 `debarred3` int(1) default '0',
118 PRIMARY KEY (`branchcode`,`categorycode`)
119 )",
120 cities => "(`cityid` int auto_increment,
121 `city_name` varchar(100) NOT NULL default '',
122 `city_zipcode` varchar(20),
123 PRIMARY KEY (`cityid`)
124 )",
125 roadtype => "(`roadtypeid` int auto_increment,
126 `road_type` varchar(100) NOT NULL default '',
127 PRIMARY KEY (`roadtypeid`)
128 )",
129
130 labels => "(
131 labelid int(11) NOT NULL auto_increment,
132 batch_id varchar(10) NOT NULL default '1',
133 itemnumber varchar(100) NOT NULL default '',
134 timestamp timestamp(14) NOT NULL,
135 PRIMARY KEY (labelid)
136 )",
137
138 labels_conf => "(
139 id int(4) NOT NULL auto_increment,
140 barcodetype char(100) default '',
141 title int(1) default '0',
142 subtitle int(1) default '0',
143 itemtype int(1) default '0',
144 barcode int(1) default '0',
145 dewey int(1) default '0',
146 class int(1) default '0',
147 subclass int(1) default '0',
148 itemcallnumber int(1) default '0',
149 author int(1) default '0',
150 issn int(1) default '0',
151 isbn int(1) default '0',
152 startlabel int(2) NOT NULL default '1',
153 printingtype char(32) default 'BAR',
154 layoutname char(20) NOT NULL default 'TEST',
155 guidebox int(1) default '0',
156 active tinyint(1) default '1',
157 fonttype char(10) collate utf8_unicode_ci default NULL,
158 ccode char(4) collate utf8_unicode_ci default NULL,
159 callnum_split int(1) default NULL,
160 text_justify char(1) collate utf8_unicode_ci default NULL,
161 PRIMARY KEY (id)
162 )",
163 reviews => "(
164 reviewid integer NOT NULL auto_increment,
165 borrowernumber integer,
166 biblionumber integer,
167 review text,
168 approved tinyint,
169 datereviewed datetime,
170 PRIMARY KEY (reviewid)
171 )",
172 subscriptionroutinglist=>"(
173 routingid integer NOT NULL auto_increment,
174 borrowernumber integer,
175 ranking integer,
176 subscriptionid integer,
177 PRIMARY KEY (routingid)
178 )",
179
180 notifys => "(
181 notify_id int(11) NOT NULL default '0',
182 `borrowernumber` int(11) NOT NULL default '0',
183 `itemnumber` int(11) NOT NULL default '0',
184 `notify_date` date default NULL,
185 `notify_send_date` date default NULL,
186 `notify_level` int(1) NOT NULL default '0',
187 `method` varchar(20) NOT NULL default ''
188 )",
189
190 charges => "(
191 `charge_id` varchar(5) NOT NULL default '',
192 `description` text NOT NULL,
193 `amount` decimal(28,6) NOT NULL default '0.000000',
194 `min` int(4) NOT NULL default '0',
195 `max` int(4) NOT NULL default '0',
196 `level` int(1) NOT NULL default '0',
197 PRIMARY KEY (`charge_id`)
198 )",
199 tags => "(
200 `entry` varchar(255) NOT NULL default '',
201 `weight` bigint(20) NOT NULL default '0',
202 PRIMARY KEY (`entry`)
203 )
204 ",
205 zebraqueue => "(
206 `id` int NOT NULL auto_increment,
207 `biblio_auth_number` int(11) NOT NULL default '0',
208 `operation` char(20) NOT NULL default '',
209 `server` char(20) NOT NULL default '',
210 PRIMARY KEY (`id`)
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
212
213 );
214
215 my %requirefields = (
216 subscription => { 'letter' => 'varchar(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date default NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(10) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''},
217 itemtypes => { 'imageurl' => 'varchar(200) NULL'},
218 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
219 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
220 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
221 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => "VARCHAR(45) NULL default ''", 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'},
222 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
223 serial =>{ 'publisheddate' => 'date AFTER planneddate', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
224 statistics => { 'associatedborrower' => 'integer'},
225 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
226 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
227 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
228 },
229 issues =>{ 'issuedate'=>"date NULL default NULL", },
230
231 # tablename => { 'field' => 'fieldtype' },
232 );
233
234 # Enter here the table to delete.
235 my @TableToDelete = qw(
236 additionalauthors
237 bibliosubject
238 bibliosubtitle
239 bibliothesaurus
240 );
241
242 my %uselessfields = (
243 # tablename => "field1,field2",
244 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
245 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
246 items => "multivolumepart,multivolume,binding",
247 deleteditems => "multivolumepart,multivolume,binding",
248 );
249 # the other hash contains other actions that can't be done elsewhere. they are done
250 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
251
252 # The tabledata hash contains data that should be in the tables.
253 # The uniquefieldrequired hash entry is used to determine which (if any) fields
254 # must not exist in the table for this row to be inserted. If the
255 # uniquefieldrequired entry is already in the table, the existing data is not
256 # modified, unless the forceupdate hash entry is also set. Fields in the
257 # anonymous "forceupdate" hash will be forced to be updated to the default
258 # values given in the %tabledata hash.
259
260 my %tabledata = (
261 # tablename => [
262 # { uniquefielrequired => 'fieldname', # the primary key in the table
263 # fieldname => fieldvalue,
264 # fieldname2 => fieldvalue2,
265 # },
266 # ],
267 systempreferences => [
268 {
269 uniquefieldrequired => 'variable',
270 variable => 'useDaysMode',
271 value => 'Calendar',
272 forceupdate => { 'explanation' => 1,
273 'type' => 1},
274 explanation => 'Choose the method for calculating due date: select Calendar to use the holidays module, and Days to ignore the holidays module',
275 type => 'Choice',
276 options => 'Calendar|Days|Datedue'
277 },
278 {
279 uniquefieldrequired => 'variable',
280 variable => 'DebugLevel',
281 value => '0',
282 forceupdate => { 'explanation' => 1,
283 'type' => 1},
284 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
285 type => 'Choice',
286 options => '0|1|2'
287 },
288 {
289 uniquefieldrequired => 'variable',
290 variable => 'BorrowersTitles',
291 value => 'Mr|Mrs|Miss|Ms',
292 forceupdate => { 'explanation' => 1,
293 'type' => 1},
294 explanation => 'List all Titles for borrowers',
295 type => 'free',
296 },
297 {
298 uniquefieldrequired => 'variable',
299 variable => 'BorrowerMandatoryField',
300 value => 'cardnumber|surname|address',
301 forceupdate => { 'explanation' => 1,
302 'type' => 1},
303 explanation => 'List all mandatory fields for borrowers',
304 type => 'free',
305 },
306 {
307 uniquefieldrequired => 'variable',
308 variable => 'borrowerRelationship',
309 value => 'father|mother,grand-mother',
310 forceupdate => { 'explanation' => 1,
311 'type' => 1},
312 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
313 type => 'free',
314 },
315 {
316 uniquefieldrequired => 'variable',
317 variable => 'ReservesMaxPickUpDelay',
318 value => '10',
319 forceupdate => { 'explanation' => 1,
320 'type' => 1},
321 explanation => 'Maximum delay to pick up a reserved document',
322 type => 'free',
323 },
324 {
325 uniquefieldrequired => 'variable',
326 variable => 'TransfersMaxDaysWarning',
327 value => '3',
328 forceupdate => { 'explanation' => 1,
329 'type' => 1},
330 explanation => 'Max delay before considering the transfer has potentialy a problem',
331 type => 'free',
332 },
333 {
334 uniquefieldrequired => 'variable',
335 variable => 'memberofinstitution',
336 value => '0',
337 forceupdate => { 'explanation' => 1,
338 'type' => 1},
339 explanation => 'Are your patrons members of institutions',
340 type => 'YesNo',
341 },
342 {
343 uniquefieldrequired => 'variable',
344 variable => 'ReadingHistory',
345 value => '0',
346 forceupdate => { 'explanation' => 1,
347 'type' => 1},
348 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
349 type => 'YesNo',
350 },
351 {
352 uniquefieldrequired => 'variable',
353 variable => 'IssuingInProcess',
354 value => '0',
355 forceupdate => { 'explanation' => 1,
356 'type' => 1},
357 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
358 type => 'YesNo',
359 },
360 {
361 uniquefieldrequired => 'variable',
362 variable => 'AutomaticItemReturn',
363 value => '1',
364 forceupdate => { 'explanation' => 1,
365 'type' => 1},
366 explanation => 'This Variable allow or not to return automaticly to his homebranch',
367 type => 'YesNo',
368 },
369 {
370 uniquefieldrequired => 'variable',
371 variable => 'reviewson',
372 value => '0',
373 forceupdate => { 'explanation' => 1,
374 'type' => 1},
375 explanation => 'Allows patrons to submit reviews from the opac',
376 type => 'YesNo',
377 },
378 {
379 uniquefieldrequired => 'variable',
380 variable => 'intranet_includes',
381 value => 'includes',
382 forceupdate => { 'explanation' => 1,
383 'type' => 1},
384 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
385 type => 'Free',
386 },
387 {
388 uniquefieldrequired => 'variable',
389 variable => 'AutoLocation',
390 value => '0',
391 forceupdate => { 'explanation' => 1,
392 'type' => 1},
393 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
394 type => 'YesNo',
395 },
396 {
397 uniquefieldrequired => 'variable',
398 variable => 'serialsadditems',
399 value => '0',
400 forceupdate => {
401 'explanation' => 1,
402 'type' => 1
403 },
404 explanation => 'If set, a new item will be automatically added when receiving an issue',
405 type => 'YesNo',
406 },
407 {
408 uniquefieldrequired => 'variable',
409 variable => 'expandedSearchOption',
410 value => '0',
411 forceupdate => {
412 'explanation' => 1,
413 'type' => 1
414 },
415 explanation => 'search among marc field',
416 type => 'YesNo',
417 },
418 {
419 uniquefieldrequired => 'variable',
420 variable => 'RequestOnOpac',
421 value => '1',
422 forceupdate => { 'explanation' => 1,
423 'type' => 1},
424 explanation => 'option to allow reserves on opac',
425 type => 'YesNo',
426 },
427 {
428 uniquefieldrequired => 'variable',
429 variable => 'OpacCloud',
430 value => '1',
431 forceupdate => { 'explanation' => 1,
432 'type' => 1},
433 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
434 type => 'YesNo',
435 },
436 {
437 uniquefieldrequired => 'variable',
438 variable => 'OpacBrowser',
439 value => '1',
440 forceupdate => { 'explanation' => 1,
441 'type' => 1},
442 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
443 type => 'YesNo',
444 },
445 {
446 uniquefieldrequired => 'variable',
447 variable => 'OpacTopissue',
448 value => '0',
449 forceupdate => { 'explanation' => 1,
450 'type' => 1},
451 explanation => 'If ON, enables the \'most popular items\' link on OPAC. Warning, this is an EXPERIMENTAL feature, turning ON may overload your server',
452 type => 'YesNo',
453 },
454 {
455 uniquefieldrequired => 'variable',
456 variable => 'OpacAuthorities',
457 value => '1',
458 forceupdate => { 'explanation' => 1,
459 'type' => 1},
460 explanation => 'Enable / Disable the search authority link on OPAC',
461 type => 'YesNo',
462 },
463 {
464 uniquefieldrequired => 'variable',
465 variable => 'CataloguingLog',
466 value => '0',
467 forceupdate => {'explanation' => 1, 'type' => 1},
468 explanation => 'Active this if you want to log cataloguing action.',
469 type => 'YesNo',
470 },
471 {
472 uniquefieldrequired => 'variable',
473 variable => 'BorrowersLog',
474 value => '0',
475 forceupdate => {'explanation' => 1, 'type' => 1},
476 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
477 type => 'YesNo',
478 },
479 {
480 uniquefieldrequired => 'variable',
481 variable => 'SubscriptionLog',
482 value => '0',
483 forceupdate => {'explanation' => 1, 'type' => 1},
484 explanation => 'Active this if you want to log Subscription action',
485 type => 'YesNo',
486 },
487 {
488 uniquefieldrequired => 'variable',
489 variable => 'IssueLog',
490 value => '0',
491 forceupdate => {'explanation' => 1, 'type' => 1},
492 explanation => 'Active this if you want to log issue.',
493 type => 'YesNo',
494 },
495 {
496 uniquefieldrequired => 'variable',
497 variable => 'ReturnLog',
498 value => '0',
499 forceupdate => {'explanation' => 1, 'type' => 1},
500 explanation => 'Active this if you want to log the circulation return',
501 type => 'YesNo',
502 },
503 {
504 uniquefieldrequired => 'variable',
505 variable => 'Version',
506 value => '3.0',
507 forceupdate => {'explanation' => 1, 'type' => 1},
508 explanation => 'Koha Version',
509 type => 'Free',
510 },
511 {
512 uniquefieldrequired => 'variable',
513 variable => 'LetterLog',
514 value => '0',
515 forceupdate => {'explanation' => 1, 'type' => 1},
516 explanation => 'Active this if you want to log all the letter sent',
517 type => 'YesNo',
518 },
519 {
520 uniquefieldrequired => 'variable',
521 variable => 'FinesLog',
522 value => '0',
523 forceupdate => {'explanation' => 1, 'type' => 1},
524 explanation => 'Active this if you want to log fines',
525 type => 'YesNo',
526 },
527 {
528 uniquefieldrequired => 'variable',
529 variable => 'NoZebra',
530 value => '0',
531 forceupdate => {'explanation' => 1, 'type' => 1},
532 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
533 type => 'YesNo',
534 },
535 {
536 uniquefieldrequired => 'variable',
537 variable => 'NoZebraIndexes',
538 value => '0',
539 forceupdate => {'explanation' => 1, 'type' => 1},
540 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
541 type => 'Free',
542 },
543 {
544 uniquefieldrequired => 'variable',
545 variable => 'uppercasesurnames',
546 value => '0',
547 forceupdate => {'explanation' => 1, 'type' => 1},
548 explanation => "Force Surnames to be uppercase",
549 type => 'YesNo',
550 },
551 ],
552 userflags => [
553 {
554 uniquefieldrequired => 'bit',
555 bit => '14',
556 flag => 'editauthorities',
557 flagdesc => 'allow to edit authorities',
558 defaulton => '0',
559 },
560 {
561 uniquefieldrequired => 'bit',
562 bit => '15',
563 flag => 'serials',
564 flagdesc => 'allow to manage serials subscriptions',
565 defaulton => '0',
566 },
567 {
568 uniquefieldrequired => 'bit',
569 bit => '16',
570 flag => 'reports',
571 flagdesc => 'allow to access to the reports module',
572 defaulton => '0',
573 },
574 ],
575 authorised_values => [
576 {
577 uniquefieldrequired => 'id',
578 category => 'SUGGEST',
579 authorised_value => 'Not enough budget',
580 lib => 'This book it too much expensive',
581 }
582 ],
583 );
584
585 my %fielddefinitions = (
586 # fieldname => [
587 # { field => 'fieldname',
588 # type => 'fieldtype',
589 # null => '',
590 # key => '',
591 # default => ''
592 # },
593 # ],
594 aqbasket => [
595 {
596 field => 'booksellerid',
597 type => 'int(11)',
598 null => 'NOT NULL',
599 key => '',
600 default => '1',
601 extra => '',
602 },
603 {
604 field => 'booksellerinvoicenumber',
605 type => 'mediumtext',
606 null => 'NULL',
607 key => '',
608 default => '',
609 extra => '',
610 },
611 ],
612 aqbookfund => [
613 {
614 field => 'bookfundid',
615 type => 'varchar(10)',
616 null => 'NOT NULL',
617 key => '',
618 default => "''",
619 extra => '',
620 },
621 {
622 field => 'branchcode',
623 type => 'varchar(10)',
624 null => 'NOT NULL',
625 key => '',
626 default => "''",
627 extra => '',
628 },
629 {
630 field => 'bookfundname',
631 type => 'mediumtext',
632 null => 'NULL',
633 key => '',
634 default => '',
635 extra => '',
636 after => 'bookfundid',
637 },
638 ],
639
640 aqbooksellers => [
641 {
642 field => 'id',
643 type => 'int(11)',
644 null => 'NOT NULL',
645 key => 'PRI',
646 default => '',
647 extra => 'auto_increment',
648 },
649 {
650 field => 'currency',
651 type => 'varchar(3)',
652 null => 'NOT NULL',
653 key => '',
654 default => "''",
655 extra => '',
656 },
657 {
658 field => 'listprice',
659 type => 'varchar(10)',
660 null => 'NULL',
661 key => '',
662 default => '',
663 extra => '',
664 },
665 {
666 field => 'invoiceprice',
667 type => 'varchar(10)',
668 null => 'NULL',
669 key => '',
670 default => '',
671 extra => '',
672 },
673 {
674 field => 'invoicedisc',
675 type => 'float(6,4)',
676 null => 'NULL',
677 key => '',
678 default => 'NULL',
679 extra => '',
680 },
681 {
682 field => 'address1',
683 type => 'mediumtext',
684 null => 'NULL',
685 key => '',
686 default => '',
687 extra => '',
688 },
689 {
690 field => 'address2',
691 type => 'mediumtext',
692 null => 'NULL',
693 key => '',
694 default => '',
695 extra => '',
696 },
697 {
698 field => 'address3',
699 type => 'mediumtext',
700 null => 'NULL',
701 key => '',
702 default => '',
703 extra => '',
704 },
705 {
706 field => 'address4',
707 type => 'mediumtext',
708 null => 'NULL',
709 key => '',
710 default => '',
711 extra => '',
712 },
713 {
714 field => 'accountnumber',
715 type => 'mediumtext',
716 null => 'NULL',
717 key => '',
718 default => '',
719 extra => '',
720 },
721 {
722 field => 'othersupplier',
723 type => 'mediumtext',
724 null => 'NULL',
725 key => '',
726 default => '',
727 extra => '',
728 },
729 {
730 field => 'specialty',
731 type => 'mediumtext',
732 null => 'NULL',
733 key => '',
734 default => '',
735 extra => '',
736 },
737 {
738 field => 'booksellerfax',
739 type => 'mediumtext',
740 null => 'NULL',
741 key => '',
742 default => '',
743 extra => '',
744 },
745 {
746 field => 'notes',
747 type => 'mediumtext',
748 null => 'NULL',
749 key => '',
750 default => '',
751 extra => '',
752 },
753 {
754 field => 'bookselleremail',
755 type => 'mediumtext',
756 null => 'NULL',
757 key => '',
758 default => '',
759 extra => '',
760 },
761 {
762 field => 'booksellerurl',
763 type => 'mediumtext',
764 null => 'NULL',
765 key => '',
766 default => '',
767 extra => '',
768 },
769 {
770 field => 'contnotes',
771 type => 'mediumtext',
772 null => 'NULL',
773 key => '',
774 default => '',
775 extra => '',
776 },
777 {
778 field => 'postal',
779 type => 'mediumtext',
780 null => 'NULL',
781 key => '',
782 default => '',
783 extra => '',
784 },
785 ],
786
787 aqbudget => [
788 {
789 field => 'bookfundid',
790 type => 'varchar(10)',
791 null => 'NOT NULL',
792 key => '',
793 default => "''",
794 exra => '',
795 },
796 {
797 field => 'branchcode',
798 type => 'varchar(10)',
799 null => 'NULL',
800 key => '',
801 default => '',
802 exra => '',
803 },
804 ],
805
806 aqorderbreakdown => [
807 {
808 field => 'bookfundid',
809 type => 'varchar(10)',
810 null => 'NOT NULL',
811 key => '',
812 default => "''",
813 exra => '',
814 },
815 {
816 field => 'branchcode',
817 type => 'varchar(10)',
818 null => 'NULL',
819 key => '',
820 default => '',
821 exra => '',
822 },
823 ],
824
825 aqorderdelivery => [
826 {
827 field => 'ordernumber',
828 type => 'date',
829 null => 'NULL',
830 key => '',
831 default => 'NULL',
832 exra => '',
833 },
834 {
835 field => 'deliverycomments',
836 type => 'mediumtext',
837 null => 'NULL',
838 key => '',
839 default => '',
840 exra => '',
841 },
842 ],
843
844 aqorders => [
845 {
846 field => 'title',
847 type => 'mediumtext',
848 null => 'NULL',
849 key => '',
850 default => '',
851 exra => '',
852 },
853 {
854 field => 'currency',
855 type => 'varchar(3)',
856 null => 'NULL',
857 key => '',
858 default => 'NULL',
859 exra => '',
860 },
861 {
862 field => 'booksellerinvoicenumber',
863 type => 'mediumtext',
864 null => 'NULL',
865 key => '',
866 default => '',
867 extra => '',
868 },
869 {
870 field => 'notes',
871 type => 'mediumtext',
872 null => 'NULL',
873 key => '',
874 default => '',
875 extra => '',
876 },
877 {
878 field => 'supplierreference',
879 type => 'mediumtext',
880 null => 'NULL',
881 key => '',
882 default => '',
883 extra => '',
884 },
885 {
886 field => 'purchaseordernumber',
887 type => 'mediumtext',
888 null => 'NULL',
889 key => '',
890 default => '',
891 extra => '',
892 },
893 ],
894
895 accountlines => [
896 {
897 field => 'notify_id',
898 type => 'int(11)',
899 null => 'NOT NULL',
900 key => '',
901 default => '0',
902 extra => '',
903 },
904 {
905 field => 'notify_level',
906 type => 'int(2)',
907 null => 'NOT NULL',
908 key => '',
909 default => '0',
910 extra => '',
911 },
912 {
913 field => 'accountno',
914 type => 'smallint(6)',
915 null => 'NOT NULL',
916 key => '',
917 default => '0',
918 extra => '',
919 },
920 {
921 field => 'description',
922 type => 'mediumtext',
923 null => 'NULL',
924 },
925 {
926 field => 'dispute',
927 type => 'mediumtext',
928 null => 'NULL',
929 },
930
931 ],
932
933 auth_header => [
934 {
935 field => 'authtypecode',
936 type => 'varchar(10)',
937 null => 'NOT NULL',
938 key => '',
939 default => "''",
940 extra => '',
941 },
942 {
943 field => 'datecreated',
944 type => 'date',
945 null => 'NULL',
946 key => '',
947 default => "NULL",
948 extra => '',
949 },
950 {
951 field => 'origincode',
952 type => 'varchar(20)',
953 null => 'NULL',
954 key => '',
955 default => "NULL",
956 extra => '',
957 },
958 {
959 field => 'authtrees',
960 type => 'mediumtext',
961 null => 'NULL',
962 key => '',
963 default => "",
964 extra => '',
965 after => 'origincode',
966 },
967 ],
968
969 auth_subfield_structure => [
970 {
971 field => 'authtypecode',
972 type => 'varchar(10)',
973 null => 'NOT NULL',
974 key => '',
975 default => "''",
976 extra => '',
977 },
978 {
979 field => 'tagfield',
980 type => 'varchar(3)',
981 null => 'NOT NULL',
982 key => '',
983 default => "''",
984 extra => '',
985 },
986 {
987 field => 'tagsubfield',
988 type => 'varchar(1)',
989 null => 'NOT NULL',
990 key => '',
991 default => "''",
992 extra => '',
993 },
994 {
995 field => 'liblibrarian',
996 type => 'varchar(255)',
997 null => 'NOT NULL',
998 key => '',
999 default => "''",
1000 extra => '',
1001 },
1002 {
1003 field => 'libopac',
1004 type => 'varchar(255)',
1005 null => 'NOT NULL',
1006 key => '',
1007 default => "''",
1008 extra => '',
1009 },
1010 {
1011 field => 'authorised_value',
1012 type => 'varchar(10)',
1013 null => 'NULL',
1014 key => '',
1015 default => "NULL",
1016 extra => '',
1017 },
1018 {
1019 field => 'value_builder',
1020 type => 'varchar(80)',
1021 null => 'NULL',
1022 key => '',
1023 default => "NULL",
1024 extra => '',
1025 },
1026 {
1027 field => 'seealso',
1028 type => 'varchar(255)',
1029 null => 'NULL',
1030 key => '',
1031 default => "NULL",
1032 extra => '',
1033 },
1034 {
1035 field => 'kohafield',
1036 type => 'varchar(45)',
1037 null => 'NULL',
1038 key => '',
1039 default => "''",
1040 extra => '',
1041 },
1042 {
1043 field => 'frameworkcode',
1044 type => 'varchar(8)',
1045 null => 'NOT NULL',
1046 key => '',
1047 default => "''",
1048 extra => '',
1049 },
1050 ],
1051
1052 auth_tag_structure => [
1053 {
1054 field => 'authtypecode',
1055 type => 'varchar(10)',
1056 null => 'NOT NULL',
1057 key => '',
1058 default => "''",
1059 extra => '',
1060 },
1061 {
1062 field => 'tagfield',
1063 type => 'varchar(3)',
1064 null => 'NOT NULL',
1065 key => '',
1066 default => "''",
1067 extra => '',
1068 },
1069 {
1070 field => 'liblibrarian',
1071 type => 'varchar(255)',
1072 null => 'NOT NULL',
1073 key => '',
1074 default => "''",
1075 extra => '',
1076 },
1077 {
1078 field => 'libopac',
1079 type => 'varchar(255)',
1080 null => 'NOT NULL',
1081 key => '',
1082 default => "''",
1083 extra => '',
1084 },
1085 {
1086 field => 'authorised_value',
1087 type => 'varchar(10)',
1088 null => 'NULL',
1089 key => '',
1090 default => "NULL",
1091 extra => '',
1092 },
1093 ],
1094
1095 auth_types => [
1096 {
1097 field => 'auth_tag_to_report',
1098 type => 'varchar(3)',
1099 null => 'NOT NULL',
1100 key => '',
1101 default => "''",
1102 extra => '',
1103 },
1104 {
1105 field => 'summary',
1106 type => 'mediumtext',
1107 null => 'NOT NULL',
1108 key => '',
1109 default => '',
1110 extra => '',
1111 },
1112 ],
1113
1114 authorised_values => [
1115 {
1116 field => 'category',
1117 type => 'varchar(10)',
1118 null => 'NOT NULL',
1119 key => '',
1120 default => "''",
1121 extra => '',
1122 },
1123 {
1124 field => 'authorised_value',
1125 type => 'varchar(80)',
1126 null => 'NOT NULL',
1127 key => '',
1128 default => "''",
1129 extra => '',
1130 },
1131 {
1132 field => 'lib',
1133 type => 'varchar(80)',
1134 null => 'NULL',
1135 key => '',
1136 default => 'NULL',
1137 extra => '',
1138 },
1139 ],
1140
1141 biblio_framework => [
1142 {
1143 field => 'frameworkcode',
1144 type => 'varchar(4)',
1145 null => 'NOT NULL',
1146 key => '',
1147 default => "''",
1148 extra => '',
1149 },
1150 {
1151 field => 'frameworktext',
1152 type => 'varchar(255)',
1153 null => 'NOT NULL',
1154 key => '',
1155 default => "''",
1156 extra => '',
1157 },
1158 ],
1159
1160 borrowers => [
1161 {
1162 field => 'cardnumber',
1163 type => 'varchar(16)',
1164 null => 'NULL',
1165 key => '',
1166 default => 'NULL',
1167 extra => '',
1168 },
1169 { field => 'surname',
1170 type => 'mediumtext',
1171 null => 'NOT NULL',
1172 },
1173 { field => 'firstname',
1174 type => 'text',
1175 null => 'NULL',
1176 },
1177 { field => 'title',
1178 type => 'mediumtext',
1179 null => 'NULL',
1180 },
1181 { field => 'othernames',
1182 type => 'mediumtext',
1183 null => 'NULL',
1184 },
1185 { field => 'initials',
1186 type => 'text',
1187 null => 'NULL',
1188 },
1189 { field => 'B_email',
1190 type => 'text',
1191 null => 'NULL',
1192 after => 'B_zipcode',
1193 },
1194 {
1195 field => 'streetnumber', # street number (hidden if streettable table is empty)
1196 type => 'varchar(10)',
1197 null => 'NULL',
1198 after => 'initials',
1199 },
1200 {
1201 field => 'streettype', # street table, list builded from a system table
1202 type => 'varchar(50)',
1203 null => 'NULL',
1204 after => 'streetnumber',
1205 },
1206 { field => 'phone',
1207 type => 'text',
1208 null => 'NULL',
1209 },
1210 {
1211 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1212 type => 'varchar(10)',
1213 null => 'NULL',
1214 after => 'fax',
1215 },
1216 {
1217 field => 'B_streettype', # street table, list builded from a system table
1218 type => 'varchar(50)',
1219 null => 'NULL',
1220 after => 'B_streetnumber',
1221 },
1222 {
1223 field => 'phonepro',
1224 type => 'text',
1225 null => 'NULL',
1226 after => 'fax',
1227 },
1228 {
1229 field => 'address2', # complement address
1230 type => 'text',
1231 null => 'NULL',
1232 after => 'address',
1233 },
1234 {
1235 field => 'emailpro',
1236 type => 'text',
1237 null => 'NULL',
1238 after => 'fax',
1239 },
1240 {
1241 field => 'contactfirstname', # contact's firstname
1242 type => 'text',
1243 null => 'NULL',
1244 after => 'contactname',
1245 },
1246 {
1247 field => 'contacttitle', # contact's title
1248 type => 'text',
1249 null => 'NULL',
1250 after => 'contactfirstname',
1251 },
1252 {
1253 field => 'branchcode',
1254 type => 'varchar(10)',
1255 null => 'NOT NULL',
1256 default => "''",
1257 extra => '',
1258 },
1259 {
1260 field => 'categorycode',
1261 type => 'varchar(10)',
1262 null => 'NOT NULL',
1263 default => "''",
1264 extra => '',
1265 },
1266 {
1267 field => 'address',
1268 type => 'mediumtext',
1269 null => 'NOT NULL',
1270 default => '',
1271 extra => '',
1272 },
1273 {
1274 field => 'email',
1275 type => 'mediumtext',
1276 null => 'NULL',
1277 default => '',
1278 extra => '',
1279 },
1280 {
1281 field => 'B_city',
1282 type => 'mediumtext',
1283 null => 'NULL',
1284 default => '',
1285 extra => '',
1286 },
1287 {
1288 field => 'city',
1289 type => 'mediumtext',
1290 null => 'NOT NULL',
1291 default => '',
1292 extra => '',
1293 },
1294 {
1295 field => 'fax',
1296 type => 'mediumtext',
1297 null => 'NULL',
1298 default => '',
1299 extra => '',
1300 },
1301 {
1302 field => 'B_phone',
1303 type => 'mediumtext',
1304 null => 'NULL',
1305 default => '',
1306 extra => '',
1307 },
1308 {
1309 field => 'contactname',
1310 type => 'mediumtext',
1311 null => 'NULL',
1312 default => '',
1313 extra => '',
1314 },
1315 {
1316 field => 'opacnote',
1317 type => 'mediumtext',
1318 null => 'NULL',
1319 default => '',
1320 extra => '',
1321 },
1322 {
1323 field => 'borrowernotes',
1324 type => 'mediumtext',
1325 null => 'NULL',
1326 default => '',
1327 extra => '',
1328 },
1329 {
1330 field => 'sex',
1331 type => 'varchar(1)',
1332 null => 'NULL',
1333 default => 'NULL',
1334 extra => '',
1335 },
1336 ],
1337
1338 biblioitems => [
1339 {
1340 field => 'itemtype',
1341 type => 'varchar(10)',
1342 null => 'NOT NULL',
1343 key => '',
1344 default => '',
1345 extra => '',
1346 },
1347 {
1348 field => 'lcsort',
1349 type => 'varchar(25)',
1350 null => 'NULL',
1351 key => '',
1352 default => '',
1353 extra => '',
1354 },
1355 {
1356 field => 'ccode',
1357 type => 'varchar(4)',
1358 null => 'NULL',
1359 key => '',
1360 default => '',
1361 extra => '',
1362 },
1363 {
1364 field => 'dewey',
1365 type => 'varchar(30)',
1366 null => 'null',
1367 default => '',
1368 extra => '',
1369 },
1370 {
1371 field => 'publicationyear',
1372 type => 'text',
1373 null => 'null',
1374 default => '',
1375 extra => '',
1376 },
1377 {
1378 field => 'collectiontitle',
1379 type => 'mediumtext',
1380 null => 'null',
1381 default => '',
1382 extra => '',
1383 after => 'volumeddesc',
1384 },
1385 {
1386 field => 'collectionissn',
1387 type => 'text',
1388 null => 'null',
1389 default => '',
1390 extra => '',
1391 after => 'collectiontitle',
1392 },
1393 {
1394 field => 'collectionvolume',
1395 type => 'mediumtext',
1396 null => 'null',
1397 default => '',
1398 extra => '',
1399 after => 'collectionissn',
1400 },
1401 {
1402 field => 'editionstatement',
1403 type => 'text',
1404 null => 'null',
1405 default => '',
1406 extra => '',
1407 after => 'collectionvolume',
1408 },
1409 {
1410 field => 'editionresponsibility',
1411 type => 'text',
1412 null => 'null',
1413 default => '',
1414 extra => '',
1415 after => 'editionstatement',
1416 },
1417 {
1418 field => 'volume',
1419 type => 'mediumtext',
1420 null => 'NULL',
1421 default => '',
1422 extra => '',
1423 },
1424 {
1425 field => 'number',
1426 type => 'mediumtext',
1427 null => 'NULL',
1428 default => '',
1429 extra => '',
1430 },
1431 {
1432 field => 'notes',
1433 type => 'mediumtext',
1434 null => 'NULL',
1435 default => '',
1436 extra => '',
1437 },
1438 ],
1439
1440 biblio => [
1441 {
1442 field => 'author',
1443 type => 'mediumtext',
1444 null => 'NULL',
1445 default => '',
1446 extra => '',
1447 },
1448 {
1449 field => 'title',
1450 type => 'mediumtext',
1451 null => 'NULL',
1452 default => '',
1453 extra => '',
1454 },
1455 {
1456 field => 'unititle',
1457 type => 'mediumtext',
1458 null => 'NULL',
1459 default => '',
1460 extra => '',
1461 },
1462 {
1463 field => 'seriestitle',
1464 type => 'mediumtext',
1465 null => 'NULL',
1466 default => '',
1467 extra => '',
1468 },
1469 {
1470 field => 'abstract',
1471 type => 'mediumtext',
1472 null => 'NULL',
1473 default => '',
1474 extra => '',
1475 },
1476 {
1477 field => 'notes',
1478 type => 'mediumtext',
1479 null => 'NULL',
1480 default => '',
1481 extra => '',
1482 },
1483 {
1484 field => 'frameworkcode',
1485 type => 'varchar(4)',
1486 null => 'NOT NULL',
1487 default => "''",
1488 extra => '',
1489 after => 'biblionumber',
1490 },
1491 ],
1492
1493 deletedbiblio => [
1494 {
1495 field => 'author',
1496 type => 'mediumtext',
1497 null => 'NULL',
1498 default => '',
1499 extra => '',
1500 },
1501 {
1502 field => 'title',
1503 type => 'mediumtext',
1504 null => 'NULL',
1505 default => '',
1506 extra => '',
1507 },
1508 {
1509 field => 'unititle',
1510 type => 'mediumtext',
1511 null => 'NULL',
1512 default => '',
1513 extra => '',
1514 },
1515 {
1516 field => 'seriestitle',
1517 type => 'mediumtext',
1518 null => 'NULL',
1519 default => '',
1520 extra => '',
1521 },
1522 {
1523 field => 'abstract',
1524 type => 'mediumtext',
1525 null => 'NULL',
1526 default => '',
1527 extra => '',
1528 },
1529 {
1530 field => 'notes',
1531 type => 'mediumtext',
1532 null => 'NULL',
1533 default => '',
1534 extra => '',
1535 },
1536 {
1537 field => 'frameworkcode',
1538 type => 'varchar(4)',
1539 null => 'NOT NULL',
1540 default => "''",
1541 extra => '',
1542 after => 'biblionumber',
1543 },
1544 ],
1545 deletedbiblioitems => [
1546 {
1547 field => 'itemtype',
1548 type => 'varchar(10)',
1549 null => 'NOT NULL',
1550 default => '',
1551 extra => '',
1552 },
1553 {
1554 field => 'dewey',
1555 type => 'varchar(30)',
1556 null => 'null',
1557 default => '',
1558 extra => '',
1559 },
1560 {
1561 field => 'itemtype',
1562 type => 'varchar(10)',
1563 null => 'NULL',
1564 default => 'NULL',
1565 extra => '',
1566 },
1567 {
1568 field => 'volume',
1569 type => 'mediumtext',
1570 null => 'NULL',
1571 default => '',
1572 extra => '',
1573 },
1574 {
1575 field => 'notes',
1576 type => 'mediumtext',
1577 null => 'NULL',
1578 default => '',
1579 extra => '',
1580 },
1581 {
1582 field => 'number',
1583 type => 'mediumtext',
1584 null => 'NULL',
1585 default => '',
1586 extra => '',
1587 },
1588 ],
1589
1590 bookshelf => [
1591 {
1592 field => 'shelfname',
1593 type => 'varchar(255)',
1594 null => 'NULL',
1595 default => 'NULL',
1596 extra => '',
1597 },
1598 {
1599 field => 'owner',
1600 type => 'varchar(80)',
1601 null => 'NULL',
1602 default => 'NULL',
1603 extra => '',
1604 },
1605 {
1606 field => 'category',
1607 type => 'varchar(1)',
1608 null => 'NULL',
1609 default => 'NULL',
1610 extra => '',
1611 },
1612 ],
1613
1614 branchcategories => [
1615 {
1616 field => 'codedescription',
1617 type => 'mediumtext',
1618 null => 'NULL',
1619 default => '',
1620 extra => '',
1621 },
1622 ],
1623
1624 branches => [
1625 {
1626 field => 'branchip',
1627 type => 'varchar(15)',
1628 null => 'NULL',
1629 key => '',
1630 default => '',
1631 extra => '',
1632 },
1633 {
1634 field => 'branchprinter',
1635 type => 'varchar(100)',
1636 null => 'NULL',
1637 key => '',
1638 default => '',
1639 extra => '',
1640 },
1641 {
1642 field => 'branchcode',
1643 type => 'varchar(10)',
1644 null => 'NOT NULL',
1645 default => "''",
1646 extra => '',
1647 },
1648 {
1649 field => 'branchname',
1650 type => 'mediumtext',
1651 null => 'NOT NULL',
1652 default => '',
1653 extra => '',
1654 },
1655 {
1656 field => 'branchaddress1',
1657 type => 'mediumtext',
1658 null => 'NULL',
1659 default => '',
1660 extra => '',
1661 },
1662 {
1663 field => 'branchaddress2',
1664 type => 'mediumtext',
1665 null => 'NULL',
1666 default => '',
1667 extra => '',
1668 },
1669 {
1670 field => 'branchaddress3',
1671 type => 'mediumtext',
1672 null => 'NULL',
1673 default => '',
1674 extra => '',
1675 },
1676 {
1677 field => 'branchphone',
1678 type => 'mediumtext',
1679 null => 'NULL',
1680 default => '',
1681 extra => '',
1682 },
1683 {
1684 field => 'branchfax',
1685 type => 'mediumtext',
1686 null => 'NULL',
1687 default => '',
1688 extra => '',
1689 },
1690 {
1691 field => 'branchemail',
1692 type => 'mediumtext',
1693 null => 'NULL',
1694 default => '',
1695 extra => '',
1696 },
1697 ],
1698
1699 branchrelations => [
1700 {
1701 field => 'branchcode',
1702 type => 'VARCHAR(10)',
1703 null => 'NOT NULL',
1704 key => '',
1705 default => "''",
1706 extra => '',
1707 },
1708 {
1709 field => 'categorycode',
1710 type => 'VARCHAR(10)',
1711 null => 'NOT NULL',
1712 key => '',
1713 default => "''",
1714 extra => '',
1715 }
1716 ],
1717
1718 branchtransfers =>[
1719 {
1720 field => 'frombranch',
1721 type => 'VARCHAR(10)',
1722 null => 'NOT NULL',
1723 key => '',
1724 default => "''",
1725 extra => '',
1726 },
1727 {
1728 field => 'tobranch',
1729 type => 'VARCHAR(10)',
1730 null => 'NOT NULL',
1731 key => '',
1732 default => "''",
1733 },
1734 {
1735 field => 'comments',
1736 type => 'mediumtext',
1737 null => 'NULL',
1738 key => '',
1739 default => '',
1740 },
1741 ],
1742
1743 categories => [
1744 {
1745 field => 'category_type',
1746 type => 'varchar(1)',
1747 null => 'NOT NULL',
1748 key => '',
1749 default => 'A',
1750 extra => '',
1751 },
1752 {
1753 field => 'categorycode',
1754 type => 'varchar(10)',
1755 null => 'NOT NULL',
1756 key => 'PRI',
1757 default => "''",
1758 extra => '',
1759 },
1760 {
1761 field => 'description',
1762 type => 'mediumtext',
1763 null => 'NULL',
1764 key => '',
1765 default => '',
1766 extra => '',
1767 },
1768 ],
1769
1770 deletedborrowers => [
1771 {
1772 field => 'branchcode',
1773 type => 'varchar(10)',
1774 null => 'NOT NULL',
1775 default => "''",
1776 extra => '',
1777 },
1778 {
1779 field => 'categorycode',
1780 type => 'varchar(2)',
1781 null => 'NULL',
1782 default => 'NULL',
1783 extra => '',
1784 },
1785 {
1786 field => 'B_phone',
1787 type => 'mediumtext',
1788 null => 'NULL',
1789 default => '',
1790 extra => '',
1791 },
1792 {
1793 field => 'borrowernotes',
1794 type => 'mediumtext',
1795 null => 'NULL',
1796 default => '',
1797 extra => '',
1798 },
1799 {
1800 field => 'contactname',
1801 type => 'mediumtext',
1802 null => 'NULL',
1803 default => '',
1804 extra => '',
1805 },
1806 {
1807 field => 'B_city',
1808 type => 'mediumtext',
1809 null => 'NULL',
1810 default => '',
1811 extra => '',
1812 },
1813 {
1814 field => 'B_zipcode',
1815 type => 'varchar(25)',
1816 null => 'NULL',
1817 default => 'NULL',
1818 extra => '',
1819 },
1820 {
1821 field => 'zipcode',
1822 type => 'varchar(25)',
1823 null => 'NULL',
1824 default => 'NULL',
1825 extra => '',
1826 after => 'city',
1827 },
1828 {
1829 field => 'email',
1830 type => 'mediumtext',
1831 null => 'NULL',
1832 default => '',
1833 extra => '',
1834 },
1835 {
1836 field => 'address',
1837 type => 'mediumtext',
1838 null => 'NOT NULL',
1839 default => '',
1840 extra => '',
1841 },
1842 {
1843 field => 'fax',
1844 type => 'mediumtext',
1845 null => 'NULL',
1846 default => '',
1847 extra => '',
1848 },
1849 {
1850 field => 'city',
1851 type => 'mediumtext',
1852 null => 'NOT NULL',
1853 default => '',
1854 extra => '',
1855 },
1856 { field => 'surname',
1857 type => 'mediumtext',
1858 null => 'NOT NULL',
1859 },
1860 { field => 'firstname',
1861 type => 'text',
1862 null => 'NULL',
1863 },
1864 { field => 'initials',
1865 type => 'text',
1866 null => 'NULL',
1867 },
1868 { field => 'title',
1869 type => 'mediumtext',
1870 null => 'NULL',
1871 },
1872 { field => 'othernames',
1873 type => 'mediumtext',
1874 null => 'NULL',
1875 },
1876 { field => 'B_email',
1877 type => 'text',
1878 null => 'NULL',
1879 after => 'B_zipcode',
1880 },
1881 {
1882 field => 'streetnumber', # street number (hidden if streettable table is empty)
1883 type => 'varchar(10)',
1884 null => 'NULL',
1885 default => 'NULL',
1886 after => 'initials',
1887 },
1888 {
1889 field => 'streettype', # street table, list builded from a system table
1890 type => 'varchar(50)',
1891 null => 'NULL',
1892 default => 'NULL',
1893 after => 'streetnumber',
1894 },
1895 { field => 'phone',
1896 type => 'text',
1897 null => 'NULL',
1898 },
1899 {
1900 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
1901 type => 'varchar(10)',
1902 null => 'NULL',
1903 after => 'fax',
1904 },
1905 {
1906 field => 'B_streettype', # street table, list builded from a system table
1907 type => 'varchar(50)',
1908 null => 'NULL',
1909 after => 'B_streetnumber',
1910 },
1911 {
1912 field => 'phonepro',
1913 type => 'text',
1914 null => 'NULL',
1915 after => 'fax',
1916 },
1917 {
1918 field => 'address2', # complement address
1919 type => 'text',
1920 null => 'NULL',
1921 after => 'address',
1922 },
1923 {
1924 field => 'emailpro',
1925 type => 'text',
1926 null => 'NULL',
1927 after => 'fax',
1928 },
1929 {
1930 field => 'contactfirstname', # contact's firstname
1931 type => 'text',
1932 null => 'NULL',
1933 after => 'contactname',
1934 },
1935 {
1936 field => 'contacttitle', # contact's title
1937 type => 'text',
1938 null => 'NULL',
1939 after => 'contactfirstname',
1940 },
1941 {
1942 field => 'sex',
1943 type => 'varchar(1)',
1944 null => 'NULL',
1945 default => 'NULL',
1946 extra => '',
1947 },
1948 ],
1949
1950 issues => [
1951 {
1952 field => 'borrowernumber',
1953 type => 'int(11)',
1954 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1955 key => '',
1956 default => '',
1957 extra => '',
1958 },
1959 {
1960 field => 'itemnumber',
1961 type => 'int(11)',
1962 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1963 key => '',
1964 default => '',
1965 extra => '',
1966 },
1967 {
1968 field => 'branchcode',
1969 type => 'varchar(10)',
1970 null => 'NULL',
1971 key => '',
1972 default => '',
1973 extra => '',
1974 },
1975 {
1976 field => 'issuedate',
1977 type => 'date',
1978 null => 'NULL',
1979 key => '',
1980 default => '',
1981 extra => '',
1982 },
1983 {
1984 field => 'return',
1985 type => 'varchar(4)',
1986 null => 'NULL',
1987 key => '',
1988 default => 'NULL',
1989 extra => '',
1990 },
1991 {
1992 field => 'issuingbranch',
1993 type => 'varchar(18)',
1994 null => 'NULL',
1995 key => '',
1996 default => '',
1997 extra => '',
1998 },
1999 ],
2000 issuingrules => [
2001 {
2002 field => 'categorycode',
2003 type => 'varchar(10)',
2004 null => 'NOT NULL',
2005 default => "''",
2006 extra => '',
2007 },
2008 {
2009 field => 'branchcode',
2010 type => 'varchar(10)',
2011 null => 'NOT NULL',
2012 default => "''",
2013 extra => '',
2014 },
2015 {
2016 field => 'itemtype',
2017 type => 'varchar(10)',
2018 null => 'NOT NULL',
2019 default => "''",
2020 extra => '',
2021 },
2022 ],
2023
2024 items => [
2025 {
2026 field => 'onloan',
2027 type => 'date',
2028 null => 'NULL',
2029 key => '',
2030 default => '',
2031 extra => '',
2032 },
2033 {
2034 field => 'cutterextra',
2035 type => 'varchar(45)',
2036 null => 'NULL',
2037 key => '',
2038 default => '',
2039 extra => '',
2040 },
2041 {
2042 field => 'homebranch',
2043 type => 'varchar(10)',
2044 null => 'NULL',
2045 key => '',
2046 default => '',
2047 extra => '',
2048 },
2049 {
2050 field => 'holdingbranch',
2051 type => 'varchar(10)',
2052 null => 'NULL',
2053 key => '',
2054 default => '',
2055 extra => '',
2056 },
2057 {
2058 field => 'itype',
2059 type => 'varchar(10)',
2060 null => 'NULL',
2061 key => '',
2062 default => '',
2063 extra => '',
2064 },
2065 {
2066 field => 'paidfor',
2067 type => 'mediumtext',
2068 null => 'NULL',
2069 key => '',
2070 default => '',
2071 extra => '',
2072 },
2073 {
2074 field => 'itemnotes',
2075 type => 'mediumtext',
2076 null => 'NULL',
2077 key => '',
2078 default => '',
2079 extra => '',
2080 },
2081 ],
2082
2083 deleteditems => [
2084 {
2085 field => 'paidfor',
2086 type => 'mediumtext',
2087 null => 'NULL',
2088 key => '',
2089 default => '',
2090 extra => '',
2091 },
2092 {
2093 field => 'itemnotes',
2094 type => 'mediumtext',
2095 null => 'NULL',
2096 key => '',
2097 default => '',
2098 extra => '',
2099 },
2100 ],
2101
2102 itemtypes => [
2103 {
2104 field => 'itemtype',
2105 type => 'varchar(10)',
2106 default => "''",
2107 null => 'NOT NULL',
2108 key => 'PRI',
2109 extra => 'UNIQUE',
2110 },
2111 {
2112 field => 'description',
2113 type => 'MEDIUMTEXT',
2114 null => 'NULL',
2115 key => '',
2116 extra => '',
2117 },
2118 {
2119 field => 'summary',
2120 type => 'TEXT',
2121 null => 'NULL',
2122 key => '',
2123 extra => '',
2124 },
2125 ],
2126 marc_breeding => [
2127 {
2128 field => 'marc',
2129 type => 'LONGBLOB',
2130 null => 'NULL',
2131 key => '',
2132 extra => '',
2133 }
2134 ],
2135 marc_subfield_structure => [
2136 {
2137 field => 'defaultvalue',
2138 type => 'TEXT',
2139 null => 'NULL',
2140 key => '',
2141 extra => '',
2142 },
2143 {
2144 field => 'authtypecode',
2145 type => 'varchar(20)',
2146 null => 'NULL',
2147 key => '',
2148 default => 'NULL',
2149 extra => '',
2150 },
2151 {
2152 field => 'tagfield',
2153 type => 'varchar(3)',
2154 null => 'NOT NULL',
2155 key => '',
2156 default => "''",
2157 extra => '',
2158 },
2159 {
2160 field => 'tagsubfield',
2161 type => 'varchar(1)',
2162 null => 'NOT NULL',
2163 key => '',
2164 default => "''",
2165 extra => '',
2166 },
2167 {
2168 field => 'authorised_value',
2169 type => 'varchar(20)',
2170 null => 'NULL',
2171 key => '',
2172 default => "NULL",
2173 extra => '',
2174 },
2175 {
2176 field => 'seealso',
2177 type => 'varchar(1100)',
2178 null => 'NULL',
2179 key => '',
2180 default => "NULL",
2181 extra => '',
2182 },
2183 ],
2184
2185 marc_tag_structure => [
2186 {
2187 field => 'tagfield',
2188 type => 'varchar(3)',
2189 null => 'NOT NULL',
2190 key => '',
2191 default => "''",
2192 extra => '',
2193 },
2194 {
2195 field => 'liblibrarian',
2196 type => 'varchar(255)',
2197 null => 'NOT NULL',
2198 key => '',
2199 default => "''",
2200 extra => '',
2201 },
2202 {
2203 field => 'libopac',
2204 type => 'varchar(255)',
2205 null => 'NOT NULL',
2206 key => '',
2207 default => "''",
2208 extra => '',
2209 },
2210 {
2211 field => 'authorised_value',
2212 type => 'varchar(10)',
2213 null => 'NULL',
2214 key => '',
2215 default => "NULL",
2216 extra => '',
2217 },
2218 {
2219 field => 'frameworkcode',
2220 type => 'varchar(4)',
2221 null => 'NOT NULL',
2222 key => '',
2223 default => "''",
2224 extra => '',
2225 },
2226 ],
2227
2228 opac_news => [
2229 {
2230 field => 'expirationdate',
2231 type => 'date',
2232 null => 'null',
2233 key => '',
2234 extra => '',
2235 },
2236 {
2237 field => 'number',
2238 type => 'int(11)',
2239 null => 'NULL',
2240 key => '',
2241 default => '',
2242 extra => '',
2243 },
2244 ],
2245
2246 printers => [
2247 {
2248 field => 'printername',
2249 type => 'varchar(40)',
2250 null => 'NOT NULL',
2251 key => '',
2252 default => "''",
2253 extra => '',
2254 },
2255 {
2256 field => 'printqueue',
2257 type => 'varchar(20)',
2258 null => 'NULL',
2259 key => '',
2260 default => "NULL",
2261 extra => '',
2262 },
2263 {
2264 field => 'printtype',
2265 type => 'varchar(20)',
2266 null => 'NULL',
2267 key => '',
2268 default => "NULL",
2269 extra => '',
2270 },
2271 ],
2272
2273 reserveconstraints => [
2274 {
2275 field => 'reservedate',
2276 type => 'date',
2277 null => 'NULL',
2278 key => '',
2279 default => 'NULL',
2280 extra => '',
2281 },
2282 ],
2283
2284 reserves => [
2285 {
2286 field => 'waitingdate',
2287 type => 'date',
2288 null => 'NULL',
2289 key => '',
2290 default => '',
2291 extra => '',
2292 },
2293 {
2294 field => 'reservedate',
2295 type => 'date',
2296 null => 'NULL',
2297 key => '',
2298 default => '',
2299 extra => '',
2300 },
2301 {
2302 field => 'constrainttype',
2303 type => 'varchar(1)',
2304 null => 'NULL',
2305 key => '',
2306 default => 'NULL',
2307 extra => '',
2308 after => 'biblionumber',
2309 },
2310 {
2311 field => 'branchcode',
2312 type => 'varchar(10)',
2313 null => 'NULL',
2314 key => '',
2315 default => '',
2316 extra => '',
2317 },
2318 {
2319 field => 'reservenotes',
2320 type => 'mediumtext',
2321 null => 'NULL',
2322 key => '',
2323 default => '',
2324 extra => '',
2325 },
2326 {
2327 field => 'found',
2328 type => 'varchar(1)',
2329 null => 'NULL',
2330 key => '',
2331 default => '',
2332 extra => '',
2333 },
2334 ],
2335
2336 serial => [
2337 {
2338 field => 'planneddate',
2339 type => 'DATE',
2340 null => 'NULL',
2341 key => '',
2342 default => 'NULL',
2343 extra => '',
2344 },
2345 {
2346 field => 'notes',
2347 type => 'TEXT',
2348 null => 'NULL',
2349 key => '',
2350 default => '',
2351 extra => '',
2352 after => 'planneddate',
2353 },
2354 ],
2355
2356 shelfcontents => [
2357 {
2358 field => 'dateadded',
2359 type => 'timestamp',
2360 null => 'NULL',
2361 },
2362 ],
2363
2364 statistics => [
2365 {
2366 field => 'branch',
2367 type => 'varchar(10)',
2368 null => 'NOT NULL',
2369 },
2370 {
2371 field => 'datetime',
2372 type => 'datetime',
2373 null => 'NULL',
2374 default => 'NULL',
2375 },
2376 {
2377 field => 'itemtype',
2378 type => 'varchar(10)',
2379 null => 'NULL',
2380 },
2381 {
2382 field => 'other',
2383 type => 'mediumtext',
2384 null => 'NULL',
2385 },
2386 ],
2387
2388 subscription => [
2389 {
2390 field => 'startdate',
2391 type => 'date',
2392 null => 'NULL',
2393 key => '' ,
2394 default => 'NULL',
2395 extra => '',
2396 },
2397 {
2398 field => 'notes',
2399 type => 'mediumtext',
2400 null => 'NULL',
2401 key => '' ,
2402 default => '',
2403 extra => '',
2404 },
2405 {
2406 field => 'monthlength',
2407 type => 'int(11)',
2408 null => 'NULL',
2409 key => '' ,
2410 default => '0',
2411 extra => '',
2412 },
2413 ],
2414
2415 subscriptionhistory => [
2416 {
2417 field => 'histstartdate',
2418 type => 'date',
2419 null => 'NULL',
2420 key => '' ,
2421 default => 'NULL',
2422 extra => '',
2423 },
2424 {
2425 field => 'enddate',
2426 type => 'date',
2427 null => 'NULL',
2428 key => '' ,
2429 default => 'NULL',
2430 extra => '',
2431 },
2432 ],
2433
2434 systempreferences => [
2435 {
2436 field => 'options',
2437 type => 'mediumtext',
2438 null => 'NULL',
2439 key => '' ,
2440 default => '',
2441 extra => '',
2442 },
2443 {
2444 field => 'value',
2445 type => 'text',
2446 null => 'NULL',
2447 key => '',
2448 default => '',
2449 extra => '',
2450 },
2451 {
2452 field => 'explanation',
2453 type => 'text',
2454 null => 'NULL',
2455 key => '',
2456 default => '',
2457 extra => '',
2458 },
2459 ],
2460 suggestions => [
2461 {
2462 field => 'reason',
2463 type => 'text',
2464 null => 'NULL',
2465 key => '' ,
2466 default => '',
2467 extra => '',
2468 },
2469 {
2470 field => 'note',
2471 type => 'mediumtext',
2472 null => 'NULL',
2473 key => '' ,
2474 default => '',
2475 extra => '',
2476 },
2477 ],
2478 userflags => [
2479 {
2480 field => 'flag',
2481 type => 'varchar(30)',
2482 null => 'NULL',
2483 key => '' ,
2484 default => '',
2485 extra => '',
2486 },
2487 {
2488 field => 'flagdesc',
2489 type => 'varchar(255)',
2490 null => 'NULL',
2491 key => '' ,
2492 default => '',
2493 extra => '',
2494 },
2495 ],
2496 z3950servers => [
2497 {
2498 field => 'name',
2499 type => 'mediumtext',
2500 null => 'NULL',
2501 key => '' ,
2502 default => '',
2503 extra => '',
2504 },
2505 ],
2506 );
2507
2508 my %indexes = (
2509 # table => [
2510 # { indexname => 'index detail'
2511 # }
2512 # ],
2513 accountoffsets => [
2514 { indexname => 'accountoffsets_ibfk_1',
2515 content => 'borrowernumber',
2516 },
2517 ],
2518 aqbooksellers => [
2519 { indexname => 'PRIMARY',
2520 content => 'id',
2521 type => 'PRI',
2522 }
2523 ],
2524 aqbasket => [
2525 { indexname => 'booksellerid',
2526 content => 'booksellerid',
2527 },
2528 ],
2529 aqorders => [
2530 { indexname => 'basketno',
2531 content => 'basketno',
2532 },
2533 ],
2534 aqorderbreakdown => [
2535 { indexname => 'ordernumber',
2536 content => 'ordernumber',
2537 },
2538 { indexname => 'bookfundid',
2539 content => 'bookfundid',
2540 },
2541 ],
2542 biblioitems => [
2543 { indexname => 'isbn',
2544 content => 'isbn',
2545 },
2546 { indexname => 'publishercode',
2547 content => 'publishercode',
2548 },
2549 ],
2550 borrowers => [
2551 {
2552 indexname => 'borrowernumber',
2553 content => 'borrowernumber',
2554 type => 'PRI',
2555 force => 1,
2556 }
2557 ],
2558 branches => [
2559 {
2560 indexname => 'branchcode',
2561 content => 'branchcode',
2562 type => 'PRI',
2563 }
2564 ],
2565 branchrelations => [
2566 {
2567 indexname => 'PRIMARY',
2568 content => 'categorycode',
2569 type => 'PRI',
2570 }
2571 ],
2572 branchrelations => [
2573 { indexname => 'PRIMARY',
2574 content => 'branchcode,categorycode',
2575 type => 'PRI',
2576 },
2577 { indexname => 'branchcode',
2578 content => 'branchcode',
2579 },
2580 { indexname => 'categorycode',
2581 content => 'categorycode',
2582 }
2583 ],
2584 currency => [
2585 { indexname => 'PRIMARY',
2586 content => 'currency',
2587 type => 'PRI',
2588 }
2589 ],
2590 categories => [
2591 {
2592 indexname => 'categorycode',
2593 content => 'categorycode',
2594 }
2595 ],
2596 issuingrules => [
2597 {
2598 indexname => 'categorycode',
2599 content => 'categorycode',
2600 },
2601 {
2602 indexname => 'itemtype',
2603 content => 'itemtype',
2604 },
2605 ],
2606 items => [
2607 { indexname => 'homebranch',
2608 content => 'homebranch',
2609 },
2610 { indexname => 'holdingbranch',
2611 content => 'holdingbranch',
2612 }
2613 ],
2614 itemtypes => [
2615 {
2616 indexname => 'itemtype',
2617 content => 'itemtype',
2618 }
2619 ],
2620 shelfcontents => [
2621 { indexname => 'shelfnumber',
2622 content => 'shelfnumber',
2623 },
2624 { indexname => 'itemnumber',
2625 content => 'itemnumber',
2626 }
2627 ],
2628 userflags => [
2629 { indexname => 'PRIMARY',
2630 content => 'bit',
2631 type => 'PRI',
2632 }
2633 ]
2634 );
2635
2636 my %foreign_keys = (
2637 # table => [
2638 # { key => 'the key in table' (must be indexed)
2639 # foreigntable => 'the foreigntable name', # (the parent)
2640 # foreignkey => 'the foreign key column(s)' # (in the parent)
2641 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2642 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
2643 # }
2644 # ],
2645 branchrelations => [
2646 { key => 'branchcode',
2647 foreigntable => 'branches',
2648 foreignkey => 'branchcode',
2649 onUpdate => 'CASCADE',
2650 onDelete => 'CASCADE',
2651 },
2652 { key => 'categorycode',
2653 foreigntable => 'branchcategories',
2654 foreignkey => 'categorycode',
2655 onUpdate => 'CASCADE',
2656 onDelete => 'CASCADE',
2657 },
2658 ],
2659 shelfcontents => [
2660 { key => 'shelfnumber',
2661 foreigntable => 'bookshelf',
2662 foreignkey => 'shelfnumber',
2663 onUpdate => 'CASCADE',
2664 onDelete => 'CASCADE',
2665 },
2666 { key => 'itemnumber',
2667 foreigntable => 'items',
2668 foreignkey => 'itemnumber',
2669 onUpdate => 'CASCADE',
2670 onDelete => 'CASCADE',
2671 },
2672 ],
2673 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
2674 # easily deleted, but branches/itemtype not too easy to empty...
2675 biblioitems => [
2676 { key => 'biblionumber',
2677 foreigntable => 'biblio',
2678 foreignkey => 'biblionumber',
2679 onUpdate => 'CASCADE',
2680 onDelete => 'CASCADE',
2681 },
2682 ],
2683 items => [
2684 { key => 'biblioitemnumber',
2685 foreigntable => 'biblioitems',
2686 foreignkey => 'biblioitemnumber',
2687 onUpdate => 'CASCADE',
2688 onDelete => 'CASCADE',
2689 },
2690 { key => 'homebranch',
2691 foreigntable => 'branches',
2692 foreignkey => 'branchcode',
2693 onUpdate => 'CASCADE',
2694 onDelete => 'RESTRICT',
2695 },
2696 { key => 'holdingbranch',
2697 foreigntable => 'branches',
2698 foreignkey => 'branchcode',
2699 onUpdate => 'CASCADE',
2700 onDelete => 'RESTRICT',
2701 },
2702 ],
2703 aqbasket => [
2704 { key => 'booksellerid',
2705 foreigntable => 'aqbooksellers',
2706 foreignkey => 'id',
2707 onUpdate => 'CASCADE',
2708 onDelete => 'RESTRICT',
2709 },
2710 ],
2711 aqorders => [
2712 { key => 'basketno',
2713 foreigntable => 'aqbasket',
2714 foreignkey => 'basketno',
2715 onUpdate => 'CASCADE',
2716 onDelete => 'CASCADE',
2717 },
2718 { key => 'biblionumber',
2719 foreigntable => 'biblio',
2720 foreignkey => 'biblionumber',
2721 onUpdate => 'SET NULL',
2722 onDelete => 'SET NULL',
2723 },
2724 ],
2725 aqbooksellers => [
2726 { key => 'listprice',
2727 foreigntable => 'currency',
2728 foreignkey => 'currency',
2729 onUpdate => 'CASCADE',
2730 onDelete => 'CASCADE',
2731 },
2732 { key => 'invoiceprice',
2733 foreigntable => 'currency',
2734 foreignkey => 'currency',
2735 onUpdate => 'CASCADE',
2736 onDelete => 'CASCADE',
2737 },
2738 ],
2739 aqorderbreakdown => [
2740 { key => 'ordernumber',
2741 foreigntable => 'aqorders',
2742 foreignkey => 'ordernumber',
2743 onUpdate => 'CASCADE',
2744 onDelete => 'CASCADE',
2745 },
2746 { key => 'bookfundid',
2747 foreigntable => 'aqbookfund',
2748 foreignkey => 'bookfundid',
2749 onUpdate => 'CASCADE',
2750 onDelete => 'CASCADE',
2751 },
2752 ],
2753 branchtransfers => [
2754 { key => 'frombranch',
2755 foreigntable => 'branches',
2756 foreignkey => 'branchcode',
2757 onUpdate => 'CASCADE',
2758 onDelete => 'CASCADE',
2759 },
2760 { key => 'tobranch',
2761 foreigntable => 'branches',
2762 foreignkey => 'branchcode',
2763 onUpdate => 'CASCADE',
2764 onDelete => 'CASCADE',
2765 },
2766 { key => 'itemnumber',
2767 foreigntable => 'items',
2768 foreignkey => 'itemnumber',
2769 onUpdate => 'CASCADE',
2770 onDelete => 'CASCADE',
2771 },
2772 ],
2773 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
2774 # for stat purposes
2775 { key => 'borrowernumber',
2776 foreigntable => 'borrowers',
2777 foreignkey => 'borrowernumber',
2778 onUpdate => 'SET NULL',
2779 onDelete => 'SET NULL',
2780 },
2781 { key => 'itemnumber',
2782 foreigntable => 'items',
2783 foreignkey => 'itemnumber',
2784 onUpdate => 'SET NULL',
2785 onDelete => 'SET NULL',
2786 },
2787 ],
2788 reserves => [
2789 { key => 'borrowernumber',
2790 foreigntable => 'borrowers',
2791 foreignkey => 'borrowernumber',
2792 onUpdate => 'CASCADE',
2793 onDelete => 'CASCADE',
2794 },
2795 { key => 'biblionumber',
2796 foreigntable => 'biblio',
2797 foreignkey => 'biblionumber',
2798 onUpdate => 'CASCADE',
2799 onDelete => 'CASCADE',
2800 },
2801 { key => 'itemnumber',
2802 foreigntable => 'items',
2803 foreignkey => 'itemnumber',
2804 onUpdate => 'CASCADE',
2805 onDelete => 'CASCADE',
2806 },
2807 { key => 'branchcode',
2808 foreigntable => 'branches',
2809 foreignkey => 'branchcode',
2810 onUpdate => 'CASCADE',
2811 onDelete => 'CASCADE',
2812 },
2813 ],
2814 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
2815 # but prevent deleting a branch as soon as it has 1 borrower !
2816 { key => 'categorycode',
2817 foreigntable => 'categories',
2818 foreignkey => 'categorycode',
2819 onUpdate => 'RESTRICT',
2820 onDelete => 'RESTRICT',
2821 },
2822 { key => 'branchcode',
2823 foreigntable => 'branches',
2824 foreignkey => 'branchcode',
2825 onUpdate => 'RESTRICT',
2826 onDelete => 'RESTRICT',
2827 },
2828 ],
2829 accountlines => [
2830 { key => 'borrowernumber',
2831 foreigntable => 'borrowers',
2832 foreignkey => 'borrowernumber',
2833 onUpdate => 'CASCADE',
2834 onDelete => 'CASCADE',
2835 },
2836 { key => 'itemnumber',
2837 foreigntable => 'items',
2838 foreignkey => 'itemnumber',
2839 onUpdate => 'SET NULL',
2840 onDelete => 'SET NULL',
2841 },
2842 ],
2843 accountoffsets => [
2844 { key => 'borrowernumber',
2845 foreigntable => 'borrowers',
2846 foreignkey => 'borrowernumber',
2847 onUpdate => 'CASCADE',
2848 onDelete => 'CASCADE',
2849 },
2850 ],
2851 auth_tag_structure => [
2852 { key => 'authtypecode',
2853 foreigntable => 'auth_types',
2854 foreignkey => 'authtypecode',
2855 onUpdate => 'CASCADE',
2856 onDelete => 'CASCADE',
2857 },
2858 ],
2859 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
2860 );
2861
2862
2863 # column changes
2864 my %column_change = (
2865 # table
2866 borrowers => [
2867 {
2868 from => 'emailaddress',
2869 to => 'email',
2870 after => 'city',
2871 },
2872 {
2873 from => 'streetaddress',
2874 to => 'address',
2875 after => 'initials',
2876 },
2877 {
2878 from => 'faxnumber',
2879 to => 'fax',
2880 after => 'phone',
2881 },
2882 {
2883 from => 'textmessaging',
2884 to => 'opacnote',
2885 after => 'userid',
2886 },
2887 {
2888 from => 'altnotes',
2889 to => 'contactnote',
2890 after => 'opacnote',
2891 },
2892 {
2893 from => 'physstreet',
2894 to => 'B_address',
2895 after => 'fax',
2896 },
2897 {
2898 from => 'streetcity',
2899 to => 'B_city',
2900 after => 'B_address',
2901 },
2902 {
2903 from => 'phoneday',
2904 to => 'mobile',
2905 after => 'phone',
2906 },
2907 {
2908 from => 'zipcode',
2909 to => 'zipcode',
2910 after => 'city',
2911 },
2912 {
2913 from => 'homezipcode',
2914 to => 'B_zipcode',
2915 after => 'B_city',
2916 },
2917 {
2918 from => 'altphone',
2919 to => 'B_phone',
2920 after => 'B_zipcode',
2921 },
2922 {
2923 from => 'expiry',
2924 to => 'dateexpiry',
2925 after => 'dateenrolled',
2926 },
2927 {
2928 from => 'guarantor',
2929 to => 'guarantorid',
2930 after => 'contactname',
2931 },
2932 {
2933 from => 'altrelationship',
2934 to => 'relationship',
2935 after => 'borrowernotes',
2936 },
2937 ],
2938
2939 deletedborrowers => [
2940 {
2941 from => 'emailaddress',
2942 to => 'email',
2943 after => 'city',
2944 },
2945 {
2946 from => 'streetaddress',
2947 to => 'address',
2948 after => 'initials',
2949 },
2950 {
2951 from => 'faxnumber',
2952 to => 'fax',
2953 after => 'phone',
2954 },
2955 {
2956 from => 'textmessaging',
2957 to => 'opacnote',
2958 after => 'userid',
2959 },
2960 {
2961 from => 'altnotes',
2962 to => 'contactnote',
2963 after => 'opacnote',
2964 },
2965 {
2966 from => 'physstreet',
2967 to => 'B_address',
2968 after => 'fax',
2969 },
2970 {
2971 from => 'streetcity',
2972 to => 'B_city',
2973 after => 'B_address',
2974 },
2975 {
2976 from => 'phoneday',
2977 to => 'mobile',
2978 after => 'phone',
2979 },
2980 {
2981 from => 'zipcode',
2982 to => 'zipcode',
2983 after => 'city',
2984 },
2985 {
2986 from => 'homezipcode',
2987 to => 'B_zipcode',
2988 after => 'B_city',
2989 },
2990 {
2991 from => 'altphone',
2992 to => 'B_phone',
2993 after => 'B_zipcode',
2994 },
2995 {
2996 from => 'expiry',
2997 to => 'dateexpiry',
2998 after => 'dateenrolled',
2999 },
3000 {
3001 from => 'guarantor',
3002 to => 'guarantorid',
3003 after => 'contactname',
3004 },
3005 {
3006 from => 'altrelationship',
3007 to => 'relationship',
3008 after => 'borrowernotes',
3009 },
3010 ],
3011 );
3012
3013
3014 # MOVE all tables TO UTF-8 and innoDB
3015 $sth = $dbh->prepare("show table status");
3016 $sth->execute;
3017 while ( my $table = $sth->fetchrow_hashref ) {
3018 next if $table->{Name} eq 'marc_word';
3019 next if $table->{Name} eq 'marc_subfield_table';
3020 next if $table->{Name} eq 'auth_word';
3021 next if $table->{Name} eq 'auth_subfield_table';
3022 if ($table->{Engine} ne 'InnoDB') {
3023 print "moving $table->{Name} to InnoDB\n";
3024 $dbh->do("ALTER TABLE $table->{Name} ENGINE = innodb");
3025 }
3026 unless ($table->{Collation} =~ /^utf8/) {
3027 print "moving $table->{Name} to utf8\n";
3028 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
3029 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
3030 # 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 !
3031 } else {
3032 }
3033 }
3034
3035 # list of columns that must exist for %column_change to be
3036 # processed without error, but which do not necessarily exist
3037 # in all 2.2 databases
3038 my %required_prereq_fields = (
3039 deletedborrowers => [
3040 [ 'textmessaging', 'mediumtext AFTER faxnumber' ],
3041 [ 'password', 'varchar(30) default NULL' ],
3042 [ 'flags', 'int(11) default NULL' ],
3043 [ 'userid', 'varchar(30) default NULL' ],
3044 [ 'homezipcode', 'varchar(25) default NULL' ],
3045 [ 'zipcode', 'varchar(25) default NULL' ],
3046 [ 'sort1', 'varchar(80) default NULL' ],
3047 [ 'sort2', 'varchar(80) default NULL' ],
3048 ],
3049 );
3050
3051 foreach $table ( keys %required_prereq_fields ) {
3052 print "Check table $table\n" if $debug and not $silent;
3053 $sth = $dbh->prepare("show columns from $table");
3054 $sth->execute();
3055 undef %types;
3056 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3057 {
3058 $types{$column} = $type;
3059 } # while
3060 foreach my $entry ( @{ $required_prereq_fields{$table} } ) {
3061 ($column, $type) = @{ $entry };
3062 print " Check column $column [$type]\n" if $debug and not $silent;
3063 if ( !$types{$column} ) {
3064
3065 # column doesn't exist
3066 print "Adding $column field to $table table...\n" unless $silent;
3067 $query = "alter table $table
3068 add column $column " . $type;
3069 print "Execute: $query\n" if $debug;
3070 my $sti = $dbh->prepare($query);
3071 $sti->execute;
3072 if ( $sti->err ) {
3073 print "**Error : $sti->errstr \n";
3074 $sti->finish;
3075 } # if error
3076 } # if column
3077 } # foreach column
3078 } # foreach table
3079
3080 foreach my $table (keys %column_change) {
3081 $sth = $dbh->prepare("show columns from $table");
3082 $sth->execute();
3083 undef %types;
3084 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3085 {
3086 $types{$column}->{type} ="$type";
3087 $types{$column}->{null} = "$null";
3088 $types{$column}->{key} = "$key";
3089 $types{$column}->{default} = "$default";
3090 $types{$column}->{extra} = "$extra";
3091 } # while
3092 my $tablerows = $column_change{$table};
3093 foreach my $row ( @$tablerows ) {
3094 if ($types{$row->{from}}->{type}) {
3095 print "altering $table $row->{from} to $row->{to}\n";
3096 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
3097 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
3098 my $sql =
3099 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
3100 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
3101 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
3102 "$types{$row->{from}}->{extra} after $row->{after} ";
3103 # print "$sql";
3104 $dbh->do($sql);
3105 }
3106 }
3107 }
3108
3109 # Enter here the field you want to delete from DB.
3110 # FIXME :: there is a %uselessfield before which seems doing the same things.
3111 my %fieldtodelete = (
3112 # tablename => [fieldname1,fieldname2,...]
3113
3114 ); # %fielddelete
3115
3116 print "removing some unused fields...\n";
3117 foreach my $table ( keys %fieldtodelete ) {
3118 foreach my $field ( @{$fieldtodelete{$table}} ){
3119 print "removing ".$field." from ".$table;
3120 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
3121 $sth->execute;
3122 if ( $sth->err ) {
3123 print "Error : $sth->errstr \n";
3124 }
3125 }
3126 }
3127
3128 # Enter here the line you want to remove from DB.
3129 my %linetodelete = (
3130 # table name => where clause.
3131 userflags => [ "bit = 8" ], # delete the 'reserveforself' flags
3132
3133 ); # %linetodelete
3134
3135 #-------------------
3136 # Initialize
3137
3138 # Start checking
3139
3140 # Get version of MySQL database engine.
3141 my $mysqlversion = `mysqld --version`;
3142 $mysqlversion =~ /Ver (\S*) /;
3143 $mysqlversion = $1;
3144 if ( $mysqlversion ge '3.23' ) {
3145 print "Could convert to MyISAM database tables...\n" unless $silent;
3146 }
3147
3148 #---------------------------------
3149 # Tables
3150
3151 # Collect all tables into a list
3152 $sth = $dbh->prepare("show tables");
3153 $sth->execute;
3154 while ( my ($table) = $sth->fetchrow ) {
3155 $existingtables{$table} = 1;
3156 }
3157
3158
3159 # Now add any missing tables
3160 foreach $table ( keys %requiretables ) {
3161 unless ( $existingtables{$table} ) {
3162 print "Adding $table table...\n" unless $silent;
3163 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
3164 $sth->execute;
3165 if ( $sth->err ) {
3166 print "Error : $sth->errstr \n";
3167 $sth->finish;
3168 } # if error
3169 } # unless exists
3170 } # foreach
3171
3172 #---------------------------------
3173 # Columns
3174
3175 foreach $table ( keys %requirefields ) {
3176 print "Check table $table\n" if $debug and not $silent;
3177 $sth = $dbh->prepare("show columns from $table");
3178 $sth->execute();
3179 undef %types;
3180 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3181 {
3182 $types{$column} = $type;
3183 } # while
3184 foreach $column ( keys %{ $requirefields{$table} } ) {
3185 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
3186 if ( !$types{$column} ) {
3187
3188 # column doesn't exist
3189 print "Adding $column field to $table table...\n" unless $silent;
3190 $query = "alter table $table
3191 add column $column " . $requirefields{$table}->{$column};
3192 print "Execute: $query\n" if $debug;
3193 my $sti = $dbh->prepare($query);
3194 $sti->execute;
3195 if ( $sti->err ) {
3196 print "**Error : $sti->errstr \n";
3197 $sti->finish;
3198 } # if error
3199 } # if column
3200 } # foreach column
3201 } # foreach table
3202
3203 foreach $table ( sort keys %fielddefinitions ) {
3204 print "Check table $table\n" if $debug;
3205 $sth = $dbh->prepare("show columns from $table");
3206 $sth->execute();
3207 my $definitions;
3208 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3209 {
3210 $definitions->{$column}->{type} = $type;
3211 $definitions->{$column}->{null} = $null;
3212 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
3213 $definitions->{$column}->{key} = $key;
3214 $definitions->{$column}->{default} = $default;
3215 $definitions->{$column}->{extra} = $extra;
3216 } # while
3217 my $fieldrow = $fielddefinitions{$table};
3218 foreach my $row (@$fieldrow) {
3219 my $field = $row->{field};
3220 my $type = $row->{type};
3221 my $null = $row->{null};
3222 # $null = 'YES' if $row->{null} eq 'NULL';
3223 my $key = $row->{key};
3224 my $default = $row->{default};
3225 # $default="''" unless $default;
3226 my $extra = $row->{extra};
3227 my $def = $definitions->{$field};
3228 my $after = ($row->{after}?" after ".$row->{after}:"");
3229
3230 unless ( $type eq $def->{type}
3231 && $null eq $def->{null}
3232 && $key eq $def->{key}
3233 && $default eq $def->{default}
3234 && $extra eq $def->{extra} )
3235 {
3236 if ( $null eq '' ) {
3237 $null = 'NOT NULL';
3238 }
3239 if ( $key eq 'PRI' ) {
3240 $key = 'PRIMARY KEY';
3241 }
3242 unless ( $extra eq 'auto_increment' ) {
3243 $extra = '';
3244 }
3245
3246 # if it's a new column use "add", if it's an old one, use "change".
3247 my $action;
3248 if ($definitions->{$field}->{type}) {
3249 $action="change `$field`"
3250 } else {
3251 $action="add";
3252 }
3253 # if it's a primary key, drop the previous pk, before altering the table
3254 print " alter or create $field in $table\n" unless $silent;
3255 my $query;
3256 if ($key ne 'PRIMARY KEY') {
3257 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
3258 $query = "alter table $table $action `$field` $type $null $key $extra ".
3259 GetDefaultClause($default)." $after";
3260 } else {
3261 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
3262 # something strange : for indexes UNIQUE, they are reported as primary key here.
3263 # but if you try to run with drop primary key, it fails.
3264 # thus, we run the query twice, one will fail, one will succeed.
3265 # strange...
3266 $query="alter table $table drop primary key, $action `$field` $type $null $key $extra ".
3267 GetDefaultClause($default)." $after";
3268 $query="alter table $table $action `$field` $type $null $key $extra ".
3269 GetDefaultClause($default)." $after";
3270 }
3271 $dbh->do($query) or warn "Error while executing: $query";
3272 }
3273 }
3274 }
3275
3276 print "removing some unused data...\n";
3277 foreach my $table ( keys %linetodelete ) {
3278 foreach my $where ( @{$linetodelete{$table}} ){
3279 print "DELETE FROM ".$table." where ".$where;
3280 print "\n";
3281 my $sth = $dbh->prepare("DELETE FROM $table where $where");
3282 $sth->execute;
3283 if ( $sth->err ) {
3284 print "Error : $sth->errstr \n";
3285 }
3286 }
3287 }
3288
3289 # Populate tables with required data
3290
3291 # synch table and deletedtable.
3292 foreach my $table (('borrowers','items','biblio','biblioitems')) {
3293 my %deletedborrowers;
3294 print "synch'ing $table and deleted$table\n";
3295 $sth = $dbh->prepare("show columns from deleted$table");
3296 $sth->execute;
3297 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3298 $deletedborrowers{$column}=1;
3299 }
3300 $sth = $dbh->prepare("show columns from $table");
3301 $sth->execute;
3302 my $previous;
3303 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
3304 unless ($deletedborrowers{$column}) {
3305 my $newcol="alter table deleted$table add $column $type";
3306 if ($null eq 'YES') {
3307 $newcol .= " NULL ";
3308 } else {
3309 $newcol .= " NOT NULL ";
3310 }
3311 $newcol .= "default ".$dbh->quote($default) if $default;
3312 $newcol .= " after $previous" if $previous;
3313 $previous=$column;
3314 print "creating column $column\n";
3315 $dbh->do($newcol);
3316 }
3317 }
3318 }
3319 #
3320 # update publisheddate
3321 #
3322 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
3323 $sth->execute;
3324 my ($emptypublished) = $sth->fetchrow;
3325 if ($emptypublished) {
3326 print "Updating publisheddate\n";
3327 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
3328 }
3329 # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it.
3330 # now, let's get rid of 000-00-00's.
3331
3332 $dbh->do("update serial set publisheddate=NULL where publisheddate = 0");
3333 $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0");
3334
3335 foreach my $table ( keys %tabledata ) {
3336 print "Checking for data required in table $table...\n" unless $silent;
3337 my $tablerows = $tabledata{$table};
3338 foreach my $row (@$tablerows) {
3339 my $uniquefieldrequired = $row->{uniquefieldrequired};
3340 my $uniquevalue = $row->{$uniquefieldrequired};
3341 my $forceupdate = $row->{forceupdate};
3342 my $sth =
3343 $dbh->prepare(
3344 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
3345 );
3346 $sth->execute($uniquevalue);
3347 if ($sth->rows) {
3348 foreach my $field (keys %$forceupdate) {
3349 if ($forceupdate->{$field}) {
3350 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
3351 $sth->execute($row->{$field}, $uniquevalue);
3352 }
3353 }
3354 } else {
3355 print "Adding row to $table: " unless $silent;
3356 my @values;
3357 my $fieldlist;
3358 my $placeholders;
3359 foreach my $field ( keys %$row ) {
3360 next if $field eq 'uniquefieldrequired';
3361 next if $field eq 'forceupdate';
3362 my $value = $row->{$field};
3363 push @values, $value;
3364 print " $field => $value" unless $silent;
3365 $fieldlist .= "$field,";
3366 $placeholders .= "?,";
3367 }
3368 print "\n" unless $silent;
3369 $fieldlist =~ s/,$//;
3370 $placeholders =~ s/,$//;
3371 print "insert into $table ($fieldlist) values ($placeholders)";
3372 my $sth =
3373 $dbh->prepare(
3374 "insert into $table ($fieldlist) values ($placeholders)");
3375 $sth->execute(@values);
3376 }
3377 }
3378 }
3379
3380 #
3381 # check indexes and create them when needed
3382 #
3383 print "Checking for index required...\n" unless $silent;
3384 foreach my $table ( keys %indexes ) {
3385 #
3386 # read all indexes from $table
3387 #
3388 $sth = $dbh->prepare("show index from $table");
3389 $sth->execute;
3390 my %existingindexes;
3391 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
3392 $existingindexes{$key_name} = 1;
3393 }
3394 # read indexes to check
3395 my $tablerows = $indexes{$table};
3396 foreach my $row (@$tablerows) {
3397 my $key_name=$row->{indexname};
3398 if ($existingindexes{$key_name} eq 1 and not $row->{force}) {
3399 # print "$key_name existing";
3400 } else {
3401 print "\tCreating index $key_name in $table\n";
3402 my $sql;
3403 if ($row->{indexname} eq 'PRIMARY' or $row->{type} eq 'PRI') {
3404 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
3405 } else {
3406 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
3407 }
3408 $dbh->do($sql);
3409 print "Error $sql : $dbh->err \n" if $dbh->err;
3410 }
3411 }
3412 }
3413
3414 #
3415 # check foreign keys and create them when needed
3416 #
3417 print "Checking for foreign keys required...\n" unless $silent;
3418 foreach my $table ( sort keys %foreign_keys ) {
3419 #
3420 # read all indexes from $table
3421 #
3422 $sth = $dbh->prepare("show table status like '$table'");
3423 $sth->execute;
3424 my $stat = $sth->fetchrow_hashref;
3425 # read indexes to check
3426 my $tablerows = $foreign_keys{$table};
3427 foreach my $row (@$tablerows) {
3428 my $foreign_table=$row->{foreigntable};
3429 if ($stat->{'Comment'} =~/$foreign_table/) {
3430 # print "$foreign_table existing\n";
3431 } else {
3432 print "\tCreating foreign key $foreign_table in $table\n";
3433 # first, drop any orphan value in child table
3434 if ($row->{onDelete} ne "RESTRICT") {
3435 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
3436 $dbh->do($sql);
3437 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
3438 }
3439 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
3440 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
3441 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
3442 $dbh->do($sql);
3443 if ($dbh->err) {
3444 print "====================
3445 An error occurred during :
3446 \t$sql
3447 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).
3448 You can find those values with select
3449 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
3450 ====================\n
3451 ";
3452 }
3453 }
3454 }
3455 }
3456 # now drop useless tables
3457 foreach $table ( @TableToDelete ) {
3458 if ( $existingtables{$table} ) {
3459 print "Dropping unused table $table\n" if $debug and not $silent;
3460 $dbh->do("drop table $table");
3461 if ( $dbh->err ) {
3462 print "Error : $dbh->errstr \n";
3463 }
3464 }
3465 }
3466
3467 #
3468 # SPECIFIC STUFF
3469 #
3470 #
3471 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
3472 #
3473
3474 # 1st, get how many biblio we will have to do...
3475 $sth = $dbh->prepare('select count(*) from marc_biblio');
3476 $sth->execute;
3477 my ($totaltodo) = $sth->fetchrow;
3478
3479 $sth = $dbh->prepare("show columns from biblio");
3480 $sth->execute();
3481 my $definitions;
3482 my $bibliofwexist=0;
3483 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
3484 $bibliofwexist=1 if $column eq 'frameworkcode';
3485 }
3486 unless ($bibliofwexist) {
3487 print "moving biblioframework to biblio table\n";
3488 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
3489 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
3490 $sth->execute;
3491 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
3492 my $totaldone=0;
3493 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
3494 $sth_update->execute($frameworkcode,$biblionumber);
3495 $totaldone++;
3496 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
3497 }
3498 print "\rdone\n";
3499 }
3500
3501 # at last, remove useless fields
3502 foreach $table ( keys %uselessfields ) {
3503 my @fields = split (/,/,$uselessfields{$table});
3504 my $fields;
3505 my $exists;
3506 foreach my $fieldtodrop (@fields) {
3507 $fieldtodrop =~ s/\t//g;
3508 $fieldtodrop =~ s/\n//g;
3509 $exists =0;
3510 $sth = $dbh->prepare("show columns from $table");
3511 $sth->execute;
3512 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
3513 {
3514 $exists =1 if ($column eq $fieldtodrop);
3515 }
3516 if ($exists) {
3517 print "deleting $fieldtodrop field in $table...\n" unless $silent;
3518 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
3519 $sth->execute;
3520 }
3521 }
3522 } # foreach
3523
3524 #
3525 # Changing aqbookfund's primary key
3526 #
3527 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
3528 $sth->execute;
3529 $sth->finish;
3530
3531 # drop extra key on borrowers.borrowernumber
3532 $dbh->do("ALTER TABLE borrowers DROP KEY borrowernumber");
3533
3534 # update enrolmentperiod
3535 $dbh->do("UPDATE categories SET enrolmentperiod = enrolmentperiod * 12");
3536
3537 print "upgrade to Koha 3.0 done\n";
3538 SetVersion ($DBversion);
3539
3540 =head1 GetDefaultClause
3541
3542 Generate a default clause (for an ALTER TABLE command)
3543
3544 =cut
3545
3546 sub GetDefaultClause {
3547 my $default = shift;
3548
3549 return "" unless defined $default;
3550 return "" if $default eq '';
3551 return "default ''" if $default eq "''";
3552 return "default NULL" if $default eq "NULL";
3553 return "default " . $dbh->quote($default);
3554 }
3555
3556 =head1 TransformToNum
3557
3558 Transform the Koha version from a 4 parts string
3559 to a number, with just 1.
3560
3561 =cut
3562
3563 sub TransformToNum {
3564 my $version = shift;
3565 # remove the 3 last . to have a Perl number
3566 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3567 return $version;
3568 }
3569
3570 =head1 SetVersion
3571
3572 set the DBversion in the systempreferences
3573
3574 =cut
3575
3576 sub SetVersion {
3577 my $kohaversion = TransformToNum(shift);
3578 if (C4::Context->preference('Version')) {
3579 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3580 $finish->execute($kohaversion);
3581 } else {
3582 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')");
3583 $finish->execute($kohaversion);
3584 }
3585 }
3586 exit;
3587
3588 # Revision 1.172 2007/07/19 10:21:22 hdl
Koha ILS