/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 2422

Added by sgarg over 19 years ago

Made changes in the sql scripts to create new tables xml_returnfield and xml_queryresult

     DROP SEQUENCE xml_documents_id_seq;
     DROP SEQUENCE accession_number_id_seq;
     DROP SEQUENCE access_log_seq;
     DROP SEQUENCE xml_returnfield_id_seq;
     DROP SEQUENCE xml_queryresult_id_seq;
     /* Drop triggers are not necessary */
     DROP TRIGGER xml_nodes_before_insert;
     DROP TRIGGER xml_revisions_before_insert;
-...
     DROP TRIGGER xml_replication_before_insert;
     DROP TRIGGER accession_number_before_insert;
     DROP TRIGGER access_log_before_insert;
     DROP TRIGGER xml_returnfield_before_insert;
     DROP TRIGGER xml_queryresult_before_insert;
     DROP TABLE xml_index;
     DROP TABLE xml_access;
     DROP TABLE xml_accesssubtree;
-...
     DROP TABLE harvest_site_schedule;
     DROP TABLE harvest_detail_log;
     DROP TABLE harvest_log;
     DROP TABLE xml_returnfield;
     DROP TABLE xml_queryresult;
     /*
      *Replication -- table to store servers that metacat is replicated to
-...
       datareplicate NUMBER(1),
       hub NUMBER(1),
       CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
     );
     );
     CREATE SEQUENCE xml_replication_id_seq;
     CREATE TRIGGER xml_replication_before_insert
     BEFORE INSERT ON xml_replication FOR EACH ROW
-...
     INSERT INTO xml_replication (server, replicate, datareplicate, hub)
      VALUES ('localhost', '0', '0', '0');
     /*
     /*
      * Nodes -- table to store XML Nodes (both elements and attributes)
      */
     CREATE SEQUENCE xml_nodes_id_seq;
-...
     	nodename	VARCHAR2(250),	-- the name of an element or attribute
     	nodeprefix	VARCHAR2(50),	-- the namespace prefix of an element
                                             -- or attribute
     	nodedata	VARCHAR2(4000), -- the data for this node (e.g.,
     	nodedata	VARCHAR2(4000), -- the data for this node (e.g.,
     					-- for TEXT it is the content)
     	parentnodeid	NUMBER(20),	-- index of the parent of this node
     	rootnodeid	NUMBER(20),	-- index of the root node of this tree
     	docid		VARCHAR2(250),	-- index to the document id
     	date_created	DATE,
     	date_updated	DATE,
     	nodedatanumerical NUMBER,       -- the data for this node if
     	nodedatanumerical NUMBER,       -- the data for this node if
     					-- it is a number
        CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_root_fk
        CONSTRAINT xml_nodes_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_nodes_parent_fk
        CONSTRAINT xml_nodes_parent_fk
     		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
     );
     CREATE TRIGGER xml_nodes_before_insert
-...
     BEGIN
       SELECT xml_nodes_id_seq.nextval
         INTO :new.nodeid
         FROM dual;
     END;
         FROM dual;
     END;
+    /
     /*
     /*
      * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
      */
     CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
     CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
     CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
     /*
     /*
      * XML Catalog -- table to store all external sources for XML documents
      */
     CREATE TABLE xml_catalog (
-...
     	public_id	VARCHAR2(500),	-- the unique id for this type
     	system_id	VARCHAR2(1000),	-- the local location of the object
        CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
        CONSTRAINT xml_catalog_uk UNIQUE
        CONSTRAINT xml_catalog_uk UNIQUE
     		(entry_type, source_doctype, target_doctype, public_id)
     );
-...
     END;
+    /
     /*
     /*
      * Documents -- table to store XML documents
      */
     CREATE TABLE xml_documents (
-...
     	doctype		VARCHAR2(100),	-- public id indicating document type
     	user_owner	VARCHAR2(100),	-- the user owned the document
     	user_updated	VARCHAR2(100),	-- the user updated the document
     	server_location NUMBER(20),	-- the server on which this document
     	server_location NUMBER(20),	-- the server on which this document
                                             -- originates
     	rev 		NUMBER(10) DEFAULT 1,--the revision number of the docume
     	date_created	DATE,
     	date_updated	DATE,
     	public_access	NUMBER(1),	-- flag for public access
             catalog_id      NUMBER(20),	-- reference to xml_catalog
             catalog_id      NUMBER(20),	-- reference to xml_catalog
        CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
        CONSTRAINT xml_documents_rep_fk
         		FOREIGN KEY (server_location) REFERENCES xml_replication,
        CONSTRAINT xml_documents_root_fk
         		FOREIGN KEY (server_location) REFERENCES xml_replication,
        CONSTRAINT xml_documents_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_documents_catalog_fk
        CONSTRAINT xml_documents_catalog_fk
     		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
     );
     /*
     /*
      * Index of <docid,doctype> in xml_document
      */
     CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
     /*
     /*
      * Revised Documents -- table to store XML documents saved after an UPDATE
      *                    or DELETE
      */
