EclipseSCADA/Installation/Database

Some components of Eclipse SCADA can work with a relational database.

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