PLC high-frequency data management system based on built-in database

Abstract

A PLC high-frequency data management system based on a built-in database comprises an embedded database, a database operation engine, a data recording function block, a database operation function block, an annular memory cache region, a nonvolatile storage medium and a communication service module during PLC operation. A lightweight database and an intelligent cache mechanism are deeply integrated in the PLC, and high-frequency real-time small data packets are written and converted into low-frequency batch large transaction operation, so that high-frequency, stable and structured recording and convenient query of process data are realized on the premise of ensuring control instantaneity and low CPU load.

Description

Technical Field

[0001] This application relates to the field of industrial automation control technology, and in particular to a PLC high-frequency data management system based on a built-in database. Background Technology

[0002] In the field of automation control for high-end energy industries such as nuclear power and wind power, due to mandatory requirements for safety supervision, fault tracing, and performance analysis, control systems need to continuously and stably record a large number of process variables (such as temperature, pressure, flow rate, etc., sometimes reaching 300 or more) at extremely high frequencies (e.g., synchronized with the scan cycle of a programmable logic controller (PLC), such as 20ms). This massive amount of historical data not only needs to be completely stored but also needs to be efficiently queried, retrieved, and analyzed to support decision-making and maintenance.

[0003] In existing technologies, the following two methods are mainly used to realize PLC data recording: 1. Direct File Writing Solution: In each PLC scan cycle, process data is directly written to a flat file (such as CSV or TXT format) on an external storage medium (such as a CF card) by calling a file write instruction (such as FileWrite). This solution has significant drawbacks: First, frequent I / O operations continuously consume a large amount of CPU resources, resulting in a high control task load; second, the unpredictability of file system operations introduces periodic delays and severe jitter, seriously interfering with the stability and real-time performance of the PLC control cycle; finally, the generated flat file lacks indexing and structured management, making subsequent data retrieval, conditional queries, and analysis inefficient and failing to meet the needs of rapid fault diagnosis.

[0004] 2. External Gateway Relay Solution: This solution sends the data to be recorded from the PLC to an external industrial computer, server, or dedicated gateway device via an industrial network (such as Ethernet). These external devices then transfer the data to an external database (such as MySQL or SQL Server). While this solution achieves structured data storage, it introduces additional hardware costs and system complexity. Furthermore, network transmission introduces unpredictable latency and new risks such as network interruptions and single points of failure in gateway devices, reducing the reliability and determinism of the entire recording system. This makes it unsuitable for fields like nuclear power, which have stringent requirements for system simplicity, high reliability, and independent controllability.

[0005] Therefore, existing technologies cannot achieve stable, reliable recording and efficient management of high-frequency process data while ensuring real-time PLC control and low resource consumption. There is an urgent need for a data recording solution that can be natively integrated within the PLC, combining high-frequency recording capabilities, low performance impact, structured storage, and convenient query functions. Summary of the Invention

[0006] In view of the shortcomings of the prior art described above, the purpose of this application is to provide a PLC high-frequency data management system based on a built-in database, which is used to solve the technical problem of how to achieve stable recording, efficient structured storage, and convenient querying and export of high-frequency, massive process data in energy and process industries such as nuclear power, wind power, and high-end chemical industry, especially in situations with high reliability requirements, without affecting the real-time performance of PLC control, increasing system complexity, or increasing the risk of single-point failure.

[0007] To achieve the above and other related objectives, this application provides a PLC high-frequency data management system based on a built-in database, comprising: When the PLC is running, it is used to execute user applications and control logic; An embedded database, integrated within the PLC, is used to provide structured data storage and SQL operation support; The database operation engine, as an embedded program, runs inside the PLC and is used to schedule and execute database operation instructions. The data logging function block is provided as a library function for the user application to call, and is used to collect process variable data and temporarily store it in each PLC scan cycle. At least one circular memory buffer, configured in the PLC memory and connected to the data recording function module, is used to temporarily cache multiple sets of timestamped process variable data collected by the data recording function module; and Non-volatile storage medium for storing the database file of the embedded database and the data export file generated by the system. The database operation engine module is configured to: in response to a preset transaction triggering condition, submit multiple sets of data temporarily stored in the circular memory cache as a transaction to the embedded database for persistent storage.

