Revision 123
Added by Matt Jones about 24 years ago
| xmltables.sql | ||
|---|---|---|
| 6 | 6 |
* Created: 12 September 1999 |
| 7 | 7 |
* Author: Matt Jones |
| 8 | 8 |
* Organization: National Center for Ecological Analysis and Synthesis |
| 9 |
* Version: '$Id$' |
|
| 9 |
* Copyright: 2000 Regents of the University of California and the |
|
| 10 |
* National Center for Ecological Analysis and Synthesis |
|
| 11 |
* For Details: http://www.nceas.ucsb.edu/ |
|
| 12 |
* File Info: '$Id$' |
|
| 10 | 13 |
* |
| 11 | 14 |
*/ |
| 12 | 15 |
|
| ... | ... | |
| 16 | 19 |
set echo off |
| 17 | 20 |
|
| 18 | 21 |
DROP SEQUENCE xml_nodes_id_seq; |
| 19 |
DROP SEQUENCE xml_entity_id_seq;
|
|
| 22 |
DROP SEQUENCE xml_catalog_id_seq;
|
|
| 20 | 23 |
DROP SEQUENCE xml_documents_id_seq; |
| 21 | 24 |
|
| 22 | 25 |
DROP TRIGGER xml_nodes_before_insert; |
| 23 | 26 |
DROP TRIGGER xml_documents_before_insert; |
| 24 | 27 |
DROP TRIGGER xml_catalog_before_insert; |
| 25 | 28 |
|
| 29 |
DROP TABLE xml_index; |
|
| 26 | 30 |
DROP TABLE xml_catalog; |
| 27 | 31 |
DROP TABLE xml_documents; |
| 28 | 32 |
DROP TABLE xml_nodes; |
| ... | ... | |
| 31 | 35 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
| 32 | 36 |
*/ |
| 33 | 37 |
CREATE TABLE xml_nodes ( |
| 34 |
nodeid NUMBER(20), |
|
| 35 |
parentnodeid NUMBER(20), |
|
| 36 |
nodeindex NUMBER(10), |
|
| 37 |
nodetype VARCHAR2(20), |
|
| 38 |
nodename VARCHAR2(100), |
|
| 39 |
nodedata VARCHAR2(4000), |
|
| 38 |
nodeid NUMBER(20), -- the unique node id (pk) |
|
| 39 |
nodeindex NUMBER(10), -- order of nodes within parent |
|
| 40 |
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI, |
|
| 41 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
| 42 |
nodename VARCHAR2(250), -- the name of an element or attribute |
|
| 43 |
nodedata VARCHAR2(4000), -- the data for this node (e.g., |
|
| 44 |
-- for TEXT it is the content) |
|
| 45 |
parentnodeid NUMBER(20), -- index of the parent of this node |
|
| 46 |
rootnodeid NUMBER(20), -- index of the root node of this tree |
|
| 47 |
docid NUMBER(20), -- index to the document id |
|
| 40 | 48 |
date_created DATE, |
| 41 | 49 |
date_updated DATE, |
| 42 | 50 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
| 51 |
CONSTRAINT xml_nodes_root_fk |
|
| 52 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
| 43 | 53 |
CONSTRAINT xml_nodes_parent_fk |
| 44 | 54 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
| 45 | 55 |
); |
| ... | ... | |
| 59 | 69 |
* Documents -- table to store XML documents |
| 60 | 70 |
*/ |
| 61 | 71 |
CREATE TABLE xml_documents ( |
| 62 |
docid NUMBER(20), |
|
| 63 |
rootnodeid NUMBER(20), |
|
| 64 |
docname VARCHAR2(100), |
|
| 65 |
doctype VARCHAR2(100), |
|
| 66 |
doctitle VARCHAR2(1000), |
|
| 72 |
docid NUMBER(20), -- the document id #
|
|
| 73 |
rootnodeid NUMBER(20), -- refernce to the root node of the DOM
|
|
| 74 |
docname VARCHAR2(100), -- usually the root element name
|
|
| 75 |
doctype VARCHAR2(100), -- public id indicating document type
|
|
| 76 |
doctitle VARCHAR2(1000), -- title of document if exists
|
|
| 67 | 77 |
date_created DATE, |
| 68 | 78 |
date_updated DATE, |
| 69 | 79 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
| ... | ... | |
| 86 | 96 |
* XML Catalog -- table to store all external sources for XML documents |
| 87 | 97 |
*/ |
| 88 | 98 |
CREATE TABLE xml_catalog ( |
| 89 |
entity_id NUMBER(20), |
|
| 90 |
entity_name VARCHAR2(100), |
|
| 91 |
entity_type VARCHAR2(20), |
|
| 92 |
source_doctype VARCHAR2(100), |
|
| 93 |
target_doctype VARCHAR2(100), |
|
| 94 |
public_id VARCHAR2(100), |
|
| 95 |
system_id VARCHAR2(1000), |
|
| 96 |
date_created DATE, |
|
| 97 |
date_updated DATE, |
|
| 98 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (entity_id), |
|
| 99 |
CONSTRAINT xml_catalog_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id) |
|
| 99 |
catalog_id NUMBER(20), -- the id for this catalog entry |
|
| 100 |
entry_type VARCHAR2(500), -- the type of this catalog entry |
|
| 101 |
-- (e.g., DTD, XSD, XSL) |
|
| 102 |
source_doctype VARCHAR2(500), -- the source public_id for transforms |
|
| 103 |
target_doctype VARCHAR2(500), -- the target public_id for transforms |
|
| 104 |
public_id VARCHAR2(500), -- the unique id for this type |
|
| 105 |
system_id VARCHAR2(1000), -- the local location of the object |
|
| 106 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id) |
|
| 107 |
-- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id) |
|
| 100 | 108 |
); |
| 101 | 109 |
|
| 102 |
CREATE SEQUENCE xml_entity_id_seq;
|
|
| 110 |
CREATE SEQUENCE xml_catalog_id_seq;
|
|
| 103 | 111 |
|
| 104 | 112 |
CREATE TRIGGER xml_catalog_before_insert |
| 105 | 113 |
BEFORE INSERT ON xml_catalog FOR EACH ROW |
| 106 | 114 |
BEGIN |
| 107 |
SELECT xml_entity_id_seq.nextval
|
|
| 108 |
INTO :new.entity_id
|
|
| 115 |
SELECT xml_catalog_id_seq.nextval
|
|
| 116 |
INTO :new.catalog_id
|
|
| 109 | 117 |
FROM dual; |
| 110 | 118 |
END; |
| 111 | 119 |
/ |
| 120 |
|
|
| 121 |
/* |
|
| 122 |
* Index of Nodes -- table to store precomputed paths through tree for |
|
| 123 |
* quick searching in structured searches |
|
| 124 |
*/ |
|
| 125 |
CREATE TABLE xml_index ( |
|
| 126 |
nodeid NUMBER(20), -- the unique node id |
|
| 127 |
path VARCHAR2(20), -- precomputed path through tree |
|
| 128 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
|
| 129 |
CONSTRAINT xml_nodes_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes |
|
| 130 |
); |
|
Also available in: Unified diff
Modifying storage model to use DOM TEXT nodes