A method for translating SQL statements based on an advanced abstract syntax tree

Abstract

The application discloses a SQL sentence translation method based on an advanced abstract syntax tree, which comprises the following steps: 1. writing a lexical file and a syntax file of a SQL language; 2. converting the written lexical file and syntax file into a syntax parser by using an ANTLR language recognition tool; 3. the syntax parser constructs an abstract syntax tree for a SQL sentence to be translated from top to bottom according to the syntax file; 4. translating each sub-tree according to the structure of the syntax tree and saving the sub-tree translation result; and 5. finally completing the complete translation of the SQL sentence by combining the sub-tree translation result in a bottom-up manner. The application uses logical association words in the process of combining the sub-tree translation result to ensure that the translation result is natural, smooth and orderly; and different combination translation modes are given for SQL sentences with different complexities, so that the translation result is clearer, the adaptability is extremely strong, and the accuracy of the SQL sentence translation is significantly improved.

Description

Technical Field

[0001] This invention relates to the field of databases, and mainly to the translation of SQL language into natural language within databases. Background Technology

[0002] With the widespread adoption of enterprise applications and data management systems, relational databases have become crucial data storage and management tools for businesses. SQL (Structured Query Language), as the standard query language for relational databases, is widely used in enterprise applications. However, the syntax and query statements of SQL are often complex, making it difficult for ordinary users to understand and use. Therefore, it is necessary to develop a method and system that can translate SQL into natural language, enabling non-experts to review SQL operations in business systems, thereby improving system security and process standardization.

[0003] Currently, there is extensive research in the field of SQL statement translation into natural language, including rule-based and machine learning methods. Rule-based methods use predefined rules to translate SQL statements into natural language. These rules need to be defined manually and typically include templates, mapping tables, and translation rules to map different parts of the SQL statement to corresponding natural language descriptions. Machine learning-based methods automatically learn the relationship between SQL statements and natural language through training data to complete the translation. However, the highly structured and standardized nature of SQL statements poses challenges for machine learning methods in understanding and parsing them, thus limiting their ability to handle complex SQL statements and making them unsuitable for real-world applications. Summary of the Invention

[0004] This invention addresses the shortcomings of existing technologies by proposing an SQL statement translation method based on a high-level abstract syntax tree. This method utilizes logical connectives to ensure the natural fluency and orderliness of the translation results, thereby significantly improving the accuracy of SQL statement translation and guaranteeing better translation outcomes.

[0005] To achieve the above-mentioned objectives, the present invention adopts the following technical solution:

[0006] The present invention provides a method for translating SQL statements based on a high-level abstract syntax tree, characterized by the following steps:

[0007] Step 1: Write the lexical and grammar files for the SQL language, and use the ANTLR tool to convert the written lexical and grammar files into a syntax parser P;

[0008] Step 2: Obtain the SQL statement to be translated, denoted as S = {s1, s2, ..., s...}i ..., s n}, where s i This represents the i-th word, and n represents the length of the SQL statement;

[0009] Step 3: Construct an abstract syntax tree T from top to bottom based on the SQL statement S to be translated using the syntax parser P;

[0010] Step 3.1: Initialize the root node R of T = 'root', where 'root' represents the word corresponding to the root node, and let T = {R};

[0011] Step 3.2: Set the keywords SELECT, FROM, WHERE, GROUP BY, HAVING, ORDERBY, and LIMIT;

[0012] Step 3.3: Determine whether the keyword SELECT exists in S. If it does, the syntax parser P will identify the keyword SELECT and all subsequent column fields as a SELECT subtree T according to the written syntax file. s Otherwise, let T s The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s );

[0013] Step 3.4: Determine whether the keyword FROM exists in S. If it exists, the syntax parser P will identify the keyword FROM and all subsequent table fields as the FROM subtree T according to the written syntax file. f Otherwise, let T f The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f );

[0014] Step 3.5: Determine whether the keyword WHERE exists in S. If it does, the parser P will identify the keyword WHERE and all subsequent condition fields as the WHERE subtree T according to the written syntax file. w Otherwise, let T w The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w );

[0015] Step 3.6: Determine whether the keyword GROUPBY exists in S. If it does, the parser P will identify the keyword GROUPBY and all its subsequent grouping fields as the GROUPBY subtree T according to the written grammar file. g Otherwise, let Tg The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g );

[0016] Step 3.7: Determine whether the keyword HAVING exists in S. If it does, the parser P will identify the keyword HAVING and all subsequent condition fields as the HAVING subtree T according to the written syntax file. h Otherwise, let T h The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g T h );

[0017] Step 3.8: Determine whether the keyword ORDERBY exists in S. If it does, the syntax parser P will identify the keyword ORDERBY and all subsequent sorting fields as the ORDERBY subtree T according to the written syntax file. o Otherwise, let T o The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w ,T g T h T o );

