1 |
81
|
bojilova
|
/*
|
2 |
2
|
jones
|
* xmltables.sql -- Create or replace tables for storing XML in the db
|
3 |
|
|
*
|
4 |
|
|
* Purpose: creates tables needed for XML database
|
5 |
|
|
*
|
6 |
|
|
* Created: 12 September 1999
|
7 |
|
|
* Author: Matt Jones
|
8 |
|
|
* Organization: National Center for Ecological Analysis and Synthesis
|
9 |
123
|
jones
|
* 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$'
|
13 |
2
|
jones
|
*
|
14 |
|
|
*/
|
15 |
|
|
|
16 |
5
|
jones
|
/*
|
17 |
19
|
jones
|
* Drop all of the objects in proper order
|
18 |
2
|
jones
|
*/
|
19 |
72
|
bojilova
|
set echo off
|
20 |
|
|
|
21 |
20
|
jones
|
DROP SEQUENCE xml_nodes_id_seq;
|
22 |
123
|
jones
|
DROP SEQUENCE xml_catalog_id_seq;
|
23 |
72
|
bojilova
|
DROP SEQUENCE xml_documents_id_seq;
|
24 |
|
|
|
25 |
20
|
jones
|
DROP TRIGGER xml_nodes_before_insert;
|
26 |
72
|
bojilova
|
DROP TRIGGER xml_documents_before_insert;
|
27 |
93
|
bojilova
|
DROP TRIGGER xml_catalog_before_insert;
|
28 |
72
|
bojilova
|
|
29 |
123
|
jones
|
DROP TABLE xml_index;
|
30 |
72
|
bojilova
|
DROP TABLE xml_catalog;
|
31 |
5
|
jones
|
DROP TABLE xml_documents;
|
32 |
20
|
jones
|
DROP TABLE xml_nodes;
|
33 |
3
|
jones
|
|
34 |
|
|
/*
|
35 |
20
|
jones
|
* Nodes -- table to store XML Nodes (both elements and attributes)
|
36 |
3
|
jones
|
*/
|
37 |
20
|
jones
|
CREATE TABLE xml_nodes (
|
38 |
123
|
jones
|
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
|
48 |
3
|
jones
|
date_created DATE,
|
49 |
|
|
date_updated DATE,
|
50 |
20
|
jones
|
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
|
51 |
123
|
jones
|
CONSTRAINT xml_nodes_root_fk
|
52 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
53 |
20
|
jones
|
CONSTRAINT xml_nodes_parent_fk
|
54 |
|
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
|
55 |
3
|
jones
|
);
|
56 |
|
|
|
57 |
20
|
jones
|
CREATE SEQUENCE xml_nodes_id_seq;
|
58 |
19
|
jones
|
|
59 |
20
|
jones
|
CREATE TRIGGER xml_nodes_before_insert
|
60 |
|
|
BEFORE INSERT ON xml_nodes FOR EACH ROW
|
61 |
19
|
jones
|
BEGIN
|
62 |
20
|
jones
|
SELECT xml_nodes_id_seq.nextval
|
63 |
19
|
jones
|
INTO :new.nodeid
|
64 |
|
|
FROM dual;
|
65 |
|
|
END;
|
66 |
|
|
/
|
67 |
|
|
|
68 |
3
|
jones
|
/*
|
69 |
72
|
bojilova
|
* Documents -- table to store XML documents
|
70 |
5
|
jones
|
*/
|
71 |
|
|
CREATE TABLE xml_documents (
|
72 |
123
|
jones
|
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
|
77 |
5
|
jones
|
date_created DATE,
|
78 |
|
|
date_updated DATE,
|
79 |
|
|
CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
|
80 |
|
|
CONSTRAINT xml_documents_root_fk
|
81 |
20
|
jones
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
|
82 |
5
|
jones
|
);
|
83 |
|
|
|
84 |
72
|
bojilova
|
CREATE SEQUENCE xml_documents_id_seq;
|
85 |
|
|
|
86 |
|
|
CREATE TRIGGER xml_documents_before_insert
|
87 |
|
|
BEFORE INSERT ON xml_documents FOR EACH ROW
|
88 |
|
|
BEGIN
|
89 |
|
|
SELECT xml_documents_id_seq.nextval
|
90 |
|
|
INTO :new.docid
|
91 |
|
|
FROM dual;
|
92 |
|
|
END;
|
93 |
|
|
/
|
94 |
|
|
|
95 |
|
|
/*
|
96 |
93
|
bojilova
|
* XML Catalog -- table to store all external sources for XML documents
|
97 |
72
|
bojilova
|
*/
|
98 |
|
|
CREATE TABLE xml_catalog (
|
99 |
123
|
jones
|
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)
|
108 |
72
|
bojilova
|
);
|
109 |
|
|
|
110 |
123
|
jones
|
CREATE SEQUENCE xml_catalog_id_seq;
|
111 |
72
|
bojilova
|
|
112 |
93
|
bojilova
|
CREATE TRIGGER xml_catalog_before_insert
|
113 |
|
|
BEFORE INSERT ON xml_catalog FOR EACH ROW
|
114 |
72
|
bojilova
|
BEGIN
|
115 |
123
|
jones
|
SELECT xml_catalog_id_seq.nextval
|
116 |
|
|
INTO :new.catalog_id
|
117 |
72
|
bojilova
|
FROM dual;
|
118 |
|
|
END;
|
119 |
|
|
/
|
120 |
123
|
jones
|
|
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 |
|
|
);
|