Attachment #170144 for bug #37592

View | Details | Raw Unified | Return to bug 37592
Collapse All | Expand All




use Modern::Perl;
use Koha::Installer::Output qw(say_warning say_failure say_success say_info);

return {
    bug_number  => '37592',
    description => 'Add created_at, updated_at fields to bookings table',
    up          => sub {
        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 ('created_at', 'updated_at')
        SQL
        my $existing_columns = $dbh->selectcol_arrayref($columns_exist_query);
        if ( @{$existing_columns} == 2 ) {
            say_info( $out, q{Columns 'created_at' and 'updated_at' already exist in 'bookings' table. Skipping...} );

            return;
        }

        my $created_at_statement = <<~'SQL';
            ALTER TABLE bookings ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'The timestamp for when a bookings was created'
        SQL
        my $updated_at_statement = <<~'SQL';
            ALTER TABLE bookings ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The timestamp for when a booking has been updated'
        SQL
        if ( @{$existing_columns} == 0 ) {
            if ( $dbh->do("$created_at_statement AFTER `end_date`") ) {
                say_success( $out, q{Added column 'bookings.created_at'} );
            } else {
                say_failure( $out, q{Failed to add column 'bookings.created_at': } . $dbh->errstr );
            }

            if ( $dbh->do("$updated_at_statement AFTER `created_at`") ) {
                say_success( $out, q{Added column 'bookings.updated_at'} );
            } else {
                say_failure( $out, q{Failed to add column 'bookings.updated_at': } . $dbh->errstr );
            }

            return;
        }

        if ( @{$existing_columns} == 1 ) {
            foreach my $column ( 'created_at', 'updated_at' ) {
                if ( column_exists( 'bookings', $column ) ) {
                    next;
                }

                my $statement;
                if ( $column eq 'created_at' ) {
                    $statement = "$created_at_statement AFTER `end_date`";
                }

                if ( $column eq 'updated_at' ) {
                    $statement = "$updated_at_statement AFTER `created_at`";
                }

                if ( $dbh->do($statement) ) {
                    say_success( $out, "Added column 'bookings.$column'" );
                } else {
                    say_failure( $out, "Failed to add column 'bookings.$column': " . $dbh->errstr );
                }
            }
        }
    },
};

Lines 1216-1221 CREATE TABLE `bookings` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +2 lines)
1216	`pickup_library_id` varchar(10) NOT NULL COMMENT 'Identifier for booking pickup library',	1216	`pickup_library_id` varchar(10) NOT NULL COMMENT 'Identifier for booking pickup library',
1217	`start_date` datetime DEFAULT NULL COMMENT 'the start date of the booking',	1217	`start_date` datetime DEFAULT NULL COMMENT 'the start date of the booking',
1218	`end_date` datetime DEFAULT NULL COMMENT 'the end date of the booking',	1218	`end_date` datetime DEFAULT NULL COMMENT 'the end date of the booking',
		1219	`created_at` timestamp DEFAULT current_timestamp() COMMENT 'the timestamp for when a booking was created',
		1220	`updated_at` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the timestamp for when a booking has been updated',
1219	PRIMARY KEY (`booking_id`),	1221	PRIMARY KEY (`booking_id`),
1220	KEY `patron_id` (`patron_id`),	1222	KEY `patron_id` (`patron_id`),
1221	KEY `biblio_id` (`biblio_id`),	1223	KEY `biblio_id` (`biblio_id`),
1222	-

Return to bug 37592

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_37592-add_created_at_updated_at_to_bookings.pl (+69 lines)
		1	use Modern::Perl;
		2	use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
		3
		4	return {
		5	bug_number => '37592',
		6	description => 'Add created_at, updated_at fields to bookings table',
		7	up => sub {
		8	my ($args) = @_;
		9	my ( $dbh, $out ) = @{$args}{qw(dbh out)};
		10
		11	my $columns_exist_query = <<~'SQL';
		12	SELECT column_name
		13	FROM information_schema.COLUMNS
		14	WHERE table_name = 'bookings'
		15	AND column_name IN ('created_at', 'updated_at')
		16	SQL
		17	my $existing_columns = $dbh->selectcol_arrayref($columns_exist_query);
		18	if ( @{$existing_columns} == 2 ) {
		19	say_info( $out, q{Columns 'created_at' and 'updated_at' already exist in 'bookings' table. Skipping...} );
		20
		21	return;
		22	}
		23
		24	my $created_at_statement = <<~'SQL';
		25	ALTER TABLE bookings ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'The timestamp for when a bookings was created'
		26	SQL
		27	my $updated_at_statement = <<~'SQL';
		28	ALTER TABLE bookings ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The timestamp for when a booking has been updated'
		29	SQL
		30	if ( @{$existing_columns} == 0 ) {
		31	if ( $dbh->do("$created_at_statement AFTER `end_date`") ) {
		32	say_success( $out, q{Added column 'bookings.created_at'} );
		33	} else {
		34	say_failure( $out, q{Failed to add column 'bookings.created_at': } . $dbh->errstr );
		35	}
		36
		37	if ( $dbh->do("$updated_at_statement AFTER `created_at`") ) {
		38	say_success( $out, q{Added column 'bookings.updated_at'} );
		39	} else {
		40	say_failure( $out, q{Failed to add column 'bookings.updated_at': } . $dbh->errstr );
		41	}
		42
		43	return;
		44	}
		45
		46	if ( @{$existing_columns} == 1 ) {
		47	foreach my $column ( 'created_at', 'updated_at' ) {
		48	if ( column_exists( 'bookings', $column ) ) {
		49	next;
		50	}
		51
		52	my $statement;
		53	if ( $column eq 'created_at' ) {
		54	$statement = "$created_at_statement AFTER `end_date`";
		55	}
		56
		57	if ( $column eq 'updated_at' ) {
		58	$statement = "$updated_at_statement AFTER `created_at`";
		59	}
		60
		61	if ( $dbh->do($statement) ) {
		62	say_success( $out, "Added column 'bookings.$column'" );
		63	} else {
		64	say_failure( $out, "Failed to add column 'bookings.$column': " . $dbh->errstr );
65	}
66	}
67	}
68	},
69	};