Metacat

/*

 * Returnfields -- table to store combinations of returnfields requested

 *                 and the number of times this table is accessed

 */

CREATE TABLE xml_returnfield (

        returnfield_id     NUMBER(20),     -- the id for this returnfield entry

        returnfield_string VARCHAR2(2000), -- the returnfield string

        usage_count        NUMBER(20),     -- the number of times this string

                                           -- has been requested

        CONSTRAINT xml_returnfield_pk PRIMARY KEY (returnfield_id)

);

CREATE INDEX xml_returnfield_idx1 ON xml_returnfield(returnfield_string);

CREATE SEQUENCE xml_returnfield_id_seq;

CREATE TRIGGER xml_returnfield_before_insert

BEFORE INSERT ON xml_returnfield FOR EACH ROW

BEGIN

  SELECT xml_returnfield_id_seq.nextval

    INTO :new.returnfield_id

    FROM dual;

END;

/

/*

 * Queryresults -- table to store queryresults for a given docid

 * and returnfield_id

 */

CREATE TABLE xml_queryresult(

  queryresult_id       NUMBER(20),     -- id for this entry

  returnfield_id       NUMBER(20),     -- id for the returnfield corresponding to this entry

  docid                VARCHAR2(250),  -- docid of the document

  queryresult_string   VARCHAR2(4000), -- resultant text generated for this docid and given

                                       -- returnfield

  CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id),

  CONSTRAINT xml_queryresult_searchid_fk

               FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield

);

CREATE INDEX xml_queryresult_idx1 ON xml_queryresult (returnfield_id, docid);

CREATE SEQUENCE xml_queryresult_id_seq;

CREATE TRIGGER xml_queryresult_before_insert

BEFORE INSERT ON xml_queryresult FOR EACH ROW

BEGIN

 SELECT xml_queryresult_id_seq.nextval

   INTO :new.queryresult_id

   FROM dual;

END;

/