[0008] In one optional embodiment of this application, the transaction triggering condition includes at least one of the following: the circular memory cache is full of data; a user-preset timer timeout period is reached; or a trigger command is received from the user.

[0009] In one optional embodiment of this application, the number of the circular memory cache areas is two, forming a double buffering mechanism; When the first ring memory buffer meets the transaction triggering condition and starts batch commit transactions, the data recording function module automatically switches to the second ring memory buffer to continue receiving new process variable data.

[0010] In an optional embodiment of this application, the system further includes a database operation function block, which serves as the external interface of the database operation engine, providing a first library function that can be called by user applications to perform data querying and exporting; and a second library function for executing SQL queries.

[0011] In an optional embodiment of this application, the system further includes a communication service module, which includes a web server and / or an FTP server; The communication service module is used to receive data query or export instructions from the host computer or human-machine interface, and forward the instructions to the database operation engine to execute the data query or export; and / or The communication service module is used to provide users with an access channel to download data export files stored on the non-volatile storage medium.

[0012] In an optional embodiment of this application, the second library function is further configured to create a database table in a database file based on a call from the user application; and / or manually initiate a transaction based on a call from the user application or the host computer, as a transaction triggering condition.

[0013] In an optional embodiment of this application, the database operation function block also provides a third library function that can be called by user applications to create or open database files during system initialization.

[0014] In an optional embodiment of this application, the database operation engine module is configured to: in response to a preset transaction triggering condition, use parameterized SQL insert statements to submit multiple sets of data temporarily stored in the circular memory cache as a transaction to the embedded database for persistent storage.

[0015] In one optional embodiment of this application, the embedded database module is an SQLite database, a Firebird database, a Berkeley DB database, or an esProc SPL database.

[0016] In one optional embodiment of this application, when the data recording function module is instantiated, it internally defines a structure array as the circular memory cache area, and the members of the structure array correspond one-to-one with the fields of the target data table in the embedded database.

[0017] This application presents a PLC high-frequency data management system based on a built-in database. By deeply integrating a lightweight database and intelligent caching mechanism within the PLC, it transforms high-frequency, real-time small data packet writing into low-frequency, batch large transaction operations. This decouples file writing from the PLC scan cycle, completely eliminating timing jitter in data recording. While ensuring real-time control and low CPU load, the system not only achieves high-frequency, stable, and structured recording of process data but also enables structured data storage within the PLC, supporting on-demand, fast querying, analysis, and export, significantly improving data utilization efficiency. Furthermore, the system requires no additional hardware gateway or complex middleware, significantly reducing system cost and maintenance complexity. Attached Figure Description

[0018] Figure 1 The architecture diagram of the PLC high-frequency data management system based on the built-in database provided in this application. Detailed Implementation

[0019] The following specific examples illustrate the implementation of this application. Those skilled in the art can easily understand other advantages and effects of this application from the content disclosed in this specification. This application can also be implemented or applied through other different specific embodiments, and various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this application. It should be noted that, unless otherwise specified, the following embodiments and features in the embodiments can be combined with each other.

[0020] It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of this application. Therefore, the illustrations only show the components related to this application and are not drawn according to the number, shape and size of the components in actual implementation. In actual implementation, the form, quantity and proportion of each component can be arbitrarily changed, and the layout of the components may also be more complex.

[0021] Figure 1 This is the overall architecture diagram of the PLC high-frequency data management system based on the built-in database of this application.

