Non-standardized sql processing method, apparatus, device, medium and program product

Abstract

This application provides a method, apparatus, device, medium, and program product for processing non-standardized SQL, relating to the fields of big data and fintech. The method includes: parsing SQL statements in a code file to be processed, extracting structured information of SQL statements from database development specifications; matching the structured information of the database development specifications with a preset rule base to identify non-standardized SQL statements in the code file to be processed; the preset rule base for database development specifications is generated based on database development specifications, historical migration cases, and performance problem cases; and determining adaptation suggestions corresponding to the non-standardized SQL statements from the database development specifications based on the matching results. By constructing an automated SQL scanning and checking device based on a rule base, combined with syntax tree parsing and semantic analysis technologies, accurate identification of non-standardized SQL, semantic-level adaptation optimization, and cross-database performance prediction are achieved.

Description

Technical Field

[0001] This application relates to the fields of big data and fintech, and in particular to a non-standardized SQL processing method, apparatus, device, medium, and program product. Background Technology

[0002] In the financial industry, upgrading and transforming database systems is a crucial step in ensuring business continuity and technological evolution. As financial institutions gradually migrate from traditional relational databases to new distributed databases, developers often rely on the specific features of the original database, such as proprietary syntax, data types, or procedural language extensions, when writing SQL statements. While these non-standardized SQL statements can optimize performance in specific scenarios, they exhibit significant differences in compatibility with the target database. For example, the CONNECT BY hierarchical query syntax of traditional relational databases may need to be completely refactored into WITH RECURSIVE recursive queries in the target database. If developers lack a deep understanding of the characteristics of new distributed databases, it can easily lead to SQL semantic deviations or performance issues. Furthermore, financial institutions' business systems typically contain a large number of complex queries, and these scenarios can result in response time fluctuations of ±300% across different database execution plans. Due to the varying coding abilities of developers and the significant manpower required to manually check the compatibility of SQL statements, the transformation costs remain high. Summary of the Invention

[0003] This application provides a method, apparatus, device, medium, and program product for processing non-standard SQL, which can be used to achieve automatic scanning of non-standard SQL.

[0004] In a first aspect, embodiments of this application provide a non-standardized SQL processing method, including:

[0005] Parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements in the database development specification;

[0006] The database development specification structured information is matched with a preset rule base to identify non-standard SQL statements in the code files to be processed according to the database development specification. The preset rule base for the database development specification is generated based on the database development specification, historical migration cases, and performance problem cases.

[0007] Based on the matching results, determine the adaptation recommendations for non-standard SQL statements in the database development specifications.

[0008] In one possible implementation, the database development specification parses the SQL statements in the code file to be processed, including:

[0009] Determine the file type of the database development specification code file to be processed based on its file extension.

[0010] Based on the database development specification file type, extract the SQL statements from the database development specification code file to be processed.

[0011] In one possible implementation, the database development specification extracts SQL statements from the database development specification code file to be processed, based on the database development specification file type, including:

[0012] For the first target file of the database development specification file, which is a stored procedure code, extract the SQL statements in the first target file of the database development specification file based on the SQL keywords and the SQL end flag.

[0013] For the second target file of the database development specification file, which is a program code file, extract the SQL statements in the second target file of the database development specification file based on the SQL keywords.

[0014] In one possible implementation, the database development specification determines, based on the matching results, the adaptation suggestions corresponding to the non-standardized SQL statements in the database development specification, including at least one of the following: syntax replacement, index optimization suggestions, and data preprocessing strategies.

[0015] In one possible implementation, after the database development specification determines the adaptation recommendations for non-standardized SQL statements based on the matching results, the following steps are also included:

[0016] Based on the query optimizer rules of the target database, a virtual execution plan is generated;

[0017] Based on the virtual execution plan of the database development specification, calculate the resource consumption of SQL statements in the code files to be processed according to the database development specification.

[0018] In one possible implementation, after the database development specification matches the structured information of the database development specification with a preset rule base, the method further includes:

[0019] Based on feedback data from historical migration cases, the rule weights in the rule base are dynamically updated.

[0020] In one possible implementation, before the database development specification parses the SQL statements in the code file to be processed, it also includes:

[0021] After a code commit is detected, a code file to be processed is generated based on the committed code;

[0022] Push adaptation suggestions through code editing tools;

[0023] After the adaptation suggestions are generated, the performance prediction and rule base update process is automatically triggered.