[0018] Step 3.9: Determine whether the keyword LIMIT exists in S. If it exists, the parser P identifies the keyword LIMIT and all subsequent restriction fields as the LIMIT subtree T according to the written grammar file. l Otherwise, let T l Empty; thus obtaining the final updated abstract syntax tree T = (R, T s T f T w T g T h T o T l );

[0019] Step 4: Translate each subtree;

[0020] Step 4.1: Initialize the collection Result = {} to store the translation results of the subtree;

[0021] Step 4.2: Traverse the abstract syntax tree T of the SQL statement S. When traversing to the SELECT subtree T... s At that time, add a key K to the Result collection to map the translation results. s And determine T s Is it empty? If it is empty, then set key K. s If it is null; otherwise, T will be null. s The translation result R s Assigned to key K s ;

[0022] Step 4.3: When traversing to the FROM subtree T f Add a key K to the Result collection to map the translation results. f1 and key K f2 And determine T f Is it empty? If T f If it is empty, then let key K be empty. f1 and key K f2 All are null values; otherwise, translate T directly. f All nodes and the translation result R f1 As key K f1 The value; at the same time, translate T separately. f The translation results for the table nodes and join condition nodes in the table are R, respectively. c and R t Store both translation results together in array R. f2 In, and R f2 As key K f2 The value;

[0023] Step 4.4: Following the process in Step 4.2, process the WHERE subtree T respectively. w GROUPBY subtree T g HAVING subtree T h , ORDERBY subtree T o LIMIT subtree T l Processing is performed to obtain the translation results R of the corresponding subtrees. w ,R h ,R g R o R l The corresponding value is assigned to key K. w ,K h ,K g K o K l This results in the final set of subtree translation results, Result = {K}. s :R s K f1 :R f1 Kf2 :R f2 K w :R w ,K h :R h ,K g :R g K o :R o K l :R l};

[0024] Step 5: Combine the translation results of the subtrees from bottom to top into a set named Result;

[0025] Step 5.1: Determine the complexity of the SQL statement S. If S only contains the keywords SELECT, FROM, and WHERE, then select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f1 If the result is not found in the subtree, proceed to step 5.2; otherwise, select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f2 ], and proceed to step 5.3;

[0026] Step 5.2: Combine the translation results of the subtrees in Result in the order of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDERBY, LIMIT to obtain the translation result Trans;

[0027] Step 5.3: First, combine the main body of the SQL statement s according to the keywords FROM, SELECT, ORDERBY, LIMIT; then, combine the conditional statements in the SQL statement s according to the keywords WHERE, GROUPBY, HAVING, thus obtaining the translation result Trans.

[0028] The SQL statement translation method based on a high-level abstract syntax tree described in this invention is also characterized in that step 5.2 includes the following steps:

[0029] Step 5.2.1: Initialize the translation result Trans to an empty string; define the following conjunctions to connect the translation results of subtrees: "Then" as Then, "Query and return" as Sr, "Query and return again" as Asr, "Finally, for the records after the query" as Fo, and "," as Comma;

[0030] Initialize the flag pointer i, which indicates whether the conjunction is used or not, to 0;

[0031] Step 5.2.2: Traverse the subtree translation result set Result. If FROM subtree T f Translation result Result[K f1 If the result is not empty, then add Result[K] to Trans in sequence. f1 If the value of i is 1, then the value of i+1 is assigned to i, and then step 5.2.3 is executed; otherwise, step 5.2.3 is executed directly.

[0032] Step 5.2.3, if WHERE subtree T w Translation result Result[K w If the result is not empty, then add Result[K] to Trans in sequence. w If i is a comma and i+1 is assigned to i, then proceed to step 5.2.4; otherwise, proceed directly to step 5.2.4.

[0033] Step 5.2.4, if the Grouppy subtree T g Translation result Result[K g If ] is not empty and i = 2, then add Then and Result[K] to Trans in sequence. g After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.5;

[0034] If Result[K] g If ] is not empty and i = 1, then add Result[K] to Trans. g ], Comma, and assign i+1 to i, then execute step 5.2.5;

[0035] If Result[K] g If ] is empty, proceed directly to step 5.2.5;

[0036] Step 5.2.5, if the HAVING subtree T h Translation result Result[K h If the result is not empty, then add Result[K] to Trans in sequence. h If i is a comma, assign i+1 to i and then proceed to step 5.2.6; otherwise, proceed directly to step 5.2.6.

[0037] Step 5.2.6: If SELECT subtree T s Translation result Result[K s If ] is not empty, and i = 2, 3, or 4, then add Asr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7;

[0038] If Result[K] s If ] is not empty and i = 1, then add Sr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7;

