5 description => "Add ILL batches",
8 my ( $dbh, $out ) = @$args{qw(dbh out)};
11 CREATE TABLE IF NOT EXISTS `illbatch_statuses` (
12 `id` int(11) NOT NULL auto_increment COMMENT "Status ID",
13 `name` varchar(100) NOT NULL COMMENT "Name of status",
14 `code` varchar(20) NOT NULL COMMENT "Unique, immutable code for status",
15 `is_system` tinyint(1) COMMENT "Is this status required for system operation",
17 UNIQUE KEY `u_illbatchstatuses__code` (`code`)
18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
23 CREATE TABLE IF NOT EXISTS `illbatches` (
24 `id` int(11) NOT NULL auto_increment COMMENT "Batch ID",
25 `name` varchar(100) NOT NULL COMMENT "Unique name of batch",
26 `backend` varchar(20) NOT NULL COMMENT "Name of batch backend",
27 `borrowernumber` int(11) COMMENT "Patron associated with batch",
28 `branchcode` varchar(50) COMMENT "Branch associated with batch",
29 `statuscode` varchar(20) COMMENT "Status of batch",
31 UNIQUE KEY `u_illbatches__name` (`name`),
32 CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
33 CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
34 CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
38 unless ( column_exists( 'illrequests', 'batch_id' ) ) {
41 ALTER TABLE `illrequests`
42 ADD COLUMN `batch_id` int(11) AFTER backend -- Optional ID of batch that this request belongs to
47 unless ( foreign_key_exists( 'illrequests', 'illrequests_ibfk' ) ) {
50 ALTER TABLE `illrequests`
51 ADD CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
56 unless ( foreign_key_exists( 'illbatches', 'illbatches_bnfk' ) ) {
59 ALTER TABLE `illbatches`
60 ADD CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
65 unless ( foreign_key_exists( 'illbatches', 'illbatches_bcfk' ) ) {
68 ALTER TABLE `illbatches`
69 ADD CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
74 unless ( foreign_key_exists( 'illbatches', 'illbatches_sfk' ) ) {
77 ALTER TABLE `illbatches`
78 ADD CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
83 # Get any existing NEW batch status
84 my ($new_status) = $dbh->selectrow_array(
86 SELECT name FROM illbatch_statuses WHERE code='NEW';
91 say $out "Bug 30719: NEW ILL batch status found. Update has already been run.";
95 INSERT INTO illbatch_statuses ( name, code, is_system ) VALUES ('New', 'NEW', '1')
98 say $out "Bug 30719: Added NEW ILL batch status";
101 # Get any existing IN_PROGRESS batch status
102 my ($in_progress_status) = $dbh->selectrow_array(
104 SELECT name FROM illbatch_statuses WHERE code='IN_PROGRESS';
108 if ($in_progress_status) {
109 say $out "Bug 30719: IN_PROGRESS ILL batch status found. Update has already been run.";
113 INSERT INTO illbatch_statuses( name, code, is_system ) VALUES( 'In progress', 'IN_PROGRESS', '1' )
116 say $out "Bug 30719: Added IN_PROGRESS ILL batch status";
119 # Get any existing COMPLETED batch status
120 my ($completed_status) = $dbh->selectrow_array(
122 SELECT name FROM illbatch_statuses WHERE code='COMPLETED';
126 if ($completed_status) {
127 say $out "Bug 30719: COMPLETED ILL batch status found. Update has already been run.";
131 INSERT INTO illbatch_statuses( name, code, is_system ) VALUES( 'Completed', 'COMPLETED', '1' )
134 say $out "Bug 30719: Added COMPLETED ILL batch status";
137 # Get any existing UNKNOWN batch status
138 my ($unknown_status) = $dbh->selectrow_array(
140 SELECT name FROM illbatch_statuses WHERE code='UNKNOWN';
144 if ($unknown_status) {
145 say $out "Bug 30719: UNKNOWN ILL batch status found. Update has already been run.";
149 INSERT INTO illbatch_statuses( name, code, is_system ) VALUES( 'Unknown', 'UNKNOWN', '1' )
152 say $out "Bug 30719: Added UNKNOWN ILL batch status";
155 say $out "Bug 30719: Add ILL batches completed";