/** * xmlpackage.sql -- SQL file defining the table structures for showing * relations among files such as one metadata file to another or * a metadatafile or files to data files. * * Purpose: defines table structure for xml relations * * Created: 06 September 2000 * Author: Chad Berkley * Organization: National Center for Ecological Analysis and Synthesis * Copyright: 2000 Regents of the University of California and the * National Center for Ecological Analysis and Synthesis * For Details: http://www.nceas.ucsb.edu/ * File Info: '$Id: xmlpackage.sql 439 2000-09-06 16:34:15Z berkley $' * */ DROP TRIGGER xml_relation_before_insert; DROP TRIGGER xml_parameter_before_insert; DROP SEQUENCE xml_relation_id_seq; DROP SEQUENCE xml_parameter_id_seq; DROP INDEX xml_parameter_index; DROP TABLE xml_parameter; DROP TABLE xml_relation; CREATE TABLE xml_relation ( relationid NUMBER(20) PRIMARY KEY, -- the unique id to this relation relation VARCHAR2(256) NOT NULL -- the relation: ismetadatafor, ); CREATE TABLE xml_parameter ( paramid NUMBER(20) PRIMARY KEY, -- unique id relationid NUMBER(20) NOT NULL, -- link to the relation type param VARCHAR2(512) NOT NULL, -- the content of the parameter paramname VARCHAR2(512) NOT NULL, -- name of the parameter paramtype VARCHAR2(128) NOT NULL, -- object or subject identifier FOREIGN KEY (relationid) REFERENCES xml_relation(relationid) ); CREATE UNIQUE INDEX xml_parameter_index ON xml_parameter(param, paramname, relationid, paramtype); CREATE SEQUENCE xml_relation_id_seq; CREATE SEQUENCE xml_parameter_id_seq; CREATE TRIGGER xml_relation_before_insert BEFORE INSERT ON xml_relation FOR EACH ROW BEGIN SELECT xml_relation_id_seq.nextval INTO :new.relationid FROM dual; END; / CREATE TRIGGER xml_parameter_before_insert BEFORE INSERT ON xml_parameter FOR EACH ROW BEGIN SELECT xml_parameter_id_seq.nextval INTO :new.paramid FROM dual; END; /