Database SQL compatibility processing method and device and medium

Abstract

The invention provides a database SQL compatibility processing method and device and a medium, and belongs to the technical field of database migration. The method comprises the following steps: capturing SQL execution context information reaching a corresponding execution interface through an interception layer dynamically mounted in a target operation environment; based on the SQL execution context information, generating a corresponding abstract syntax tree, and determining a to-be-rewritten AST execution node in the abstract syntax tree according to a preset two-dimensional rule base corresponding to the target database; wherein the preset two-dimensional rule base comprises the following dimensions: grammar difference and data types. Determining whether each AST execution node to be rewritten meets a preset automatic rewriting rule or not; if yes, the compatible SQL statement obtained through rewriting is sent to an interception layer, so that the interception layer sends the compatible SQL statement to a target database for execution, and a received corresponding execution result is sent to a user terminal.

Description

Technical Field

[0001] This application relates to the field of database migration technology, and in particular to a database SQL compatibility processing method, device and medium. Background Technology

[0002] As the process of domestic substitution for domestically developed information technologies accelerates, many information systems need to be migrated from databases such as Oracle, MySQL, and SQL Server to domestic databases (such as Highgo, DM, Kingbase, and Massive). However, different databases differ in SQL syntax, functions, data types, transaction isolation levels, and driver implementations. During application migration, directly executing the original SQL on the domestic database to be migrated can easily lead to syntax errors or performance degradation.

[0003] Therefore, enterprises usually need to manually audit and modify existing SQL scripts, but this method has a long migration cycle, high cost, and is prone to missing dynamic SQL. Summary of the Invention

[0004] This application provides a database SQL compatibility processing method, device, and medium to solve the technical problems of long migration cycles, high manual input costs, and easy omission of dynamic SQL in the current database application migration process, resulting in poor application migration results.

[0005] On the one hand, embodiments of this application provide a database SQL compatibility handling method, the method comprising: By dynamically attaching an interception layer to the target runtime environment, the SQL execution context information arriving at the corresponding execution interface is captured; Based on the SQL execution context information, a corresponding abstract syntax tree is generated, and the execution node of the abstract syntax tree to be rewritten is determined according to the preset two-dimensional rule base corresponding to the target database; wherein, the preset two-dimensional rule base includes the following dimensions: syntax differences and data types; Determine whether each of the AST execution nodes to be rewritten meets the preset automatic rewriting rules; If so, the rewritten compatible SQL statement is sent to the interception layer, so that the interception layer sends the compatible SQL statement to the target database for execution and sends the received corresponding execution result to the user terminal.

[0006] In one implementation of this application, the interception layer proxies the PreparedStatement and Statement interfaces in JDBC through a Java Agent to dynamically mount them to the JVM runtime environment; the interception layer proxies the DbCommand and DbProviderFactory interfaces in ADO.NET through a CLR Profiler to dynamically mount them to the .NET runtime environment.

[0007] In one implementation of this application, the SQL execution context information includes at least: SQL statement, parameter binding data, database connection metadata, and transaction metadata; wherein, the parameter binding data includes parameter values ​​and parameter data types, the database connection metadata includes the target database type, version, and driver information, and the transaction metadata includes the transaction isolation level and transaction number.

[0008] In one implementation of this application, after capturing the SQL execution context information arriving at the corresponding execution interface, the method further includes: The SQL statement and parameter data types in the SQL execution context information are hashed to generate a templated hash key; the templated hash key is obtained based on the SQL statement after parameterization. The templated hash key is matched with the preset cache. If the match is successful, the compatible SQL statement, parameter mapping table and AST mapping information are matched from the preset cache. Otherwise, based on the SQL execution context information, a corresponding abstract syntax tree is generated.

[0009] In one implementation of this application, the method further includes: If it is determined that each of the AST execution nodes to be rewritten does not meet the preset automatic rewriting rules, the interception layer executes the original SQL statement and attaches a rollback marker log to synchronously send the rollback marker log to the governance center so that the governance center can update the preset two-dimensional rule base.

[0010] In one implementation of this application, before sending the rewritten compatible SQL statement to the interception layer, the method further includes: Using a preset rewriting engine, rewriting operations are performed on the execution nodes of the AST to be rewritten, generating rewritten SQL statements; wherein, the rewriting operations include at least node replacement, insertion, and deletion; Based on the preset semantic mapping table, the original database and the target database, the semantic difference of the rewritten SQL statement is verified. If the verification passes, the rewritten SQL statement will be used as the compatible SQL statement. Otherwise, the rewritten SQL statement is rolled back to the original SQL statement, and manual review information is generated and sent to the corresponding user terminal.

