Database incremental synchronization method, system, electronic device and medium

Abstract

The present invention provides a database incremental synchronization method, system, electronic device and medium, belonging to the database field, the method comprising: obtaining a target version number, and obtaining a corresponding version of the SQL statement set from a code base according to the target version number; for any SQL statement, parsing the SQL statement to obtain the target SQL statement; encrypting the target SQL statement to obtain SQL ciphertext; querying whether the SQL ciphertext exists in the execution record data table corresponding to the target version number; if so, not executing the corresponding SQL statement; otherwise, querying whether the keyword in the SQL statement exists in the filtering configuration file, if so, not executing the SQL statement; otherwise, executing the SQL statement to perform incremental synchronization on the database, and storing the SQL ciphertext in the execution record data table corresponding to the target version number. The present invention automates the process of database incremental synchronization, reduces manual intervention, and improves synchronization efficiency.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a database incremental synchronization method, system, electronic equipment and medium. Background Art

[0002] Since general system development currently adopts an agile model, system functions change frequently. That is, when the existing system version is upgraded, due to changes in corresponding functions, manual inspection and execution of DDL (Data Definition Language) and DML (Data Manipulation Language) statements are often required to change the database structure, add, modify, and delete basic configuration data, resulting in extremely low efficiency of database synchronization. Summary of the invention

[0003] The purpose of the present invention is to provide a database incremental synchronization method, system, electronic device and medium, which can improve the efficiency of database synchronization.

[0004] To achieve the above object, the present invention provides the following solutions:

[0005] A database incremental synchronization method, comprising:

[0006] Obtaining a target version number, and obtaining a corresponding version of an SQL statement set from a code library according to the target version number; the code library pre-stores a plurality of version numbers and SQL statements corresponding to each version number; the SQL statement set includes one or more SQL statements;

[0007] For any SQL statement, parse the SQL statement to obtain a target SQL statement;

[0008] Encrypting the target SQL statement to obtain SQL ciphertext;

[0009] Check whether the SQL ciphertext exists in the execution record data table corresponding to the target version number;

[0010] If the SQL ciphertext exists in the execution record data table corresponding to the target version number, the corresponding SQL statement is not executed;

[0011] If the SQL ciphertext does not exist in the execution record data table corresponding to the target version number, query whether the keyword in the SQL statement exists in the filtering configuration file; the filtering configuration file pre-stores the keyword to be filtered;

[0012] If the keyword in the SQL statement exists in the filtering configuration file, the SQL statement is not executed;

[0013] If the keyword in the SQL statement does not exist in the filtering configuration file, the SQL statement is executed to incrementally synchronize the database, and the SQL ciphertext is stored in the execution record data table corresponding to the target version number.

[0014] Optionally, the code base is a GIT code repository.

[0015] Optionally, parsing the SQL statement to obtain a target SQL statement specifically includes:

[0016] Format the SQL statement using a SQL grammar interpreter to obtain a standard SQL statement;

[0017] Determine whether the standard SQL statement has a syntax error;

[0018] If it exists, the standard SQL statement is filtered, and a prompt message is generated to obtain the target SQL statement input by the user;

[0019] If it does not exist, the standard SQL statement is the target SQL statement.

[0020] Optionally, after obtaining the target SQL statement input by the user, parsing the SQL statement to obtain the target SQL statement further includes:

[0021] The target SQL statement input by the user is stored in the parsing library to optimize the SQL grammar interpreter.

[0022] Optionally, the SQL syntax interpreter is a CCJSqlParserUtil parsing tool.

[0023] Optionally, encrypting the target SQL statement to obtain SQL ciphertext specifically includes:

[0024] The target SQL statement is encrypted using the Md5Util tool class to obtain SQL ciphertext.

[0025] To achieve the above object, the present invention also provides the following solution:

[0026] A database incremental synchronization system, comprising:

[0027] A statement acquisition unit, used to acquire a target version number, and acquire a corresponding version of an SQL statement set from a code library according to the target version number; the code library pre-stores a plurality of version numbers and SQL statements corresponding to each version number; the SQL statement set includes one or more SQL statements;

