Project

General

Profile

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
  
(9-9/10)