A data synchronization method between databases isolated by network barriers

Abstract

The present invention discloses a method for synchronizing data between databases isolated by a network gate, comprising: a database monitors data changes in a data table and records the data changes in a monitoring intermediate table or a data export table; based on job scheduling and stored procedures, the data in the monitoring intermediate table are parsed into SQL statements and synchronized into the data export table, and the data in the data export table are converted into a first export file; the first export file is saved to another system through isolated data exchange; the other system converts the first export file into SQL statements based on job scheduling or manual operation, imports the SQL statements and records the import information, thereby realizing data synchronization; the method for synchronizing data between databases isolated by a network gate proposed by the present invention ensures the security and reliability of data, is compatible with relational databases on the market, tracks import and export information, modifies and re-executes abnormal imports, and avoids data deadlocks caused by a large number of problematic SQL imports.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a method for synchronizing data between databases isolated by network barriers. Background Art

[0002] Manufacturers of products using key technologies usually split the intelligent manufacturing system into two independent systems, a private network and an industrial control network, through a network gate for operation and maintenance, because the production data during the production process involves high-level commercial secrets or even state secrets. The private network manufacturing system is mainly responsible for entering confidential personnel information, confidential production basic data, and confidential production data of other workshops; the industrial control network manufacturing system is mainly responsible for entering production data of formal product production lines and internal business process flow data of the industrial control network. Although the two systems are isolated and independent of each other by the network gate, they complement and depend on each other.

[0003] There are three main existing data synchronization methods: 1) data synchronization through an interface; 2) data synchronization through EXCEL data import; 3) data synchronization through database import.

[0004] When using EXCEL data import, there are many interrelated business data in the manufacturing system. The fault tolerance of data synchronization directly realized through EXCEL is low. It is easy to fail to realize business flow in the intelligent manufacturing system due to data errors due to incomplete import of business document related data. In addition, EXCEL import requires a large number of EXCEL export templates. The amount of EXCEL export of large databases is large. At the same time, database deadlock is prone to occur during import, resulting in reduced efficiency of the intelligent manufacturing system.

[0005] Database import: There are many database products on the market. In the scenario where two intelligent manufacturing systems of the same manufacturer are isolated from each other by a network gate and use different database management systems, database import requires the conversion of exported database files according to different databases. The conversion operation is troublesome and prone to data loss. Different intelligent manufacturing systems isolated by network gates also have their own independent business processes. If database import is used directly, the data generated by the business processes of each manufacturing system may be overwritten, resulting in data loss. Summary of the invention

[0006] In order to overcome the shortcomings of the above technologies, the present invention provides a method for data synchronization between databases isolated by a network firewall, which is applied to independent intelligent manufacturing systems isolated by a network firewall but need to synchronize some basic data and business data. By triggering and monitoring data changes, the system exports the SQL statements of the changed data, encrypts and compresses them, and imports and exports the encrypted and compressed SQL statements to achieve data synchronization between intelligent manufacturing systems.

[0007] The technical solution adopted by the present invention to overcome the technical problems is:

[0008] The present invention proposes a method for synchronizing data between databases isolated by a network gate, which is applied to an intelligent manufacturing system with a network gate. The intelligent manufacturing system is isolated into at least two independently operated and maintained first systems and second systems by the network gate, wherein the first system is operated and maintained based on the first database, and the second system is operated and maintained based on the second database. The method comprises the following steps: the first / second system database monitors data changes in a data table and records them in a monitoring intermediate table / data export table; if the data changes in the data table are recorded in the monitoring intermediate table, the data in the monitoring intermediate table is parsed into an SQL statement and synchronized to the data export table; based on database job scheduling and stored procedures, the unexported data in the data export table is converted into a first export file; the first export file of the first / second system is saved in a first directory, and is saved in a second directory of a second / first system through isolated data exchange; the second / first system converts the first export file into an SQL statement based on job scheduling or manual operation, imports the SQL statement and records the import information; the second / first system determines whether the imported data exists based on the first export file, and if so, executes the SQL statement to achieve data synchronization, otherwise, records the corresponding SQL statement and abnormal operation to the data import table of the second / first database.

