Cross-database SQL automatic adaptation detection method and system

Abstract

The invention relates to the technical field of database migration, in particular to a cross-database SQL automatic adaptation detection method and system. According to the cross-database SQL automatic adaptation detection method, a source database and a target database are installed and configured, structured query language SQL statements during source code running are captured in a multi-dimensional mode, and the method comprises the two parts of static code analysis and dynamic running monitoring; a difference knowledge base is constructed, and cross-database structured query language (SQL) automatic conversion is achieved through an intelligent conversion engine; bidirectional conversion verification is carried out, and result comparison and boundary testing are executed; and establishing a risk scoring mechanism, quantifying conversion risks, and performing grading processing according to risk scores. According to the cross-database SQL automatic adaptation detection method and system, full-automatic structured query language SQL grammar detection and intelligent repair during database migration are achieved, and the problems that traditional manual migration is low in efficiency and insufficient in test coverage rate are effectively solved.

Description

Technical Field

[0001] The present invention relates to the technical field of database migration, and particularly relates to a cross-database SQL automated adaptation detection method and system. Background Art

[0002] Currently, there are three major defects in database migration technology: 1) Traditional migration tools (such as flyway) only support version control and lack the ability to convert syntax; 2) The manual comparison method is time-consuming and prone to missing edge cases. According to statistics, about 38% of syntax errors are not detected during the testing phase; 3) Existing automated solutions (such as SQL translators) have the risk of semantic distortion and cannot guarantee the consistency of SQL execution results before and after conversion.

[0003] In order to solve the problems of low efficiency of manual adaptation of SQL during database migration, insufficient test coverage, and difficulty in guaranteeing semantic consistency, the present invention proposes a cross-database SQL automated adaptation detection method and system. Summary of the Invention

[0004] The present invention provides a simple and efficient cross-database SQL automated adaptation detection method and system to make up for the defects of the existing technology.

[0005] The present invention is implemented through the following technical solutions: A cross-database SQL automated adaptation detection method, characterized by comprising the following steps: Step S1: Install the target database and ensure that the source database and target database services are both running normally; Configure the connection information of the source database and the target database, including the database address, port, username, and password; Step S2: Capture the Structured Query Language (SQL) statements during the runtime of the source code in multiple dimensions, including static code parsing and dynamic runtime monitoring; The static code parsing process is as follows: Configure the code file path for static parsing, extract SQL statements from the configuration file, construct a multi-database Structured Query Language (SQL) parser, parse the Structured Query Language (SQL) statements, and generate a standardized Abstract Syntax Tree (AST) for subsequent processing; During the parsing process, the multi-database Structured Query Language (SQL) parser can identify the dynamic parameters in the Structured Query Language (SQL) template and retain the context information of the dynamic parameters so that the parameter values can be correctly replaced when generating the final Structured Query Language (SQL) statements; The dynamic runtime monitoring process is as follows: Configure the application context for dynamic tracing, implement structured query language (SQL) capture through bytecode instrumentation, achieve zero-code transformation, and avoid intruding into the business logic; Associate the call stack and request parameters, restore the SQL execution scenario, and achieve precise tracing; Preset the sampling rate and threshold, balance the monitoring coverage and system overhead, and achieve performance balance; In step S2, the static code parsing supports multiple programming languages and frameworks, specifically including: Java language: Use the MyBatis or Hibernate framework; Python language: Use the Django or Flask framework; Configuration file format: Support extracting SQL statements from configuration files in Extensible Markup Language (XML) format or JSON format; Build a multi-database SQL parser based on the lexical and syntactic analyzer generator tool ANTLR 4.9+.

[0006] In step S2, during dynamic runtime monitoring, based on bytecode enhancement technology, dynamically intercept SQL execution during application runtime without modifying the business code; Record the call stack, request parameters, execution time, and user information of SQL statements, implement context information association, and support end-to-end tracing; The sampling rate is default set to 10%, and the threshold is 1 second. When the execution time of a single SQL statement exceeds 1 second, it is forced to be recorded and marked as a slow SQL statement to ensure that key issues are not missed.