[0024] Secondly, embodiments of this application provide a non-standardized SQL processing apparatus, comprising:

[0025] The parsing and extraction module is used to parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements in the database development specification.

[0026] The matching module is used to match the structured information of the database development specification with the preset rule base to identify non-standard SQL statements in the code file to be processed by the database development specification; the preset rule base of the database development specification is generated based on the database development specification, historical migration cases and performance problem cases;

[0027] A recommendation module is provided to determine the compatibility recommendations for non-standard SQL statements in database development specifications based on the matching results.

[0028] Thirdly, embodiments of this application provide a computer device, including: a memory, a processor; the memory stores computer execution instructions; the processor executes the computer execution instructions stored in the memory, causing the processor to perform the first aspect and / or various possible implementations of the first aspect.

[0029] Fourthly, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the first aspect and / or various possible implementations of the first aspect.

[0030] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the first aspect and / or various possible implementations of the first aspect.

[0031] The non-standard SQL processing method, apparatus, device, medium, and program product provided in this application include: parsing SQL statements in a code file to be processed and extracting structured information of SQL statements from database development specifications; matching the structured information of the database development specifications with a preset rule base to identify non-standard SQL statements in the code file to be processed; the preset rule base for database development specifications is generated based on database development specifications, historical migration cases, and performance problem cases; and determining adaptation suggestions corresponding to non-standard SQL statements from database development specifications based on the matching results. By constructing an automated SQL scanning and checking device based on a rule base, combined with syntax tree parsing and semantic analysis technologies, accurate identification of non-standard SQL, semantic-level adaptation optimization, and cross-database performance prediction are achieved. Attached Figure Description

[0032] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0033] Figure 1 Flowchart of the non-standard SQL processing method provided in this application Figure 1 ;

[0034] Figure 2 Flowchart of the non-standard SQL processing method provided in this application Figure 2 ;

[0035] Figure 3 Flowchart of the non-standard SQL processing method provided in this application Figure 3 ;

[0036] Figure 4 A schematic diagram of the non-standard SQL processing device provided in this application;

[0037] Figure 5 A schematic diagram of the structure of the computer device provided in this application.

[0038] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0039] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.

[0040] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of the relevant data all comply with the relevant laws, regulations, and standards of the relevant countries and regions, have taken necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation access points for users to choose to authorize or refuse.

[0041] Furthermore, the technical solution involved in this application, which involves big data analysis of user information (including but not limited to personal biometrics, identity data, consumption data, asset data, electronic terminal operation data, etc.) and the use of artificial intelligence technology for automated decision-making, and makes decisions that have a significant impact on personal rights based on the results of automated decision-making, provides users with corresponding operation entry points for users to choose to agree to or reject the results of automated decision-making; if the user chooses to reject, the process will proceed to the expert decision-making process.

[0042] It should be noted that the non-standard SQL processing methods, devices, equipment, media and program products provided in this application can be used in the field of big data, that is, in the field of fintech, and can also be used in any field other than big data and fintech. This application does not limit the application field of the non-standard SQL processing methods, devices, equipment, media and program products.

[0043] In the financial industry, upgrading and transforming database systems is a crucial step in ensuring business continuity and technological evolution. As financial institutions gradually migrate from traditional relational databases to new distributed databases, developers often rely on the original database's specialized features, such as proprietary syntax, data types, or procedural language extensions, when writing SQL statements.

[0044] While these non-standardized SQL statements can optimize performance in specific scenarios, they exhibit significant compatibility differences with the target database. For example, the CONNECT BY hierarchical query syntax of traditional relational databases may require complete refactoring to WITH RECURSIVE recursive queries in the target database. Developers lacking a deep understanding of the characteristics of modern distributed databases are prone to SQL semantic deviations or performance issues. Furthermore, financial institutions' business systems typically contain numerous complex queries, such as multi-table JOINs, nested subqueries, and aggregation calculations. These scenarios can result in response time fluctuations of ±300% across different database execution plans. Due to varying coding abilities among developers and the significant manpower required to manually check the compatibility of SQL statements, the transformation costs remain high.