[0028] A parsing unit, connected to the statement acquisition unit, for parsing any SQL statement to obtain a target SQL statement;

[0029] An encryption unit, connected to the parsing unit, for encrypting the target SQL statement to obtain an SQL ciphertext;

[0030] A first query unit, connected to the encryption unit, for querying whether the SQL ciphertext exists in the execution record data table corresponding to the target version number;

[0031] a first filtering unit, connected to the first query unit, configured to not execute a corresponding SQL statement if the SQL ciphertext exists in the execution record data table corresponding to the target version number;

[0032] A second query unit, connected to the first query unit, is used to query whether the keyword in the SQL statement exists in the filtering configuration file if the SQL ciphertext does not exist in the execution record data table corresponding to the target version number; the filtering configuration file pre-stores the keyword to be filtered;

[0033] a second filtering unit, connected to the second query unit, configured not to execute the SQL statement if the keyword in the SQL statement exists in the filtering configuration file;

[0034] An execution unit, connected to the second query unit, is used to execute the SQL statement if the keyword in the SQL statement does not exist in the filtering configuration file, so as to incrementally synchronize the database and store the SQL ciphertext in the execution record data table corresponding to the target version number.

[0035] To achieve the above object, the present invention also provides the following solution:

[0036] An electronic device includes a memory and a processor, wherein the memory is used to store a computer program, and the processor runs the computer program to enable the electronic device to execute the above-mentioned database incremental synchronization method.

[0037] To achieve the above object, the present invention also provides the following solution:

[0038] A computer-readable storage medium stores a computer program, which implements the above-mentioned database incremental synchronization method when executed by a processor.

[0039] According to the specific embodiments provided by the present invention, the present invention discloses the following technical effects:

[0040] The present invention automatically obtains SQL statements corresponding to a specified version number from a code library, and automatically parses, encrypts, filters and executes the SQL statements, thereby greatly reducing manual participation and improving the efficiency of database synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0042] Figure 1 It is a flow chart of the incremental synchronization method of the database of the present invention;

[0043] Figure 2 This is a schematic diagram of the incremental synchronization process of the database;

[0044] Figure 3 It is a module schematic diagram of the database incremental synchronization system of the present invention.

[0045] Explanation of symbols:

[0046] Statement acquisition unit-1, parsing unit-2, encryption unit-3, first query unit-4, first filtering unit-5, second query unit-6, second filtering unit-7, execution unit-8. DETAILED DESCRIPTION

[0047] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0048] The purpose of the present invention is to provide a database incremental synchronization method, system, electronic device and medium. By designing a database incremental synchronization program, it is possible to automatically obtain database change statements submitted by developers, parse and filter them, and then execute them to improve the efficiency of database synchronization.

[0049] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0050] First, the database terminology used in the present invention is introduced:

[0051] SQL (Structured Query Language) is a database query and programming language used to access data as well as query, update and manage relational database systems.

[0052] DDL: Data Definition Language. Common DDL statements include creating a database, creating a database table, modifying a database table, deleting a database table, creating a view, modifying a view, etc.

[0053] DML: Data manipulation language. Common DML statements include querying, adding, modifying, and deleting data.

[0054] Embodiment 1

[0055] like Figure 1 and Figure 2 As shown, this embodiment provides a database incremental synchronization method, including:

[0056] S1: Obtain a target version number, and obtain a corresponding version of the SQL statement set from the code base according to the target version number. The SQL statement set includes one or more SQL statements.

[0057] Specifically, the code library pre-stores multiple version numbers and SQL statements corresponding to each version number. In this embodiment, the code library is a GIT code repository. That is, the code and database execution statements generated by developers during the development process are uploaded to the GIT code repository. The purpose of the present invention is to synchronize the data in the GIT code repository to a target folder to save a copy.