[0039] If Result[K] s If ] is empty, proceed directly to step 5.2.7;

[0040] Step 5.2.7, if ORDERBY subtree T o Translation result Result[K o If ] is not empty, then add Sr and Result[K] to Trans in sequence. o If i is a comma and i+1 is assigned to i, then proceed to step 5.2.8; otherwise, proceed directly to step 5.2.8.

[0041] Step 5.2.8: If LIMIT subtree T l Translation result Result[K l If the result is not empty, then add Result[K] to Trans in sequence. l If the expression is incomplete or contains errors, it indicates that the final translation result Trans is obtained directly; otherwise, it indicates that the final translation result Trans is obtained directly.

[0042] Step 5.3 includes the following steps:

[0043] Step 5.3.1: Initialize the translation result Trans to an empty string;

[0044] Define the following related terms to connect the translation results of subtrees: Last for "last", Fc for "query records that meet the following conditions", Se for "select from them", Order for "results", Comma for "," Lbracket for "(", Rbracket for ")", Jc for "join condition is", and Gr for "take from them".

[0045] Initialize the index variable i used to indicate the condition to 1;

[0046] Step 5.3.2: Traverse Result, if FROM subtree T f Translation result Result[K f2 If the result is not empty, then add Result[K] to Trans in sequence. f2 The first value in the array (the translation result of the table node) Result[K f2If [0] or Fc, proceed to step 5.3.3; otherwise, proceed directly to step 5.3.3.

[0047] Step 5.3.3, if SELECT subtree T s Translation result Result[K s If the value is not empty, then add Comma, Se, and Result[K] to Trans in sequence. s If the condition is met, proceed to step 5.3.4; otherwise, proceed directly to step 5.3.4.

[0048] Step 5.3.4: If ORDERBY subtree T o Translation result Result[K o If the value is not empty, then add Comma, Order, and Result[K] to Trans in sequence. o If the condition is met, proceed to step 5.3.5; otherwise, proceed directly to step 5.3.5.

[0049] Step 5.3.5, if LIMIT subtree T l If the translation result Result[Kl] is not empty, then add Comma, Last, and Result[Kl] to Trans in sequence. l If the condition is met, proceed to step 5.3.6; otherwise, proceed directly to step 5.3.6.

[0050] Step 5.3.6: Add Comma, Lbracket, i, Rbracket, Jc, and Result[K] to Trans in sequence. f2 The second value in the array (the translation result of the condition node) Result[K f2 [1], and after assigning i+1 to i, execute step 5.3.7; otherwise, directly execute step 5.3.7;

[0051] Step 5.3.7, if WHERE subtree T w Translation result Result[K w If the value is not empty, then add Comma, Lbracket, i, Rbracket, and Result[K] to Trans in sequence. w If i is positive, assign i+1 to i and then execute step 5.3.8; otherwise, execute step 5.3.8 directly.

[0052] Step 5.3.8, if the Grouppy subtree T g Translation result Result[K g ] is not empty, and the HAVING subtree T h Translation result Result[K hIf not empty, then concatenate Comma, Lbracket, i, Rbracket, Result[K] in sequence. g After that, assign the concatenation result to the new variable Temp, and add Temp, Lbracket, and Result[K] to Trans in sequence. h ] and Rbracket; thus obtaining the final translation result Trans;

[0053] If Result[K] g ] is not empty but Result[K h If ] is empty, then only Temp is added to Trans, thus obtaining the final translation result Trans.

[0054] The present invention provides an electronic device, including a memory and a processor, wherein the memory is used to store a program that supports the processor in executing the SQL statement translation method, and the processor is configured to execute the program stored in the memory.

[0055] The present invention discloses a computer-readable storage medium on which a computer program is stored, wherein the computer program is executed by a processor to perform the steps of the SQL statement translation method.

[0056] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0057] 1. The SQL statement translation method based on high-level abstract syntax tree provided by this invention makes full use of the ideas of top-down construction and bottom-up combination of syntax tree structure, abstracting the SQL statement into a tree with a syntax structure, and this syntax structure becomes the basis for translating the SQL subtree, which enables the SQL statement translation to be performed correctly.

[0058] 2. The SQL statement translation method based on high-level abstract syntax tree provided by this invention has a higher accuracy rate compared with machine learning algorithms and can be applied to SQL statements with complex nested structures; moreover, this invention uses many conjunctions to transition the translation results in SQL statement translation, making the translation results more natural and fluent.

[0059] 3. This invention provides two combination methods for translating SQL statements and sets rules to automatically select the appropriate combination translation method for the input SQL statement. Both combination methods can adapt to all SQL statements, but their translation results have their own advantages on SQL statements of varying complexity. For example, the combination method that combines the translation results of subtrees according to the execution order of the SQL statement is more user-friendly for SQL statements with fewer conditions, while the combination method that separates the SQL statement body and SQL statement conditions is more user-friendly for SQL statements with more conditions, because it uses a condition list presentation method, and a large number of conditions can be clearly translated and displayed. Attached Figure Description

[0060] Figure 1 This is an example SQL statement abstract syntax tree diagram of the present invention;

[0061] Figure 2 This is a subtree partitioning diagram of the SQL statement in this invention;

[0062] Figure 3 This is a flowchart illustrating the implementation of the present invention. Detailed Implementation

[0063] In this example, a SQL language translation method based on a high-level abstract syntax tree (LST) first designs the lexical and grammar files for the SQL language. The tree construction engine then generates a lexical-grammar parser based on these files. Next, the engine constructs a syntax tree for the SQL statement from top to bottom based on the parser. Finally, it traverses this syntax tree and translates each subtree node, as shown in the subtree node partitioning diagram. Figure 2 As shown; each SQL statement is divided into seven subtrees: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. These subtrees are traversed and translated, and the translation results are stored in a specific data structure. Finally, following a bottom-up approach, different methods are used to combine the subtree translation results, and the appropriate combination method is automatically selected based on the complexity of the SQL statement, ultimately completing the translation of the entire SQL statement. Specifically, as... Figure 3 As shown, the entire implementation process is carried out according to the following steps:

[0064] Step 1: Write the lexical and grammar files for the SQL language, and use the ANTLR tool to convert the written lexical and grammar files into a syntax parser P;

[0065] Step 2: Obtain the SQL statement to be translated, denoted as S = {s1, s2, ..., s...} i ..., s n}, where s i This represents the i-th word, and n represents the length of the SQL statement;

[0066] Step 3: Construct an abstract syntax tree T from top to bottom based on the SQL statement S to be translated using the syntax parser P.

[0067] Step 3.1: Initialize the root node R of T = 'root', where 'root' represents the word corresponding to the root node, and let T = {R};

[0068] Step 3.2: Set the keywords SELECT = 'select', FROM = 'from', WHERE = 'where', GROUP BY = 'group by', HAVING = 'having', ORDERBY = 'order by', and LIMIT = 'limit'.

[0069] Step 3.3: Determine if the keyword SELECT exists in S. If it does, the syntax parser P will identify the keyword SELECT and all subsequent column fields as a SELECT subtree T according to the written syntax file. s Otherwise, let T s The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s );

[0070] Step 3.4: Determine if the keyword FROM exists in S. If it exists, the syntax parser P will identify the keyword FROM and all subsequent table fields as the FROM subtree T according to the written syntax file. f Otherwise, let T f The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f ).

[0071] Step 3.5: Determine if the keyword WHERE exists in S. If it does, the parser P will identify the keyword WHERE and all subsequent condition fields as the WHERE subtree T according to the written syntax file. w Otherwise, let T w The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w );

[0072] Step 3.6: Determine if the keyword GROUPBY exists in S. If it does, the parser P, based on the written grammar file, identifies the keyword GROUPBY and all subsequent grouping fields together as the GROUPBY subtree T. g Otherwise, let T gThe result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g );

