|
Lines 10-23
return {
Link Here
|
| 10 |
unless ( TableExists('illbatch_statuses') ) { |
10 |
unless ( TableExists('illbatch_statuses') ) { |
| 11 |
$dbh->do( |
11 |
$dbh->do( |
| 12 |
q{ |
12 |
q{ |
| 13 |
CREATE TABLE`illbatch_statuses` ( |
13 |
CREATE TABLE `illbatch_statuses` ( |
| 14 |
`id` int(11) NOT NULL auto_increment COMMENT "Status ID", |
14 |
`id` int(11) NOT NULL auto_increment COMMENT "Status ID", |
| 15 |
`name` varchar(100) NOT NULL COMMENT "Name of status", |
15 |
`name` varchar(100) NOT NULL COMMENT "Name of status", |
| 16 |
`code` varchar(20) NOT NULL COMMENT "Unique, immutable code for status", |
16 |
`code` varchar(20) NOT NULL COMMENT "Unique, immutable code for status", |
| 17 |
`is_system` tinyint(1) COMMENT "Is this status required for system operation", |
17 |
`is_system` tinyint(1) COMMENT "Is this status required for system operation", |
| 18 |
PRIMARY KEY (`id`), |
18 |
PRIMARY KEY(`id`), |
| 19 |
UNIQUE KEY `u_illbatchstatuses__code` (`code`) |
19 |
UNIQUE KEY `u_illbatchstatuses__code` (`code`) |
| 20 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
20 |
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; |
| 21 |
} |
21 |
} |
| 22 |
); |
22 |
); |
| 23 |
|
23 |
|
|
Lines 28-45
return {
Link Here
|
| 28 |
$dbh->do( |
28 |
$dbh->do( |
| 29 |
q{ |
29 |
q{ |
| 30 |
CREATE TABLE `illbatches` ( |
30 |
CREATE TABLE `illbatches` ( |
| 31 |
`id` int(11) NOT NULL auto_increment COMMENT "Batch ID", |
31 |
`ill_batch_id` int(11) NOT NULL auto_increment COMMENT "Batch ID", |
| 32 |
`name` varchar(100) NOT NULL COMMENT "Unique name of batch", |
32 |
`name` varchar(100) NOT NULL COMMENT "Unique name of batch", |
| 33 |
`backend` varchar(20) NOT NULL COMMENT "Name of batch backend", |
33 |
`backend` varchar(20) NOT NULL COMMENT "Name of batch backend", |
| 34 |
`borrowernumber` int(11) COMMENT "Patron associated with batch", |
34 |
`patron_id` int(11) NULL DEFAULT NULL COMMENT "Patron associated with batch", |
| 35 |
`branchcode` varchar(50) COMMENT "Branch associated with batch", |
35 |
`library_id` varchar(50) NULL DEFAULT NULL COMMENT "Branch associated with batch", |
| 36 |
`statuscode` varchar(20) COMMENT "Status of batch", |
36 |
`status_code` varchar(20) NULL DEFAULT NULL COMMENT "Status of batch", |
| 37 |
PRIMARY KEY (`id`), |
37 |
PRIMARY KEY (`ill_batch_id`), |
| 38 |
UNIQUE KEY `u_illbatches__name` (`name`), |
38 |
UNIQUE KEY `u_illbatches__name` (`name`), |
| 39 |
CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
39 |
CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 40 |
CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, |
40 |
CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 41 |
CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE |
41 |
CONSTRAINT `illbatches_sfk` FOREIGN KEY (`status_code`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE |
| 42 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
42 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
| 43 |
} |
43 |
} |
| 44 |
); |
44 |
); |
| 45 |
|
45 |
|
|
Lines 49-55
return {
Link Here
|
| 49 |
$dbh->do( |
49 |
$dbh->do( |
| 50 |
q{ |
50 |
q{ |
| 51 |
ALTER TABLE `illrequests` |
51 |
ALTER TABLE `illrequests` |
| 52 |
ADD COLUMN `batch_id` int(11) AFTER backend -- Optional ID of batch that this request belongs to |
52 |
ADD COLUMN `batch_id` int(11) COMMENT 'Optional ID of batch that this request belongs to' AFTER backend |
| 53 |
} |
53 |
} |
| 54 |
); |
54 |
); |
| 55 |
|
55 |
|
|
Lines 60-93
return {
Link Here
|
| 60 |
$dbh->do( |
60 |
$dbh->do( |
| 61 |
q{ |
61 |
q{ |
| 62 |
ALTER TABLE `illrequests` |
62 |
ALTER TABLE `illrequests` |
| 63 |
ADD CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE |
63 |
ADD CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`ill_batch_id`) ON DELETE SET NULL ON UPDATE CASCADE |
| 64 |
} |
|
|
| 65 |
); |
| 66 |
} |
| 67 |
|
| 68 |
unless ( foreign_key_exists( 'illbatches', 'illbatches_bnfk' ) ) { |
| 69 |
$dbh->do( |
| 70 |
q{ |
| 71 |
ALTER TABLE `illbatches` |
| 72 |
ADD CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE |
| 73 |
} |
| 74 |
); |
| 75 |
} |
| 76 |
|
| 77 |
unless ( foreign_key_exists( 'illbatches', 'illbatches_bcfk' ) ) { |
| 78 |
$dbh->do( |
| 79 |
q{ |
| 80 |
ALTER TABLE `illbatches` |
| 81 |
ADD CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE |
| 82 |
} |
| 83 |
); |
| 84 |
} |
| 85 |
|
| 86 |
unless ( foreign_key_exists( 'illbatches', 'illbatches_sfk' ) ) { |
| 87 |
$dbh->do( |
| 88 |
q{ |
| 89 |
ALTER TABLE `illbatches` |
| 90 |
ADD CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE |
| 91 |
} |
64 |
} |
| 92 |
); |
65 |
); |
| 93 |
} |
66 |
} |