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 `begin_date` date DEFAULT NULL COMMENT 'start date of the harvester',
24 `end_date` date DEFAULT NULL COMMENT 'end date of the harvester',
25 `customer_id` varchar(50) DEFAULT NULL COMMENT 'sushi customer id',
26 `requestor_id` varchar(50) DEFAULT NULL COMMENT 'sushi requestor id',
27 `api_key` varchar(80) DEFAULT NULL COMMENT 'sushi api key',
28 `requestor_name` varchar(80) DEFAULT NULL COMMENT 'requestor name',
29 `requestor_email` varchar(80) DEFAULT NULL COMMENT 'requestor email',
30 `report_types` varchar(255) DEFAULT NULL COMMENT 'report types provided by the harvester',
31 PRIMARY KEY (`erm_usage_data_provider_id`)
32 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
36 say $out "Added new table erm_usage_data_providers";
38 say $out "erm_usage_data_providers table already exists - skipping to next table";
41 unless( TableExists( 'erm_counter_files')) {
44 CREATE TABLE `erm_counter_files` (
45 `erm_counter_files_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
46 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'foreign key to erm_usage_data_providers',
47 `type` varchar(80) DEFAULT NULL COMMENT 'type of counter file',
48 `filename` varchar(80) DEFAULT NULL COMMENT 'name of the counter file',
49 `file_content` longblob DEFAULT NULL COMMENT 'content of the counter file',
50 `date_uploaded` timestamp DEFAULT NULL DEFAULT current_timestamp() COMMENT 'counter file upload date',
51 PRIMARY KEY (`erm_counter_files_id`),
52 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
53 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
57 say $out "Added new table erm_counter_files";
59 say $out "erm_counter_files table already exists - skipping to next table";
62 unless( TableExists( 'erm_counter_logs')) {
65 CREATE TABLE `erm_counter_logs` (
66 `erm_counter_log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
67 `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key to borrowers',
68 `counter_files_id` int(11) DEFAULT NULL COMMENT 'foreign key to erm_counter_files',
69 `importdate` timestamp DEFAULT NULL DEFAULT current_timestamp() COMMENT 'counter file import date',
70 `filename` varchar(80) DEFAULT NULL COMMENT 'name of the counter file',
71 `logdetails` longtext DEFAULT NULL COMMENT 'details from the counter log',
72 PRIMARY KEY (`erm_counter_log_id`),
73 CONSTRAINT `erm_counter_log_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
74 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
75 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
79 say $out "Added new table erm_counter_logs";
81 say $out "erm_counter_logs table already exists - skipping to next table";
84 unless( TableExists( 'erm_usage_titles')) {
87 CREATE TABLE `erm_usage_titles` (
88 `title_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
89 `title` varchar(255) DEFAULT NULL COMMENT 'item title',
90 `usage_data_provider_id` int(11) NOT NULL COMMENT 'platform the title is harvested by',
91 `title_doi` varchar(24) DEFAULT NULL COMMENT 'DOI number for the title',
92 `print_issn` varchar(24) DEFAULT NULL COMMENT 'Print ISSN number for the title',
93 `online_issn` varchar(24) DEFAULT NULL COMMENT 'Online ISSN number for the title',
94 `title_uri` varchar(24) DEFAULT NULL COMMENT 'URI number for the title',
95 PRIMARY KEY (`title_id`),
96 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
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
101 say $out "Added new table erm_usage_titles";
103 say $out "erm_usage_titles table already exists - skipping to next table";
106 unless( TableExists( 'erm_usage_mus')) {
109 CREATE TABLE `erm_usage_mus` (
110 `monthly_usage_summary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
111 `title_id` int(11) DEFAULT NULL COMMENT 'item title id number',
112 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'item title id number',
113 `year` int(4) DEFAULT NULL COMMENT 'year of usage statistics',
114 `month` int(2) DEFAULT NULL COMMENT 'month of usage statistics',
115 `usage_count` int(11) DEFAULT NULL COMMENT 'usage count for the title',
116 `metric_type` varchar(50) DEFAULT NULL COMMENT 'metric type for the usage statistic',
117 `report_type` varchar(50) DEFAULT NULL COMMENT 'report type for the usage statistic',
118 PRIMARY KEY (`monthly_usage_summary_id`),
119 CONSTRAINT `erm_usage_mus_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_usage_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
120 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
121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
125 say $out "Added new table erm_usage_mus";
127 say $out "erm_usage_mus table already exists - skipping to next table";
130 unless( TableExists( 'erm_usage_yus')) {
133 CREATE TABLE `erm_usage_yus` (
134 `yearly_usage_summary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
135 `title_id` int(11) DEFAULT NULL COMMENT 'item title id number',
136 `usage_data_provider_id` int(11) DEFAULT NULL COMMENT 'item title id number',
137 `year` int(4) DEFAULT NULL COMMENT 'year of usage statistics',
138 `totalcount` int(11) DEFAULT NULL COMMENT 'usage count for the title',
139 `metric_type` varchar(50) DEFAULT NULL COMMENT 'metric type for the usage statistic',
140 `report_type` varchar(50) DEFAULT NULL COMMENT 'report type for the usage statistic',
141 PRIMARY KEY (`yearly_usage_summary_id`),
142 CONSTRAINT `erm_usage_yus_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_usage_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
143 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
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
148 say $out "Added new table erm_usage_yus";
150 say $out "erm_usage_yus table already exists - skipping to next table";
154 INSERT IGNORE INTO authorised_value_categories (category_name, is_system)
156 ('ERM_REPORT_TYPES', 1),
157 ('ERM_PLATFORM_REPORTS_METRICS', 1),
158 ('ERM_DATABASE_REPORTS_METRICS', 1),
159 ('ERM_TITLE_REPORTS_METRICS', 1),
160 ('ERM_ITEM_REPORTS_METRICS', 1);
163 INSERT IGNORE INTO authorised_values (category, authorised_value, lib)
165 ('ERM_REPORT_TYPES', 'PR', 'PR - Platform master report'),
166 ('ERM_REPORT_TYPES', 'PR_P1', 'PR_P1 - Platform usage'),
167 ('ERM_REPORT_TYPES', 'DR', 'DR - Database master report'),
168 ('ERM_REPORT_TYPES', 'DR_D1', 'DR_D1 - Database search and item usage'),
169 ('ERM_REPORT_TYPES', 'DR_D2', 'DR_D2 - Database access denied'),
170 ('ERM_REPORT_TYPES', 'TR', 'TR - Title master report'),
171 ('ERM_REPORT_TYPES', 'TR_B1', 'TR_B1 - Book requests (excluding OA_Gold)'),
172 ('ERM_REPORT_TYPES', 'TR_B2', 'TR_B2 - Book access denied'),
173 ('ERM_REPORT_TYPES', 'TR_B3', 'TR_B3 - Book usage by access type'),
174 ('ERM_REPORT_TYPES', 'TR_J1', 'TR_J1 - Journal requests (excluding OA_Gold)'),
175 ('ERM_REPORT_TYPES', 'TR_J2', 'TR_J2 - Journal access denied'),
176 ('ERM_REPORT_TYPES', 'TR_J3', 'TR_J3 - Journal usage by access type'),
177 ('ERM_REPORT_TYPES', 'TR_J4', 'TR_J4 - Journal requests by YOP(excluding OA_Gold)'),
178 ('ERM_REPORT_TYPES', 'IR', 'IR - Item master report'),
179 ('ERM_REPORT_TYPES', 'IR_A1', 'IR_A1 - Journal article requests'),
180 ('ERM_REPORT_TYPES', 'IR_M1', 'IR_M1 - Multimedia item requests'),
181 ('ERM_PLATFORM_REPORTS_METRICS', 'Searches_Platform', 'Searches platform'),
182 ('ERM_PLATFORM_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
183 ('ERM_PLATFORM_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
184 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
185 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
186 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
187 ('ERM_PLATFORM_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
188 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Automated', 'Searches automated'),
189 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Federated', 'Searches federated'),
190 ('ERM_DATABASE_REPORTS_METRICS', 'Searches_Regular', 'Searches regular'),
191 ('ERM_DATABASE_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
192 ('ERM_DATABASE_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
193 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
194 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
195 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
196 ('ERM_DATABASE_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
197 ('ERM_DATABASE_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
198 ('ERM_DATABASE_REPORTS_METRICS', 'No_License', 'No license'),
199 ('ERM_TITLE_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
200 ('ERM_TITLE_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
201 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
202 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
203 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Title_Investigations', 'Unique title investigations'),
204 ('ERM_TITLE_REPORTS_METRICS', 'Unique_Title_Requests', 'Unique title requests'),
205 ('ERM_TITLE_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
206 ('ERM_TITLE_REPORTS_METRICS', 'No_License', 'No license'),
207 ('ERM_ITEM_REPORTS_METRICS', 'Total_Item_Investigations', 'Total item investigations'),
208 ('ERM_ITEM_REPORTS_METRICS', 'Total_Item_Requests', 'Total item requests'),
209 ('ERM_ITEM_REPORTS_METRICS', 'Unique_Item_Investigations', 'Unique item investigations'),
210 ('ERM_ITEM_REPORTS_METRICS', 'Unique_Item_Requests', 'Unique item requests'),
211 ('ERM_ITEM_REPORTS_METRICS', 'Limit_Exceeded', 'Limit exceeded'),
212 ('ERM_ITEM_REPORTS_METRICS', 'No_License', 'No license');