[0011] In one implementation of this application, the method further includes: The first execution result of the compatible SQL statement in the target database is compared with the second execution result of the original SQL statement in the original database; Based on the comparison results, the corresponding performance index values ​​are determined, and the decision on whether to perform a secondary rewrite is based on the performance index values; wherein, the performance index values ​​characterize the degree of performance degradation of SQL compatibility processing.

[0012] In one implementation of this application, the compatible SQL statement is sent to the target database for execution, including a canary execution mode, which specifically includes: The interception layer routes some compatible SQL statements to the grayscale table of the target database for execution based on preset grayscale rules; the grayscale table has the same structure as the original database's formal table. The third execution result of the grayscale table is simultaneously determined, and the fourth execution result of the execution of the official table in the original database is determined, along with the original SQL statement corresponding to the partially compatible SQL statement. The result difference value between the third execution result and the fourth execution result is calculated. If the difference in the result is less than a preset threshold, the system will switch to the full execution model; otherwise, a difference analysis report will be generated and sent to the user terminal.

[0013] Secondly, embodiments of this application also provide a database SQL compatibility processing device, the device comprising: At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to perform a database SQL compatibility processing method as described above.

[0014] Thirdly, embodiments of this application also provide a non-volatile computer storage medium storing computer-executable instructions, which are capable of executing a database SQL compatibility processing method as described above.

[0015] Compared with the prior art, the significant advantages of this application are as follows: Through the above technical solution, this application dynamically mounts an interception layer at the execution layer, thereby achieving non-intrusive SQL interception. Dynamic mounting at the execution layer avoids missing dynamic SQL queries, and efficient automatic SQL rewriting is achieved through an abstract syntax tree and a pre-defined two-dimensional rule base, freeing up human resources. This solves the problems of long migration cycles, high manual costs, and easy omission of dynamic SQL queries in current database application migration processes, improving application migration efficiency. Attached Figure Description

[0016] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 This is a flowchart illustrating a database SQL compatibility handling method in an embodiment of this application; Figure 2 This is a flowchart illustrating the latency of cache rewriting in an embodiment of this application. Figure 3 This is a schematic diagram of the hot update timing of a rule base in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of a database SQL compatibility processing system according to an embodiment of this application; Figure 5 This is a schematic diagram of an SQL interception and rewriting process in an embodiment of this application; Figure 6 This is a schematic diagram of an AST rewriting timing in an embodiment of this application; Figure 7 This is a schematic diagram of the structure of a database SQL compatibility processing device in an embodiment of this application. Detailed Implementation

[0017] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0018] As the process of domestic substitution for domestically developed information technologies accelerates, many information systems need to be migrated from databases such as Oracle, MySQL, and SQL Server to domestic databases (such as Highgo, DM, Kingbase, and Massive). However, differences exist between different databases in terms of SQL syntax, functions, data types, transaction isolation levels, and driver implementation. Typical manifestations include: different function names and pseudo-columns (such as NVL, ISNULL, ROWNUM); differences in pagination syntax (such as LIMIT vs ROWNUM vs TOP); differences in date / numeric type precision (such as NUMBER → DECIMAL); and incompatibility between system variables and pseudo-columns (such as SYSDATE, SYSTIMESTAMP). During the application migration process, directly executing the original SQL on the domestic database to be migrated can easily lead to syntax errors or performance degradation.

[0019] Therefore, enterprises usually need to manually audit and modify existing SQL scripts, but this method has a long migration cycle, high cost, and is prone to missing dynamic SQL.

[0020] Currently, the mainstream technical solution in the industry is static code analysis tools. These solutions typically scan source code or SQL files, analyze the structure of SQL statements, and perform tests against compatibility rules.

[0021] The specific implementation steps are as follows: Specify the scan path: Developers configure the source code directory, Mapper file path (e.g., MyBatis' XML mapping file directory), or SQL script storage path in the tool; Static SQL statement parsing: The tool extracts SQL statements using regular expressions or syntax tree parsing techniques; Rule comparison and report generation: Detects whether there are functions, keywords, or syntax structures in the SQL that are not supported by the target database; Output static compatibility report: Generates a list of migration risk items for developers to manually fix. Representative products include migration assessment tools from some database vendors (such as Oracle→DM Migration Assistant, Kingbase Compatibility Detection Tool), as well as some IDE plug-in scanners.

[0022] The limitations of existing static testing solutions can be summarized in the following table: Table 1 Limitations of Static Testing Solutions

[0023] Currently, database migration requires extensive modifications to the application-layer code; compatibility testing for different database versions is time-consuming and costly; the driver layer cannot dynamically identify SQL dialects and execution characteristics; the system lacks a unified compatibility assessment and optimization mechanism; existing solutions using static code analysis tools cannot capture dynamically running SQL; and existing systems are only applicable to middleware layers such as MyBatis. It is difficult to achieve driver-layer interception, compatibility identification, and intelligent optimization of SQL statements from multiple database sources without modifying the application-layer code, thereby improving the efficiency and performance of replacing domestic databases.

[0024] Based on this, embodiments of this application provide a database SQL compatibility processing method, device, and medium to solve the technical problems of long migration cycles, high manual input costs, and easy omission of dynamic SQL in the current database application migration process, resulting in poor application migration results.

[0025] The various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0026] This application provides a database SQL compatibility handling method, such as... Figure 1 As shown, the method may include steps S101-S104: S101 captures the SQL execution context information arriving at the corresponding execution interface by dynamically attaching an interception layer to the target runtime environment.

