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

(-)a/installer/data/mysql/atomicupdate/bug_40665-add_booking_id_issues_old_issues.pl (+55 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use Koha::Installer::Output qw(say_warning say_success say_info);
3
4
return {
5
    bug_number  => "40665",
6
    description => "Add booking_id to issues and old_issues tables to link checkouts to bookings",
7
    up          => sub {
8
        my ($args) = @_;
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
11
        if ( !column_exists( 'issues', 'booking_id' ) ) {
12
            $dbh->do(
13
                q{
14
                ALTER TABLE issues
15
                ADD COLUMN booking_id int(11) DEFAULT NULL
16
                COMMENT 'foreign key linking this checkout to the booking it fulfills'
17
                AFTER itemnumber
18
            }
19
            );
20
21
            $dbh->do(
22
                q{
23
                ALTER TABLE issues
24
                ADD CONSTRAINT issues_booking_id_fk
25
                FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
26
                ON DELETE SET NULL ON UPDATE CASCADE
27
            }
28
            );
29
30
            say_success( $out, "Added column 'issues.booking_id'" );
31
        }
32
33
        if ( !column_exists( 'old_issues', 'booking_id' ) ) {
34
            $dbh->do(
35
                q{
36
                ALTER TABLE old_issues
37
                ADD COLUMN booking_id int(11) DEFAULT NULL
38
                COMMENT 'foreign key linking this checkout to the booking it fulfilled'
39
                AFTER itemnumber
40
            }
41
            );
42
43
            $dbh->do(
44
                q{
45
                ALTER TABLE old_issues
46
                ADD CONSTRAINT old_issues_booking_id_fk
47
                FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
48
                ON DELETE SET NULL ON UPDATE CASCADE
49
            }
50
            );
51
52
            say_success( $out, "Added column 'old_issues.booking_id'" );
53
        }
54
    },
55
};
(-)a/installer/data/mysql/kohastructure.sql (-3 / +6 lines)
Lines 3976-3981 CREATE TABLE `issues` ( Link Here
3976
  `borrowernumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
3976
  `borrowernumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
3977
  `issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item',
3977
  `issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item',
3978
  `itemnumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
3978
  `itemnumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
3979
  `booking_id` int(11) DEFAULT NULL COMMENT 'foreign key linking this checkout to the booking it fulfills',
3979
  `date_due` datetime DEFAULT NULL COMMENT 'datetime the item is due (yyyy-mm-dd hh:mm::ss)',
3980
  `date_due` datetime DEFAULT NULL COMMENT 'datetime the item is due (yyyy-mm-dd hh:mm::ss)',
3980
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out',
3981
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out',
3981
  `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned, will be NULL until moved to old_issues',
3982
  `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned, will be NULL until moved to old_issues',
Lines 4000-4006 CREATE TABLE `issues` ( Link Here
4000
  KEY `issues_ibfk_borrowers_borrowernumber` (`issuer_id`),
4001
  KEY `issues_ibfk_borrowers_borrowernumber` (`issuer_id`),
4001
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON UPDATE CASCADE,
4002
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON UPDATE CASCADE,
4002
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON UPDATE CASCADE,
4003
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON UPDATE CASCADE,
4003
  CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
4004
  CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
4005
  CONSTRAINT `issues_booking_id_fk` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`booking_id`) ON DELETE SET NULL ON UPDATE CASCADE
4004
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4006
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4005
/*!40101 SET character_set_client = @saved_cs_client */;
4007
/*!40101 SET character_set_client = @saved_cs_client */;
4006
4008
Lines 5032-5037 CREATE TABLE `old_issues` ( Link Here
5032
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
5034
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
5033
  `issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item',
5035
  `issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item',
5034
  `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
5036
  `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
5037
  `booking_id` int(11) DEFAULT NULL COMMENT 'foreign key linking this checkout to the booking it fulfills',
5035
  `date_due` datetime DEFAULT NULL COMMENT 'date the item is due (yyyy-mm-dd)',
5038
  `date_due` datetime DEFAULT NULL COMMENT 'date the item is due (yyyy-mm-dd)',
5036
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out',
5039
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out',
5037
  `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned',
5040
  `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned',
Lines 5055-5061 CREATE TABLE `old_issues` ( Link Here
5055
  KEY `old_issues_ibfk_borrowers_borrowernumber` (`issuer_id`),
5058
  KEY `old_issues_ibfk_borrowers_borrowernumber` (`issuer_id`),
5056
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
5059
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
5057
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
5060
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
5058
  CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
5061
  CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
5062
  CONSTRAINT `old_issues_booking_id_fk` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`booking_id`) ON DELETE SET NULL ON UPDATE CASCADE
5059
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5063
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5060
/*!40101 SET character_set_client = @saved_cs_client */;
5064
/*!40101 SET character_set_client = @saved_cs_client */;
5061
5065
5062
- 

Return to bug 40665