[0009] Furthermore, if the data changes in the data table are recorded in the monitoring intermediate table, the data in the monitoring intermediate table is parsed into SQL statements and synchronized to the data export table, it specifically includes: calling the stored procedure based on job scheduling, parsing the data in the monitoring intermediate table into SQL statements for inserting / modifying / deleting; inserting the parsed SQL statements into the data export table, and marking the updated data in the data export table as non-exported data.

[0010] Furthermore, based on database job scheduling and stored procedures, the unexported data in the data export table is converted into a first export file, specifically including: encrypting the data marked as unexported and writing it into a text file based on 3DES encryption; and compressing the text file into a compressed file as the first export file.

[0011] The 3DES encryption algorithm is used to write text files and then compress them into compressed files, ensuring the security and reliability of the information.

[0012] Furthermore, the second / first system converts the first export file into SQL statements based on job scheduling or manual operation, imports the SQL statements and records the import information, specifically including: the second / first system periodically queries the second directory based on job scheduling or manually synchronizes the first export file, thereby decrypting and decompressing the first export file saved in the second directory into a text file, wherein the text file at least includes the amount of synchronized data and synchronized data information, the job scheduling at least includes Spring job scheduling or Windows job scheduling, and the synchronized data information at least includes the synchronized data table name, data operation type, primary key ID and SQL statement; parsing the text file into SQL statements; importing SQL statements and recording SQL import information, wherein the import information at least includes the imported file name, import time, import status, importer and imported SQL detailed data.

[0013] Record all import and export information to help people who perform data synchronization track problems and view import and export status.

[0014] Furthermore, the second / first system determines whether the imported data exists based on the first export file. If so, an SQL statement is executed to achieve data synchronization. Otherwise, the corresponding SQL statement and the abnormal operation are recorded in the data import table of the second / first database, specifically including: determining whether the imported data exists based on the data table name, operation type and primary key ID in the text file decrypted and decompressed from the first export file. If the operation type is new, querying whether the data exists based on the data table name and / or primary key ID. If the data table name and / or primary key ID already exist, the SQL statement is not executed, and the corresponding SQL statement and the abnormal operation are recorded in the data import table; if the data table name and primary key ID do not exist, the SQL statement is executed to achieve data synchronization.

[0015] Avoid unnecessary duplicate imports of data.

[0016] Furthermore, importing SQL statements and recording SQL import information specifically includes: importing SQL statements to record the amount of synchronized data and all imported SQL statements, and executing SQL statements one by one; if the SQL statement execution is abnormal, the operation of the imported SQL statement is not rolled back, and the SQL statement, SQL statement execution status, whether the SQL is abnormal, and abnormal information are recorded in the data import table.

[0017] Furthermore, based on the data import table and the data export table, the SQL statements that failed in import and export can be edited again and re-imported and exported.

[0018] It is convenient for the personnel involved in data synchronization to track problems, and it is also convenient for the operators to modify the SQL and re-execute it according to the actual situation or ignore the problematic SQL, so as to avoid the problem of data deadlock caused by the import of a large number of problematic SQL during data import.

[0019] Furthermore, the first / second database is a SQLSERVER database or a domestic DAMO database.

[0020] Furthermore, the first / second database monitors data changes in the data table and records them in the monitoring intermediate table / data export table, specifically including: if the first database and / or the second database is a domestic DAMO database, the domestic DAMO database monitors the addition, deletion or update of data in the data table based on table-level triggers, and records it in the data export table; if the first database and / or the second database is SQLSERVER, the data in the data table is added, deleted or updated based on SQLSERVER CDC monitoring, and recorded in the monitoring intermediate table.

[0021] Furthermore, if the domestic DAMO database monitors data updates in the data table based on table-level triggers, it will compare the data before and after the update operation. If there is no difference in the data before and after the update, it will not be recorded in the data export table; if SQLSERVER monitors data updates in the data table based on SQLSERVER CDC, the difference between the new and old data will be compared through stored procedures. If there is no difference, it will not be updated to the monitoring intermediate table.