[0027] It should be noted that the server, as the executing entity of the database SQL compatibility handling method, exists only as an example, and the executing entity is not limited to the server. This application does not make any specific limitation in this regard.

[0028] In this embodiment, the interception layer proxies the PreparedStatement and Statement interfaces in JDBC through a Java Agent to dynamically mount them to the JVM runtime environment. The interception layer also proxies the DbCommand and DbProviderFactory interfaces in ADO.NET through a CLR Profiler to dynamically mount them to the .NET runtime environment.

[0029] In other words, the interception layer of this application is dynamically mounted to the Java Virtual Machine (JVM) or .NET runtime environment. The Java environment can be implemented using a Java Agent (bytecode enhancement or proxy factory implementation); the .NET environment can be implemented using a CLR Profiler / dynamic proxy (such as DispatchProxy). Therefore, for both Java and .NET target runtime environments, the interception layer can proxy the corresponding execution interface to capture SQL execution context information. The corresponding execution interface can be understood as the execution interface in the target runtime environment, such as the PreparedStatement and Statement interfaces in JDBC, and the DbCommand and DbProviderFactory interfaces in ADO.NET.

[0030] Taking Java Agent as an example, the pseudocode for the interception layer is as follows: public ResultSet execute(String sql, Object[] params, ConnectionInfoci) { InterceptRecord rec = InterceptRecord.of(sql, params, ci,System.currentTimeMillis()); / / Asynchronous sending to the parsing queue ParserQueue.offer(rec); / / Waiting for rewrite results or timeout rollback RewriteResult result = RewriteCoordinator.getRewrite(rec.id, TIMEOUT_MS); if (result.isRewritten()) { return realConnection.execute(result.getSql(), result.getParams()); } else { / / Revert to the original SQL, note / COMPAT_FALLBACK / logFallback(rec); return realConnection.execute(sql, params); }} The SQL execution context information mentioned above includes at least the following: SQL statement, parameter binding data, database connection metadata, and transaction metadata. Parameter binding data includes parameter values ​​and data types; database connection metadata includes the target database type, version, and driver information; and transaction metadata includes the transaction isolation level and transaction number. In the code above, `sql` represents the original SQL statement to be executed by the application (e.g., `SELECT * FROM user WHERE id = ?`); `params` represents the parameter binding data corresponding to placeholders in the SQL (e.g., `

[1001] `, i.e., the specific value of `?`); `ci` (ConnectionInfo) represents database connection metadata (e.g., target database type, version, connection string, etc.); `System.currentTimeMillis()` records the interception timestamp for subsequent timeout judgment or log tracing. `rec.id`: A unique identifier for the intercepted record, used to associate the corresponding rewrite result in the asynchronous parsing queue; `TIMEOUT_MS`: A timeout threshold (e.g., 50ms). If no rewrite result is received within this time, it is considered a "rewrite failure"; `RewriteCoordinator`: Responsible for synchronously waiting for the rewrite result (e.g., the rewritten SQL, adapted parameters) returned from the asynchronous parsing queue. `result.isRewritten()`: Determines whether the rewrite was successful (e.g., whether SQL adapted to the target database was generated); `result.getSql()`: The rewritten compatible SQL (e.g., rewriting Oracle's `||` concatenation into MySQL's `CONCAT` function); `result.getParams()`: Parameters adapted to the target database (e.g., parameter type conversion, converting Date to Timestamp); `realConnection.execute(...)`: Executes the rewritten SQL through a real database connection and returns a result set (ResultSet). `logFallback(rec)`: Records rollback logs (including the original SQL, failure reason, timestamp, etc.) for subsequent optimization of the rule base; `realConnection.execute(sql,params)`: Directly executes the original SQL and parameters passed in by the application (equivalent to "bypassing rewriting") to avoid business interruption due to rewriting failure; "Comment / COMPAT_FALLBACK / " in the comments: In actual implementation, special markers (such as comments) may be added to the original SQL to facilitate the identification of "SQL executed during rollback" during subsequent log analysis.