[0007] Step S3: Build a differential knowledge base through a hierarchical knowledge base and intelligent decision-making, including predefined grammar layer rules, establishing function layer mappings, and establishing isolation level mapping matrices in the transaction / lock mechanism layer; Then, through the intelligent conversion engine, achieve automated cross-database SQL conversion, taking into account both accuracy and performance optimization; In step S3, manually organize the differential rules of the grammar layer, function layer, and transaction layer between different databases, build a differential knowledge base, and configure the storage location and access permissions of the differential knowledge base. Preset no less than 2000 predefined rules in the grammar layer, and define the grammar differences between different databases in JSON format; Customize no less than 300 commonly used conversion functions in the function layer, including parameter order adjustment logic; The intelligent conversion engine supports context-aware decision-making and multi-scheme evaluation, generates three conversion schemes for the same Structured Query Language (SQL) according to the rules in the difference knowledge base, and customizes the selection of the optimal conversion strategy based on the SQL type, execution frequency, and data table structure; The SQL types include Data Manipulation Language (DML) and Data Definition Language (DDL).

[0008] In step S3, during the migration process, the difference knowledge base is dynamically updated using a weight evaluation algorithm based on the usage times, success rate, and conflict times of the conversion rules.

[0009] Step S4: Ensure the accuracy and reliability of SQL cross-database conversion through two-way conversion verification, execution result comparison, and boundary testing; In step S4, the implementation process is as follows: Step S4.1: Two-way conversion verification Forward conversion: Convert the source database SQL to the target database SQL; Reverse backtranslation: Through the reverse rule base, convert the target database SQL back to the source database SQL; Structure comparison: Compare the Abstract Syntax Trees (ASTs) of the original SQL and the reverse backtranslated SQL, with a similarity requirement of not less than 90%; Step S4.2: Execution result comparison Execute the original SQL and the converted SQL respectively to verify whether the results are consistent; During verification, compare key metrics, including index usage, join order, and scanned row count, with a similarity requirement of more than 85%; Step S4.3: Boundary condition testing Automatically generate test cases: Customize boundary test data for the conversion rules, including null values, maximum values, and specially selected special characters; Exception handling verification: Verify whether the error handling logic of the converted SQL in the scenarios of null value and constraint conflict is consistent with the original SQL.

[0010] Step S5: Establish a risk scoring mechanism to quantify conversion risks, including syntax compatibility, data consistency, and performance impact, providing a basis for repair strategies; Perform hierarchical processing based on the risk score, and dynamically allocate repair priorities through the risk score to balance automation efficiency and manual precise control.

[0011] In step S5, it is processed in three levels according to the risk score; If the risk score is lower than 30, it is directly repaired automatically by applying the optimal conversion rule without manual intervention; If the risk score is not lower than 30 and less than 70, a marked SQL suggestion is generated, and after manual confirmation, it is executed to achieve semi-automatic repair; If the risk score is not lower than 70, it is marked as high risk, audited and decided by humans, and an alternative implementation solution is provided.

[0012] A cross-database SQL automated adaptation detection system for implementing the above method, including: A system configuration module, responsible for installing the target database and ensuring that both the source database and the target database services are running properly; at the same time, configuring the connection information of the source database and the target database, including the database address, port, username, and password; A structured query language SQL collection module, responsible for capturing structured query language SQL statements during the runtime of the source code in multiple dimensions, including static code parsing and dynamic runtime monitoring; A difference knowledge base, responsible for pre-defining syntax layer rules, establishing function layer mappings, and establishing an isolation level mapping matrix at the transaction / lock mechanism layer; An intelligent conversion engine, responsible for generating three conversion schemes for the same structured query language SQL according to the rules in the difference knowledge base, and customizing and selecting the optimal conversion strategy according to the structured query language SQL type, execution frequency, and data table structure; A semantic equivalence verification module, responsible for ensuring the accuracy and reliability of the cross-database conversion of structured query language SQL through two-way conversion verification, execution result comparison, and boundary testing; A risk assessment and repair module, responsible for establishing a risk scoring mechanism, quantifying conversion risks, and performing hierarchical processing according to the risk score.