[0022] Compare data before and after data update to avoid unnecessary operations on data.

[0023] The beneficial effects of the present invention are:

[0024] 1. Use SQLSERVER CDC technology and DAMO database's table-level trigger technology to monitor database data changes in real time.

[0025] 2. When data update is detected, compare the new and old data to avoid unnecessary duplication of data when importing.

[0026] 3. Export the data changes caused by the addition, deletion and modification operations using SQL statements, and directly execute the corresponding SQL statements when importing, which can adapt to most relational databases on the market;

[0027] 4. The exported SQL statements are encrypted using 3DES encryption algorithm technology and written into text files, and then compressed into compressed files, ensuring the security and reliability of information;

[0028] 5. Track the import and export information to facilitate the relevant personnel who perform data synchronization to track the related issues of exporting data and importing data, and be able to modify the abnormal import SQL and re-execute the import operation.

[0029] 6. Handle abnormal situations of data flow in one-way and two-way gateways, especially duplicate detection of primary key IDs, to avoid problems such as data deadlock caused by a large number of problematic SQL imports during data import. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 It is a flow chart of an embodiment of a method for synchronizing data between databases isolated by a network gate according to the present invention;

[0031] Figure 2 Flow chart of data synchronization between databases isolated by a network gate according to an embodiment of the present invention

[0032] Figure 3 A schematic diagram of the data import and export implementation process of an embodiment of the present invention; DETAILED DESCRIPTION

[0033] In order to facilitate those skilled in the art to better understand the present invention, the present invention is further described in detail below in conjunction with the accompanying drawings and specific embodiments. The following is only exemplary and does not limit the protection scope of the present invention.

[0034] In order to better understand the technical solution of the present application, some professional terms in the present application are first explained.

[0035] Intelligent manufacturing system: a production information management system for the execution layer of the workshop of manufacturing enterprises

[0036] SQLSERVER CDC: Change Data Capture (CDC for short), Change Data Capture records the insert, update and delete activities of SQL Server tables. SQLServer operations will write logs, which is also the source of CDC captured data. The source table with CDC enabled will insert data into the log table during insert, update and delete activities. CDC captures the change data into the change table through the capture process. Through the query function provided by CDC, we can capture this part of data.

[0037] Gatekeeper: Two independent host systems are isolated by a gatekeeper, so that there is no physical connection, logical connection or information transmission protocol for communication between the systems, and there is no information exchange based on the protocol, but only non-protocol ferrying in the form of data files.

[0038] One-way network gate: To ensure that data in a high-density network cannot flow to a low-density network, but data in a low-density network can flow to a high-density network.

[0039] Two-way gateway: Data in high-density network and low-density network can be exchanged in isolation.

[0040] 3DES: Triple data encryption algorithm.

[0041] SQL statement: Structured query statement used to access data and query, update and manage relational database systems.

[0042] like Figure 1 As shown, it is a flow chart of a method for data synchronization between databases isolated by a network gate of the present invention, which is applied to an intelligent manufacturing system with a network gate. The intelligent manufacturing system is isolated into at least two independently operated and maintained first systems and second systems through the network gate, wherein the first system is operated and maintained based on the first database, and the second system is operated and maintained based on the second database.

[0043] If the first / second database is a SQLSERVER database, use SQLSERVER CDC technology to monitor data changes in the data table in real time and record them in the database monitoring table. Obtain the monitored data changes through database job scheduling and stored procedures to determine whether the data needs to be exported. Then parse the data to be exported into SQL statements and record them in the data export table.

[0044] If the first / second system database is the DAMO database, table-level triggers are used to monitor data changes in the data table in real time. When the data is updated, the data information before and after the update is compared to determine whether the updated data needs to be exported. The data to be exported is parsed into SQL statements and recorded in the data export table. In addition, the data that has not been exported in the data export table is encrypted through the 3DES encryption algorithm and converted into the first export file, and the data status is changed to "exported", thereby preventing repeated export of data.

