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