What |
RdbmsExport.xml |
|---|---|
Required |
no |
Description |
Exports historical time series data to RDBMS |
schema location |
|
Entry in ModuleDescriptors |
<moduleDescriptor id="RdbmsExport"> |
Configuration
The RdbmsExport module exports historical time series data to tables in a RDBMS. These tables must exist prior to running the module.
The configuration of the module is setup as:
In the sections below the different elements of the configuration are described
General
jdbcDriverClass
JDBC driver class to use for connection to RDBMS.
FEWS installation contains drivers for Oracle, PostgreSQL, Firebird
An Oracle example
<jdbcDriverClass>oracle.jdbc.driver.OracleDriver</jdbcDriverClass>
jdbcConnectionString
Connection string to use by JDBC driver to connect to RDBMS
An Oracle example:
<jdbcConnectionString>jdbc:oracle:thin:@localhost:1521:xe</jdbcConnectionString>
user
Username on the (target) RDBMS.
password
Password for user on the (target) RDBMS.
Encryption of the password in the FEWS configuration is not implemented yet.
exportTimeWindow
Defines the time window for which to export data from FEWS.
For the start and end time it can be configured whether they can be overruled from the User Interface.
<exportTimeWindow unit="day" start="-600" end="0" startOverrulable="true" endOverrulable="false"/>
exportTimeZone
The time zone in which to export the data from FEWS.
<exportTimeZone>+01:00</exportTimeZone>
moduleInstanceID
Optional list of Module Instance Id's for which to export time series data.
<moduleInstance moduleInstanceID="Statistiek_Percentielen_jaar" /> <moduleInstance moduleInstanceID="Statistiek_Percentielen_seizoen" />
filter
Optional list of Filter Id's for which to export time series data.
<filter filterID="TSI_productie" /> <filter filterID="TMX_ruw" /> <filter filterID="DINO_ruw" />
RDBMS DDL/object creation scripts (Oracle)
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:
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)
);