[0031] In addition, the driver layer interception needs to ensure low latency (it is recommended that the main path blocking limit be 50ms, which can be configured), and the parsing and rewriting adopt an asynchronous first-look strategy to reduce blocking.

[0032] In one embodiment of this application, after capturing the SQL execution context information arriving at the corresponding execution interface, the method further includes: The SQL statement and parameter data types in the SQL execution context information are hashed to generate a templated hash key. The templated hash key is obtained based on the SQL statement after parameterization. The templated hash key is matched against a preset cache. If a match is found, a compatible SQL statement, parameter mapping table, and AST mapping information are retrieved from the preset cache. Otherwise, the corresponding abstract syntax tree is generated based on the SQL execution context information.

[0033] In other words, this application includes a cache rewrite function, and the latency diagram for cache rewrite is shown below. Figure 2 As shown, after the application initiates an SQL request, the interception layer captures it and submits the corresponding parsing task to the parsing and rewriting thread pool. Subsequently, the parsing and rewriting cache (Rewrite Cache) performs hash calculations on each SQL statement and parameter data type in the SQL execution context information to generate a templated hash key. For example, SQL1: SELECT first_name || ' ' || last_name FROM users WHERE age>18; SQL2: SELECT first_name || ' ' || last_name FROM users WHERE age>21; After parameter removal, the templates for both SQL statements are: SELECT first_name || ' ' || last_name FROM users WHERE age>?, and a templated hash key is generated using this template. The pre-set cache contains several cache entries, each containing rewritten SQL, parameter mapping tables, and Abstract Syntax Tree (AST) mapping information. After obtaining the templated hash key, the interceptor first matches it against the pre-set cache. If a match is found, the rewritten SQL is treated as a compatible SQL statement, and the parameter mapping table-level AST mapping information is obtained. If no match is found, execution continues to step S102. Figure 2 In this context, the database is the target database, the agent is the interception layer, and TIMEOUT_MS is the preset time limit. If the preset cache is not hit or the rewritten SQL is not obtained after the time limit is exceeded, the original SQL will be rolled back and executed.

[0034] Through the above technical solution, this application achieves real-time and fast compatibility rewriting of dynamic SQL at the driver layer by rewriting with cached, taking into account low latency, high hit rate and semantic security.

[0035] S102, based on the SQL execution context information, generates the corresponding abstract syntax tree, and determines the execution node of the AST to be rewritten in the abstract syntax tree according to the preset two-dimensional rule base corresponding to the target database.

[0036] The preset two-dimensional rule base includes the following dimensions: syntax differences and data types.

[0037] After obtaining the SQL execution context information, this application will parse the SQL execution context information through ANTLR4 multi-syntax entry or other parsers to output an AST structure, such as: AST{ type, children[], tokenPos, originalText}. Simultaneously, during AST generation, a NodeIndex (a mapping from node to text offset) can be constructed to locate and generate rewrite patches.

[0038] Subsequently, the server can also call the dialect recognition module to perform dialect recognition based on the SQL execution context information. Example of dialect recognition feature rules: If the SQL contains ROWNUM or CONNECTBY → high probability Oracle; contains TOP → SQL Server; contains LIMIT but no ROWNUM → MySQL / Postgres. Output format: DialectCandidate{name,confidenceScore}. Dialect recognition can be understood as identifying the original database that the original SQL statement is adapted to through the SQL execution context information, in order to obtain the original database and then perform rewrite rule matching.

[0039] This application pre-configures a two-dimensional rule base, which includes a syntax difference rule sub-base and a data type rule sub-base. The abstract syntax tree in this application comprises a statement layer (the entire SELECT query), a field selection layer (first_name || ' ' || last_name AS full_name), a function call layer (string concatenation operation || and its parameters), an operator layer (> comparison operator), a condition layer (age>18), and a pagination layer (empty in this example). Based on the abstract syntax tree, the syntax difference rule base matches nodes to identify SQL structure differences (such as functions, pseudo-columns, pagination syntax, etc.). The data type rule base matches field type differences and precision / scale risks, generating rewrite suggestions or warnings, and checking whether field types are compatible with the target database. For example, if first_name and last_name are strings and age is an integer, both meet the requirements.

[0040] This enables real-time dynamic compatibility analysis and automatic rewriting in both syntax and type dimensions.