[0073] Step 3.7: Determine if the keyword HAVING exists in S. If it does, the parser P will identify the keyword HAVING and all subsequent condition fields as the HAVING subtree T according to the written syntax file. h Otherwise, let T h The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g T h );

[0074] Step 3.8: Determine if the keyword ORDERBY exists in S. If it does, the parser P, based on the written syntax file, identifies the keyword ORDERBY and all subsequent sorting fields as the ORDERBY subtree T. o Otherwise, let T o The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w ,T g T h T o ).

[0075] Step 3.9: Determine if the keyword LIMIT exists in S. If it does, the parser P, based on the written grammar file, identifies the keyword LIMIT and all subsequent restriction fields together as the LIMIT subtree T. l Otherwise, let T l Empty; thus obtaining the final updated abstract syntax tree T = (R, T s T f T w T g T h T o T l );

[0076] Figure 1 The image shows the abstract syntax tree of the SQL statement "select sale_price from shop where quantity < 100". According to step 3.9, the syntax tree of this SQL statement is T = (R, T...). s T f T wT g T h T o T1), from Figure 1 It can be seen that: the root node R is "root", and T s The subtree corresponds to the "selectClause" subtree, T f The subtree corresponds to the "fromClause" subtree, T w Subtree corresponds to "whereClause" subtree, T o The subtree corresponds to the "orderbyClause" subtree, and the remaining subtrees are T. g T h Both Tl and Tl are null values.

