Prozessdaten mit SQLite in PLCnext C++ Projekten speichern

Dieser Artikel beschreibt, wie die auf PLCnext Controllern vorinstallierte SQLite-Datenbank-Engine genutzt werden kann, um Daten aus dem Global Data Space (GDS) zu speichern. Die Datenbank ermöglicht standardisierte Speicherung von Prozessdaten und Export in andere Systeme via SFTP.

Bitte stellen Sie sicher, dass die plcncli-Tool-Version zur Firmware-Version Ihres Controllers passt.

Eclipse C++ Projekt erstellen

Neues C++ Projekt in Eclipse anlegen, gemäß PLCnext Info Center Anleitung mit folgenden Eigenschaften:

• Projektname: CppDB
• Komponentenname: DBComponent
• Programmname: DBProgram
• Projekt-Namespace: CppDB

Andere Namen funktionieren auch, aber ein gemeinsamer Name vereinfacht das Tutorial.

Neuen Ordner (gleiche Hierarchie wie src) im Projekt anlegen, 'cmake' nennen. Darin Datei 'FindSqlite.cmake' erstellen und folgenden Inhalt einfügen:

FindSqlite.cmake

# Copyright (c) 2018 PHOENIX CONTACT GmbH & Co. KG
# Created by Björn sauer
#
# - Find Sqlite
# Find the Sqlite headers and libraries.
#
# Defined Variables:
# Sqlite_INCLUDE_DIRS - Where to find sqlite3.h.
# Sqlite_LIBRARIES - The sqlite library.
# Sqlite_FOUND - True if sqlite found.
#
# Defined Targets:
# Sqlite::Sqlite
find_path(Sqlite_INCLUDE_DIR NAMES sqlite3.h)
find_library(Sqlite_LIBRARY NAMES sqlite3)
include(FindPackageHandleStandardArgs)
find_package_handle_standard_args(Sqlite DEFAULT_MSG Sqlite_LIBRARY Sqlite_INCLUDE_DIR)
if(Sqlite_FOUND)
 set(Sqlite_INCLUDE_DIRS "${Sqlite_INCLUDE_DIR}")
 set(Sqlite_LIBRARIES "${Sqlite_LIBRARY}")
 mark_as_advanced(Sqlite_INCLUDE_DIRS Sqlite_LIBRARIES)
 if(NOT TARGET Sqlite::Sqlite)
  add_library(Sqlite::Sqlite UNKNOWN IMPORTED)
  set_target_properties(Sqlite::Sqlite PROPERTIES IMPORTED_LOCATION "${Sqlite_LIBRARY}" INTERFACE_INCLUDE_DIRECTORIES "${Sqlite_INCLUDE_DIRS}")
 endif()
endif()

Inhalt von DBComponent.cpp und DBComponent.hpp durch folgende ersetzen:

DBComponent.hpp

