6 # This script checks for required updates to the database.
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
12 # - Would also be a good idea to offer to do a backup at this time...
14 # NOTE: If you do something more than once in here, make it table driven.
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
46 my $dbh = C4::Context->dbh;
47 print "connected to your DB. Checking & modifying it\n" unless $silent;
48 $|=1; # flushes output
53 # Tables to add if they don't exist
55 categorytable => "(categorycode char(5) NOT NULL default '',
56 description text default '',
57 itemtypecodes text default '',
58 PRIMARY KEY (categorycode)
60 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
61 description text default '',
62 itemtypecodes text default '',
63 PRIMARY KEY (subcategorycode)
65 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
66 description text default '',
67 itemtypecodes text default '',
68 PRIMARY KEY (mediatypecode)
71 `timestamp` TIMESTAMP NOT NULL ,
72 `user` INT( 11 ) NOT NULL ,
73 `module` TEXT default '',
74 `action` TEXT default '' ,
75 `object` INT(11) default '' ,
76 `info` TEXT default '' ,
77 PRIMARY KEY ( `timestamp` , `user` )
80 module varchar(20) NOT NULL default '',
81 code varchar(20) NOT NULL default '',
82 name varchar(100) NOT NULL default '',
83 title varchar(200) NOT NULL default '',
85 PRIMARY KEY (module,code)
88 alertid int(11) NOT NULL auto_increment,
89 borrowernumber int(11) NOT NULL default '0',
90 type varchar(10) NOT NULL default '',
91 externalid varchar(20) NOT NULL default '',
92 PRIMARY KEY (alertid),
93 KEY borrowernumber (borrowernumber),
94 KEY type (type,externalid)
100 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'},
101 itemtypes => { 'imageurl' => 'char(200) NULL'},
102 # tablename => { 'field' => 'fieldtype' },
105 my %dropable_table = (
106 sessionqueries => 'sessionqueries',
107 marcrecorddone => 'marcrecorddone',
109 itemsprices => 'itemsprices',
110 biblioanalysis => 'biblioanalysis',
112 # tablename => 'tablename',
115 my %uselessfields = (
116 # tablename => "field1,field2",
118 # the other hash contains other actions that can't be done elsewhere. they are done
119 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
121 # The tabledata hash contains data that should be in the tables.
122 # The uniquefieldrequired hash entry is used to determine which (if any) fields
123 # must not exist in the table for this row to be inserted. If the
124 # uniquefieldrequired entry is already in the table, the existing data is not
125 # modified, unless the forceupdate hash entry is also set. Fields in the
126 # anonymous "forceupdate" hash will be forced to be updated to the default
127 # values given in the %tabledata hash.
131 # { uniquefielrequired => 'fieldname', # the primary key in the table
132 # fieldname => fieldvalue,
133 # fieldname2 => fieldvalue2,
136 systempreferences => [
138 uniquefieldrequired => 'variable',
139 variable => 'Activate_Log',
141 forceupdate => { 'explanation' => 1,
143 explanation => 'Turn Log Actions on DB On an Off',
147 uniquefieldrequired => 'variable',
148 variable => 'IndependantBranches',
150 forceupdate => { 'explanation' => 1,
152 explanation => 'Turn Branch independancy management On an Off',
156 uniquefieldrequired => 'variable',
157 variable => 'ReturnBeforeExpiry',
159 forceupdate => { 'explanation' => 1,
161 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
165 uniquefieldrequired => 'variable',
166 variable => 'opacstylesheet',
168 forceupdate => { 'explanation' => 1,
170 explanation => 'Enter a complete URL to use an alternate stylesheet in OPAC',
174 uniquefieldrequired => 'variable',
175 variable => 'opacsmallimage',
177 forceupdate => { 'explanation' => 1,
179 explanation => 'Enter a complete URL to an image, will be on top/left instead of the Koha logo',
183 uniquefieldrequired => 'variable',
184 variable => 'opaclargeimage',
186 forceupdate => { 'explanation' => 1,
188 explanation => 'Enter a complete URL to an image, will be on the main page, instead of the Koha logo',
192 uniquefieldrequired => 'variable',
193 variable => 'delimiter',
195 forceupdate => { 'explanation' => 1,
197 explanation => 'separator for reports exported to spreadsheet',
201 uniquefieldrequired => 'variable',
203 value => 'OPENOFFICE.ORG',
204 forceupdate => { 'explanation' => 1,
207 explanation => 'Define the default application for report exportations into files',
209 options => 'EXCEL|OPENOFFICE.ORG'
212 uniquefieldrequired => 'variable',
213 variable => 'Delimiter',
215 forceupdate => { 'explanation' => 1,
218 explanation => 'Define the default separator character for report exportations into files',
220 options => ';|tabulation|,|/|\|#'
223 uniquefieldrequired => 'variable',
224 variable => 'SubscriptionHistory',
226 forceupdate => { 'explanation' => 1,
229 explanation => 'Define the information level for serials history in OPAC',
231 options => 'simplified|full'
234 uniquefieldrequired => 'variable',
235 variable => 'hidelostitems',
237 forceupdate => { 'explanation' => 1,
239 explanation => 'show or hide "lost" items in OPAC.',
243 uniquefieldrequired => 'variable',
244 variable => 'IndependantBranches',
246 forceupdate => { 'explanation' => 1,
248 explanation => 'Turn Branch independancy management On an Off',
252 uniquefieldrequired => 'variable',
253 variable => 'ReturnBeforeExpiry',
255 forceupdate => { 'explanation' => 1,
257 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
261 uniquefieldrequired => 'variable',
262 variable => 'Disable_Dictionary',
264 forceupdate => { 'explanation' => 1,
266 explanation => 'Disables Dictionary buttons if set to yes',
270 uniquefieldrequired => 'variable',
271 variable => 'hide_marc',
273 forceupdate => { 'explanation' => 1,
275 explanation => 'hide marc specific datas like subfield code & indicators to library',
279 uniquefieldrequired => 'variable',
280 variable => 'NotifyBorrowerDeparture',
282 forceupdate => { 'explanation' => 1,
284 explanation => 'Delay before expiry where a notice is sent when issuing',
288 uniquefieldrequired => 'variable',
289 variable => 'OpacPasswordChange',
291 forceupdate => { 'explanation' => 1,
293 explanation => 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
300 my %fielddefinitions = (
302 # { field => 'fieldname',
303 # type => 'fieldtype',
321 field => 'booksellerid',
331 field => 'listprice',
332 type => 'varchar(10)',
339 field => 'invoiceprice',
340 type => 'varchar(10)',
349 field => 'borrowernumber',
351 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
357 field => 'itemnumber',
359 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
369 # { indexname => 'index detail'
373 { indexname => 'shelfnumber',
374 content => 'shelfnumber',
376 { indexname => 'itemnumber',
377 content => 'itemnumber',
381 { indexname => 'biblionumber',
382 content => 'biblionumber',
386 { indexname => 'homebranch',
387 content => 'homebranch',
389 { indexname => 'holdingbranch',
390 content => 'holdingbranch',
394 { indexname => 'PRIMARY',
400 { indexname => 'booksellerid',
401 content => 'booksellerid',
405 { indexname => 'basketno',
406 content => 'basketno',
409 aqorderbreakdown => [
410 { indexname => 'ordernumber',
411 content => 'ordernumber',
413 { indexname => 'bookfundid',
414 content => 'bookfundid',
418 { indexname => 'PRIMARY',
419 content => 'currency',
427 # { key => 'the key in table' (must be indexed)
428 # foreigntable => 'the foreigntable name', # (the parent)
429 # foreignkey => 'the foreign key column(s)' # (in the parent)
430 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
431 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
435 { key => 'shelfnumber',
436 foreigntable => 'bookshelf',
437 foreignkey => 'shelfnumber',
438 onUpdate => 'CASCADE',
439 onDelete => 'CASCADE',
441 { key => 'itemnumber',
442 foreigntable => 'items',
443 foreignkey => 'itemnumber',
444 onUpdate => 'CASCADE',
445 onDelete => 'CASCADE',
448 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
449 # easily deleted, but branches/itemtype not too easy to empty...
451 { key => 'biblionumber',
452 foreigntable => 'biblio',
453 foreignkey => 'biblionumber',
454 onUpdate => 'CASCADE',
455 onDelete => 'CASCADE',
458 foreigntable => 'itemtypes',
459 foreignkey => 'itemtype',
460 onUpdate => 'CASCADE',
461 onDelete => 'RESTRICT',
465 { key => 'biblioitemnumber',
466 foreigntable => 'biblioitems',
467 foreignkey => 'biblioitemnumber',
468 onUpdate => 'CASCADE',
469 onDelete => 'CASCADE',
471 { key => 'homebranch',
472 foreigntable => 'branches',
473 foreignkey => 'branchcode',
474 onUpdate => 'CASCADE',
475 onDelete => 'RESTRICT',
477 { key => 'holdingbranch',
478 foreigntable => 'branches',
479 foreignkey => 'branchcode',
480 onUpdate => 'CASCADE',
481 onDelete => 'RESTRICT',
484 additionalauthors => [
485 { key => 'biblionumber',
486 foreigntable => 'biblio',
487 foreignkey => 'biblionumber',
488 onUpdate => 'CASCADE',
489 onDelete => 'CASCADE',
493 { key => 'biblionumber',
494 foreigntable => 'biblio',
495 foreignkey => 'biblionumber',
496 onUpdate => 'CASCADE',
497 onDelete => 'CASCADE',
501 { key => 'booksellerid',
502 foreigntable => 'aqbooksellers',
504 onUpdate => 'CASCADE',
505 onDelete => 'RESTRICT',
510 foreigntable => 'aqbasket',
511 foreignkey => 'basketno',
512 onUpdate => 'CASCADE',
513 onDelete => 'CASCADE',
515 { key => 'biblionumber',
516 foreigntable => 'biblio',
517 foreignkey => 'biblionumber',
518 onUpdate => 'SET NULL',
519 onDelete => 'SET NULL',
523 { key => 'listprice',
524 foreigntable => 'currency',
525 foreignkey => 'currency',
526 onUpdate => 'CASCADE',
527 onDelete => 'CASCADE',
529 { key => 'invoiceprice',
530 foreigntable => 'currency',
531 foreignkey => 'currency',
532 onUpdate => 'CASCADE',
533 onDelete => 'CASCADE',
536 aqorderbreakdown => [
537 { key => 'ordernumber',
538 foreigntable => 'aqorders',
539 foreignkey => 'ordernumber',
540 onUpdate => 'CASCADE',
541 onDelete => 'CASCADE',
543 { key => 'bookfundid',
544 foreigntable => 'aqbookfund',
545 foreignkey => 'bookfundid',
546 onUpdate => 'CASCADE',
547 onDelete => 'CASCADE',
551 { key => 'frombranch',
552 foreigntable => 'branches',
553 foreignkey => 'branchcode',
554 onUpdate => 'CASCADE',
555 onDelete => 'CASCADE',
558 foreigntable => 'branches',
559 foreignkey => 'branchcode',
560 onUpdate => 'CASCADE',
561 onDelete => 'CASCADE',
563 { key => 'itemnumber',
564 foreigntable => 'items',
565 foreignkey => 'itemnumber',
566 onUpdate => 'CASCADE',
567 onDelete => 'CASCADE',
571 { key => 'categorycode',
572 foreigntable => 'categories',
573 foreignkey => 'categorycode',
574 onUpdate => 'CASCADE',
575 onDelete => 'CASCADE',
578 foreigntable => 'itemtypes',
579 foreignkey => 'itemtype',
580 onUpdate => 'CASCADE',
581 onDelete => 'CASCADE',
584 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
586 { key => 'borrowernumber',
587 foreigntable => 'borrowers',
588 foreignkey => 'borrowernumber',
589 onUpdate => 'SET NULL',
590 onDelete => 'SET NULL',
592 { key => 'itemnumber',
593 foreigntable => 'items',
594 foreignkey => 'itemnumber',
595 onUpdate => 'SET NULL',
596 onDelete => 'SET NULL',
600 { key => 'borrowernumber',
601 foreigntable => 'borrowers',
602 foreignkey => 'borrowernumber',
603 onUpdate => 'CASCADE',
604 onDelete => 'CASCADE',
606 { key => 'biblionumber',
607 foreigntable => 'biblio',
608 foreignkey => 'biblionumber',
609 onUpdate => 'CASCADE',
610 onDelete => 'CASCADE',
612 { key => 'itemnumber',
613 foreigntable => 'items',
614 foreignkey => 'itemnumber',
615 onUpdate => 'CASCADE',
616 onDelete => 'CASCADE',
618 { key => 'branchcode',
619 foreigntable => 'branches',
620 foreignkey => 'branchcode',
621 onUpdate => 'CASCADE',
622 onDelete => 'CASCADE',
625 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
626 # but prevent deleting a branch as soon as it has 1 borrower !
627 { key => 'categorycode',
628 foreigntable => 'categories',
629 foreignkey => 'categorycode',
630 onUpdate => 'RESTRICT',
631 onDelete => 'RESTRICT',
633 { key => 'branchcode',
634 foreigntable => 'branches',
635 foreignkey => 'branchcode',
636 onUpdate => 'RESTRICT',
637 onDelete => 'RESTRICT',
641 { key => 'borrowernumber',
642 foreigntable => 'borrowers',
643 foreignkey => 'borrowernumber',
644 onUpdate => 'CASCADE',
645 onDelete => 'CASCADE',
647 { key => 'itemnumber',
648 foreigntable => 'items',
649 foreignkey => 'itemnumber',
650 onUpdate => 'SET NULL',
651 onDelete => 'SET NULL',
654 auth_tag_structure => [
655 { key => 'authtypecode',
656 foreigntable => 'auth_types',
657 foreignkey => 'authtypecode',
658 onUpdate => 'CASCADE',
659 onDelete => 'CASCADE',
662 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
670 # Get version of MySQL database engine.
671 my $mysqlversion = `mysqld --version`;
672 $mysqlversion =~ /Ver (\S*) /;
674 if ( $mysqlversion ge '3.23' ) {
675 print "Could convert to MyISAM database tables...\n" unless $silent;
678 #---------------------------------
681 # Collect all tables into a list
682 $sth = $dbh->prepare("show tables");
684 while ( my ($table) = $sth->fetchrow ) {
685 $existingtables{$table} = 1;
689 # Now add any missing tables
690 foreach $table ( keys %requiretables ) {
691 unless ( $existingtables{$table} ) {
692 print "Adding $table table...\n" unless $silent;
693 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
696 print "Error : $sth->errstr \n";
702 # now drop useless tables
703 foreach $table ( keys %dropable_table ) {
704 if ( $existingtables{$table} ) {
705 print "Dropping unused table $table\n" if $debug and not $silent;
706 $dbh->do("drop table $table");
708 print "Error : $dbh->errstr \n";
713 #---------------------------------
716 foreach $table ( keys %requirefields ) {
717 print "Check table $table\n" if $debug and not $silent;
718 $sth = $dbh->prepare("show columns from $table");
721 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
723 $types{$column} = $type;
725 foreach $column ( keys %{ $requirefields{$table} } ) {
726 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
727 if ( !$types{$column} ) {
729 # column doesn't exist
730 print "Adding $column field to $table table...\n" unless $silent;
731 $query = "alter table $table
732 add column $column " . $requirefields{$table}->{$column};
733 print "Execute: $query\n" if $debug;
734 my $sti = $dbh->prepare($query);
737 print "**Error : $sti->errstr \n";
744 foreach $table ( keys %fielddefinitions ) {
745 print "Check table $table\n" if $debug;
746 $sth = $dbh->prepare("show columns from $table");
749 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
751 $definitions->{$column}->{type} = $type;
752 $definitions->{$column}->{null} = $null;
753 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
754 $definitions->{$column}->{key} = $key;
755 $definitions->{$column}->{default} = $default;
756 $definitions->{$column}->{extra} = $extra;
758 my $fieldrow = $fielddefinitions{$table};
759 foreach my $row (@$fieldrow) {
760 my $field = $row->{field};
761 my $type = $row->{type};
762 my $null = $row->{null};
763 # $null = 'YES' if $row->{null} eq 'NULL';
764 my $key = $row->{key};
765 my $default = $row->{default};
766 my $null = $row->{null};
767 # $default="''" unless $default;
768 my $extra = $row->{extra};
769 my $def = $definitions->{$field};
771 unless ( $type eq $def->{type}
772 && $null eq $def->{null}
773 && $key eq $def->{key}
774 && $extra eq $def->{extra} )
779 if ( $key eq 'PRI' ) {
780 $key = 'PRIMARY KEY';
782 unless ( $extra eq 'auto_increment' ) {
786 # if it's a new column use "add", if it's an old one, use "change".
788 if ($definitions->{$field}->{type}) {
789 $action="change $field"
793 # if it's a primary key, drop the previous pk, before altering the table
795 if ($key ne 'PRIMARY KEY') {
796 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
798 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
800 $sth->execute($default);
801 print " Alter $field in $table\n" unless $silent;
807 # Populate tables with required data
810 # synch table and deletedtable.
811 foreach my $table (('borrowers','items','biblio','biblioitems')) {
812 my %deletedborrowers;
813 print "synch'ing $table\n";
814 $sth = $dbh->prepare("show columns from deleted$table");
816 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
817 $deletedborrowers{$column}=1;
819 $sth = $dbh->prepare("show columns from $table");
822 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
823 unless ($deletedborrowers{$column}) {
824 my $newcol="alter table deleted$table add $column $type";
825 if ($null eq 'YES') {
828 $newcol .= " NOT NULL ";
830 $newcol .= "default $default" if $default;
831 $newcol .= " after $previous" if $previous;
833 print "creating column $column\n";
839 foreach my $table ( keys %tabledata ) {
840 print "Checking for data required in table $table...\n" unless $silent;
841 my $tablerows = $tabledata{$table};
842 foreach my $row (@$tablerows) {
843 my $uniquefieldrequired = $row->{uniquefieldrequired};
844 my $uniquevalue = $row->{$uniquefieldrequired};
845 my $forceupdate = $row->{forceupdate};
848 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
850 $sth->execute($uniquevalue);
852 foreach my $field (keys %$forceupdate) {
853 if ($forceupdate->{$field}) {
854 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
855 $sth->execute($row->{$field}, $uniquevalue);
859 print "Adding row to $table: " unless $silent;
863 foreach my $field ( keys %$row ) {
864 next if $field eq 'uniquefieldrequired';
865 next if $field eq 'forceupdate';
866 my $value = $row->{$field};
867 push @values, $value;
868 print " $field => $value" unless $silent;
869 $fieldlist .= "$field,";
870 $placeholders .= "?,";
872 print "\n" unless $silent;
873 $fieldlist =~ s/,$//;
874 $placeholders =~ s/,$//;
877 "insert into $table ($fieldlist) values ($placeholders)");
878 $sth->execute(@values);
884 # check indexes and create them when needed
886 print "Checking for index required...\n" unless $silent;
887 foreach my $table ( keys %indexes ) {
889 # read all indexes from $table
891 $sth = $dbh->prepare("show index from $table");
894 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
895 $existingindexes{$key_name} = 1;
897 # read indexes to check
898 my $tablerows = $indexes{$table};
899 foreach my $row (@$tablerows) {
900 my $key_name=$row->{indexname};
901 if ($existingindexes{$key_name} eq 1) {
902 # print "$key_name existing";
904 print "Creating $key_name in $table\n";
906 if ($row->{indexname} eq 'PRIMARY') {
907 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
909 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
912 print "Error $sql : $dbh->err \n" if $dbh->err;
918 # check foreign keys and create them when needed
920 print "Checking for foreign keys required...\n" unless $silent;
921 foreach my $table ( keys %foreign_keys ) {
923 # read all indexes from $table
925 $sth = $dbh->prepare("show table status like '$table'");
927 my $stat = $sth->fetchrow_hashref;
928 # read indexes to check
929 my $tablerows = $foreign_keys{$table};
930 foreach my $row (@$tablerows) {
931 my $foreign_table=$row->{foreigntable};
932 if ($stat->{'Comment'} =~/$foreign_table/) {
933 # print "$foreign_table existing\n";
935 print "Creating $foreign_table in $table\n";
936 # first, drop any orphan value in child table
937 if ($row->{onDelete} ne "RESTRICT") {
938 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
940 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
942 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
943 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
944 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
947 print "====================
948 An error occured during :
950 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).
951 You can find those values with select
952 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
953 ====================\n
964 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
967 # 1st, get how many biblio we will have to do...
968 $sth = $dbh->prepare('select count(*) from marc_biblio');
970 my ($totaltodo) = $sth->fetchrow;
972 $sth = $dbh->prepare("show columns from biblio");
976 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
977 $bibliofwexist=1 if $column eq 'frameworkcode';
979 unless ($bibliofwexist) {
980 print "moving biblioframework to biblio table\n";
981 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
982 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
984 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
986 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
987 $sth_update->execute($frameworkcode,$biblionumber);
989 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
995 # moving MARC data from marc_subfield_table to biblioitems.marc
997 $sth = $dbh->prepare("show columns from biblioitems");
1001 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1002 $marcdone=1 if ($type eq 'blob' && $column eq 'marc') ;
1004 unless ($marcdone) {
1005 print "moving MARC record to biblioitems table\n";
1006 # changing marc field type
1007 $dbh->do('ALTER TABLE `biblioitems` CHANGE `marc` `marc` BLOB NULL DEFAULT NULL ');
1008 # adding marc xml, just for convenience
1009 $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT NOT NULL');
1010 # moving data from marc_subfield_value to biblio
1011 $sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
1013 my $sth_update = $dbh->prepare('update biblioitems set marc=?, marcxml=? where biblionumber=?');
1015 while (my ($bibid,$biblionumber) = $sth->fetchrow) {
1016 my $record = MARCgetbiblio($dbh,$bibid);
1017 $sth_update->execute($record->as_usmarc(),$record->as_xml(),$biblionumber);
1019 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1024 # MOVE all tables TO UTF-8 and innoDB
1025 $sth = $dbh->prepare("show table status");
1027 while ( my $table = $sth->fetchrow_hashref ) {
1028 if ($table->{Engine} ne 'InnoDB') {
1029 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1030 print "moving $table->{Name} to InnoDB\n";
1032 unless ($table->{Collation} =~ /^utf8/) {
1033 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1034 # 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 !
1035 print "moving $table->{Name} to utf8\n";
1040 # at last, remove useless fields
1041 foreach $table ( keys %uselessfields ) {
1042 my @fields = split /,/,$uselessfields{$table};
1045 foreach my $fieldtodrop (@fields) {
1046 $fieldtodrop =~ s/\t//g;
1047 $fieldtodrop =~ s/\n//g;
1049 $sth = $dbh->prepare("show columns from $table");
1051 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1053 $exists =1 if ($column eq $fieldtodrop);
1056 print "deleting $fieldtodrop field in $table...\n" unless $silent;
1057 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
1067 # those 2 subs are a copy of Biblio.pm, version 2.2.4
1068 # they are useful only once, for moving from 2.2 to 3.0
1069 # the MARCgetbiblio & MARCgetitem subs in Biblio.pm
1070 # are still here, but uses other tables
1071 # (the ones that are filled by updatedatabase !)
1075 # Returns MARC::Record of the biblio passed in parameter.
1076 my ( $dbh, $bibid ) = @_;
1077 my $record = MARC::Record->new();
1082 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1083 from marc_subfield_table
1084 where bibid=? order by tag,tagorder,subfieldorder
1089 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1090 $sth->execute($bibid);
1091 my $prevtagorder = 1;
1092 my $prevtag = 'XXX';
1094 my $field; # for >=10 tags
1095 my $prevvalue; # for <10 tags
1096 while ( my $row = $sth->fetchrow_hashref ) {
1098 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1099 $sth2->execute( $row->{'valuebloblink'} );
1100 my $row2 = $sth2->fetchrow_hashref;
1102 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1104 if ( $row->{tagorder} ne $prevtagorder || $row->{tag} ne $prevtag ) {
1105 $previndicator .= " ";
1106 if ( $prevtag < 10 ) {
1107 if ($prevtag ne '000') {
1108 $record->add_fields( ( sprintf "%03s", $prevtag ), $prevvalue ) unless $prevtag eq "XXX"; # ignore the 1st loop
1110 $record->leader(sprintf("%24s",$prevvalue));
1114 $record->add_fields($field) unless $prevtag eq "XXX";
1117 $prevtagorder = $row->{tagorder};
1118 $prevtag = $row->{tag};
1119 $previndicator = $row->{tag_indicator};
1120 if ( $row->{tag} < 10 ) {
1121 $prevvalue = $row->{subfieldvalue};
1124 $field = MARC::Field->new(
1125 ( sprintf "%03s", $prevtag ),
1126 substr( $row->{tag_indicator} . ' ', 0, 1 ),
1127 substr( $row->{tag_indicator} . ' ', 1, 1 ),
1128 $row->{'subfieldcode'},
1129 $row->{'subfieldvalue'}
1134 if ( $row->{tag} < 10 ) {
1135 $record->add_fields( ( sprintf "%03s", $row->{tag} ),
1136 $row->{'subfieldvalue'} );
1139 $field->add_subfields( $row->{'subfieldcode'},
1140 $row->{'subfieldvalue'} );
1142 $prevtag = $row->{tag};
1143 $previndicator = $row->{tag_indicator};
1147 # the last has not been included inside the loop... do it now !
1148 if ( $prevtag ne "XXX" )
1149 { # check that we have found something. Otherwise, prevtag is still XXX and we
1150 # must return an empty record, not make MARC::Record fail because we try to
1151 # create a record with XXX as field :-(
1152 if ( $prevtag < 10 ) {
1153 $record->add_fields( $prevtag, $prevvalue );
1157 # my $field = MARC::Field->new( $prevtag, "", "", %subfieldlist);
1158 $record->add_fields($field);
1166 # Returns MARC::Record of the biblio passed in parameter.
1167 my ( $dbh, $bibid, $itemnumber ) = @_;
1168 my $record = MARC::Record->new();
1170 # search MARC tagorder
1173 "select tagorder from marc_subfield_table,marc_subfield_structure where marc_subfield_table.tag=marc_subfield_structure.tagfield and marc_subfield_table.subfieldcode=marc_subfield_structure.tagsubfield and bibid=? and kohafield='items.itemnumber' and subfieldvalue=?"
1175 $sth2->execute( $bibid, $itemnumber );
1176 my ($tagorder) = $sth2->fetchrow_array();
1178 #---- TODO : the leader is missing
1181 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1182 from marc_subfield_table
1183 where bibid=? and tagorder=? order by subfieldcode,subfieldorder
1188 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1189 $sth->execute( $bibid, $tagorder );
1190 while ( my $row = $sth->fetchrow_hashref ) {
1191 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1192 $sth2->execute( $row->{'valuebloblink'} );
1193 my $row2 = $sth2->fetchrow_hashref;
1195 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1197 if ( $record->field( $row->{'tag'} ) ) {
1200 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
1201 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
1202 if ( length( $row->{'tag'} ) < 3 ) {
1203 $row->{'tag'} = "0" . $row->{'tag'};
1205 $field = $record->field( $row->{'tag'} );
1208 $field->add_subfields( $row->{'subfieldcode'},
1209 $row->{'subfieldvalue'} );
1210 $record->delete_field($field);
1211 $record->add_fields($field);
1215 if ( length( $row->{'tag'} ) < 3 ) {
1216 $row->{'tag'} = "0" . $row->{'tag'};
1219 MARC::Field->new( $row->{'tag'}, " ", " ",
1220 $row->{'subfieldcode'} => $row->{'subfieldvalue'} );
1221 $record->add_fields($temp);
1232 # Revision 1.128 2006/01/25 15:16:06 tipaul
1234 # * removing useless tables
1235 # * adding useful indexes
1236 # * altering some columns definitions
1237 # * The goal being to have updater working fine for foreign keys.
1239 # For me it's done, let me know if it works for you. You can see an updated schema of the DB (with constraints) on the wiki
1241 # Revision 1.127 2006/01/24 17:57:17 tipaul
1242 # DB improvements : adding foreign keys on some tables. partial stuff done.
1244 # Revision 1.126 2006/01/06 16:39:42 tipaul
1245 # synch'ing head and rel_2_2 (from 2.2.5, including npl templates)
1246 # Seems not to break too many things, but i'm probably wrong here.
1247 # at least, new features/bugfixes from 2.2.5 are here (tested on some features on my head local copy)
1249 # - removing useless directories (koha-html and koha-plucene)
1251 # Revision 1.125 2006/01/04 15:54:55 tipaul
1252 # utf8 is a : go for beta test in HEAD.
1253 # some explanations :
1254 # - updater/updatedatabase => will transform all tables in innoDB (not related to utf8, just to warn you) AND collate them in utf8 / utf8_general_ci. The SQL command is : ALTER TABLE tablename DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci.
1255 # - *-top.inc will show the pages in utf8
1256 # - THE HARD THING : for me, mysql-client and mysql-server were set up to communicate in iso8859-1, whatever the mysql collation ! Thus, pages were improperly shown, as datas were transmitted in iso8859-1 format ! After a full day of investigation, someone on usenet pointed "set NAMES 'utf8'" to explain that I wanted utf8. I could put this in my.cnf, but if I do that, ALL databases will "speak" in utf8, that's not what we want. Thus, I added a line in Context.pm : everytime a DB handle is opened, the communication is set to utf8.
1257 # - using marcxml field and no more the iso2709 raw marc biblioitems.marc field.
1259 # Revision 1.124 2005/10/27 12:09:05 tipaul
1260 # new features for serial module :
1261 # - the last 5 issues are now shown, and their status can be changed (but not reverted to "waited", as there can be only one "waited")
1262 # - the library can create a "distribution list". this paper contains a list of borrowers (selected from the borrower list, or manually entered), and print it for a given issue. once printed, the sheet can be put on the issue and distributed to every reader on the list (one by one).
1264 # Revision 1.123 2005/10/26 09:13:37 tipaul
1265 # big commit, still breaking things...
1267 # * synch with rel_2_2. Probably the last non manual synch, as rel_2_2 should not be modified deeply.
1268 # * code cleaning (cleaning warnings from perl -w) continued
1270 # Revision 1.122 2005/09/02 14:18:38 tipaul
1271 # new feature : image for itemtypes.
1273 # * run updater/updatedatabase to create imageurl field in itemtypes.
1274 # * go to Koha >> parameters >> itemtypes >> modify (or add) an itemtype. You will see around 20 nice images to choose between (thanks to owen). If you prefer your own image, you also can type a complete url (http://www.myserver.lib/path/to/my/image.gif)
1275 # * go to OPAC, and search something. In the result list, you now have the picture instead of the text itemtype.
1277 # Revision 1.121 2005/08/24 08:49:03 hdl
1278 # Adding a note field in serial table.
1279 # This will allow librarian to mention a note on a peculiar waiting serial number.
1281 # Revision 1.120 2005/08/09 14:10:32 tipaul
1282 # 1st commit to go to zebra.
1283 # don't update your cvs if you want to have a working head...
1285 # this commit contains :
1286 # * updater/updatedatabase : get rid with marc_* tables, but DON'T remove them. As a lot of things uses them, it would not be a good idea for instance to drop them. If you really want to play, you can rename them to test head without them but being still able to reintroduce them...
1287 # * Biblio.pm : modify MARCgetbiblio to find the raw marc record in biblioitems.marc field, not from marc_subfield_table, modify MARCfindframeworkcode to find frameworkcode in biblio.frameworkcode, modify some other subs to use biblio.biblionumber & get rid of bibid.
1288 # * other files : get rid of bibid and use biblionumber instead.
1291 # * does not do anything on zebra yet.
1292 # * if you rename marc_subfield_table, you can't search anymore.
1293 # * you can view a biblio & bibliodetails, go to MARC editor, but NOT save any modif.
1294 # * don't try to add a biblio, it would add data poorly... (don't try to delete either, it may work, but that would be a surprise ;-) )
1296 # IMPORTANT NOTE : you need MARC::XML package (http://search.cpan.org/~esummers/MARC-XML-0.7/lib/MARC/File/XML.pm), that requires a recent version of MARC::Record
1297 # Updatedatabase stores the iso2709 data in biblioitems.marc field & an xml version in biblioitems.marcxml Not sure we will keep it when releasing the stable version, but I think it's a good idea to have something readable in sql, at least for development stage.
1299 # Revision 1.119 2005/08/04 16:07:58 tipaul
1300 # Synch really broke this script...
1302 # Revision 1.118 2005/08/04 16:02:55 tipaul
1303 # oops... error in synch between 2.2 and head
1305 # Revision 1.117 2005/08/04 14:24:39 tipaul
1306 # synch'ing 2.2 and head
1308 # Revision 1.116 2005/08/04 08:55:54 tipaul
1309 # Letters / alert system, continuing...
1311 # * adding a package Letters.pm, that manages Letters & alerts.
1312 # * adding feature : it's now possible to define a "letter" for any subscription created. If a letter is defined, users in OPAC can put an alert on the subscription. When an issue is marked "arrived", all users in the alert will recieve a mail (as defined in the "letter"). This last part (= send the mail) is not yet developped. (Should be done this week)
1313 # * adding feature : it's now possible to "put to an alert" in OPAC, for any serial subscription. The alert is stored in a new table, called alert. An alert can be put only if the librarian has activated them in subscription (and they activate it just by choosing a "letter" to sent to borrowers on new issues)
1314 # * adding feature : librarian can see in borrower detail which alerts they have put, and a user can see in opac-detail which alert they have put too.
1316 # Note that the system should be generic enough to manage any type of alert.
1317 # I plan to extend it soon to virtual shelves : a borrower will be able to put an alert on a virtual shelf, to be warned when something is changed in the virtual shelf (mail being sent once a day by cron, or manually by the shelf owner. Anyway, a mail won't be sent on every change, users would be spammed by Koha ;-) )
1319 # Revision 1.115 2005/08/02 16:15:34 tipaul
1320 # adding 2 fields to letter system :
1321 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
1322 # * title, that will be used as mail subject.
1324 # Revision 1.114 2005/07/28 15:10:13 tipaul
1325 # Introducing new "Letters" system : Letters will be used everytime you want to sent something to someone (through mail or paper). For example, sending a mail for overdues use letter that you can put as parameters. Sending a mail to a borrower when a suggestion is validated uses a letter too.
1326 # the letter table contains 3 fields :
1327 # * code => the code of the letter
1328 # * name => the complete name of the letter
1329 # * content => the complete text. It's a TEXT field type, so has no limits.
1331 # My next goal now is to work on point 2-I "serial issue alert"
1332 # With this feature, in serials, a user can subscribe the "issue alert". For every issue arrived/missing, a mail is sent to all subscribers of this list. The mail warns the user that the issue is arrive or missing. Will be in head.
1333 # (see mail on koha-devel, 2005/04/07)
1335 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
1337 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
1339 # Revision 1.113 2005/07/28 08:38:41 tipaul
1340 # For instance, the return date does not rely on the borrower expiration date. A systempref will be added in Koha, to modify return date calculation schema :
1341 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
1342 # * ReturnBeforeExpiry = no => return date can be after expiry date
1344 # Revision 1.112 2005/07/26 08:19:47 hdl
1345 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
1347 # Revision 1.111 2005/07/25 15:35:38 tipaul
1348 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
1349 # So, the updatedatabase script can highly be cleaned (90% removed).
1350 # Let's play with the new Koha DB structure now ;-)