[0045] To address the aforementioned technical issues, this application provides a method for processing non-standardized SQL. By constructing an automated SQL scanning and checking device based on a rule base, combined with syntax tree parsing and semantic analysis technologies, it achieves accurate identification, semantic-level adaptation optimization, and cross-database performance prediction of non-standardized SQL. The core of this concept lies in: dynamic management of the rule base, dynamically generating and maintaining a set of scanning rules based on database development specifications, historical migration experience, and performance issue cases, covering the syntax, performance bottleneck scenarios, and semantic deviation risk points of non-standardized SQL. Semantic-level parsing and adaptation: Syntax tree parsing technology is used to extract the structured information of SQL statements, and a semantic analysis model is combined to determine their compatibility with the target database, generating adaptation suggestions. Cross-database performance prediction: By simulating the execution plan of the target database, the performance of the SQL after migration is predicted, assisting developers in optimizing query logic. This concept overcomes the limitations of traditional manual verification, integrating rule formulation, semantic analysis, and performance prediction into an automated process, significantly reducing manual intervention costs and improving migration efficiency.

[0046] The non-standard SQL processing method provided in this application is applicable to scenarios involving database upgrades and transformations in financial institutions, particularly the migration from traditional relational databases to new distributed databases. Typical application scenarios include:

[0047] Code commit pipeline integration: During the code commit phase, developers can trigger SQL scan checks through the CI / CD pipeline to automatically intercept non-standard SQL and provide remediation suggestions.

[0048] Batch SQL file adaptation: For legacy SQL file libraries, batch parse and generate adaptation code, reducing the workload of manual line-by-line verification.

[0049] Cross-database performance verification: Simulate the execution environment of the target database before migration, predict SQL performance, and avoid system stability issues caused by differences in execution plans after migration.

[0050] like Figure 1 As shown, a non-standardized SQL processing method is provided, which includes:

[0051] Step 101: Parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements in the database development specification.

[0052] Step 102: Match the structured information of the database development specification with the preset rule base to identify non-standard SQL statements in the code file to be processed in the database development specification; the preset rule base of the database development specification is generated based on the database development specification, historical migration cases and performance problem cases;

[0053] Step 103: Based on the matching results, determine the adaptation suggestions for non-standard SQL statements in the database development specifications.

[0054] The code files to be processed refer to program files submitted by developers, such as SQL files, Java class files, and stored procedure code, containing SQL statements that need to be adapted. For example, the SQL statement embedded in a Java class file: `String sql = "SELECT * FROM table WHERE id = ?";`

[0055] SQL statements refer to statements in the Structured Query Language (SQL) used to manipulate databases, including operations such as querying, updating, and inserting. For example, `SELECT * FROM table WHERE id = ?`.

[0056] Structured information refers to the syntax structure, operation types (such as JOIN and aggregate functions), and field information of SQL statements extracted through syntax tree parsing technology. For example, it might extract the fields `id` and `name` from the SELECT operation, and `id=?` from the WHERE condition.

[0057] The default rule base is a collection of rules that stores database development specifications, historical migration cases, and performance issues, used to match non-standardized SQL. For example, the rule "prohibits the use of the TO_CHAR function from traditional relational databases."

[0058] The adaptation suggestions are specific modification plans for non-standard SQL, such as syntax replacement and index optimization suggestions. For example, it is suggested to replace TO_CHAR(date_column, 'YYYY-MM-DD') with CAST(date_column AS DATE).

[0059] The automatic identification and adaptation suggestion generation of non-standard SQL in this application embodiment includes:

[0060] Code file parsing: Receives code files submitted by developers, such as SQL files and Java class files. Filters SQL class files by file extension and uses syntax tree parsing technology to extract structured information of SQL statements, such as operation types, field names, and conditional expressions.

[0061] Rule matching: The extracted structured information is matched against a pre-defined rule base to identify non-standardized SQL, such as syntax specific to traditional relational databases or performance bottleneck scenarios. For example, if the rule base contains "prohibit the use of the TO_CHAR function", the system will match TO_CHAR(date_column, 'YYYY-MM-DD') and mark it as a violation.

[0062] Adaptation suggestion generation: Based on the matching results, adaptation suggestions are generated, such as syntax replacement and index optimization, and integrated into a report for developers to review. For example, for the non-compliant TO_CHAR function, the system suggests replacing it with the standard SQL CAST function.