[0045] The first export file of the first / second system is compressed and saved in the first directory of the first / second system, and saved in the second directory of the second / first system through isolated data exchange; the second / first system reads the import file in the second directory of the second / first system based on Spring job scheduling or human operation events, reads the file after decompression, decrypts the content of the encrypted file into a corresponding SQL statement, executes the corresponding SQL statement and records the execution record in the import information table; the second / first system database determines whether new data exists based on the SQL in the decompressed and decrypted first file, determines whether to execute the SQL statement, and if new data exists, executes the SQL statement to achieve data synchronization, otherwise, records the corresponding SQL statement and abnormal operation to the data import table of the second / first database.

[0046] like Figure 2The figure shows a schematic diagram of the data synchronization process. Two independent manufacturing systems monitor data changes through the database. The system exports the SQL statements of the changed data, encrypts them and compresses them into compressed files. The encrypted and compressed SQL files are imported and exported to achieve data synchronization between intelligent manufacturing systems.

[0047] The following describes a method for synchronizing data between databases isolated by a network gate according to the present invention in conjunction with a specific embodiment.

[0048] like Figure 3 As shown, the intelligent manufacturing system is divided into independent manufacturing system A and manufacturing system B by setting a one-way network gate or a two-way network gate. The networks and interfaces of manufacturing system A and manufacturing system B are independent.

[0049] In one embodiment of the present invention, manufacturing system A uses SQLSERVER database, intelligent system B uses DAMO database, and the changes to the data in manufacturing system A are synchronized to manufacturing system B.

[0050] The SQLSERVER of manufacturing system A adds, deletes, and modifies production basic data, production data, inspection data, warehousing data, and other data. The SQLSERVER database uses SQLSERVER CDC technology to monitor the data addition, deletion, and modification operations in the data table enabled with CDC in real time, and records the SQL statements related to the operations in the monitoring intermediate table.

[0051] The DAMO database of manufacturing system B uses the table-level trigger of the data table to listen to the data addition, deletion and modification operations in the data table of the trigger, and records the SQL statements related to the operation into the monitoring intermediate table.

[0052] After the CDC function of the SQLSERVER database that needs to capture data change tables is enabled, the database will automatically generate the cdc.dbo_table name for enabling the CDC function_CT table, compare the old and new data for update operations based on the field [__$operate] in the table, and compare the difference between the old and new data through stored procedures. If there is no difference, the data will not be updated to the monitoring intermediate table.

[0053] The database uses job scheduling to periodically call the stored procedure to parse the data in the monitoring intermediate table into the operation table, operation SQL statement and other information, and updates it to the data synchronization export table and marks the import status as not exported.

[0054] The SQLSERVER of manufacturing system A starts a scheduling task to periodically synchronize and export the data marked as not exported in the table, and uses 3DES encryption technology to encrypt the operation data information into a text and compress it into a compressed file in the specified directory.

[0055] The exported encrypted compressed file is transferred to the designated directory for import under manufacturing system B through a removable storage medium. Manufacturing system B periodically queries the imported folder through a scheduling task and decrypts and decompresses the unimported data.

[0056] In some implementations, the removable storage medium may be a CD or a secure USB flash drive.

[0057] The DAMO database of manufacturing system B under network 2 parses the decrypted and decompressed files and executes the operation SQL statements in the files, and records the executed SQL statements and statement execution status into the system. Manufacturing system B under network 2 executes the imported data SQL one by one, thus realizing the one-way network gate data synchronization from system A to system B.

[0058] In some implementations, if a problematic SQL statement is encountered, all SQL statements will not be rolled back, but the problematic SQL will be rolled back and the problematic SQL statement, all SQL statements, and all SQL execution conditions, whether the SQL statement is abnormal, and abnormal information will be recorded. The operator can view all problematic SQL statements through the records and can re-edit and execute the problematic SQL statements to ensure the correctness of the data.

