use File::Path qw[remove_tree]; # perl core module
use File::Spec;
-use Path::Tiny;
+use File::Slurp;
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
$dbh->do("ALTER TABLE biblioitems DROP INDEX isbn");
$dbh->do("ALTER TABLE biblioitems DROP INDEX issn");
+ $dbh->do("ALTER TABLE biblioitems DROP INDEX issn_idx");
$dbh->do("ALTER TABLE biblioitems
CHANGE isbn isbn MEDIUMTEXT NULL DEFAULT NULL,
CHANGE issn issn MEDIUMTEXT NULL DEFAULT NULL
MODIFY COLUMN seealso varchar(1100) COLLATE utf8_unicode_ci DEFAULT NULL,
MODIFY COLUMN link varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL
|);
+ $dbh->do(qq|ALTER TABLE $name CHARACTER SET utf8 COLLATE utf8_unicode_ci|);
}
else {
$dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci|);
$DBversion = "3.19.00.041";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q|
- ALTER IGNORE TABLE suggestions ADD KEY status (STATUS)
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE suggestions ADD KEY biblionumber (biblionumber)
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE suggestions ADD KEY branchcode (branchcode)
- |);
+ unless ( constraint_exists( 'suggestions', 'status' ) ) {
+ $dbh->do(q|
+ ALTER TABLE suggestions ADD KEY status (STATUS)
+ |);
+ }
+ unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {
+ $dbh->do(q|
+ ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
+ |);
+ }
+ unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {
+ $dbh->do(q|
+ ALTER TABLE suggestions ADD KEY branchcode (branchcode)
+ |);
+ }
print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
SetVersion ($DBversion);
}
WHERE auth_types.authtypecode IS NULL
});
- $dbh->do(q{
- ALTER IGNORE TABLE auth_subfield_structure
- ADD CONSTRAINT auth_subfield_structure_ibfk_1
- FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
- ON DELETE CASCADE ON UPDATE CASCADE
- });
+ unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
+ $dbh->do(q{
+ ALTER TABLE auth_subfield_structure
+ ADD CONSTRAINT auth_subfield_structure_ibfk_1
+ FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
+ ON DELETE CASCADE ON UPDATE CASCADE
+ });
+ }
print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
SetVersion($DBversion);
$DBversion = "3.21.00.007";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q|
- ALTER IGNORE TABLE aqbasket
- ADD KEY authorisedby (authorisedby)
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE aqbooksellers
- ADD KEY name (name(255))
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE aqbudgets
- ADD KEY budget_parent_id (budget_parent_id),
- ADD KEY budget_code (budget_code),
- ADD KEY budget_branchcode (budget_branchcode),
- ADD KEY budget_period_id (budget_period_id),
- ADD KEY budget_owner_id (budget_owner_id)
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE aqbudgets_planning
- ADD KEY budget_period_id (budget_period_id)
- |);
- $dbh->do(q|
- ALTER IGNORE TABLE aqorders
- ADD KEY parent_ordernumber (parent_ordernumber),
- ADD KEY orderstatus (orderstatus)
- |);
+ unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbasket
+ ADD KEY authorisedby (authorisedby)
+ |);
+ }
+ unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbooksellers
+ ADD KEY name (name(255))
+ |);
+ }
+ unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets
+ ADD KEY budget_parent_id (budget_parent_id)|);
+ }
+ unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets
+ ADD KEY budget_code (budget_code)|);
+ }
+ unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets
+ ADD KEY budget_branchcode (budget_branchcode)|);
+ }
+ unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets
+ ADD KEY budget_period_id (budget_period_id)|);
+ }
+ unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets
+ ADD KEY budget_owner_id (budget_owner_id)|);
+ }
+ unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqbudgets_planning
+ ADD KEY budget_period_id (budget_period_id)|);
+ }
+ unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqorders
+ ADD KEY parent_ordernumber (parent_ordernumber)|);
+ }
+ unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {
+ $dbh->do(q|
+ ALTER TABLE aqorders
+ ADD KEY orderstatus (orderstatus)|);
+ }
print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
SetVersion ($DBversion);
}
INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
});
- $dbh->do(q|
- ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
- |);
+
+ if ( constraint_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
+ $dbh->do(q|
+ ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
+ |);
+ }
print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
SetVersion ($DBversion);
}
$DBversion = "3.21.00.019";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q{
- ALTER TABLE reserves DROP constrainttype
- });
- $dbh->do(q{
- ALTER TABLE old_reserves DROP constrainttype
- });
+ if ( column_exists( 'reserves', 'constrainttype' ) ) {
+ $dbh->do(q{
+ ALTER TABLE reserves DROP constrainttype
+ });
+ $dbh->do(q{
+ ALTER TABLE old_reserves DROP constrainttype
+ });
+ }
$dbh->do(q{
DROP TABLE IF EXISTS reserveconstraints
});
my ($print_error) = $dbh->{PrintError};
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 0;
- $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
- $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
+ if ( constraint_exists('course_reserves', 'course_reserves_ibfk_2') ) {
+ $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
+ $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
+ }
$dbh->{PrintError} = $print_error;
$dbh->do(q{
- ALTER IGNORE TABLE course_reserves
+ ALTER TABLE course_reserves
ADD CONSTRAINT course_reserves_ibfk_2
FOREIGN KEY (ci_id) REFERENCES course_items (ci_id)
ON DELETE CASCADE ON UPDATE CASCADE
$DBversion = "3.21.00.028";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q{
- ALTER TABLE uploaded_files
- ADD COLUMN public tinyint,
- ADD COLUMN permanent tinyint
- });
- $dbh->do(q{
- UPDATE uploaded_files SET public=1, permanent=1
- });
- $dbh->do(q{
- ALTER TABLE uploaded_files
- CHANGE COLUMN categorycode uploadcategorycode tinytext
- });
+ unless ( column_exists('uploaded_files', 'public') ) {
+ $dbh->do(q{
+ ALTER TABLE uploaded_files
+ ADD COLUMN public tinyint,
+ ADD COLUMN permanent tinyint
+ });
+ $dbh->do(q{
+ UPDATE uploaded_files SET public=1, permanent=1
+ });
+ $dbh->do(q{
+ ALTER TABLE uploaded_files
+ CHANGE COLUMN categorycode uploadcategorycode tinytext
+ });
+ }
print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
SetVersion($DBversion);
}
$DBversion = "3.21.00.029";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q{
- ALTER IGNORE TABLE discharges
- ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
- });
+ unless ( column_exists('discharges', 'discharge_id') ) {
+ $dbh->do(q{
+ ALTER TABLE discharges
+ ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
+ });
+ }
print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
SetVersion($DBversion);
}
my $sth2 = $dbh->prepare("SELECT * FROM subscription_numberpatterns WHERE id = ?");
- my $sth3 = $dbh->prepare("UPDATE serials SET serialseq_x = ?, serialseq_y = ?, serialseq_z = ? WHERE serialid = ?");
+ my $sth3 = $dbh->prepare("UPDATE serial SET serialseq_x = ?, serialseq_y = ?, serialseq_z = ? WHERE serialid = ?");
foreach my $subscription ( $sth->fetchrow_hashref() ) {
next if !defined($subscription);
- my $number_pattern = $subscription->numberpattern();
$sth2->execute( $subscription->{numberpattern} );
- $number_pattern = $sth2->fetchrow_hashref();
+ my $number_pattern = $sth2->fetchrow_hashref();
my $numbering_method = $number_pattern->{numberingmethod};
# Get all the data between the enumeration values, we need
$DBversion = "16.06.00.027";
if ( CheckVersion($DBversion) ) {
- $dbh->do(q{
- ALTER IGNORE TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
- });
- $dbh->do(q{
- ALTER IGNORE TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
- });
+ unless ( column_exists('borrowers', 'lastseen') ) {
+ $dbh->do(q{
+ ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
+ });
+ $dbh->do(q{
+ ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
+ });
+ }
$dbh->do(q{
INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
});
UPDATE marc_subfield_structure SET authorised_value = NULL WHERE authorised_value = ';';
});
+ # If the DB has been created before 3.19.00.006, the default collate for marc_subfield_structure if not set to utf8_unicode_ci and the new FK will not be create (MariaDB or MySQL will raise err 150)
+ my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE marc_subfield_structure|);
+ $table_sth->execute;
+ my @table = $table_sth->fetchrow_array;
+ if ( $table[1] !~ /COLLATE=utf8_unicode_ci/ and $table[1] !~ /COLLATE=utf8mb4_unicode_ci/ ) { #catches utf8mb4 collated tables
+ $dbh->do(qq|ALTER TABLE marc_subfield_structure CHARACTER SET utf8 COLLATE utf8_unicode_ci|);
+ }
$dbh->do(q{
ALTER TABLE marc_subfield_structure
MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL,
SetVersion($DBversion);
}
+$DBversion = '16.06.00.043';
+if ( CheckVersion($DBversion) ) {
+ # Add the new columns
+ $dbh->do(q|
+ ALTER TABLE aqorders
+ ADD COLUMN tax_rate_on_ordering decimal(6,4) default NULL AFTER tax_rate,
+ ADD COLUMN tax_rate_on_receiving decimal(6,4) default NULL AFTER tax_rate_on_ordering,
+ ADD COLUMN tax_value_on_ordering decimal(28,6) default NULL AFTER tax_value,
+ ADD COLUMN tax_value_on_receiving decimal(28,6) default NULL AFTER tax_value_on_ordering
+ |);
+
+ my $orders = $dbh->selectall_arrayref(q|
+ SELECT * FROM aqorders
+ |, { Slice => {} } );
+
+ my $sth_update_order = $dbh->prepare(q|
+ UPDATE aqorders
+ SET tax_rate_on_ordering = tax_rate,
+ tax_rate_on_receiving = tax_rate,
+ tax_value_on_ordering = ?,
+ tax_value_on_receiving = ?
+ WHERE ordernumber = ?
+ |);
+
+ require Koha::Number::Price;
+ for my $order (@$orders) {
+ my $tax_value_on_ordering =
+ $order->{quantity} *
+ $order->{ecost_tax_excluded} *
+ $order->{tax_rate};
+
+ my $tax_value_on_receiving =
+ ( defined $order->{unitprice_tax_excluded} )
+ ? $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate}
+ : undef;
+
+ $sth_update_order->execute( $tax_value_on_ordering,
+ $tax_value_on_receiving, $order->{ordernumber} );
+ }
+
+ # Remove the old columns
+ $dbh->do(q|
+ ALTER TABLE aqorders
+ CHANGE COLUMN tax_value tax_value_bak decimal(28,6) default NULL,
+ CHANGE COLUMN tax_rate tax_rate_bak decimal(6,4) default NULL
+ |);
+
+ print "Upgrade to $DBversion done (Bug 13323 - Change the tax rate on receiving)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '16.06.00.044';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE `messages`
+ ADD `manager_id` int(11) NULL,
+ ADD FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL;
+ });
+
+ print "Upgrade to $DBversion done (Bug 17397 - Show name of librarian who created circulation message)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '16.06.00.045';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE systempreferences SET options = "now|dateexpiry|combination", explanation = "Set whether the borrower renewal date should be counted from the dateexpiry, from the current date or by combination: if the dateexpiry is in future use dateexpiry, else use current date " WHERE variable = "BorrowerRenewalPeriodBase";
+ });
+
+ print "Upgrade to $DBversion done (Bug 17443 - Make possible to renew patron by later of expiry and current date)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '16.06.00.046';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE issuingrules ADD COLUMN no_auto_renewal_after INT(4) DEFAULT NULL AFTER auto_renew;
+ });
+
+ print "Upgrade to $DBversion done (Bug 15581 - Add a circ rule to not allow auto-renewals after defined loan period)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '16.06.00.047';
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ UPDATE language_descriptions SET description = 'Čeština' WHERE subtag = 'cs' AND type = 'language' AND lang = 'cs'
+ });
+
+ print "Upgrade to $DBversion done (Bug 17518: Displayed language name for Czech is wrong)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = '16.06.00.048';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO permissions (module_bit, code, description) VALUES
+ (13, 'upload_general_files', 'Upload any file'),
+ (13, 'upload_manage', 'Manage uploaded files');
+ |);
+
+ # Update user_permissions for current users (check count in uploaded_files)
+ # Note 9 == edit_catalogue and 13 == tools
+ # We do not insert if someone is superlibrarian, does not have edit_catalogue,
+ # or already has all tools
+ $dbh->do(q|
+ INSERT IGNORE INTO user_permissions (borrowernumber, module_bit, code)
+ SELECT borrowernumber, 13, 'upload_general_files'
+ FROM borrowers bo
+ WHERE flags<>1 AND flags & POW(2,13) = 0 AND
+ ( flags & POW(2,9) > 0 OR (
+ SELECT COUNT(*) FROM user_permissions
+ WHERE borrowernumber=bo.borrowernumber AND module_bit=9 ) > 0 )
+ AND ( SELECT COUNT(*) FROM uploaded_files ) > 0;
+ |);
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 17663 - Forgotten userpermissions)\n";
+}
+
+$DBversion = '16.06.00.049';
+if( CheckVersion( $DBversion ) ) {
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('ReplytoDefault', '', NULL, 'The default email address to be set as replyto.', 'Free');
+ |);
+
+ $dbh->do(q|
+ INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
+ VALUES ('ReturnpathDefault', '', NULL, 'The default email address to be set as return-path', 'Free');
+ |);
+
+ SetVersion( $DBversion );
+ print "Upgrade to $DBversion done (Bug 17391 - ReturnpathDefault and ReplyToDefault missing from syspref.sql)\n";
+}
+
+$DBversion = "16.06.00.050";
+if ( CheckVersion($DBversion) ) {
+
+ # If index issn_idx still exists, we assume that dbrev 3.15.00.049 failed,
+ # and we repeat it (partially).
+ # Note: the db rev only pertains to biblioitems and is not needed for
+ # deletedbiblioitems.
+
+ my $temp = $dbh->selectall_arrayref( "SHOW INDEXES FROM biblioitems WHERE key_name = 'issn_idx'" );
+
+ if( @$temp > 0 ) {
+ $dbh->do( "ALTER TABLE biblioitems DROP INDEX isbn" );
+ $dbh->do( "ALTER TABLE biblioitems DROP INDEX issn" );
+ $dbh->do( "ALTER TABLE biblioitems DROP INDEX issn_idx" );
+ $dbh->do( "ALTER TABLE biblioitems CHANGE isbn isbn MEDIUMTEXT NULL DEFAULT NULL, CHANGE issn issn MEDIUMTEXT NULL DEFAULT NULL" );
+ $dbh->do( "ALTER TABLE biblioitems ADD INDEX isbn ( isbn ( 255 ) ), ADD INDEX issn ( issn ( 255 ) )" );
+ print "Upgrade to $DBversion done (Bug 8835). Removed issn_idx.\n";
+ } else {
+ print "Upgrade to $DBversion done (Bug 8835). Everything is fine.\n";
+ }
+
+ SetVersion($DBversion);
+}
+
+$DBversion = "16.11.00.000";
+if ( CheckVersion($DBversion) ) {
+ print "Upgrade to $DBversion done (Koha 16.11)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "16.12.00.000";
+if ( CheckVersion($DBversion) ) {
+ print "Upgrade to $DBversion done (Koha 16.12 - Our battered suitcases were piled on the sidewalk again; we had longer ways to go. But no matter, the road is life.)\n";
+ SetVersion($DBversion);
+}
+
+$DBversion = "16.12.00.001";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do(q{
+ ALTER TABLE borrower_modifications
+ ADD COLUMN extended_attributes text DEFAULT NULL
+ AFTER privacy
+ });
+
+ print "Upgrade to $DBversion done (Bug 17767 - Let Koha::Patron::Modification handle extended attributes)\n";
+ SetVersion($DBversion);
+}
+
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
# SEE bug 13068
# if there is anything in the atomicupdate, read and execute it.
opendir( my $dirh, $update_dir );
foreach my $file ( sort readdir $dirh ) {
next if $file !~ /\.(sql|perl)$/; #skip other files
+ next if $file eq 'skeleton.perl'; # skip the skeleton file
print "DEV atomic update: $file\n";
if ( $file =~ /\.sql$/ ) {
my $installer = C4::Installer->new();
my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1;
} elsif ( $file =~ /\.perl$/ ) {
- my $code = path( $update_dir . $file )->slurp_utf8;
+ my $code = read_file( $update_dir . $file );
eval $code;
say "Atomic update generated errors: $@" if $@;
}
}
}
+sub constraint_exists {
+ my ( $table_name, $key_name ) = @_;
+ my $dbh = C4::Context->dbh;
+ my ($exists) = $dbh->selectrow_array(
+ qq|
+ SHOW INDEX FROM $table_name
+ WHERE key_name = ?
+ |, undef, $key_name
+ );
+ return $exists;
+}
+
+sub column_exists {
+ my ( $table_name, $column_name ) = @_;
+ my $dbh = C4::Context->dbh;
+ my ($exists) = $dbh->selectrow_array(
+ qq|
+ SHOW COLUMNS FROM $table_name
+ WHERE Field = ?
+ |, undef, $column_name
+ );
+ return $exists;
+
+}
+
exit;