Revision 689
Added by bojilova over 23 years ago
| xmltables.sql | ||
|---|---|---|
| 34 | 34 |
DROP TABLE xml_catalog; |
| 35 | 35 |
DROP TABLE xml_access; |
| 36 | 36 |
DROP TABLE xml_revisions; |
| 37 |
DROP TABLE xml_relation; |
|
| 37 | 38 |
DROP TABLE xml_documents; |
| 38 | 39 |
DROP TABLE xml_nodes; |
| 39 |
DROP TABLE xml_acc_numbers; |
|
| 40 |
DROP TABLE xml_relation; |
|
| 41 | 40 |
DROP TABLE xml_replication; |
| 42 | 41 |
|
| 43 | 42 |
/* |
| ... | ... | |
| 105 | 104 |
doctitle VARCHAR2(1000), -- title of document if exists |
| 106 | 105 |
user_owner VARCHAR2(100), -- the user owned the document |
| 107 | 106 |
user_updated VARCHAR2(100), -- the user updated the document |
| 108 |
server_location NUMBER(20), -- the server on which this document resides
|
|
| 109 |
rev NUMBER(10) DEFAULT 1, --the revision number of the document
|
|
| 107 |
server_location NUMBER(20), -- the server on which this document resides
|
|
| 108 |
rev NUMBER(10) DEFAULT 1, --the revision number of the document
|
|
| 110 | 109 |
date_created DATE, |
| 111 | 110 |
date_updated DATE, |
| 112 | 111 |
public_access NUMBER(1) DEFAULT 1, -- flag for public access |
| 113 |
updated NUMBER(1) DEFAULT 0, |
|
| 114 | 112 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
| 115 | 113 |
CONSTRAINT xml_documents_rep_fk |
| 116 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
| 114 |
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
|
| 117 | 115 |
CONSTRAINT xml_documents_root_fk |
| 118 | 116 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes |
| 119 | 117 |
); |
| ... | ... | |
| 136 | 134 |
begin_time DATE, -- the time that permission begins |
| 137 | 135 |
end_time DATE, -- the time that permission ends |
| 138 | 136 |
ticket_count NUMBER(5), -- ticket counter for that permission |
| 139 |
CONSTRAINT xml_access_pk UNIQUE
|
|
| 140 |
(docid, accessfileid, principal_name, permission, perm_type),
|
|
| 141 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
|
|
| 137 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
|
|
| 138 |
CONSTRAINT xml_access_accessfileid_fk
|
|
| 139 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents
|
|
| 142 | 140 |
); |
| 143 | 141 |
|
| 144 | 142 |
/* |
| ... | ... | |
| 155 | 153 |
user_owner VARCHAR2(100), |
| 156 | 154 |
user_updated VARCHAR2(100), |
| 157 | 155 |
server_location NUMBER(20), |
| 158 |
rev NUMBER(10),
|
|
| 156 |
rev NUMBER(10),
|
|
| 159 | 157 |
date_created DATE, |
| 160 | 158 |
date_updated DATE, |
| 161 | 159 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
| 162 | 160 |
CONSTRAINT xml_revisions_rep_fk |
| 163 |
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
|
| 161 |
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
|
| 164 | 162 |
CONSTRAINT xml_revisions_root_fk |
| 165 | 163 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes |
| 166 | 164 |
); |
| ... | ... | |
| 188 | 186 |
public_id VARCHAR2(500), -- the unique id for this type |
| 189 | 187 |
system_id VARCHAR2(1000), -- the local location of the object |
| 190 | 188 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
| 191 |
CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id) |
|
| 189 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
| 190 |
(entry_type, source_doctype, target_doctype, public_id) |
|
| 192 | 191 |
); |
| 193 | 192 |
|
| 194 | 193 |
CREATE SEQUENCE xml_catalog_id_seq; |
| ... | ... | |
| 211 | 210 |
path VARCHAR2(200), -- precomputed path through tree |
| 212 | 211 |
docid VARCHAR2(250), -- index to the document id |
| 213 | 212 |
doctype VARCHAR2(100), -- public id indicating document type |
| 214 |
parentnodeid NUMBER(20), -- id of the parent of the node represented by this row |
|
| 213 |
parentnodeid NUMBER(20), -- id of the parent of the node represented |
|
| 214 |
-- by this row |
|
| 215 | 215 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
| 216 | 216 |
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes, |
| 217 | 217 |
CONSTRAINT xml_index_docid_fk |
| ... | ... | |
| 229 | 229 |
CREATE SEQUENCE accnum_uniqueid_seq; |
| 230 | 230 |
|
| 231 | 231 |
CREATE TABLE xml_relation ( |
| 232 |
relationid NUMBER(20) PRIMARY KEY, -- unique id |
|
| 233 |
docid VARCHAR2(250) , -- the docid of the package file that |
|
| 234 |
-- this relation came from |
|
| 235 |
subject VARCHAR2(512) NOT NULL, -- the subject of the relation |
|
| 236 |
subdoctype VARCHAR2(128), -- the doctype of the subject |
|
| 237 |
relationship VARCHAR2(128) NOT NULL, -- the relationship type |
|
| 238 |
object VARCHAR2(512) NOT NULL, -- the object of the relation |
|
| 239 |
objdoctype VARCHAR2(128), -- the doctype of the object |
|
| 240 |
CONSTRAINT xml_relation_unk UNIQUE (subject, relationship, object) |
|
| 232 |
relationid NUMBER(20) PRIMARY KEY, -- unique id |
|
| 233 |
docid VARCHAR2(250) , -- the docid of the package file |
|
| 234 |
-- that this relation came from |
|
| 235 |
subject VARCHAR2(512) NOT NULL, -- the subject of the relation |
|
| 236 |
subdoctype VARCHAR2(128), -- the doctype of the subject |
|
| 237 |
relationship VARCHAR2(128) NOT NULL,-- the relationship type |
|
| 238 |
object VARCHAR2(512) NOT NULL, -- the object of the relation |
|
| 239 |
objdoctype VARCHAR2(128), -- the doctype of the object |
|
| 240 |
CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object), |
|
| 241 |
CONSTRAINT xml_relation_docid_fk |
|
| 242 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
| 241 | 243 |
); |
| 242 | 244 |
|
| 243 | 245 |
CREATE SEQUENCE xml_relation_id_seq; |
Also available in: Unified diff
cleared the UNIQUE constraint in xml_access - moved in AccessControlList
added FOREIGN keys for xml_relation.docid and xml_access.accessfileid referencing xml_dociments.docid