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