Attachment #171377 for bug #37592

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





return {
    bug_number  => '37592',
    description => 'Add created_on, updated_on fields to bookings table',
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @{$args}{qw(dbh out)};

            SELECT column_name
            FROM information_schema.COLUMNS
            WHERE table_name = 'bookings'
                AND column_name IN ('created_on', 'updated_on')
        SQL
        my $existing_columns = $dbh->selectcol_arrayref($columns_exist_query);
        if ( @{$existing_columns} == 2 ) {
            say_info( $out, q{Columns 'created_on' and 'updated_on' already exist in 'bookings' table. Skipping...} );

            return;
        }

        my $created_on_statement = <<~'SQL';
            ALTER TABLE bookings ADD COLUMN created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'The timestamp for when a bookings was created'
        SQL
        my $updated_on_statement = <<~'SQL';
            ALTER TABLE bookings ADD COLUMN updated_on 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_on_statement AFTER `end_date`") ) {
                say_success( $out, q{Added column 'bookings.created_on'} );
            } else {
                say_failure( $out, q{Failed to add column 'bookings.created_on': } . $dbh->errstr );
            }

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

            return;
        }

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

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

                if ( $column eq 'updated_on' ) {
                    $statement = "$updated_on_statement AFTER `created_on`";
                }

                if ( $dbh->do($statement) ) {

Lines 1217-1224 CREATE TABLE `bookings` ( Link Here

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

Return to bug 37592

Lines 3-9 use Koha::Installer::Output qw(say_warning say_failure say_success say_info); Link Here

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