|
1
|
/**
|
|
2
|
* xmlpackage.sql -- SQL file defining the table structures for showing
|
|
3
|
* relations among files such as one metadata file to another or
|
|
4
|
* a metadatafile or files to data files.
|
|
5
|
*
|
|
6
|
* Purpose: defines table structure for xml relations
|
|
7
|
*
|
|
8
|
* Created: 06 September 2000
|
|
9
|
* Author: Chad Berkley
|
|
10
|
* Organization: National Center for Ecological Analysis and Synthesis
|
|
11
|
* Copyright: 2000 Regents of the University of California and the
|
|
12
|
* National Center for Ecological Analysis and Synthesis
|
|
13
|
* For Details: http://www.nceas.ucsb.edu/
|
|
14
|
* File Info: '$Id: xmlpackage.sql 439 2000-09-06 16:34:15Z berkley $'
|
|
15
|
*
|
|
16
|
*/
|
|
17
|
|
|
18
|
DROP TRIGGER xml_relation_before_insert;
|
|
19
|
DROP TRIGGER xml_parameter_before_insert;
|
|
20
|
DROP SEQUENCE xml_relation_id_seq;
|
|
21
|
DROP SEQUENCE xml_parameter_id_seq;
|
|
22
|
DROP INDEX xml_parameter_index;
|
|
23
|
DROP TABLE xml_parameter;
|
|
24
|
DROP TABLE xml_relation;
|
|
25
|
|
|
26
|
CREATE TABLE xml_relation (
|
|
27
|
relationid NUMBER(20) PRIMARY KEY, -- the unique id to this relation
|
|
28
|
relation VARCHAR2(256) NOT NULL -- the relation: ismetadatafor,
|
|
29
|
);
|
|
30
|
|
|
31
|
|
|
32
|
CREATE TABLE xml_parameter (
|
|
33
|
paramid NUMBER(20) PRIMARY KEY, -- unique id
|
|
34
|
relationid NUMBER(20) NOT NULL, -- link to the relation type
|
|
35
|
param VARCHAR2(512) NOT NULL, -- the content of the parameter
|
|
36
|
paramname VARCHAR2(512) NOT NULL, -- name of the parameter
|
|
37
|
paramtype VARCHAR2(128) NOT NULL, -- object or subject identifier
|
|
38
|
FOREIGN KEY (relationid) REFERENCES xml_relation(relationid)
|
|
39
|
);
|
|
40
|
|
|
41
|
|
|
42
|
CREATE UNIQUE INDEX xml_parameter_index
|
|
43
|
ON xml_parameter(param, paramname, relationid, paramtype);
|
|
44
|
|
|
45
|
CREATE SEQUENCE xml_relation_id_seq;
|
|
46
|
CREATE SEQUENCE xml_parameter_id_seq;
|
|
47
|
|
|
48
|
CREATE TRIGGER xml_relation_before_insert
|
|
49
|
BEFORE INSERT ON xml_relation FOR EACH ROW
|
|
50
|
BEGIN
|
|
51
|
SELECT xml_relation_id_seq.nextval
|
|
52
|
INTO :new.relationid
|
|
53
|
FROM dual;
|
|
54
|
END;
|
|
55
|
/
|
|
56
|
|
|
57
|
CREATE TRIGGER xml_parameter_before_insert
|
|
58
|
BEFORE INSERT ON xml_parameter FOR EACH ROW
|
|
59
|
BEGIN
|
|
60
|
SELECT xml_parameter_id_seq.nextval
|
|
61
|
INTO :new.paramid
|
|
62
|
FROM dual;
|
|
63
|
END;
|
|
64
|
/
|
|
65
|
|
|
66
|
|