[0077] Step 4: Translate each subtree;

[0078] Step 4.1: Initialize the collection Result = {} to store the translation results of the subtree;

[0079] Step 4.2: Traverse the abstract syntax tree T of the SQL statement S. When traversing to the SELECT subtree T... s At that time, add a key K to the Result collection to map the translation results. s And determine T s Is it empty? If it is empty, then set key K. s If it is null; otherwise, T will be null. s The translation result R s Assigned to key K s ;

[0080] Step 4.3: When traversing to the FROM subtree T f Add a key K to the Result collection to map the translation results. f1 and key K f2 And determine T f Is it empty? If T f If it is empty, then let key K be empty. f1 and key K f2 All are null values; otherwise, translate T directly. f All nodes and the translation result R f1 As key K f1 The value; at the same time, translate T separately. f The translation results for the table nodes and join condition nodes in the table are R, respectively. c and R t Store both translation results together in array R. f2 In, and R f2 As key K f2 The value; that is

[0081] Step 4.4: Following the process in Step 4.2, process the WHERE subtree T respectively. w GROUPBY subtree T g HAVING subtree T h , ORDERBY subtree T o LIMIT subtree T l Processing is performed to obtain the translation results R of the corresponding subtrees. w ,R h ,R g R o R l The corresponding value is assigned to key K. w ,K h ,K g K o K l This results in the final set of subtree translation results, Result = {K}. s :R s K f1 :R f1 K f2 :R f2 K w :R w ,K h :R h ,K g :R g K o :R o K l :R l}

[0082] Step 5: Combine the translation results of the subtrees from bottom to top into a set named Result;

[0083] Step 5.1: Determine the complexity of the SQL statement S. If S only contains the keywords SELECT, FROM, and WHERE, then select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f1 If the result is not found in the subtree, proceed to step 5.2; otherwise, select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f2 Then proceed to step 5.3.

[0084] Step 5.2: Combine the translation results of the subtrees in the Result in the order of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT:

[0085] Step 5.2.1: Initialize the translation result Trans to an empty string; define the following conjunctions to connect the translation results of subtrees: "Then" as Then, "Query and return" as Sr, "Query and return again" as Asr, "Finally, for the records after the query" as Fo, and "," as Comma;

[0086] Initialize the flag pointer i = 0, which is used to indicate whether the conjunction is used.

[0087] Step 5.2.2: Traverse the subtree translation result set Result. If FROM subtree T f Translation result Result[K f1 If the result is not empty, then add Result[K] to Trans in sequence. f1 If the value of i is 1, then the value of i+1 is assigned to i, and then step 5.2.3 is executed; otherwise, step 5.2.3 is executed directly.

[0088] Step 5.2.3, if WHERE subtree T w Translation result Result[K w If the result is not empty, then add Result[K] to Trans in sequence. w If i is a comma and i+1 is assigned to i, then proceed to step 5.2.4; otherwise, proceed directly to step 5.2.4.

[0089] Step 5.2.4, if the Grouppy subtree T g Translation result Result[K g If ] is not empty and i = 2, then add Then and Result[K] to Trans in sequence. g After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.5;

[0090] If Result[K] g If ] is not empty and i = 1, then add Result[K] to Trans. g ], Comma, and assign i+1 to i, then execute step 5.2.5;

[0091] If Result[K] g If ] is empty, proceed directly to step 5.2.5.

[0092] Step 5.2.5, if the HAVING subtree T h Translation result Result[K h If the result is not empty, then add Result[K] to Trans in sequence. hIf i is a comma, assign i+1 to i and then proceed to step 5.2.6; otherwise, proceed directly to step 5.2.6.

[0093] Step 5.2.6: If SELECT subtree T s Translation result Result[K s If ] is not empty, and i = 2, 3, or 4, then add Asr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7;

[0094] If Result[K] s If ] is not empty and i = 1, then add Sr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7;

[0095] If Result[K] s If ] is empty, proceed directly to step 5.2.7.

[0096] Step 5.2.7, if ORDERBY subtree T o Translation result Result[K o If ] is not empty, then add Sr and Result[K] to Trans in sequence. o If i is a comma and i+1 is assigned to i, then proceed to step 5.2.8; otherwise, proceed directly to step 5.2.8.