[0063] The method provided in the above embodiments solves the problems of high cost and low accuracy of manual verification of non-standard SQL in existing technologies by combining rule base and syntax tree parsing technology. Its technical principle lies in dynamically generating and maintaining a rule base based on database development specifications, historical migration cases, and performance issues, covering the syntax, performance bottleneck scenarios, and semantic deviation risk points of non-standard SQL. For example, the rule base can directly match the TO_CHAR function of traditional relational databases and suggest replacing it with the CAST function of standard SQL. The SQL statement is parsed into a structured syntax tree, extracting operation types (such as JOIN, aggregate functions), field names, and conditional expressions, avoiding the ambiguity of traditional regular expression extraction. For example, by parsing the association dimensions of multi-table JOINs, the system can identify the business meaning of JOIN fields (such as primary keys, foreign keys) and suggest optimizing the JOIN order or adding indexes in the target database. Combining the rule matching results with syntax tree parsing information, targeted adaptation suggestions (such as syntax replacement and index optimization) are automatically generated, reducing the workload of manual verification.

[0064] This method achieves accurate identification and automated adaptation of non-standardized SQL, significantly reducing the cost of manual intervention and improving the relevance of adaptation suggestions. For example, in complex JOIN scenarios, the system can identify the business meaning of JOIN fields and suggest optimizing query logic in the target database, thereby reducing the risk of performance fluctuations after migration. Furthermore, the dynamic management of the rule base ensures that adaptation rules are continuously updated to adapt to changes in the syntax characteristics of new distributed databases, avoiding missed detections due to rule lag.

[0065] In one embodiment, the database development specification parses the SQL statements in the code file to be processed, including:

[0066] Determine the file type of the database development specification code file to be processed based on its file extension.

[0067] Based on the database development specification file type, extract the SQL statements from the database development specification code file to be processed.

[0068] The file types include stored procedure code and Java class program files. The file type is determined by the SQL suffix, which indicates a file format with an SQL extension, such as .sql or .ddl. For example, the filename is table_structure.sql.

[0069] SQL keywords are specific terms used in SQL statements to identify the type of operation, such as SELECT, INSERT, and UPDATE. For example, in a Java class file, `String sql = "SELECT * FROM table";`

[0070] Boundary identification refers to determining the start and end positions of an SQL statement using SQL keywords and statement termination markers (such as semicolons); for example, identifying the end position of SELECT * FROM table WHERE id = 1; as semicolons.

[0071] In one embodiment, such as Figure 2 As shown, the database development specification extracts SQL statements from the code file to be processed based on the database development specification file type, including:

[0072] Step 201: For the first target file of the database development specification file type, which is a stored procedure code, extract the SQL statements in the first target file of the database development specification file according to the SQL keywords and the SQL end flag.

[0073] Step 202: For the second target file of the database development specification file, which is program code, extract the SQL statements in the second target file of the database development specification file according to the SQL keywords.

[0074] When parsing target code files, SQL-related files are first filtered by their file extensions (e.g., .sql), and the SQL statements within them are extracted directly. For files without .sql extensions (e.g., Java class files), the boundaries of SQL statements are identified by scanning the file content for SQL keywords (e.g., SELECT, INSERT), and the complete SQL statements are extracted. For example, the line `String sql = "SELECT * FROM table WHERE id = ?";` in a Java class file will be identified as an SQL statement and extracted.

[0075] The method provided in the above embodiments achieves accurate extraction of embedded SQL statements from files without SQL suffixes by combining file extension filtering with SQL keyword recognition. For example, for dynamically concatenated SQL statements in Java class files, the system can extract the complete statement by scanning the SELECT keyword and statement end markers (such as ;), avoiding omissions caused by non-standard file formats. This refinement step improves the comprehensiveness of SQL statement extraction, ensuring that the generated adaptation suggestions cover all potential non-standard SQL scenarios.

[0076] In one embodiment, the database development specification determines, based on the matching results, the adaptation suggestions corresponding to the non-standard SQL statements in the database development specification, including at least one of the following: syntax replacement, index optimization suggestions, and data preprocessing strategies.

[0077] Syntax substitution refers to replacing non-standard SQL syntax with syntax compatible with the target database. For example, replacing TO_CHAR(date_column, 'YYYY-MM-DD') with CAST(date_column AS DATE).

[0078] Index optimization recommendations suggest adding or adjusting indexes in the target database to improve query performance. For example, it might be recommended to add an index on the JOIN field.

[0079] Data preprocessing strategies suggest pre-compiling intermediate results before migration to reduce resource consumption during execution. For example, pre-compiling intermediate tables for complex JOIN operations.