[0013] A computing device, characterized by including: One or more processors, one or more memories, and one or more programs, where one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing any of the above methods.

[0014] A readable storage medium, characterized in that: a computer program is stored on the readable storage medium, and when the computer program is executed by a processor, the above-mentioned method is implemented.

[0015] The beneficial effects of the present invention are as follows: The cross-database SQL automated adaptation detection method and system achieve fully automated structured query language (SQL) syntax detection and intelligent repair during database migration, effectively solving the problems of low efficiency of traditional manual migration and insufficient test coverage. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0017] Attached Figure 1 is a schematic diagram of the cross-database SQL automated adaptation detection system of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0018] In order to enable those skilled in the art of the present technology to better understand the technical solutions in the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0019] The cross-database SQL automated adaptation detection method includes the following steps: Step S1: Install the target database (such as MySQL database, DM, Oracle database, Highgo, etc.), and ensure that both the source database and the target database services are running normally; Configure the connection information of the source database and the target database, including the database address, port, username, and password; Step S2: Capture structured query language (SQL) statements during the runtime of the source code from multiple dimensions, including static code parsing and dynamic runtime monitoring; The process of static code parsing is as follows: Configure the code file path for static parsing, extract SQL statements from the configuration file, construct a multi-database structured query language (SQL) parser, parse the structured query language (SQL) statements, and generate a standardized abstract syntax tree (AST) for subsequent processing; During the parsing process, the multi-database structured query language (SQL) parser can identify the dynamic parameters in the structured query language (SQL) template and retain the context information of the dynamic parameters, so that the parameter values can be correctly replaced when generating the final structured query language (SQL) statements; For example: #{}: Usually used for pre-compiled parameters to prevent SQL injection in Structured Query Language (SQL).

[0020] ${}: Used to directly insert parameter values, but there may be a risk of SQL injection in SQL.

[0021] The process of dynamic runtime monitoring is as follows: Configure the application context for dynamic tracing, capture SQL in SQL through bytecode instrumentation, achieve zero-code transformation, and do not need to invade the business logic; Associate the call stack and request parameters, restore the SQL execution scenario in SQL, and achieve accurate tracing; Pre-set the sampling rate and threshold, balance the monitoring coverage and system overhead, and achieve performance balance; In step S2, the static code parsing supports multiple programming languages and frameworks, specifically including: Java language: Use the MyBatis or Hibernate framework; Python language: Use the Django or Flask framework; Configuration file format: Support extracting SQL statements in SQL from configuration files in XML format or JSON format; Build a multi-database SQL parser based on the lexical and syntactic analyzer generation tool ANTLR 4.9+.

[0022] Table 1 Instance of the isolation level mapping matrix

[0023] In step S2, during dynamic runtime monitoring, based on bytecode enhancement technologies (such as the bytecode dynamic operation library Byte Buddy or the AOP framework AspectJ), dynamically intercept SQL execution in SQL during application runtime without modifying the business code; Record the call stack, request parameters, execution time consumption, and user information of SQL statements in SQL, implement context information association, and support full-link tracing; The sampling rate is default set to 10%, and the threshold is 1 second. When the execution of a single SQL statement in SQL exceeds 1 second, it is forced to record and marked as a slow SQL statement to ensure that key issues are not missed.

