Project

General

Profile

1
/*
2
 * 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
 *      Version: '$Id: xmltables.sql 20 2000-04-11 18:22:35Z jones $'
10
 *
11
 */
12

    
13
/*
14
 * Drop all of the objects in proper order
15
 */
16
DROP SEQUENCE xml_nodes_id_seq;
17
DROP TRIGGER xml_nodes_before_insert;
18
DROP TABLE xml_documents;
19
DROP TABLE xml_nodes;
20

    
21
/* 
22
 * Nodes -- table to store XML Nodes (both elements and attributes)
23
 */
24
CREATE TABLE xml_nodes (
25
	nodeid		NUMBER(20),
26
	parentnodeid	NUMBER(20),
27
	nodetype	VARCHAR2(2000),
28
	nodename	VARCHAR2(2000),
29
	nodedata	VARCHAR2(2000),
30
	date_created	DATE,
31
	date_updated	DATE,
32
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
33
   CONSTRAINT xml_nodes_parent_fk 
34
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
35
);
36

    
37
CREATE SEQUENCE xml_nodes_id_seq;
38

    
39
CREATE TRIGGER xml_nodes_before_insert
40
BEFORE INSERT ON xml_nodes FOR EACH ROW
41
BEGIN
42
  SELECT xml_nodes_id_seq.nextval
43
    INTO :new.nodeid
44
    FROM dual;
45
END;
46
/
47

    
48
/* 
49
 * Documents -- table to store XML document catalog
50
 */
51
CREATE TABLE xml_documents (
52
	docid		NUMBER(20),
53
	rootnodeid	NUMBER(20),
54
	docname		VARCHAR2(1000),
55
	doctype		VARCHAR2(1000),
56
	date_created	DATE,
57
	date_updated	DATE,
58
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
59
   CONSTRAINT xml_documents_root_fk 
60
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
61
);
62

    
(12-12/12)