4 bug_number => "BUG_NUMBER",
5 description => "Some tables for ERM",
8 my ($dbh, $out) = @$args{qw(dbh out)};
11 INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
12 VALUES ('ERMModule', '0', NULL, 'Enable the E-Resource management module', 'YesNo');
16 INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton)
17 VALUES (28, 'erm', 'Manage electronic resources', 0)
20 unless ( TableExists('erm_agreements') ) {
22 CREATE TABLE `erm_agreements` (
23 `agreement_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
24 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
25 `name` VARCHAR(255) NOT NULL COMMENT 'name of the agreement',
26 `description` LONGTEXT DEFAULT NULL COMMENT 'description of the agreement',
27 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the agreement',
28 `closure_reason` VARCHAR(80) DEFAULT NULL COMMENT 'reason of the closure',
29 `is_perpetual` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'is the agreement perpetual',
30 `renewal_priority` VARCHAR(80) DEFAULT NULL COMMENT 'priority of the renewal',
31 `license_info` VARCHAR(80) DEFAULT NULL COMMENT 'info about the license',
32 CONSTRAINT `erm_agreements_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
33 PRIMARY KEY(`agreement_id`)
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
39 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
41 ('ERM_AGREEMENT_STATUS', 1),
42 ('ERM_AGREEMENT_CLOSURE_REASON', 1),
43 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 1)
46 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
48 ('ERM_AGREEMENT_STATUS', 'active', 'Active'),
49 ('ERM_AGREEMENT_STATUS', 'in_negotiation', 'In negotiation'),
50 ('ERM_AGREEMENT_STATUS', 'closed', 'Closed'),
51 ('ERM_AGREEMENT_CLOSURE_REASON', 'expired', 'Expired'),
52 ('ERM_AGREEMENT_CLOSURE_REASON', 'cancelled', 'Cancelled'),
53 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'for_review', 'For review'),
54 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'renew', 'Renew'),
55 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'cancel', 'Cancel')
58 unless ( TableExists('erm_agreement_periods') ) {
60 CREATE TABLE `erm_agreement_periods` (
61 `agreement_period_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
62 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
63 `started_on` DATE NOT NULL COMMENT 'start of the agreement period',
64 `ended_on` DATE COMMENT 'end of the agreement period',
65 `cancellation_deadline` DATE DEFAULT NULL COMMENT 'Deadline for the cancellation',
66 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this period',
67 CONSTRAINT `erm_agreement_periods_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
68 PRIMARY KEY(`agreement_period_id`)
69 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
73 unless ( TableExists('erm_agreement_user_roles') ) {
75 CREATE TABLE `erm_agreement_user_roles` (
76 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
77 `user_id` INT(11) NOT NULL COMMENT 'link to the user',
78 `role` VARCHAR(80) NOT NULL COMMENT 'role of the user',
79 CONSTRAINT `erm_agreement_users_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
80 CONSTRAINT `erm_agreement_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
85 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
87 ('ERM_AGREEMENT_USER_ROLES', 1)
90 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
92 ('ERM_AGREEMENT_USER_ROLES', 'librarian', 'ERM librarian'),
93 ('ERM_AGREEMENT_USER_ROLES', 'subject_specialist', 'Subject specialist')
96 unless ( TableExists('erm_licenses') ) {
98 CREATE TABLE `erm_licenses` (
99 `license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
100 `name` VARCHAR(255) NOT NULL COMMENT 'name of the license',
101 `description` LONGTEXT DEFAULT NULL COMMENT 'description of the license',
102 `type` VARCHAR(80) NOT NULL COMMENT 'type of the license',
103 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
104 `started_on` DATE COMMENT 'start of the license',
105 `ended_on` DATE COMMENT 'end of the license',
106 PRIMARY KEY(`license_id`)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
110 unless ( TableExists('erm_agreement_licenses') ) {
112 CREATE TABLE `erm_agreement_licenses` (
113 `agreement_license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
114 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
115 `license_id` INT(11) NOT NULL COMMENT 'link to the license',
116 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
117 `physical_location` VARCHAR(80) DEFAULT NULL COMMENT 'physical location of the license',
118 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this license',
119 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license',
120 CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
121 CONSTRAINT `erm_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
122 PRIMARY KEY(`agreement_license_id`)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
127 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
129 ('ERM_LICENSE_TYPE', 1),
130 ('ERM_LICENSE_STATUS', 1),
131 ('ERM_AGREEMENT_LICENSE_STATUS', 1),
132 ('ERM_AGREEMENT_LICENSE_LOCATION', 1);
136 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
138 ('ERM_LICENSE_TYPE', 'local', 'Local'),
139 ('ERM_LICENSE_TYPE', 'consortial', 'Consortial'),
140 ('ERM_LICENSE_TYPE', 'national', 'National'),
141 ('ERM_LICENSE_TYPE', 'alliance', 'Alliance'),
142 ('ERM_LICENSE_STATUS', 'in_negotiation', 'In negotiation'),
143 ('ERM_LICENSE_STATUS', 'not_yet_active', 'Not yet active'),
144 ('ERM_LICENSE_STATUS', 'active', 'Active'),
145 ('ERM_LICENSE_STATUS', 'rejected', 'Rejected'),
146 ('ERM_LICENSE_STATUS', 'expired', 'Expired'),
147 ('ERM_AGREEMENT_LICENSE_STATUS', 'controlling', 'Controlling'),
148 ('ERM_AGREEMENT_LICENSE_STATUS', 'future', 'Future'),
149 ('ERM_AGREEMENT_LICENSE_STATUS', 'history', 'Historic'),
150 ('ERM_AGREEMENT_LICENSE_LOCATION', 'filing_cabinet', 'Filing cabinet'),
151 ('ERM_AGREEMENT_LICENSE_LOCATION', 'cupboard', 'Cupboard');
154 unless ( TableExists('erm_agreement_relationships') ) {
156 CREATE TABLE `erm_agreement_relationships` (
157 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
158 `related_agreement_id` INT(11) NOT NULL COMMENT 'link to the related agreement',
159 `relationship` ENUM('supersedes', 'is-superseded-by', 'provides_post-cancellation_access_for', 'has-post-cancellation-access-in', 'tracks_demand-driven_acquisitions_for', 'has-demand-driven-acquisitions-in', 'has_backfile_in', 'has_frontfile_in', 'related_to') NOT NULL COMMENT 'relationship between the two agreements',
160 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
161 CONSTRAINT `erm_agreement_relationships_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
162 CONSTRAINT `erm_agreement_relationships_ibfk_2` FOREIGN KEY (`related_agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
163 PRIMARY KEY(`agreement_id`, `related_agreement_id`)
164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
168 unless ( TableExists('erm_agreement_documents') ) {
170 CREATE TABLE `erm_agreement_documents` (
171 `document_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
172 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
173 `file_name` varchar(255) DEFAULT NULL COMMENT 'name of the file',
174 `file_type` varchar(255) DEFAULT NULL COMMENT 'type of the file',
175 `file_description` varchar(255) DEFAULT NULL COMMENT 'description of the file',
176 `file_content` longblob DEFAULT NULL COMMENT 'the content of the file',
177 `uploaded_on` datetime DEFAULT NULL COMMENT 'datetime when the file as attached',
178 `physical_location` VARCHAR(255) DEFAULT NULL COMMENT 'physical location of the document',
179 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the document',
180 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
181 CONSTRAINT `erm_agreement_documents_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
182 PRIMARY KEY(`document_id`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;