[0022] The high-frequency data management system for PLCs based on a built-in database proposed in this application achieves this by porting a lightweight embedded database (such as SQLite) to the PLC's embedded operating system. Core operations such as data recording, SQL command execution, data querying, and exporting are encapsulated into a series of library functions (also called function blocks) conforming to the IEC 61131-3 standard and directly callable by user applications. These library functions mainly include the data recording function block FB_DataLogger, and the database operation function blocks FB_DataExporter, SQLiteExec, and SQLiteOpen. Through the collaborative work of this system architecture and the aforementioned library functions, high-frequency, stable, and low-resource-consumption process data recording, querying, and management are achieved.

[0023] like Figure 1 As shown, the PLC high-frequency data management system based on the built-in database includes a PLC runtime, an embedded database, a database operation engine, a data recording function block, a database operation function block, a ring-shaped memory buffer, a non-volatile storage medium, and a communication service module.

[0024] The following section will provide a detailed introduction to each component of the system.

[0025] When the PLC is running, it serves as a basic execution platform and is configured with a user application program (corresponding to...). Figure 1 The user application program (in the PLC) is used to execute user application programs and control logic. Specifically, during each PLC scan cycle (e.g., 20ms), the PLC calls the data logging function block FB_DataLogger to collect and temporarily store process variable data.

[0026] The embedded database, integrated within the PLC, provides structured data storage and SQL operation support. For example, a lightweight SQLite database can be used. SQLite is a relational database that requires no separate server process and supports ACID transactions. Its zero-configuration, serverless, transactional, and single-file storage characteristics make it ideal for embedded environments. It should be understood that other lightweight databases such as Firebird, Berkeley DB, or esProc SPL can also be used as alternatives.

[0027] The database operation engine runs as an embedded program within the PLC, used to schedule and execute database operation instructions. This database operation engine is a resident embedded background service, acting as middleware between the user application and the embedded database. It is responsible for scheduling and executing all database operation instructions, including receiving requests from upper-level functional blocks and managing database connections and transactions. For example, during system initialization, the user application creates or opens a database file by calling the SQLiteOpen library function provided by the database operation engine, and subsequently calls the SQLiteExec library function to execute SQL statements to create the database table structure. More importantly, the database operation engine is configured to, in response to preset transaction triggering conditions, batch-submit multiple sets of data temporarily stored in the circular memory buffer as a single transaction to the embedded database for persistent storage.

[0028] As an example, the SQL statement for creating the database table structure during initialization is as follows: CREATE TABLE IF NOT EXISTS process_data ( timestamp INTEGER PRIMARY KEY, -- timestamp, primary key cycle_count INTEGER, -- PLC scan cycle counter var1 REAL,-- Process variable 1 var2 REAL, -- Process variable 2 ... var300 REAL -- Process variable 300 ); The transaction triggering conditions include at least one of the following: the circular memory cache is full of data (defined as cache full trigger), the user-preset timeout period is reached (defined as timer timeout trigger), and a trigger command is received from the user through the user application or external interface (defined as user command trigger).

[0029] Among these, buffer full triggering is the most common and efficient triggering method. The system maintains a buffer write pointer. Whenever the pointer reaches the preset buffer capacity limit (50 in this example), the triggering condition is immediately met. This means that the system will submit data packets in batches at a fixed size (e.g., 50 packets).

[0030] Timer timeout trigger: To prevent data from remaining in memory for too long (risk of loss) when process variables change slowly and the buffer cannot be filled for an extended period, the system enables a separate watchdog timer. The timer period can be configured by the user (e.g., 1 second). Regardless of whether the buffer is full, as soon as the timer times out, the system will force a transaction commit, persisting all data currently stored in the buffer.

[0031] User-triggered commands: To meet special operating conditions, users can actively trigger immediate data submission by calling the SQLiteExec library function when critical events (such as equipment emergency stop or process parameter over-limit alarms) occur via user applications or external interfaces (such as HMI buttons). This method ensures that data before and after critical events is written to disk in real time, facilitating detailed post-event analysis.

[0032] As an example, the database operation engine can use the following parameterized SQL insert statement to submit multiple sets of data temporarily stored in the circular memory cache as a single transaction to the embedded database for persistent storage: INSERT INTO process_data VALUES (?, ?, ?, ..., ?).

