Method for transferring mainstream relational database SQL (Structured Query Language) sentences into domestic DM 8 database SQL sentences

Abstract

The invention discloses a method for escaping mainstream relational database SQL statements into domestic DM 8 database SQL statements, and relates to the technical field of databases. Comprising the steps of obtaining SQL statements in a mainstream database, analyzing the SQL statements in the mainstream database, extracting statement keywords, matching the statement keywords through a rule model, converting the statement keywords into keywords used by a DM 8 database, generating the SQL statements of the DM 8 database, and conducting grammar verification and statement optimization on the generated SQL statements of the DM 8 database. And obtaining the optimized DM 8 database statement, outputting the optimized DM 8 database statement, and using and processing the DM 8 database statement by an operator according to requirements. According to the method, the efficiency and accuracy of database migration and compatibility can be improved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular, to a method for escaping SQL statements of mainstream relational databases into SQL statements of the domestic DM8 database. Background Art

[0002] With the rapid development of domestic database technology, more and more enterprises and institutions have begun to consider migrating their original mainstream relational databases to domestic database systems, such as the DM8 database. However, there are differences in SQL syntax between different database systems, which may lead to SQL statement incompatibility issues during direct migration, and there is a lack of a method in the market to escape mainstream database statements into SQL statements of the domestic DM8 database. Currently, there is an urgent need for an efficient and accurate method to achieve automatic escaping of SQL statements to meet the requirements of different database systems.

[0003] Therefore, proposing a method for escaping SQL statements of mainstream relational databases into SQL statements of the domestic DM8 database to solve the problems existing in the prior art is an urgent problem that needs to be solved by those skilled in the art. Summary of the Invention

[0004] In view of this, the present invention provides a method for escaping SQL statements of mainstream relational databases into SQL statements of the domestic DM8 database, which helps to improve the efficiency and accuracy of database migration and compatibility.

[0005] To achieve the above object, the present invention adopts the following technical solutions:

[0006] A method for escaping SQL statements of mainstream relational databases into SQL statements of the domestic DM8 database includes the following steps:

[0007] Data acquisition step: Acquire the SQL statements in the mainstream database;

[0008] Statement parsing step: Parse the SQL statements in the mainstream database and extract statement keywords;

[0009] Statement construction step: Match the statement keywords through a rule model, convert them into keywords used by the DM8 database, and generate SQL statements of the DM8 database;

[0010] Verification and optimization step: Perform syntax verification and statement optimization on the generated SQL statements of the DM8 database to obtain optimized SQL statements of the DM8 database;

[0011] Output and application step: Output the optimized SQL statements of the DM8 database, and the operator uses them according to the requirements.

[0012] In the above method, optionally, the statement parsing step includes constructing a semantic model using ANTLR4.

[0013] In the above method, optionally, the semantic model is divided into a syntax definition layer, a compilation front-end layer, and a business logic layer from bottom to top;

[0014] The syntax definition layer is used to define various database types and generate a syntax definition g4 file;

[0015] The compilation front-end layer is used to process the g4 file to generate the parsing of the input SQL statement and determine the keywords;

[0016] The business logic layer is used to determine the statement type to which the SQL statement belongs according to the SQL statement and the keywords.

[0017] In the above method, optionally, the statement construction step uses a rule model to generate an SQL statement;

[0018] The rule model includes: determining the general SQL statement rules and the specific rules of the DM8 database, comparing the similarities and differences between the DM8 database and other mainstream databases, and using the parsed SQL statement for corresponding keyword replacement.

[0019] In the above method, optionally, the verification in the verification and optimization step includes the verification of the syntax of the SQL statement and writing a script to execute and verify in the DM8 database after the syntax verification.

[0020] In the above method, optionally, the optimization in the verification and optimization step includes simplifying queries, avoiding queries, using aggregate functions and window functions, optimizing the WHERE clause, and selecting the connection type.

[0021] As can be seen from the above technical solutions, compared with the prior art, the present invention provides a method for converting SQL statements of mainstream relational databases into SQL statements of the domestic DM8 database, and has the following beneficial effects: 1) The automated conversion process of the present invention greatly reduces manual intervention and errors, and improves the efficiency of database migration; 2) The present invention ensures that the converted SQL statement can be correctly executed in the DM8 database through an accurate conversion rule library, enhancing the compatibility between different database systems; 3) The present invention reduces the maintenance problems caused by SQL statement incompatibility and reduces the long-term operation cost. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only the embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained according to the provided drawings without creative efforts.

[0023] Figure 1 This is a flowchart of a method for converting SQL statements of a mainstream relational database into SQL statements of the domestic DM8 database disclosed by the present invention;

