A device and method for constructing an OPC protocol specification based on an SQL SERVER database

Abstract

The application discloses a device and method for constructing a protocol based on an SQLSERVER database, and relates to the technical field of industrial automation and data communication, which comprises a protocol meta-database module, a protocol engine module, a dynamic node generator module, a version and audit module, a cross-protocol mapper module and a management configuration interface module, wherein the protocol meta-database module is connected with the protocol engine module, the protocol engine module is connected with the dynamic node generator module, the dynamic node generator module is connected with the version and audit module, the version and audit module is connected with the cross-protocol mapper module, and the cross-protocol mapper module is connected with the management configuration interface module. Through efficient cooperation among the protocol engine, an OPC server and a client, real-time processing and dynamic updating of data flow in the field of industrial automation are realized.

Description

Technical Field

[0001] This invention relates to the fields of industrial automation and data communication technology, specifically to a device and method for constructing an OPC protocol specification based on a SQL Server database. Background Technology

[0002] OPC (now OPC UA / OPC DA) is a widely adopted standard communication protocol in the field of industrial automation, used to enable data exchange between devices, PLCs, HMIs, and MES systems from different manufacturers. Traditional OPC protocols (such as OPC DA 2.05 / 3.0) rely on COM / DCOM technology and define metadata such as tag structure, data type, and access permissions through hard-coding on the Windows platform, which has the following drawbacks: (1) Rigid configuration: The node structure of the OPC Server is usually statically defined during the development phase, making it difficult to dynamically adjust according to on-site requirements; (2) High maintenance costs: Modifying the tag structure requires recompiling or restarting the service, which affects production continuity; (3) Lack of version management: unable to audit, roll back, and trace specification changes; (4) Difficulty in cross-system integration: The OPC specifications of equipment from different manufacturers vary greatly, and there is a lack of a unified modeling and mapping mechanism; (5) Metadata silos: Specification information is scattered in configuration files or registry, making it difficult to link with enterprise-level databases (such as MES and ERP).

[0003] In existing technologies, some solutions have attempted to describe the OPC node structure using XML or JSON, but these still rely on file system storage and lack transactional, concurrency control, and security auditing capabilities. Furthermore, no technology has yet used a relational database as the sole authoritative source of truth for OPC specifications to achieve an automated generation mechanism where "database definition is specification".

[0004] Therefore, in view of this, the present invention proposes an apparatus and method for constructing OPC protocol specifications based on SQL Server database to make up for and improve the deficiencies of the prior art. Summary of the Invention

[0005] To address the aforementioned technical problems, this invention provides an apparatus and method for constructing an OPC protocol specification based on a SQL Server database, thereby resolving the corresponding technical issues raised in the background section.

[0006] To achieve the above objectives, the technical solution adopted by the present invention is as follows: a device and method for constructing OPC protocol specifications based on a SQL Server database, comprising a specification meta-database module, a specification engine module, a dynamic node generator module, a version and audit module, a cross-protocol mapper module, and a management configuration interface module, wherein the specification meta-database module is connected to the specification engine module, the specification engine module is connected to the dynamic node generator module, the dynamic node generator module is connected to the version and audit module, the version and audit module is connected to the cross-protocol mapper module, and the cross-protocol mapper module is connected to the management configuration interface module; The protocol metadata database module is used to obtain OPC protocols, store structured metadata of OPC protocols, and send them to the protocol engine module; The specification engine module is used to obtain the stored OPC specification structured metadata, read and parse the OPC specification definition in the metadata database in real time, and send the parsed result to the dynamic node generator module. The dynamic node generator module is used to obtain the parsed results and dynamically create and update OPC address space nodes according to database instructions, and send them to the version and audit module; The version and audit module is used to obtain OPC address space nodes, synchronously record the change history of the OPC protocol, and send it to the cross-protocol mapper module; The cross-protocol mapper module is used to obtain the change history of the OPC protocol, automatically convert the OPC protocol to other protocols, and send the conversion results to the management configuration interface module. The management configuration interface module is used to obtain the conversion results and to perform visual editing and publishing of the OPC specification.

