Project

General

Profile

metacat / src / xmltables.sql @ 755

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 755 2001-05-29 18:09:52Z 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
DROP SEQUENCE xml_relation_id_seq;
25
DROP SEQUENCE xml_replication_id_seq;
26
DROP SEQUENCE accnum_uniqueid_seq;
27
DROP SEQUENCE xml_documents_id_seq;
28

    
29
DROP TRIGGER xml_revisions_before_insert;
30
DROP TRIGGER xml_catalog_before_insert;
31
DROP TRIGGER xml_relation_before_insert;
32
DROP TRIGGER xml_replication_before_insert;
33

    
34
DROP TABLE xml_index;
35
DROP TABLE xml_access;
36
DROP TABLE xml_revisions;
37
DROP TABLE xml_relation;
38
DROP TABLE xml_documents;
39
DROP TABLE xml_nodes;
40
DROP TABLE xml_replication;
41
DROP TABLE xml_catalog;
42

    
43
/*
44
 *Replication -- table to store servers that metacat is replicated to
45
 */
46
CREATE TABLE xml_replication (
47
  serverid      NUMBER(20),
48
  server        VARCHAR2(512),
49
  last_checked  DATE,
50
  replicate     NUMBER(1), 
51
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
52
);  
53
  
54
CREATE SEQUENCE xml_replication_id_seq;
55
CREATE TRIGGER xml_replication_before_insert
56
BEFORE INSERT ON xml_replication FOR EACH ROW
57
BEGIN
58
  SELECT xml_replication_id_seq.nextval
59
    INTO :new.serverid
60
    FROM dual;
61
END;
62
/
63

    
64
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
65

    
66
/* 
67
 * Nodes -- table to store XML Nodes (both elements and attributes)
68
 */
69
CREATE TABLE xml_nodes (
70
        nodeid                NUMBER(20),        -- the unique node id (pk)
71
        nodeindex        NUMBER(10),        -- order of nodes within parent
72
        nodetype        VARCHAR2(20),        -- type (DOCUMENT, COMMENT, PI,
73
                                        -- ELEMENT, ATTRIBUTE, TEXT)
74
        nodename        VARCHAR2(250),        -- the name of an element or attribute
75
        nodedata        VARCHAR2(4000), -- the data for this node (e.g., 
76
                                        -- for TEXT it is the content)
77
        parentnodeid        NUMBER(20),        -- index of the parent of this node
78
        rootnodeid        NUMBER(20),        -- index of the root node of this tree
79
        docid                VARCHAR2(250),        -- index to the document id
80
        date_created        DATE,
81
        date_updated        DATE,
82
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
83
   CONSTRAINT xml_nodes_root_fk 
84
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
85
   CONSTRAINT xml_nodes_parent_fk 
86
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
87
);
88

    
89
/* 
90
 * Indexes of rootnodeid & parentnodeid in xml_nodes
91
 */
92
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
93
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
94
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
95

    
96
CREATE SEQUENCE xml_nodes_id_seq;
97

    
98
/* 
99
 * XML Catalog -- table to store all external sources for XML documents
100
 */
101
CREATE TABLE xml_catalog (
102
        catalog_id        NUMBER(20),        -- the id for this catalog entry
103
        entry_type        VARCHAR2(500),        -- the type of this catalog entry
104
                                        -- (e.g., DTD, XSD, XSL)
105
        source_doctype        VARCHAR2(500),        -- the source public_id for transforms
106
        target_doctype        VARCHAR2(500),        -- the target public_id for transforms
107
        public_id        VARCHAR2(500),        -- the unique id for this type
108
        system_id        VARCHAR2(1000),        -- the local location of the object
109
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
110
   CONSTRAINT xml_catalog_uk UNIQUE 
111
                (entry_type, source_doctype, target_doctype, public_id)
112
);
113

    
114
CREATE SEQUENCE xml_catalog_id_seq;
115

    
116
CREATE TRIGGER xml_catalog_before_insert
117
BEFORE INSERT ON xml_catalog FOR EACH ROW
118
BEGIN
119
  SELECT xml_catalog_id_seq.nextval
120
    INTO :new.catalog_id
121
    FROM dual;
122
END;
123
/
124

    
125
/* 
126
 * Documents -- table to store XML documents
127
 */
128
CREATE TABLE xml_documents (
129
        docid                VARCHAR2(250),        -- the document id #
130
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
131
        docname                VARCHAR2(100),        -- usually the root element name
132
        doctype                VARCHAR2(100),        -- public id indicating document type
133
        user_owner        VARCHAR2(100),        -- the user owned the document
134
        user_updated        VARCHAR2(100),        -- the user updated the document
135
        server_location NUMBER(20),        -- the server on which this document resides
136
        rev                 NUMBER(10) DEFAULT 1,--the revision number of the document
137
        date_created        DATE,
138
        date_updated        DATE,
139
        public_access        NUMBER(1),        -- flag for public access
140
        catalog_id      NUMBER(20),        -- reference to xml_catalog 
141
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
142
   CONSTRAINT xml_documents_rep_fk
143
                    FOREIGN KEY (server_location) REFERENCES xml_replication, 
144
   CONSTRAINT xml_documents_root_fk 
145
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
146
   CONSTRAINT xml_documents_catalog_fk 
147
                FOREIGN KEY (catalog_id) REFERENCES xml_catalog
148
);
149

    
150
/* 
151
 * Sequence to get uniqueID for Accession #
152
 */