#pragma once
#include "Arp/System/Core/Arp.h"
#include "Arp/System/Acf/ComponentBase.hpp"
#include "Arp/System/Acf/IApplication.hpp"
#include "Arp/Plc/Commons/Esm/ProgramComponentBase.hpp"
#include "DBComponentProgramProvider.hpp"
#include "Arp/Plc/Commons/Meta/MetaLibraryBase.hpp"
#include "Arp/System/Commons/Logging.h"
#include "CppDBLibrary.hpp"
#include "Arp/System/Acf/IControllerComponent.hpp"
#include "Arp/System/Commons/Threading/WorkerThread.hpp"
#include <sqlite3.h>
namespace CppDB {
 using namespace Arp;
 using namespace Arp::System::Acf;
 using namespace Arp::Plc::Commons::Esm;
 using namespace Arp::Plc::Commons::Meta;
 //#component
 class DBComponent : public ComponentBase, public IControllerComponent, public ProgramComponentBase, private Loggable<DBComponent> {
 public:
  // typedefs
 public:
  // construction/destruction
  DBComponent(IApplication& application, const String& name);
  virtual ~DBComponent() = default;
 public:
  // IComponent operations
  void Initialize() override;
  void LoadConfig() override;
  void SetupConfig() override;
  void ResetConfig() override;
  void PowerDown() override;
 public:
  // IControllerComponent operations
  void Start(void) override;
  void Stop(void) override;
 public:
  // ProgramComponentBase operations
  void RegisterComponentPorts() override;
  void WriteToDB();
 private:
  // methods
  DBComponent(const DBComponent& arg) = delete;
  DBComponent& operator= (const DBComponent& arg) = delete;
 public:
  // static factory operations
  static IComponent::Ptr Create(Arp::System::Acf::IApplication& application, const String& name);
 private:
  // fields
  DBComponentProgramProvider programProvider;
  WorkerThread workerThread;
 private:
  // static fields
  static const int workerThreadIdleTimeWrite = 10; // 10 ms
 public:
  // Ports
  //#port
  //#attributes(Input)
  int16 control = 0;
  //#port
  //#attributes(Input)
  int16 intArray[10] {}; // INT in PLCnext Engineer
  //#port
  //#attributes(Input)
  float32 floatArray[10] {}; // REAL in PLCnext Engineer
  //#port
  //#attributes(Output)
  int16 status = 0;
 };
 // inline methods of class DBComponent
 inline DBComponent::DBComponent(IApplication& application, const String& name)
  : ComponentBase(application, ::CppDB::CppDBLibrary::GetInstance(), name, ComponentCategory::Custom)
  , programProvider(*this)
  , workerThread(make_delegate(this, &DBComponent::WriteToDB), workerThreadIdleTimeWrite, "CppDB.WriteToDatabase")
  , ProgramComponentBase(::CppDB::CppDBLibrary::GetInstance().GetNamespace(), programProvider)
 {
 }
 inline IComponent::Ptr DBComponent::Create(Arp::System::Acf::IApplication& application, const String& name) {
  return IComponent::Ptr(new DBComponent(application, name));
 }
} // end of namespace CppDB

DBComponent.cpp

#include "DBComponent.hpp"
#include "Arp/Plc/Commons/Esm/ProgramComponentBase.hpp"
namespace CppDB {
 sqlite3 *db = nullptr; // pointer to the database
 sqlite3_stmt * stmt = nullptr; // needed to prepare
 std::string sql = ""; // sqlite statement
 int rc = 0; // for error codes of the database
 void DBComponent::Initialize() {
  // never remove next line
  ProgramComponentBase::Initialize();
  // subscribe events from the event system (Nm) here
 }
 void DBComponent::LoadConfig() {
  // load project config here
 }
 void DBComponent::SetupConfig() {
  // never remove next line
  ProgramComponentBase::SetupConfig();
  // setup project config here
 }
 void DBComponent::ResetConfig() {
  // never remove next line
  ProgramComponentBase::ResetConfig();
  // implement this inverse to SetupConfig() and LoadConfig()
 }
 #pragma region IControllerComponent operations
 void DBComponent::Start() {
  // start your threads here accessing any Arp components or services
  // open the database connection
  // the database path (/opt/plcnext/) and name (database) could be modified
  rc = sqlite3_open("/opt/plcnext/database.db", &db);
  if( rc ) {
   Log::Error("DB - 1 - {}", sqlite3_errmsg(db));
   status = 1;
   return;
  } else{
   // modify the database behaviour with pragma statements
   sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
   sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, NULL);
   sqlite3_exec(db, "PRAGMA temp_store = MEMORY", NULL, NULL, NULL);
   // create tables
   sql = "CREATE TABLE IF NOT EXISTS tb0 ("
    "_id INTEGER PRIMARY KEY, "
    "value1 INTEGER DEFAULT 0, "
    "value2 REAL DEFAULT 0.0 );";
   // execute the sql-statement
   rc = sqlite3_exec(db, sql.c_str(), 0, 0, 0);
   if(rc) {
    Log::Error("DB - 3 - {}", sqlite3_errmsg(db));
    status = 3;
   }
  }
  // prepare sql-statement
  sql = "INSERT INTO tb0 (value1, value2) VALUES (?,?)";
  rc = sqlite3_prepare_v2(db, sql.c_str(), strlen(sql.c_str()), &stmt, nullptr);
  if(rc) {
   Log::Error("DB - 4 - {}", sqlite3_errmsg(db));
   status = 4;
  }
  // start the WorkerThread
  this->workerThread.Start();
 }
 void DBComponent::Stop() {
  // stop your threads here accessing any Arp components or services
  // delete the prepared sqlite statements
  rc = sqlite3_finalize(stmt);
  { Log::Error("DB - 1 - {}", sqlite3_errmsg(db)); status = 1; }
  // close the database connection
  rc = sqlite3_close(db);
  { Log::Error("DB - 1 - {}", sqlite3_errmsg(db)); status = 1; }
  // stop the WorkerThread
  this->workerThread.Stop();
 }
 #pragma endregion
 void DBComponent::PowerDown() {
  // implement this only if data must be retained even on power down event
  // Available with 2021.6 FW
 }
 void DBComponent::WriteToDB() {
  // store data in the database
  if(control == 1) {
   // start transaction
   rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
   if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; }
   // iterate over the arrays
   for(int i = 0; i < 10; i++) {
    // bind values to the prepared statement
    rc = sqlite3_bind_int(stmt, 1, intArray[i]);
    if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; }
    rc = sqlite3_bind_double(stmt, 2, floatArray[i]);
    if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; }
    // execute the sqlite statement and reset the prepared statement
    rc = sqlite3_step(stmt);
    rc = sqlite3_clear_bindings(stmt);
    if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; }
    rc = sqlite3_reset(stmt);
    if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; }
   }
   // end transaction
   rc = sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
   if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; }
  }
  // delete the database entries
  if(control == 2) {
   // begin transaction
   rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
   if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; }
   rc = sqlite3_exec(db, "DELETE FROM tb0", 0, 0, 0);
   if(rc) { Log::Error("DB - 7 - {}", sqlite3_errmsg(db)); status = 7; }
   // end transaction
   sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
   if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; }
   // release used memory
   rc = sqlite3_exec(db, "VACUUM", 0, 0, 0);
   if(rc) { Log::Error("DB - 8 - {}", sqlite3_errmsg(db)); status = 8; }
  }
 }
} // end of namespace CppDB

