| 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 |
);
|
Modifying storage model to use DOM TEXT nodes