Revision 2109
Added by Duane Costa about 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