[0097] Step 5.2.8: If LIMIT subtree T l Translation result Result[K l If the result is not empty, then add Result[K] to Trans in sequence. l If the expression is incomplete or contains errors, it indicates that the final translation result Trans is obtained directly; otherwise, it indicates that the final translation result Trans is obtained directly.

[0098] Step 5.3: First, combine the body of the SQL statement s according to the keywords FROM, SELECT, ORDERBY, LIMIT; then, combine the conditional statements in the SQL statement s according to the keywords WHERE, GROUPBY, HAVING.

[0099] Step 5.3.1: Initialize the translation result Trans to an empty string;

[0100] Define the following related terms to connect the translation results of subtrees: Last for "last", Fc for "query records that meet the following conditions", Se for "select from them", Order for "results", Comma for "," Lbracket for "(", Rbracket for ")", Jc for "join condition is", and Gr for "take from them".

[0101] Initialize the index variable i used to indicate the condition to 1.

[0102] Step 5.3.2: Traverse Result, if FROM subtree T f Translation result Result[K f2 If the result is not empty, then add Result[K] to Trans in sequence. f2 The first value in the array (the translation result of the table node) Result[K f2 If [0] or Fc, proceed to step 5.3.3; otherwise, proceed directly to step 5.3.3.

[0103] Step 5.3.3, if SELECT subtree T s Translation result Result[K s If the value is not empty, then add Comma, Se, and Result[K] to Trans in sequence. s If the condition is met, proceed to step 5.3.4; otherwise, proceed directly to step 5.3.4.

[0104] Step 5.3.4: If ORDERBY subtree T o Translation result Result[K o If the value is not empty, then add Comma, Order, and Result[K] to Trans in sequence. o If the condition is met, proceed to step 5.3.5; otherwise, proceed directly to step 5.3.5.

[0105] Step 5.3.5, if LIMIT subtree T l Translation result Result[K l If the value is not empty, then add Comma, Last, and Result[K] to Trans in sequence. l If not, proceed to step 5.3.6; otherwise, proceed directly to step 5.3.6.

[0106] Step 5.3.6: Add Comma, Lbracket, i, Rbracket, Jc, and Result[K] to Trans in sequence. f2 The second value in the array (the translation result of the condition node) Result[Kf2 [1], and after assigning i+1 to i, execute step 5.3.7; otherwise, directly execute step 5.3.7;

[0107] Step 5.3.7, if WHERE subtree T w Translation result Result[K w If the value is not empty, then add Comma, Lbracket, i, Rbracket, and Result[K] to Trans in sequence. w If i is positive, assign i+1 to i and then execute step 5.3.8; otherwise, execute step 5.3.8 directly.

[0108] Step 5.3.8, if the Grouppy subtree T g Translation result Result[K g ] is not empty, and the HAVING subtree T h Translation result Result[K h If not empty, then concatenate Comma, Lbracket, i, Rbracket, Result[K] in sequence. g After that, assign the concatenation result to the new variable Temp, and add Temp, Lbracket, and Result[K] to Trans in sequence. h ] and Rbracket; thus obtaining the final translation result Trans;

[0109] If Result[K] g ] is not empty but Result[K h If ] is empty, then only Temp is added to Trans, thus obtaining the final translation result Trans.

[0110] In this embodiment, an electronic device includes a memory and a processor. The memory stores a program that supports the processor in executing the above-described method, and the processor is configured to execute the program stored in the memory.

[0111] In this embodiment, a computer-readable storage medium stores a computer program, which is executed by a processor to perform the steps of the above method.

[0112] In summary, this invention proposes an SQL language translation method based on a high-level abstract syntax tree. It constructs an abstract syntax tree for SQL statements using a top-down approach, designs rules to translate subtrees within the syntax tree, and employs a bottom-up approach to design various combinations to combine the translated subtrees and complete the SQL statement translation. This method simplifies the understanding and use of SQL language and provides a reliable solution for SQL language translation.

Claims

1. A method for translating SQL statements based on a high-level abstract syntax tree, characterized in that, Includes the following steps: Step 1: Write the lexical and grammar files for the SQL language, and use the ANTLR tool to convert the written lexical and grammar files into a syntax parser P; Step 2: Obtain the SQL statement to be translated, denoted as S = {s1, s2, ..., s...} i ..., s n }, where s i This represents the i-th word, and n represents the length of the SQL statement; Step 3: Construct an abstract syntax tree T from top to bottom based on the SQL statement S to be translated using the syntax parser P; Step 3.1: Initialize the root node R of T = 'root', where 'root' represents the word corresponding to the root node, and let T = {R}; Step 3.2: Set the keywords SELECT, FROM, WHERE, GROUP BY, HAVING, ORDERBY, and LIMIT; Step 3.3: Determine whether the keyword SELECT exists in S. If it does, the syntax parser P will identify the keyword SELECT and all subsequent column fields as a SELECT subtree T according to the written syntax file. s Otherwise, let T s The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s ); Step 3.4: Determine whether the keyword FROM exists in S. If it exists, the syntax parser P will identify the keyword FROM and all subsequent table fields as the FROM subtree T according to the written syntax file. f Otherwise, let T f The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f ); Step 3.5: Determine whether the keyword WHERE exists in S. If it does, the parser P will identify the keyword WHERE and all subsequent condition fields as the WHERE subtree T according to the written syntax file. w Otherwise, let T w The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w ); Step 3.6: Determine whether the keyword GROUPBY exists in S. If it does, the parser P will identify the keyword GROUPBY and all its subsequent grouping fields as the GROUPBY subtree T according to the written grammar file. g Otherwise, let T g The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g ); Step 3.7: Determine whether the keyword HAVING exists in S. If it does, the parser P will identify the keyword HAVING and all subsequent condition fields as the HAVING subtree T according to the written syntax file. h Otherwise, let T h The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w T g T h ); Step 3.8: Determine whether the keyword ORDERBY exists in S. If it does, the syntax parser P will identify the keyword ORDERBY and all subsequent sorting fields as the ORDERBY subtree T according to the written syntax file. o Otherwise, let T o The result is empty; thus, the updated abstract syntax tree T = (R, T) is obtained. s T f T w ,T g T h T o ); Step 3.9: Determine whether the keyword LIMIT exists in S. If it exists, the parser P identifies the keyword LIMIT and all subsequent restriction fields as the LIMIT subtree T according to the written grammar file. l Otherwise, let T l Empty; thus obtaining the final updated abstract syntax tree T = (R, T s T f T w T g T h T o T l ); Step 4: Translate each subtree; Step 4.1: Initialize the collection Result = {} to store the translation results of the subtree; Step 4.2: Traverse the abstract syntax tree T of the SQL statement S. When traversing to the SELECT subtree T... s At that time, add a key K to the Result collection to map the translation results. s And determine T s Is it empty? If it is empty, then set key K. s If it is null; otherwise, T will be null. s The translation result R s Assigned to key K s ; Step 4.3: When traversing to the FROM subtree T f Add a key K to the Result collection to map the translation results. f1 and key K f2 And determine T f Is it empty? If T f If it is empty, then let key K be empty. f1 and key K f2 All are null values; otherwise, translate T directly. f All nodes and the translation result R f1 As key K f1 The value; at the same time, translate T separately. f The translation results for the table nodes and join condition nodes in the table are R, respectively. c and R t Store both translation results together in array R. f2 In, and R f2 As key K f2 The value; Step 4.4: Following the process in Step 4.2, process the WHERE subtree T respectively. w GROUPBY subtree T g HAVING subtree T h , ORDERBY subtree T o LIMIT subtree T l Processing is performed to obtain the translation results R of the corresponding subtrees. w ,R h ,R g R o R l The corresponding value is assigned to key K. w ,K h ,K g K o K l This results in the final set of subtree translation results, Result = {K}. s :R s K f1 :R f1 K f2 :R f2 K w :R w ,K h :R h ,K g :R g K o :R o K l :R l }; Step 5: Combine the translation results of the subtrees from bottom to top into a set named Result; Step 5.1: Determine the complexity of the SQL statement S. If S only contains the keywords SELECT, FROM, and WHERE, then select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f1 If the result is not found in the subtree, proceed to step 5.2; otherwise, select the FROM subtree T from the subtree translation result set Result. f The translation result is Result[K] f2 ], and proceed to step 5.3; Step 5.2: Combine the translation results of the subtrees in Result in the order of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDERBY, LIMIT to obtain the translation result Trans; Step 5.3: First, combine the main body of the SQL statement s according to the keywords FROM, SELECT, ORDERBY, LIMIT; then, combine the conditional statements in the SQL statement s according to the keywords WHERE, GROUPBY, HAVING, thus obtaining the translation result Trans.