[0024] Step S3: Build a differential knowledge base through a hierarchical knowledge base and intelligent decision-making, including pre-defining grammar layer rules, establishing function layer mappings, and establishing an isolation level mapping matrix in the transaction / lock mechanism layer; Then, through the intelligent conversion engine, realize the automatic conversion of Structured Query Language (SQL) across databases (such as from MySQL database to DM database), taking into account both accuracy and performance optimization; In step S3, manually sort out the difference rules between different databases at the syntax layer, function layer, and transaction layer, build a difference knowledge base, and configure the storage location and access permissions of the difference knowledge base. Preset no less than 2,000 predefined rules at the syntax layer, and define the syntax differences between different databases in JSON format; for example: { "MySQL": { "LIMIT ? OFFSET ?": "WHERE ROWNUM<= (? + ?) MINUS WHERE ROWNUM<= ?", "DATE_FORMAT(date, '%Y-%m-%d')": "TO_CHAR(date, 'YYYY-MM-DD')" }, "DM": { "NVL(value, default)": "COALESCE(value, default)" } } Customize no less than 300 commonly used conversion functions at the function layer, including the logic for adjusting the parameter order (such as the MySQL database string concatenation function CONCAT to the DM database string concatenation function with delimiter CONCAT_WS); The intelligent conversion engine supports context-aware decision-making and multi-scheme evaluation, generates 3 conversion schemes for the same Structured Query Language (SQL) according to the rules in the difference knowledge base, and customizes the selection of the optimal conversion strategy according to the SQL type, execution frequency, and data table structure; The SQL types include Data Manipulation Language (DML) and Data Definition Language (DDL).

[0025] In step S3, during the migration process, dynamically update the difference knowledge base using a weight evaluation algorithm according to the usage times, success rate, and conflict times of the conversion rules.

[0026] Step S4: Ensure the accuracy and reliability of SQL cross-database conversion through two-way conversion verification, execution result comparison, and boundary testing; In step S4, the implementation process is as follows: Step S4.1: Two-way conversion verification Forward conversion: Convert the source database Structured Query Language (SQL) to the target database SQL; Reverse backtranslation: Through the reverse rule library, convert the target database SQL back to the source database SQL; Structure comparison: Compare the Abstract Syntax Trees (ASTs) of the original SQL and the reverse backtranslated SQL, requiring a similarity of no less than 90%; Step S4.2, Execution result comparison Execute the original SQL and the converted SQL respectively to verify whether the results are consistent; For example, use the execution plan parsing command EXPLAIN (for MySQL database) or the execution plan analysis command EXPLAIN PLAN (for DM) to obtain the execution plan.

[0027] During verification, compare the key metrics, including index usage, join order, and number of scanned rows, requiring a similarity of more than 85%; Step S4.3, Boundary condition testing Automatically generate test cases: Customize and generate boundary test data for the conversion rules, including null values, maximum values, and special characters selected customarily; Exception handling verification: Verify whether the error handling logic of the converted SQL in the scenarios of null value and constraint conflict is consistent with the original SQL.

[0028] Step S5, Establish a risk scoring mechanism to quantify the conversion risks, including syntax compatibility, data consistency, and performance impact, providing a basis for the repair strategy; Perform classification processing according to the risk score, dynamically allocate repair priorities through the risk score to balance the automation efficiency and manual precise control.

[0029] In the said Step S5, perform processing in three levels according to the risk score; If the risk score is less than 30, directly apply the optimal conversion rule for automatic repair without manual intervention; If the risk score is not less than 30 and less than 70, generate marked SQL suggestions, prompt for manual confirmation and then execute to achieve semi-automatic repair; If the risk score is not less than 70, mark it as high risk, conduct manual review and decision-making, and provide alternative implementation solutions.

[0030] This cross-database SQL automated adaptation detection system is used to implement the above method, including: The system configuration module is responsible for installing the target database (such as MySQL database, DM, Oracle database, Highgo, etc.) and ensuring that both the source database and the target database services are running properly. At the same time, it configures the connection information of the source database and the target database, including the database address, port, username, and password. The Structured Query Language (SQL) collection module is responsible for capturing SQL statements during the runtime of the source code in multiple dimensions, including static code parsing and dynamic runtime monitoring. The difference knowledge base is responsible for predefining syntax layer rules, establishing function layer mappings, and establishing an isolation level mapping matrix at the transaction / lock mechanism layer. The intelligent conversion engine is responsible for generating three conversion schemes for the same SQL according to the rules in the difference knowledge base, and customizing and selecting the optimal conversion strategy according to the SQL type, execution frequency, and data table structure. The semantic equivalence verification module is responsible for ensuring the accuracy and reliability of SQL cross-database conversion through two-way conversion verification, execution result comparison, and boundary testing. The risk assessment and repair module is responsible for establishing a risk scoring mechanism to quantify conversion risks, including syntax compatibility, data consistency, and performance impact, providing a basis for repair strategies; and performing hierarchical processing according to the risk score, dynamically allocating repair priorities through the risk score to balance automation efficiency and manual precision control.