[0041] The Syntax Rule DB described above is stored in JSON / YAML format and includes the following fields: rule_id (rule identifier), pattern (syntax tree node pattern, supporting wildcards and regular expressions), action (rewrite / warn / block), template (template rewrite or AST transformation function reference), weight (compatibility evaluation weight), auto_rewrite (boolean), since_version (adapt to the target database version), examples (example usage), and supports version control, audit flow, and rule tags (such as "pagination", "function", and "pseudo-column").

[0042] Type Mapping DB (Type Data Type Rule Base), example fields for table entries: src_type, src_precision, src_scale; dst_type, dst_precision, dst_scale; risk_weight (precision / semantic loss weight); precision_loss_flag (whether truncation is possible); suggested_fix (e.g., rewritten as DECIMAL(?,?) or split into high-precision storage + rounding).

[0043] Furthermore, for a single SQL execution context, if multiple rules apply, this application adopts the following priority strategy: rules that explicitly specify since_version and match the current target version take precedence; rules with higher weights take precedence; if a conflict still cannot be resolved, it is marked as "manual review".

[0044] By using a pre-defined two-dimensional rule base, nodes in the abstract syntax tree that can be automatically rewritten are matched and used as the execution result of the AST to be rewritten.

[0045] S103, determine whether each AST execution node to be rewritten meets the preset automatic rewriting rules.

[0046] In other words, this application will be subject to rewriting determination, and the determination strategy is as follows: If all rules have `auto_rewrite=true` and the rewriting action does not introduce semantic ambiguity, then automatic rewriting will occur. If `auto_rewrite=false` exists or rule combinations may change the result set, then it will be marked as "requires manual review" or rolled back. Rewriting priority is sorted by rule weight and update time; multi-pass rewriting and idempotency detection are supported.

[0047] The default automatic rewriting rule is that all execution nodes of the AST to be rewritten can be automatically rewritten. In this case, a rewriting plan will be generated and the AST will be applied for rewriting. If there are any execution nodes of the AST to be rewritten that cannot be automatically rewritten, a rollback will be marked ( / COMPAT_FALLBACK / ) so that the original SQL can be re-executed and the gray-scale / manual review log can be reported.

[0048] That is, if it is determined that each AST execution node to be rewritten does not meet the preset automatic rewriting rules, the interception layer executes the original SQL statement and attaches a rollback marker log, so as to send the rollback marker log synchronously to the governance center, so that the governance center can update the preset two-dimensional rule base.

[0049] The Governance Center's functions include: centralized storage of the rule base (JSON / YAML), receiving asynchronously reported rewrite logs and evaluation reports, providing a hot rule release interface, a canary release control panel, and an audit interface. Its interfaces include: rule push (Webhook / configuration center retrieval), log query, and version rollback. The Governance Center also includes a self-learning feedback module (optional) to analyze the rewrite success rate, rollback rate, and manual review results of the Governance Center, and to recommend rule optimizations / additions or weight adjustments (semi-automatic).

[0050] The governance center generates a sequence diagram of rule base hot updates, such as... Figure 3 As shown. Administrators can submit / review rules (version N) to the governance center. The governance center pushes an update notification (version N) to the interception layer. The interception layer loads the new rule into the rule engine, and the rule engine returns the loading result. Subsequently, the interception layer sends the loading result and version number back to the governance center, and also sends a notification to the administrator regarding the release result. The governance center allows for hot updates to the preset two-dimensional rule base to ensure proper compatibility handling.

[0051] In other words, rule hot reload is stored in the governance center (configuration center) as JSON / YAML, and the interception layer periodically pulls or receives updates via push (webhook). Update process: The administrator submits a new rule (or rule version) in the governance center; the governance center publishes an update notification; all agents pull and load the new rule into memory (principle: atomic replacement, support for rollback); the agent returns the loading result and the new version number. Hot reload requirements: The update operation should not restart the service and there should be no request loss.

[0052] S104. If it is determined that all execution nodes of the AST to be rewritten meet the preset automatic rewriting rules, the rewritten compatible SQL statement is sent to the interception layer so that the interception layer sends the compatible SQL statement to the target database for execution and sends the received corresponding execution result to the user terminal.

[0053] In this embodiment of the application, before sending the rewritten compatible SQL statement to the interception layer, the method further includes: Using a pre-defined rewriting engine, rewriting operations are performed on the nodes of the AST to be rewritten, generating rewritten SQL statements. These rewriting operations include at least node replacement, insertion, and deletion. Based on a pre-defined semantic mapping table, the original database, and the target database, semantic differences are validated on the rewritten SQL statements. If the validation passes, the rewritten SQL statements are treated as compatible SQL statements. Otherwise, the rewritten SQL statements are reverted to the original SQL statements, and manual review information is generated and sent to the corresponding user terminals.

