如何在 PLCnext C++ 项目中的 SQLite 中存储数据

本文介绍了如何使用已安装在 PLCnext 控制器上的 SQLite 数据库引擎来存储通过全局数据空间 (GDS) 提供的数据。该数据库能够以标准化的方式存储过程数据，并可以通过SFTP导出到其他系统。

请确保 plcncli 工具版本与您控制器的固件版本匹配。

创建一个 Eclipse C++ 项目

在 Eclipse 中创建一个新的 C++ 项目，按照 PLCnext 信息中心的说明，使用以下属性：

• 项目名称：CppDB
• 组件名称：DBComponent
• 程序名称：DBProgram
• 项目命名空间：CppDB

其他名称也可以，但是，通用名称可以简化教程。

在项目中创建一个新文件夹（与 src 文件夹相同的层次结构）并将其命名为“cmake”。在文件夹中创建一个文件并将其命名为“FindSqlite.cmake”并将以下内容插入其中。

查找Sqlite.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()

将文件 DBComponent.cpp 和 DBComponent.hpp 的内容替换为以下内容：

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

之后，构建项目。创建好的PLCnext库可以在工程目录（C:\Users\eclipse-workspace\CppDB\bin）中找到。

说明

在这种方法中，WorkerThread 用于处理写入操作。这是一个低优先级的线程，重复执行线程代码直到 Stop() 叫做。在线程中，我们将检查数据库的新数据是否可用并存储数据。执行后，WorkerThreads 等待指定的时间（这里：10 毫秒）。

借助“控制”端口，我们可以触发不同的数据库操作。应该存储的数据由端口'intArray'和'floatArray'提供。

让我们创建一个简单的 IEC 程序：

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;

最后，我们必须连接端口：

现在，我们可以编译项目并将其发送到连接的 PLC。在“实时模式”中，我们可以通过为“iControl”变量分配不同的值来与数据库进行交互。

在控制器目录 /opt/plcnext 中创建数据库“database.db”。我们可以通过 WinSCP 等工具访问它。我们可以使用工具 DB Browser (SQLite) 查看数据库内容：

更多信息

SQlite pragma 语句