Revision 72
Added by bojilova about 24 years ago
xmltables.sql | ||
---|---|---|
1 |
/* |
|
1 |
source_doctype, public_id, /*
|
|
2 | 2 |
* xmltables.sql -- Create or replace tables for storing XML in the db |
3 | 3 |
* |
4 | 4 |
* Purpose: creates tables needed for XML database |
... | ... | |
13 | 13 |
/* |
14 | 14 |
* Drop all of the objects in proper order |
15 | 15 |
*/ |
16 |
set echo off |
|
17 |
|
|
16 | 18 |
DROP SEQUENCE xml_nodes_id_seq; |
19 |
DROP SEQUENCE xml_entity_id_seq; |
|
20 |
DROP SEQUENCE xml_documents_id_seq; |
|
21 |
|
|
17 | 22 |
DROP TRIGGER xml_nodes_before_insert; |
23 |
DROP TRIGGER xml_documents_before_insert; |
|
24 |
DROP TRIGGER xml_cat_entities_before_insert; |
|
25 |
|
|
26 |
DROP TABLE xml_catalog_entities; |
|
27 |
DROP TABLE xml_catalog; |
|
18 | 28 |
DROP TABLE xml_documents; |
19 | 29 |
DROP TABLE xml_nodes; |
20 | 30 |
|
... | ... | |
24 | 34 |
CREATE TABLE xml_nodes ( |
25 | 35 |
nodeid NUMBER(20), |
26 | 36 |
parentnodeid NUMBER(20), |
27 |
nodetype VARCHAR2(2000), |
|
28 |
nodename VARCHAR2(2000), |
|
29 |
nodedata VARCHAR2(2000), |
|
37 |
nodeindex NUMBER(10), |
|
38 |
nodetype VARCHAR2(20), |
|
39 |
nodename VARCHAR2(100), |
|
40 |
nodedata LONG, |
|
30 | 41 |
date_created DATE, |
31 | 42 |
date_updated DATE, |
32 | 43 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
... | ... | |
46 | 57 |
/ |
47 | 58 |
|
48 | 59 |
/* |
49 |
* Documents -- table to store XML document catalog
|
|
60 |
* Documents -- table to store XML documents
|
|
50 | 61 |
*/ |
51 | 62 |
CREATE TABLE xml_documents ( |
52 | 63 |
docid NUMBER(20), |
53 | 64 |
rootnodeid NUMBER(20), |
54 |
docname VARCHAR2(1000),
|
|
55 |
doctype VARCHAR2(1000),
|
|
65 |
docname VARCHAR2(100), |
|
66 |
doctype VARCHAR2(100), |
|
56 | 67 |
date_created DATE, |
57 | 68 |
date_updated DATE, |
58 | 69 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
... | ... | |
60 | 71 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes |
61 | 72 |
); |
62 | 73 |
|
74 |
CREATE SEQUENCE xml_documents_id_seq; |
|
75 |
|
|
76 |
CREATE TRIGGER xml_documents_before_insert |
|
77 |
BEFORE INSERT ON xml_documents FOR EACH ROW |
|
78 |
BEGIN |
|
79 |
SELECT xml_documents_id_seq.nextval |
|
80 |
INTO :new.docid |
|
81 |
FROM dual; |
|
82 |
END; |
|
83 |
/ |
|
84 |
|
|
85 |
/* |
|
86 |
* XML Catalog -- tables to store all external sources for XML documents |
|
87 |
*/ |
|
88 |
CREATE TABLE xml_catalog ( |
|
89 |
doctype VARCHAR2(100), |
|
90 |
date_created DATE, |
|
91 |
date_updated DATE, |
|
92 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (doctype) |
|
93 |
); |
|
94 |
|
|
95 |
CREATE TABLE xml_catalog_entities ( |
|
96 |
entity_id NUMBER(20), |
|
97 |
entity_name VARCHAR2(100), |
|
98 |
entity_type VARCHAR2(20), |
|
99 |
source_doctype VARCHAR2(100), |
|
100 |
target_doctype VARCHAR2(100), |
|
101 |
public_id VARCHAR2(100), |
|
102 |
system_id VARCHAR2(1000), |
|
103 |
date_created DATE, |
|
104 |
date_updated DATE, |
|
105 |
CONSTRAINT xml_catalog_entities_pk PRIMARY KEY (entity_id), |
|
106 |
CONSTRAINT xml_catalog_entities_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id), |
|
107 |
CONSTRAINT xml_source_catalog_fk FOREIGN KEY (source_doctype) REFERENCES xml_catalog, |
|
108 |
CONSTRAINT xml_target_catalog_fk FOREIGN KEY (target_doctype) REFERENCES xml_catalog |
|
109 |
); |
|
110 |
|
|
111 |
CREATE SEQUENCE xml_entity_id_seq; |
|
112 |
|
|
113 |
CREATE TRIGGER xml_cat_entities_before_insert |
|
114 |
BEFORE INSERT ON xml_catalog_entities FOR EACH ROW |
|
115 |
BEGIN |
|
116 |
SELECT xml_entity_id_seq.nextval |
|
117 |
INTO :new.entity_id |
|
118 |
FROM dual; |
|
119 |
END; |
|
120 |
/ |
Also available in: Unified diff
Included new features about writing XML documents into db
This includes writing data into db XML Catalog, document data into xml_documents,
comments into xml_nodes, added nodeindex for the order of elements by given parent element.