Project

General

Profile

metacat / src / xmltables.sql @ 166

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
 *    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: xmltables.sql 166 2000-06-16 01:58:30Z jones $'
13
 *
14
 */
15

    
16
/*
17
 * Drop all of the objects in proper order
18
 */
19
set echo off
20

    
21
DROP SEQUENCE xml_nodes_id_seq;
22
DROP SEQUENCE xml_catalog_id_seq;
23

    
24
DROP TRIGGER xml_nodes_before_insert;
25
DROP TRIGGER xml_catalog_before_insert;
26

    
27
DROP TABLE xml_index;
28
DROP TABLE xml_catalog;
29
DROP TABLE xml_documents;
30
DROP TABLE xml_nodes;
31
DROP TABLE xml_acc_numbers;
32

    
33
/* 
34
 * Nodes -- table to store XML Nodes (both elements and attributes)
35
 */
36
CREATE TABLE xml_nodes (
37
        nodeid                NUMBER(20),        -- the unique node id (pk)
38
        nodeindex        NUMBER(10),        -- order of nodes within parent
39
        nodetype        VARCHAR2(20),        -- type (DOCUMENT, COMMENT, PI,
40
                                        -- ELEMENT, ATTRIBUTE, TEXT)
41
        nodename        VARCHAR2(250),        -- the name of an element or attribute
42
        nodedata        VARCHAR2(4000), -- the data for this node (e.g., 
43
                                        -- for TEXT it is the content)
44
        parentnodeid        NUMBER(20),        -- index of the parent of this node
45
        rootnodeid        NUMBER(20),        -- index of the root node of this tree
46
        docid                VARCHAR2(55),        -- index to the document id
47
        date_created        DATE,
48
        date_updated        DATE,
49
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
50
   CONSTRAINT xml_nodes_root_fk 
51
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
52
   CONSTRAINT xml_nodes_parent_fk 
53
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
54
);
55

    
56
CREATE SEQUENCE xml_nodes_id_seq;
57

    
58
CREATE TRIGGER xml_nodes_before_insert
59
BEFORE INSERT ON xml_nodes FOR EACH ROW
60
BEGIN
61
  SELECT xml_nodes_id_seq.nextval
62
    INTO :new.nodeid
63
    FROM dual;
64
END;
65
/
66

    
67
/* 
68
 * Documents -- table to store XML documents
69
 */
70
CREATE TABLE xml_documents (
71
        docid                VARCHAR2(250),        -- the document id #
72
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
73
        docname                VARCHAR2(100),        -- usually the root element name
74
        doctype                VARCHAR2(100),        -- public id indicating document type
75
        doctitle        VARCHAR2(1000),        -- title of document if exists
76
        date_created        DATE,
77
        date_updated        DATE,
78
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
79
   CONSTRAINT xml_documents_root_fk 
80
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
81
);
82

    
83
/* 
84
 * XML Catalog -- table to store all external sources for XML documents
85
 */
86
CREATE TABLE xml_catalog (
87
        catalog_id        NUMBER(20),        -- the id for this catalog entry
88
        entry_type        VARCHAR2(500),        -- the type of this catalog entry
89
                                        -- (e.g., DTD, XSD, XSL)
90
        source_doctype        VARCHAR2(500),        -- the source public_id for transforms
91
        target_doctype        VARCHAR2(500),        -- the target public_id for transforms
92
        public_id        VARCHAR2(500),        -- the unique id for this type
93
        system_id        VARCHAR2(1000),        -- the local location of the object
94
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
95
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
96
);
97

    
98
CREATE SEQUENCE xml_catalog_id_seq;
99

    
100
CREATE TRIGGER xml_catalog_before_insert
101
BEFORE INSERT ON xml_catalog FOR EACH ROW
102
BEGIN
103
  SELECT xml_catalog_id_seq.nextval
104
    INTO :new.catalog_id
105
    FROM dual;
106
END;
107
/
108

    
109
/* 
110
 * Index of Nodes -- table to store precomputed paths through tree for 
111
 * quick searching in structured searches
112
 */
113
CREATE TABLE xml_index (
114
        nodeid                NUMBER(20),        -- the unique node id
115
        path                VARCHAR2(20),        -- precomputed path through tree
116
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
117
   CONSTRAINT xml_nodes_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes
118
);
119

    
120
/* 
121
 * table to store unique Accession # for every document in 2 parts
122
 */
123
CREATE TABLE xml_acc_numbers (
124
        global_name        VARCHAR2(32),        -- first part of acc #
125
        local_id        NUMBER(20),        -- second part - unique in global name
126
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
127
);