2. The SQL statement translation method based on a high-level abstract syntax tree according to claim 1, characterized in that, Step 5.2 includes the following steps: Step 5.2.1: Initialize the translation result Trans to an empty string; define the following conjunctions to connect the translation results of subtrees: "Then" as Then, "Query and return" as Sr, "Query and return again" as Asr, "Finally, for the records after the query" as Fo, and "," as Comma; Initialize the flag pointer i, which indicates whether the conjunction is used or not, to 0; Step 5.2.2: Traverse the subtree translation result set Result. If FROM subtree T f Translation result Result[K f1 If the result is not empty, then add Result[K] to Trans in sequence. f1 If the value of i is 1, then the value of i+1 is assigned to i, and then step 5.2.3 is executed; otherwise, step 5.2.3 is executed directly. Step 5.2.3, if WHERE subtree T w Translation result Result[K w If the result is not empty, then add Result[K] to Trans in sequence. w If i is a comma and i+1 is assigned to i, then proceed to step 5.2.4; otherwise, proceed directly to step 5.2.

4. Step 5.2.4, if the Grouppy subtree T g Translation result Result[K g If ] is not empty and i = 2, then add Then and Result[K] to Trans in sequence. g After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.5; If Result[K] g If ] is not empty and i = 1, then add Result[K] to Trans. g ], Comma, and assign i+1 to i, then execute step 5.2.5; If Result[K] g If ] is empty, proceed directly to step 5.2.5; Step 5.2.5, if the HAVING subtree T h Translation result Result[K h If the result is not empty, then add Result[K] to Trans in sequence. h If i is a comma, assign i+1 to i and then proceed to step 5.2.6; otherwise, proceed directly to step 5.2.

6. Step 5.2.6: If SELECT subtree T s Translation result Result[K s If ] is not empty, and i = 2, 3, or 4, then add Asr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7; If Result[K] s If ] is not empty and i = 1, then add Sr and Result[K] to Trans in sequence. s After setting i and Comma, and assigning i+1 to i, proceed to step 5.2.7; If Result[K] s If ] is empty, proceed directly to step 5.2.7; Step 5.2.7, if ORDERBY subtree T o Translation result Result[K o If ] is not empty, then add Sr and Result[K] to Trans in sequence. o If i is a comma and i+1 is assigned to i, then proceed to step 5.2.8; otherwise, proceed directly to step 5.2.

