5 description => "Creating the tables for ERM Usage Statistics",
8 my ($dbh, $out) = @$args{qw(dbh out)};
10 unless( TableExists( 'erm_usage_data_providers')) {
13 CREATE TABLE `erm_usage_data_providers` (
14 `erm_usage_data_provider_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
15 `name` varchar(80) NOT NULL COMMENT 'name of the data provider',
16 `description` longtext DEFAULT NULL COMMENT 'description of the data provider',
17 `active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'current status of the harvester - active/inactive',
18 `method` varchar(80) DEFAULT NULL COMMENT 'method of the harvester',
19 `aggregator` varchar(80) DEFAULT NULL COMMENT 'aggregator of the harvester',
20 `service_type` varchar(80) DEFAULT NULL COMMENT 'service_type of the harvester',
21 `service_url` varchar(80) DEFAULT NULL COMMENT 'service_url of the harvester',
22 `report_release` varchar(80) DEFAULT NULL COMMENT 'report_release of the harvester',
23 `customer_id` varchar(50) DEFAULT NULL COMMENT 'sushi customer id',
24 `requestor_id` varchar(50) DEFAULT NULL COMMENT 'sushi requestor id',
25 `api_key` varchar(80) DEFAULT NULL COMMENT 'sushi api key',
26 `requestor_name` varchar(80) DEFAULT NULL COMMENT 'requestor name',
27 `requestor_email` varchar(80) DEFAULT NULL COMMENT 'requestor email',
28 `report_types` varchar(255) DEFAULT NULL COMMENT 'report types provided by the harvester',
29 PRIMARY KEY (`erm_usage_data_provider_id`)
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
34 say $out "Added new table erm_usage_data_providers";
36 say $out "erm_usage_data_providers table already exists - skipping to next table";
39 unless( TableExists( 'erm_counter_files')) {
42 CREATE TABLE `erm_counter_files` (
43 `erm_counter_files_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
44 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'foreign key to erm_usage_data_providers',
45 `type` varchar(80) DEFAULT NULL COMMENT 'type of counter file',
46 `filename` varchar(80) DEFAULT NULL COMMENT 'name of the counter file',
47 `file_content` longblob DEFAULT NULL COMMENT 'content of the counter file',
48 `date_uploaded` timestamp DEFAULT NULL DEFAULT current_timestamp() COMMENT 'counter file upload date',
49 PRIMARY KEY (`erm_counter_files_id`),
50 CONSTRAINT `erm_counter_files_ibfk_1` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE
51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
55 say $out "Added new table erm_counter_files";
57 say $out "erm_counter_files table already exists - skipping to next table";
60 unless( TableExists( 'erm_counter_logs')) {
63 CREATE TABLE `erm_counter_logs` (
64 `erm_counter_log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
65 `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key to borrowers',
66 `counter_files_id` int(11) DEFAULT NULL COMMENT 'foreign key to erm_counter_files',
67 `importdate` timestamp DEFAULT NULL DEFAULT current_timestamp() COMMENT 'counter file import date',
68 `filename` varchar(80) DEFAULT NULL COMMENT 'name of the counter file',
69 `logdetails` longtext DEFAULT NULL COMMENT 'details from the counter log',
70 PRIMARY KEY (`erm_counter_log_id`),
71 CONSTRAINT `erm_counter_log_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
72 CONSTRAINT `erm_counter_log_ibfk_2` FOREIGN KEY (`counter_files_id`) REFERENCES `erm_counter_files` (`erm_counter_files_id`) ON DELETE CASCADE ON UPDATE CASCADE
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
77 say $out "Added new table erm_counter_logs";
79 say $out "erm_counter_logs table already exists - skipping to next table";
82 unless( TableExists( 'erm_usage_titles')) {
85 CREATE TABLE `erm_usage_titles` (
86 `title_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
87 `title` varchar(255) DEFAULT NULL COMMENT 'item title',
88 `usage_data_provider_id` int(11) NOT NULL COMMENT 'platform the title is harvested by',
89 `title_doi` varchar(24) DEFAULT NULL COMMENT 'DOI number for the title',
90 `print_issn` varchar(24) DEFAULT NULL COMMENT 'Print ISSN number for the title',
91 `online_issn` varchar(24) DEFAULT NULL COMMENT 'Online ISSN number for the title',
92 `title_uri` varchar(24) DEFAULT NULL COMMENT 'URI number for the title',
93 `publisher` varchar(255) DEFAULT NULL COMMENT 'Publisher for the title',
94 `publisher_id` varchar(24) DEFAULT NULL COMMENT 'Publisher ID for the title',
95 `yop` varchar(24) DEFAULT NULL COMMENT 'year of publication of the title',
96 `isbn` varchar(24) DEFAULT NULL COMMENT 'ISBN of the title',
97 PRIMARY KEY (`title_id`),
98 CONSTRAINT `erm_usage_titles_ibfk_1` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
103 say $out "Added new table erm_usage_titles";
105 say $out "erm_usage_titles table already exists - skipping to next table";
108 unless ( TableExists('erm_usage_platforms') ) {
111 CREATE TABLE `erm_usage_platforms` (
112 `platform_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
113 `platform` varchar(255) DEFAULT NULL COMMENT 'item title',
114 `usage_data_provider_id` int(11) NOT NULL COMMENT 'data provider the platform is harvested by',
115 PRIMARY KEY (`platform_id`),
116 CONSTRAINT `erm_usage_platforms_ibfk_1` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
121 say $out "Added new table erm_usage_platforms";
123 say $out "erm_usage_platforms table already exists - skipping to next table";
126 unless ( TableExists('erm_usage_databases') ) {
129 CREATE TABLE `erm_usage_databases` (
130 `database_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
131 `database` varchar(255) DEFAULT NULL COMMENT 'item title',
132 `platform` varchar(255) DEFAULT NULL COMMENT 'database platform',
133 `publisher` varchar(255) DEFAULT NULL COMMENT 'Publisher for the database',
134 `publisher_id` varchar(24) DEFAULT NULL COMMENT 'Publisher ID for the database',
135 `usage_data_provider_id` int(11) NOT NULL COMMENT 'data provider the database is harvested by',
136 PRIMARY KEY (`database_id`),
137 CONSTRAINT `erm_usage_databases_ibfk_1` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE
138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
142 say $out "Added new table erm_usage_databases";
144 say $out "erm_usage_databases table already exists - skipping to next table";
147 unless ( TableExists('erm_usage_items') ) {
150 CREATE TABLE `erm_usage_items` (
151 `item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
152 `item` varchar(500) DEFAULT NULL COMMENT 'item title',
153 `platform` varchar(255) DEFAULT NULL COMMENT 'item platform',
154 `publisher` varchar(255) DEFAULT NULL COMMENT 'Publisher for the item',
155 `usage_data_provider_id` int(11) NOT NULL COMMENT 'data provider the database is harvested by',
156 PRIMARY KEY (`item_id`),
157 CONSTRAINT `erm_usage_items_ibfk_1` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE
158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
162 say $out "Added new table erm_usage_items";
164 say $out "erm_usage_items table already exists - skipping to next table";
167 unless( TableExists( 'erm_usage_mus')) {
170 CREATE TABLE `erm_usage_mus` (
171 `monthly_usage_summary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
172 `title_id` int(11) DEFAULT NULL COMMENT 'item title id number',
173 `platform_id` int(11) DEFAULT NULL COMMENT 'platform id number',
174 `database_id` int(11) DEFAULT NULL COMMENT 'database id number',
175 `item_id` int(11) DEFAULT NULL COMMENT 'item id number',
176 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'item title id number',
177 `year` int(4) DEFAULT NULL COMMENT 'year of usage statistics',
178 `month` int(2) DEFAULT NULL COMMENT 'month of usage statistics',
179 `usage_count` int(11) DEFAULT NULL COMMENT 'usage count for the title',
180 `metric_type` varchar(50) DEFAULT NULL COMMENT 'metric type for the usage statistic',
181 `access_type` varchar(50) DEFAULT NULL COMMENT 'access type for the usage statistic',
182 `report_type` varchar(50) DEFAULT NULL COMMENT 'report type for the usage statistic',
183 PRIMARY KEY (`monthly_usage_summary_id`),
184 CONSTRAINT `erm_usage_mus_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_usage_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
185 CONSTRAINT `erm_usage_mus_ibfk_2` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE,
186 CONSTRAINT `erm_usage_mus_ibfk_3` FOREIGN KEY (`platform_id`) REFERENCES `erm_usage_platforms` (`platform_id`) ON DELETE CASCADE ON UPDATE CASCADE,
187 CONSTRAINT `erm_usage_mus_ibfk_4` FOREIGN KEY (`database_id`) REFERENCES `erm_usage_databases` (`database_id`) ON DELETE CASCADE ON UPDATE CASCADE,
188 CONSTRAINT `erm_usage_mus_ibfk_5` FOREIGN KEY (`item_id`) REFERENCES `erm_usage_items` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
193 say $out "Added new table erm_usage_mus";
195 say $out "erm_usage_mus table already exists - skipping to next table";
198 unless( TableExists( 'erm_usage_yus')) {
201 CREATE TABLE `erm_usage_yus` (
202 `yearly_usage_summary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
203 `title_id` int(11) DEFAULT NULL COMMENT 'item title id number',
204 `platform_id` int(11) DEFAULT NULL COMMENT 'platform id number',
205 `database_id` int(11) DEFAULT NULL COMMENT 'database id number',
206 `item_id` int(11) DEFAULT NULL COMMENT 'item id number',
207 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'item title id number',
208 `year` int(4) DEFAULT NULL COMMENT 'year of usage statistics',
209 `totalcount` int(11) DEFAULT NULL COMMENT 'usage count for the title',
210 `metric_type` varchar(50) DEFAULT NULL COMMENT 'metric type for the usage statistic',
211 `access_type` varchar(50) DEFAULT NULL COMMENT 'access type for the usage statistic',
212 `report_type` varchar(50) DEFAULT NULL COMMENT 'report type for the usage statistic',
213 PRIMARY KEY (`yearly_usage_summary_id`),
214 CONSTRAINT `erm_usage_yus_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_usage_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
215 CONSTRAINT `erm_usage_yus_ibfk_2` FOREIGN KEY (`usage_data_provider_id`) REFERENCES `erm_usage_data_providers` (`erm_usage_data_provider_id`) ON DELETE CASCADE ON UPDATE CASCADE,
216 CONSTRAINT `erm_usage_yus_ibfk_3` FOREIGN KEY (`platform_id`) REFERENCES `erm_usage_platforms` (`platform_id`) ON DELETE CASCADE ON UPDATE CASCADE,
217 CONSTRAINT `erm_usage_yus_ibfk_4` FOREIGN KEY (`database_id`) REFERENCES `erm_usage_databases` (`database_id`) ON DELETE CASCADE ON UPDATE CASCADE,
218 CONSTRAINT `erm_usage_yus_ibfk_5` FOREIGN KEY (`item_id`) REFERENCES `erm_usage_items` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
223 say $out "Added new table erm_usage_yus";
225 say $out "erm_usage_yus table already exists - skipping to next table";
228 unless( TableExists( 'erm_default_usage_reports')) {
231 CREATE TABLE `erm_default_usage_reports` (
232 `erm_default_usage_report_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
233 `report_name` varchar(50) DEFAULT NULL COMMENT 'name of the default report',
234 `report_url_params` longtext DEFAULT NULL COMMENT 'url params for the default report',
235 PRIMARY KEY (`erm_default_usage_report_id`)
236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
240 say $out "Added new table erm_default_usage_reports";
242 say $out "erm_default_usage_reports table already exists - skipping to next table";
246 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
248 ('ERM_REPORT_TYPES', 1),
249 ('ERM_PLATFORM_REPORTS_METRICS', 1),
250 ('ERM_DATABASE_REPORTS_METRICS', 1),
251 ('ERM_TITLE_REPORTS_METRICS', 1),
252 ('ERM_ITEM_REPORTS_METRICS', 1);
255 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
257 ('ERM_REPORT_TYPES', 'PR', 'PR - Platform master report'),
258 ('ERM_REPORT_TYPES', 'PR_P1', 'PR_P1 - Platform usage'),
259 ('ERM_REPORT_TYPES', 'DR', 'DR - Database master report'),
260 ('ERM_REPORT_TYPES', 'DR_D1', 'DR_D1 - Database search and item usage'),
261 ('ERM_REPORT_TYPES', 'DR_D2', 'DR_D2 - Database access denied'),
262 ('ERM_REPORT_TYPES', 'TR', 'TR - Title master report'),
263 ('ERM_REPORT_TYPES', 'TR_B1', 'TR_B1 - Book requests (excluding OA_Gold)'),
264 ('ERM_REPORT_TYPES', 'TR_B2', 'TR_B2 - Book access denied'),
265 ('ERM_REPORT_TYPES', 'TR_B3', 'TR_B3 - Book usage by access type'),
266 ('ERM_REPORT_TYPES', 'TR_J1', 'TR_J1 - Journal requests (excluding OA_Gold)'),
267 ('ERM_REPORT_TYPES', 'TR_J2', 'TR_J2 - Journal access denied'),
268 ('ERM_REPORT_TYPES', 'TR_J3', 'TR_J3 - Journal usage by access type'),
269 ('ERM_REPORT_TYPES', 'TR_J4', 'TR_J4 - Journal requests by YOP(excluding OA_Gold)'),
270 ('ERM_REPORT_TYPES', 'IR', 'IR - Item master report'),
271 ('ERM_REPORT_TYPES', 'IR_A1', 'IR_A1 - Journal article requests'),
272 ('ERM_REPORT_TYPES', 'IR_M1', 'IR_M1 - Multimedia item requests'),
273 ('ERM_PLATFORM_REPORTS_METRICS', 'Searches_Platform', 'Searches platform'),
274 ('ERM_PLATFORM_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
275 ('ERM_PLATFORM_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
276 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
277 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
278 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
279 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
280 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Automated', 'Searches automated'),
281 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Federated', 'Searches federated'),
282 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Regular', 'Searches regular'),
283 ('ERM_DATABASE_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
284 ('ERM_DATABASE_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
285 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
286 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
287 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
288 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
289 ('ERM_DATABASE_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
290 ('ERM_DATABASE_REPORTS_METRICS', 'No_License', 'No license'),
291 ('ERM_TITLE_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
292 ('ERM_TITLE_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
293 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
294 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
295 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
296 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
297 ('ERM_TITLE_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
298 ('ERM_TITLE_REPORTS_METRICS', 'No_License', 'No license'),
299 ('ERM_ITEM_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
300 ('ERM_ITEM_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
301 ('ERM_ITEM_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
302 ('ERM_ITEM_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
303 ('ERM_ITEM_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
304 ('ERM_ITEM_REPORTS_METRICS', 'No_License', 'No license');