Módulo de Exportación a Base de Datos

Introducción

El módulo permite exportar los datos que fueron recibidos desde dispositivos, en tiempo real, en el formato de algunas bases de datos populares. Los DBMS soportados son Microsoft SQL Server, Oracle, PostgreSQL y MySQL. Este módulo viene incluido en el paquete de instalación de Rapid SCADA y no requiere instalación por separado. El driver (controlador) del mismo es ModDBExport.dll.

Configuración

Seleccionado un proyecto, vaya a la página Modules, active el módulo ModDBExport.dll y abra sus propiedades. El módulo soporta la exportación en varios tipos de bases de datos diferentes y en paralelo. Para agregar una base de datos, haga clic en el botón . La página Connection contiene los parámetros para conectarse a la base de datos. Especifique las consultas SQL en las páginas Current Data (datos actuales), Archive Data (datos a archivar) y Events (eventos). El módulo ejecuta estas consultas cuando el servidor recibe nuevos datos. La base de datos, que es un objetivo de exportación, debe crearse y contener las tablas apropiadas para almacenar los datos.

Parámetros de exportación de datos actuales

Si algunos datos no se exportaron a tiempo, por ejemplo, si una base de datos no está disponible, los datos pueden ser transferidos en modo manual. El formulario de exportación correspondiente se abre con el botón . Para hacer posible la exportación manual, cree los canales de salida correspondientes en la base de datos de configuración y especifíquelos en el formulario.

Ejemplos de tablas y consultas SQL

Microsoft SQL Server

-- Delete channel data table if it exists
IF OBJECT_ID('CnlData', 'U') IS NOT NULL
DROP TABLE CnlData;

-- Create channel data table
CREATE TABLE CnlData (
  DateTime datetime2 NOT NULL,
  CnlNum   int NOT NULL,
  Val      float NOT NULL,
  Stat     int NOT NULL,
  PRIMARY KEY (DateTime, CnlNum)
);

CREATE INDEX idx_CnlData_CnlNum ON CnlData (CnlNum);

-- Delete events table if it exists
IF OBJECT_ID('Events', 'U') IS NOT NULL
DROP TABLE Events;

-- Create events table
CREATE TABLE Events (
  DateTime   datetime2 NOT NULL,
  ObjNum     int NOT NULL,
  KPNum      int NOT NULL,
  ParamID    int NOT NULL,
  CnlNum     int NOT NULL,
  OldCnlVal  float NOT NULL,
  OldCnlStat int NOT NULL,
  NewCnlVal  float NOT NULL,
  NewCnlStat int NOT NULL,
  Checked    bit NOT NULL,
  UserID     int NOT NULL,
  Descr      char(100),
  Data       char(50)
);

CREATE INDEX idx_Events_DateTime ON Events (DateTime);
CREATE INDEX idx_Events_ObjNum ON Events (ObjNum);
CREATE INDEX idx_Events_KPNum ON Events (KPNum);
CREATE INDEX idx_Events_CnlNum ON Events (CnlNum);

-- Insert current data
INSERT INTO CnlData (DateTime, CnlNum, Val, Stat)
VALUES (@dateTime, @cnlNum, @val, @stat)

-- Insert or update existing archive data
MERGE CnlData AS target
USING (SELECT @dateTime, @cnlNum) AS source (DateTime, CnlNum)
ON (target.DateTime = source.DateTime AND target.CnlNum = source.CnlNum)
WHEN MATCHED THEN 
  UPDATE SET Val = @val, Stat = @stat
WHEN NOT MATCHED THEN
  INSERT (DateTime, CnlNum, Val, Stat)
  VALUES (@dateTime, @cnlNum, @val, @stat);

-- Insert event
INSERT INTO Events (DateTime, ObjNum, KPNum, ParamID, CnlNum, OldCnlVal, OldCnlStat, NewCnlVal, NewCnlStat, Checked, UserID, Descr, Data)
VALUES (@dateTime, @objNum, @kpNum, @paramID, @cnlNum, @oldCnlVal, @oldCnlStat, @newCnlVal, @newCnlStat, @checked, @userID, @descr, @data)

Oracle

-- Delete channel data table if it exists
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE cnldata';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

-- Create channel data table
CREATE TABLE cnldata (
  datetime TIMESTAMP NOT NULL,
  cnlnum   INTEGER NOT NULL,
  val      FLOAT NOT NULL,
  stat     INTEGER NOT NULL,
  PRIMARY KEY (datetime, cnlnum)
);

CREATE INDEX idx_cnldata_cnlnum ON cnldata (cnlnum);

-- Delete events table if it exists
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE events';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

-- Create events table
CREATE TABLE events (
  datetime   TIMESTAMP NOT NULL,
  objnum     INTEGER NOT NULL,
  kpnum      INTEGER NOT NULL,
  paramid    INTEGER NOT NULL,
  cnlnum     INTEGER NOT NULL,
  oldcnlval  FLOAT NOT NULL,
  oldcnlstat INTEGER NOT NULL,
  newcnlval  FLOAT NOT NULL,
  newcnlstat INTEGER NOT NULL,
  checked    INTEGER NOT NULL,
  userid     INTEGER NOT NULL,
  descr      CHAR(100),
  data       CHAR(50)
);

CREATE INDEX idx_events_datetime ON events (datetime);
CREATE INDEX idx_events_objnum ON events (objnum);
CREATE INDEX idx_events_kpnum ON events (kpnum);
CREATE INDEX idx_events_cnlnum ON events (cnlnum);