[0007] Preferably, the specification meta-database module is deployed in an SQL Server database, the specification engine module runs on an OPC Server, and the version and audit module supports rollback and difference comparison.

[0008] A method for constructing an OPC protocol specification based on a SQL Server database includes the following steps: Step 1: Build a relational data table in the SQL Server database and define the core entities of the OPC protocol; Step 2: Define the hierarchical structure and semantic rules of the OPC address space; Step 3: Monitor metadata changes by polling; Step 4: Generate and store a version snapshot before each OPC specification release; Step 5: Automatically map the defined OPC address space nodes to Modbus registers; Step 6: Utilize SQL Server database row-level security and dynamic data masking to control different users' editing permissions for the OPC specification; Step 7: Provide a web-based specification editor through a visual configuration front-end and persist the results to a SQL Server database.

[0009] As a preferred option, In step one, The core entities of the OPC specification include a namespace table, a node table, a table of references between nodes, a custom data type structure, version snapshots and change records, and a cross-protocol mapping rule table.

[0010] Preferably, the node table includes fields for node ID, name, data type, access permissions, unit, description, and historical sampling period, and the custom data type structure supports nested structures.

[0011] As a preferred option, In step three, When an update to the node table is detected, the OPC SDK interface is automatically invoked to dynamically create and modify node objects in the OPC Server.

[0012] As a preferred option, In step four, The generated version snapshots are stored in the version snapshot and change record, and the version snapshot and change record support querying historical versions, comparing differences, and one-click rollback via T-SQL.

[0013] Compared with existing technologies, the beneficial effects of this invention are: through efficient collaboration between the protocol engine, OPC server and client, real-time processing and dynamic updating of data streams in the field of industrial automation are realized, which is applicable to industrial control systems (ICS), SCADA systems, intelligent manufacturing platforms and industrial Internet data integration scenarios. Attached Figure Description

[0014] Figure 1 This is a schematic diagram of the overall structure of a preferred embodiment of the present invention; Figure 2 This is a flowchart of the method shown in this invention.

[0015] The numbers on the map are: 1. Specification Metadatabase Module; 2. Specification Engine Module; 3. Dynamic Node Generator Module; 4. Version and Audit Module; 5. Cross-Protocol Mapper Module; 6. Management Configuration Interface Module. Detailed Implementation

[0016] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0017] Embodiments of the present invention: Please refer to Figures 1 to 2 As shown, a device for constructing OPC protocol specifications based on a SQL Server database includes a specification metadata module 1, a specification engine module 2, a dynamic node generator module 3, a version and audit module 4, a cross-protocol mapper module 5, and a management configuration interface module 6. The specification metadata module 1 is connected to the specification engine module 2, the specification engine module 2 is connected to the dynamic node generator module 3, the dynamic node generator module 3 is connected to the version and audit module 4, the version and audit module 4 is connected to the cross-protocol mapper module 5, and the cross-protocol mapper module 5 is connected to the management configuration interface module 6. in: The specification metadata module 1 is deployed on SQL Server and is used to store the structured metadata of the OPC specification; Specification engine module 2 runs on the OPC Server and reads and parses specification definitions from the metadata database in real time; Dynamic node generator module 3 is used to dynamically create / update OPC address space nodes according to database instructions; Version and Audit module 4 is used to record the history of specification changes and supports rollback and difference comparison; The cross-protocol mapper module 5 is used to implement automatic conversion between OPC protocol and other protocols (such as Modbus, MQTT, BACnet); The Management Configuration Interface Module 6 is used to provide a web / API-based method for visual editing and publishing of specifications.