[0058] Software projects will determine the software version during the daily development process. Each version involves the adjustment of several important functions, and the version number is such as V1.1.0.0. The new functions of each version involve corresponding database changes, which are specifically reflected in DDL and DML database statements. Database statements are stored in different folders according to different version numbers. The present invention reads SQL statements from the corresponding version folder of the GIT code library according to the manually input target version number and saves them to the background folder.

[0059] S2: For any SQL statement, parse the SQL statement to obtain a target SQL statement.

[0060] Furthermore, S2 specifically includes:

[0061] (1) Use a SQL grammar interpreter to format the SQL statement (remove extra spaces, adjust to a regular style, etc.) to obtain a standard SQL statement. In this embodiment, the SQL grammar interpreter is the CCJSqlParserUtil parsing tool.

[0062] (2) Determine whether the standard SQL statement has a syntax error. If so, filter the standard SQL statement, generate a prompt message, and obtain the target SQL statement input by the user. If not, the standard SQL statement is the target SQL statement.

[0063] Specifically, if the parsing fails, a pop-up window prompt is generated to provide the user with an opportunity to select an SQL statement that still needs to be executed. The SQL statement manually selected by the user is the target SQL statement.

[0064] After obtaining the target SQL statement input by the user, the target SQL statement input by the user is stored in the parsing library to optimize the SQL grammar interpreter. The third-party parsing tool CCJSqlParserUtil, which has limited functions and is not suitable for some actual work scenarios, is enriched to enhance parsing capabilities. When similar SQL statements are encountered again in the future, CCJSqlParserUtil and manual selection records can be used at the same time to parse successfully without manual intervention, and it has adaptive and self-learning capabilities. SQL statements that are not parsed successfully and are not manually selected will not be transferred to the subsequent decryption and execution process.

[0065] S3: Encrypt the target SQL statement to obtain SQL ciphertext. Preferably, the target SQL statement is encrypted using the Md5Util tool class.

[0066] Md5Util is a mature third-party tool that can be directly called with one line of code to implement encryption. Encryption is to convert SQL statements of varying lengths and complexities into ciphertext of uniform length. Subsequent use of this ciphertext for matching queries can greatly improve the efficiency of query matching.

[0067] S4: Query whether the SQL ciphertext exists in the execution record data table corresponding to the target version number. The execution record data table is used to store the SQL ciphertext that has been executed for the corresponding target version number. The execution log (execution record data table) is preloaded into the cache after obtaining the version number to improve query efficiency.

[0068] S5: If the SQL ciphertext exists in the execution record data table corresponding to the target version number, the corresponding SQL statement is not executed.

[0069] S6: If the SQL ciphertext does not exist in the execution record data table corresponding to the target version number, query whether the keyword in the SQL statement exists in the filtering configuration file. If the keyword in the SQL statement exists in the filtering configuration file, the SQL statement is not executed. That is, the SQL ciphertext has been executed and will not be executed again. The keywords to be filtered are pre-stored in the filtering configuration file.

[0070] The filtering rules are configured separately using configuration files, and keyword matching filtering is used to exclude SQL statements that you do not want to execute. The filtering configuration file is preloaded during initialization to improve matching efficiency. Specifically, users can configure certain keywords in the filtering configuration file according to actual business needs, such as droptable, delete, etc., or use regular expression configuration to protect key configuration data. Users save SQL keywords that they do not want to execute in the filtering configuration file, and can use this file to filter out related SQL without executing it.

[0071] S7: If the keyword in the SQL statement does not exist in the filtering configuration file, the SQL statement is executed to incrementally synchronize the database, and the SQL ciphertext is stored in the execution record data table corresponding to the target version number.

[0072] In this embodiment, each SQL statement is executed using an independent transaction to ensure that the execution of SQL statements does not interfere with each other, and the SQL statement that reports an error will not affect the normal execution of other SQL statements. After the program executes the SQL statement, the corresponding SQL ciphertext is stored in the execution record data table. This is convenient for database maintenance personnel to track and review the execution status.

[0073] As a specific implementation method, the database involved in the present invention is an insurance claim database, and the version number is the version number of the insurance claim software. The insurance claim database includes multiple claim data tables. The data in the claim data table includes version number, serial number, policy number, report number, processing location and other data.