[0033] The database operation engine continuously monitors preset transaction triggering conditions. When the triggering conditions are met, the database operation engine uses parameterized SQL statements (e.g., INSERT INTO process_data VALUES (?, ?,..., ?);) to insert all data in the buffer into the database at once, forming an atomic transaction. This design transforms the high-frequency (20ms), small-scale (single group) real-time data generation process into a low-frequency (approximately 1 second), large-scale (batch) persistent storage process, fundamentally avoiding the interference of I / O operations on the PLC scan cycle.

[0034] The data logging function block FB_DataLogger is provided as a library function for the user application to call. Its function is to collect a set of process variable data (e.g., 300 REAL values) in each PLC scan cycle and temporarily store it in a circular memory buffer, while also recording information such as the current timestamp and cycle count.

[0035] To ensure that the continuous acquisition of new data is not blocked during I / O for writing data to the database, the data recording function module defines two structure arrays as a dual-ring memory cache during instantiation: a first cache and a second cache. The capacity of each cache can be configured as needed; in this embodiment, it is set to store 50 sets of records. The members of the structure arrays correspond one-to-one with the fields of the target data table (such as the process_data table) in the embedded database, ensuring efficient data mapping.

[0036] The dual-buffering mechanism works as follows: At any given time, only one buffer is in an "active" state to receive new data. When the active buffer (e.g., the first buffer) meets the transaction triggering condition, the data recording function block FB_DataLogger automatically switches the data write pointer to another idle buffer (the second buffer) to continue receiving new process variable data, thus achieving zero interruption in the data acquisition process. Simultaneously, the database operation engine batch commits the data in the first buffer as a complete transaction.

[0037] The database operation function block is the programming interface provided by the database operation engine to the user application, mainly including: The SQLiteOpen function block (defined as a third-party library function) is used to create or open a database file during system initialization.

[0038] The SQLiteExec function block (defined as a second library function) is used to execute various SQL statements, such as creating tables, manually triggering transactions, and executing SQL queries.

[0039] The FB_DataExporte function block (defined as the first library function) is used for data querying and exporting. User applications call this function block and pass in parameters (such as start time, end time, and export file path). Based on these parameters, it generates a standard SQL query statement (e.g., SELECT * FROM process_data WHERE timestamp BETWEEN {start_time} AND {end_time};). The SQL query is then executed by calling the database operation engine or the SQLiteExec function block. After obtaining the query result set, the FB_DataExporte function block further utilizes the database's built-in functions (such as SQLite's .mode csv and .output commands) to directly export the results to a file in a specified format, such as a CSV file.

[0040] The non-volatile storage medium (such as an industrial-grade CFast card or MMC card) serves as the persistent storage foundation of the system, used to store the database files of the embedded database and the data export files generated by the system.

[0041] The communication service module includes a web server and / or an FTP server; the communication service module is used to receive data query or export instructions from a host computer or human-machine interface (HMI), and forward the instructions to the database operation engine or the FB_DataExporter function block to perform data query or export; and the communication service module can also be used to provide users with an access channel to download data export files stored on the non-volatile storage medium.

[0042] In summary, the PLC high-frequency data management system based on a built-in database of this application, by deeply integrating a lightweight database and intelligent caching mechanism within the PLC, transforms high-frequency real-time small data packet writing into low-frequency batch large transaction operations, thereby decoupling file writing from the PLC scan cycle and completely eliminating timing jitter in data recording. While ensuring real-time control and low CPU load, this system not only achieves high-frequency, stable, and structured recording of process data, but also enables structured data storage within the PLC, supporting on-demand fast querying, analysis, and export, greatly improving data utilization efficiency. Simultaneously, this system requires no additional hardware gateway or complex middleware, significantly reducing system cost and maintenance complexity.

[0043] The above embodiments are merely illustrative of the principles and effects of this application and are not intended to limit this application. Any person skilled in the art can modify or alter the above embodiments without departing from the spirit and scope of this application. Therefore, all equivalent modifications or alterations made by those skilled in the art without departing from the spirit and technical concept disclosed in this application should still be covered by the claims of this application.