-...
     	date_created	DATE,
     	date_updated	DATE,
     	public_access	NUMBER(1),	-- flag for public access
             catalog_id      NUMBER(20),	-- reference to xml_catalog
             catalog_id      NUMBER(20),	-- reference to xml_catalog
        CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
        CONSTRAINT xml_revisions_rep_fk
     		FOREIGN KEY (server_location) REFERENCES xml_replication,
        CONSTRAINT xml_revisions_root_fk
        CONSTRAINT xml_revisions_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_revisions_catalog_fk
        CONSTRAINT xml_revisions_catalog_fk
     		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
     );
-...
     END;
+    /
     /*
     /*
      * ACL -- table to store ACL for XML documents by principals
      */
     CREATE TABLE xml_access (
-...
       startnodeid NUMBER(20), -- start node for sub tree
       endnodeid NUMBER(20),    -- end node for sub tree
        CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
        CONSTRAINT xml_access_accessfileid_fk
        CONSTRAINT xml_access_accessfileid_fk
     		FOREIGN KEY (accessfileid) REFERENCES xml_documents
     );
     /*
      * Index of Nodes -- table to store precomputed paths through tree for
     /*
      * Index of Nodes -- table to store precomputed paths through tree for
      * quick searching in structured searches
      */
     CREATE TABLE xml_index (
-...
     	path		VARCHAR2(1000),	-- precomputed path through tree
     	docid		VARCHAR2(250),	-- index to the document id
     	doctype		VARCHAR2(100),	-- public id indicating document type
             parentnodeid    NUMBER(20),     -- id of the parent of the node
             parentnodeid    NUMBER(20),     -- id of the parent of the node
     					-- represented by this row
     	nodedata        VARCHAR2(4000), -- the data for the given path
     	nodedata        VARCHAR2(4000), -- the data for the given path
        CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
        CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_index_docid_fk
        CONSTRAINT xml_index_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
     );
     /*
      * Index of the paths in xml_index
     /*
      * Index of the paths in xml_index
      */
     CREATE INDEX xml_index_idx1 ON xml_index (path);
     CREATE INDEX xml_index_idx2 ON xml_index (docid);
-...
     	object        VARCHAR2(512) NOT NULL, -- the object of the relation
     	objdoctype    VARCHAR2(128),          -- the doctype of the object
     	CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object),
     	CONSTRAINT xml_relation_docid_fk
     	CONSTRAINT xml_relation_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
       );
     CREATE SEQUENCE xml_relation_id_seq;
     CREATE TRIGGER xml_relation_before_insert
     BEFORE INSERT ON xml_relation FOR EACH ROW
     BEGIN
       SELECT xml_relation_id_seq.nextval
         INTO :new.relationid
         FROM dual;
     END;
     END;
+    /
     /*
     /*
      * Table used as Unique ID generator for the uniqueid part of Accession#
      */
     CREATE SEQUENCE accession_number_id_seq;
-...
       SELECT accession_number_id_seq.nextval
         INTO :new.uniqueid
         FROM dual;
     END;
     END;
+    /
     /*
      * accesssubtree -- table to store access subtree info
     /*
      * accesssubtree -- table to store access subtree info
      */
     CREATE TABLE xml_accesssubtree (
     	docid		VARCHAR2(250),	-- the document id #
       rev 		NUMBER(10) DEFAULT 1, --the revision number of the docume
       controllevel VARCHAR2(50), -- the level it control -- document or subtree
       subtreeid VARCHAR2(250), -- the subtree id
       subtreeid VARCHAR2(250), -- the subtree id
     	startnodeid	NUMBER(20),	-- the start node id of access subtree
       endnodeid NUMBER(20), -- the end node if of access subtree
       CONSTRAINT xml_accesssubtree_docid_fk
       CONSTRAINT xml_accesssubtree_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
     );
     /*
      * 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;
+    /
     /*
      * Logging -- table to store metadata and data access log
      */
     CREATE TABLE access_log (
-...
     END;
+    /
     /*
     /*
      * harvest_site_schedule -- table to store harvest sites and schedule info
      */
     CREATE TABLE harvest_site_schedule (
-...
       CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)
     );
     /*
     /*
      * harvest_log -- table to log entries for harvest operations
      */
     CREATE TABLE harvest_log (
-...
       CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id)
     );
     /*
     /*
      * harvest_detail_log -- table to log detailed info about documents that
      *                       generated errors during the harvest
      */
