...
Tables for storage of data in the RDBMS must be present before first execution of RDBMS Export module.
Proper priviledges must be assigned to the #user account by the database administrator to insert/update data in these tables as well as (execution) rights on the sequences and triggers in use.
Data model:
Script to create tables/objects in an Oracle RDBMS:
No Format |
---|
DROP TABLE timeseriedata;
DROP TABLE timeserie;
DROP TABLE filter;
DROP TABLE location;
DROP TABLE parameter;
DROP SEQUENCE seq_parameter;
DROP SEQUENCE seq_location;
DROP SEQUENCE seq_filter;
DROP SEQUENCE seq_timeserie;
DROP SEQUENCE seq_timeseriedata;
CREATE SEQUENCE seq_parameter;
CREATE SEQUENCE seq_location;
CREATE SEQUENCE seq_filter;
CREATE SEQUENCE seq_timeserie;
CREATE SEQUENCE seq_timeseriedata;
CREATE TABLE parameter (
pkey integer NOT NULL
,id varchar(64) NOT NULL
,name varchar(256) DEFAULT NULL
,shortname varchar(256) DEFAULT NULL
,unit varchar(64) DEFAULT NULL
,parametertype varchar(64) DEFAULT NULL
,parametergroup varchar(64) DEFAULT NULL
,CONSTRAINT parameters_pk1
PRIMARY KEY (pkey)
,CONSTRAINT parameter_uk1
UNIQUE (id)
);
CREATE OR REPLACE TRIGGER parameter_bi BEFORE INSERT ON parameter
FOR EACH ROW
BEGIN
SELECT seq_parameter.NEXTVAL into :new.pkey FROM dual;
END;
CREATE TABLE location (
lkey integer NOT NULL
,id varchar(64) NOT NULL
,name varchar(256) DEFAULT NULL
,parentid varchar(64) DEFAULT NULL
,description varchar(256) DEFAULT NULL
,shortname varchar(256) DEFAULT NULL
,tooltiptext varchar(1000) DEFAULT NULL
,x float DEFAULT NULL
,y float DEFAULT NULL
,z float DEFAULT NULL
,longitude float DEFAULT NULL
,latitude float DEFAULT NULL
,CONSTRAINT location_pk1
PRIMARY KEY (lkey)
,CONSTRAINT location_uk1
UNIQUE(id)
);
CREATE OR REPLACE TRIGGER location_bi BEFORE INSERT ON location
FOR EACH ROW
BEGIN
SELECT seq_location.NEXTVAL into :new.lkey FROM dual;
END;
CREATE TABLE filter (
fkey integer NOT NULL
,id varchar(64) NOT NULL
,name varchar(256) DEFAULT NULL
,description varchar(256) DEFAULT NULL
,issubfilter integer DEFAULT NULL
,parentfkey integer DEFAULT NULL
,isendnode integer DEFAULT NULL
,CONSTRAINT filter_pk1
PRIMARY KEY (fkey)
,CONSTRAINT filter_uk1
UNIQUE (id)
,CONSTRAINT filter_filter_fk
FOREIGN KEY (parentfkey)
REFERENCES filter(fkey)
);
CREATE OR REPLACE TRIGGER filter_bi BEFORE INSERT ON filter
FOR EACH ROW
BEGIN
SELECT seq_filter.NEXTVAL into :new.fkey FROM dual;
END;
CREATE TABLE timeserie (
tkey integer NOT NULL
,moduleinstanceid varchar(64) NOT NULL
,timestep varchar(64) NOT NULL
,filterkey integer NOT NULL
,locationkey integer NOT NULL
,parameterkey integer NOT NULL
,CONSTRAINT timeserie_pk1
PRIMARY KEY (tkey)
,CONSTRAINT timeserie_uk1
UNIQUE (locationkey, parameterkey, filterkey, moduleinstanceid, timestep)
,CONSTRAINT timeserie_filter_fk
FOREIGN KEY (filterkey)
REFERENCES filter(fkey)
,CONSTRAINT timeserie_location_fk
FOREIGN KEY (locationkey)
REFERENCES location(lkey)
,CONSTRAINT timeserie_parameter_fk
FOREIGN KEY (parameterkey)
REFERENCES parameter(pkey)
);
CREATE OR REPLACE TRIGGER timeserie_bi BEFORE INSERT ON timeserie
FOR EACH ROW
BEGIN
SELECT seq_timeserie.NEXTVAL into :new.tkey FROM dual;
END;
CREATE TABLE timeseriedata (
tkey integer NOT NULL
,tsd_time timestamp NOT NULL
,tsd_value float DEFAULT NULL
,tsd_flag varchar(1 ) DEFAULT NULL
,tsd_detection varchar(1) DEFAULT NULL
,tsd_comments varchar(256) DEFAULT NULL
,CONSTRAINT timeseriedata_pk1
PRIMARY KEY (
tkey
,tsd_time
)
,CONSTRAINT timeseriedata_fk
FOREIGN KEY (tkey)
REFERENCES timeserie(tkey)
);
|