Danach Projekt bauen. Erstellte PLCnext Library im Projektverzeichnis (C:\Users\eclipse-workspace\CppDB\bin) finden.

Erklärung

In diesem Ansatz wird ein WorkerThread für den Schreibvorgang genutzt. Niedrigprioritärer Thread, der threaded Code wiederholt ausführt bis Stop() aufgerufen wird. Im Thread prüfen wir, ob neue Daten für Datenbank verfügbar sind und speichern sie. Nach Ausführung wartet WorkerThread definierte Zeit (hier: 10 ms).

Über 'control'-Port können verschiedene Datenbankoperationen getriggert werden. Zu speichernde Daten kommen über Ports 'intArray' und 'floatArray'.

Einfaches IEC-Programm erstellen:

IF iControl = 1 THEN iControl := 0; END_IF;
IF xWrite THEN
 arrInt[0] := 0; arrInt[1] := 1; arrInt[2] := 2; arrInt[3] := 3; arrInt[4] := 4;
 arrInt[5] := 5; arrInt[6] := 6; arrInt[7] := 7; arrInt[8] := 8; arrInt[9] := 9;
 arrReal[0] := 9.0; arrReal[1] := 8.0; arrReal[2] := 7.0; arrReal[3] := 6.0; arrReal[4] := 5.0;
 arrReal[5] := 4.0; arrReal[6] := 3.0; arrReal[7] := 2.0; arrReal[8] := 1.0; arrReal[9] := 0.0;
 iControl := 1; xWrite := FALSE;
END_IF;

Ports verbinden:

Projekt kompilieren und an verbundene PLC senden. Im 'Live-Mode' mit Datenbank interagieren, indem 'iControl' verschiedene Werte zugewiesen werden.

Datenbank 'database.db' wird im Controller-Verzeichnis /opt/plcnext erstellt. Zugriff via Tools wie WinSCP. Inhalt prüfbar mit DB Browser (SQLite):

Weitere Informationen

SQLite pragma statements