[0080] Based on the matching results, adaptation suggestions may selectively include syntax replacement (such as replacing functions specific to traditional relational databases), index optimization suggestions (such as adding indexes to JOIN fields), or data preprocessing strategies (such as pre-compiling intermediate results). For example, for high-risk complex JOIN queries, the system may simultaneously suggest syntax replacement and pre-compiling intermediate tables to reduce the execution overhead after migration.

[0081] By offering diverse adaptation suggestions, targeted optimizations are achieved for different migration scenarios. For example, syntax replacement is prioritized to address syntax compatibility issues, while index optimization and data preprocessing strategies are combined to address performance bottlenecks, thereby enhancing the flexibility and practicality of the adaptation suggestions.

[0082] In one embodiment, such as Figure 3 As shown, after the database development specification determines the adaptation suggestions for non-standard SQL statements based on the matching results, it also includes:

[0083] Step 301: Generate a virtual execution plan based on the query optimizer rules of the target database;

[0084] Step 302: Calculate the resource consumption of SQL statements in the code files to be processed in the database development specification according to the virtual execution plan of the database development specification.

[0085] Query optimizer rules refer to the algorithms and strategies used in a database management system to generate execution plans. For example, the `cost_model` parameter in a traditional relational database.

[0086] An execution plan refers to the execution path generated by the database query optimizer for an SQL statement, including index usage, JOIN order, etc. In traditional relational databases, execution plans are generated using the EXPLAIN PLAN. A virtual execution plan refers to a simulated execution path for the SQL statement, rather than the actual execution. For example, a plan generated using the EXPLAIN statement.

[0087] Resource consumption refers to the usage of system resources such as CPU, memory, and I / O during the execution of SQL statements. For example, high I / O consumption caused by a full table scan.

[0088] After generating adaptation suggestions, the system further simulates the execution plan of the target database, analyzes the execution path of SQL statements in the target database, such as index selection and partitioning strategies, and predicts their resource consumption, such as CPU, memory, and I / O. For example, for complex JOIN operations, the system can simulate the partitioning strategy of the target database, predict the resource consumption of cross-node data transfer, and generate optimization suggestions, such as pre-calculating intermediate results.

[0089] Specifically, when simulating execution plans, the system generates virtual execution plans based on the target database's query optimizer rules (such as index selection strategies and partitioning rules), and calculates the resource consumption (such as CPU, memory, and I / O) of SQL statements accordingly. For example, for the sharding rules of a distributed database, the system can simulate the impact of data sharding strategies on JOIN operations and predict the resource consumption of cross-node data transfer.

[0090] The method provided in the above embodiments achieves accurate prediction of the target database execution plan through the simulation of query optimizer rules. For example, in a distributed database, the system can simulate the impact of data sharding strategies on JOIN operations and suggest that developers adjust query logic to reduce cross-node data transfer, thereby improving query performance after migration.

[0091] In one embodiment, after the database development specification matches the structured information of the database development specification with a preset rule base, the method further includes:

[0092] Based on feedback data from historical migration cases, the rule weights in the rule base are dynamically updated.

[0093] Feedback data refers to data collected after SQL adaptation is completed, such as the adaptation success rate and performance improvement. This includes SQL statements that failed to adapt and their rectification records.

[0094] Rule weight refers to the matching priority or applicability score of each rule in the rule base. For example, the matching threshold for high-risk rules is lowered.

[0095] After rule matching is completed, the system collects feedback data from historical migration cases (such as adaptation success rate and performance improvement), and dynamically adjusts the weights of rules in the rule base based on supervised learning algorithms (such as decision trees). For example, for rules with high adaptation failure rates, the system can lower their matching threshold to ensure that the rule base continuously adapts to changes in the syntax characteristics of new distributed databases.

[0096] By dynamically optimizing the rule base through an incremental learning mechanism, the system achieves continuous updates to the adaptation rules. For example, when a new aggregate function syntax is added to the target database, the system can quickly generate adaptation rules and adjust the matching priority, avoiding missed detections caused by rule lag, while reducing interference from inefficient rules and improving the accuracy of adaptation suggestions.

[0097] In one embodiment, before the database development specification parses the SQL statements in the code file to be processed, it also includes:

[0098] After a code commit is detected, a code file to be processed is generated based on the committed code;

[0099] Push adaptation suggestions through code editing tools;

