Revision 2109
Added by Duane Costa over 20 years ago
src/xmltables_postgres.sql | ||
---|---|---|
53 | 53 |
DROP TABLE xml_catalog; |
54 | 54 |
DROP TABLE accession_number; |
55 | 55 |
DROP TABLE access_log; |
56 |
DROP TABLE harvest_site_schedule; |
|
57 |
DROP TABLE harvest_log; |
|
58 |
DROP TABLE harvest_detail_log; |
|
56 | 59 |
|
57 | 60 |
/* |
58 | 61 |
*Replication -- table to store servers that metacat is replicated to |
... | ... | |
282 | 285 |
date_logged TIMESTAMP, -- the datetime on which the event occurred |
283 | 286 |
CONSTRAINT access_log_pk PRIMARY KEY (entryid) |
284 | 287 |
); |
288 |
|
|
289 |
/* |
|
290 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
291 |
*/ |
|
292 |
CREATE TABLE harvest_site_schedule ( |
|
293 |
site_schedule_id INT8, -- unique id |
|
294 |
documentlisturl VARCHAR(255), -- URL of the site harvest document list |
|
295 |
ldapdn VARCHAR(255), -- LDAP distinguished name for site account |
|
296 |
datenextharvest DATE, -- scheduled date of next harvest |
|
297 |
datelastharvest DATE, -- recorded date of last harvest |
|
298 |
updatefrequency INT8, -- the harvest update frequency |
|
299 |
unit VARCHAR(50), -- update unit -- days weeks or months |
|
300 |
contact_email VARCHAR(50), -- email address of the site contact person |
|
301 |
ldappwd VARCHAR(20), -- LDAP password for site account |
|
302 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
303 |
); |
|
304 |
|
|
305 |
/* |
|
306 |
* harvest_log -- table to log entries for harvest operations |
|
307 |
*/ |
|
308 |
CREATE TABLE harvest_log ( |
|
309 |
harvest_log_id INT8, -- unique id |
|
310 |
harvest_date DATE, -- date of the current harvest |
|
311 |
status INT8, -- non-zero indicates an error status |
|
312 |
message VARCHAR(1000), -- text message for this log entry |
|
313 |
harvest_operation_code VARCHAR(30), -- the type of harvest operation |
|
314 |
site_schedule_id INT8, -- foreign key |
|
315 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id), |
|
316 |
CONSTRAINT harvest_log_site_schedule_id_fk |
|
317 |
FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule |
|
318 |
); |
|
319 |
|
|
320 |
/* |
|
321 |
* harvest_detail_log -- table to log detailed info about documents that |
|
322 |
* generated errors during the harvest |
|
323 |
*/ |
|
324 |
CREATE TABLE harvest_detail_log ( |
|
325 |
detail_log_id INT8, -- unique id |
|
326 |
harvest_log_id INT8, -- ponter to the related log entry |
|
327 |
scope VARCHAR(50), -- document scope |
|
328 |
identifier INT8, -- document identifier |
|
329 |
revision INT8, -- document revision |
|
330 |
document_url VARCHAR(255), -- document URL |
|
331 |
error_message VARCHAR(1000), -- text error message |
|
332 |
document_type VARCHAR(100), -- document type |
|
333 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
334 |
CONSTRAINT harvest_detail_log_harvest_log_id_fk |
|
335 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
336 |
); |
|
337 |
|
src/upgrade-db-to-1.4.sql | ||
---|---|---|
44 | 44 |
FROM dual; |
45 | 45 |
END; |
46 | 46 |
/ |
47 |
|
|
48 |
/* |
|
49 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
50 |
*/ |
|
51 |
CREATE TABLE harvest_site_schedule ( |
|
52 |
site_schedule_id NUMBER, -- unique id |
|
53 |
documentlisturl VARCHAR2(255), -- URL of the site harvest document list |
|
54 |
ldapdn VARCHAR2(255), -- LDAP distinguished name for site account |
|
55 |
datenextharvest DATE, -- scheduled date of next harvest |
|
56 |
datelastharvest DATE, -- recorded date of last harvest |
|
57 |
updatefrequency NUMBER, -- the harvest update frequency |
|
58 |
unit VARCHAR2(50), -- update unit -- days weeks or months |
|
59 |
contact_email VARCHAR2(50), -- email address of the site contact person |
|
60 |
ldappwd VARCHAR2(20), -- LDAP password for site account |
|
61 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
62 |
); |
|
63 |
|
|
64 |
/* |
|
65 |
* harvest_log -- table to log entries for harvest operations |
|
66 |
*/ |
|
67 |
CREATE TABLE harvest_log ( |
|
68 |
harvest_log_id NUMBER, -- unique id |
|
69 |
harvest_date DATE, -- date of the current harvest |
|
70 |
status NUMBER, -- non-zero indicates an error status |
|
71 |
message VARCHAR2(1000), -- text message for this log entry |
|
72 |
harvest_operation_code VARCHAR2(30), -- the type of harvest operation |
|
73 |
site_schedule_id NUMBER, -- foreign key |
|
74 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id), |
|
75 |
CONSTRAINT harvest_log_site_schedule_id_fk |
|
76 |
FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule |
|
77 |
); |
|
78 |
|
|
79 |
/* |
|
80 |
* harvest_detail_log -- table to log detailed info about documents that |
|
81 |
* generated errors during the harvest |
|
82 |
*/ |
|
83 |
CREATE TABLE harvest_detail_log ( |
|
84 |
detail_log_id NUMBER, -- unique id |
|
85 |
harvest_log_id NUMBER, -- ponter to the related log entry |
|
86 |
scope VARCHAR2(50), -- document scope |
|
87 |
identifier NUMBER, -- document identifier |
|
88 |
revision NUMBER, -- document revision |
|
89 |
document_url VARCHAR2(255), -- document URL |
|
90 |
error_message VARCHAR2(1000), -- text error message |
|
91 |
document_type VARCHAR2(100), -- document type |
|
92 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
93 |
CONSTRAINT harvest_detail_log_harvest_log_id_fk |
|
94 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
95 |
); |
|
96 |
|
src/xmltables.sql | ||
---|---|---|
57 | 57 |
DROP TABLE xml_catalog; |
58 | 58 |
DROP TABLE accession_number; |
59 | 59 |
DROP TABLE access_log; |
60 |
DROP TABLE harvest_site_schedule; |
|
61 |
DROP TABLE harvest_log; |
|
62 |
DROP TABLE harvest_detail_log; |
|
60 | 63 |
|
61 | 64 |
/* |
62 | 65 |
*Replication -- table to store servers that metacat is replicated to |
... | ... | |
343 | 346 |
INTO :new.entryid |
344 | 347 |
FROM dual; |
345 | 348 |
END; |
349 |
|
|
350 |
/* |
|
351 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
352 |
*/ |
|
353 |
CREATE TABLE harvest_site_schedule ( |
|
354 |
site_schedule_id NUMBER, -- unique id |
|
355 |
documentlisturl VARCHAR2(255), -- URL of the site harvest document list |
|
356 |
ldapdn VARCHAR2(255), -- LDAP distinguished name for site account |
|
357 |
datenextharvest DATE, -- scheduled date of next harvest |
|
358 |
datelastharvest DATE, -- recorded date of last harvest |
|
359 |
updatefrequency NUMBER, -- the harvest update frequency |
|
360 |
unit VARCHAR2(50), -- update unit -- days weeks or months |
|
361 |
contact_email VARCHAR2(50), -- email address of the site contact person |
|
362 |
ldappwd VARCHAR2(20), -- LDAP password for site account |
|
363 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
364 |
); |
|
365 |
|
|
366 |
/* |
|
367 |
* harvest_log -- table to log entries for harvest operations |
|
368 |
*/ |
|
369 |
CREATE TABLE harvest_log ( |
|
370 |
harvest_log_id NUMBER, -- unique id |
|
371 |
harvest_date DATE, -- date of the current harvest |
|
372 |
status NUMBER, -- non-zero indicates an error status |
|
373 |
message VARCHAR2(1000), -- text message for this log entry |
|
374 |
harvest_operation_code VARCHAR2(30), -- the type of harvest operation |
|
375 |
site_schedule_id NUMBER, -- foreign key |
|
376 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id), |
|
377 |
CONSTRAINT harvest_log_site_schedule_id_fk |
|
378 |
FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule |
|
379 |
); |
|
380 |
|
|
381 |
/* |
|
382 |
* harvest_detail_log -- table to log detailed info about documents that |
|
383 |
* generated errors during the harvest |
|
384 |
*/ |
|
385 |
CREATE TABLE harvest_detail_log ( |
|
386 |
detail_log_id NUMBER, -- unique id |
|
387 |
harvest_log_id NUMBER, -- ponter to the related log entry |
|
388 |
scope VARCHAR2(50), -- document scope |
|
389 |
identifier NUMBER, -- document identifier |
|
390 |
revision NUMBER, -- document revision |
|
391 |
document_url VARCHAR2(255), -- document URL |
|
392 |
error_message VARCHAR2(1000), -- text error message |
|
393 |
document_type VARCHAR2(100), -- document type |
|
394 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
395 |
CONSTRAINT harvest_detail_log_harvest_log_id_fk |
|
396 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
397 |
); |
|
398 |
|
src/xmltables-sqlserver.sql | ||
---|---|---|
27 | 27 |
drop table [dbo].[xml_replication]; |
28 | 28 |
drop table [dbo].[xml_catalog]; |
29 | 29 |
drop table [dbo].[accession_number]; |
30 |
drop table [dbo].[harvest_site_schedule]; |
|
31 |
drop table [dbo].[harvest_log]; |
|
32 |
drop table [dbo].[harvest_detail_log]; |
|
30 | 33 |
|
31 | 34 |
/* |
32 | 35 |
* ACL -- table to store ACL for XML documents by principals |
... | ... | |
188 | 191 |
GO |
189 | 192 |
|
190 | 193 |
/* |
194 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
195 |
*/ |
|
196 |
CREATE TABLE [dbo].[harvest_site_schedule] ( |
|
197 |
[site_schedule_id] [int] IDENTITY (1, 1) NOT NULL , |
|
198 |
[documentlisturl] [varchar] (250) NOT NULL , |
|
199 |
[ldapdn] [varchar] (250) NOT NULL , |
|
200 |
[datenextharvest] [datetime] NULL , |
|
201 |
[datelastharvest] [datetime] NULL , |
|
202 |
[updatefrequency] [int] NULL , |
|
203 |
[unit] [varchar] (50) NULL , |
|
204 |
[contact_email] [varchar] (50) NULL , |
|
205 |
[ldappwd] [varchar] (20) NOT NULL |
|
206 |
) ON [PRIMARY] |
|
207 |
GO |
|
208 |
|
|
209 |
/* |
|
210 |
* harvest_log -- table to log entries for harvest operations |
|
211 |
*/ |
|
212 |
CREATE TABLE [dbo].[harvest_log] ( |
|
213 |
[harvest_log_id] [int] IDENTITY (1, 1) NOT NULL , |
|
214 |
[harvest_date] [datetime] NOT NULL , |
|
215 |
[status] [int] NOT NULL , |
|
216 |
[message] [varchar] (1000) NULL , |
|
217 |
[harvest_operation_code] [varchar] (30) NOT NULL , |
|
218 |
[site_schedule_id] [int] NOT NULL |
|
219 |
) ON [PRIMARY] |
|
220 |
GO |
|
221 |
|
|
222 |
/* |
|
223 |
* harvest_detail_log -- table to log detailed info about documents that |
|
224 |
* generated errors during the harvest |
|
225 |
*/ |
|
226 |
CREATE TABLE [dbo].[harvest_detail_log] ( |
|
227 |
[detail_log_id] [int] IDENTITY (1, 1) NOT NULL , |
|
228 |
[harvest_log_id] [int] NOT NULL , |
|
229 |
[scope] [varchar] (50) NOT NULL , |
|
230 |
[identifier] [bigint] NOT NULL , |
|
231 |
[revision] [bigint] NOT NULL , |
|
232 |
[document_url] [varchar] (255) NOT NULL , |
|
233 |
[error_message] [varchar] (1000) NOT NULL , |
|
234 |
[document_type] [varchar] (100) NOT NULL |
|
235 |
) ON [PRIMARY] |
|
236 |
GO |
|
237 |
|
|
238 |
/* |
|
191 | 239 |
* Constraints and indexes |
192 | 240 |
*/ |
193 | 241 |
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD |
... | ... | |
240 | 288 |
) ON [PRIMARY] |
241 | 289 |
GO |
242 | 290 |
|
291 |
ALTER TABLE [dbo].[harvest_site_schedule] WITH NOCHECK ADD |
|
292 |
CONSTRAINT [PK_harvest_site_schedule] PRIMARY KEY CLUSTERED |
|
293 |
( |
|
294 |
[site_schedule_id] |
|
295 |
) ON [PRIMARY] |
|
296 |
GO |
|
297 |
|
|
298 |
ALTER TABLE [dbo].[harvest_log] WITH NOCHECK ADD |
|
299 |
CONSTRAINT [PK_harvest_log] PRIMARY KEY CLUSTERED |
|
300 |
( |
|
301 |
[harvest_log_id] |
|
302 |
) ON [PRIMARY] |
|
303 |
GO |
|
304 |
|
|
305 |
ALTER TABLE [dbo].[harvest_detail_log] WITH NOCHECK ADD |
|
306 |
CONSTRAINT [PK_harvest_detail_log] PRIMARY KEY CLUSTERED |
|
307 |
( |
|
308 |
[detail_log_id] |
|
309 |
) ON [PRIMARY] |
|
310 |
GO |
|
311 |
|
|
243 | 312 |
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD |
244 | 313 |
CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time]) |
245 | 314 |
GO |
... | ... | |
387 | 456 |
) |
388 | 457 |
GO |
389 | 458 |
|
459 |
ALTER TABLE [dbo].[harvest_log] ADD |
|
460 |
CONSTRAINT [FK_harvest_log_harvest_site_schedule] FOREIGN KEY |
|
461 |
( |
|
462 |
[site_schedule_id] |
|
463 |
) REFERENCES [dbo].[harvest_site_schedule] ( |
|
464 |
[site_schedule_id] |
|
465 |
) |
|
466 |
GO |
|
467 |
|
|
468 |
ALTER TABLE [dbo].[harvest_detail_log] ADD |
|
469 |
CONSTRAINT [FK_harvest_detail_log_harvest_log] FOREIGN KEY |
|
470 |
( |
|
471 |
[harvest_log_id] |
|
472 |
) REFERENCES [dbo].[harvest_log] ( |
|
473 |
[harvest_log_id] |
|
474 |
) |
|
475 |
GO |
|
476 |
|
src/upgrade-db-to-1.4_postgres.sql | ||
---|---|---|
35 | 35 |
date_logged TIMESTAMP, -- the datetime on which the event occurred |
36 | 36 |
CONSTRAINT access_log_pk PRIMARY KEY (entryid) |
37 | 37 |
); |
38 |
|
|
39 |
/* |
|
40 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
41 |
*/ |
|
42 |
CREATE TABLE harvest_site_schedule ( |
|
43 |
site_schedule_id INT8, -- unique id |
|
44 |
documentlisturl VARCHAR(255), -- URL of the site harvest document list |
|
45 |
ldapdn VARCHAR(255), -- LDAP distinguished name for site account |
|
46 |
datenextharvest DATE, -- scheduled date of next harvest |
|
47 |
datelastharvest DATE, -- recorded date of last harvest |
|
48 |
updatefrequency INT8, -- the harvest update frequency |
|
49 |
unit VARCHAR(50), -- update unit -- days weeks or months |
|
50 |
contact_email VARCHAR(50), -- email address of the site contact person |
|
51 |
ldappwd VARCHAR(20), -- LDAP password for site account |
|
52 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
53 |
); |
|
54 |
|
|
55 |
/* |
|
56 |
* harvest_log -- table to log entries for harvest operations |
|
57 |
*/ |
|
58 |
CREATE TABLE harvest_log ( |
|
59 |
harvest_log_id INT8, -- unique id |
|
60 |
harvest_date DATE, -- date of the current harvest |
|
61 |
status INT8, -- non-zero indicates an error status |
|
62 |
message VARCHAR(1000), -- text message for this log entry |
|
63 |
harvest_operation_code VARCHAR(30), -- the type of harvest operation |
|
64 |
site_schedule_id INT8, -- foreign key |
|
65 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id), |
|
66 |
CONSTRAINT harvest_log_site_schedule_id_fk |
|
67 |
FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule |
|
68 |
); |
|
69 |
|
|
70 |
/* |
|
71 |
* harvest_detail_log -- table to log detailed info about documents that |
|
72 |
* generated errors during the harvest |
|
73 |
*/ |
|
74 |
CREATE TABLE harvest_detail_log ( |
|
75 |
detail_log_id INT8, -- unique id |
|
76 |
harvest_log_id INT8, -- ponter to the related log entry |
|
77 |
scope VARCHAR(50), -- document scope |
|
78 |
identifier INT8, -- document identifier |
|
79 |
revision INT8, -- document revision |
|
80 |
document_url VARCHAR(255), -- document URL |
|
81 |
error_message VARCHAR(1000), -- text error message |
|
82 |
document_type VARCHAR(100), -- document type |
|
83 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
84 |
CONSTRAINT harvest_detail_log_harvest_log_id_fk |
|
85 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
86 |
); |
|
87 |
|
Also available in: Unified diff
Add three new Harvester tables