153
CREATE SEQUENCE xml_documents_id_seq;
154

    
155
/* 
156
 * Index of <docid,doctype> in xml_document
157
 */
158
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
159

    
160
/* 
161
 * Revised Documents -- table to store XML documents saved after an UPDATE
162
 *                    or DELETE
163
 */
164
CREATE TABLE xml_revisions (
165
        revisionid        NUMBER(20),        -- the revision number we are saving
166
        docid                VARCHAR2(250),        -- the document id #
167
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
168
        docname                VARCHAR2(100),        -- usually the root element name
169
        doctype                VARCHAR2(100),        -- public id indicating document type
170
        user_owner        VARCHAR2(100),
171
        user_updated        VARCHAR2(100),
172
        server_location NUMBER(20),
173
        rev                NUMBER(10),
174
        date_created        DATE,
175
        date_updated        DATE,
176
        public_access        NUMBER(1),        -- flag for public access
177
        catalog_id      NUMBER(20),        -- reference to xml_catalog 
178
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
179
   CONSTRAINT xml_revisions_rep_fk
180
                FOREIGN KEY (server_location) REFERENCES xml_replication,
181
   CONSTRAINT xml_revisions_root_fk 
182
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
183
   CONSTRAINT xml_revisions_catalog_fk 
184
                FOREIGN KEY (catalog_id) REFERENCES xml_catalog
185
);
186

    
187
CREATE SEQUENCE xml_revisions_id_seq;
188

    
189
CREATE TRIGGER xml_revisions_before_insert
190
BEFORE INSERT ON xml_revisions FOR EACH ROW
191
BEGIN
192
  SELECT xml_revisions_id_seq.nextval
193
    INTO :new.revisionid
194
    FROM dual;
195
END;
196
/
197

    
198
/* 
199
 * ACL -- table to store ACL for XML documents by principals
200
 */
201
CREATE TABLE xml_access (
202
        docid                VARCHAR2(250),        -- the document id #
203
        accessfileid        VARCHAR2(250),        -- the document id # for the access file
204
        principal_name        VARCHAR2(100),        -- name of user, group, etc.
205
        permission        NUMBER(1),        -- "read", "write", "all"
206
        perm_type        VARCHAR2(32),        -- "allowed" or "denied"
207
        perm_order        VARCHAR2(32),        -- "allow first" or "deny first"
208
        begin_time        DATE,                -- the time that permission begins
209
        end_time        DATE,                -- the time that permission ends
210
        ticket_count        NUMBER(5),        -- ticket counter for that permission
211
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
212
   CONSTRAINT xml_access_accessfileid_fk 
213
                FOREIGN KEY (accessfileid) REFERENCES xml_documents
214
);
215

    
216
/* 
217
 * Index of Nodes -- table to store precomputed paths through tree for 
218
 * quick searching in structured searches
219
 */
220
CREATE TABLE xml_index (
221
        nodeid                NUMBER(20),        -- the unique node id
222
        path                VARCHAR2(200),        -- precomputed path through tree
223
        docid                VARCHAR2(250),        -- index to the document id
224
        doctype                VARCHAR2(100),        -- public id indicating document type
225
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented
226
                                        -- by this row
227
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
228
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
229
   CONSTRAINT xml_index_docid_fk 
230
                FOREIGN KEY (docid) REFERENCES xml_documents
231
);
232

    
233
/* 
234
 * Index of the paths in xml_index 
235
 */
236
CREATE INDEX xml_index_idx1 ON xml_index (path);
237

    
238
CREATE TABLE xml_relation (
239
        relationid    NUMBER(20) PRIMARY KEY, -- unique id
240
        docid         VARCHAR2(250),          -- the docid of the package file
241
                                              -- that this relation came from
242
        packagetype   VARCHAR2(250),          -- the type of the package
243
        subject       VARCHAR2(512) NOT NULL, -- the subject of the relation
244
        subdoctype    VARCHAR2(128),          -- the doctype of the subject
245
        relationship  VARCHAR2(128)  NOT NULL,-- the relationship type
246
        object        VARCHAR2(512) NOT NULL, -- the object of the relation
247
        objdoctype    VARCHAR2(128),          -- the doctype of the object
248
        CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object),
249
        CONSTRAINT xml_relation_docid_fk 
250
                FOREIGN KEY (docid) REFERENCES xml_documents
251
  );
252

    
253
CREATE SEQUENCE xml_relation_id_seq;
254
  
255
CREATE TRIGGER xml_relation_before_insert
256
BEFORE INSERT ON xml_relation FOR EACH ROW
257
BEGIN
258
  SELECT xml_relation_id_seq.nextval
259
    INTO :new.relationid
260
    FROM dual;
261
END;
262
/
263