6 # This script checks for required updates to the database.
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
12 # - Would also be a good idea to offer to do a backup at this time...
14 # NOTE: If you do something more than once in here, make it table driven.
24 use MARC::File::XML ( BinaryEncoding => 'utf8' );
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
46 my $dbh = C4::Context->dbh;
47 print "connected to your DB. Checking & modifying it\n" unless $silent;
48 $|=1; # flushes output
53 # Tables to add if they don't exist
55 categorytable => "(categorycode char(5) NOT NULL default '',
56 description text default '',
57 itemtypecodes text default '',
58 PRIMARY KEY (categorycode)
60 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
61 description text default '',
62 itemtypecodes text default '',
63 PRIMARY KEY (subcategorycode)
65 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
66 description text default '',
67 itemtypecodes text default '',
68 PRIMARY KEY (mediatypecode)
71 `timestamp` TIMESTAMP NOT NULL ,
72 `user` INT( 11 ) NOT NULL ,
73 `module` TEXT default '',
74 `action` TEXT default '' ,
75 `object` INT(11) default '' ,
76 `info` TEXT default '' ,
77 PRIMARY KEY ( `timestamp` , `user` )
80 module varchar(20) NOT NULL default '',
81 code varchar(20) NOT NULL default '',
82 name varchar(100) NOT NULL default '',
83 title varchar(200) NOT NULL default '',
85 PRIMARY KEY (module,code)
88 alertid int(11) NOT NULL auto_increment,
89 borrowernumber int(11) NOT NULL default '0',
90 type varchar(10) NOT NULL default '',
91 externalid varchar(20) NOT NULL default '',
92 PRIMARY KEY (alertid),
93 KEY borrowernumber (borrowernumber),
94 KEY type (type,externalid)
97 `idnew` int(10) unsigned NOT NULL auto_increment,
98 `title` varchar(250) NOT NULL default '',
100 `lang` varchar(4) NOT NULL default '',
101 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
102 PRIMARY KEY (`idnew`)
104 repeatable_holidays => "(
105 `id` int(11) NOT NULL auto_increment,
106 `branchcode` varchar(4) NOT NULL default '',
107 `weekday` smallint(6) default NULL,
108 `day` smallint(6) default NULL,
109 `month` smallint(6) default NULL,
110 `title` varchar(50) NOT NULL default '',
111 `description` text NOT NULL,
114 special_holidays => "(
115 `id` int(11) NOT NULL auto_increment,
116 `branchcode` varchar(4) NOT NULL default '',
117 `day` smallint(6) NOT NULL default '0',
118 `month` smallint(6) NOT NULL default '0',
119 `year` smallint(6) NOT NULL default '0',
120 `isexception` smallint(1) NOT NULL default '1',
121 `title` varchar(50) NOT NULL default '',
122 `description` text NOT NULL,
125 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
126 `categorycode` char(2) NOT NULL default '',
127 `delay1` int(4) default '0',
128 `letter1` varchar(20) default NULL,
129 `debarred1` char(1) default '0',
130 `delay2` int(4) default '0',
131 `debarred2` char(1) default '0',
132 `letter2` varchar(20) default NULL,
133 `delay3` int(4) default '0',
134 `letter3` varchar(20) default NULL,
135 `debarred3` int(1) default '0',
136 PRIMARY KEY (`branchcode`,`categorycode`)
138 cities => "(`cityid` int auto_increment,
139 `city_name` char(100) NOT NULL,
140 `city_zipcode` char(20),
141 PRIMARY KEY (`cityid`)
143 roadtype => "(`roadtypeid` int auto_increment,
144 `road_type` char(100) NOT NULL,
145 PRIMARY KEY (`roadtypeid`)
149 labelid int(11) NOT NULL auto_increment,
150 itemnumber varchar(100) NOT NULL default '',
151 timestamp timestamp(14) NOT NULL,
152 PRIMARY KEY (labelid)
156 id int(4) NOT NULL auto_increment,
157 barcodetype char(100) default '',
158 title tinyint(1) default '0',
159 isbn tinyint(1) default '0',
160 itemtype tinyint(1) default '0',
161 barcode tinyint(1) default '0',
162 dewey tinyint(1) default '0',
163 class tinyint(1) default '0',
164 author tinyint(1) default '0',
165 papertype char(100) default '',
166 startrow int(2) default NULL,
172 my %requirefields = (
173 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'},
174 itemtypes => { 'imageurl' => 'char(200) NULL'},
175 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
176 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
177 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
178 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL UNSIGNED ZEROFILL', 'kohafield' => 'VARCHAR(45) NOT NULL', 'linkid' => 'TINYINT(1) NOT NULL UNSIGNED', 'isurl' => 'TINYINT(1) UNSIGNED'},
179 statistics => { 'associatedborrower' => 'integer'},
180 # tablename => { 'field' => 'fieldtype' },
183 my %dropable_table = (
184 sessionqueries => 'sessionqueries',
185 marcrecorddone => 'marcrecorddone',
187 itemsprices => 'itemsprices',
188 biblioanalysis => 'biblioanalysis',
190 # tablename => 'tablename',
193 my %uselessfields = (
194 # tablename => "field1,field2",
195 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
197 # the other hash contains other actions that can't be done elsewhere. they are done
198 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
200 # The tabledata hash contains data that should be in the tables.
201 # The uniquefieldrequired hash entry is used to determine which (if any) fields
202 # must not exist in the table for this row to be inserted. If the
203 # uniquefieldrequired entry is already in the table, the existing data is not
204 # modified, unless the forceupdate hash entry is also set. Fields in the
205 # anonymous "forceupdate" hash will be forced to be updated to the default
206 # values given in the %tabledata hash.
210 # { uniquefielrequired => 'fieldname', # the primary key in the table
211 # fieldname => fieldvalue,
212 # fieldname2 => fieldvalue2,
215 systempreferences => [
217 uniquefieldrequired => 'variable',
218 variable => 'Activate_Log',
220 forceupdate => { 'explanation' => 1,
222 explanation => 'Turn Log Actions on DB On an Off',
226 uniquefieldrequired => 'variable',
227 variable => 'IndependantBranches',
229 forceupdate => { 'explanation' => 1,
231 explanation => 'Turn Branch independancy management On an Off',
235 uniquefieldrequired => 'variable',
236 variable => 'ReturnBeforeExpiry',
238 forceupdate => { 'explanation' => 1,
240 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
244 uniquefieldrequired => 'variable',
245 variable => 'opacstylesheet',
247 forceupdate => { 'explanation' => 1,
249 explanation => 'Enter a complete URL to use an alternate stylesheet in OPAC',
253 uniquefieldrequired => 'variable',
254 variable => 'opacsmallimage',
256 forceupdate => { 'explanation' => 1,
258 explanation => 'Enter a complete URL to an image, will be on top/left instead of the Koha logo',
262 uniquefieldrequired => 'variable',
263 variable => 'opaclargeimage',
265 forceupdate => { 'explanation' => 1,
267 explanation => 'Enter a complete URL to an image, will be on the main page, instead of the Koha logo',
271 uniquefieldrequired => 'variable',
272 variable => 'delimiter',
274 forceupdate => { 'explanation' => 1,
276 explanation => 'separator for reports exported to spreadsheet',
280 uniquefieldrequired => 'variable',
282 value => 'OPENOFFICE.ORG',
283 forceupdate => { 'explanation' => 1,
286 explanation => 'Define the default application for report exportations into files',
288 options => 'EXCEL|OPENOFFICE.ORG'
291 uniquefieldrequired => 'variable',
292 variable => 'Delimiter',
294 forceupdate => { 'explanation' => 1,
297 explanation => 'Define the default separator character for report exportations into files',
299 options => ';|tabulation|,|/|\|#'
302 uniquefieldrequired => 'variable',
303 variable => 'SubscriptionHistory',
305 forceupdate => { 'explanation' => 1,
308 explanation => 'Define the information level for serials history in OPAC',
310 options => 'simplified|full'
313 uniquefieldrequired => 'variable',
314 variable => 'hidelostitems',
316 forceupdate => { 'explanation' => 1,
318 explanation => 'show or hide "lost" items in OPAC.',
322 uniquefieldrequired => 'variable',
323 variable => 'IndependantBranches',
325 forceupdate => { 'explanation' => 1,
327 explanation => 'Turn Branch independancy management On an Off',
331 uniquefieldrequired => 'variable',
332 variable => 'ReturnBeforeExpiry',
334 forceupdate => { 'explanation' => 1,
336 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
340 uniquefieldrequired => 'variable',
341 variable => 'Disable_Dictionary',
343 forceupdate => { 'explanation' => 1,
345 explanation => 'Disables Dictionary buttons if set to yes',
349 uniquefieldrequired => 'variable',
350 variable => 'hide_marc',
352 forceupdate => { 'explanation' => 1,
354 explanation => 'hide marc specific datas like subfield code & indicators to library',
358 uniquefieldrequired => 'variable',
359 variable => 'NotifyBorrowerDeparture',
361 forceupdate => { 'explanation' => 1,
363 explanation => 'Delay before expiry where a notice is sent when issuing',
367 uniquefieldrequired => 'variable',
368 variable => 'OpacPasswordChange',
370 forceupdate => { 'explanation' => 1,
372 explanation => 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
376 uniquefieldrequired => 'variable',
377 variable => 'useDaysMode',
379 forceupdate => { 'explanation' => 1,
381 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
383 options => 'Calendar|Days'
386 uniquefieldrequired => 'variable',
387 variable => 'borrowerMandatoryField',
388 value => 'zipcode|surname',
389 forceupdate => { 'explanation' => 1,
391 explanation => 'List all mandatory fields for borrowers',
395 uniquefieldrequired => 'variable',
396 variable => 'borrowerRelationship',
397 value => 'father|mother,grand-mother',
398 forceupdate => { 'explanation' => 1,
400 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
404 uniquefieldrequired => 'variable',
405 variable => 'ReservesMaxPickUpDelay',
407 forceupdate => { 'explanation' => 1,
409 explanation => 'Maximum delay to pick up a reserved document',
413 uniquefieldrequired => 'variable',
414 variable => 'TransfersMaxDaysWarning',
416 forceupdate => { 'explanation' => 1,
418 explanation => 'Max delay before considering the transfer has potentialy a problem',
422 uniquefieldrequired => 'variable',
423 variable => 'memberofinstitution',
425 forceupdate => { 'explanation' => 1,
427 explanation => 'Are your patrons members of institutions',
431 uniquefieldrequired => 'variable',
432 variable => 'ReadingHistory',
434 forceupdate => { 'explanation' => 1,
436 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
440 uniquefieldrequired => 'variable',
441 variable => 'IssuingInProcess',
443 forceupdate => { 'explanation' => 1,
445 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
449 uniquefieldrequired => 'variable',
450 variable => 'AutomaticItemReturn',
452 forceupdate => { 'explanation' => 1,
454 explanation => 'This Variable allow or not to return automaticly to his homebranch',
461 my %fielddefinitions = (
463 # { field => 'fieldname',
464 # type => 'fieldtype',
482 field => 'booksellerid',
492 field => 'listprice',
493 type => 'varchar(10)',
500 field => 'invoiceprice',
501 type => 'varchar(10)',
510 field => 'borrowernumber',
512 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
518 field => 'itemnumber',
520 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
527 { field => 'B_email',
530 after => 'B_zipcode',
533 field => 'streetnumber', # street number (hidden if streettable table is empty)
539 field => 'streettype', # street table, list builded from a system table
542 after => 'streetnumber',
545 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
551 field => 'B_streettype', # street table, list builded from a system table
554 after => 'B_streetnumber',
563 field => 'address2', # complement address
575 field => 'contactfirstname', # contact's firstname
578 after => 'contactname',
581 field => 'contacttitle', # contact's title
584 after => 'contactfirstname',
591 type => 'varchar(15)',
598 field => 'branchprinter',
599 type => 'varchar(100)',
608 field => 'category_type',
618 field => 'waitingdate',
630 # { indexname => 'index detail'
634 { indexname => 'shelfnumber',
635 content => 'shelfnumber',
637 { indexname => 'itemnumber',
638 content => 'itemnumber',
642 { indexname => 'biblionumber',
643 content => 'biblionumber',
647 { indexname => 'homebranch',
648 content => 'homebranch',
650 { indexname => 'holdingbranch',
651 content => 'holdingbranch',
655 { indexname => 'PRIMARY',
661 { indexname => 'booksellerid',
662 content => 'booksellerid',
666 { indexname => 'basketno',
667 content => 'basketno',
670 aqorderbreakdown => [
671 { indexname => 'ordernumber',
672 content => 'ordernumber',
674 { indexname => 'bookfundid',
675 content => 'bookfundid',
679 { indexname => 'PRIMARY',
680 content => 'currency',
688 # { key => 'the key in table' (must be indexed)
689 # foreigntable => 'the foreigntable name', # (the parent)
690 # foreignkey => 'the foreign key column(s)' # (in the parent)
691 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
692 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
696 { key => 'shelfnumber',
697 foreigntable => 'bookshelf',
698 foreignkey => 'shelfnumber',
699 onUpdate => 'CASCADE',
700 onDelete => 'CASCADE',
702 { key => 'itemnumber',
703 foreigntable => 'items',
704 foreignkey => 'itemnumber',
705 onUpdate => 'CASCADE',
706 onDelete => 'CASCADE',
709 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
710 # easily deleted, but branches/itemtype not too easy to empty...
712 { key => 'biblionumber',
713 foreigntable => 'biblio',
714 foreignkey => 'biblionumber',
715 onUpdate => 'CASCADE',
716 onDelete => 'CASCADE',
719 foreigntable => 'itemtypes',
720 foreignkey => 'itemtype',
721 onUpdate => 'CASCADE',
722 onDelete => 'RESTRICT',
726 { key => 'biblioitemnumber',
727 foreigntable => 'biblioitems',
728 foreignkey => 'biblioitemnumber',
729 onUpdate => 'CASCADE',
730 onDelete => 'CASCADE',
732 { key => 'homebranch',
733 foreigntable => 'branches',
734 foreignkey => 'branchcode',
735 onUpdate => 'CASCADE',
736 onDelete => 'RESTRICT',
738 { key => 'holdingbranch',
739 foreigntable => 'branches',
740 foreignkey => 'branchcode',
741 onUpdate => 'CASCADE',
742 onDelete => 'RESTRICT',
745 additionalauthors => [
746 { key => 'biblionumber',
747 foreigntable => 'biblio',
748 foreignkey => 'biblionumber',
749 onUpdate => 'CASCADE',
750 onDelete => 'CASCADE',
754 { key => 'biblionumber',
755 foreigntable => 'biblio',
756 foreignkey => 'biblionumber',
757 onUpdate => 'CASCADE',
758 onDelete => 'CASCADE',
762 { key => 'booksellerid',
763 foreigntable => 'aqbooksellers',
765 onUpdate => 'CASCADE',
766 onDelete => 'RESTRICT',
771 foreigntable => 'aqbasket',
772 foreignkey => 'basketno',
773 onUpdate => 'CASCADE',
774 onDelete => 'CASCADE',
776 { key => 'biblionumber',
777 foreigntable => 'biblio',
778 foreignkey => 'biblionumber',
779 onUpdate => 'SET NULL',
780 onDelete => 'SET NULL',
784 { key => 'listprice',
785 foreigntable => 'currency',
786 foreignkey => 'currency',
787 onUpdate => 'CASCADE',
788 onDelete => 'CASCADE',
790 { key => 'invoiceprice',
791 foreigntable => 'currency',
792 foreignkey => 'currency',
793 onUpdate => 'CASCADE',
794 onDelete => 'CASCADE',
797 aqorderbreakdown => [
798 { key => 'ordernumber',
799 foreigntable => 'aqorders',
800 foreignkey => 'ordernumber',
801 onUpdate => 'CASCADE',
802 onDelete => 'CASCADE',
804 { key => 'bookfundid',
805 foreigntable => 'aqbookfund',
806 foreignkey => 'bookfundid',
807 onUpdate => 'CASCADE',
808 onDelete => 'CASCADE',
812 { key => 'frombranch',
813 foreigntable => 'branches',
814 foreignkey => 'branchcode',
815 onUpdate => 'CASCADE',
816 onDelete => 'CASCADE',
819 foreigntable => 'branches',
820 foreignkey => 'branchcode',
821 onUpdate => 'CASCADE',
822 onDelete => 'CASCADE',
824 { key => 'itemnumber',
825 foreigntable => 'items',
826 foreignkey => 'itemnumber',
827 onUpdate => 'CASCADE',
828 onDelete => 'CASCADE',
832 { key => 'categorycode',
833 foreigntable => 'categories',
834 foreignkey => 'categorycode',
835 onUpdate => 'CASCADE',
836 onDelete => 'CASCADE',
839 foreigntable => 'itemtypes',
840 foreignkey => 'itemtype',
841 onUpdate => 'CASCADE',
842 onDelete => 'CASCADE',
845 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
847 { key => 'borrowernumber',
848 foreigntable => 'borrowers',
849 foreignkey => 'borrowernumber',
850 onUpdate => 'SET NULL',
851 onDelete => 'SET NULL',
853 { key => 'itemnumber',
854 foreigntable => 'items',
855 foreignkey => 'itemnumber',
856 onUpdate => 'SET NULL',
857 onDelete => 'SET NULL',
861 { key => 'borrowernumber',
862 foreigntable => 'borrowers',
863 foreignkey => 'borrowernumber',
864 onUpdate => 'CASCADE',
865 onDelete => 'CASCADE',
867 { key => 'biblionumber',
868 foreigntable => 'biblio',
869 foreignkey => 'biblionumber',
870 onUpdate => 'CASCADE',
871 onDelete => 'CASCADE',
873 { key => 'itemnumber',
874 foreigntable => 'items',
875 foreignkey => 'itemnumber',
876 onUpdate => 'CASCADE',
877 onDelete => 'CASCADE',
879 { key => 'branchcode',
880 foreigntable => 'branches',
881 foreignkey => 'branchcode',
882 onUpdate => 'CASCADE',
883 onDelete => 'CASCADE',
886 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
887 # but prevent deleting a branch as soon as it has 1 borrower !
888 { key => 'categorycode',
889 foreigntable => 'categories',
890 foreignkey => 'categorycode',
891 onUpdate => 'RESTRICT',
892 onDelete => 'RESTRICT',
894 { key => 'branchcode',
895 foreigntable => 'branches',
896 foreignkey => 'branchcode',
897 onUpdate => 'RESTRICT',
898 onDelete => 'RESTRICT',
902 { key => 'borrowernumber',
903 foreigntable => 'borrowers',
904 foreignkey => 'borrowernumber',
905 onUpdate => 'CASCADE',
906 onDelete => 'CASCADE',
908 { key => 'itemnumber',
909 foreigntable => 'items',
910 foreignkey => 'itemnumber',
911 onUpdate => 'SET NULL',
912 onDelete => 'SET NULL',
915 auth_tag_structure => [
916 { key => 'authtypecode',
917 foreigntable => 'auth_types',
918 foreignkey => 'authtypecode',
919 onUpdate => 'CASCADE',
920 onDelete => 'CASCADE',
923 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
928 my %column_change = (
932 from => 'emailaddress',
937 from => 'streetaddress',
947 from => 'textmessaging',
957 from => 'physstreet',
962 from => 'streetcity',
964 after => 'B_address',
977 from => 'homezipcode',
984 after => 'B_zipcode',
989 after => 'dateenrolled',
994 after => 'contactname',
997 from => 'textmessaging',
1003 to => 'contactnotes',
1004 after => 'opacnotes',
1007 from => 'altrelationship',
1008 to => 'relationship',
1009 after => 'borrowernotes',
1014 foreach my $table (keys %column_change) {
1015 $sth = $dbh->prepare("show columns from $table");
1018 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1020 $types{$column}->{type} ="$type";
1021 $types{$column}->{null} = "$null";
1022 $types{$column}->{key} = "$key";
1023 $types{$column}->{default} = "$default";
1024 $types{$column}->{extra} = "$extra";
1026 my $tablerows = $column_change{$table};
1027 foreach my $row ( @$tablerows ) {
1028 if ($types{$row->{from}}->{type}) {
1029 print "altering $table $row->{from} to $row->{to}\n";
1030 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1031 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1033 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1034 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1035 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1036 "$types{$row->{from}}->{extra} after $row->{after} ";
1043 #-------------------
1048 # Get version of MySQL database engine.
1049 my $mysqlversion = `mysqld --version`;
1050 $mysqlversion =~ /Ver (\S*) /;
1052 if ( $mysqlversion ge '3.23' ) {
1053 print "Could convert to MyISAM database tables...\n" unless $silent;
1056 #---------------------------------
1059 # Collect all tables into a list
1060 $sth = $dbh->prepare("show tables");
1062 while ( my ($table) = $sth->fetchrow ) {
1063 $existingtables{$table} = 1;
1067 # Now add any missing tables
1068 foreach $table ( keys %requiretables ) {
1069 unless ( $existingtables{$table} ) {
1070 print "Adding $table table...\n" unless $silent;
1071 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1074 print "Error : $sth->errstr \n";
1080 # now drop useless tables
1081 foreach $table ( keys %dropable_table ) {
1082 if ( $existingtables{$table} ) {
1083 print "Dropping unused table $table\n" if $debug and not $silent;
1084 $dbh->do("drop table $table");
1086 print "Error : $dbh->errstr \n";
1091 #---------------------------------
1094 foreach $table ( keys %requirefields ) {
1095 print "Check table $table\n" if $debug and not $silent;
1096 $sth = $dbh->prepare("show columns from $table");
1099 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1101 $types{$column} = $type;
1103 foreach $column ( keys %{ $requirefields{$table} } ) {
1104 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1105 if ( !$types{$column} ) {
1107 # column doesn't exist
1108 print "Adding $column field to $table table...\n" unless $silent;
1109 $query = "alter table $table
1110 add column $column " . $requirefields{$table}->{$column};
1111 print "Execute: $query\n" if $debug;
1112 my $sti = $dbh->prepare($query);
1115 print "**Error : $sti->errstr \n";
1122 foreach $table ( keys %fielddefinitions ) {
1123 print "Check table $table\n" if $debug;
1124 $sth = $dbh->prepare("show columns from $table");
1127 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1129 $definitions->{$column}->{type} = $type;
1130 $definitions->{$column}->{null} = $null;
1131 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1132 $definitions->{$column}->{key} = $key;
1133 $definitions->{$column}->{default} = $default;
1134 $definitions->{$column}->{extra} = $extra;
1136 my $fieldrow = $fielddefinitions{$table};
1137 foreach my $row (@$fieldrow) {
1138 my $field = $row->{field};
1139 my $type = $row->{type};
1140 my $null = $row->{null};
1141 # $null = 'YES' if $row->{null} eq 'NULL';
1142 my $key = $row->{key};
1143 my $default = $row->{default};
1144 my $null = $row->{null};
1145 # $default="''" unless $default;
1146 my $extra = $row->{extra};
1147 my $def = $definitions->{$field};
1148 my $after = ($row->{after}?" after ".$row->{after}:"");
1150 unless ( $type eq $def->{type}
1151 && $null eq $def->{null}
1152 && $key eq $def->{key}
1153 && $extra eq $def->{extra} )
1155 if ( $null eq '' ) {
1158 if ( $key eq 'PRI' ) {
1159 $key = 'PRIMARY KEY';
1161 unless ( $extra eq 'auto_increment' ) {
1165 # if it's a new column use "add", if it's an old one, use "change".
1167 if ($definitions->{$field}->{type}) {
1168 $action="change $field"
1172 # if it's a primary key, drop the previous pk, before altering the table
1174 if ($key ne 'PRIMARY KEY') {
1175 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ? $after");
1177 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ? $after");
1179 $sth->execute($default);
1180 print " alter or create $field in $table\n" unless $silent;
1185 # Populate tables with required data
1188 # synch table and deletedtable.
1189 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1190 my %deletedborrowers;
1191 print "synch'ing $table\n";
1192 $sth = $dbh->prepare("show columns from deleted$table");
1194 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1195 $deletedborrowers{$column}=1;
1197 $sth = $dbh->prepare("show columns from $table");
1200 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1201 unless ($deletedborrowers{$column}) {
1202 my $newcol="alter table deleted$table add $column $type";
1203 if ($null eq 'YES') {
1204 $newcol .= " NULL ";
1206 $newcol .= " NOT NULL ";
1208 $newcol .= "default $default" if $default;
1209 $newcol .= " after $previous" if $previous;
1211 print "creating column $column\n";
1217 foreach my $table ( keys %tabledata ) {
1218 print "Checking for data required in table $table...\n" unless $silent;
1219 my $tablerows = $tabledata{$table};
1220 foreach my $row (@$tablerows) {
1221 my $uniquefieldrequired = $row->{uniquefieldrequired};
1222 my $uniquevalue = $row->{$uniquefieldrequired};
1223 my $forceupdate = $row->{forceupdate};
1226 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1228 $sth->execute($uniquevalue);
1230 foreach my $field (keys %$forceupdate) {
1231 if ($forceupdate->{$field}) {
1232 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1233 $sth->execute($row->{$field}, $uniquevalue);
1237 print "Adding row to $table: " unless $silent;
1241 foreach my $field ( keys %$row ) {
1242 next if $field eq 'uniquefieldrequired';
1243 next if $field eq 'forceupdate';
1244 my $value = $row->{$field};
1245 push @values, $value;
1246 print " $field => $value" unless $silent;
1247 $fieldlist .= "$field,";
1248 $placeholders .= "?,";
1250 print "\n" unless $silent;
1251 $fieldlist =~ s/,$//;
1252 $placeholders =~ s/,$//;
1255 "insert into $table ($fieldlist) values ($placeholders)");
1256 $sth->execute(@values);
1262 # check indexes and create them when needed
1264 print "Checking for index required...\n" unless $silent;
1265 foreach my $table ( keys %indexes ) {
1267 # read all indexes from $table
1269 $sth = $dbh->prepare("show index from $table");
1271 my %existingindexes;
1272 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
1273 $existingindexes{$key_name} = 1;
1275 # read indexes to check
1276 my $tablerows = $indexes{$table};
1277 foreach my $row (@$tablerows) {
1278 my $key_name=$row->{indexname};
1279 if ($existingindexes{$key_name} eq 1) {
1280 # print "$key_name existing";
1282 print "\tCreating index $key_name in $table\n";
1284 if ($row->{indexname} eq 'PRIMARY') {
1285 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
1287 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
1290 print "Error $sql : $dbh->err \n" if $dbh->err;
1296 # check foreign keys and create them when needed
1298 print "Checking for foreign keys required...\n" unless $silent;
1299 foreach my $table ( keys %foreign_keys ) {
1301 # read all indexes from $table
1303 $sth = $dbh->prepare("show table status like '$table'");
1305 my $stat = $sth->fetchrow_hashref;
1306 # read indexes to check
1307 my $tablerows = $foreign_keys{$table};
1308 foreach my $row (@$tablerows) {
1309 my $foreign_table=$row->{foreigntable};
1310 if ($stat->{'Comment'} =~/$foreign_table/) {
1311 # print "$foreign_table existing\n";
1313 print "\tCreating foreign key $foreign_table in $table\n";
1314 # first, drop any orphan value in child table
1315 if ($row->{onDelete} ne "RESTRICT") {
1316 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
1318 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
1320 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
1321 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
1322 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
1325 print "====================
1326 An error occured during :
1328 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).
1329 You can find those values with select
1330 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
1331 ====================\n
1342 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
1345 # 1st, get how many biblio we will have to do...
1346 $sth = $dbh->prepare('select count(*) from marc_biblio');
1348 my ($totaltodo) = $sth->fetchrow;
1350 $sth = $dbh->prepare("show columns from biblio");
1353 my $bibliofwexist=0;
1354 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1355 $bibliofwexist=1 if $column eq 'frameworkcode';
1357 unless ($bibliofwexist) {
1358 print "moving biblioframework to biblio table\n";
1359 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
1360 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
1362 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
1364 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
1365 $sth_update->execute($frameworkcode,$biblionumber);
1367 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1373 # moving MARC data from marc_subfield_table to biblioitems.marc
1375 $sth = $dbh->prepare("show columns from biblioitems");
1379 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1380 $marcdone=1 if ($type eq 'blob' && $column eq 'marc') ;
1382 unless ($marcdone) {
1383 print "moving MARC record to biblioitems table\n";
1384 # changing marc field type
1385 $dbh->do('ALTER TABLE `biblioitems` CHANGE `marc` `marc` BLOB NULL DEFAULT NULL ');
1386 # adding marc xml, just for convenience
1387 $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ');
1388 # moving data from marc_subfield_value to biblio
1389 $sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
1391 my $sth_update = $dbh->prepare('update biblioitems set marc=?, marcxml=? where biblionumber=?');
1393 while (my ($bibid,$biblionumber) = $sth->fetchrow) {
1394 my $record = MARCgetbiblio($dbh,$bibid);
1395 #Force UTF-8 in record leader
1396 $record->encoding('UTF-8');
1397 print $record->as_formatted if ($biblionumber==3902);
1398 $sth_update->execute($record->as_usmarc(),$record->as_xml_record(),$biblionumber);
1400 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1406 # at last, remove useless fields
1407 foreach $table ( keys %uselessfields ) {
1408 my @fields = split /,/,$uselessfields{$table};
1411 foreach my $fieldtodrop (@fields) {
1412 $fieldtodrop =~ s/\t//g;
1413 $fieldtodrop =~ s/\n//g;
1415 $sth = $dbh->prepare("show columns from $table");
1417 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1419 $exists =1 if ($column eq $fieldtodrop);
1422 print "deleting $fieldtodrop field in $table...\n" unless $silent;
1423 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
1430 # MOVE all tables TO UTF-8 and innoDB
1431 $sth = $dbh->prepare("show table status");
1433 while ( my $table = $sth->fetchrow_hashref ) {
1434 # if ($table->{Engine} ne 'InnoDB') {
1435 # $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1436 # print "moving $table->{Name} to InnoDB\n";
1438 unless ($table->{Collation} =~ /^utf8/) {
1439 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1440 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1441 # 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 !
1442 print "moving $table->{Name} to utf8\n";
1450 # those 2 subs are a copy of Biblio.pm, version 2.2.4
1451 # they are useful only once, for moving from 2.2 to 3.0
1452 # the MARCgetbiblio & MARCgetitem subs in Biblio.pm
1453 # are still here, but uses other tables
1454 # (the ones that are filled by updatedatabase !)
1459 # Returns MARC::Record of the biblio passed in parameter.
1460 my ( $dbh, $bibid ) = @_;
1461 my $record = MARC::Record->new();
1466 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1467 from marc_subfield_table
1468 where bibid=? order by tag,tagorder,subfieldorder
1473 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1474 $sth->execute($bibid);
1475 my $prevtagorder = 1;
1476 my $prevtag = 'XXX';
1478 my $field; # for >=10 tags
1479 my $prevvalue; # for <10 tags
1480 while ( my $row = $sth->fetchrow_hashref ) {
1482 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1483 $sth2->execute( $row->{'valuebloblink'} );
1484 my $row2 = $sth2->fetchrow_hashref;
1486 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1488 if ( $row->{tagorder} ne $prevtagorder || $row->{tag} ne $prevtag ) {
1489 $previndicator .= " ";
1490 if ( $prevtag < 10 ) {
1491 if ($prevtag ne '000') {
1492 $record->add_fields( ( sprintf "%03s", $prevtag ), $prevvalue ) unless $prevtag eq "XXX"; # ignore the 1st loop
1494 $record->leader(sprintf("%24s",$prevvalue));
1498 $record->add_fields($field) unless $prevtag eq "XXX";
1501 $prevtagorder = $row->{tagorder};
1502 $prevtag = $row->{tag};
1503 $previndicator = $row->{tag_indicator};
1504 if ( $row->{tag} < 10 ) {
1505 $prevvalue = $row->{subfieldvalue};
1508 $field = MARC::Field->new(
1509 ( sprintf "%03s", $prevtag ),
1510 substr( $row->{tag_indicator} . ' ', 0, 1 ),
1511 substr( $row->{tag_indicator} . ' ', 1, 1 ),
1512 $row->{'subfieldcode'},
1513 $row->{'subfieldvalue'}
1518 if ( $row->{tag} < 10 ) {
1519 $record->add_fields( ( sprintf "%03s", $row->{tag} ),
1520 $row->{'subfieldvalue'} );
1523 $field->add_subfields( $row->{'subfieldcode'},
1524 $row->{'subfieldvalue'} );
1526 $prevtag = $row->{tag};
1527 $previndicator = $row->{tag_indicator};
1531 # the last has not been included inside the loop... do it now !
1532 if ( $prevtag ne "XXX" )
1533 { # check that we have found something. Otherwise, prevtag is still XXX and we
1534 # must return an empty record, not make MARC::Record fail because we try to
1535 # create a record with XXX as field :-(
1536 if ( $prevtag < 10 ) {
1537 $record->add_fields( $prevtag, $prevvalue );
1541 # my $field = MARC::Field->new( $prevtag, "", "", %subfieldlist);
1542 $record->add_fields($field);
1550 # Returns MARC::Record of the biblio passed in parameter.
1551 my ( $dbh, $bibid, $itemnumber ) = @_;
1552 my $record = MARC::Record->new();
1554 # search MARC tagorder
1557 "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=?"
1559 $sth2->execute( $bibid, $itemnumber );
1560 my ($tagorder) = $sth2->fetchrow_array();
1562 #---- TODO : the leader is missing
1565 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1566 from marc_subfield_table
1567 where bibid=? and tagorder=? order by subfieldcode,subfieldorder
1572 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1573 $sth->execute( $bibid, $tagorder );
1574 while ( my $row = $sth->fetchrow_hashref ) {
1575 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1576 $sth2->execute( $row->{'valuebloblink'} );
1577 my $row2 = $sth2->fetchrow_hashref;
1579 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1581 if ( $record->field( $row->{'tag'} ) ) {
1584 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
1585 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
1586 if ( length( $row->{'tag'} ) < 3 ) {
1587 $row->{'tag'} = "0" . $row->{'tag'};
1589 $field = $record->field( $row->{'tag'} );
1592 $field->add_subfields( $row->{'subfieldcode'},
1593 $row->{'subfieldvalue'} );
1594 $record->delete_field($field);
1595 $record->add_fields($field);
1599 if ( length( $row->{'tag'} ) < 3 ) {
1600 $row->{'tag'} = "0" . $row->{'tag'};
1603 MARC::Field->new( $row->{'tag'}, " ", " ",
1604 $row->{'subfieldcode'} => $row->{'subfieldvalue'} );
1605 $record->add_fields($temp);
1616 # Revision 1.144 2006/06/08 15:36:31 alaurin
1617 # Add a new system preference 'AutomaticItemReturn' :
1619 # if this prefence is switched on: the document returned in another library than homebranch, the system automaticly transfer the document to his homebranch (with notification for librarian in returns.tmpl) .
1621 # switch off : the document stay in the holdingbranch ...
1624 # - comment C4::acquisition (not using in request.pl).
1625 # - correcting date in request.pl
1626 # -add the new call of function getbranches in request.pl
1628 # Revision 1.143 2006/06/07 02:02:47 bob_lyon
1629 # merging katipo changes...
1631 # adding new preference IssuingInProcess
1633 # Revision 1.142 2006/06/06 23:42:46 bob_lyon
1634 # Merging Katipo changes...
1636 # Adding new system pref where one can still retrieve a correct reading
1637 # record history if one has moved older data from issues to oldissues table
1638 # to speed up issues speed
1640 # Revision 1.141 2006/06/01 03:18:11 rangi
1641 # Adding a new column to the statistics table
1643 # Revision 1.140 2006/05/22 22:40:45 rangi
1644 # Adding new systempreference allowing for the library to add borrowers to institutions (rest homes, parishes, schools, classes etc).
1646 # Revision 1.139 2006/05/19 19:31:29 tgarip1957
1647 # Added new fields to auth_header and auth_subfield_table to allow ZEBRA use of authorities and new MARC framework like structure.
1648 # Authority tables are modified to be compatible with new MARC frameworks. This change is part of Authority Linking & Zebra authorities. Requires change in Mysql database. It will break head unless all changes regarding this is implemented. This warning will take place on all commits regarding this
1650 # Revision 1.138 2006/05/19 16:51:44 alaurin
1651 # update database for :
1652 # - new feature ip and printer management
1653 # adding two fields in branches table (branchip,branchprinter)
1655 # - waiting date : adding one field in reserves table(waiting date) to calculate the Maximum delay to pick up a reserved document when it's available
1657 # new system preference :
1658 # - ReservesMaxPickUpDelay : Maximum delay to pick up a reserved document
1659 # TransfersMaxDaysWarning : Max delay before considering the transfer as potentialy a problem
1661 # Revision 1.137 2006/04/18 09:36:36 plg
1662 # bug fixed: typo fixed in labels and labels_conf tables creation query.
1664 # Revision 1.136 2006/04/17 21:55:33 sushi
1665 # Added 'labels' and 'labels_conf' tables, for spine lable tool.
1667 # Revision 1.135 2006/04/15 02:37:03 tgarip1957
1668 # Marc record should be set to UTF-8 in leader.Force it.
1669 # XML should be with<record> wrappers
1671 # Revision 1.134 2006/04/14 09:37:29 tipaul
1672 # improvements from SAN Ouest Provence :
1673 # * introducing a category_type into categories. It can be A (adult), C (children), P (Professionnal), I (institution/organisation).
1674 # * each category_type has it's own forms to create members.
1675 # * the borrowers table has been heavily modified (many fields changed), to get something more logic & readable
1676 # * reintroducing guarantor/guanrantee system that is now independant from hardcoded C/A for categories
1677 # * updating templates to fit template rules
1679 # (see mail feb, 17 on koha-devel "new features for borrowers" for more details)
1681 # Revision 1.133 2006/04/13 08:36:42 plg
1682 # new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on
1683 # the system preference prefered date format.
1685 # improvement: book fund list and budget list screen redesigned. Filters on
1686 # each field. Columns are not sortable yet. Using DHTML Calendar to fill date
1687 # fields instead of manual filling. Pagination system. From the book fund
1688 # list, you can reach the budget list, filtered on a book fund, or not. A
1689 # budget can be added only from book fund list screen.
1691 # bug fixed: branchcode was missing in table aqbudget.
1693 # bug fixed: when setting a branchcode to a book fund, all associated budgets
1694 # move to this branchcode.
1696 # modification: when adding/modifying budget/fund, MySQL specific "REPLACE..."
1697 # statements replaced by standard SQL compliant statement.
1699 # bug fixed: when adding/modifying a budget, if the book fund is associated to
1700 # a branch, the branch selection is disabled and set to the book fund branch.
1702 # Revision 1.132 2006/04/06 12:37:05 hdl
1703 # Bugfixing : aqbookfund needed a field.
1705 # Revision 1.131 2006/03/03 17:02:22 tipaul
1706 # commit for holidays and news management.
1707 # (some forgotten files)
1709 # Revision 1.130 2006/03/03 16:35:21 tipaul
1710 # commit for holidays and news management.
1712 # Contrib from Tümer Garip (from Turkey) :
1714 # in /tools/ the holiday.pl script let you define holidays (days where the library is closed), branch by branch. You can define 3 types of holidays :
1715 # - single day : only this day is closed
1716 # - repet weekly (like "sunday") : the day is holiday every week
1717 # - repet yearly (like "July, 4") : this day is closed every year.
1719 # You can also put exception :
1720 # - sunday is holiday, but "2006 March, 5th" the library will be open
1722 # The holidays are used for return date calculation : the return date is set to the next date where the library is open. A systempreference (useDaysMode) set ON (Calendar) or OFF (Normal) the calendar calculation.
1724 # Revision 1.129 2006/02/27 18:19:33 hdl
1725 # New table used in overduerules.pl tools page.
1727 # Revision 1.128 2006/01/25 15:16:06 tipaul
1729 # * removing useless tables
1730 # * adding useful indexes
1731 # * altering some columns definitions
1732 # * The goal being to have updater working fine for foreign keys.
1734 # 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
1736 # Revision 1.127 2006/01/24 17:57:17 tipaul
1737 # DB improvements : adding foreign keys on some tables. partial stuff done.
1739 # Revision 1.126 2006/01/06 16:39:42 tipaul
1740 # synch'ing head and rel_2_2 (from 2.2.5, including npl templates)
1741 # Seems not to break too many things, but i'm probably wrong here.
1742 # at least, new features/bugfixes from 2.2.5 are here (tested on some features on my head local copy)
1744 # - removing useless directories (koha-html and koha-plucene)
1746 # Revision 1.125 2006/01/04 15:54:55 tipaul
1747 # utf8 is a : go for beta test in HEAD.
1748 # some explanations :
1749 # - 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.
1750 # - *-top.inc will show the pages in utf8
1751 # - 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.
1752 # - using marcxml field and no more the iso2709 raw marc biblioitems.marc field.
1754 # Revision 1.124 2005/10/27 12:09:05 tipaul
1755 # new features for serial module :
1756 # - 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")
1757 # - 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).
1759 # Revision 1.123 2005/10/26 09:13:37 tipaul
1760 # big commit, still breaking things...
1762 # * synch with rel_2_2. Probably the last non manual synch, as rel_2_2 should not be modified deeply.
1763 # * code cleaning (cleaning warnings from perl -w) continued
1765 # Revision 1.122 2005/09/02 14:18:38 tipaul
1766 # new feature : image for itemtypes.
1768 # * run updater/updatedatabase to create imageurl field in itemtypes.
1769 # * 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)
1770 # * go to OPAC, and search something. In the result list, you now have the picture instead of the text itemtype.
1772 # Revision 1.121 2005/08/24 08:49:03 hdl
1773 # Adding a note field in serial table.
1774 # This will allow librarian to mention a note on a peculiar waiting serial number.
1776 # Revision 1.120 2005/08/09 14:10:32 tipaul
1777 # 1st commit to go to zebra.
1778 # don't update your cvs if you want to have a working head...
1780 # this commit contains :
1781 # * 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...
1782 # * 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.
1783 # * other files : get rid of bibid and use biblionumber instead.
1786 # * does not do anything on zebra yet.
1787 # * if you rename marc_subfield_table, you can't search anymore.
1788 # * you can view a biblio & bibliodetails, go to MARC editor, but NOT save any modif.
1789 # * 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 ;-) )
1791 # 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
1792 # 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.
1794 # Revision 1.119 2005/08/04 16:07:58 tipaul
1795 # Synch really broke this script...
1797 # Revision 1.118 2005/08/04 16:02:55 tipaul
1798 # oops... error in synch between 2.2 and head
1800 # Revision 1.117 2005/08/04 14:24:39 tipaul
1801 # synch'ing 2.2 and head
1803 # Revision 1.116 2005/08/04 08:55:54 tipaul
1804 # Letters / alert system, continuing...
1806 # * adding a package Letters.pm, that manages Letters & alerts.
1807 # * 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)
1808 # * 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)
1809 # * 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.
1811 # Note that the system should be generic enough to manage any type of alert.
1812 # 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 ;-) )
1814 # Revision 1.115 2005/08/02 16:15:34 tipaul
1815 # adding 2 fields to letter system :
1816 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
1817 # * title, that will be used as mail subject.
1819 # Revision 1.114 2005/07/28 15:10:13 tipaul
1820 # 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.
1821 # the letter table contains 3 fields :
1822 # * code => the code of the letter
1823 # * name => the complete name of the letter
1824 # * content => the complete text. It's a TEXT field type, so has no limits.
1826 # My next goal now is to work on point 2-I "serial issue alert"
1827 # 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.
1828 # (see mail on koha-devel, 2005/04/07)
1830 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
1832 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
1834 # Revision 1.113 2005/07/28 08:38:41 tipaul
1835 # 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 :
1836 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
1837 # * ReturnBeforeExpiry = no => return date can be after expiry date
1839 # Revision 1.112 2005/07/26 08:19:47 hdl
1840 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
1842 # Revision 1.111 2005/07/25 15:35:38 tipaul
1843 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
1844 # So, the updatedatabase script can highly be cleaned (90% removed).
1845 # Let's play with the new Koha DB structure now ;-)