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 |
} |