Project

General

Profile

Revision 759

Added by bojilova almost 20 years ago

several changes were needed related to the change of the unique id generation and use

View differences:

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