Procesdata Opslaan Met SQLite In PLCnext C++ Projecten

Dit artikel beschrijft hoe de SQLite-database-engine die al op PLCnext-controllers geïnstalleerd is, gebruikt kan worden om data op te slaan die via de Global Data Space (GDS) wordt geleverd. De database stelt de opslag van procesdata in een gestandaardiseerde manier in en kan geëxporteerd worden naar andere systemen met SFTP.

Zorg ervoor dat de plcncli-toolversie overeenkomt met de firmwareversie van je controller.

Een Eclipse C++ Project Aanmaken

Maak een nieuw C++-project in Eclipse, volgens de instructies uit het PLCnext Info Center met de volgende eigenschappen:

• projectnaam: CppDB
• componentnaam: DBComponent
• programmanaam: DBProgram
• projectnaamruimte: CppDB

Andere namen zouden ook prima zijn, maar een gemeenschappelijke naam vereenvoudigt de tutorial.

Maak een nieuwe map (dezelfde hiërarchie als de src-map) in het project en noem het 'cmake'. Binnenin de map maak een bestand aan en noem het 'FindSqlite.cmake' en voeg de volgende inhoud in.

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() 

Vervang de inhoud van de bestanden DBComponent.cpp en DBComponent.hpp met het volgende:

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  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 { 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") // WorkerThread , 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 the 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 

Daarna, bouw het project. De aangemaakte PLCnext Library kan in de projectmap gevonden worden (C:\Users\eclipse-workspace\CppDB\bin).

Uitleg

In deze benadering wordt een WorkerThread gebruikt om de schrijfoperatie af te handelen. Dit is een thread met lage prioriteit die de uitvoering van de getthreadde code herhaalt tot Stop() wordt aangeroepen. In de thread controleren we of nieuwe data voor de database beschikbaar is en slaan we de data op. Na de uitvoering wacht de WorkerThread voor een gespecificeerde tijd (hier: 10 ms).

Met de hulp van de 'control'-poort kunnen we verschillende database-operaties triggeren. De data die opgeslagen moet worden, wordt geleverd met de poorten 'intArray' en 'floatArray'.

Laten we een eenvoudig IEC-programma maken:

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; 

Ten slotte moeten we de poorten verbinden:

Nu kunnen we het project compileren en naar een aangesloten PLC sturen. In de 'live-mode' kunnen we interacteren met de database, door verschillende waarden toe te wijzen aan de 'iControl'-variabele.

Een database 'database.db' wordt aangemaakt in de controllersmap /opt/plcnext. We kunnen er toegang toe krijgen met tools zoals WinSCP. We kunnen de database-inhoud controleren met het tool DB Browser (SQLite):

Meer Informatie

SQLite pragma statements