Gegevens opslaan in SQLite in een PLCnext C++-project

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

Zorg ervoor dat de versie van de plcncli-tool overeenkomt met de firmwareversie van uw controller.

Maak een Eclipse C++-project

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

• projectnaam:CppDB
• naam onderdeel:DBComponent
• programmanaam:DBProgram
• projectnaamruimte:CppDB

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

Maak een nieuwe map (dezelfde hirarchie als de src-map) in het project en noem deze 'cmake'. Maak in de map een bestand aan en noem het 'FindSqlite.cmake' en voeg de volgende inhoud erin 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 door 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 <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")	// 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

Bouw daarna het project op. De aangemaakte PLCnext Library is te vinden in de projectdirectory (C:\Users\eclipse-workspace\CppDB\bin).

Uitleg

In deze benadering wordt een WorkerThread gebruikt om de schrijfbewerking af te handelen. Dit is een thread met lage prioriteit die de uitvoering van de threaded code herhaalt tot Stop() wordt genoemd. In de thread kijken we of er nieuwe data voor de database beschikbaar is en slaan we de data op. Na de uitvoering wacht de WorkerThreads een bepaalde tijd (hier:10 ms).

Met behulp van de 'control'-poort kunnen we verschillende databasebewerkingen activeren. De gegevens die moeten worden opgeslagen zijn voorzien van 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 aansluiten:

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

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

Meer informatie

SQlite pragmaverklaringen