Metacat

DROP TABLE harvest_site_schedule;

DROP TABLE harvest_log;

DROP TABLE harvest_detail_log;

/* 

 * harvest_site_schedule -- table to store harvest sites and schedule info

 */

CREATE TABLE harvest_site_schedule (

  site_schedule_id NUMBER,         -- unique id

  documentlisturl  VARCHAR2(255),  -- URL of the site harvest document list

  ldapdn           VARCHAR2(255),  -- LDAP distinguished name for site account

  datenextharvest  DATE,           -- scheduled date of next harvest

  datelastharvest  DATE,           -- recorded date of last harvest

  updatefrequency  NUMBER,         -- the harvest update frequency

  unit             VARCHAR2(50),   -- update unit -- days weeks or months

  contact_email    VARCHAR2(50),   -- email address of the site contact person

  ldappwd          VARCHAR2(20),   -- LDAP password for site account

  CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)

);

/* 

 * harvest_log -- table to log entries for harvest operations

 */

CREATE TABLE harvest_log (

  harvest_log_id         NUMBER,         -- unique id

  harvest_date           DATE,           -- date of the current harvest

  status                 NUMBER,         -- non-zero indicates an error status

  message                VARCHAR2(1000), -- text message for this log entry

  harvest_operation_code VARCHAR2(30),   -- the type of harvest operation

  site_schedule_id       NUMBER,         -- foreign key

  CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id),

  CONSTRAINT harvest_log_site_schedule_id_fk 

        FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule

);

/* 

 * harvest_detail_log -- table to log detailed info about documents that

 *                       generated errors during the harvest

 */

CREATE TABLE harvest_detail_log (

  detail_log_id          NUMBER,         -- unique id

  harvest_log_id         NUMBER,         -- ponter to the related log entry

  scope                  VARCHAR2(50),   -- document scope

  identifier             NUMBER,         -- document identifier

  revision               NUMBER,         -- document revision

  document_url           VARCHAR2(255),  -- document URL

  error_message          VARCHAR2(1000), -- text error message

  document_type          VARCHAR2(100),  -- document type

  CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id),

  CONSTRAINT harvest_detail_log_harvest_log_id_fk 

        FOREIGN KEY (harvest_log_id) REFERENCES harvest_log

);