5 description => "Add an ERM module",
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');
15 say $out "Added new system preference 'ERMModule'";
18 INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton)
19 VALUES (28, 'erm', 'Manage electronic resources', 0)
22 say $out "Added new permission 'erm'";
24 unless ( TableExists('erm_agreements') ) {
26 CREATE TABLE `erm_agreements` (
27 `agreement_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
28 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
29 `name` VARCHAR(255) NOT NULL COMMENT 'name of the agreement',
30 `description` LONGTEXT DEFAULT NULL COMMENT 'description of the agreement',
31 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the agreement',
32 `closure_reason` VARCHAR(80) DEFAULT NULL COMMENT 'reason of the closure',
33 `is_perpetual` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'is the agreement perpetual',
34 `renewal_priority` VARCHAR(80) DEFAULT NULL COMMENT 'priority of the renewal',
35 `license_info` VARCHAR(80) DEFAULT NULL COMMENT 'info about the license',
36 CONSTRAINT `erm_agreements_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
37 PRIMARY KEY(`agreement_id`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
41 say $out "Added new table 'erm_agreements'";
45 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
47 ('ERM_AGREEMENT_STATUS', 1),
48 ('ERM_AGREEMENT_CLOSURE_REASON', 1),
49 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 1)
52 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
54 ('ERM_AGREEMENT_STATUS', 'active', 'Active'),
55 ('ERM_AGREEMENT_STATUS', 'in_negotiation', 'In negotiation'),
56 ('ERM_AGREEMENT_STATUS', 'closed', 'Closed'),
57 ('ERM_AGREEMENT_CLOSURE_REASON', 'expired', 'Expired'),
58 ('ERM_AGREEMENT_CLOSURE_REASON', 'cancelled', 'Cancelled'),
59 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'for_review', 'For review'),
60 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'renew', 'Renew'),
61 ('ERM_AGREEMENT_RENEWAL_PRIORITY', 'cancel', 'Cancel')
64 unless ( TableExists('erm_agreement_periods') ) {
66 CREATE TABLE `erm_agreement_periods` (
67 `agreement_period_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
68 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
69 `started_on` DATE NOT NULL COMMENT 'start of the agreement period',
70 `ended_on` DATE COMMENT 'end of the agreement period',
71 `cancellation_deadline` DATE DEFAULT NULL COMMENT 'Deadline for the cancellation',
72 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this period',
73 CONSTRAINT `erm_agreement_periods_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
74 PRIMARY KEY(`agreement_period_id`)
75 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
78 say $out "Added new table 'erm_agreement_periods'";
80 unless ( TableExists('erm_licenses') ) {
82 CREATE TABLE `erm_licenses` (
83 `license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
84 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
85 `name` VARCHAR(255) NOT NULL COMMENT 'name of the license',
86 `description` LONGTEXT DEFAULT NULL COMMENT 'description of the license',
87 `type` VARCHAR(80) NOT NULL COMMENT 'type of the license',
88 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
89 `started_on` DATE COMMENT 'start of the license',
90 `ended_on` DATE COMMENT 'end of the license',
91 CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
92 PRIMARY KEY(`license_id`)
93 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
96 say $out "Added new table 'erm_licenses'";
98 unless ( TableExists('erm_agreement_licenses') ) {
100 CREATE TABLE `erm_agreement_licenses` (
101 `agreement_license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
102 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
103 `license_id` INT(11) NOT NULL COMMENT 'link to the license',
104 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
105 `physical_location` VARCHAR(80) DEFAULT NULL COMMENT 'physical location of the license',
106 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this license',
107 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license',
108 CONSTRAINT `erm_agreement_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
109 CONSTRAINT `erm_agreement_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
110 PRIMARY KEY(`agreement_license_id`),
111 UNIQUE KEY `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`)
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
115 say $out "Added new table 'erm_agreement_licenses'";
118 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
120 ('ERM_LICENSE_TYPE', 1),
121 ('ERM_LICENSE_STATUS', 1),
122 ('ERM_AGREEMENT_LICENSE_STATUS', 1),
123 ('ERM_AGREEMENT_LICENSE_LOCATION', 1);
127 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
129 ('ERM_LICENSE_TYPE', 'local', 'Local'),
130 ('ERM_LICENSE_TYPE', 'consortial', 'Consortial'),
131 ('ERM_LICENSE_TYPE', 'national', 'National'),
132 ('ERM_LICENSE_TYPE', 'alliance', 'Alliance'),
133 ('ERM_LICENSE_STATUS', 'in_negotiation', 'In negotiation'),
134 ('ERM_LICENSE_STATUS', 'not_yet_active', 'Not yet active'),
135 ('ERM_LICENSE_STATUS', 'active', 'Active'),
136 ('ERM_LICENSE_STATUS', 'rejected', 'Rejected'),
137 ('ERM_LICENSE_STATUS', 'expired', 'Expired'),
138 ('ERM_AGREEMENT_LICENSE_STATUS', 'controlling', 'Controlling'),
139 ('ERM_AGREEMENT_LICENSE_STATUS', 'future', 'Future'),
140 ('ERM_AGREEMENT_LICENSE_STATUS', 'history', 'Historic'),
141 ('ERM_AGREEMENT_LICENSE_LOCATION', 'filing_cabinet', 'Filing cabinet'),
142 ('ERM_AGREEMENT_LICENSE_LOCATION', 'cupboard', 'Cupboard');
145 unless ( TableExists('erm_user_roles') ) {
147 CREATE TABLE `erm_user_roles` (
148 `agreement_id` INT(11) NULL COMMENT 'link to the agreement',
149 `license_id` INT(11) NULL COMMENT 'link to the license',
150 `user_id` INT(11) NOT NULL COMMENT 'link to the user',
151 `role` VARCHAR(80) NOT NULL COMMENT 'role of the user',
152 CONSTRAINT `erm_user_roles_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
153 CONSTRAINT `erm_user_roles_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
154 CONSTRAINT `erm_user_roles_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
158 say $out "Added new table 'erm_user_roles'";
161 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
163 ('ERM_USER_ROLES', 1)
166 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
168 ('ERM_USER_ROLES', 'librarian', 'ERM librarian'),
169 ('ERM_USER_ROLES', 'subject_specialist', 'Subject specialist')
172 unless ( TableExists('erm_agreement_relationships') ) {
174 CREATE TABLE `erm_agreement_relationships` (
175 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
176 `related_agreement_id` INT(11) NOT NULL COMMENT 'link to the related agreement',
177 `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',
178 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
179 CONSTRAINT `erm_agreement_relationships_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
180 CONSTRAINT `erm_agreement_relationships_ibfk_2` FOREIGN KEY (`related_agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
181 PRIMARY KEY(`agreement_id`, `related_agreement_id`)
182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
185 say $out "Added new table 'erm_agreement_relationships'";
188 unless ( TableExists('erm_documents') ) {
190 CREATE TABLE `erm_documents` (
191 `document_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
192 `agreement_id` INT(11) NULL COMMENT 'link to the agreement',
193 `license_id` INT(11) NULL COMMENT 'link to the agreement',
194 `file_name` varchar(255) DEFAULT NULL COMMENT 'name of the file',
195 `file_type` varchar(255) DEFAULT NULL COMMENT 'type of the file',
196 `file_description` varchar(255) DEFAULT NULL COMMENT 'description of the file',
197 `file_content` longblob DEFAULT NULL COMMENT 'the content of the file',
198 `uploaded_on` datetime DEFAULT NULL COMMENT 'datetime when the file as attached',
199 `physical_location` VARCHAR(255) DEFAULT NULL COMMENT 'physical location of the document',
200 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the document',
201 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
202 CONSTRAINT `erm_documents_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
203 CONSTRAINT `erm_documents_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
204 PRIMARY KEY(`document_id`)
205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
208 say $out "Added new table 'erm_documents'";
211 unless ( TableExists('erm_eholdings_packages') ) {
213 CREATE TABLE `erm_eholdings_packages` (
214 `package_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
215 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
216 `name` VARCHAR(255) NOT NULL COMMENT 'name of the package',
217 `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key',
218 `provider` ENUM('ebsco') DEFAULT NULL COMMENT 'External provider',
219 `package_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
220 `content_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
221 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this package',
222 `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of creation of the package',
223 CONSTRAINT `erm_packages_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
224 PRIMARY KEY(`package_id`)
225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
228 say $out "Added new table 'erm_eholdings_packages'";
231 unless ( TableExists('erm_eholdings_packages_agreements') ) {
233 CREATE TABLE `erm_eholdings_packages_agreements` (
234 `package_id` INT(11) NOT NULL COMMENT 'link to the package',
235 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
236 UNIQUE KEY `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`),
237 CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
238 CONSTRAINT `erm_eholdings_packages_agreements_ibfk_2` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE
239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
242 say $out "Added new table 'erm_eholdings_packages_agreements'";
245 unless ( TableExists('erm_eholdings_titles') ) {
247 CREATE TABLE `erm_eholdings_titles` (
248 `title_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
249 `biblio_id` INT(11) DEFAULT NULL,
250 `publication_title` VARCHAR(255) DEFAULT NULL,
251 `external_id` VARCHAR(255) DEFAULT NULL,
252 `print_identifier` VARCHAR(255) DEFAULT NULL,
253 `online_identifier` VARCHAR(255) DEFAULT NULL,
254 `date_first_issue_online` VARCHAR(255) DEFAULT NULL,
255 `num_first_vol_online` VARCHAR(255) DEFAULT NULL,
256 `num_first_issue_online` VARCHAR(255) DEFAULT NULL,
257 `date_last_issue_online` VARCHAR(255) DEFAULT NULL,
258 `num_last_vol_online` VARCHAR(255) DEFAULT NULL,
259 `num_last_issue_online` VARCHAR(255) DEFAULT NULL,
260 `title_url` VARCHAR(255) DEFAULT NULL,
261 `first_author` VARCHAR(255) DEFAULT NULL,
262 `embargo_info` VARCHAR(255) DEFAULT NULL,
263 `coverage_depth` VARCHAR(255) DEFAULT NULL,
264 `notes` VARCHAR(255) DEFAULT NULL,
265 `publisher_name` VARCHAR(255) DEFAULT NULL,
266 `publication_type` VARCHAR(80) DEFAULT NULL,
267 `date_monograph_published_print` VARCHAR(255) DEFAULT NULL,
268 `date_monograph_published_online` VARCHAR(255) DEFAULT NULL,
269 `monograph_volume` VARCHAR(255) DEFAULT NULL,
270 `monograph_edition` VARCHAR(255) DEFAULT NULL,
271 `first_editor` VARCHAR(255) DEFAULT NULL,
272 `parent_publication_title_id` VARCHAR(255) DEFAULT NULL,
273 `preceeding_publication_title_id` VARCHAR(255) DEFAULT NULL,
274 `access_type` VARCHAR(255) DEFAULT NULL,
275 CONSTRAINT `erm_eholdings_titles_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
276 PRIMARY KEY(`title_id`)
277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
280 say $out "Added new table 'erm_eholdings_titles'";
282 unless ( TableExists('erm_eholdings_resources') ) {
284 CREATE TABLE `erm_eholdings_resources` (
285 `resource_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
286 `title_id` INT(11) NOT NULL,
287 `package_id` INT(11) NOT NULL,
288 `vendor_id` INT(11) DEFAULT NULL,
291 `proxy` VARCHAR(80) DEFAULT NULL,
292 UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`),
293 CONSTRAINT `erm_eholdings_resources_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_eholdings_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
294 CONSTRAINT `erm_eholdings_resources_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
295 CONSTRAINT `erm_eholdings_resources_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
296 PRIMARY KEY(`resource_id`)
297 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
300 say $out "Added new table 'erm_eholdings_resources'";
303 unless ( column_exists('aqbooksellers', 'external_id') ) {
305 ALTER TABLE `aqbooksellers`
306 ADD COLUMN `external_id` VARCHAR(255) DEFAULT NULL
310 say $out "Added column 'aqbooksellers.external_id'";
314 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
316 ('ERM_PACKAGE_TYPE', 1),
317 ('ERM_PACKAGE_CONTENT_TYPE', 1),
318 ('ERM_TITLE_PUBLICATION_TYPE', 1)
322 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
324 ('ERM_PACKAGE_TYPE', 'local', 'Local'),
325 ('ERM_PACKAGE_TYPE', 'complete', 'Complete'),
326 ('ERM_PACKAGE_CONTENT_TYPE', 'AggregatedFullText', 'Aggregated full'),
327 ('ERM_PACKAGE_CONTENT_TYPE', 'AbstractAndIndex', 'Abstract and index'),
328 ('ERM_PACKAGE_CONTENT_TYPE', 'EBook', 'E-book'),
329 ('ERM_PACKAGE_CONTENT_TYPE', 'MixedContent', 'Mixed content'),
330 ('ERM_PACKAGE_CONTENT_TYPE', 'EJournal', 'E-journal'),
331 ('ERM_PACKAGE_CONTENT_TYPE', 'OnlineReference', 'Online reference'),
332 ('ERM_PACKAGE_CONTENT_TYPE', 'Print', 'Print'),
333 ('ERM_PACKAGE_CONTENT_TYPE', 'StreamingMedia', 'Streaming media'),
334 ('ERM_PACKAGE_CONTENT_TYPE', 'Unknown', 'Unknown'),
335 ('ERM_TITLE_PUBLICATION_TYPE', 'journal', 'Journal'),
336 ('ERM_TITLE_PUBLICATION_TYPE', 'newsletter', 'Newsletter'),
337 ('ERM_TITLE_PUBLICATION_TYPE', 'report', 'Report'),
338 ('ERM_TITLE_PUBLICATION_TYPE', 'proceedings', 'Proceedings'),
339 ('ERM_TITLE_PUBLICATION_TYPE', 'website', 'Website'),
340 ('ERM_TITLE_PUBLICATION_TYPE', 'newspaper', 'Newspaper'),
341 ('ERM_TITLE_PUBLICATION_TYPE', 'unspecified', 'Unspecified'),
342 ('ERM_TITLE_PUBLICATION_TYPE', 'book', 'Book'),
343 ('ERM_TITLE_PUBLICATION_TYPE', 'ebook', 'E-book'),
344 ('ERM_TITLE_PUBLICATION_TYPE', 'bookseries', 'Bookseries'),
345 ('ERM_TITLE_PUBLICATION_TYPE', 'database', 'Database'),
346 ('ERM_TITLE_PUBLICATION_TYPE', 'thesisdissertation', 'Thesis/Dissertation'),
347 ('ERM_TITLE_PUBLICATION_TYPE', 'streamingaudio', 'Streaming audio'),
348 ('ERM_TITLE_PUBLICATION_TYPE', 'streamingvideo', 'Streaming video'),
349 ('ERM_TITLE_PUBLICATION_TYPE', 'audiobook', 'AudioBook');
353 INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
354 VALUES ('ERMProviders', 'local', 'local|ebsco', 'Set the providers for the ERM module', 'multiple');
357 say $out "Added new system preference 'ERMProviders'";
360 INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
362 ('ERMProviderEbscoCustomerID', '', '', 'Customer ID for EBSCO', 'free'),
363 ('ERMProviderEbscoApiKey', '', '', 'API key for EBSCO', 'free');
366 say $out "Added new system preference 'ERMProviderEbscoCustomerID'";
367 say $out "Added new system preference 'ERMProviderEbscoApiKey'";