8. Step 5.2.8: If LIMIT subtree T l Translation result Result[K l If the result is not empty, then add Result[K] to Trans in sequence. l If the expression is incomplete or contains errors, it indicates that the final translation result Trans is obtained directly; otherwise, it indicates that the final translation result Trans is obtained directly.

3. The SQL statement translation method based on a high-level abstract syntax tree according to claim 1, characterized in that, Step 5.3 includes the following steps: Step 5.3.1: Initialize the translation result Trans to an empty string; Define the following related terms to connect the translation results of subtrees: "Last" for "query records that meet the following conditions" for "Fc" for "select from them" for "Se" for "results" for "Order" for "," "" for "Comma" for "(" for "Lbracket")" for "Rbracket" for "join condition" for "Jc" for "take from them" for "Gr". Initialize the index variable i used to indicate the condition to 1; Step 5.3.2: Traverse Result, if FROM subtree T f Translation result Result[K f2 If the result is not empty, then add Result[K] to Trans in sequence. f2 The first value in the array (the translation result of the table node) Result[K f2 If [0] or Fc, proceed to step 5.3.3; otherwise, proceed directly to step 5.3.

3. Step 5.3.3, if SELECT subtree T s Translation result Result[K s If the value is not empty, then add Comma, Se, and Result[K] to Trans in sequence. s If the condition is met, proceed to step 5.3.4; otherwise, proceed directly to step 5.3.

4. Step 5.3.4: If ORDERBY subtree T o Translation result Result[K o If the value is not empty, then add Comma, Order, and Result[K] to Trans in sequence. o If the condition is met, proceed to step 5.3.5; otherwise, proceed directly to step 5.3.

5. Step 5.3.5, if LIMIT subtree T l Translation result Result[K l If the value is not empty, then add Comma, Last, and Result[K] to Trans in sequence. l If the condition is met, proceed to step 5.3.6; otherwise, proceed directly to step 5.3.

6. Step 5.3.6: Add Comma, Lbracket, i, Rbracket, Jc, and Result[K] to Trans in sequence. f2 The second value in the array (the translation result of the condition node) Result[K f2 [1], and after assigning i+1 to i, execute step 5.3.7; otherwise, directly execute step 5.3.7; Step 5.3.7, if WHERE subtree T w Translation result Result[K w If the value is not empty, then add Comma, Lbracket, i, Rbracket, and Result[K] to Trans in sequence. w If i is positive, assign i+1 to i and then execute step 5.3.8; otherwise, execute step 5.3.8 directly. Step 5.3.8, if the Grouppy subtree T g Translation result Result[K g ] is not empty, and the HAVING subtree T h Translation result Result[K h If not empty, then concatenate Comma, Lbracket, i, Rbracket, Result[K] in sequence. g After that, assign the concatenation result to the new variable Temp, and add Temp, Lbracket, and Result[K] to Trans in sequence. h ] and Rbracket; thus obtaining the final translation result Trans; If Result[K] g ] is not empty but Result[K h If ] is empty, then only Temp is added to Trans, thus obtaining the final translation result Trans.

4. An electronic device, comprising a memory and a processor, characterized in that, The memory is used to store a program that supports the processor in executing the SQL statement translation method of any one of claims 1-3, and the processor is configured to execute the program stored in the memory.

5. A computer-readable storage medium storing a computer program thereon, characterized in that, The computer program, when run by a processor, performs the steps of any of the SQL statement translation methods described in claims 1-3.

Images