-...
       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_fk
       CONSTRAINT harvest_detail_log_fk
             FOREIGN KEY (harvest_log_id) REFERENCES harvest_log
     );

      * along with this program; if not, write to the Free Software
      * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
      */
     /*
      *	this is sql script does the same as the sql script named
      *	xmltables.sql except that this script is to be use to
      *	this is sql script does the same as the sql script named
      *	xmltables.sql except that this script is to be use to
      *	create the database tables on a Postgresql backend rather
      *	than an Oracle Backend
      */
-...
      */
     CREATE SEQUENCE xml_replication_id_seq;
     CREATE TABLE xml_replication (
       serverid INT8 default nextval('xml_replication_id_seq'),
       serverid INT8 default nextval('xml_replication_id_seq'),
       server VARCHAR(512),
       last_checked DATE,
       replicate INT8,
       datareplicate INT8,
       hub INT8,
       CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
     );
     );
     INSERT INTO xml_replication (server, replicate, datareplicate, hub) VALUES ('localhost', '0', '0', '0');
     /*
     /*
      * Nodes -- table to store XML Nodes (both elements and attributes)
      */
     CREATE SEQUENCE xml_nodes_id_seq;
-...
     				-- ELEMENT, ATTRIBUTE, TEXT)
     	nodename VARCHAR(250),	-- the name of an element or attribute
             nodeprefix VARCHAR(50), -- the namespace prefix of the node
     	nodedata VARCHAR(4000), -- the data for this node (e.g.,
     	nodedata VARCHAR(4000), -- the data for this node (e.g.,
     				-- for TEXT it is the content)
     	parentnodeid INT8,	-- index of the parent of this node
     	rootnodeid INT8,	-- index of the root node of this tree
-...
             nodedatanumerical FLOAT8, -- the data for this node if
     				  -- if it is a number
        CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_root_fk
        CONSTRAINT xml_nodes_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_nodes_parent_fk
        CONSTRAINT xml_nodes_parent_fk
     		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
     );
     /*
     /*
      * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
      */
     CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
     CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
     CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
     /*
     /*
      * XML Catalog -- table to store all external sources for XML documents
      */
     CREATE SEQUENCE xml_catalog_id_seq;
-...
     	public_id VARCHAR(500),	-- the unique id for this type
     	system_id VARCHAR(1000),	-- the local location of the object
        CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
        CONSTRAINT xml_catalog_uk UNIQUE
        CONSTRAINT xml_catalog_uk UNIQUE
                   (entry_type, source_doctype, target_doctype, public_id)
     );
     /*
     /*
      * Sequence to get uniqueID for Accession #
      */
     CREATE SEQUENCE xml_documents_id_seq;
     /*
     /*
      * Documents -- table to store XML documents
      */
     CREATE TABLE xml_documents (
-...
     	date_created DATE,
     	date_updated DATE,
     	public_access INT8,	-- flag for public access
             catalog_id INT8,	-- reference to xml_catalog
             catalog_id INT8,	-- reference to xml_catalog
          CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
          CONSTRAINT xml_documents_rep_fk
          		FOREIGN KEY (server_location) REFERENCES xml_replication,
         CONSTRAINT xml_documents_root_fk
          		FOREIGN KEY (server_location) REFERENCES xml_replication,
         CONSTRAINT xml_documents_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_documents_catalog_fk
        CONSTRAINT xml_documents_catalog_fk
     		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
     );
     /*
     /*
      * Index of <docid,doctype> in xml_document
      */
     CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
     /*
     /*
      * Revised Documents -- table to store XML documents saved after an UPDATE
      *                    or DELETE
      */