[0054] Specifically, based on rules, node replacement / insertion / deletion operations are performed on the AST to generate SQL compatible with the target database; at the same time, a rewrite report is generated (comparison before / after rewrite, whether semantics are preserved, etc.).

[0055] Rewriting the SQL using the AST can be achieved through the following steps: apply a transformation (visitorpattern) to the AST to generate a new AST, and then generate the SQL (pretty-print, retaining necessary comments). To ensure idempotency and rollback, a mapping package `RewritePatch{ originalNodePath, newNode, rule_id}` is generated before rewriting the SQL and submitted to the governance center for auditing.

[0056] Example rewrite (Oracle ROWNUM → Subquery + ROW_NUMBER): Original AST (simplified representation): SELECT ... WHERE ROWNUM<= 10; Rewritten AST: SELECT * FROM (SELECT ..., ROW_NUMBER() OVER()RN FROM ... )WHERE RN<= 10.

[0057] The AST transformation mechanism employs a Visitor pattern to traverse the AST: First traversal: Identifies nodes to be rewritten (matchrules) and generates a RewritePlan (a sequence of rewrite actions ordered by priority); Second traversal: Applyes transformations (replacement, insertion, deletion, subtree wrapping) according to the RewritePlan; Third traversal: Performs syntax validation (ensuring the new AST conforms to the target database syntax); Supports a "patch generation" mechanism: Records only the minimum set of rewritten nodes for easy difference auditing and rollback. Furthermore, complex statements (such as those containing subqueries, CTEs, and common table expressions) are rewritten in stages, generating intermediate SQL and rollback points at each step; if any step fails, a rollback strategy is triggered.

[0058] Simultaneously, lightweight semantic checks can be performed after rewriting, such as column count, column name consistency, and GROUP BY clause checks in aggregation statements. For rewrites that cannot guarantee semantic equivalence, manual review is required. This application specifically performs semantic checks through a pre-defined semantic mapping table.

[0059] For example, consider an Oracle database where the || concatenation operator returns NULL when encountering NULL, while the target database is MySQL, requiring the use of CONCAT, which ignores NULL and returns a partial concatenation result. Even if the syntax and types are valid, the logic may differ. This application identifies potential differences through a semantic layer, thereby performing semantic difference verification. Simultaneously, it can prompt or automatically apply compatible functions and null value handling rules to ensure that the cross-database execution result is consistent with the original semantics, achieving dynamic and seamless SQL compatibility.

[0060] In some embodiments, to quantitatively assess database compatibility, the following embodiments are also provided, specifically including: The first execution result of the compatible SQL statement in the target database is compared with the second execution result of the original SQL statement in the original database. Based on the comparison result, the corresponding performance index value is determined, and the decision to perform a second rewrite is based on the performance index value. The performance index value characterizes the degree of performance degradation in SQL compatibility processing.

[0061] The first and second execution results can be understood as quantitative metrics of the SQL statement after execution in the target database, such as execution time and resource utilization. By comparing these metrics, performance metrics can be obtained through difference calculations or Euclidean distance calculations, using the metric values ​​corresponding to the first and second execution results. These performance metrics are then compared to a preset performance threshold. If the performance metric value is less than the preset threshold, it indicates a high degree of performance degradation, requiring a second rewrite or manual review for further assessment. The preset performance threshold can be set based on actual usage scenarios and is not specifically limited here.

[0062] Furthermore, this application can be configured with a quantitative evaluation engine to generate a database compatibility evaluation report. Specifically, based on the weighting coefficients assigned by the rules, the compatibility score, migration workload (estimated in person-days), and performance loss estimate (based on type downgrade weights and estimated row count) are calculated. An evaluation report is generated and tagged (e.g., "Requires manual review"). Formulas (example, rules are configurable): Compatibility score = 100 - Σ(syntax difference weight × number of times) - Σ(type risk weight × number of fields); Migration workload = ceil(Σ(syntax difference weight × number of times) / 50) (unit: person-days); Performance loss estimate = Σ(type downgrade weight × number of fields × estimated row count × CPU coefficient).

[0063] In some embodiments, compatible SQL statements are sent to the target database for execution, including a canary execution mode, which specifically includes: The interception layer, based on preset grayscale rules, routes partially compatible SQL statements to a grayscale table in the target database for execution. The grayscale table has the same structure as the original database's official table. Simultaneously, it determines the third execution result of the grayscale table and the fourth execution result of the original SQL statement corresponding to the partially compatible SQL statement in the original database's official table, and calculates the result difference between the third and fourth execution results. If the result difference is less than a preset threshold, it switches to the full execution model; otherwise, it generates a difference analysis report and sends it to the user terminal.

