use MARC::Record;
use MARC::File::XML ( BinaryEncoding => 'utf8' );
+use File::Path qw[remove_tree]; # perl core module
+use File::Spec;
+
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.
} else {
print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: OK)\n";
}
- SetVersion($DBversion);
+ SetVersion ($DBversion);
}
$DBversion = "3.19.00.008";
ALTER TABLE search_history ADD COLUMN id INT(11) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id);
|);
print "Upgrade to $DBversion done (Bug 11430: Add primary key for search_history)\n";
- SetVersion($DBversion);
+ SetVersion ($DBversion);
}
$DBversion = "3.19.00.016";
|);
print "Upgrade to $DBversion done (Bug 8007: Add System Preferences useDischarge, the discharge notice and the new table discharges)\n";
- SetVersion($DBversion);
+ SetVersion ($DBversion);
}
$DBversion = "3.19.00.036";
print "Upgrade to $DBversion done (Bug 11569 - Typo in userpermissions.sql)\n";
SetVersion($DBversion);
}
+$DBversion = "3.23.00.006";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do("
+ ALTER TABLE serial
+ ADD serialseq_x VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq,
+ ADD serialseq_y VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x,
+ ADD serialseq_z VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y
+ ");
+
+ my $schema = Koha::Database->new()->schema();
+ my @subscriptions = $schema->resultset('Subscription')->all();
+
+ foreach my $subscription (@subscriptions) {
+ my $number_pattern = $subscription->numberpattern();
+
+ my $numbering_method = $number_pattern->numberingmethod();
+ # Get all the data between the enumeration values, we need
+ # to split each enumeration string based on these values.
+ my @splits = split( /\{[XYZ]\}/, $numbering_method );
+ # Get the order in which the X Y and Z values are used
+ my %indexes;
+ foreach my $i (qw(X Y Z)) {
+ $indexes{$i} = index( $numbering_method, "{$i}" );
+ delete $indexes{$i} if $indexes{$i} == -1;
+ }
+ my @indexes = sort { $indexes{$a} <=> $indexes{$b} } keys(%indexes);
+
+ my @serials =
+ $schema->resultset('Serial')
+ ->search( { subscriptionid => $subscription->subscriptionid() } );
+
+ foreach my $serial (@serials) {
+ my $serialseq = $serial->serialseq();
+ my %enumeration_data;
+
+ ## We cannot split on multiple values at once,
+ ## so let's replace each of those values with __SPLIT__
+ if (@splits) {
+ map( $serialseq =~ s/$_/__SPLIT__/, @splits );
+ (
+ undef,
+ $enumeration_data{ $indexes[0] // q{} },
+ $enumeration_data{ $indexes[1] // q{} },
+ $enumeration_data{ $indexes[2] // q{} }
+ ) = split( /__SPLIT__/, $serialseq );
+ }
+ else
+ { ## Nothing to split on means the only thing in serialseq is a single placeholder e.g. {X}
+ $enumeration_data{ $indexes[0] } = $serialseq;
+ }
+
+ $serial->update(
+ {
+ serialseq_x => $enumeration_data{'X'},
+ serialseq_y => $enumeration_data{'Y'},
+ serialseq_z => $enumeration_data{'Z'},
+ }
+ );
+ }
+ }
+
+ print "Upgrade to $DBversion done ( Bug 8956 - Split serials enumeration data into separate fields )\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.007";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do("SET FOREIGN_KEY_CHECKS=0");
+ $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
+ $dbh->do("CREATE TABLE overduerules (
+ `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
+ `branchcode` varchar(10) NOT NULL DEFAULT '',
+ `categorycode` varchar(10) NOT NULL DEFAULT '',
+ `delay1` int(4) DEFAULT NULL,
+ `letter1` varchar(20) DEFAULT NULL,
+ `debarred1` varchar(1) DEFAULT '0',
+ `delay2` int(4) DEFAULT NULL,
+ `debarred2` varchar(1) DEFAULT '0',
+ `letter2` varchar(20) DEFAULT NULL,
+ `delay3` int(4) DEFAULT NULL,
+ `letter3` varchar(20) DEFAULT NULL,
+ `debarred3` int(1) DEFAULT '0',
+ PRIMARY KEY (`overduerules_id`),
+ UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
+ $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
+ $dbh->do("DROP TABLE old_overduerules");
+ $dbh->do("ALTER TABLE overduerules_transport_types
+ ADD COLUMN overduerules_id int(11) NOT NULL");
+ my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} });
+ $dbh->do("DELETE FROM overduerules_transport_types");
+ $dbh->do("ALTER TABLE overduerules_transport_types
+ DROP FOREIGN KEY overduerules_fk,
+ ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ DROP COLUMN branchcode,
+ DROP COLUMN categorycode");
+ my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
+ ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
+ foreach my $mtt(@$mtts){
+ $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
+ }
+ $dbh->do("SET FOREIGN_KEY_CHECKS=1");
+
+ print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.008";
+if ( CheckVersion($DBversion) ) {
+
+ $dbh->do(q{ALTER TABLE borrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0' AFTER privacy});
+
+ $dbh->do(q{ALTER TABLE deletedborrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0' AFTER privacy});
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type )
+ VALUES (
+ 'AllowStaffToSetCheckoutsVisibilityForGuarantor', '0', NULL,
+ 'If enabled, library staff can set a patron''s checkouts to be visible to linked patrons from the opac.', 'YesNo'
+ ), (
+ 'AllowPatronToSetCheckoutsVisibilityForGuarantor', '0', NULL,
+ 'If enabled, the patron can set checkouts to be visible to his or her guarantor', 'YesNo'
+ )
+ });
+
+ print "Upgrade to $DBversion done (Bug 9303 - relative's checkouts in the opac)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.009";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type ) VALUES
+ ( 'EnablePayPalOpacPayments', '0', NULL , 'Enables the ability to pay fees and fines from the OPAC via PayPal', 'YesNo' ),
+ ( 'PayPalChargeDescription', 'Koha fee payment', NULL , 'This preference defines what the user will see the charge listed as in PayPal', 'Free' ),
+ ( 'PayPalPwd', '', NULL , 'Your PayPal API password', 'Free' ),
+ ( 'PayPalSandboxMode', '1', NULL , 'If enabled, the system will use PayPal''s sandbox server for testing, rather than the production server.', 'YesNo' ),
+ ( 'PayPalSignature', '', NULL , 'Your PayPal API signature', 'Free' ),
+ ( 'PayPalUser', '', NULL , 'Your PayPal API username ( email address )', 'Free' )
+ });
+
+ print "Upgrade to $DBversion done (Bug 11622 - Add ability to pay fees and fines from OPAC via PayPal)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.010";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE issuingrules ADD cap_fine_to_replacement_price BOOLEAN NOT NULL DEFAULT '0' AFTER overduefinescap
+ });
+
+ print "Upgrade to $DBversion done (Bug 9129 - Add the ability to set the maximum fine for an item to its replacement price)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.011";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES ('HoldFeeMode','not_always','always|not_always','Set the hold fee mode','Choice')
+ });
+
+ print "Upgrade to $DBversion done (Bug 13592 - Hold fee not being applied on placing a hold)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.012";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `explanation`, `options`, `type` ) VALUES('MaxSearchResultsItemsPerRecordStatusCheck','20','Max number of items per record for which to check transit and hold status','','Integer')
+ });
+
+ print "Upgrade to $DBversion done (Bug 15380 - Move the authority types related code to Koha::Authority::Type[s] - part 1)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.013";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES ('StoreLastBorrower','0','','If ON, the last borrower to return an item will be stored in items.last_returned_by','YesNo')
+ });
+ $dbh->do(q{
+ CREATE TABLE IF NOT EXISTS `items_last_borrower` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `itemnumber` int(11) NOT NULL,
+ `borrowernumber` int(11) NOT NULL,
+ `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `itemnumber` (`itemnumber`),
+ KEY `borrowernumber` (`borrowernumber`),
+ CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+ });
+
+ print "Upgrade to $DBversion done (Bug 14945 - Add the ability to store the last patron to return an item)\n";
+ SetVersion($DBversion);
+
+}
+
+$DBversion = "3.23.00.014";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` )
+VALUES ('ClaimsBccCopy','0','','Bcc the ClaimAcquisition and ClaimIssues alerts','YesNo')
+ });
+
+ print "Upgrade to $DBversion done (Bug 10076 - Add Bcc syspref for claimacquisition and clamissues)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.015";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE letter SET code = "HOLD_SLIP" WHERE code = "RESERVESLIP";
+ });
+
+ print "Upgrade to $DBversion done (Bug 15443 - Re-code RESERVESLIP as HOLD_SLIP)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.016";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('OpacResetPassword', '0','','Shows the ''Forgot your password?'' link in the OPAC','YesNo');
+});
+ $dbh->do(q{
+ CREATE TABLE IF NOT EXISTS borrower_password_recovery (
+ borrowernumber int(11) NOT NULL,
+ uuid varchar(128) NOT NULL,
+ valid_until timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (borrowernumber),
+ KEY borrowernumber (borrowernumber)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+});
+ $dbh->do(q{
+ INSERT IGNORE INTO `letter` (module, code, branchcode, name, is_html, title, content, message_transport_type)
+ VALUES ('members','PASSWORD_RESET','','Online password reset',1,'Koha password recovery','<html>\r\n<p>This email has been sent in response to your password recovery request for the account <strong><<user>></strong>.\r\n</p>\r\n<p>\r\nYou can now create your new password using the following link:\r\n<br/><a href=\"<<passwordreseturl>>\"><<passwordreseturl>></a>\r\n</p>\r\n<p>This link will be valid for 2 days from this email\'s reception, then you must reapply if you do not change your password.</p>\r\n<p>Thank you.</p>\r\n</html>\r\n','email');
+
+ });
+
+ print "Upgrade to $DBversion done (Bug 8753 - Add forgot password link to OPAC)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.017";
+if ( CheckVersion($DBversion) ) {
+
+$dbh->do(q{
+ DELETE FROM uploaded_files
+ WHERE COALESCE(permanent,0)=0 AND dir='koha_upload'
+});
+
+my $tmp= File::Spec->tmpdir.'/koha_upload';
+remove_tree( $tmp ) if -d $tmp;
+
+ print "Upgrade to $DBversion done (Bug 14893 - Separate temporary storage per instance in Upload.pm)\n";
+ SetVersion($DBversion);
+
+}
+
+$DBversion = "3.23.00.018";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET value="0" where type="YesNo" and value="";
+ });
+
+ print "Upgrade to $DBversion done (Bug 15446 - Fix systempreferences rows where type=YesNo and value='')\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.019";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE `authorised_values` SET `lib`='Non-fiction' WHERE `lib`='Non Fiction';
+ });
+
+ print "Upgrade to $DBversion done (Bug 15411 - Change Non Fiction to Non-fiction in authorised_values)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.020";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ CREATE TABLE sms_providers (
+ id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
+ name VARCHAR( 255 ) NOT NULL ,
+ domain VARCHAR( 255 ) NOT NULL ,
+ UNIQUE (
+ name
+ )
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ });
+
+ $dbh->do(q{
+ ALTER TABLE borrowers ADD sms_provider_id INT( 11 ) NULL DEFAULT NULL AFTER smsalertnumber;
+ });
+ $dbh->do(q{
+ ALTER TABLE borrowers ADD FOREIGN KEY ( sms_provider_id ) REFERENCES sms_providers ( id ) ON UPDATE CASCADE ON DELETE SET NULL;
+ });
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers ADD sms_provider_id INT( 11 ) NULL DEFAULT NULL AFTER smsalertnumber;
+ });
+
+ print "Upgrade to $DBversion done (Bug 9021 - Add SMS via email as an alternative to SMS services via SMS::Send drivers)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.021";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES ('ShowAllCheckins', '0', '', 'Show all checkins', 'YesNo');
+ });
+
+ print "Upgrade to $DBversion done (Bug 15736 - Add a preference to control whether all items should be shown in checked-in items list)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.022";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{ ALTER TABLE tags_all MODIFY COLUMN borrowernumber INT(11) });
+ $dbh->do(q{ ALTER TABLE tags_all drop FOREIGN KEY tags_borrowers_fk_1 });
+ $dbh->do(q{ ALTER TABLE tags_all ADD CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE });
+ $dbh->do(q{ ALTER TABLE tags_approval DROP FOREIGN KEY tags_approval_borrowers_fk_1 });
+ $dbh->do(q{ ALTER TABLE tags_approval ADD CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE });
+
+ print "Upgrade to $DBversion done (Bug 13534 - Deleting staff patron will delete tags approved by this patron)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.023";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES('OpenLibrarySearch','0','If Yes Open Library search results will show in OPAC',NULL,'YesNo');
+ });
+
+ print "Upgrade to $DBversion done (Bug 6624 - Allow Koha to use the new read API from OpenLibrary)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.024";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers MODIFY COLUMN userid VARCHAR(75) DEFAULT NULL;
+ });
+
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers MODIFY COLUMN password VARCHAR(60) DEFAULT NULL;
+ });
+
+ print "Upgrade to $DBversion done (Bug 15517 - Tables borrowers and deletedborrowers differ again)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.025";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ DROP TABLE IF EXISTS nozebra;
+ });
+
+ print "Upgrade to $DBversion done (Bug 15526 - Drop nozebra database table)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.026";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET value = CONCAT_WS('|', IF(value='', NULL, value), "password") WHERE variable="PatronSelfRegistrationBorrowerUnwantedField" AND value NOT LIKE "%password%";
+ });
+
+ print "Upgrade to $DBversion done (Bug 15343 - Allow patrons to choose their own password on self registration)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.027";
+if ( CheckVersion($DBversion) ) {
+ my ( $db_value ) = $dbh->selectrow_array(q|SELECT count(*) FROM branches|);
+ my $pref_value = C4::Context->preference("singleBranchMode") || 0;
+ if ( $db_value > 1 and $pref_value == 1 ) {
+ warn "WARNING: You have more than 1 libraries in your branches tables but the singleBranchMode system preference is on.\n";
+ warn "This configuration does not make sense. The system preference is going to be deleted,\n";
+ warn "and this parameter will be based on the number of libraries defined.\n";
+ }
+ $dbh->do(q|DELETE FROM systempreferences WHERE variable="singleBranchMode"|);
+
+ print "Upgrade to $DBversion done (Bug 4941 - Can't set branch in staff client when singleBranchMode is enabled)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.028";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) SELECT 'PatronSelfModificationBorrowerUnwantedField',value,NULL,'Name the fields you don\'t want to display when a patron is editing their information via the OPAC.','free' FROM systempreferences WHERE variable = 'PatronSelfRegistrationBorrowerUnwantedField';
+ });
+
+ print "Upgrade to $DBversion done (Bug 14658 - Split PatronSelfRegistrationBorrowerUnwantedField into two preferences for creating and editing)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.029";
+if ( CheckVersion($DBversion) ) {
+
+ # move marc21_field_003.pl 040c and 040d to marc21_orgcode.pl
+ $dbh->do(q{
+ update marc_subfield_structure set value_builder='marc21_orgcode.pl' where value_builder IN ( 'marc21_field_003.pl', 'marc21_field_040c.pl', 'marc21_field_040d.pl' );
+ });
+ $dbh->do(q{
+ update auth_subfield_structure set value_builder='marc21_orgcode.pl' where value_builder IN ( 'marc21_field_003.pl', 'marc21_field_040c.pl', 'marc21_field_040d.pl' );
+ });
+
+ print "Upgrade to $DBversion done (Bug 14199 - Unify all organization code plugins)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.030";
+if(CheckVersion($DBversion)) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('OpacMaintenanceNotice','','','A user-defined block of HTML to appear on screen when OpacMaintenace is enabled','Textarea')
+ });
+
+ print "Upgrade to $DBversion done (Bug 15311: Let libraries set text to display when OpacMaintenance = on)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.031";
+if(CheckVersion($DBversion)) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
+ VALUES ('NoRenewalBeforePrecision', 'date', 'Calculate "No renewal before" based on date or exact time. Only relevant for loans calculated in days, hourly loans are not affected.', 'date|exact_time', 'Choice')
+ });
+
+ print "Upgrade to $DBversion done (Bug 14395 - Two different ways to calculate 'No renewal before')\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.032";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ -- Add issue_id to accountlines table
+ ALTER TABLE accountlines ADD issue_id INT(11) NULL DEFAULT NULL AFTER accountlines_id;
+ });
+
+## Close out any accruing fines with no current issue
+ $dbh->do(q{
+ UPDATE accountlines LEFT JOIN issues USING ( itemnumber, borrowernumber ) SET accounttype = 'F' WHERE accounttype = 'FU' and issues.issue_id IS NULL;
+ });
+
+## Close out any extra not really accruing fines, keep only the latest accring fine
+ $dbh->do(q{
+ UPDATE accountlines a1
+ LEFT JOIN (SELECT MAX(accountlines_id) AS keeper,
+ borrowernumber,
+ itemnumber
+ FROM accountlines
+ WHERE accounttype = 'FU'
+ GROUP BY borrowernumber, itemnumber
+ ) a2 USING ( borrowernumber, itemnumber )
+ SET a1.accounttype = 'F'
+ WHERE a1.accounttype = 'FU'
+ AND a1.accountlines_id != a2.keeper;
+ });
+
+## Update the unclosed fines to add the current issue_id to them
+ $dbh->do(q{
+ UPDATE accountlines LEFT JOIN issues USING ( itemnumber ) SET accountlines.issue_id = issues.issue_id WHERE accounttype = 'FU';
+ });
+
+ print "Upgrade to $DBversion done (Bug 15675 - Add issue_id column to accountlines and use it for updating fines)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.033";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET value = CONCAT_WS('|', IF(value = '', NULL, value), 'cardnumber') WHERE variable = 'PatronSelfRegistrationBorrowerUnwantedField' AND value NOT LIKE '%cardnumber%';
+ });
+
+ $dbh->do(q{
+ UPDATE systempreferences SET value = CONCAT_WS('|', IF(value = '', NULL, value), 'categorycode') WHERE variable = 'PatronSelfRegistrationBorrowerUnwantedField' AND value NOT LIKE '%categorycode%';
+ });
+
+ print "Upgrade to $DBversion done (Bug 14659 - Allow patrons to enter card number and patron category on OPAC registration page)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.034";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE `items` ADD `new` VARCHAR(32) NULL AFTER `stocknumber`;
+ });
+ $dbh->do(q{
+ ALTER TABLE `deleteditems` ADD `new` VARCHAR(32) NULL AFTER `stocknumber`;
+ });
+ print "Upgrade to $DBversion done (Bug 11023: Adds field 'new' in items and deleteditems tables)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.035";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('HTML5MediaYouTube',0,'Embed|Don\'t embed','YouTube links as videos','YesNo');
+ });
+ print "Upgrade to $DBversion done (Bug 14168 - enhance streaming cataloging to include youtube)\n";
+
+ SetVersion($DBversion);
+ }
+
+$DBversion = "3.23.00.036";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ INSERT INTO systempreferences (variable,value,explanation,type) VALUES ('HoldsQueueSkipClosed', '0', 'If enabled, any libraries that are closed when the holds queue is built will be ignored for the purpose of filling holds.', 'YesNo');
+ });
+ print "Upgrade to $DBversion done (Bug 12803 - Add ability to skip closed libraries when generating the holds queue)\n";
+ SetVersion($DBversion);
+ }
+
+$DBversion = "3.23.00.037";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+## Add the new currency.archived column
+ $dbh->do(q{
+ ALTER TABLE currency ADD column archived tinyint(1) DEFAULT 0;
+ });
+## Set currency=NULL if empty (just in case)
+ $dbh->do(q{
+ UPDATE aqorders SET currency=NULL WHERE currency="";
+ });
+## Insert the missing currency and mark them as archived before adding the FK
+ $dbh->do(q{
+ INSERT INTO currency(currency, archived) SELECT distinct currency, 1 FROM aqorders WHERE currency NOT IN (SELECT currency FROM currency);
+ });
+## Correct the field length in aqorders before adding FK too
+ $dbh->do(q{ ALTER TABLE aqorders MODIFY COLUMN currency varchar(10) default NULL; });
+## And finally add the FK
+ $dbh->do(q{
+ ALTER TABLE aqorders ADD FOREIGN KEY (currency) REFERENCES currency(currency) ON DELETE SET NULL ON UPDATE SET null;
+ });
+
+ print "Upgrade to $DBversion done (Bug 15084 - Move the currency related code to Koha::Acquisition::Currenc[y|ies])\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.038";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES ('decreaseLoanHighHoldsControl', 'static', 'static|dynamic', "Chooses between static and dynamic high holds checking", 'Choice'), ('decreaseLoanHighHoldsIgnoreStatuses', '', 'damaged|itemlost|notforloan|withdrawn', "Ignore items with these statuses for dynamic high holds checking", 'Choice');
+ });
+ print "Upgrade to $DBversion done (Bug 14694 - Make decreaseloanHighHolds more flexible)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.039";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ $dbh->do(q{
+ ALTER TABLE suggestions
+ MODIFY COLUMN currency varchar(10) default NULL;
+ });
+ $dbh->do(q{
+ ALTER TABLE aqbooksellers
+ MODIFY COLUMN currency varchar(10) default NULL;
+ });
+ print "Upgrade to $DBversion done (Bug 15084 - Move the currency related code to Koha::Acquisition::Currenc[y|ies])\n";
+ SetVersion($DBversion);
+}
+
+
+$DBversion = "3.23.00.040";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ my $c = $dbh->selectrow_array('SELECT COUNT(*) FROM systempreferences WHERE variable="intranetcolorstylesheet" AND value="blue.css"');
+
+ if ( $c ) {
+ print "WARNING: You are using a stylesheeet which has been removed from the Koha codebase.\n";
+ print "Update your intranetcolorstylesheet.\n";
+ }
+ print "Upgrade to $DBversion done (Bug 16019 - Check intranetcolorstylesheet for blue.css)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.041";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ my $dbh = C4::Context->dbh;
+ my ($print_error) = $dbh->{PrintError};
+ $dbh->{RaiseError} = 0;
+ $dbh->{PrintError} = 0;
+ $dbh->do("ALTER TABLE overduerules_transport_types ADD COLUMN letternumber INT(1) NOT NULL DEFAULT 1 AFTER id");
+ $dbh->{PrintError} = $print_error;
+
+ print "Upgrade to $DBversion done (Bug 16007: Make sure overduerules_transport_types.letternumber exists)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.042";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ $dbh->do(q{
+ ALTER TABLE items CHANGE new new_status VARCHAR(32) NULL;
+ });
+ $dbh->do(q{
+ ALTER TABLE deleteditems CHANGE new new_status VARCHAR(32) NULL;
+ });
+ $dbh->do(q{
+ UPDATE systempreferences SET value=REPLACE(value, '"items.new"', '"items.new_status"') WHERE variable="automatic_item_modification_by_age_configuration";
+ });
+
+ print "Upgrade to $DBversion done (Bug 16004 - Replace items.new with items.new_status)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.043";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET value="" WHERE value IS NULL;
+ });
+
+ print "Upgrade to $DBversion done (Bug 16070 - Empty (undef) system preferences may cause some issues in combination with memcache)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.044";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES
+ ('GoogleOpenIDConnect', '0', NULL, 'if ON, allows the use of Google OpenID Connect for login', 'YesNo'),
+ ('GoogleOAuth2ClientID', '', NULL, 'Client ID for the web app registered with Google', 'Free'),
+ ('GoogleOAuth2ClientSecret', '', NULL, 'Client Secret for the web app registered with Google', 'Free'),
+ ('GoogleOpenIDConnectDomain', '', NULL, 'Restrict OpenID Connect to this domain (or subdomains of this domain). Leave blank for all Google domains', 'Free');
+ });
+
+ print "Upgrade to $DBversion done (Bug 10988 - Allow login via Google OAuth2 (OpenID Connect))\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.045";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+## Holds details for vendors supplying goods by EDI
+ $dbh->do(q{
+ CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
+ id INT(11) NOT NULL auto_increment,
+ description TEXT NOT NULL,
+ host VARCHAR(40),
+ username VARCHAR(40),
+ password VARCHAR(40),
+ last_activity DATE,
+ vendor_id INT(11) REFERENCES aqbooksellers( id ),
+ download_directory TEXT,
+ upload_directory TEXT,
+ san VARCHAR(20),
+ id_code_qualifier VARCHAR(3) default '14',
+ transport VARCHAR(6) default 'FTP',
+ quotes_enabled TINYINT(1) not null default 0,
+ invoices_enabled TINYINT(1) not null default 0,
+ orders_enabled TINYINT(1) not null default 0,
+ responses_enabled TINYINT(1) not null default 0,
+ auto_orders TINYINT(1) not null default 0,
+ shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ),
+ PRIMARY KEY (id),
+ KEY vendorid (vendor_id),
+ KEY shipmentbudget (shipment_budget),
+ CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ),
+ CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id )
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ });
+
+## Hold the actual edifact messages with links to associated baskets
+ $dbh->do(q{
+ CREATE TABLE IF NOT EXISTS edifact_messages (
+ id INT(11) NOT NULL auto_increment,
+ message_type VARCHAR(10) NOT NULL,
+ transfer_date DATE,
+ vendor_id INT(11) REFERENCES aqbooksellers( id ),
+ edi_acct INTEGER REFERENCES vendor_edi_accounts( id ),
+ status TEXT,
+ basketno INT(11) REFERENCES aqbasket( basketno),
+ raw_msg MEDIUMTEXT,
+ filename TEXT,
+ deleted BOOLEAN NOT NULL DEFAULT 0,
+ PRIMARY KEY (id),
+ KEY vendorid ( vendor_id),
+ KEY ediacct (edi_acct),
+ KEY basketno ( basketno),
+ CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ),
+ CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ),
+ CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno )
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ });
+
+## invoices link back to the edifact message it was generated from
+ $dbh->do(q{
+ ALTER TABLE aqinvoices ADD COLUMN message_id INT(11) REFERENCES edifact_messages( id );
+ });
+
+## clean up link on deletes
+ $dbh->do(q{
+ ALTER TABLE aqinvoices ADD CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL;
+ });
+
+## Hold the supplier ids from quotes for ordering
+## although this is an EAN-13 article number the standard says 35 characters ???
+ $dbh->do(q{
+ ALTER TABLE aqorders ADD COLUMN line_item_id VARCHAR(35);
+ });
+
+## The suppliers unique reference usually a quotation line number ('QLI')
+## Otherwise Suppliers unique orderline reference ('SLI')
+ $dbh->do(q{
+ ALTER TABLE aqorders ADD COLUMN suppliers_reference_number VARCHAR(35);
+ });
+ $dbh->do(q{
+ ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier VARCHAR(3);
+ });
+ $dbh->do(q{
+ ALTER TABLE aqorders ADD COLUMN suppliers_report text;
+ });
+
+## hold the EAN/SAN used in ordering
+ $dbh->do(q{
+ CREATE TABLE IF NOT EXISTS edifact_ean (
+ ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ description VARCHAR(128) NULL DEFAULT NULL,
+ branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode),
+ ean VARCHAR(15) NOT NULL,
+ id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14',
+ CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode )
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ });
+## Syspref budget to hold shipping costs
+ $dbh->do(q{
+ INSERT INTO systempreferences (variable, explanation, type) VALUES('EDIInvoicesShippingBudget','The budget code used to allocate shipping charges to when processing EDI Invoice messages', 'free');
+ });
+
+## Add a permission for managing EDI
+ $dbh->do(q{
+ INSERT INTO permissions (module_bit, code, description) values (11, 'edi_manage', 'Manage EDIFACT transmissions');
+ });
+
+ print "Upgrade to $DBversion done (Bug 7736 - Edifact QUOTE and ORDER functionality))\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.046";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ $dbh->do(q{
+ ALTER TABLE vendor_edi_accounts ADD COLUMN plugin VARCHAR(256) NOT NULL DEFAULT "";
+ });
+
+ print "Upgrade to $DBversion done (Bug 15630 - Make Edifact module pluggable))\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.047";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('IntranetReportsHomeHTML', '', 'Show the following HTML in a div on the bottom of the reports home page', NULL, 'Free');
+ });
+ $dbh->do(q{
+ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('IntranetCirculationHomeHTML', '', 'Show the following HTML in a div on the bottom of the reports home page', NULL, 'Free');
+ });
+
+ print "Upgrade to $DBversion done (Bug 15008 - Add custom HTML areas to circulation and reports home pages)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "3.23.00.048";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ $dbh->do(q{
+ INSERT IGNORE INTO `systempreferences` (variable,value,options,explanation,type) SELECT 'OPACISBD', value, '70|10', 'Allows to define ISBD view in OPAC', 'Textarea' FROM `systempreferences` WHERE variable = 'ISBD';
+ });
+
+ print "Upgrade to $DBversion done (Bug 5979 - Add separate OPACISBD system preference)\n";
+ SetVersion($DBversion);
+}
+
+
+
+$DBversion = "3.23.00.049";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+my $dbh = C4::Context->dbh;
+my ( $column_has_been_used ) = $dbh->selectrow_array(q|
+ SELECT COUNT(*)
+ FROM borrower_attributes
+ WHERE password IS NOT NULL
+ |);
+
+if ( $column_has_been_used ) {
+ print q|WARNING: The columns borrower_attribute_types.password_allowed and borrower_attributes.password have been removed from the Koha codebase. They were not used. However your installation has at least one borrower_attributes.password defined. In order not to alter your data, the columns have been kept, please save the information elsewhere and remove these columns manually.|;
+} else {
+ $dbh->do(q|
+ ALTER TABLE borrower_attribute_types DROP column password_allowed
+ |);
+ $dbh->do(q|
+ ALTER TABLE borrower_attributes DROP column password;
+ |);
+ }
+ print "Upgrade to $DBversion done (Bug 12267 - Allow password option in Patron Attribute non functional)\n";
+ SetVersion($DBversion);
+}
+
+
+$DBversion = "XXX";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ my $installer = C4::Installer->new();
+ my $full_path = C4::Context->config('intranetdir') . "/installer/data/$installer->{dbms}/elasticsearch_mapping.sql";
+ my $error = $installer->load_sql($full_path);
+ warn $error if $error;
+ print "Upgrade to $DBversion done (Bug 12478 - set up elasticsearch tables)\n";
+ SetVersion($DBversion);
+}
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
# SEE bug 13068