-...
     	date_created DATE,
     	date_updated DATE,
     	public_access INT8,	-- flag for public access
             catalog_id INT8,	-- reference to xml_catalog
             catalog_id INT8,	-- reference to xml_catalog
        CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
        CONSTRAINT xml_revisions_rep_fk
     		FOREIGN KEY (server_location) REFERENCES xml_replication,
        CONSTRAINT xml_revisions_root_fk
        CONSTRAINT xml_revisions_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_revisions_catalog_fk
        CONSTRAINT xml_revisions_catalog_fk
     		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
     );
     /*
     /*
      * ACL -- table to store ACL for XML documents by principals
      */
     CREATE TABLE xml_access (
-...
       startnodeid INT8,
       endnodeid INT8,
        CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
        CONSTRAINT xml_access_accessfileid_fk
        CONSTRAINT xml_access_accessfileid_fk
     		FOREIGN KEY (accessfileid) REFERENCES xml_documents
     );
     /*
      * Index of Nodes -- table to store precomputed paths through tree for
     /*
      * Index of Nodes -- table to store precomputed paths through tree for
      * quick searching in structured searches
      */
     CREATE TABLE xml_index (
-...
     	doctype VARCHAR(100),	-- public id indicating document type
             parentnodeid INT8,      -- id of the parent of the node represented
     				-- by this row
     	nodedata VARCHAR (4000),-- for storing data for a given path
     	nodedata VARCHAR (4000),-- for storing data for a given path
        CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
        CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
        CONSTRAINT xml_index_docid_fk
        CONSTRAINT xml_index_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
     );
     /*
      * Index of the paths in xml_index
     /*
      * Index of the paths in xml_index
      */
     CREATE INDEX xml_index_idx1 ON xml_index (path);
     CREATE INDEX xml_index_idx2 ON xml_index (docid);
-...
     	object VARCHAR(512) NOT NULL, -- the object of the relation
     	objdoctype VARCHAR(128),          -- the doctype of the object
     	CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object),
     	CONSTRAINT xml_relation_docid_fk
     	CONSTRAINT xml_relation_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
     );
     /*
     /*
      * Table used as Unique ID generator for the uniqueid part of Accession#
      */
     CREATE SEQUENCE accession_number_id_seq;
-...
        date_created DATE
     );
     /*
      * accesssubtree -- table to store access subtree info
     /*
      * accesssubtree -- table to store access subtree info
      */
     CREATE TABLE xml_accesssubtree (
     	docid		VARCHAR(250),	-- the document id #
       rev 		INT8 default 1, --the revision number of the docume
       controllevel VARCHAR(50), -- the level it control -- document or subtree
       subtreeid VARCHAR(250), -- the subtree id
       subtreeid VARCHAR(250), -- the subtree id
     	startnodeid	INT8,	-- the start node id of access subtree
       endnodeid INT8, -- the end node if of access subtree
       CONSTRAINT xml_accesssubtree_docid_fk
       CONSTRAINT xml_accesssubtree_docid_fk
     		FOREIGN KEY (docid) REFERENCES xml_documents
     );
     /*
      * Returnfields -- table to store combinations of returnfields requested
      *		    and the number of times this table is accessed
      */
     CREATE TABLE xml_returnfield (
             returnfield_id     INT8 default nextval('xml_returnfield_id_seq'),   -- the id for this returnfield entry
             returnfield_string VARCHAR(2000),                                    -- the returnfield string
             usage_count        INT8,                                             -- 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;
     /*
      * Queryresults -- table to store queryresults for a given docid
      * and returnfield_id
      */
     CREATE TABLE xml_queryresult(
       queryresult_id INT8 default nextval('xml_queryresult_id_seq'), -- id for this entry
       returnfield_id       INT8,          -- id for the returnfield corresponding to this entry
       docid                VARCHAR(250),  -- docid of the document
       queryresult_string   VARCHAR(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;
     /*
      * Logging -- table to store metadata and data access log
      */
     CREATE SEQUENCE access_log_id_seq;
-...
       CONSTRAINT access_log_pk PRIMARY KEY (entryid)
     );
     /*
     /*
      * harvest_site_schedule -- table to store harvest sites and schedule info
      */
     CREATE TABLE harvest_site_schedule (
-...
       CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)
     );
     /*
     /*
      * harvest_log -- table to log entries for harvest operations
      */
     CREATE TABLE harvest_log (
-...
       CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id)
     );
     /*
     /*
      * harvest_detail_log -- table to log detailed info about documents that
      *                       generated errors during the harvest
      */
-...
       error_message          VARCHAR(1000), -- text error message
       document_type          VARCHAR(100),  -- document type
       CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id),
       CONSTRAINT harvest_detail_log_fk
       CONSTRAINT harvest_detail_log_fk
             FOREIGN KEY (harvest_log_id) REFERENCES harvest_log
     );

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 2422

Added by sgarg over 19 years ago