[0064] In other words, this application can be configured with a gray-scale execution mode. Gray-scale execution only obtains the third and corresponding fourth execution results within the gray-scale range, thereby determining the difference in execution results between the target database and the original database for SQL statements with the same semantics. Based on the comparison of the result difference value and a preset threshold, it switches to the full execution model, implementing the above step S104. Otherwise, a difference analysis report is generated based on the result difference value and sent to the user terminal, allowing the user to intervene and perform rule adjustments or rewrites. The preset threshold can be set based on actual usage scenarios and expert experience, and is not specifically limited here.

[0065] Figure 4 This is a schematic diagram of the system structure corresponding to the implementation of the above-described database SQL compatibility handling method in an embodiment of this application. For example... Figure 4 As shown, specifically, a lightweight interception proxy (hereinafter referred to as the "interception layer") is introduced into the JDBC (Java Database Connectivity) / .NET driver layer to transparently capture every SQL statement issued by the application; the SQL is parsed into a unified AST (Abstract Syntax Tree), and rule matching is performed using a "dual-dimensional rule base" (syntax difference rule base and data type mapping rule base); for rewritable statements, AST node replacement is used to generate compatible SQL and it is then executed; for non-rewritable statements, a gray-scale / rollback strategy is used, and a quantitative evaluation report is generated (compatibility score, migration workload, performance loss estimate, etc.). The system supports hot updates of the rule base, gray-scale control, audit reporting, and self-learning feedback.

[0066] Figure 5 This is a schematic diagram of a SQL interception and rewriting process in an embodiment of this application, such as... Figure 5As shown, the process can be described as including the following steps: S1. Application initiates SQL execution → S2. Interception layer captures SQL and parameters → S3. Parsing engine generates AST → S4. Dialect recognition → S5. Rule matching (syntax rules / type rules) → S6. Determine: Can it be automatically rewritten? → If yes: S7a Rewriting engine generates compatible SQL → S8a Quantitative evaluation and execution → S9a Report to governance center; If no: S7b Mark rollback ( / COMPAT_FALLBACK / ), record gray log → S8b Directly send the original SQL and report to governance center (manual review) → S9b (Execution result feedback, may trigger self-learning). Furthermore, after generating the rewriting plan, the application rewrites using the AST, followed by semantic verification. If the verification passes, the rewritten SQL is generated and evaluated, the rewritten SQL is executed, and a rewriting report is asynchronously reported; if the verification fails, it is marked for manual review and the original SQL is rolled back, and a rollback log is reported.

[0067] Figure 6 This is an AST rewriting sequence diagram in an embodiment of this application, which includes the process of each role, including application, interception layer, parsing engine, rule engine, rewriting engine, quantitative evaluation, governance center, and domestic database, participating in the execution of the above-mentioned database SQL compatibility processing method.

[0068] Through the above technical solution, this application dynamically mounts an interception layer at the execution layer, thereby achieving non-intrusive SQL interception. Dynamic mounting at the execution layer avoids missing dynamic SQL queries, and efficient automatic SQL rewriting is achieved through an abstract syntax tree and a pre-defined two-dimensional rule base, freeing up human resources. This solves the problems of long migration cycles, high manual costs, and easy omission of dynamic SQL queries in current database application migration processes, improving application migration efficiency.

[0069] Figure 7 A schematic diagram of the structure of a database SQL compatibility processing device provided in this application embodiment is shown below. Figure 7 As shown, the device includes: At least one processor; and a memory communicatively connected to the at least one processor. The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: By dynamically attaching an interception layer to the target runtime environment, the SQL execution context information arriving at the corresponding execution interface is captured. Based on the SQL execution context information, a corresponding abstract syntax tree (AST) is generated, and the execution nodes of the AST to be rewritten are determined according to a preset two-dimensional rule base corresponding to the target database. The preset two-dimensional rule base includes the following dimensions: syntax differences and data types. It is then determined whether each execution node of the AST to be rewritten satisfies the preset automatic rewriting rules. If so, the rewritten compatible SQL statement is sent to the interception layer, which then sends the compatible SQL statement to the target database for execution and sends the received execution result to the user terminal.

[0070] This application embodiment also provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows: By dynamically attaching an interception layer to the target runtime environment, the SQL execution context information arriving at the corresponding execution interface is captured. Based on the SQL execution context information, a corresponding abstract syntax tree (AST) is generated, and the execution nodes of the AST to be rewritten are determined according to a preset two-dimensional rule base corresponding to the target database. The preset two-dimensional rule base includes the following dimensions: syntax differences and data types. It is then determined whether each execution node of the AST to be rewritten satisfies the preset automatic rewriting rules. If so, the rewritten compatible SQL statement is sent to the interception layer, which then sends the compatible SQL statement to the target database for execution and sends the received execution result to the user terminal.