[0059] In some implementations, a two-way network switch is set between system A and system B to perform data synchronization. When system A adds new data, the SQLSERVER database of system A uses SQLSERVER CDC technology to monitor the data addition operation in the data table with CDC enabled in real time, and records the operation-related SQL statements in the monitoring intermediate table. The SQLSERVER database uses job scheduling to regularly parse the data in the monitoring intermediate table into insert SQL statements, inserts the parsed SQL statements into the data export table, and marks the updated data in the data export table as unexported data. The unexported data is encrypted and written into a text file based on 3DES encryption, and the text file is compressed into a compressed file as the first export file for data synchronization.

[0060] The synchronized data includes the synchronized data table name, operation type, primary key ID and SQL statement. System B synchronizes the data according to the data table name, operation type and primary key ID.

[0061] The first export file is imported into the removable storage medium manually or by task scheduling, and the first export file is saved to the second directory of system B through the removable storage medium. System B obtains the first export file by periodically querying the second directory, decrypts and decompresses the first export file to obtain a text file, and parses it into an SQL statement. When the operation type is to add, the data table name or primary key ID is judged to check whether the newly added data already exists. If not, the SQL statement is executed and the corresponding task operation of adding data is performed. If it exists, the execution is not continued, and the abnormal information record is saved in the data export table.

[0062] This method is to solve the abnormal situation of duplicate synchronization of data during the two-way network gate data synchronization. After the new data generated by system A is synchronized to system B, system B will also synchronize the new data. System B will also generate a data packet with the same primary key ID and generate a synchronization file. If system A receives this synchronization file, it will judge based on the primary key ID and will not process the SQL file with the same primary key ID, thus avoiding duplicate synchronization.

[0063] In some implementations, the data of both system A and system B need to be changed and bidirectional synchronization is performed through a network gateway. Before data synchronization is performed, system A adds a new piece of data and system B adds a new piece of data. The primary key IDs in the synchronization files generated by the two systems are the same. In the process of data synchronization, subsequent manual intervention is required to modify the primary key ID.

[0064] In some implementations, all export information of system A and system B records information such as the exporter, number of exported data items, and export time, and the imported SQL also records the imported file name, import time, import status, importer, and imported SQL details, making it easier for relevant personnel who perform data synchronization to track problems.

[0065] In some implementations, if an SQL statement is not executed during the data import process, the abnormal SQL is recorded in the input import table, which can facilitate the operator to modify the SQL and re-execute it according to the actual situation or ignore the problematic SQL, thereby avoiding problems such as data deadlock caused by the import of a large number of problematic SQL statements during data import.

[0066] In some implementations, if data is modified in system A but the modified data is not saved, the actual data does not change. When the SQLSERVER CDC trigger monitors that the data in the data table is an update operation, the difference between the new and old data is compared. If there is no difference, the operation record is not updated to the monitoring intermediate table to avoid unnecessary operations on data.

[0067] The above only describes the basic principles and preferred embodiments of the present invention. Those skilled in the art may make many changes and improvements based on the above description, and these changes and improvements should fall within the protection scope of the present invention.

Claims

