From 5056eb1ef16a07aac6bee762846f2a252c2f38d8 Mon Sep 17 00:00:00 2001
From: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Date: Tue, 3 Dec 2024 14:21:34 +0200
Subject: [PATCH] Bug 38602: Do not check if columns bookings.creation_date and
 bookings.modification_date from information_schema

If one has multiple databases in use adding columns
bookings.creation_date and bookings.modification_date is added
just to first updated database and skipped in other updates.
This happens because we check if these columns already exist
in database from information_schema. We should instead make
this check with method column_exists.

To test:
1. Apply this patch.
3. Drop columns bookings.creation_date and bookings.modification_date:
ALTER TABLE bookings DROP COLUMN creation_date;
ALTER TABLE bookings DROP COLUMN modification_date;
2. Revert your database back to version 24.0600028:
UPDATE systempreferences SET value = "24.0600014" WHERE variable = "version";
3. Run updatedatabase.pl.
=> Check that columns were added.

If you happen to have two databases:
1. Check if you have columns in your bookings table:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN
('creation_date', 'modification_date');
=> Note that columns exist only in one of the databases.
2. Make sure you're using database missing columns
from booking table.
3. Apply this patch.
4. Revert back to version 24.0600028.
5. Run updatedatabase.pl
=> Check that columns were now added to database.

Sponsored-by: Koha-Suomi Oy
Signed-off-by: David Nind <david@davidnind.com>
---
 installer/data/mysql/db_revs/240600029.pl | 13 +++----------
 1 file changed, 3 insertions(+), 10 deletions(-)

diff --git a/installer/data/mysql/db_revs/240600029.pl b/installer/data/mysql/db_revs/240600029.pl
index 3dd3438afe..709da5b941 100755
--- a/installer/data/mysql/db_revs/240600029.pl
+++ b/installer/data/mysql/db_revs/240600029.pl
@@ -8,14 +8,7 @@ return {
         my ($args) = @_;
         my ( $dbh, $out ) = @{$args}{qw(dbh out)};
 
-        my $columns_exist_query = <<~'SQL';
-            SELECT column_name
-            FROM information_schema.COLUMNS
-            WHERE table_name = 'bookings'
-                AND column_name IN ('creation_date', 'modification_date')
-        SQL
-        my $existing_columns = $dbh->selectcol_arrayref($columns_exist_query);
-        if ( @{$existing_columns} == 2 ) {
+        if ( column_exists( 'bookings', 'creation_date' ) && column_exists( 'bookings', 'modification_date' ) ) {
             say_info(
                 $out,
                 q{Columns 'creation_date' and 'modification_date' already exist in 'bookings' table. Skipping...}
@@ -30,7 +23,7 @@ return {
         my $modification_date_statement = <<~'SQL';
             ALTER TABLE bookings ADD COLUMN modification_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'the timestamp for when a booking has been updated'
         SQL
-        if ( @{$existing_columns} == 0 ) {
+        unless ( column_exists( 'bookings', 'creation_date' ) && column_exists( 'bookings', 'modification_date' )) {
             $dbh->do("$creation_date_statement AFTER `end_date`");
             say_success( $out, q{Added column 'bookings.creation_date'} );
 
@@ -40,7 +33,7 @@ return {
             return;
         }
 
-        if ( @{$existing_columns} == 1 ) {
+        if ( column_exists( 'bookings', 'creation_date' ) || column_exists( 'bookings', 'modification_date' ) ) {
             foreach my $column ( 'creation_date', 'modification_date' ) {
                 if ( column_exists( 'bookings', $column ) ) {
                     next;
-- 
2.39.5