-- Insert current data
INSERT INTO cnldata (datetime, cnlnum, val, stat)
VALUES (:dateTime, :cnlNum, @val, :stat)

-- Insert or update existing archive data
MERGE INTO cnldata
USING dual ON (datetime = :dateTime AND cnlnum = :cnlnum)
WHEN MATCHED THEN 
  UPDATE SET val = :val, stat = :stat
WHEN NOT MATCHED THEN 
  INSERT (datetime, cnlnum, val, stat)
  VALUES (:dateTime, :cnlNum, :val, :stat)

-- Insert event
INSERT INTO events (datetime, objnum, kpnum, paramid, cnlnum, oldcnlval, oldcnlstat, newcnlval, newcnlstat, checked, userid, descr, data)
VALUES (:dateTime, :objNum, :kpNum, :paramID, :cnlNum, :oldCnlVal, :oldCnlStat, :newCnlVal, :newCnlStat, :checked, :userID, :descr, :data)

PostgreSQL

-- Delete channel data table if it exists
DROP TABLE IF EXISTS cnldata;

-- Create channel data table
CREATE TABLE cnldata (
  datetime timestamp NOT NULL,
  cnlnum   integer NOT NULL,
  val      double precision NOT NULL,
  stat     integer NOT NULL,
  PRIMARY KEY (datetime, cnlnum)
);

CREATE INDEX ON cnldata (cnlnum);

-- Delete events table if it exists
DROP TABLE IF EXISTS events;

-- Create events table
CREATE TABLE events (
  datetime   timestamp NOT NULL,
  objnum     integer NOT NULL,
  kpnum      integer NOT NULL,
  paramid    integer NOT NULL,
  cnlnum     integer NOT NULL,
  oldcnlval  double precision NOT NULL,
  oldcnlstat integer NOT NULL,
  newcnlval  double precision NOT NULL,
  newcnlstat integer NOT NULL,
  checked    boolean NOT NULL,
  userid     integer NOT NULL,
  descr      char(100),
  data       char(50)
);

CREATE INDEX ON events (datetime);
CREATE INDEX ON events (objnum);
CREATE INDEX ON events (kpnum);
CREATE INDEX ON events (cnlnum);

-- Insert current data
INSERT INTO cnldata (datetime, cnlnum, val, stat)
VALUES (@dateTime, @cnlNum, @val, @stat)

-- Insert or update existing archive data
WITH upsert AS (UPDATE cnldata SET val = @val, stat = @stat 
WHERE datetime = @datetime AND cnlnum = @cnlNum RETURNING *)
INSERT INTO cnldata (datetime, cnlnum, val, stat)
SELECT @dateTime, @cnlNum, @val, @stat
WHERE NOT EXISTS (SELECT * FROM upsert)

-- Insert event
INSERT INTO events (datetime, objnum, kpnum, paramid, cnlnum, oldcnlval, oldcnlstat, newcnlval, newcnlstat, checked, userid, descr, data)
VALUES (@dateTime, @objNum, @kpNum, @paramID, @cnlNum, @oldCnlVal, @oldCnlStat, @newCnlVal, @newCnlStat, @checked, @userID, @descr, @data)

MySQL

-- Delete channel data table if it exists
DROP TABLE IF EXISTS cnldata;

-- Create channel data table
CREATE TABLE cnldata (
  datetime DATETIME NOT NULL,
  cnlnum   INT NOT NULL,
  val      DOUBLE NOT NULL,
  stat     SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (datetime, cnlnum)
) ENGINE=InnoDB;

CREATE INDEX idx_cnldata_cnlnum ON cnldata (cnlnum);

-- Delete events table if it exists
DROP TABLE IF EXISTS events;

-- Create events table
CREATE TABLE events (
  datetime   DATETIME NOT NULL,
  objnum     INT NOT NULL,
  kpnum      INT NOT NULL,
  paramid    INT NOT NULL,
  cnlnum     INT NOT NULL,
  oldcnlval  DOUBLE NOT NULL,
  oldcnlstat SMALLINT UNSIGNED NOT NULL,
  newcnlval  DOUBLE NOT NULL,
  newcnlstat SMALLINT UNSIGNED NOT NULL,
  checked    TINYINT UNSIGNED NOT NULL,
  userid     INT NOT NULL,
  descr      CHAR(100),
  data       CHAR(50)
) ENGINE=InnoDB;

CREATE INDEX idx_events_datetime ON events (datetime);
CREATE INDEX idx_events_objnum ON events (objnum);
CREATE INDEX idx_events_kpnum ON events (kpnum);
CREATE INDEX idx_events_cnlnum ON events (cnlnum);

-- Insert current data
INSERT INTO cnldata (datetime, cnlnum, val, stat)
VALUES (@dateTime, @cnlNum, @val, @stat)

-- Insert or update existing archive data
INSERT INTO cnldata (datetime, cnlnum, val, stat)
VALUES (@dateTime, @cnlNum, @val, @stat)
ON DUPLICATE KEY UPDATE val = @val, stat = @stat

-- Insert event
INSERT INTO events (datetime, objnum, kpnum, paramid, cnlnum, oldcnlval, oldcnlstat, newcnlval, newcnlstat, checked, userid, descr, data)
VALUES (@dateTime, @objNum, @kpNum, @paramID, @cnlNum, @oldCnlVal, @oldCnlStat, @newCnlVal, @newCnlStat, @checked, @userID, @descr, @data)

Table of contents