[0018] A method for constructing an OPC protocol specification based on a SQL Server database includes the following steps: 1. Establish the specification metadata model: Build relational data tables in SQL Server to define the core entities of the OPC specification, including: OPC_Namespace: Namespace table; OPC_Node: Node table, containing fields such as node ID, name, data type, access permissions, unit, description, and historical sampling period; OPC_Reference: A table of reference relationships between nodes; OPC_DataType: Custom data type structure (supports nested structures); OPC_VersionHistory: Version snapshots and change history; OPC_MappingRule: Cross-protocol mapping rule table; 2. Define the specification DSL (Domain-Specific Language): Define the hierarchical structure and semantic rules of the OPC address space in the database using T-SQL or JSON Schema, supporting inheritance, templates, and batch generation; 3. Real-time synchronization and node generation: The specification engine listens for metadata changes through polling or SQL Server Service Broker; when an update to the OPC_Node table is detected, it automatically calls the OPC SDK interface to dynamically create or modify node objects in the OPCServer; it supports incremental updates without requiring a service restart. 4. Version Control and Auditing: A version snapshot is generated before each specification release and stored in OPC_VersionHistory; it supports querying historical versions, comparing differences, and one-click rollback via T-SQL; all change records include the operator, time, and IP address, meeting industrial auditing requirements. 5. Cross-protocol intelligent mapping: Based on the OPC_MappingRule table, it automatically maps OPC nodes defined in SQL Server to Modbus registers, MQTT topics, or REST API endpoints; it supports expression engines (such as T-SQL functions) to perform data transformation and calculation. 6. Security and Access Control: Utilize SQL Server's Row-Level Security (RLS) and Dynamic Data Masking (DDM) to control different users' editing permissions for the specification; 7. OPC node access permissions are linked with database roles: A web-based specification editor is provided through a visual configuration front-end, which supports drag-and-drop modeling, syntax checking, conflict detection, and persists the results to SQL Server.

[0019] In this application, reference is made to Figure 2 As shown, the specific steps are as follows: (1) Develop the corresponding SQLSERVER driver using Python, run the example, and it can successfully collect the required SQLSERVER database data; Establish a specification metadata model and classify the collected data as follows: OPC_Namespace: Namespace table; OPC_Node: Node table, containing fields such as node ID, name, data type, access permissions, unit, description, and historical sampling period; OPC_Reference: A table of reference relationships between nodes; OPC_DataType: Custom data type structure (supports nested structures); OPC_VersionHistory: Version snapshots and change history; OPC_MappingRule: Cross-protocol mapping rule table; These required fields are written to the SQL Server database provided by the device; (2) Run the specification engine module. The system can automatically complete a series of operations from database changes to OPC node updates in the background. This process includes four main steps: listening, parsing, generating and feedback. Once a change is detected in the relevant table in SQL Server, the reduction engine immediately starts the parsing mechanism, identifies the node information that needs to be updated, calls the corresponding API function to perform the update operation, and feeds back the result to the front-end user interface; (3) Engineers add records through the Web interface, triggering database updates. The OPC Server completes node publishing within 3 seconds and completes the corresponding OPC protocol output through a configurable cross-protocol mapper. (4) OPC_MappingRule automatically maps the node to the MQTT topic factory / sensor / temp_comp for edge computing nodes to subscribe to.

[0020] Through efficient collaboration between the protocol engine, OPC server, and client, real-time processing and dynamic updates of data streams in the field of industrial automation are achieved, making it suitable for industrial control systems (ICS), SCADA systems, smart manufacturing platforms, and industrial internet data integration scenarios.

[0021] like Figure 1 As shown, it specifically includes: (1) Specification Metadatabase Module 1, which is adapted to various versions of SQL Server driver intelligent selection and is used to store structured metadata of OPC specifications; (2) Specification engine module 2, which runs on the OPC Server, reads and parses the specification definitions in the metadata database in real time; (3) Dynamic node generator module 3, dynamically creates / updates OPC address space nodes according to database instructions; (4) Version and Audit Module 4 records the history of specification changes and supports rollback and difference comparison; (5) Cross-protocol mapper module 5, which realizes automatic conversion between OPC protocol and other protocols (such as Modbus, MQTT, BACnet); (6) Management configuration interface module 6 provides Web / API methods for visual editing and publishing of specifications.

[0022] The size of the interval and threshold is set to facilitate comparison. The size of the threshold depends on the amount of sample data and the number of bases set by those skilled in the art for each set of sample data; as long as it does not affect the ratio between the parameter and the quantized value.