1. A method for synchronizing data between databases isolated by a network barrier, characterized in that: The method is applied to an intelligent manufacturing system with a network gate, wherein the intelligent manufacturing system is isolated into at least two independently operated and maintained first and second systems through the network gate, wherein the first system is operated and maintained based on a first database, and the second system is operated and maintained based on a second database, and the method comprises the following steps: The database of the first system or the second system monitors the data changes in the data table and records them in the monitoring intermediate table or the data export table; If the data changes in the data table are recorded in the monitoring intermediate table, the data in the monitoring intermediate table is parsed into SQL statements and synchronized to the data export table; Based on database job scheduling and stored procedures, the unexported data in the data export table is converted into a first export file; The first export file of the first system is saved in the first directory and saved to the second directory of the second system through the isolated data exchange, or the first export file of the second system is saved in the first directory and saved to the second directory of the first system through the isolated data exchange; The first system or the second system converts the first export file into an SQL statement based on job scheduling or manual operation, imports the SQL statement and records the import information, the first system or the second system periodically queries the second directory based on job scheduling or manually synchronizes the first export file, thereby decrypting and decompressing the first export file saved in the second directory into a text file, wherein the text file at least includes the amount of synchronized data and synchronized data information, the job scheduling at least includes Spring job scheduling or Windows job scheduling, and the synchronized data information at least includes the synchronized data table name, data operation type, primary key ID and SQL statement; Parse text files into SQL statements; Import SQL statements and record SQL import information, including: import SQL statements to record the amount of synchronized data and all imported SQL statements, and execute SQL statements one by one; if the SQL statement execution is abnormal, the operation of the imported SQL statement is not rolled back, and the SQL statement, SQL statement execution status, whether the SQL is abnormal and the abnormal information are recorded in the data import table, where the import information at least includes the imported file name, import time, import status, importer and imported SQL detailed data, The second system determines whether the imported data exists based on the first export file. If the imported data exists, the SQL statement is executed to achieve data synchronization. Otherwise, the corresponding SQL statement and the abnormal operation are recorded in the data import table of the second database. Or the first system determines whether the imported data exists based on the first export file. If the imported data exists, the SQL statement is executed to achieve data synchronization. Otherwise, the corresponding SQL statement and the abnormal operation are recorded in the data import table of the first database. Specifically, it includes: Determine whether the imported data exists based on the data table name, operation type, and primary key ID in the text file decrypted and decompressed from the first exported file, If the operation type is Add, query whether the data exists based on the data table name and / or primary key ID. If the data table name and / or primary key ID already exists, the SQL statement will not be executed, and the corresponding SQL statement and abnormal operation will be recorded in the data import table; If the data table name and primary key ID do not exist, execute the SQL statement to achieve data synchronization.

2. The method for synchronizing data between databases isolated by a network gate according to claim 1, characterized in that: If the data changes of the data table are recorded in the monitoring intermediate table, the data of the monitoring intermediate table is parsed into SQL statements and synchronized to the data export table, specifically including: Based on job scheduling, the stored procedure is called regularly to parse the data in the monitoring intermediate table into SQL statements for inserting, modifying or deleting; Insert the parsed SQL statement into the data export table, and mark the updated data in the data export table as unexported data.

3. The method for synchronizing data between databases isolated by a network gate according to claim 2, characterized in that: Based on database job scheduling and stored procedures, the unexported data in the data export table is converted into the first export file, specifically including: Encrypt the data marked as not exported and write them into a text file based on 3DES encryption; And compress the text file into a compressed file as the first export file.

4. The method for synchronizing data between databases isolated by a network gate according to claim 1, characterized in that: It also includes re-editing and re-importing and exporting SQL statements that failed in import and export based on the data import table and data export table.

5. The method for synchronizing data between databases isolated by a network gate according to any one of claims 1 to 4, characterized in that: The first database or the second database is a SQLSERVER database or a domestically produced DAMO database.

6. The method for synchronizing data between databases isolated by a network gate according to claim 5, characterized in that: The first database or the second database monitors the data changes in the data table and records them in the monitoring intermediate table or the data export table, specifically including: If the first database and / or the second database is a domestic DAMO database, the domestic DAMO database monitors the addition, deletion or update of data in the data table based on table-level triggers, and records the data in the data export table; If the first database and / or the second database is SQLSERVER, the data in the SQLSERVER CDC monitoring data table is added, deleted or updated and recorded in the monitoring intermediate table.

7. The method for synchronizing data between databases isolated by a network gate according to claim 6, characterized in that: Also includes: If the domestic DAMO database monitors data updates in the data table based on table-level triggers, the data before and after the update operation will be compared. If there is no difference between the data before and after the update, it will not be recorded in the data export table; If SQLSERVER updates data in the SQLSERVER CDC monitoring data table, the difference between the new and old data is compared through the stored procedure. If there is no difference, it will not be updated to the monitoring intermediate table.

Images