[0024] Figure 2 This is a schematic diagram of the working principle of the semantic model disclosed by the present invention. Detailed implementation manners

[0025] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0026] In this application, relational terms such as first and second are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. The terms "comprising", "including" or any other variant thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements not only includes those elements, but also includes other elements not expressly listed, or further includes elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "including a..." does not exclude the existence of additional identical elements in the process, method, article or device including the element.

[0027] Refer to Figure 1 As shown, the present invention discloses a method for converting SQL statements of a mainstream relational database into SQL statements of the domestic DM8 database, including the following steps:

[0028] Data acquisition step: Acquire SQL statements in the mainstream database;

[0029] Statement parsing step: Parse the SQL statements in the mainstream database and extract statement keywords;

[0030] Statement construction step: Match the statement keywords through a rule model, convert them into keywords used by the DM8 database, and generate SQL statements of the DM8 database;

[0031] Verification and optimization step: Perform syntax verification and statement optimization on the generated SQL statements of the DM8 database to obtain optimized SQL statements of the DM8 database;

[0032] Output application steps: Output the optimized DM8 database statements, and the operator uses them according to requirements.

[0033] Furthermore, the mainstream databases in the data acquisition step include databases such as MySQL, Oracle, SQLserver, and PostgreSQL used in the mainstream market.

[0034] Furthermore, the statement parsing step includes constructing a semantic model using antlr4.

[0035] Furthermore, referring to Figure 2 As shown, the semantic model is divided into a syntax definition layer, a compilation front-end layer, and a business logic layer from bottom to top;

[0036] The syntax definition layer is used to define each database type and generate a syntax definition g4 file;

[0037] The compilation front-end layer is used to process the g4 file to generate the parsing of the input SQL statement and determine the keywords;

[0038] The business logic layer is used to determine the type of SQL statement to which the SQL statement belongs based on the SQL statement and keywords.

[0039] Specifically, antlr4 is a powerful parser generator tool. It can automatically generate a compiler (the compiler refers to the three stages of lexical analysis, syntax analysis, and semantic analysis) based on the syntax definition, liberating developers from complex compilation theories and enabling them to focus on handling business logic. Antlr4 generates lexical analyzer, syntactic analyzer code, and dictionary files in the compilation front-end layer to complete the parsing of the corresponding input SQL syntax. In the business logic layer, code is written to inherit the Visitor class or implement the Listener interface to develop its own business logic code, and determine the type of SQL statement to which the SQL statement belongs based on keywords included in statements such as DDL, DML, and DCL.

[0040] Furthermore, the statement construction step uses a rule model to generate SQL statements;

[0041] The rule model includes: determining the general SQL statement rules and the specific rules of the DM8 database, comparing the similarities and differences between the DM8 database and other mainstream databases, and using the parsed SQL statement for corresponding keyword replacement.

[0042] Furthermore, determining the general SQL statement rules includes:

[0043] Basic structure: The basic structures and usages of statements such as SELECT, INSERT, UPDATE, DELETE, etc.;

[0044] Data types: Define the data types supported by the database and their usage rules;

[0045] Functions and operators: Define the supported functions and operators and their usage.

[0046] The rules specific to the DM8 database include:

[0047] Specific functions and procedures: List the functions and stored procedures specific to the DM database and define their usage scenarios and rules;

[0048] System tables and views: List and explain the important system tables, views and their uses. Security features: such as permission management, role definition, etc.

[0049] In a specific embodiment, the SELECT rule model principle includes:

[0050] It must start with the SELECT keyword;

[0051] The DISTINCT keyword can be optionally used to remove duplicate rows;

[0052] The column names or expressions to be selected must be specified, or * can be used to select all columns;

[0053] The FROM keyword must be used to specify the table from which to select data;

[0054] The WHERE clause can be used to filter rows;

[0055] The GROUP BY clause can be used to group data;

[0056] The HAVING clause can be used to filter the results after grouping;

[0057] The ORDER BY clause can be used to sort the results;

[0058] The LIMIT / OFFSET clause can be used to limit the number of rows returned.

[0059] The corresponding SQL statement is:

[0060] SELECT DISTINCT column1,column2

[0061] FROM table_name

[0062] WHERE condition

[0063] GROUP BY column1

[0064] HAVING aggregate_function(column2)>value

[0065] ORDER BY column1 ASC

[0066] LIMIT 10 OFFSET 5。

[0067] Furthermore, the verification in the verification and optimization steps includes verifying the syntax of the SQL statement and writing a script after syntax verification to execute and verify in the DM8 database.

