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;
72 unless ( TableExists('erm_licenses') ) {
74 CREATE TABLE `erm_licenses` (
75 `license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
76 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
77 `name` VARCHAR(255) NOT NULL COMMENT 'name of the license',
78 `description` LONGTEXT DEFAULT NULL COMMENT 'description of the license',
79 `type` VARCHAR(80) NOT NULL COMMENT 'type of the license',
80 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
81 `started_on` DATE COMMENT 'start of the license',
82 `ended_on` DATE COMMENT 'end of the license',
83 CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
84 PRIMARY KEY(`license_id`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
88 unless ( TableExists('erm_agreement_licenses') ) {
90 CREATE TABLE `erm_agreement_licenses` (
91 `agreement_license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
92 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
93 `license_id` INT(11) NOT NULL COMMENT 'link to the license',
94 `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
95 `physical_location` VARCHAR(80) DEFAULT NULL COMMENT 'physical location of the license',
96 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this license',
97 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license',
98 CONSTRAINT `erm_agreement_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
99 CONSTRAINT `erm_agreement_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
100 PRIMARY KEY(`agreement_license_id`),
101 UNIQUE KEY `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`)
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
106 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
108 ('ERM_LICENSE_TYPE', 1),
109 ('ERM_LICENSE_STATUS', 1),
110 ('ERM_AGREEMENT_LICENSE_STATUS', 1),
111 ('ERM_AGREEMENT_LICENSE_LOCATION', 1);
115 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
117 ('ERM_LICENSE_TYPE', 'local', 'Local'),
118 ('ERM_LICENSE_TYPE', 'consortial', 'Consortial'),
119 ('ERM_LICENSE_TYPE', 'national', 'National'),
120 ('ERM_LICENSE_TYPE', 'alliance', 'Alliance'),
121 ('ERM_LICENSE_STATUS', 'in_negotiation', 'In negotiation'),
122 ('ERM_LICENSE_STATUS', 'not_yet_active', 'Not yet active'),
123 ('ERM_LICENSE_STATUS', 'active', 'Active'),
124 ('ERM_LICENSE_STATUS', 'rejected', 'Rejected'),
125 ('ERM_LICENSE_STATUS', 'expired', 'Expired'),
126 ('ERM_AGREEMENT_LICENSE_STATUS', 'controlling', 'Controlling'),
127 ('ERM_AGREEMENT_LICENSE_STATUS', 'future', 'Future'),
128 ('ERM_AGREEMENT_LICENSE_STATUS', 'history', 'Historic'),
129 ('ERM_AGREEMENT_LICENSE_LOCATION', 'filing_cabinet', 'Filing cabinet'),
130 ('ERM_AGREEMENT_LICENSE_LOCATION', 'cupboard', 'Cupboard');
133 unless ( TableExists('erm_user_roles') ) {
135 CREATE TABLE `erm_user_roles` (
136 `agreement_id` INT(11) NULL COMMENT 'link to the agreement',
137 `license_id` INT(11) NULL COMMENT 'link to the license',
138 `user_id` INT(11) NOT NULL COMMENT 'link to the user',
139 `role` VARCHAR(80) NOT NULL COMMENT 'role of the user',
140 CONSTRAINT `erm_user_roles_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
141 CONSTRAINT `erm_user_roles_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
142 CONSTRAINT `erm_user_roles_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
147 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
149 ('ERM_USER_ROLES', 1)
152 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
154 ('ERM_USER_ROLES', 'librarian', 'ERM librarian'),
155 ('ERM_USER_ROLES', 'subject_specialist', 'Subject specialist')
158 unless ( TableExists('erm_agreement_relationships') ) {
160 CREATE TABLE `erm_agreement_relationships` (
161 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
162 `related_agreement_id` INT(11) NOT NULL COMMENT 'link to the related agreement',
163 `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',
164 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
165 CONSTRAINT `erm_agreement_relationships_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
166 CONSTRAINT `erm_agreement_relationships_ibfk_2` FOREIGN KEY (`related_agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
167 PRIMARY KEY(`agreement_id`, `related_agreement_id`)
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
172 unless ( TableExists('erm_documents') ) {
174 CREATE TABLE `erm_documents` (
175 `document_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
176 `agreement_id` INT(11) NULL COMMENT 'link to the agreement',
177 `license_id` INT(11) NULL COMMENT 'link to the agreement',
178 `file_name` varchar(255) DEFAULT NULL COMMENT 'name of the file',
179 `file_type` varchar(255) DEFAULT NULL COMMENT 'type of the file',
180 `file_description` varchar(255) DEFAULT NULL COMMENT 'description of the file',
181 `file_content` longblob DEFAULT NULL COMMENT 'the content of the file',
182 `uploaded_on` datetime DEFAULT NULL COMMENT 'datetime when the file as attached',
183 `physical_location` VARCHAR(255) DEFAULT NULL COMMENT 'physical location of the document',
184 `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the document',
185 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
186 CONSTRAINT `erm_documents_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
187 CONSTRAINT `erm_documents_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
188 PRIMARY KEY(`document_id`)
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
193 unless ( TableExists('erm_eholdings_packages') ) {
195 CREATE TABLE `erm_eholdings_packages` (
196 `package_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
197 `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
198 `name` VARCHAR(255) NOT NULL COMMENT 'name of the package',
199 `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key',
200 `provider` ENUM('ebsco') DEFAULT NULL COMMENT 'External provider',
201 `package_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
202 `content_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
203 `notes` mediumtext DEFAULT NULL COMMENT 'notes about this package',
204 `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of creation of the package',
205 CONSTRAINT `erm_packages_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
206 PRIMARY KEY(`package_id`)
207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
211 unless ( TableExists('erm_eholdings_packages_agreements') ) {
213 CREATE TABLE `erm_eholdings_packages_agreements` (
214 `package_id` INT(11) NOT NULL COMMENT 'link to the package',
215 `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
216 UNIQUE KEY `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`),
217 CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
218 CONSTRAINT `erm_eholdings_packages_agreements_ibfk_2` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE
219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
223 unless ( TableExists('erm_eholdings_titles') ) {
225 CREATE TABLE `erm_eholdings_titles` (
226 `title_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
227 `biblio_id` INT(11) DEFAULT NULL,
228 `publication_title` VARCHAR(255) DEFAULT NULL,
229 `external_id` VARCHAR(255) DEFAULT NULL,
230 `print_identifier` VARCHAR(255) DEFAULT NULL,
231 `online_identifier` VARCHAR(255) DEFAULT NULL,
232 `date_first_issue_online` VARCHAR(255) DEFAULT NULL,
233 `num_first_vol_online` VARCHAR(255) DEFAULT NULL,
234 `num_first_issue_online` VARCHAR(255) DEFAULT NULL,
235 `date_last_issue_online` VARCHAR(255) DEFAULT NULL,
236 `num_last_vol_online` VARCHAR(255) DEFAULT NULL,
237 `num_last_issue_online` VARCHAR(255) DEFAULT NULL,
238 `title_url` VARCHAR(255) DEFAULT NULL,
239 `first_author` VARCHAR(255) DEFAULT NULL,
240 `embargo_info` VARCHAR(255) DEFAULT NULL,
241 `coverage_depth` VARCHAR(255) DEFAULT NULL,
242 `notes` VARCHAR(255) DEFAULT NULL,
243 `publisher_name` VARCHAR(255) DEFAULT NULL,
244 `publication_type` VARCHAR(80) DEFAULT NULL,
245 `date_monograph_published_print` VARCHAR(255) DEFAULT NULL,
246 `date_monograph_published_online` VARCHAR(255) DEFAULT NULL,
247 `monograph_volume` VARCHAR(255) DEFAULT NULL,
248 `monograph_edition` VARCHAR(255) DEFAULT NULL,
249 `first_editor` VARCHAR(255) DEFAULT NULL,
250 `parent_publication_title_id` VARCHAR(255) DEFAULT NULL,
251 `preceeding_publication_title_id` VARCHAR(255) DEFAULT NULL,
252 `access_type` VARCHAR(255) DEFAULT NULL,
253 CONSTRAINT `erm_eholdings_titles_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
254 PRIMARY KEY(`title_id`)
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
258 unless ( TableExists('erm_eholdings_resources') ) {
260 CREATE TABLE `erm_eholdings_resources` (
261 `resource_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
262 `title_id` INT(11) NOT NULL,
263 `package_id` INT(11) NOT NULL,
264 `vendor_id` INT(11) DEFAULT NULL,
267 `proxy` VARCHAR(80) DEFAULT NULL,
268 UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`),
269 CONSTRAINT `erm_eholdings_resources_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_eholdings_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
270 CONSTRAINT `erm_eholdings_resources_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
271 CONSTRAINT `erm_eholdings_resources_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
272 PRIMARY KEY(`resource_id`)
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
277 unless ( column_exists('aqbooksellers', 'external_id') ) {
279 ALTER TABLE `aqbooksellers`
280 ADD COLUMN `external_id` VARCHAR(255) DEFAULT NULL
286 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
288 ('ERM_PACKAGE_TYPE', 1),
289 ('ERM_PACKAGE_CONTENT_TYPE', 1),
290 ('ERM_TITLE_PUBLICATION_TYPE', 1)
294 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
296 ('ERM_PACKAGE_TYPE', 'local', 'Local'),
297 ('ERM_PACKAGE_TYPE', 'complete', 'Complete'),
298 ('ERM_PACKAGE_CONTENT_TYPE', 'AggregatedFullText', 'Aggregated full'),
299 ('ERM_PACKAGE_CONTENT_TYPE', 'AbstractAndIndex', 'Abstract and index'),
300 ('ERM_PACKAGE_CONTENT_TYPE', 'EBook', 'E-book'),
301 ('ERM_PACKAGE_CONTENT_TYPE', 'MixedContent', 'Mixed content'),
302 ('ERM_PACKAGE_CONTENT_TYPE', 'EJournal', 'E-journal'),
303 ('ERM_PACKAGE_CONTENT_TYPE', 'OnlineReference', 'Online reference'),
304 ('ERM_PACKAGE_CONTENT_TYPE', 'Print', 'Print'),
305 ('ERM_PACKAGE_CONTENT_TYPE', 'StreamingMedia', 'Streaming media'),
306 ('ERM_PACKAGE_CONTENT_TYPE', 'Unknown', 'Unknown'),
307 ('ERM_TITLE_PUBLICATION_TYPE', 'journal', 'Journal'),
308 ('ERM_TITLE_PUBLICATION_TYPE', 'newsletter', 'Newsletter'),
309 ('ERM_TITLE_PUBLICATION_TYPE', 'report', 'Report'),
310 ('ERM_TITLE_PUBLICATION_TYPE', 'proceedings', 'Proceedings'),
311 ('ERM_TITLE_PUBLICATION_TYPE', 'website', 'Website'),
312 ('ERM_TITLE_PUBLICATION_TYPE', 'newspaper', 'Newspaper'),
313 ('ERM_TITLE_PUBLICATION_TYPE', 'unspecified', 'Unspecified'),
314 ('ERM_TITLE_PUBLICATION_TYPE', 'book', 'Book'),
315 ('ERM_TITLE_PUBLICATION_TYPE', 'ebook', 'E-book'),
316 ('ERM_TITLE_PUBLICATION_TYPE', 'bookseries', 'Bookseries'),
317 ('ERM_TITLE_PUBLICATION_TYPE', 'database', 'Database'),
318 ('ERM_TITLE_PUBLICATION_TYPE', 'thesisdissertation', 'Thesis/Dissertation'),
319 ('ERM_TITLE_PUBLICATION_TYPE', 'streamingaudio', 'Streaming audio'),
320 ('ERM_TITLE_PUBLICATION_TYPE', 'streamingvideo', 'Streaming video'),
321 ('ERM_TITLE_PUBLICATION_TYPE', 'audiobook', 'AudioBook');
325 INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
326 VALUES ('ERMProviders', 'local', 'local|ebsco', 'Set the providers for the ERM module', 'multiple');
329 INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
331 ('ERMProviderEbscoCustomerID', '', '', 'Customer ID for EBSCO', 'free'),
332 ('ERMProviderEbscoApiKey', '', '', 'API key for EBSCO', 'free');