Revision 698
Added by bojilova about 23 years ago
| xmltables.sql | ||
|---|---|---|
| 31 | 31 |
DROP TRIGGER xml_replication_before_insert; |
| 32 | 32 |
|
| 33 | 33 |
DROP TABLE xml_index; |
| 34 |
DROP TABLE xml_catalog; |
|
| 35 | 34 |
DROP TABLE xml_access; |
| 36 | 35 |
DROP TABLE xml_revisions; |
| 37 | 36 |
DROP TABLE xml_relation; |
| 38 | 37 |
DROP TABLE xml_documents; |
| 39 | 38 |
DROP TABLE xml_nodes; |
| 40 | 39 |
DROP TABLE xml_replication; |
| 40 |
DROP TABLE xml_catalog; |
|
| 41 | 41 |
|
| 42 | 42 |
/* |
| 43 | 43 |
*Replication -- table to store servers that metacat is replicated to |
| ... | ... | |
| 94 | 94 |
CREATE SEQUENCE xml_nodes_id_seq; |
| 95 | 95 |
|
| 96 | 96 |
/* |
| 97 |
* XML Catalog -- table to store all external sources for XML documents |
|
| 98 |
*/ |
|
| 99 |
CREATE TABLE xml_catalog ( |
|
| 100 |
catalog_id NUMBER(20), -- the id for this catalog entry |
|
| 101 |
entry_type VARCHAR2(500), -- the type of this catalog entry |
|
| 102 |
-- (e.g., DTD, XSD, XSL) |
|
| 103 |
source_doctype VARCHAR2(500), -- the source public_id for transforms |
|
| 104 |
target_doctype VARCHAR2(500), -- the target public_id for transforms |
|
| 105 |
public_id VARCHAR2(500), -- the unique id for this type |
|
| 106 |
system_id VARCHAR2(1000), -- the local location of the object |
|
| 107 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
| 108 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
| 109 |
(entry_type, source_doctype, target_doctype, public_id) |
|
| 110 |
); |
|
| 111 |
|
|
| 112 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
| 113 |
|
|
| 114 |
CREATE TRIGGER xml_catalog_before_insert |
|
| 115 |
BEFORE INSERT ON xml_catalog FOR EACH ROW |
|
| 116 |
BEGIN |
|
| 117 |
SELECT xml_catalog_id_seq.nextval |
|
| 118 |
INTO :new.catalog_id |
|
| 119 |
FROM dual; |
|
| 120 |
END; |
|
| 121 |
/ |
|
| 122 |
|
|
| 123 |
/* |
|
| 97 | 124 |
* Documents -- table to store XML documents |
| 98 | 125 |
*/ |
| 99 | 126 |
CREATE TABLE xml_documents ( |
| ... | ... | |
| 104 | 131 |
user_owner VARCHAR2(100), -- the user owned the document |
| 105 | 132 |
user_updated VARCHAR2(100), -- the user updated the document |
| 106 | 133 |
server_location NUMBER(20), -- the server on which this document resides |
| 107 |
rev NUMBER(10) DEFAULT 1, --the revision number of the document
|
|
| 134 |
rev NUMBER(10) DEFAULT 1,--the revision number of the document
|
|
| 108 | 135 |
date_created DATE, |
| 109 | 136 |
date_updated DATE, |
| 110 |
public_access NUMBER(1) DEFAULT 1, -- flag for public access |
|
| 137 |
public_access NUMBER(1), -- flag for public access |
|
| 138 |
catalog_id NUMBER(20), -- reference to xml_catalog |
|
| 111 | 139 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
| 112 | 140 |
CONSTRAINT xml_documents_rep_fk |
| 113 | 141 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
| 114 | 142 |
CONSTRAINT xml_documents_root_fk |
| 115 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes |
|
| 143 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
| 144 |
CONSTRAINT xml_documents_catalog_fk |
|
| 145 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
| 116 | 146 |
); |
| 117 | 147 |
|
| 118 | 148 |
/* |
| ... | ... | |
| 121 | 151 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
| 122 | 152 |
|
| 123 | 153 |
/* |
| 124 |
* ACL -- table to store ACL for XML documents by principals |
|
| 125 |
*/ |
|
| 126 |
CREATE TABLE xml_access ( |
|
| 127 |
docid VARCHAR2(250), -- the document id # |
|
| 128 |
accessfileid VARCHAR2(250), -- the document id # for the access file |
|
| 129 |
principal_name VARCHAR2(100), -- name of user, group, etc. |
|
| 130 |
permission NUMBER(1), -- "read", "write", "all" |
|
| 131 |
perm_type VARCHAR2(32), -- "allowed" or "denied" |
|
| 132 |
perm_order VARCHAR2(32), -- "allow first" or "deny first" |
|
| 133 |
begin_time DATE, -- the time that permission begins |
|
| 134 |
end_time DATE, -- the time that permission ends |
|
| 135 |
ticket_count NUMBER(5), -- ticket counter for that permission |
|
| 136 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
|
| 137 |
CONSTRAINT xml_access_accessfileid_fk |
|
| 138 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
|
| 139 |
); |
|
| 140 |
|
|
| 141 |
/* |
|
| 142 | 154 |
* Revised Documents -- table to store XML documents saved after an UPDATE |
| 143 | 155 |
* or DELETE |
| 144 | 156 |
*/ |
| ... | ... | |
| 154 | 166 |
rev NUMBER(10), |
| 155 | 167 |
date_created DATE, |
| 156 | 168 |
date_updated DATE, |
| 169 |
public_access NUMBER(1), -- flag for public access |
|
| 170 |
catalog_id NUMBER(20), -- reference to xml_catalog |
|
| 157 | 171 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
| 158 | 172 |
CONSTRAINT xml_revisions_rep_fk |
| 159 | 173 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
| 160 | 174 |
CONSTRAINT xml_revisions_root_fk |
| 161 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes |
|
| 175 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
| 176 |
CONSTRAINT xml_revisions_catalog_fk |
|
| 177 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
| 162 | 178 |
); |
| 163 | 179 |
|
| 164 | 180 |
CREATE SEQUENCE xml_revisions_id_seq; |
| ... | ... | |
| 173 | 189 |
/ |
| 174 | 190 |
|
| 175 | 191 |
/* |
| 176 |
* XML Catalog -- table to store all external sources for XML documents
|
|
| 192 |
* ACL -- table to store ACL for XML documents by principals
|
|
| 177 | 193 |
*/ |
| 178 |
CREATE TABLE xml_catalog ( |
|
| 179 |
catalog_id NUMBER(20), -- the id for this catalog entry |
|
| 180 |
entry_type VARCHAR2(500), -- the type of this catalog entry |
|
| 181 |
-- (e.g., DTD, XSD, XSL) |
|
| 182 |
source_doctype VARCHAR2(500), -- the source public_id for transforms |
|
| 183 |
target_doctype VARCHAR2(500), -- the target public_id for transforms |
|
| 184 |
public_id VARCHAR2(500), -- the unique id for this type |
|
| 185 |
system_id VARCHAR2(1000), -- the local location of the object |
|
| 186 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
| 187 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
| 188 |
(entry_type, source_doctype, target_doctype, public_id) |
|
| 194 |
CREATE TABLE xml_access ( |
|
| 195 |
docid VARCHAR2(250), -- the document id # |
|
| 196 |
accessfileid VARCHAR2(250), -- the document id # for the access file |
|
| 197 |
principal_name VARCHAR2(100), -- name of user, group, etc. |
|
| 198 |
permission NUMBER(1), -- "read", "write", "all" |
|
| 199 |
perm_type VARCHAR2(32), -- "allowed" or "denied" |
|
| 200 |
perm_order VARCHAR2(32), -- "allow first" or "deny first" |
|
| 201 |
begin_time DATE, -- the time that permission begins |
|
| 202 |
end_time DATE, -- the time that permission ends |
|
| 203 |
ticket_count NUMBER(5), -- ticket counter for that permission |
|
| 204 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
|
| 205 |
CONSTRAINT xml_access_accessfileid_fk |
|
| 206 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
|
| 189 | 207 |
); |
| 190 | 208 |
|
| 191 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
| 192 |
|
|
| 193 |
CREATE TRIGGER xml_catalog_before_insert |
|
| 194 |
BEFORE INSERT ON xml_catalog FOR EACH ROW |
|
| 195 |
BEGIN |
|
| 196 |
SELECT xml_catalog_id_seq.nextval |
|
| 197 |
INTO :new.catalog_id |
|
| 198 |
FROM dual; |
|
| 199 |
END; |
|
| 200 |
/ |
|
| 201 |
|
|
| 202 | 209 |
/* |
| 203 | 210 |
* Index of Nodes -- table to store precomputed paths through tree for |
| 204 | 211 |
* quick searching in structured searches |
Also available in: Unified diff
- new attribute catalog_id added in xml_documents and xml_revisions
as a FK to xml_catalog