[0044] The above description of the embodiments shown in this application (including the content set forth in the abstract of the specification) is not intended to be an exhaustive enumeration or to limit this application to the precise forms disclosed herein. Although specific embodiments and examples of this application have been described herein for illustrative purposes only, various equivalent modifications are possible within the spirit and scope of this application, as will be recognized and understood by those skilled in the art. As indicated, these modifications can be made to this application in accordance with the above description of the embodiments described herein, and such modifications will be within the spirit and scope of this application.

[0045] Therefore, although this application has been described herein with reference to specific embodiments thereof, freedom of modification, various changes and substitutions are also within the scope of the above disclosure, and it should be understood that in some cases, certain features of this application may be adopted without departing from the scope and spirit of the application and without corresponding use of other features. Thus, many modifications can be made to adapt a particular environment or material to the substantive scope and spirit of this application. This application is not intended to be limited to the specific terminology used in the following claims and / or the specific embodiments disclosed as the best mode contemplated for carrying out this application, but this application will include any and all embodiments and equivalents falling within the scope of the appended claims. Therefore, the scope of this application will be determined only by the appended claims.

Claims

1. A PLC high-frequency data management system based on a built-in database, characterized in that, include: When the PLC is running, it is used to execute user applications and control logic; An embedded database, integrated within the PLC, is used to provide structured data storage and SQL operation support; The database operation engine, as an embedded program, runs inside the PLC and is used to schedule and execute database operation instructions. The data logging function block is provided as a library function for the user application to call, and is used to collect process variable data and temporarily store it in each PLC scan cycle. At least one circular memory buffer is set in the PLC memory and connected to the data recording function module for temporarily caching multiple sets of time-stamped process variable data collected by the data recording function module. as well as Non-volatile storage medium for storing the database file of the embedded database and the data export file generated by the system. The database operation engine module is configured to: in response to a preset transaction triggering condition, submit multiple sets of data temporarily stored in the circular memory cache as a transaction to the embedded database for persistent storage.

2. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The transaction triggering conditions include at least one of the following: The circular memory cache is full of data; The user-preset timeout period has been reached; Received a trigger command from the user.

3. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The number of the circular memory cache areas is two, forming a double buffering mechanism; When the first ring memory buffer meets the transaction triggering condition and starts batch commit transactions, the data recording function module automatically switches to the second ring memory buffer to continue receiving new process variable data.

4. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The system also includes a database operation function block, which serves as the external interface of the database operation engine. It provides a first library function that user applications can call to perform data querying and exporting, and a second library function for executing SQL queries.

5. The PLC high-frequency data management system based on a built-in database according to claim 5, characterized in that, The second library function is also used to create database tables in the database file based on calls from the user application; and / or to manually initiate transactions based on calls from the user application or the host computer, as a transaction triggering condition.

6. The PLC high-frequency data management system based on a built-in database according to claim 5, characterized in that, The database operation function block also provides third library functions that user applications can call to create or open database files during system initialization.

7. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The system also includes a communication service module, which includes a web server and / or an FTP server; The communication service module is used to receive data query or export instructions from the host computer or human-machine interface, and forward the instructions to the database operation engine to execute the data query or export; and / or The communication service module is used to provide users with an access channel to download data export files stored on the non-volatile storage medium.

8. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The database operation engine module is configured to: respond to a preset transaction triggering condition, use parameterized SQL insert statements to submit multiple sets of data temporarily stored in the circular memory cache as a transaction to the embedded database for persistent storage.

9. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, The embedded database module is an SQLite database, Firebird database, Berkeley DB database, or esProc SPL database.

10. The PLC high-frequency data management system based on a built-in database according to claim 1, characterized in that, When the data recording function module is instantiated, it internally defines a structure array as the circular memory cache area, and the members of the structure array correspond one-to-one with the fields of the target data table in the embedded database.

Images