[0031] The computer-readable storage medium storing one or more programs, the one or more programs including instructions that, when executed by a computing device, cause the computing device to execute any of the above methods.

[0032] The computing device includes: One or more processors, one or more memories, and one or more programs, where the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing any of the above methods.

[0033] In summary, the cross-database SQL automated adaptation detection method and system, by constructing a multi-dimensional SQL capture mechanism and a dynamically updated difference knowledge base, combined with semantic equivalence verification and context-aware conversion technology, realizes the full-automatic SQL syntax detection and intelligent repair when migrating from a MySQL database to databases such as DM and Highgo, effectively solving the problems of low efficiency of traditional manual migration and insufficient test coverage.

[0034] The above-described embodiments are only one of the specific implementation manners of the present invention, and the ordinary variations and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A cross-database SQL automated adaptation detection method, characterized in that: It includes the following steps: Step S1: Install the target database and ensure that both the source database and the target database services are running properly; Configure the connection information of the source database and the target database, including the database address, port, username, and password; Step S2: Capture the Structured Query Language (SQL) statements during the runtime of the source code in multiple dimensions, including static code parsing and dynamic runtime monitoring; The process of static code parsing is as follows: Configure the code file path for static parsing, extract SQL statements from the configuration file, build a multi-database Structured Query Language (SQL) parser, parse the SQL statements, and generate a standardized Abstract Syntax Tree (AST) for subsequent processing; During the parsing process, the multi-database Structured Query Language (SQL) parser can identify the dynamic parameters in the SQL template and retain the context information of the dynamic parameters so that the parameter values can be correctly replaced when generating the final SQL statements; The process of dynamic runtime monitoring is as follows: Configure the application context for dynamic tracing, and implement SQL capture through bytecode instrumentation, achieving zero-code transformation without invading the business logic; Associate the call stack and request parameters to restore the SQL execution scenario and achieve accurate tracing; Pre-set the sampling rate and threshold to balance monitoring coverage and system overhead and achieve performance balance; Step S3: Build a differential knowledge base through a hierarchical knowledge base and intelligent decision-making, including pre-defining grammar layer rules, establishing function layer mappings, and establishing an isolation level mapping matrix at the transaction / lock mechanism layer; Then, implement automated SQL conversion across databases through an intelligent conversion engine, taking into account both accuracy and performance optimization; Step S4: Ensure the accuracy and reliability of SQL conversion across databases through two-way conversion verification, execution result comparison, and boundary testing; Step S5: Establish a risk scoring mechanism to quantify conversion risks, including syntax compatibility, data consistency, and performance impact, providing a basis for repair strategies; Perform hierarchical processing based on the risk score, and dynamically allocate repair priorities through the risk score to balance automation efficiency and manual precise control.

2. The cross-database SQL automated adaptation detection method according to claim 1, wherein: In step S2, static code parsing supports multiple programming languages and frameworks, specifically including: Java language: Use the MyBatis or Hibernate framework; Python language: Use the Django or Flask framework; Configuration file format: Support extracting SQL statements from configuration files in Extensible Markup Language (XML) format or JSON format; Build a multi-database Structured Query Language (SQL) parser based on the lexical and syntactic analyzer generation tool ANTLR 4.9+ 3. The cross-database SQL automated adaptation detection method according to claim 1, wherein: In step S2, during dynamic runtime monitoring, based on bytecode enhancement technology, dynamically intercept SQL execution during application runtime without modifying business code; Record the call stack, request parameters, execution time consumption, and user information of SQL statements, implement context information association, and support end-to-end tracing; The sampling rate is default set to 10%, and the threshold is 1 second. When a single Structured Query Language (SQL) execution exceeds 1 second, it is forced to record and mark as a slow SQL statement to ensure that key issues are not missed.