[0068] Furthermore, the syntax verification includes: by constructing a g4 grammar file that conforms to the SQL statement rules of the DM8 database, using the lexical parser and syntax parser constructed by antlr4 based on the g4 grammar file to calculate the offset of the input SQL statement, and judging whether it conforms to the syntax through the defined SQL statement standard rules and offset threshold.

[0069] In a specific syntax verification embodiment,

[0070]

[0071]

[0072] Furthermore, the execution verification includes: in the DM database, the EXPLAIN command can be used to view the execution plan of the SQL statement. This command will show how the database executes the query, including the indexes used, join types, and data scanning methods; by analyzing the execution plan, potential performance bottlenecks can be found, such as full table scans and missing indexes. After the plan analysis, the corresponding database table structure can be constructed in the DM8 database, and the corresponding SQL statement can be executed to view the final execution situation.

[0073] Furthermore, the optimization in the verification and optimization steps includes simplifying queries, avoiding queries, using aggregate functions and window functions, optimizing the WHERE clause, and selecting the join type.

[0074] Specifically, simplifying queries includes: trying to decompose complex queries into multiple simple queries to reduce the complexity of a single query; using the WITH clause (common table expression) to simplify complex subqueries and repeated calculations;

[0075] Avoiding queries includes: whenever possible, rewriting subqueries as join operations, because join operations are usually more efficient than subqueries; paying special attention to the selection of the IN clause and the EXISTS clause, and choosing a more optimal writing method according to the actual situation;

[0076] Using aggregate functions and window functions includes: Reasonably using aggregate functions and window functions can reduce the number of data scans and the amount of data transmission; Pay attention to the performance overhead of window functions and avoid using complex window functions on large datasets;

[0077] Optimizing the WHERE clause includes: Avoid using complex logical expressions in the WHERE clause, especially those containing multiple ORs and ANDs; Try to split complex logical expressions into multiple simple queries and use UNION or UNION ALL to combine the results;

[0078] The ways of selecting connection types include: Selecting the appropriate connection type according to the actual situation of the query, such as HASHJOIN, NEST LOOP JOIN, MERGE JOIN, etc.;

[0079] Generally speaking, HASH JOIN is suitable for joining between large datasets, while NEST LOOP JOIN is suitable for joining between small datasets.

[0080] Each embodiment in this specification is described in a progressive manner. The same or similar parts between each embodiment can be referred to each other, and the key point of each embodiment is to illustrate the differences from other embodiments. In particular, for a system or system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment. The systems and system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art can understand and implement it without creative efforts.

[0081] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present invention. Various modifications to these embodiments will be obvious to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but will be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for converting SQL statements of a mainstream relational database into SQL statements of the domestic DM8 database, characterized in that, It includes the following steps: Data acquisition step: Obtain SQL statements in mainstream databases; Statement parsing step: Parse SQL statements in mainstream databases and extract statement keywords; Statement construction step: Match statement keywords through a rule model, convert them into keywords used by DM8 database, and generate SQL statements for DM8 database; Verification and optimization step: Perform syntax verification and statement optimization on the generated SQL statements for DM8 database to obtain optimized SQL statements for DM8 database; Output and application step: Output the optimized SQL statements for DM8 database, and the operator uses them according to requirements.

2. A method for converting SQL statements of a mainstream relational database into SQL statements of domestic DM8 database according to claim 1, characterized in that The statement parsing step includes constructing a semantic model using ANTLR4.

3. A method for converting SQL statements of a mainstream relational database into SQL statements of domestic DM8 database according to claim 2, characterized in that The semantic model is divided into a syntax definition layer, a compilation front-end layer, and a business logic layer from bottom to top; The syntax definition layer is used to define each database type and generate a syntax definition g4 file; The compilation front-end layer is used to process the g4 file to generate the parsing of the input SQL statements and determine the keywords; The business logic layer is used to determine the statement type to which the SQL statement belongs according to the SQL statement and keywords.

4. A method for converting SQL statements of a mainstream relational database into SQL statements of domestic DM8 database according to claim 1, characterized in that The statement construction step uses a rule model to generate SQL statements; The rule model includes: determining general SQL statement rules and DM8 database-specific rules, comparing the similarities and differences between the DM8 database and other mainstream databases, and using the parsed SQL statements for corresponding keyword replacement.

5. A method for converting SQL statements of a mainstream relational database into SQL statements of domestic DM8 database according to claim 1, characterized in that In the verification and optimization step, the verification includes verification of the SQL statement syntax and writing a script after syntax verification to execute and verify in the DM8 database.

6. A method for converting SQL statements of a mainstream relational database into SQL statements of domestic DM8 database according to claim 1, characterized in that In the verification and optimization step, the optimization includes simplifying queries, avoiding queries, using aggregate functions and window functions, optimizing the WHERE clause, and selecting connection types.

Images