Project

General

Profile

metacat / src / xmltables_postgres.sql @ 741

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: 08 May 2001 
7
 *       Author: John Harris
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
 *
12
 *        this is sql script does the same as the sql script named 
13
 *        xmltables.sql except that this script is to be use to 
14
 *        create the database tables on a Postgresql backend rather
15
 *        than an Oracle Backend
16
 */
17

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

    
23
DROP SEQUENCE xml_nodes_id_seq;
24
DROP SEQUENCE xml_revisions_id_seq;
25
DROP SEQUENCE xml_catalog_id_seq;
26
DROP SEQUENCE xml_relation_id_seq;
27
DROP SEQUENCE xml_replication_id_seq;
28
DROP SEQUENCE accnum_uniqueid_seq;
29

    
30

    
31
DROP TABLE xml_index;
32
DROP TABLE xml_access;
33
DROP TABLE xml_revisions;
34
DROP TABLE xml_relation;
35
DROP TABLE xml_documents;
36
DROP TABLE xml_nodes;
37
DROP TABLE xml_replication;
38
DROP TABLE xml_catalog;
39

    
40
/*
41
 *Replication -- table to store servers that metacat is replicated to
42
 */
43

    
44
CREATE SEQUENCE xml_replication_id_seq;
45

    
46
CREATE TABLE xml_replication (
47
  serverid     INT8  default nextval('xml_replication_id_seq'), 
48
  server        VARCHAR(512),
49
  last_checked  DATE,
50
  replicate     INT8, 
51
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
52
);  
53

    
54
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
55

    
56
/* 
57
 * Nodes -- table to store XML Nodes (both elements and attributes)
58
 */
59
CREATE TABLE xml_nodes (
60
        nodeid                INT8,                 -- the unique node id (pk)
61
        nodeindex        INT8,                -- order of nodes within parent
62
        nodetype        VARCHAR(20),        -- type (DOCUMENT, COMMENT, PI,
63
                                        -- ELEMENT, ATTRIBUTE, TEXT)
64
        nodename        VARCHAR(250),        -- the name of an element or attribute
65
        nodedata        VARCHAR(4000),         -- the data for this node (e.g., 
66
                                        -- for TEXT it is the content)
67
        parentnodeid        INT8,                -- index of the parent of this node
68
        rootnodeid        INT8,                -- index of the root node of this tree
69
        docid                VARCHAR(250),        -- index to the document id
70
        date_created        DATE,
71
        date_updated        DATE,
72
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
73
   CONSTRAINT xml_nodes_root_fk 
74
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
75
   CONSTRAINT xml_nodes_parent_fk 
76
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
77
);
78

    
79
/* 
80
 * Indexes of rootnodeid & parentnodeid in xml_nodes
81
 */
82
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
83
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
84
CREATE SEQUENCE xml_nodes_id_seq;
85

    
86

    
87
/* 
88
 * XML Catalog -- table to store all external sources for XML documents
89
 */
90
CREATE SEQUENCE xml_catalog_id_seq;
91

    
92
CREATE TABLE xml_catalog (
93
        catalog_id INT8 default nextval('xml_catalog_id_seq'), -- the id for this catalog entry
94
        entry_type        VARCHAR(500),        -- the type of this catalog entry
95
                                        -- (e.g., DTD, XSD, XSL)
96
        source_doctype        VARCHAR(500),        -- the source public_id for transforms
97
        target_doctype        VARCHAR(500),        -- the target public_id for transforms
98
        public_id        VARCHAR(500),        -- the unique id for this type
99
        system_id        VARCHAR(1000),        -- the local location of the object
100
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
101
   CONSTRAINT xml_catalog_uk UNIQUE  (entry_type, source_doctype, target_doctype, public_id)
102
);
103

    
104

    
105

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

    
131
/* 
132
 * Index of <docid,doctype> in xml_document
133
 */
134
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
135

    
136
/* 
137
 * Revised Documents -- table to store XML documents saved after an UPDATE
138
 *                    or DELETE
139
 */
140

    
141

    
142
CREATE SEQUENCE xml_revisions_id_seq;
143
CREATE TABLE xml_revisions (
144
        revisionid        INT8  default nextval('xml_revisions_id_seq'), -- the revision number we are saving
145
        docid                VARCHAR(250),        -- the document id #
146
        rootnodeid        INT8,        -- reference to root node of the DOM
147
        docname                VARCHAR(100),        -- usually the root element name
148
        doctype                VARCHAR(100),        -- public id indicating document type
149
        user_owner        VARCHAR(100),
150
        user_updated        VARCHAR(100),
151
        server_location INT8,
152
        rev                INT8,
153
        date_created        DATE,
154
        date_updated        DATE,
155
        public_access        INT8,        -- flag for public access
156
        catalog_id      INT8,        -- reference to xml_catalog 
157
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
158
   CONSTRAINT xml_revisions_rep_fk
159
                FOREIGN KEY (server_location) REFERENCES xml_replication,
160
   CONSTRAINT xml_revisions_root_fk 
161
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
162
   CONSTRAINT xml_revisions_catalog_fk 
163
                FOREIGN KEY (catalog_id) REFERENCES xml_catalog
164
);
165

    
166

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

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

    
202
/* 
203
 * Index of the paths in xml_index 
204
 */
205
CREATE INDEX xml_index_idx1 ON xml_index (path);
206

    
207
/* 
208
 * Sequence to get uniqueID for Accession #
209
 */
210
CREATE SEQUENCE accnum_uniqueid_seq;
211
CREATE SEQUENCE xml_relation_id_seq;
212

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

    
227