4. The cross-database SQL automated adaptation detection method according to claim 1, wherein: In step S3, manually sort out the difference rules in the syntax layer, function layer, and transaction layer between different databases, build a difference knowledge base, and configure the storage location and access permissions of the difference knowledge base. Preset no less than 2,000 predefined rules in the syntax layer, and define the syntax differences of different databases in JSON format; Customize and select no less than 300 common conversion functions in the function layer, including the logic for adjusting the parameter order; The intelligent conversion engine supports context-aware decision-making and multi-scheme evaluation. It generates 3 conversion schemes for the same SQL according to the rules in the difference knowledge base, and customizes and selects the optimal conversion strategy according to the SQL type, execution frequency, and data table structure; The SQL types include Data Manipulation Language (DML) and Data Definition Language (DDL).

5. The cross-database SQL automated adaptation detection method according to claim 4, wherein: In step S3, during the migration process, the difference knowledge base is dynamically updated using a weight evaluation algorithm according to the usage times, success rate, and conflict times of the conversion rules.

6. The cross-database SQL automated adaptation detection method according to claim 1, wherein: In step S4, the implementation process is as follows: Step S4.1, Two-way conversion verification Forward conversion: Convert the source database SQL to the target database SQL; Reverse backtranslation: Through the reverse rule base, convert the target database SQL back to the source database SQL; Structure comparison: Compare the Abstract Syntax Tree (AST) of the original SQL and the reverse backtranslated SQL, and the similarity is required to be no less than 90%; Step S4.2, Execution result comparison Execute the original SQL and the converted SQL respectively to verify whether the results are consistent; During verification, compare the key metrics, including index usage, join order, and scanned row count, and the similarity is required to exceed 85%; Step S4.3, Boundary condition testing Automatically generate test cases: Customize and generate boundary test data for the conversion rules, including null values, maximum values, and specially selected special characters; Exception handling verification: Verify whether the error handling logic of the converted SQL in the scenario of null value and constraint conflict is consistent with the original SQL.

7. The cross-database SQL automated adaptation detection method according to claim 1, wherein: In step S5, it is processed in three levels according to the risk score; If the risk score is less than 30, directly apply the optimal conversion rule for automatic repair without manual intervention; If the risk score is not less than 30 and less than 70, generate a marked SQL suggestion, prompt for manual confirmation and then execute to achieve semi-automatic repair; If the risk score is not less than 70, mark it as high risk, and it is reviewed and decided by humans, and an alternative implementation plan is provided.

8. A cross-database SQL automated adaptation detection system, characterized in that: For implementing the method described in any one of claims 1 to 7, including: The system configuration module is responsible for installing the target database and ensuring the normal operation of both the source database and the target database services; meanwhile, it configures the connection information of the source database and the target database, including the database address, port, username, and password. The Structured Query Language (SQL) collection module is responsible for capturing SQL statements during the runtime of the source code in multiple dimensions, including static code parsing and dynamic runtime monitoring. The difference knowledge base is responsible for pre-defining grammar layer rules, establishing function layer mappings, and establishing an isolation level mapping matrix at the transaction / lock mechanism layer. The intelligent conversion engine is responsible for generating three conversion schemes for the same SQL based on the rules in the difference knowledge base, and customarily selecting the optimal conversion strategy according to the SQL type, execution frequency, and data table structure. The semantic equivalence verification module is responsible for ensuring the accuracy and reliability of SQL cross-database conversion through two-way conversion verification, execution result comparison, and boundary testing. The risk assessment and repair module is responsible for establishing a risk scoring mechanism, quantifying conversion risks, and performing hierarchical processing based on the risk scores.

9. A computer-readable storage medium storing one or more programs, characterized in that: The one or more programs include instructions that, when executed by a computing device, cause the computing device to perform any of the methods described in claims 1 to 7.

10. A computing device, characterized in that: Comprising: One or more processors, one or more memories, and one or more programs, wherein the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for performing any of the methods described in claims 1 to 7.

Images