EclipseSCADA/Installation/Database
Some components of Eclipse SCADA can work with a relational database.
Contents
Basic Information
All further examples assume that:
- the database name is "eclipsescada"
- the database user is "eclipsescada"
- the password is "eclipsescada"
JDBC Driver
Eclipse SCADA uses the OSGi Enterprise method of obtaining a JDBC driver in an OSGi environment. This requires to have a JDBC driver that supports the OSGi enterprise specification. Otherwise you can take a normal JDBC driver and create a wrapper bundle which implements the DataSourceFactory interface.
Also see:
- Gemini DBAccess – http://www.eclipse.org/gemini/dbaccess/
- The Gemini DBAcess project provides OSGi Enterprise compliant JDBC drivers. At the moment they have drivers for MySQL, H2 and Derby
- openSCADA – http://openscada.org
- The openSCADA project provides a wrapper bundle for the Postgres JDBC driver. The bundle is called "org.openscada.external.postgresql" and is contained in the "external" repostory.
- See http://download.openscada.org/external/I/1.2.0/ for the P2 archive and a ZIP archive
- See http://apt.openscada.org and http://yum.openscada.org for .deb or .rpm versions of the archive
Postgres
If you want to use Postgres you will need to install the following packages:
Suse
zypper install postgresql92 postgresql92-server postgresql92-contrib
A&E
A&E stores event data in a relational database. It provides two storage formats.
Plain SQL
This storage format stores everything using plain SQL statements. Is is very portable but not as performant.
Table structure
See http://git.eclipse.org/c/eclipsescada/org.eclipse.scada.core.git/tree/org.eclipse.scada.ae.server.storage.jdbc/create.postgresql.sql for a more recent version.
---------------------------------------------------------------------------------- -- Copyright (c) 2013 Jürgen Rose and others. -- All rights reserved. This program and the accompanying materials -- are made available under the terms of the Eclipse Public License v1.0 -- which accompanies this distribution, and is available at -- http://www.eclipse.org/legal/epl-v10.html -- -- Contributors: -- Jürgen Rose - initial API and implementation ---------------------------------------------------------------------------------- -- DROP TABLE es_ae_events -- DROP TABLE es_ae_events_attr CREATE TABLE es_ae_events ( id CHAR(36) NOT NULL, source_timestamp TIMESTAMP, entry_timestamp TIMESTAMP, instance_id VARCHAR(32), monitor_type VARCHAR(32), event_type VARCHAR(32), value_type VARCHAR(32), value_string VARCHAR(4000), value_integer BIGINT, value_double DOUBLE PRECISION, message VARCHAR(4000), message_code VARCHAR(255), priority SMALLINT, source VARCHAR(255), actor_name VARCHAR(128), actor_type VARCHAR(32), severity VARCHAR(32), replicated SMALLINT, CONSTRAINT es_ae_events_pkey PRIMARY KEY (id) ); CREATE TABLE es_ae_events_attr ( id CHAR(36) NOT NULL, key VARCHAR(64) NOT NULL, value_type VARCHAR(32), value_string VARCHAR(4000), value_integer BIGINT, value_double DOUBLE PRECISION, CONSTRAINT es_ae_events_attr_pkey PRIMARY KEY (id , key), CONSTRAINT es_ae_events_attr_id_fkey FOREIGN KEY (id) REFERENCES es_ae_events (id) ON DELETE CASCADE ); CREATE INDEX es_ae_events_idx_1 ON es_ae_events (source_timestamp); CREATE INDEX es_ae_events_idx_1d ON es_ae_events (source_timestamp DESC); CREATE INDEX es_ae_events_idx_2 ON es_ae_events (entry_timestamp); CREATE INDEX es_ae_events_idx_2d ON es_ae_events (entry_timestamp DESC); CREATE INDEX es_ae_events_idx_3 ON es_ae_events (instance_id); CREATE INDEX es_ae_events_idx_4 ON es_ae_events (monitor_type); CREATE INDEX es_ae_events_idx_4l ON es_ae_events (lower(monitor_type)); CREATE INDEX es_ae_events_idx_5 ON es_ae_events (event_type); CREATE INDEX es_ae_events_idx_5l ON es_ae_events (lower(event_type)); CREATE INDEX es_ae_events_idx_6 ON es_ae_events (value_type); CREATE INDEX es_ae_events_idx_7 ON es_ae_events (value_string); CREATE INDEX es_ae_events_idx_7l ON es_ae_events (lower(value_string)); CREATE INDEX es_ae_events_idx_8 ON es_ae_events (value_integer); CREATE INDEX es_ae_events_idx_9 ON es_ae_events (value_double); CREATE INDEX es_ae_events_idx_10 ON es_ae_events (message); CREATE INDEX es_ae_events_idx_10l ON es_ae_events (lower(message)); CREATE INDEX es_ae_events_idx_11 ON es_ae_events (message_code); CREATE INDEX es_ae_events_idx_11l ON es_ae_events (lower(message_code)); CREATE INDEX es_ae_events_idx_12 ON es_ae_events (priority); CREATE INDEX es_ae_events_idx_13 ON es_ae_events (source); CREATE INDEX es_ae_events_idx_13l ON es_ae_events (lower(source)); CREATE INDEX es_ae_events_idx_14 ON es_ae_events (actor_name); CREATE INDEX es_ae_events_idx_14l ON es_ae_events (lower(actor_name)); CREATE INDEX es_ae_events_idx_15 ON es_ae_events (actor_type); CREATE INDEX es_ae_events_idx_15l ON es_ae_events (lower(actor_type)); CREATE INDEX es_ae_events_idx_16 ON es_ae_events (severity); CREATE INDEX es_ae_events_idx_16l ON es_ae_events (lower(severity)); CREATE INDEX es_ae_events_idx_17 ON es_ae_events (replicated); CREATE INDEX es_ae_events_attr_idx_1 ON es_ae_events_attr (id, key); CREATE INDEX es_ae_events_attr_idx_2 ON es_ae_events_attr (value_type); CREATE INDEX es_ae_events_attr_idx_3 ON es_ae_events_attr (value_string); CREATE INDEX es_ae_events_attr_idx_3l ON es_ae_events_attr (lower(value_string)); CREATE INDEX es_ae_events_attr_idx_4 ON es_ae_events_attr (value_integer); CREATE INDEX es_ae_events_attr_idx_5 ON es_ae_events_attr (value_double); -- ALTER TABLE es_ae_events OWNER TO es; -- ALTER TABLE es_ae_events_attr OWNER TO es; -- for replication table ( blob mode ) CREATE TABLE ES_AE_REP ( ID CHAR(36) NOT NULL, ENTRY_TIMESTAMP TIMESTAMP, NODE_ID VARCHAR(32), DATA BYTEA, CONSTRAINT ES_AE_REP_PK PRIMARY KEY ( ID ) );
Postgres specific
See the setup script at [1]
Download the file and execute it as postgres user:
sudo su - postgres psql -f create.postgresql.sql -declipsescada
The script will install a set of postgres extensions (from the contrib package) and therefore requires database admin privileges.
Alternatives
If it is not possible for you, some services provide an alternate method of persistence.
- The CA provides a file based storage