[0074] During the development or update process of insurance claims software, multiple version numbers and SQL statements with new functions of each version number will be generated. When the insurance claims database is incrementally updated, the method of the present invention is used to automatically obtain the SQL statement of the specified version number from the code library, and perform the process of parsing, encryption, filtering and execution.

[0075] The present invention is developed using Java language and adopts a C / S (Client / Server) structure. Compared with the existing database synchronization method with full manual participation, the present invention integrates the original database incremental synchronization process of acquisition, analysis, judgment and execution with full manual participation, and changes it to basically automatic execution by the program. Only two places require manual participation: one is to select the target version number; the other is to manually select the target SQL statement after the analysis fails. However, it has adaptive and self-learning analysis capabilities, and adds the results of each analysis and manual selection to the analysis library. The analysis library is continuously enriched, and the intervention of manual selection can be reduced in the analysis of subsequent versions, which greatly improves the efficiency of database synchronization.

[0076] Embodiment 2

[0077] In order to execute the method corresponding to the above-mentioned embodiment 1 to achieve corresponding functions and technical effects, a database incremental synchronization system is provided below.

[0078] like Figure 3 As shown, the database incremental synchronization system provided in this embodiment includes: a statement acquisition unit 1, a parsing unit 2, an encryption unit 3, a first query unit 4, a first filtering unit 5, a second query unit 6, a second filtering unit 7 and an execution unit 8.

[0079] The statement acquisition unit 1 is used to acquire a target version number, and acquire a corresponding version of the SQL statement set from the code library according to the target version number. The code library pre-stores multiple version numbers and SQL statements corresponding to each version number. The SQL statement set includes one or more SQL statements.

[0080] The parsing unit 2 is connected to the statement acquisition unit 1, and is used for parsing any SQL statement to obtain a target SQL statement.

[0081] The encryption unit 3 is connected to the parsing unit 2, and is used to encrypt the target SQL statement to obtain an SQL ciphertext.

[0082] The first query unit 4 is connected to the encryption unit 3, and is used to query whether the SQL ciphertext exists in the execution record data table corresponding to the target version number.

[0083] The first filtering unit 5 is connected to the first query unit 4, and the first filtering unit 5 is used for not executing the corresponding SQL statement if the SQL ciphertext exists in the execution record data table corresponding to the target version number.

[0084] The second query unit 6 is connected to the first query unit 4. The second query unit 6 is used to query whether the keyword in the SQL statement exists in the filtering configuration file if the SQL ciphertext does not exist in the execution record data table corresponding to the target version number; the filtering configuration file pre-stores the keywords to be filtered.

[0085] The second filtering unit 7 is connected to the second query unit 6, and is configured to not execute the SQL statement if the keyword in the SQL statement exists in the filtering configuration file.

[0086] The execution unit 8 is connected to the second query unit 7. The execution unit 8 is used to execute the SQL statement if the keyword in the SQL statement does not exist in the filtering configuration file, so as to incrementally synchronize the database and store the SQL ciphertext in the execution record data table corresponding to the target version number.

[0087] Compared with the prior art, the database incremental synchronization system provided in this embodiment has the same beneficial effects as the database incremental synchronization method provided in the first embodiment, which will not be described in detail here.

[0088] Embodiment 3

[0089] This embodiment provides an electronic device, including a memory and a processor, wherein the memory is used to store a computer program, and the processor runs the computer program to enable the electronic device to execute the database incremental synchronization method of the first embodiment.

[0090] Optionally, the above-mentioned electronic device may be a server.

[0091] In addition, an embodiment of the present invention further provides a computer-readable storage medium storing a computer program, which implements the database incremental synchronization method of the first embodiment when executed by a processor.

[0092] The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referenced to each other.

[0093] The principles and implementation methods of the present invention are described in this article using specific examples. The description of the above embodiments is only used to help understand the method and core idea of ​​the present invention. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting the present invention.