[0071] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0072] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0073] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0074] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A database SQL compatibility handling method, characterized in that, The method includes: By dynamically attaching an interception layer to the target runtime environment, the SQL execution context information arriving at the corresponding execution interface is captured; Based on the SQL execution context information, a corresponding abstract syntax tree is generated, and the execution node of the abstract syntax tree to be rewritten is determined according to the preset two-dimensional rule base corresponding to the target database; wherein, the preset two-dimensional rule base includes the following dimensions: syntax differences and data types; Determine whether each of the AST execution nodes to be rewritten meets the preset automatic rewriting rules; If so, the rewritten compatible SQL statement is sent to the interception layer, so that the interception layer sends the compatible SQL statement to the target database for execution and sends the received corresponding execution result to the user terminal.

2. The database SQL compatibility handling method according to claim 1, characterized in that, The interception layer proxies the PreparedStatement and Statement interfaces in JDBC through a Java Agent to dynamically mount them to the JVM runtime environment; the interception layer proxies the DbCommand and DbProviderFactory interfaces in ADO.NET through a CLR Profiler to dynamically mount them to the .NET runtime environment.

3. The database SQL compatibility handling method according to claim 1, characterized in that, The SQL execution context information includes at least: SQL statement, parameter binding data, database connection metadata, and transaction metadata; wherein, the parameter binding data includes parameter values ​​and parameter data types, the database connection metadata includes the target database type, version, and driver information, and the transaction metadata includes the transaction isolation level and transaction number.

4. The database SQL compatibility handling method according to claim 3, characterized in that, After capturing the SQL execution context information arriving at the corresponding execution interface, the method further includes: The SQL statement and parameter data types in the SQL execution context information are hashed to generate a templated hash key; the templated hash key is obtained based on the SQL statement after parameterization. The templated hash key is matched with the preset cache. If the match is successful, the compatible SQL statement, parameter mapping table and AST mapping information are matched from the preset cache. Otherwise, based on the SQL execution context information, a corresponding abstract syntax tree is generated.

5. A database SQL compatibility handling method according to claim 1, characterized in that, The method further includes: If it is determined that each of the AST execution nodes to be rewritten does not meet the preset automatic rewriting rules, the interception layer executes the original SQL statement and attaches a rollback marker log to synchronously send the rollback marker log to the governance center so that the governance center can update the preset two-dimensional rule base.

6. A database SQL compatibility handling method according to claim 1, characterized in that, Before sending the rewritten compatible SQL statement to the interception layer, the method further includes: Using a preset rewriting engine, rewriting operations are performed on the execution nodes of the AST to be rewritten, generating rewritten SQL statements; wherein, the rewriting operations include at least node replacement, insertion, and deletion; Based on the preset semantic mapping table, the original database and the target database, the semantic difference of the rewritten SQL statement is verified. If the verification passes, the rewritten SQL statement will be used as the compatible SQL statement. Otherwise, the rewritten SQL statement is rolled back to the original SQL statement, and manual review information is generated and sent to the corresponding user terminal.

7. A database SQL compatibility handling method according to claim 1, characterized in that, The method further includes: The first execution result of the compatible SQL statement in the target database is compared with the second execution result of the original SQL statement in the original database; Based on the comparison results, the corresponding performance index values ​​are determined, and the decision on whether to perform a secondary rewrite is based on the performance index values; wherein, the performance index values ​​characterize the degree of performance degradation of SQL compatibility processing.

8. A database SQL compatibility handling method according to claim 1, characterized in that, Sending the compatible SQL statement to the target database for execution, including a canary execution mode, specifically including: The interception layer routes some compatible SQL statements to the grayscale table of the target database for execution based on preset grayscale rules; the grayscale table has the same structure as the original database's formal table. The third execution result of the grayscale table is simultaneously determined, and the fourth execution result of the execution of the official table in the original database is determined, along with the original SQL statement corresponding to the partially compatible SQL statement. The result difference value between the third execution result and the fourth execution result is calculated. If the difference in the result is less than a preset threshold, the system will switch to the full execution model; otherwise, a difference analysis report will be generated and sent to the user terminal.

9. A database SQL compatibility processing device, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a database SQL compatibility processing method as described in any one of claims 1-8.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing a database SQL compatibility processing method as described in any one of claims 1-8.

Images