Project

General

Profile

metacat / src / xmltables.sql @ 439

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 426 2000-09-01 00:08:36Z bojilova $'
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_revisions_id_seq;
23
DROP SEQUENCE xml_catalog_id_seq;
24

    
25
DROP TRIGGER xml_revisions_before_insert;
26
DROP TRIGGER xml_catalog_before_insert;
27

    
28
DROP TABLE xml_index;
29
DROP TABLE xml_catalog;
30
DROP TABLE xml_access;
31
DROP TABLE xml_revisions;
32
DROP TABLE xml_documents;
33
DROP TABLE xml_nodes;
34
DROP TABLE xml_acc_numbers;
35

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

    
59
CREATE SEQUENCE xml_nodes_id_seq;
60

    
61
/* 
62
 * Documents -- table to store XML documents
63
 */
64
CREATE TABLE xml_documents (
65
        docid                VARCHAR2(250),        -- the document id #
66
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
67
        docname                VARCHAR2(100),        -- usually the root element name
68
        doctype                VARCHAR2(100),        -- public id indicating document type
69
        doctitle        VARCHAR2(1000),        -- title of document if exists
70
        user_owner        VARCHAR2(100),        -- the user owned the document
71
        user_updated        VARCHAR2(100),        -- the user updated the document
72
        date_created        DATE,
73
        date_updated        DATE,
74
        public_access        NUMBER(1),        -- flag for public access
75
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
76
   CONSTRAINT xml_documents_root_fk 
77
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
78
);
79

    
80
/* 
81
 * ACL -- table to store ACL for XML documents by principals
82
 */
83
CREATE TABLE xml_access (
84
        docid                VARCHAR2(250),        -- the document id #
85
        principal_name        VARCHAR2(100),        -- name of user, user group, etc.
86
        principal_type        VARCHAR2(20),        -- like "user", "group", etc.
87
        access_type        NUMBER(1),        -- "read", "write", "all"
88
        begin_time        DATE,                -- the time that access permission begins
89
        end_time        DATE,                -- the time that access permission ends
90
   CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, principal_type, access_type, begin_time),
91
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
92
);
93

    
94
/* 
95
 * Revised Documents -- table to store XML documents saved after an UPDATE
96
 *                    or DELETE
97
 */
98
CREATE TABLE xml_revisions (
99
        revisionid        NUMBER(20),        -- the revision number we are saving
100
        docid                VARCHAR2(250),        -- the document id #
101
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
102
        docname                VARCHAR2(100),        -- usually the root element name
103
        doctype                VARCHAR2(100),        -- public id indicating document type
104
        doctitle        VARCHAR2(1000),        -- title of document if exists
105
        user_owner        VARCHAR2(100),
106
        user_updated        VARCHAR2(100),
107
        date_created        DATE,
108
        date_updated        DATE,
109
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
110
   CONSTRAINT xml_revisions_root_fk 
111
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
112
);
113

    
114
CREATE SEQUENCE xml_revisions_id_seq;
115

    
116
CREATE TRIGGER xml_revisions_before_insert
117
BEFORE INSERT ON xml_revisions FOR EACH ROW
118
BEGIN
119
  SELECT xml_revisions_id_seq.nextval
120
    INTO :new.revisionid
121
    FROM dual;
122
END;
123
/
124

    
125
/* 
126
 * XML Catalog -- table to store all external sources for XML documents
127
 */
128
CREATE TABLE xml_catalog (
129
        catalog_id        NUMBER(20),        -- the id for this catalog entry
130
        entry_type        VARCHAR2(500),        -- the type of this catalog entry
131
                                        -- (e.g., DTD, XSD, XSL)
132
        source_doctype        VARCHAR2(500),        -- the source public_id for transforms
133
        target_doctype        VARCHAR2(500),        -- the target public_id for transforms
134
        public_id        VARCHAR2(500),        -- the unique id for this type
135
        system_id        VARCHAR2(1000),        -- the local location of the object
136
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
137
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
138
);
139

    
140
CREATE SEQUENCE xml_catalog_id_seq;
141

    
142
CREATE TRIGGER xml_catalog_before_insert
143
BEFORE INSERT ON xml_catalog FOR EACH ROW
144
BEGIN
145
  SELECT xml_catalog_id_seq.nextval
146
    INTO :new.catalog_id
147
    FROM dual;
148
END;
149
/
150

    
151
/* 
152
 * Index of Nodes -- table to store precomputed paths through tree for 
153
 * quick searching in structured searches
154
 */
155
CREATE TABLE xml_index (
156
        nodeid                NUMBER(20),        -- the unique node id
157
        path                VARCHAR2(200),        -- precomputed path through tree
158
        docid                VARCHAR2(250),        -- index to the document id
159
        doctype                VARCHAR2(100),        -- public id indicating document type
160
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
161
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
162
   CONSTRAINT xml_index_docid_fk 
163
                FOREIGN KEY (docid) REFERENCES xml_documents
164
);
165

    
166
/* 
167
 * Index of the paths in xml_index 
168
 */
169
CREATE INDEX xml_index_idx1 ON xml_index (path);
170

    
171
/* 
172
 * table to store unique Accession # for every document in 2 parts
173
 */
174
CREATE TABLE xml_acc_numbers (
175
        global_name        VARCHAR2(32),        -- first part of acc #
176
        local_id        NUMBER(20),        -- second part - unique in global name
177
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
178
);