Claims

1. A database incremental synchronization method, characterized in that: The database incremental synchronization method comprises: Obtaining a target version number, and obtaining a corresponding version of an SQL statement set from a code library according to the target version number; the code library pre-stores a plurality of version numbers and SQL statements corresponding to each version number; the SQL statement set includes one or more SQL statements; For any SQL statement, the SQL statement is parsed to obtain a target SQL statement, specifically including: formatting the SQL statement using an SQL grammar interpreter to obtain a standard SQL statement; determining whether the standard SQL statement has a grammatical error; if so, that is, parsing fails, filtering the standard SQL statement, and generating a pop-up window prompt to provide a user with a selection of SQL statements that still need to be executed, the SQL statement manually selected by the user is the target SQL statement, and the target SQL statement input by the user is stored in a parsing library to optimize the SQL grammar interpreter; if not, the standard SQL statement is the target SQL statement; Encrypting the target SQL statement to obtain SQL ciphertext; Check whether the SQL ciphertext exists in the execution record data table corresponding to the target version number; If the SQL ciphertext exists in the execution record data table corresponding to the target version number, the corresponding SQL statement is not executed; If the SQL ciphertext does not exist in the execution record data table corresponding to the target version number, query whether the keyword in the SQL statement exists in the filtering configuration file; the filtering configuration file pre-stores the keyword to be filtered; If the keyword in the SQL statement exists in the filtering configuration file, the SQL statement is not executed; If the keyword in the SQL statement does not exist in the filtering configuration file, the SQL statement is executed to incrementally synchronize the database, and the SQL ciphertext is stored in the execution record data table corresponding to the target version number.

2. The database incremental synchronization method according to claim 1, characterized in that: The code base is a GIT code repository.

3. The database incremental synchronization method according to claim 1, characterized in that: The SQL syntax interpreter is the CCJSqlParserUtil parsing tool.

4. The database incremental synchronization method according to claim 1, characterized in that: Encrypting the target SQL statement to obtain SQL ciphertext specifically includes: The target SQL statement is encrypted using the Md5Util tool class to obtain SQL ciphertext.

5. A database incremental synchronization system, applied to the database incremental synchronization method according to any one of claims 1 to 4, characterized in that: The database incremental synchronization system comprises: A statement acquisition unit, used to acquire a target version number, and acquire a corresponding version of an SQL statement set from a code library according to the target version number; the code library pre-stores a plurality of version numbers and SQL statements corresponding to each version number; the SQL statement set includes one or more SQL statements; A parsing unit, connected to the statement acquisition unit, for parsing any SQL statement to obtain a target SQL statement; An encryption unit, connected to the parsing unit, for encrypting the target SQL statement to obtain an SQL ciphertext; A first query unit, connected to the encryption unit, for querying whether the SQL ciphertext exists in the execution record data table corresponding to the target version number; a first filtering unit, connected to the first query unit, configured to not execute a corresponding SQL statement if the SQL ciphertext exists in the execution record data table corresponding to the target version number; A second query unit, connected to the first query unit, is used to query whether the keyword in the SQL statement exists in the filtering configuration file if the SQL ciphertext does not exist in the execution record data table corresponding to the target version number; the filtering configuration file pre-stores the keyword to be filtered; a second filtering unit, connected to the second query unit, configured not to execute the SQL statement if the keyword in the SQL statement exists in the filtering configuration file; An execution unit, connected to the second query unit, is used to execute the SQL statement if the keyword in the SQL statement does not exist in the filtering configuration file, so as to incrementally synchronize the database and store the SQL ciphertext in the execution record data table corresponding to the target version number.

6. An electronic device, characterized in that: The electronic device comprises a memory and a processor, wherein the memory is used to store a computer program, and the processor runs the computer program to enable the electronic device to execute the database incremental synchronization method according to any one of claims 1 to 4.

7. A computer-readable storage medium, characterized in that: It stores a computer program, which, when executed by a processor, implements the database incremental synchronization method as described in any one of claims 1 to 4.

Images