[0100] After the adaptation suggestions are generated, the performance prediction and rule base update process is automatically triggered.

[0101] Specifically, the detection of code commits occurs within the code commit pipeline, which refers to the automated build and testing process triggered when a developer commits code. For example, a Git commit triggers a Jenkins pipeline.

[0102] Code editing tools refer to the software environment that developers use to write code.

[0103] Automatic triggering refers to the system automatically executing subsequent processing, such as performance prediction and rule base updates, after the adaptation suggestions are generated. For example, after the adaptation suggestions are generated, the system automatically simulates the execution plan and updates the rule base.

[0104] In the code submission pipeline, when developers submit code, the system pushes adaptation suggestions through the code editing tool, such as highlighting non-compliant SQL. After the adaptation suggestions are generated, the system automatically triggers subsequent processes, including performance prediction (simulating execution plans) and rule base updates (adjusting rule weights based on feedback data). For example, after a developer submits an SQL file containing syntax specific to traditional relational databases in the IDE, the system pushes adaptation suggestions in real time and automatically simulates the execution plan to predict performance risks.

[0105] By integrating code editing tools and implementing automated processes, a complete closed loop is built, from SQL scanning to adaptation suggestions, performance prediction, and rule base updates. For example, developers receive adaptation suggestions in real time within the IDE, and the system automatically predicts performance risks and updates the rule base after adaptation is complete, thus forming a continuously optimized migration process that significantly improves the efficiency and reliability of database migration.

[0106] This application also provides a non-standard SQL statement interception device, including:

[0107] Scan rule formulation module: It is used to compile a set of scan rules based on the development and coding standards of the database, the SQL syntax of various database characteristics, slow performance scenarios, specific query optimization scenarios, and production issues that have occurred in the past.

[0108] The SQL inspection module decompresses the uploaded code files and filters them again based on their file extensions to identify SQL-related program files. Stored procedure code is extracted based on SQL keywords and SQL end-of-line markers; Java programs are extracted based on SQL keywords. Using techniques such as syntax tree parsing and SQL feature analysis, each extracted SQL statement is reviewed, matching every rule in the rule set and returning the review result for each specification to the caller.

[0109] Results display module: Used to query audit results, including scanning rules, rectification and optimization suggestions, etc.

[0110] The entire solution first performs SQL file parsing and statement extraction, using code files submitted by developers, such as SQL files, Java class files, and stored procedure code. SQL class files are filtered by file extension, and SQL statements are extracted using syntax tree parsing technology. For example, SQL statements embedded in Java class files (such as `String sql = "SELECT * FROM table WHEREid = ?";`) are extracted using SQL keywords (such as `SELECT` and `FROM`), and statement boundaries are identified using syntax tree parsing. The result is a set of SQL statements and their contextual information (such as call location and parameter types). Next, rule matching and suggestion generation are performed, matching each SQL statement against rules in a scanned rule base to generate adaptation suggestions. For example, if the rule base contains "prohibit the use of the traditional relational database's TO_CHAR function," when `TO_CHAR(date_column, 'YYYY-MM-DD')` is extracted, the rule is matched, and it is suggested to replace it with the standard SQL CAST(date_column AS DATE). This provides the check results for each SQL statement (such as whether it violates a rule and adaptation suggestions).

[0111] Building upon the above approach, an incremental learning mechanism is introduced to dynamically optimize rule weights and matching strategies in the rule base using feedback data from historical migration cases (such as adaptation success rates and performance improvements). For example, for SQL statements that fail to adapt, new rules are automatically generated and the priority of existing rules is adjusted.

[0112] The incremental learning model can be trained on historical migration cases using supervised learning algorithms (such as decision trees and random forests) to identify high-risk rules (such as rules with high failure rates) in the rule base and optimize matching strategies. After SQL adaptation is completed, data on developers' adoption of adaptation suggestions and post-migration performance (such as execution time and resource consumption) are collected. Based on feedback data, the matching priority of each rule in the rule base is dynamically adjusted (such as lowering the matching threshold for high-risk rules) to ensure that the rule base continuously adapts to the characteristics of the new distributed database.

[0113] Through an incremental learning mechanism, the rule base can automatically adapt to new non-standardized SQL scenarios that arise during database migration, such as the unique syntax features of new distributed databases. For example, when the target database adds a new aggregate function syntax, the system can quickly generate adaptation rules and adjust the matching strategy to avoid missed detections due to rule lag. Simultaneously, dynamically optimizing rule weights can reduce interference from inefficient rules, improve the accuracy of adaptation suggestions, and reduce the workload for developers in making changes.

