Revision 759
Added by bojilova almost 23 years ago
| xmltables.sql | ||
|---|---|---|
| 25 | 25 |
DROP SEQUENCE xml_replication_id_seq; |
| 26 | 26 |
DROP SEQUENCE accnum_uniqueid_seq; |
| 27 | 27 |
DROP SEQUENCE xml_documents_id_seq; |
| 28 |
|
|
| 28 |
DROP SEQUENCE accession_number_id_seq; |
|
| 29 |
|
|
| 30 |
/* Drop triggers are not necessary */ |
|
| 31 |
DROP TRIGGER xml_nodes_before_insert; |
|
| 29 | 32 |
DROP TRIGGER xml_revisions_before_insert; |
| 30 | 33 |
DROP TRIGGER xml_catalog_before_insert; |
| 31 | 34 |
DROP TRIGGER xml_relation_before_insert; |
| 32 | 35 |
DROP TRIGGER xml_replication_before_insert; |
| 36 |
DROP TRIGGER accession_number_before_insert; |
|
| 33 | 37 |
|
| 34 | 38 |
DROP TABLE xml_index; |
| 35 | 39 |
DROP TABLE xml_access; |
| ... | ... | |
| 39 | 43 |
DROP TABLE xml_nodes; |
| 40 | 44 |
DROP TABLE xml_replication; |
| 41 | 45 |
DROP TABLE xml_catalog; |
| 46 |
DROP TABLE accession_number; |
|
| 42 | 47 |
|
| 43 | 48 |
/* |
| 44 | 49 |
*Replication -- table to store servers that metacat is replicated to |
| ... | ... | |
| 66 | 71 |
/* |
| 67 | 72 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
| 68 | 73 |
*/ |
| 74 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
| 69 | 75 |
CREATE TABLE xml_nodes ( |
| 70 | 76 |
nodeid NUMBER(20), -- the unique node id (pk) |
| 71 | 77 |
nodeindex NUMBER(10), -- order of nodes within parent |
| ... | ... | |
| 85 | 91 |
CONSTRAINT xml_nodes_parent_fk |
| 86 | 92 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
| 87 | 93 |
); |
| 88 |
|
|
| 94 |
CREATE TRIGGER xml_nodes_before_insert |
|
| 95 |
BEFORE INSERT ON xml_nodes FOR EACH ROW |
|
| 96 |
BEGIN |
|
| 97 |
SELECT xml_nodes_id_seq.nextval |
|
| 98 |
INTO :new.nodeid |
|
| 99 |
FROM dual; |
|
| 100 |
END; |
|
| 101 |
/ |
|
| 102 |
|
|
| 89 | 103 |
/* |
| 90 |
* Indexes of rootnodeid & parentnodeid in xml_nodes
|
|
| 104 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
|
|
| 91 | 105 |
*/ |
| 92 | 106 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
| 93 | 107 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
| 94 | 108 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
| 95 | 109 |
|
| 96 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
| 97 |
|
|
| 98 | 110 |
/* |
| 99 | 111 |
* XML Catalog -- table to store all external sources for XML documents |
| 100 | 112 |
*/ |
| ... | ... | |
| 148 | 160 |
); |
| 149 | 161 |
|
| 150 | 162 |
/* |
| 151 |
* Sequence to get uniqueID for Accession # |
|
| 152 |
*/ |
|
| 153 |
CREATE SEQUENCE xml_documents_id_seq; |
|
| 154 |
|
|
| 155 |
/* |
|
| 156 | 163 |
* Index of <docid,doctype> in xml_document |
| 157 | 164 |
*/ |
| 158 | 165 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
| ... | ... | |
| 258 | 265 |
SELECT xml_relation_id_seq.nextval |
| 259 | 266 |
INTO :new.relationid |
| 260 | 267 |
FROM dual; |
| 261 |
END; |
|
| 268 |
END;
|
|
| 262 | 269 |
/ |
| 263 | 270 |
|
| 271 |
/* |
|
| 272 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
|
| 273 |
*/ |
|
| 274 |
CREATE SEQUENCE accession_number_id_seq; |
|
| 275 |
CREATE TABLE accession_number ( |
|
| 276 |
uniqueid NUMBER(20) PRIMARY KEY, |
|
| 277 |
site_code VARCHAR2(100), |
|
| 278 |
date_created DATE |
|
| 279 |
); |
|
| 280 |
CREATE TRIGGER accession_number_before_insert |
|
| 281 |
BEFORE INSERT ON accession_number FOR EACH ROW |
|
| 282 |
BEGIN |
|
| 283 |
SELECT accession_number_id_seq.nextval |
|
| 284 |
INTO :new.uniqueid |
|
| 285 |
FROM dual; |
|
| 286 |
END; |
|
| 287 |
/ |
|
| 288 |
|
|
Also available in: Unified diff
several changes were needed related to the change of the unique id generation and use