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 # tablename => { 'field' => 'fieldtype' },
180 my %dropable_table = (
181 sessionqueries => 'sessionqueries',
182 marcrecorddone => 'marcrecorddone',
184 itemsprices => 'itemsprices',
185 biblioanalysis => 'biblioanalysis',
187 # tablename => 'tablename',
190 my %uselessfields = (
191 # tablename => "field1,field2",
192 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
194 # the other hash contains other actions that can't be done elsewhere. they are done
195 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
197 # The tabledata hash contains data that should be in the tables.
198 # The uniquefieldrequired hash entry is used to determine which (if any) fields
199 # must not exist in the table for this row to be inserted. If the
200 # uniquefieldrequired entry is already in the table, the existing data is not
201 # modified, unless the forceupdate hash entry is also set. Fields in the
202 # anonymous "forceupdate" hash will be forced to be updated to the default
203 # values given in the %tabledata hash.
207 # { uniquefielrequired => 'fieldname', # the primary key in the table
208 # fieldname => fieldvalue,
209 # fieldname2 => fieldvalue2,
212 systempreferences => [
214 uniquefieldrequired => 'variable',
215 variable => 'Activate_Log',
217 forceupdate => { 'explanation' => 1,
219 explanation => 'Turn Log Actions on DB On an Off',
223 uniquefieldrequired => 'variable',
224 variable => 'IndependantBranches',
226 forceupdate => { 'explanation' => 1,
228 explanation => 'Turn Branch independancy management On an Off',
232 uniquefieldrequired => 'variable',
233 variable => 'ReturnBeforeExpiry',
235 forceupdate => { 'explanation' => 1,
237 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
241 uniquefieldrequired => 'variable',
242 variable => 'opacstylesheet',
244 forceupdate => { 'explanation' => 1,
246 explanation => 'Enter a complete URL to use an alternate stylesheet in OPAC',
250 uniquefieldrequired => 'variable',
251 variable => 'opacsmallimage',
253 forceupdate => { 'explanation' => 1,
255 explanation => 'Enter a complete URL to an image, will be on top/left instead of the Koha logo',
259 uniquefieldrequired => 'variable',
260 variable => 'opaclargeimage',
262 forceupdate => { 'explanation' => 1,
264 explanation => 'Enter a complete URL to an image, will be on the main page, instead of the Koha logo',
268 uniquefieldrequired => 'variable',
269 variable => 'delimiter',
271 forceupdate => { 'explanation' => 1,
273 explanation => 'separator for reports exported to spreadsheet',
277 uniquefieldrequired => 'variable',
279 value => 'OPENOFFICE.ORG',
280 forceupdate => { 'explanation' => 1,
283 explanation => 'Define the default application for report exportations into files',
285 options => 'EXCEL|OPENOFFICE.ORG'
288 uniquefieldrequired => 'variable',
289 variable => 'Delimiter',
291 forceupdate => { 'explanation' => 1,
294 explanation => 'Define the default separator character for report exportations into files',
296 options => ';|tabulation|,|/|\|#'
299 uniquefieldrequired => 'variable',
300 variable => 'SubscriptionHistory',
302 forceupdate => { 'explanation' => 1,
305 explanation => 'Define the information level for serials history in OPAC',
307 options => 'simplified|full'
310 uniquefieldrequired => 'variable',
311 variable => 'hidelostitems',
313 forceupdate => { 'explanation' => 1,
315 explanation => 'show or hide "lost" items in OPAC.',
319 uniquefieldrequired => 'variable',
320 variable => 'IndependantBranches',
322 forceupdate => { 'explanation' => 1,
324 explanation => 'Turn Branch independancy management On an Off',
328 uniquefieldrequired => 'variable',
329 variable => 'ReturnBeforeExpiry',
331 forceupdate => { 'explanation' => 1,
333 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
337 uniquefieldrequired => 'variable',
338 variable => 'Disable_Dictionary',
340 forceupdate => { 'explanation' => 1,
342 explanation => 'Disables Dictionary buttons if set to yes',
346 uniquefieldrequired => 'variable',
347 variable => 'hide_marc',
349 forceupdate => { 'explanation' => 1,
351 explanation => 'hide marc specific datas like subfield code & indicators to library',
355 uniquefieldrequired => 'variable',
356 variable => 'NotifyBorrowerDeparture',
358 forceupdate => { 'explanation' => 1,
360 explanation => 'Delay before expiry where a notice is sent when issuing',
364 uniquefieldrequired => 'variable',
365 variable => 'OpacPasswordChange',
367 forceupdate => { 'explanation' => 1,
369 explanation => 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
373 uniquefieldrequired => 'variable',
374 variable => 'useDaysMode',
376 forceupdate => { 'explanation' => 1,
378 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
380 options => 'Calendar|Days'
383 uniquefieldrequired => 'variable',
384 variable => 'borrowerMandatoryField',
385 value => 'zipcode|surname',
386 forceupdate => { 'explanation' => 1,
388 explanation => 'List all mandatory fields for borrowers',
392 uniquefieldrequired => 'variable',
393 variable => 'borrowerRelationship',
394 value => 'father|mother,grand-mother',
395 forceupdate => { 'explanation' => 1,
397 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
401 uniquefieldrequired => 'variable',
402 variable => 'ReservesMaxPickUpDelay',
404 forceupdate => { 'explanation' => 1,
406 explanation => 'Maximum delay to pick up a reserved document',
410 uniquefieldrequired => 'variable',
411 variable => 'TransfersMaxDaysWarning',
413 forceupdate => { 'explanation' => 1,
415 explanation => 'Max delay before considering the transfer has potentialy a problem',
422 my %fielddefinitions = (
424 # { field => 'fieldname',
425 # type => 'fieldtype',
443 field => 'booksellerid',
453 field => 'listprice',
454 type => 'varchar(10)',
461 field => 'invoiceprice',
462 type => 'varchar(10)',
471 field => 'borrowernumber',
473 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
479 field => 'itemnumber',
481 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
488 { field => 'B_email',
491 after => 'B_zipcode',
494 field => 'streetnumber', # street number (hidden if streettable table is empty)
500 field => 'streettype', # street table, list builded from a system table
503 after => 'streetnumber',
506 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
512 field => 'B_streettype', # street table, list builded from a system table
515 after => 'B_streetnumber',
524 field => 'address2', # complement address
536 field => 'contactfirstname', # contact's firstname
539 after => 'contactname',
542 field => 'contacttitle', # contact's title
545 after => 'contactfirstname',
552 type => 'varchar(15)',
559 field => 'branchprinter',
560 type => 'varchar(100)',
569 field => 'category_type',
579 field => 'waitingdate',
591 # { indexname => 'index detail'
595 { indexname => 'shelfnumber',
596 content => 'shelfnumber',
598 { indexname => 'itemnumber',
599 content => 'itemnumber',
603 { indexname => 'biblionumber',
604 content => 'biblionumber',
608 { indexname => 'homebranch',
609 content => 'homebranch',
611 { indexname => 'holdingbranch',
612 content => 'holdingbranch',
616 { indexname => 'PRIMARY',
622 { indexname => 'booksellerid',
623 content => 'booksellerid',
627 { indexname => 'basketno',
628 content => 'basketno',
631 aqorderbreakdown => [
632 { indexname => 'ordernumber',
633 content => 'ordernumber',
635 { indexname => 'bookfundid',
636 content => 'bookfundid',
640 { indexname => 'PRIMARY',
641 content => 'currency',
649 # { key => 'the key in table' (must be indexed)
650 # foreigntable => 'the foreigntable name', # (the parent)
651 # foreignkey => 'the foreign key column(s)' # (in the parent)
652 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
653 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
657 { key => 'shelfnumber',
658 foreigntable => 'bookshelf',
659 foreignkey => 'shelfnumber',
660 onUpdate => 'CASCADE',
661 onDelete => 'CASCADE',
663 { key => 'itemnumber',
664 foreigntable => 'items',
665 foreignkey => 'itemnumber',
666 onUpdate => 'CASCADE',
667 onDelete => 'CASCADE',
670 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
671 # easily deleted, but branches/itemtype not too easy to empty...
673 { key => 'biblionumber',
674 foreigntable => 'biblio',
675 foreignkey => 'biblionumber',
676 onUpdate => 'CASCADE',
677 onDelete => 'CASCADE',
680 foreigntable => 'itemtypes',
681 foreignkey => 'itemtype',
682 onUpdate => 'CASCADE',
683 onDelete => 'RESTRICT',
687 { key => 'biblioitemnumber',
688 foreigntable => 'biblioitems',
689 foreignkey => 'biblioitemnumber',
690 onUpdate => 'CASCADE',
691 onDelete => 'CASCADE',
693 { key => 'homebranch',
694 foreigntable => 'branches',
695 foreignkey => 'branchcode',
696 onUpdate => 'CASCADE',
697 onDelete => 'RESTRICT',
699 { key => 'holdingbranch',
700 foreigntable => 'branches',
701 foreignkey => 'branchcode',
702 onUpdate => 'CASCADE',
703 onDelete => 'RESTRICT',
706 additionalauthors => [
707 { key => 'biblionumber',
708 foreigntable => 'biblio',
709 foreignkey => 'biblionumber',
710 onUpdate => 'CASCADE',
711 onDelete => 'CASCADE',
715 { key => 'biblionumber',
716 foreigntable => 'biblio',
717 foreignkey => 'biblionumber',
718 onUpdate => 'CASCADE',
719 onDelete => 'CASCADE',
723 { key => 'booksellerid',
724 foreigntable => 'aqbooksellers',
726 onUpdate => 'CASCADE',
727 onDelete => 'RESTRICT',
732 foreigntable => 'aqbasket',
733 foreignkey => 'basketno',
734 onUpdate => 'CASCADE',
735 onDelete => 'CASCADE',
737 { key => 'biblionumber',
738 foreigntable => 'biblio',
739 foreignkey => 'biblionumber',
740 onUpdate => 'SET NULL',
741 onDelete => 'SET NULL',
745 { key => 'listprice',
746 foreigntable => 'currency',
747 foreignkey => 'currency',
748 onUpdate => 'CASCADE',
749 onDelete => 'CASCADE',
751 { key => 'invoiceprice',
752 foreigntable => 'currency',
753 foreignkey => 'currency',
754 onUpdate => 'CASCADE',
755 onDelete => 'CASCADE',
758 aqorderbreakdown => [
759 { key => 'ordernumber',
760 foreigntable => 'aqorders',
761 foreignkey => 'ordernumber',
762 onUpdate => 'CASCADE',
763 onDelete => 'CASCADE',
765 { key => 'bookfundid',
766 foreigntable => 'aqbookfund',
767 foreignkey => 'bookfundid',
768 onUpdate => 'CASCADE',
769 onDelete => 'CASCADE',
773 { key => 'frombranch',
774 foreigntable => 'branches',
775 foreignkey => 'branchcode',
776 onUpdate => 'CASCADE',
777 onDelete => 'CASCADE',
780 foreigntable => 'branches',
781 foreignkey => 'branchcode',
782 onUpdate => 'CASCADE',
783 onDelete => 'CASCADE',
785 { key => 'itemnumber',
786 foreigntable => 'items',
787 foreignkey => 'itemnumber',
788 onUpdate => 'CASCADE',
789 onDelete => 'CASCADE',
793 { key => 'categorycode',
794 foreigntable => 'categories',
795 foreignkey => 'categorycode',
796 onUpdate => 'CASCADE',
797 onDelete => 'CASCADE',
800 foreigntable => 'itemtypes',
801 foreignkey => 'itemtype',
802 onUpdate => 'CASCADE',
803 onDelete => 'CASCADE',
806 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
808 { key => 'borrowernumber',
809 foreigntable => 'borrowers',
810 foreignkey => 'borrowernumber',
811 onUpdate => 'SET NULL',
812 onDelete => 'SET NULL',
814 { key => 'itemnumber',
815 foreigntable => 'items',
816 foreignkey => 'itemnumber',
817 onUpdate => 'SET NULL',
818 onDelete => 'SET NULL',
822 { key => 'borrowernumber',
823 foreigntable => 'borrowers',
824 foreignkey => 'borrowernumber',
825 onUpdate => 'CASCADE',
826 onDelete => 'CASCADE',
828 { key => 'biblionumber',
829 foreigntable => 'biblio',
830 foreignkey => 'biblionumber',
831 onUpdate => 'CASCADE',
832 onDelete => 'CASCADE',
834 { key => 'itemnumber',
835 foreigntable => 'items',
836 foreignkey => 'itemnumber',
837 onUpdate => 'CASCADE',
838 onDelete => 'CASCADE',
840 { key => 'branchcode',
841 foreigntable => 'branches',
842 foreignkey => 'branchcode',
843 onUpdate => 'CASCADE',
844 onDelete => 'CASCADE',
847 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
848 # but prevent deleting a branch as soon as it has 1 borrower !
849 { key => 'categorycode',
850 foreigntable => 'categories',
851 foreignkey => 'categorycode',
852 onUpdate => 'RESTRICT',
853 onDelete => 'RESTRICT',
855 { key => 'branchcode',
856 foreigntable => 'branches',
857 foreignkey => 'branchcode',
858 onUpdate => 'RESTRICT',
859 onDelete => 'RESTRICT',
863 { key => 'borrowernumber',
864 foreigntable => 'borrowers',
865 foreignkey => 'borrowernumber',
866 onUpdate => 'CASCADE',
867 onDelete => 'CASCADE',
869 { key => 'itemnumber',
870 foreigntable => 'items',
871 foreignkey => 'itemnumber',
872 onUpdate => 'SET NULL',
873 onDelete => 'SET NULL',
876 auth_tag_structure => [
877 { key => 'authtypecode',
878 foreigntable => 'auth_types',
879 foreignkey => 'authtypecode',
880 onUpdate => 'CASCADE',
881 onDelete => 'CASCADE',
884 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
889 my %column_change = (
893 from => 'emailaddress',
898 from => 'streetaddress',
908 from => 'textmessaging',
918 from => 'physstreet',
923 from => 'streetcity',
925 after => 'B_address',
938 from => 'homezipcode',
945 after => 'B_zipcode',
950 after => 'dateenrolled',
955 after => 'contactname',
958 from => 'textmessaging',
964 to => 'contactnotes',
965 after => 'opacnotes',
968 from => 'altrelationship',
969 to => 'relationship',
970 after => 'borrowernotes',
975 foreach my $table (keys %column_change) {
976 $sth = $dbh->prepare("show columns from $table");
979 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
981 $types{$column}->{type} ="$type";
982 $types{$column}->{null} = "$null";
983 $types{$column}->{key} = "$key";
984 $types{$column}->{default} = "$default";
985 $types{$column}->{extra} = "$extra";
987 my $tablerows = $column_change{$table};
988 foreach my $row ( @$tablerows ) {
989 if ($types{$row->{from}}->{type}) {
990 print "altering $table $row->{from} to $row->{to}\n";
991 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
992 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
994 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
995 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
996 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
997 "$types{$row->{from}}->{extra} after $row->{after} ";
1004 #-------------------
1009 # Get version of MySQL database engine.
1010 my $mysqlversion = `mysqld --version`;
1011 $mysqlversion =~ /Ver (\S*) /;
1013 if ( $mysqlversion ge '3.23' ) {
1014 print "Could convert to MyISAM database tables...\n" unless $silent;
1017 #---------------------------------
1020 # Collect all tables into a list
1021 $sth = $dbh->prepare("show tables");
1023 while ( my ($table) = $sth->fetchrow ) {
1024 $existingtables{$table} = 1;
1028 # Now add any missing tables
1029 foreach $table ( keys %requiretables ) {
1030 unless ( $existingtables{$table} ) {
1031 print "Adding $table table...\n" unless $silent;
1032 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1035 print "Error : $sth->errstr \n";
1041 # now drop useless tables
1042 foreach $table ( keys %dropable_table ) {
1043 if ( $existingtables{$table} ) {
1044 print "Dropping unused table $table\n" if $debug and not $silent;
1045 $dbh->do("drop table $table");
1047 print "Error : $dbh->errstr \n";
1052 #---------------------------------
1055 foreach $table ( keys %requirefields ) {
1056 print "Check table $table\n" if $debug and not $silent;
1057 $sth = $dbh->prepare("show columns from $table");
1060 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1062 $types{$column} = $type;
1064 foreach $column ( keys %{ $requirefields{$table} } ) {
1065 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1066 if ( !$types{$column} ) {
1068 # column doesn't exist
1069 print "Adding $column field to $table table...\n" unless $silent;
1070 $query = "alter table $table
1071 add column $column " . $requirefields{$table}->{$column};
1072 print "Execute: $query\n" if $debug;
1073 my $sti = $dbh->prepare($query);
1076 print "**Error : $sti->errstr \n";
1083 foreach $table ( keys %fielddefinitions ) {
1084 print "Check table $table\n" if $debug;
1085 $sth = $dbh->prepare("show columns from $table");
1088 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1090 $definitions->{$column}->{type} = $type;
1091 $definitions->{$column}->{null} = $null;
1092 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1093 $definitions->{$column}->{key} = $key;
1094 $definitions->{$column}->{default} = $default;
1095 $definitions->{$column}->{extra} = $extra;
1097 my $fieldrow = $fielddefinitions{$table};
1098 foreach my $row (@$fieldrow) {
1099 my $field = $row->{field};
1100 my $type = $row->{type};
1101 my $null = $row->{null};
1102 # $null = 'YES' if $row->{null} eq 'NULL';
1103 my $key = $row->{key};
1104 my $default = $row->{default};
1105 my $null = $row->{null};
1106 # $default="''" unless $default;
1107 my $extra = $row->{extra};
1108 my $def = $definitions->{$field};
1109 my $after = ($row->{after}?" after ".$row->{after}:"");
1111 unless ( $type eq $def->{type}
1112 && $null eq $def->{null}
1113 && $key eq $def->{key}
1114 && $extra eq $def->{extra} )
1116 if ( $null eq '' ) {
1119 if ( $key eq 'PRI' ) {
1120 $key = 'PRIMARY KEY';
1122 unless ( $extra eq 'auto_increment' ) {
1126 # if it's a new column use "add", if it's an old one, use "change".
1128 if ($definitions->{$field}->{type}) {
1129 $action="change $field"
1133 # if it's a primary key, drop the previous pk, before altering the table
1135 if ($key ne 'PRIMARY KEY') {
1136 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ? $after");
1138 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ? $after");
1140 $sth->execute($default);
1141 print " alter or create $field in $table\n" unless $silent;
1146 # Populate tables with required data
1149 # synch table and deletedtable.
1150 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1151 my %deletedborrowers;
1152 print "synch'ing $table\n";
1153 $sth = $dbh->prepare("show columns from deleted$table");
1155 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1156 $deletedborrowers{$column}=1;
1158 $sth = $dbh->prepare("show columns from $table");
1161 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1162 unless ($deletedborrowers{$column}) {
1163 my $newcol="alter table deleted$table add $column $type";
1164 if ($null eq 'YES') {
1165 $newcol .= " NULL ";
1167 $newcol .= " NOT NULL ";
1169 $newcol .= "default $default" if $default;
1170 $newcol .= " after $previous" if $previous;
1172 print "creating column $column\n";
1178 foreach my $table ( keys %tabledata ) {
1179 print "Checking for data required in table $table...\n" unless $silent;
1180 my $tablerows = $tabledata{$table};
1181 foreach my $row (@$tablerows) {
1182 my $uniquefieldrequired = $row->{uniquefieldrequired};
1183 my $uniquevalue = $row->{$uniquefieldrequired};
1184 my $forceupdate = $row->{forceupdate};
1187 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1189 $sth->execute($uniquevalue);
1191 foreach my $field (keys %$forceupdate) {
1192 if ($forceupdate->{$field}) {
1193 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1194 $sth->execute($row->{$field}, $uniquevalue);
1198 print "Adding row to $table: " unless $silent;
1202 foreach my $field ( keys %$row ) {
1203 next if $field eq 'uniquefieldrequired';
1204 next if $field eq 'forceupdate';
1205 my $value = $row->{$field};
1206 push @values, $value;
1207 print " $field => $value" unless $silent;
1208 $fieldlist .= "$field,";
1209 $placeholders .= "?,";
1211 print "\n" unless $silent;
1212 $fieldlist =~ s/,$//;
1213 $placeholders =~ s/,$//;
1216 "insert into $table ($fieldlist) values ($placeholders)");
1217 $sth->execute(@values);
1223 # check indexes and create them when needed
1225 print "Checking for index required...\n" unless $silent;
1226 foreach my $table ( keys %indexes ) {
1228 # read all indexes from $table
1230 $sth = $dbh->prepare("show index from $table");
1232 my %existingindexes;
1233 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
1234 $existingindexes{$key_name} = 1;
1236 # read indexes to check
1237 my $tablerows = $indexes{$table};
1238 foreach my $row (@$tablerows) {
1239 my $key_name=$row->{indexname};
1240 if ($existingindexes{$key_name} eq 1) {
1241 # print "$key_name existing";
1243 print "\tCreating index $key_name in $table\n";
1245 if ($row->{indexname} eq 'PRIMARY') {
1246 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
1248 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
1251 print "Error $sql : $dbh->err \n" if $dbh->err;
1257 # check foreign keys and create them when needed
1259 print "Checking for foreign keys required...\n" unless $silent;
1260 foreach my $table ( keys %foreign_keys ) {
1262 # read all indexes from $table
1264 $sth = $dbh->prepare("show table status like '$table'");
1266 my $stat = $sth->fetchrow_hashref;
1267 # read indexes to check
1268 my $tablerows = $foreign_keys{$table};
1269 foreach my $row (@$tablerows) {
1270 my $foreign_table=$row->{foreigntable};
1271 if ($stat->{'Comment'} =~/$foreign_table/) {
1272 # print "$foreign_table existing\n";
1274 print "\tCreating foreign key $foreign_table in $table\n";
1275 # first, drop any orphan value in child table
1276 if ($row->{onDelete} ne "RESTRICT") {
1277 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
1279 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
1281 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
1282 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
1283 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
1286 print "====================
1287 An error occured during :
1289 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).
1290 You can find those values with select
1291 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
1292 ====================\n
1303 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
1306 # 1st, get how many biblio we will have to do...
1307 $sth = $dbh->prepare('select count(*) from marc_biblio');
1309 my ($totaltodo) = $sth->fetchrow;
1311 $sth = $dbh->prepare("show columns from biblio");
1314 my $bibliofwexist=0;
1315 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1316 $bibliofwexist=1 if $column eq 'frameworkcode';
1318 unless ($bibliofwexist) {
1319 print "moving biblioframework to biblio table\n";
1320 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
1321 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
1323 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
1325 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
1326 $sth_update->execute($frameworkcode,$biblionumber);
1328 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1334 # moving MARC data from marc_subfield_table to biblioitems.marc
1336 $sth = $dbh->prepare("show columns from biblioitems");
1340 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
1341 $marcdone=1 if ($type eq 'blob' && $column eq 'marc') ;
1343 unless ($marcdone) {
1344 print "moving MARC record to biblioitems table\n";
1345 # changing marc field type
1346 $dbh->do('ALTER TABLE `biblioitems` CHANGE `marc` `marc` BLOB NULL DEFAULT NULL ');
1347 # adding marc xml, just for convenience
1348 $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ');
1349 # moving data from marc_subfield_value to biblio
1350 $sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
1352 my $sth_update = $dbh->prepare('update biblioitems set marc=?, marcxml=? where biblionumber=?');
1354 while (my ($bibid,$biblionumber) = $sth->fetchrow) {
1355 my $record = MARCgetbiblio($dbh,$bibid);
1356 #Force UTF-8 in record leader
1357 $record->encoding('UTF-8');
1358 print $record->as_formatted if ($biblionumber==3902);
1359 $sth_update->execute($record->as_usmarc(),$record->as_xml_record(),$biblionumber);
1361 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
1367 # at last, remove useless fields
1368 foreach $table ( keys %uselessfields ) {
1369 my @fields = split /,/,$uselessfields{$table};
1372 foreach my $fieldtodrop (@fields) {
1373 $fieldtodrop =~ s/\t//g;
1374 $fieldtodrop =~ s/\n//g;
1376 $sth = $dbh->prepare("show columns from $table");
1378 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1380 $exists =1 if ($column eq $fieldtodrop);
1383 print "deleting $fieldtodrop field in $table...\n" unless $silent;
1384 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
1391 # MOVE all tables TO UTF-8 and innoDB
1392 $sth = $dbh->prepare("show table status");
1394 while ( my $table = $sth->fetchrow_hashref ) {
1395 # if ($table->{Engine} ne 'InnoDB') {
1396 # $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1397 # print "moving $table->{Name} to InnoDB\n";
1399 unless ($table->{Collation} =~ /^utf8/) {
1400 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1401 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1402 # 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 !
1403 print "moving $table->{Name} to utf8\n";
1411 # those 2 subs are a copy of Biblio.pm, version 2.2.4
1412 # they are useful only once, for moving from 2.2 to 3.0
1413 # the MARCgetbiblio & MARCgetitem subs in Biblio.pm
1414 # are still here, but uses other tables
1415 # (the ones that are filled by updatedatabase !)
1420 # Returns MARC::Record of the biblio passed in parameter.
1421 my ( $dbh, $bibid ) = @_;
1422 my $record = MARC::Record->new();
1427 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1428 from marc_subfield_table
1429 where bibid=? order by tag,tagorder,subfieldorder
1434 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1435 $sth->execute($bibid);
1436 my $prevtagorder = 1;
1437 my $prevtag = 'XXX';
1439 my $field; # for >=10 tags
1440 my $prevvalue; # for <10 tags
1441 while ( my $row = $sth->fetchrow_hashref ) {
1443 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1444 $sth2->execute( $row->{'valuebloblink'} );
1445 my $row2 = $sth2->fetchrow_hashref;
1447 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1449 if ( $row->{tagorder} ne $prevtagorder || $row->{tag} ne $prevtag ) {
1450 $previndicator .= " ";
1451 if ( $prevtag < 10 ) {
1452 if ($prevtag ne '000') {
1453 $record->add_fields( ( sprintf "%03s", $prevtag ), $prevvalue ) unless $prevtag eq "XXX"; # ignore the 1st loop
1455 $record->leader(sprintf("%24s",$prevvalue));
1459 $record->add_fields($field) unless $prevtag eq "XXX";
1462 $prevtagorder = $row->{tagorder};
1463 $prevtag = $row->{tag};
1464 $previndicator = $row->{tag_indicator};
1465 if ( $row->{tag} < 10 ) {
1466 $prevvalue = $row->{subfieldvalue};
1469 $field = MARC::Field->new(
1470 ( sprintf "%03s", $prevtag ),
1471 substr( $row->{tag_indicator} . ' ', 0, 1 ),
1472 substr( $row->{tag_indicator} . ' ', 1, 1 ),
1473 $row->{'subfieldcode'},
1474 $row->{'subfieldvalue'}
1479 if ( $row->{tag} < 10 ) {
1480 $record->add_fields( ( sprintf "%03s", $row->{tag} ),
1481 $row->{'subfieldvalue'} );
1484 $field->add_subfields( $row->{'subfieldcode'},
1485 $row->{'subfieldvalue'} );
1487 $prevtag = $row->{tag};
1488 $previndicator = $row->{tag_indicator};
1492 # the last has not been included inside the loop... do it now !
1493 if ( $prevtag ne "XXX" )
1494 { # check that we have found something. Otherwise, prevtag is still XXX and we
1495 # must return an empty record, not make MARC::Record fail because we try to
1496 # create a record with XXX as field :-(
1497 if ( $prevtag < 10 ) {
1498 $record->add_fields( $prevtag, $prevvalue );
1502 # my $field = MARC::Field->new( $prevtag, "", "", %subfieldlist);
1503 $record->add_fields($field);
1511 # Returns MARC::Record of the biblio passed in parameter.
1512 my ( $dbh, $bibid, $itemnumber ) = @_;
1513 my $record = MARC::Record->new();
1515 # search MARC tagorder
1518 "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=?"
1520 $sth2->execute( $bibid, $itemnumber );
1521 my ($tagorder) = $sth2->fetchrow_array();
1523 #---- TODO : the leader is missing
1526 "select bibid,subfieldid,tag,tagorder,tag_indicator,subfieldcode,subfieldorder,subfieldvalue,valuebloblink
1527 from marc_subfield_table
1528 where bibid=? and tagorder=? order by subfieldcode,subfieldorder
1533 "select subfieldvalue from marc_blob_subfield where blobidlink=?");
1534 $sth->execute( $bibid, $tagorder );
1535 while ( my $row = $sth->fetchrow_hashref ) {
1536 if ( $row->{'valuebloblink'} ) { #---- search blob if there is one
1537 $sth2->execute( $row->{'valuebloblink'} );
1538 my $row2 = $sth2->fetchrow_hashref;
1540 $row->{'subfieldvalue'} = $row2->{'subfieldvalue'};
1542 if ( $record->field( $row->{'tag'} ) ) {
1545 #--- this test must stay as this, because of strange behaviour of mySQL/Perl DBI with char var containing a number...
1546 #--- sometimes, eliminates 0 at beginning, sometimes no ;-\\\
1547 if ( length( $row->{'tag'} ) < 3 ) {
1548 $row->{'tag'} = "0" . $row->{'tag'};
1550 $field = $record->field( $row->{'tag'} );
1553 $field->add_subfields( $row->{'subfieldcode'},
1554 $row->{'subfieldvalue'} );
1555 $record->delete_field($field);
1556 $record->add_fields($field);
1560 if ( length( $row->{'tag'} ) < 3 ) {
1561 $row->{'tag'} = "0" . $row->{'tag'};
1564 MARC::Field->new( $row->{'tag'}, " ", " ",
1565 $row->{'subfieldcode'} => $row->{'subfieldvalue'} );
1566 $record->add_fields($temp);
1577 # Revision 1.138 2006/05/19 16:51:44 alaurin
1578 # update database for :
1579 # - new feature ip and printer management
1580 # adding two fields in branches table (branchip,branchprinter)
1582 # - 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
1584 # new system preference :
1585 # - ReservesMaxPickUpDelay : Maximum delay to pick up a reserved document
1586 # TransfersMaxDaysWarning : Max delay before considering the transfer as potentialy a problem
1588 # Revision 1.137 2006/04/18 09:36:36 plg
1589 # bug fixed: typo fixed in labels and labels_conf tables creation query.
1591 # Revision 1.136 2006/04/17 21:55:33 sushi
1592 # Added 'labels' and 'labels_conf' tables, for spine lable tool.
1594 # Revision 1.135 2006/04/15 02:37:03 tgarip1957
1595 # Marc record should be set to UTF-8 in leader.Force it.
1596 # XML should be with<record> wrappers
1598 # Revision 1.134 2006/04/14 09:37:29 tipaul
1599 # improvements from SAN Ouest Provence :
1600 # * introducing a category_type into categories. It can be A (adult), C (children), P (Professionnal), I (institution/organisation).
1601 # * each category_type has it's own forms to create members.
1602 # * the borrowers table has been heavily modified (many fields changed), to get something more logic & readable
1603 # * reintroducing guarantor/guanrantee system that is now independant from hardcoded C/A for categories
1604 # * updating templates to fit template rules
1606 # (see mail feb, 17 on koha-devel "new features for borrowers" for more details)
1608 # Revision 1.133 2006/04/13 08:36:42 plg
1609 # new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on
1610 # the system preference prefered date format.
1612 # improvement: book fund list and budget list screen redesigned. Filters on
1613 # each field. Columns are not sortable yet. Using DHTML Calendar to fill date
1614 # fields instead of manual filling. Pagination system. From the book fund
1615 # list, you can reach the budget list, filtered on a book fund, or not. A
1616 # budget can be added only from book fund list screen.
1618 # bug fixed: branchcode was missing in table aqbudget.
1620 # bug fixed: when setting a branchcode to a book fund, all associated budgets
1621 # move to this branchcode.
1623 # modification: when adding/modifying budget/fund, MySQL specific "REPLACE..."
1624 # statements replaced by standard SQL compliant statement.
1626 # bug fixed: when adding/modifying a budget, if the book fund is associated to
1627 # a branch, the branch selection is disabled and set to the book fund branch.
1629 # Revision 1.132 2006/04/06 12:37:05 hdl
1630 # Bugfixing : aqbookfund needed a field.
1632 # Revision 1.131 2006/03/03 17:02:22 tipaul
1633 # commit for holidays and news management.
1634 # (some forgotten files)
1636 # Revision 1.130 2006/03/03 16:35:21 tipaul
1637 # commit for holidays and news management.
1639 # Contrib from Tümer Garip (from Turkey) :
1641 # 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 :
1642 # - single day : only this day is closed
1643 # - repet weekly (like "sunday") : the day is holiday every week
1644 # - repet yearly (like "July, 4") : this day is closed every year.
1646 # You can also put exception :
1647 # - sunday is holiday, but "2006 March, 5th" the library will be open
1649 # 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.
1651 # Revision 1.129 2006/02/27 18:19:33 hdl
1652 # New table used in overduerules.pl tools page.
1654 # Revision 1.128 2006/01/25 15:16:06 tipaul
1656 # * removing useless tables
1657 # * adding useful indexes
1658 # * altering some columns definitions
1659 # * The goal being to have updater working fine for foreign keys.
1661 # 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
1663 # Revision 1.127 2006/01/24 17:57:17 tipaul
1664 # DB improvements : adding foreign keys on some tables. partial stuff done.
1666 # Revision 1.126 2006/01/06 16:39:42 tipaul
1667 # synch'ing head and rel_2_2 (from 2.2.5, including npl templates)
1668 # Seems not to break too many things, but i'm probably wrong here.
1669 # at least, new features/bugfixes from 2.2.5 are here (tested on some features on my head local copy)
1671 # - removing useless directories (koha-html and koha-plucene)
1673 # Revision 1.125 2006/01/04 15:54:55 tipaul
1674 # utf8 is a : go for beta test in HEAD.
1675 # some explanations :
1676 # - 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.
1677 # - *-top.inc will show the pages in utf8
1678 # - 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.
1679 # - using marcxml field and no more the iso2709 raw marc biblioitems.marc field.
1681 # Revision 1.124 2005/10/27 12:09:05 tipaul
1682 # new features for serial module :
1683 # - 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")
1684 # - 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).
1686 # Revision 1.123 2005/10/26 09:13:37 tipaul
1687 # big commit, still breaking things...
1689 # * synch with rel_2_2. Probably the last non manual synch, as rel_2_2 should not be modified deeply.
1690 # * code cleaning (cleaning warnings from perl -w) continued
1692 # Revision 1.122 2005/09/02 14:18:38 tipaul
1693 # new feature : image for itemtypes.
1695 # * run updater/updatedatabase to create imageurl field in itemtypes.
1696 # * 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)
1697 # * go to OPAC, and search something. In the result list, you now have the picture instead of the text itemtype.
1699 # Revision 1.121 2005/08/24 08:49:03 hdl
1700 # Adding a note field in serial table.
1701 # This will allow librarian to mention a note on a peculiar waiting serial number.
1703 # Revision 1.120 2005/08/09 14:10:32 tipaul
1704 # 1st commit to go to zebra.
1705 # don't update your cvs if you want to have a working head...
1707 # this commit contains :
1708 # * 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...
1709 # * 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.
1710 # * other files : get rid of bibid and use biblionumber instead.
1713 # * does not do anything on zebra yet.
1714 # * if you rename marc_subfield_table, you can't search anymore.
1715 # * you can view a biblio & bibliodetails, go to MARC editor, but NOT save any modif.
1716 # * 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 ;-) )
1718 # 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
1719 # 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.
1721 # Revision 1.119 2005/08/04 16:07:58 tipaul
1722 # Synch really broke this script...
1724 # Revision 1.118 2005/08/04 16:02:55 tipaul
1725 # oops... error in synch between 2.2 and head
1727 # Revision 1.117 2005/08/04 14:24:39 tipaul
1728 # synch'ing 2.2 and head
1730 # Revision 1.116 2005/08/04 08:55:54 tipaul
1731 # Letters / alert system, continuing...
1733 # * adding a package Letters.pm, that manages Letters & alerts.
1734 # * 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)
1735 # * 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)
1736 # * 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.
1738 # Note that the system should be generic enough to manage any type of alert.
1739 # 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 ;-) )
1741 # Revision 1.115 2005/08/02 16:15:34 tipaul
1742 # adding 2 fields to letter system :
1743 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
1744 # * title, that will be used as mail subject.
1746 # Revision 1.114 2005/07/28 15:10:13 tipaul
1747 # 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.
1748 # the letter table contains 3 fields :
1749 # * code => the code of the letter
1750 # * name => the complete name of the letter
1751 # * content => the complete text. It's a TEXT field type, so has no limits.
1753 # My next goal now is to work on point 2-I "serial issue alert"
1754 # 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.
1755 # (see mail on koha-devel, 2005/04/07)
1757 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
1759 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
1761 # Revision 1.113 2005/07/28 08:38:41 tipaul
1762 # 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 :
1763 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
1764 # * ReturnBeforeExpiry = no => return date can be after expiry date
1766 # Revision 1.112 2005/07/26 08:19:47 hdl
1767 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
1769 # Revision 1.111 2005/07/25 15:35:38 tipaul
1770 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
1771 # So, the updatedatabase script can highly be cleaned (90% removed).
1772 # Let's play with the new Koha DB structure now ;-)