[0114] In one embodiment, context-aware technology is introduced during the semantic parsing phase. This involves combining business logic with database metadata (such as table structure and index information) to perform deep semantic analysis of SQL, avoiding semantic misjudgments caused by relying solely on syntax tree parsing. For example, for multi-table JOIN operations, the business meaning of the JOIN fields (such as primary key and foreign key relationships) is analyzed to determine their suitability.

[0115] Specifically, by parsing the database table structure (such as field types and constraints) and historical SQL execution logs, a business logic model, such as a data flow diagram and entity relationship diagram, is constructed. Based on the syntax tree parsing, the business logic model is used to determine whether the SQL semantics conform to the execution logic of the target database. For example, it identifies whether a CONNECT BY recursive query in a traditional relational database depends on a specific index structure and suggests rebuilding similar indexes in the target database. During the SQL scan, metadata of the target database, such as table partitioning rules and index distribution, is loaded in real time to ensure that the semantic analysis results are consistent with the actual environment of the target database.

[0116] Context-aware analysis significantly improves the accuracy of SQL semantic parsing, avoiding biases in adaptation suggestions caused by limitations in syntax tree parsing. For example, in multi-table JOIN scenarios, the system can identify the business meaning of JOIN fields (such as primary key associations) and suggest optimizing the JOIN order or adding missing indexes in the target database, thereby improving the relevance of adaptation suggestions. Furthermore, dynamically loading metadata from the target database ensures that semantic analysis results match the actual execution environment, reducing performance prediction errors caused by environmental differences.

[0117] It should be understood that although the steps in the flowcharts of the various embodiments of the database development specification described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some of the steps in the flowcharts of the various embodiments of the database development specification described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0118] Based on the same inventive concept, this application also provides a non-standard SQL processing device. The solution provided by this device is similar to the solution described in the above method. Therefore, the specific limitations of one or more non-standard SQL processing device embodiments provided below can be found in the limitations of the non-standard SQL processing method above, and will not be repeated here.

[0119] In one embodiment, such as Figure 4 As shown, the non-standardized SQL processing device includes a parsing and extraction module 401, a matching module 402, and a suggestion generation module 403, wherein:

[0120] The parsing and extraction module 401 is used to parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements in the database development specification.

[0121] The matching module 402 is used to match the structured information of the database development specification with the preset rule base to identify non-standard SQL statements in the code file to be processed by the database development specification; the preset rule base of the database development specification is generated based on the database development specification, historical migration cases and performance problem cases;

[0122] It is recommended to generate module 403 to determine the compatibility recommendations for non-standard SQL statements in the database development specification based on the matching results.

[0123] In one possible implementation, the parsing and extraction module 401 is specifically used for:

[0124] Determine the file type of the database development specification code file to be processed based on its file extension.

[0125] Based on the database development specification file type, extract the SQL statements from the database development specification code file to be processed.

[0126] In one possible implementation, the parsing and extraction module 401 is specifically used for:

[0127] For the first target file of the database development specification file, which is a stored procedure code, extract the SQL statements in the first target file of the database development specification file based on the SQL keywords and the SQL end flag.

[0128] For the second target file of the database development specification file, which is a program code file, extract the SQL statements in the second target file of the database development specification file based on the SQL keywords.

[0129] In one possible implementation, it is suggested that module 403 be specifically used for:

[0130] Based on the matching results, determine the database development specifications. The adaptation suggestions for non-standard SQL statements include at least one of the following: syntax replacement, index optimization suggestions, and data preprocessing strategies.

[0131] In one possible implementation, it is suggested that the generation module 403 is also used for:

[0132] Based on the query optimizer rules of the target database, a virtual execution plan is generated;

[0133] Based on the database development specification's virtual execution plan, calculate the resource consumption of SQL statements in the code file to be processed.

[0134] In one possible implementation, it is suggested that the generation module 403 is also used for:

[0135] Based on feedback data from historical migration cases, the rule weights in the rule base are dynamically updated.

[0136] In one possible implementation, the parsing and extraction module 401 is further used for:

[0137] After a code commit is detected, a code file to be processed is generated based on the committed code;

[0138] Push adaptation suggestions through code editing tools;