[0023] The above formulas are all dimensionless calculations. The formulas are derived from software simulations based on a large amount of collected data to obtain the most recent real-world results. The preset parameters in the formulas are set by those skilled in the art according to the actual situation. In the two embodiments provided in this application, it should be understood that the disclosed apparatus and system can be implemented in other ways; for example, the apparatus embodiments described above are merely illustrative, and the division of modules is merely a logical functional division. In actual implementation, there may be other division methods, such as multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed; furthermore, the coupling or direct coupling or communication connection between the shown or discussed mutuals can be through some interfaces, and the indirect coupling or communication connection between the apparatus or modules can be electrical, mechanical or other forms. The above description is only a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and inventive concept of the present invention, should be covered within the scope of protection of the present invention.

Claims

1. A device for constructing an OPC protocol specification based on a SQL Server database, characterized in that, It includes a specification meta-database module (1), a specification engine module (2), a dynamic node generator module (3), a version and audit module (4), a cross-protocol mapper module (5), and a management configuration interface module (6). The specification meta-database module (1) is connected to the specification engine module (2), the specification engine module (2) is connected to the dynamic node generator module (3), the dynamic node generator module (3) is connected to the version and audit module (4), the version and audit module (4) is connected to the cross-protocol mapper module (5), and the cross-protocol mapper module (5) is connected to the management configuration interface module (6). The protocol metadata database module (1) is used to obtain the OPC protocol, store the structured metadata of the OPC protocol, and send it to the protocol engine module (2). The specification engine module (2) is used to obtain the stored OPC specification structured metadata, read and parse the OPC specification definition in the metadata database in real time, and send the parsed result to the dynamic node generator module (3). The dynamic node generator module (3) is used to obtain the parsed results and dynamically create and update OPC address space nodes according to database instructions, and send them to the version and audit module (4). The version and audit module (4) is used to obtain OPC address space nodes, synchronously record the change history of OPC protocol, and send it to the cross-protocol mapper module (5). The cross-protocol mapper module (5) is used to obtain the change history of the OPC protocol, automatically convert the OPC protocol to other protocols, and send the conversion result to the management configuration interface module (6). The management configuration interface module (6) is used to obtain the conversion results and to perform visual editing and publishing of the OPC specification.

2. The device for constructing an OPC protocol specification based on a SQL Server database according to claim 1, characterized in that, The specification meta-database module (1) is deployed in the SQL Server database, the specification engine module (2) runs on the OPC Server, and the version and audit module (4) supports rollback and difference comparison.

3. A method for constructing an OPC protocol specification based on a SQL Server database, applied to the device for constructing an OPC protocol specification based on a SQL Server database as described in any one of claims 1-2, characterized in that, Includes the following steps: Step 1: Build a relational data table in the SQL Server database to define the core entities of the OPC protocol; Step 2: Define the hierarchical structure and semantic rules of the OPC address space; Step 3: Monitor metadata changes by polling; Step 4: Generate and store a version snapshot before each OPC specification release; Step 5: Automatically map the defined OPC address space nodes to Modbus registers; Step 6: Utilize SQL Server database row-level security and dynamic data masking to control different users' editing permissions for the OPC specification; Step 7: Provide a web-based specification editor through a visual configuration front-end and persist the results to a SQL Server database.

4. The method for constructing an OPC protocol specification based on a SQL Server database according to claim 3, characterized in that, In step one, The core entities of the OPC specification include a namespace table, a node table, a table of references between nodes, a custom data type structure, version snapshots and change records, and a cross-protocol mapping rule table.

5. The method for constructing an OPC protocol specification based on a SQL Server database according to claim 4, characterized in that, The node table includes fields for node ID, name, data type, access permissions, unit, description, and historical sampling period. The custom data type structure supports nested structures.

6. The method for constructing an OPC protocol specification based on a SQL Server database according to claim 4, characterized in that, In step three, When an update to the node table is detected, the OPC SDK interface is automatically invoked to dynamically create and modify node objects in the OPC Server.

7. The method for constructing an OPC protocol specification based on a SQL Server database according to claim 4, characterized in that, In step four, The generated version snapshots are stored in the version snapshot and change record, and the version snapshot and change record support querying historical versions, comparing differences, and one-click rollback via T-SQL.

Images