Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 738

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-sqlserver.sql 729 2001-04-12 18:13:50Z berkley $'
13
 *
14
 */
15

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

    
21
CREATE TRIGGER xml_replication_before_insert BEFORE INSERT
22
ON xml_replication FOR EACH ROW
23
BEGIN
24
  SELECT xml_replication_id_seq.nextval
25
    INTO :new.serverid
26
    FROM dual;
27
END;
28
/
29

    
30
/*CREATE SEQUENCE xml_replication_id_seq;*/
31
/*
32
 *Replication -- table to store servers that metacat is replicated to
33
 */
34
CREATE TABLE xml_replication (
35
  serverid      NUMBER(20),
36
  server        VARCHAR2(512),
37
  last_checked  DATE,
38
  replicate     NUMBER(1), 
39
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
40
);  
41

    
42
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
43

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

    
67
/* 
68
 * Indexes of rootnodeid & parentnodeid in xml_nodes
69
 */
70
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
71
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
72

    
73
CREATE SEQUENCE xml_nodes_id_seq;
74

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

    
91
CREATE SEQUENCE xml_catalog_id_seq;
92

    
93
CREATE TRIGGER xml_catalog_before_insert
94
BEFORE INSERT ON xml_catalog FOR EACH ROW
95
BEGIN
96
  SELECT xml_catalog_id_seq.nextval
97
    INTO :new.catalog_id
98
    FROM dual;
99
END;
100
/
101

    
102
/* 
103
 * Documents -- table to store XML documents
104
 */
105
CREATE TABLE xml_documents (
106
        docid                VARCHAR2(250),        -- the document id #
107
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
108
        docname                VARCHAR2(100),        -- usually the root element name
109
        doctype                VARCHAR2(100),        -- public id indicating document type
110
        user_owner        VARCHAR2(100),        -- the user owned the document
111
        user_updated        VARCHAR2(100),        -- the user updated the document
112
        server_location NUMBER(20),        -- the server on which this document resides
113
        rev                 NUMBER(10) DEFAULT 1,--the revision number of the document
114
        date_created        DATE,
115
        date_updated        DATE,
116
        public_access        NUMBER(1),        -- flag for public access
117
        catalog_id      NUMBER(20),        -- reference to xml_catalog 
118
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
119
   CONSTRAINT xml_documents_rep_fk
120
                    FOREIGN KEY (server_location) REFERENCES xml_replication, 
121
   CONSTRAINT xml_documents_root_fk 
122
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
123
   CONSTRAINT xml_documents_catalog_fk 
124
                FOREIGN KEY (catalog_id) REFERENCES xml_catalog
125
);
126

    
127
/* 
128
 * Index of <docid,doctype> in xml_document
129
 */
130
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
131

    
132
/* 
133
 * Revised Documents -- table to store XML documents saved after an UPDATE
134
 *                    or DELETE
135
 */
136
CREATE TABLE xml_revisions (
137
        revisionid        NUMBER(20),        -- the revision number we are saving
138
        docid                VARCHAR2(250),        -- the document id #
139
        rootnodeid        NUMBER(20),        -- reference to root node of the DOM
140
        docname                VARCHAR2(100),        -- usually the root element name
141
        doctype                VARCHAR2(100),        -- public id indicating document type
142
        user_owner        VARCHAR2(100),
143
        user_updated        VARCHAR2(100),
144
        server_location NUMBER(20),
145
        rev                NUMBER(10),
146
        date_created        DATE,
147
        date_updated        DATE,
148
        public_access        NUMBER(1),        -- flag for public access
149
        catalog_id      NUMBER(20),        -- reference to xml_catalog 
150
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
151
   CONSTRAINT xml_revisions_rep_fk
152
                FOREIGN KEY (server_location) REFERENCES xml_replication,
153
   CONSTRAINT xml_revisions_root_fk 
154
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
155
   CONSTRAINT xml_revisions_catalog_fk 
156
                FOREIGN KEY (catalog_id) REFERENCES xml_catalog
157
);
158

    
159
CREATE SEQUENCE xml_revisions_id_seq;
160

    
161
CREATE TRIGGER xml_revisions_before_insert
162
BEFORE INSERT ON xml_revisions FOR EACH ROW
163
BEGIN
164
  SELECT xml_revisions_id_seq.nextval
165
    INTO :new.revisionid
166
    FROM dual;
167
END;
168
/
169

    
170
/* 
171
 * ACL -- table to store ACL for XML documents by principals
172
 */
173
CREATE TABLE xml_access (
174
        docid                VARCHAR2(250),        -- the document id #
175
        accessfileid        VARCHAR2(250),        -- the document id # for the access file
176
        principal_name        VARCHAR2(100),        -- name of user, group, etc.
177
        permission        NUMBER(1),        -- "read", "write", "all"
178
        perm_type        VARCHAR2(32),        -- "allowed" or "denied"
179
        perm_order        VARCHAR2(32),        -- "allow first" or "deny first"
180
        begin_time        DATE,                -- the time that permission begins
181
        end_time        DATE,                -- the time that permission ends
182
        ticket_count        NUMBER(5),        -- ticket counter for that permission
183
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
184
   CONSTRAINT xml_access_accessfileid_fk 
185
                FOREIGN KEY (accessfileid) REFERENCES xml_documents
186
);
187

    
188
/* 
189
 * Index of Nodes -- table to store precomputed paths through tree for 
190
 * quick searching in structured searches
191
 */
192
CREATE TABLE xml_index (
193
        nodeid                NUMBER(20),        -- the unique node id
194
        path                VARCHAR2(200),        -- precomputed path through tree
195
        docid                VARCHAR2(250),        -- index to the document id
196
        doctype                VARCHAR2(100),        -- public id indicating document type
197
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented
198
                                        -- by this row
199
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
200
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
201
   CONSTRAINT xml_index_docid_fk 
202
                FOREIGN KEY (docid) REFERENCES xml_documents
203
);
204

    
205
/* 
206
 * Index of the paths in xml_index 
207
 */
208
CREATE INDEX xml_index_idx1 ON xml_index (path);
209

    
210
/* 
211
 * Sequence to get uniqueID for Accession #
212
 */
213
CREATE SEQUENCE accnum_uniqueid_seq;
214

    
215
CREATE TABLE xml_relation (
216
        relationid    NUMBER(20) PRIMARY KEY, -- unique id
217
        docid         VARCHAR2(250) ,         -- the docid of the package file
218
                                              -- that this relation came from
219
        subject       VARCHAR2(512) NOT NULL, -- the subject of the relation
220
        subdoctype    VARCHAR2(128),                 -- the doctype of the subject
221
        relationship  VARCHAR2(128)  NOT NULL,-- the relationship type
222
        object        VARCHAR2(512) NOT NULL, -- the object of the relation
223
        objdoctype    VARCHAR2(128),          -- the doctype of the object
224
        CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object),
225
        CONSTRAINT xml_relation_docid_fk 
226
                FOREIGN KEY (docid) REFERENCES xml_documents
227
  );
228

    
229
CREATE SEQUENCE xml_relation_id_seq;
230
  
231
CREATE TRIGGER xml_relation_before_insert
232
BEFORE INSERT ON xml_relation FOR EACH ROW
233
BEGIN
234
  SELECT xml_relation_id_seq.nextval
235
    INTO :new.relationid
236
    FROM dual;
237
END;
238
/
239