[0139] After the adaptation suggestions are generated, the performance prediction and rule base update process is automatically triggered.

[0140] The modules in the aforementioned non-standard SQL processing device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0141] Figure 5 A schematic diagram of the structure of the computer device provided in this application. Figure 5 As shown, the computer device 50 provided in this embodiment includes at least one processor 501 and a memory 502. Optionally, the device 50 further includes a communication component 503. The processor 501, memory 502, and communication component 503 are connected via a bus 504.

[0142] In a specific implementation, at least one processor 501 executes computer execution instructions stored in memory 502, causing at least one processor 501 to perform the above-described method.

[0143] The specific implementation process of processor 501 can be found in the above method embodiments, and its implementation principle and technical effect are similar. It will not be repeated here.

[0144] In the above embodiments, it should be understood that the processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in this invention can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules within the processor.

[0145] The memory may include random access memory (RAM) and may also include non-volatile memory (NVM), such as at least one disk storage device.

[0146] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. Buses can be categorized as address buses, data buses, control buses, etc. For ease of illustration, the buses shown in the accompanying drawings are not limited to a single bus or a single type of bus.

[0147] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method.

[0148] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the above-described method.

[0149] The aforementioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The readable storage medium can be any available medium accessible to a general-purpose or special-purpose computer.

[0150] An exemplary readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can reside in an Application Specific Integrated Circuit (ASIC). Alternatively, the processor and the readable storage medium can exist as discrete components in the device.

[0151] The division of units is merely a logical functional division; in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be indirect coupling or communication connection through some interfaces, devices, or units, and may be electrical, mechanical, or other forms.

[0152] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0153] In addition, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0154] If a function is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0155] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.

[0156] Finally, it should be noted that other embodiments of the invention will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This invention is intended to cover any variations, uses, or adaptations of the invention that follow the general principles of the invention and include common knowledge or customary techniques in the art not disclosed herein, and is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of the invention is limited only by the appended claims.

Claims

1. A non-standardized SQL processing method, characterized in that, include: Parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements; The structured information is matched with a preset rule base to identify non-standardized SQL statements in the code file to be processed; the preset rule base is generated based on database development specifications, historical migration cases, and performance problem cases. Based on the matching results, adaptation suggestions are determined for the non-standardized SQL statements.

2. The method according to claim 1, characterized in that, The parsing of SQL statements in the code file to be processed includes: The file type of the code file to be processed is determined based on its suffix. Based on the file type, extract the SQL statements from the code file to be processed.

3. The method according to claim 2, characterized in that, The step of extracting SQL statements from the code file to be processed based on the file type includes: For the first target file whose file type is stored procedure code, extract the SQL statements in the first target file according to the SQL keywords and the SQL end flag. For the second target file whose file type is program code, extract the SQL statements from the second target file based on the SQL keywords.

4. The method according to claim 1, characterized in that, The adaptation suggestions for the non-standard SQL statement determined based on the matching results include at least one of the following: syntax replacement, index optimization suggestions, and data preprocessing strategies.

5. The method according to claim 1, characterized in that, After determining the adaptation suggestions corresponding to the non-standardized SQL statement based on the matching results, the method further includes: Based on the query optimizer rules of the target database, a virtual execution plan is generated; Based on the virtual execution plan, calculate the resource consumption of the SQL statements in the code file to be processed.

6. The method according to claim 5, characterized in that, After matching the structured information with a preset rule base, the method further includes: Based on feedback data from historical migration cases, the rule weights in the rule base are dynamically updated.

7. The method according to claim 1, characterized in that, Before parsing the SQL statements in the code file to be processed, the process also includes: After a code commit is detected, a code file to be processed is generated based on the committed code; Push adaptation suggestions through code editing tools; After the adaptation suggestions are generated, the performance prediction and rule base update process is automatically triggered.

8. A non-standardized SQL processing device, characterized in that, include: The parsing and extraction module is used to parse the SQL statements in the code file to be processed and extract the structured information of the SQL statements; The matching module is used to match the structured information with a preset rule base to identify non-standardized SQL statements in the code file to be processed; the preset rule base is generated based on database development specifications, historical migration cases, and performance problem cases. A recommendation generation module is used to determine the adaptation recommendations corresponding to the non-standardized SQL statements based on the matching results.

9. A computer device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1 to 7.

11. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method as described in any one of claims 1 to 7.

Images