CN106708954B - Internal and external connection conversion method and conversion device for non-standard SQL statement - Google Patents
Internal and external connection conversion method and conversion device for non-standard SQL statement Download PDFInfo
- Publication number
- CN106708954B CN106708954B CN201611071273.0A CN201611071273A CN106708954B CN 106708954 B CN106708954 B CN 106708954B CN 201611071273 A CN201611071273 A CN 201611071273A CN 106708954 B CN106708954 B CN 106708954B
- Authority
- CN
- China
- Prior art keywords
- connection state
- state tree
- node
- nodes
- connection
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2452—Query translation
- G06F16/24526—Internal representations for queries
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a conversion method and a conversion device for internal and external connection of a non-standard SQL statement, wherein the conversion method comprises the following steps: searching all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement; generating a connection state tree node for each second type expression; merging all the nodes of the connection state tree to obtain a connection state tree; linking each said first type expression into attributes of said join state tree nodes on said join state tree; and modifying the syntax parse tree according to the connection state tree linked with the first type expression so as to convert the internal connection of the non-standard SQL statement into the external connection. By the technical scheme of the invention, the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the internal connection, so that the correctness of the query statement after the database is transplanted is ensured.
Description
Technical Field
The invention relates to the technical field of databases, in particular to an internal and external connection conversion method and device for a non-standard SQL statement.
Background
In the database migration process, some users write SQL (Structured Query Language) statements using a non-ANSI (American National standards institute) standard syntax, and hereinafter, the SQL statements written using the non-ANSI standard syntax are referred to as non-standard SQL statements. Since the user cannot modify and compile the non-standard SQL statement again in the long term, the database is required to be compatible with the grammar of the transplanted database later.
A special outerjoin syntax is supported in the Oracle database, for example: select from a, b where c1 ═ b.c1 (+); where (+) is a newly added key of Oracle, c1 is a column of table, b is a table modified by (+) a is a table not modified by (+) a.c1 ═ b.c1(+) is used to denote a table left join b table, and the linking conditions are a.c1 ═ b.c1, i.e. the rows where all c1 columns of table a are equal to c1 columns of table b are found in an outer linking manner. If the user uses the above non-standard SQL statement to perform an external connection query on the table, when the database needs to be migrated, the new database needs to be compliant with such non-standard syntax of Oracle because the non-standard SQL statement in the application cannot be modified. If the compatibility modification is not carried out, the original application program cannot operate correctly after the database is transplanted. If the (+) key is omitted from the above example, the non-standard SQL statement would resolve to an inlier according to the SQL standard syntax. In Oracle, however, this non-standard SQL statement would be parsed into an outerjoin.
Therefore, how to convert the internal connection of the non-standard SQL statement into the external connection equivalent to the internal connection, thereby ensuring the correctness of the query statement after the database migration becomes an urgent technical problem to be solved.
Disclosure of Invention
The present invention is directed to solving at least one of the problems of the prior art or the related art.
Therefore, the invention aims to provide a conversion method for internal and external connection of non-standard SQL sentences.
The invention also aims to provide an internal and external connection conversion device of the non-standard SQL statement.
To achieve at least one of the above objects, according to an embodiment of the first aspect of the present invention, there is provided an internal and external connection transformation method for a non-standard SQL statement, including: searching all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, wherein the first type expressions only comprise a table modified by a preset keyword, and the second type expressions only comprise a table modified by the preset keyword and a table not modified by the preset keyword; generating a connection state tree node for each second type expression; merging all the nodes of the connection state tree to obtain a connection state tree; linking each said first type expression into attributes of said join state tree nodes on said join state tree; modifying the syntax parse tree to convert Inner connections (Inner joins) of the non-standard SQL statement to Outer connections (Outer joins) according to a connection state tree to which the first type of expression is linked.
In the technical scheme, the connection state tree capable of describing the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement is generated, so that the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the internal connection according to the connection state tree, and the correctness of the query statement after the database is transplanted is ensured. In addition, the method and the system can not influence the original grammar parsing process and can not influence the optimizer, and when the method and the system are implemented, excessive modification can not be carried out on the database execution process, so that the risk of new function development is reduced.
In the foregoing technical solution, preferably, the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
In the technical scheme, all the nodes of the connection state tree are merged according to the attributes of the nodes of the connection state tree, so that the connection state tree is obtained, and the connection state tree describes the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement.
In any one of the above technical solutions, preferably, the step of linking each expression of the first type to the attribute of the node of the connection state tree on the connection state tree includes: searching the connection state tree node corresponding to each first type expression in the connection state tree, wherein the table of the first type expression modified by the preset keyword is the same as the table of the connection state tree node corresponding to the first type expression modified by the preset keyword; linking each said first type expression into the connection condition of its corresponding said connection state tree node.
In the technical scheme, each first-type expression is linked to the connection condition between the table modified by the preset key words and the table not modified by the preset key words of the corresponding connection state tree node, so that redistribution of the first-type expressions on the connection state tree is realized.
In any one of the above technical solutions, preferably, the step of merging all the nodes of the connection state tree to obtain the connection state tree includes: when a first connection state tree node in all the connection state tree nodes is merged with a second connection state tree node on the right side of the first connection state tree node, taking the first connection state tree node as a target node and the second connection state tree node as a participating node, and judging whether the target node and the participating node meet a left merging condition or a right merging condition; if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node; if the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition; if the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node; and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
In the technical scheme, all the nodes of the connection state tree are merged according to the rules to obtain a complete connection state tree, so that the connection state tree describes the external connection relation among a plurality of tables in the syntax parsing tree, and the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the external connection according to the connection state tree.
In any of the above technical solutions, preferably, the transformation method further comprises: and if other types of expressions except the first type of expression and the second type of expression are found in the filtering condition tree, and the other types of expressions comprise tables modified by the preset keywords, prompting a user that an error occurs.
In the technical scheme, if other types of expressions comprising the table modified by the preset keywords are found, the user is prompted to have errors, so that the user can take corresponding measures against the errors in time.
According to an embodiment of the second aspect of the present invention, there is provided an internal and external connection translation apparatus for a non-standard SQL statement, including: the query unit is used for searching all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, wherein the first type expressions only comprise a table modified by a preset keyword, and the second type expressions only comprise a table modified by the preset keyword and a table not modified by the preset keyword; a generating unit, configured to generate a connection state tree node for each second type expression; the merging unit is used for merging all the nodes of the connection state tree to obtain a connection state tree; a linking unit for linking each of the first type expressions into attributes of the connection state tree nodes on the connection state tree; and the conversion unit is used for modifying the syntax parse tree according to the connection state tree linked with the first type expression so as to convert the internal connection of the non-standard SQL statement into the external connection.
In the technical scheme, the connection state tree capable of describing the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement is generated, so that the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the internal connection according to the connection state tree, and the correctness of the query statement after the database is transplanted is ensured. In addition, the method and the system can not influence the original grammar parsing process and can not influence the optimizer, and when the method and the system are implemented, excessive modification can not be carried out on the database execution process, so that the risk of new function development is reduced.
In the foregoing technical solution, preferably, the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
In the technical scheme, all the nodes of the connection state tree are merged according to the attributes of the nodes of the connection state tree, so that the connection state tree is obtained, and the connection state tree describes the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement.
In any one of the foregoing technical solutions, preferably, the linking unit is specifically configured to find out the connection state tree node corresponding to each first-type expression in the connection state tree, where a table of the first-type expression modified by the preset keyword is the same as a table of the connection state tree node corresponding to the first-type expression modified by the preset keyword, and link each first-type expression to the connection condition of the connection state tree node corresponding to the first-type expression.
In the technical scheme, each first-type expression is linked to the connection condition between the table modified by the preset key words and the table not modified by the preset key words of the corresponding connection state tree node, so that redistribution of the first-type expressions on the connection state tree is realized.
In any one of the foregoing technical solutions, preferably, the merging unit is specifically configured to, when a first connection state tree node in all the connection state tree nodes is merged with a second connection state tree node on the right side of the first connection state tree node, regard the first connection state tree node as a target node, regard the second connection state tree node as a participating node, and determine whether the target node and the participating node satisfy a left merging condition or a right merging condition; if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node; if the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition; if the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node; and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
In the technical scheme, all the nodes of the connection state tree are merged according to the rules to obtain a complete connection state tree, so that the connection state tree describes the external connection relation among a plurality of tables in the syntax parsing tree, and the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the external connection according to the connection state tree.
In any one of the above technical solutions, preferably, the conversion apparatus further includes: and the prompting unit is used for prompting a user that an error occurs if the searching unit searches for other types of expressions except the first type of expression and the second type of expression in the filtering condition tree, and the other types of expressions comprise tables modified by the preset keywords.
In the technical scheme, if other types of expressions comprising the table modified by the preset keywords are found, the user is prompted to have errors, so that the user can take corresponding measures against the errors in time.
By the technical scheme, the internal connection of the non-standard SQL statement can be converted into the external connection equivalent to the internal connection, so that the correctness of the query statement after the database is transplanted is ensured.
Drawings
FIG. 1 is a flow diagram illustrating a transformation method for internal and external concatenation of non-standard SQL statements according to an embodiment of the invention;
FIG. 2 illustrates a schematic diagram of properties of a connection state tree node according to an embodiment of the invention;
FIGS. 3A-3C are diagrams illustrating a merge rule connecting nodes of a state tree, according to an embodiment of the invention;
FIGS. 4A-4D illustrate schematic diagrams of a conversion of an inner connection to an outer connection, according to embodiments of the invention;
FIG. 5 shows a schematic structural diagram of an internal and external connected translation device for a non-standard SQL statement according to an embodiment of the invention.
Detailed Description
So that the manner in which the above recited objects, features and advantages of the present invention can be understood in detail, a more particular description of the invention, briefly summarized above, may be had by reference to the embodiments thereof which are illustrated in the appended drawings. It should be noted that the embodiments and features of the embodiments of the present application may be combined with each other without conflict.
In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present invention, however, the present invention may be practiced in other ways than those specifically described herein, and therefore the scope of the present invention is not limited by the specific embodiments disclosed below.
FIG. 1 is a flow diagram illustrating a transformation method for internal and external concatenation of a non-standard SQL statement according to an embodiment of the invention.
As shown in fig. 1, the method for transforming internal and external connections of a non-standard SQL statement according to an embodiment of the present invention includes:
102, finding out all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, wherein the first type expressions only comprise a table modified by a preset keyword, and the second type expressions only comprise a table modified by the preset keyword and a table not modified by the preset keyword.
For example, the preset key is (+) and the table modified by (+) is marked by Parser (Parser), and the expression where the table modified by (+) is located is searched in the filtering condition tree in the syntax parsing tree of the non-standard SQL statement according to the mark. If the expression only includes a (+) modified table, the expression is a first type expression; if the expression includes only one (+) modified table and one (+) unmodified table, the expression is a second type of expression.
And 104, generating a connection state tree node for each second type expression.
In one embodiment, the connection state tree nodes are generated according to the obtained tables of the second type expression which are modified by the preset keywords and the tables which are not modified by the preset keywords.
Preferably, the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
For example, if the preset keyword is (+) then the attribute of the connection state tree node is as shown in fig. 2, where the attribute of the connection state tree node includes: a (+) modified table, a (+) unmodified table, a connection condition between a (+) modified table and a (+) unmodified table, a left connection state tree node of a connection state tree node, a right connection state tree node of a connection state tree node, whether there is an empty connection between a left connection state tree node and a right connection state tree node.
And 106, combining all the nodes of the connection state tree to obtain the connection state tree.
Preferably, step 106 comprises: when a first connection state tree node in all the connection state tree nodes is merged with a second connection state tree node on the right side of the first connection state tree node, the first connection state tree node is used as a target node, the second connection state tree node is used as a participating node, and whether the target node and the participating node meet a left merging condition or a right merging condition is judged. Wherein the right merging condition is as follows: the table decorated by the preset key of the target node (i.e. the first connection state tree node) is the same as the table decorated by the non-preset key of the participating node (i.e. the second connection state tree node), and the left merging condition is as follows: the tables of the participating nodes (i.e., the second connectivity state tree nodes) that are not decorated by the preset key are the same as the tables of the target nodes (i.e., the first connectivity state tree nodes) that are not decorated by the preset key.
And if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node.
If the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition. Wherein the right merging condition is as follows: the table modified by the preset key of the modified target node (i.e. the second connection state tree node) is the same as the table of the modified participating node (i.e. the first connection state tree node) which is not modified by the preset key, and the left merging condition is as follows: the table of the modified participating node (i.e., the first connectivity state tree node) that is not modified by the preset key is the same as the table of the modified target node (i.e., the second connectivity state tree node) that is not modified by the preset key.
If the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node; and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
And merging all the nodes of the connection state tree according to the rules to obtain a complete connection state tree, so that the connection state tree describes the external connection relation among a plurality of tables in the syntax parse tree.
The above technical means will be further described below.
Firstly, two connection state tree nodes are selected from all connection state tree nodes, the left connection state tree node is a first connection state tree node, the right connection state tree node is a second connection state tree node, and the first connection state tree node and the second connection state tree are combined to obtain an intermediate connection state tree. And then taking the intermediate connection state tree as a first connection state tree node, taking a node from the connection state tree nodes which do not participate in merging as a second connection state tree node, merging the first connection state tree node and the second connection state tree node until all the connection state tree nodes are merged to obtain a complete connection state tree.
In addition, when the intermediate connection state tree is used as a first connection state tree node, and one node is taken out from the connection state tree nodes which do not participate in merging as a second connection state tree node, if the attribute of the root node of the intermediate connection state tree is null connection, after the first connection state tree node and the second connection state tree node are merged to obtain a new intermediate connection state tree, the left tree and the right tree of the root node of the new intermediate connection state tree are merged. And if the combination is successful and the root node of the new intermediate connection state tree is still in empty connection, continuing to combine the left tree and the right tree of the root node of the intermediate connection state tree until the attribute of the root node of the intermediate connection state tree is not in empty connection or the combination fails. And if the combination fails, the intermediate connection state tree is not modified, and the combination of the connection state tree is finished.
In addition to this, two intermediate connection state trees can also participate in the merging. The step of merging the two intermediate connection state trees specifically comprises:
and selecting a target node (the degree of the target node is less than 2) from the first intermediate connection state tree, selecting a root node as a participating node from the second intermediate connection state tree, and judging whether the target node and the participating node meet a left merging condition or a right merging condition.
And if the target node and the participating nodes meet the left merging condition or the right merging condition, merging the participating nodes to the target node.
If the target node and the participating nodes do not meet the left merging condition or the right merging condition, selecting a node with the next degree smaller than 2 from the first intermediate connection state tree as a target node, merging the target node and the participating nodes again, if merging is successful, completing the merging, if the merging fails, and all nodes with the degree smaller than 2 of the first connection state tree are failed to be merged with the participating nodes as the target node, selecting a node with the degree smaller than 2 from the second intermediate connection state tree as the target node, selecting a root node from the first intermediate connection state tree as the participating node, and repeating the merging process of the first intermediate connection state tree and the second intermediate connection state tree. And if the combination fails, performing null combination on the root node of the first intermediate connection state tree and the root node of the second intermediate connection state tree.
The left merge, right merge, and null merge between two connected state tree nodes are explained in detail below with a preset key (+) as a (+) key.
As shown in fig. 3A, the participating node is an ALB, the target node is a BLC, the participating node and the target node satisfy a right merge condition, the (+) modified table of the target node is the same as the non- (+) modified table of the participating node, and the participating node ALB is merged to the target node BLC, that is, a ═ B (+) and B ═ C (+), where (+) is a preset key in a non-standard SQL statement, a in the participating node is a non- (+) modified table, B is a (+) modified table, B in the target node is a non- (+) modified table, and C is a (+) modified table.
As shown in fig. 3B, the participating node is an ALB, the target node is an ALC, the participating node and the target node satisfy a left merge condition, the table of the participating node that is not (+) modified is the same as the table of the target node that is not (+) modified, and the participating node ALB is merged to the target node ALC, that is, a ═ B (+) and a ═ C (+), where (+) is a preset key in a non-standard SQL statement, a in the target node is a table that is not (+) modified, C is a table that is (+) modified, a in the participating node is a table that is not (+) modified, and B is a table that is (+) modified.
As shown in fig. 3C, the participating node is an ALB, the target node is a CLD, the participating node ALB and the target node CLD neither satisfy the left merge condition nor the right merge condition, the target node is changed to an ALB, the participating node is changed to a CLD, it is determined whether the target node ALB and the participating node CLD satisfy the left merge condition or the right merge condition, if the target node ALB and the participating node CLD neither satisfy the left merge condition nor the right merge condition, the target node ALB and the participating node CLD are null-merged, i.e., a ═ B (+) and C ═ D (+), where (+) is a preset key in a non-standard SQL statement, a in the ALB is a table that is not modified by (+) B is a table that is modified by (+) C, and D is a table that is not modified by (+) C in CLD.
After the generation of the connection state tree linked with the first type expression is finished, if the connection state tree still has empty connection, the empty connection indicates that the connection relation of the two connection state tree nodes is internal connection.
Preferably, step 108 comprises: searching the connection state tree node corresponding to each first type expression in the connection state tree, wherein the table of the first type expression modified by the preset keyword is the same as the table of the connection state tree node corresponding to the first type expression modified by the preset keyword; linking each said first type expression into said connection condition in its corresponding attribute of said connection state tree node.
And linking each first-type expression to a connection condition between the table modified by the preset key words and the table not modified by the preset key words of the corresponding connection state tree node, thereby realizing redistribution of the first-type expressions on the connection state tree.
For example, the table modified by the preset key in the first type expression is a, and each connected state tree node is traversed in the connected state tree by taking the a table as a reference until the connected state tree node meeting the following condition is found: the table modified by the preset keywords of the connection state tree node is the same as the table A modified by the preset keywords in the first type expression, and then the first type expression is linked to the connection condition between the table modified by the preset keywords and the table not modified by the preset keywords of the connection state tree node.
In the database to which an embodiment of the present scheme is applied, if two tables a and B are in an inter-connected relationship, the connection relationship between the two tables is as shown in fig. 4A. If two tables A and B are externally connected, the connection between the two tables is as shown in FIG. 4B, where NULL represents an empty node.
In the technical scheme, the connection state tree capable of describing the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement is generated, so that the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the internal connection according to the connection state tree, and the correctness of the query statement after the database is transplanted is ensured. In addition, the method and the system can not influence the original grammar parsing process and can not influence the optimizer, and when the method and the system are implemented, excessive modification can not be carried out on the database execution process, so that the risk of new function development is reduced.
FIG. 5 shows a schematic structural diagram of an internal and external connected translation device for a non-standard SQL statement according to an embodiment of the invention.
As shown in fig. 5, the internal and external connection translation apparatus 500 of the non-standard SQL statement according to an embodiment of the present invention includes: a search unit 502, a generation unit 504, a merging unit 506, a linking unit 508 and a translation unit 510.
The searching unit 502 is configured to search all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, where the first type expressions only include a table modified by a preset keyword, and the second type expressions only include a table modified by the preset keyword and a table not modified by the preset keyword.
A generating unit 504, configured to generate a connection state tree node for each of the second type expressions.
Preferably, the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
And according to the attributes of the connection state tree nodes, combining all the connection state tree nodes to obtain a connection state tree, wherein the connection state tree describes the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement.
A merging unit 506, configured to merge all the nodes of the connection state tree to obtain a connection state tree.
Preferably, the merging unit 506 is specifically configured to, when merging a first connection state tree node in all the connection state tree nodes and a second connection state tree node on the right side of the first connection state tree node, regard the first connection state tree node as a target node, regard the second connection state tree node as a participating node, and determine whether the target node and the participating node satisfy a left merging condition or a right merging condition; if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node; if the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition; if the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node; and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
And merging all the nodes of the connection state tree according to the rules to obtain a complete connection state tree, so that the connection state tree describes the external connection relation among a plurality of tables in the syntax parse tree, and the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the external connection according to the connection state tree.
A linking unit 508, configured to link each expression of the first type into an attribute of a node of the connection state tree on the connection state tree.
Preferably, the linking unit 508 is specifically configured to find out the connection state tree node corresponding to each first-type expression in the connection state tree, where a table of the first-type expression modified by the preset key is the same as a table of the connection state tree node corresponding to the first-type expression modified by the preset key, and link each first-type expression to the connection condition of the connection state tree node corresponding to the first-type expression.
And linking each first-type expression to a connection condition between the table modified by the preset key words and the table not modified by the preset key words of the corresponding connection state tree node, thereby realizing redistribution of the first-type expressions on the connection state tree.
A conversion unit 510, configured to modify the syntax parse tree according to the connection state tree linked with the first type expression, so as to convert the internal connection of the non-standard SQL statement into an external connection.
In the above technical solution, preferably, the internal and external connected conversion apparatus 500 of the non-standard SQL statement further includes: a prompting unit 512, configured to prompt a user that an error occurs if the searching unit 502 searches for an expression of another type in the filtering condition tree, except for the first type expression and the second type expression, and the expression of another type includes a table modified by the preset keyword.
If other types of expressions comprising the table modified by the preset keywords are found, prompting the user of an error to take corresponding measures against the error in time.
In the technical scheme, the connection state tree capable of describing the external connection relation between the tables in the syntax parsing tree of the non-standard SQL statement is generated, so that the internal connection of the non-standard SQL statement is converted into the external connection equivalent to the internal connection according to the connection state tree, and the correctness of the query statement after the database is transplanted is ensured. In addition, the method and the system can not influence the original grammar parsing process and can not influence the optimizer, and when the method and the system are implemented, excessive modification can not be carried out on the database execution process, so that the risk of new function development is reduced.
Because the principle of solving the problem of the internal and external connection transformation device of the non-standard SQL statement is similar to that of the internal and external connection transformation method of the non-standard SQL statement, the implementation of the transformation device can refer to the implementation of the transformation method, and repeated parts are not described again.
The technical scheme of the invention is explained in detail in the above with the help of the attached drawings, and the internal connection of the non-standard SQL statement can be converted into the external connection equivalent to the internal connection, so that the correctness of the query statement after the database is transplanted is ensured.
The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention, and various modifications and changes may be made by those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (8)
1. A transformation method for internal and external connection of a non-standard SQL statement is characterized by comprising the following steps:
searching all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, wherein the first type expressions only comprise a table modified by a preset keyword, and the second type expressions only comprise a table modified by the preset keyword and a table not modified by the preset keyword;
generating a connection state tree node for each second type expression;
merging all the nodes of the connection state tree to obtain a connection state tree;
linking each said first type expression into attributes of said join state tree nodes on said join state tree;
modifying the syntax parse tree according to the connection state tree linked with the first type expression so as to convert the internal connection of the non-standard SQL statement into the external connection;
the step of combining all the nodes of the connection state tree to obtain the connection state tree comprises the following steps:
when a first connection state tree node in all the connection state tree nodes is merged with a second connection state tree node on the right side of the first connection state tree node, taking the first connection state tree node as a target node and the second connection state tree node as a participating node, and judging whether the target node and the participating node meet a left merging condition or a right merging condition;
if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node;
if the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition;
if the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node;
and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
2. The method of transformation of internal and external joins of non-standard SQL statements according to claim 1,
the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
3. The method of translating internal and external connections of non-standard SQL statements according to claim 2, wherein the step of linking each expression of the first type into attributes of the nodes of the join state tree on the join state tree comprises:
searching the connection state tree node corresponding to each first type expression in the connection state tree, wherein the table of the first type expression modified by the preset keyword is the same as the table of the connection state tree node corresponding to the first type expression modified by the preset keyword;
linking each said first type expression into the connection condition of its corresponding said connection state tree node.
4. The transformation method for internal and external concatenation of non-standard SQL statements according to any of claims 1 to 3, characterized in that the transformation method further comprises:
and if other types of expressions except the first type of expression and the second type of expression are found in the filtering condition tree, and the other types of expressions comprise tables modified by the preset keywords, prompting a user that an error occurs.
5. An internal and external connection conversion device for a non-standard SQL statement, which is characterized by comprising:
the query unit is used for searching all first type expressions and all second type expressions in a filtering condition tree in a syntax parsing tree of a non-standard SQL statement, wherein the first type expressions only comprise a table modified by a preset keyword, and the second type expressions only comprise a table modified by the preset keyword and a table not modified by the preset keyword;
a generating unit, configured to generate a connection state tree node for each second type expression;
the merging unit is used for merging all the nodes of the connection state tree to obtain a connection state tree;
a linking unit for linking each of the first type expressions into attributes of the connection state tree nodes on the connection state tree;
the conversion unit is used for modifying the syntax parse tree according to the connection state tree linked with the first type expression so as to convert the internal connection of the non-standard SQL statement into the external connection;
the merging unit is specifically configured to,
when a first connection state tree node in all the connection state tree nodes is merged with a second connection state tree node on the right side of the first connection state tree node, taking the first connection state tree node as a target node and the second connection state tree node as a participating node, and judging whether the target node and the participating node meet a left merging condition or a right merging condition;
if the target node and the participating node meet the left merging condition or the right merging condition, merging the participating node to the target node;
if the target node and the participating nodes do not satisfy the left merging condition and the right merging condition, changing the participating nodes into first connection state tree nodes, changing the target nodes into second connection state tree nodes, and judging whether the changed target nodes and participating nodes satisfy the left merging condition or the right merging condition;
if the changed target node and the changed participating nodes meet the left merging condition or the right merging condition, merging the changed participating nodes to the changed target node;
and if the changed target node and the changed participating node do not meet the left merging condition and the right merging condition, performing null merging on the changed target node and the changed participating node.
6. The apparatus for translating internal and external connections of non-standard SQL statements according to claim 5,
the attributes of the connection state tree nodes include: the table modified by the preset keyword, the table not modified by the preset keyword, the connection condition between the table modified by the preset keyword and the table not modified by the preset keyword, the left connection state tree node of the connection state tree node, the right connection state tree node of the connection state tree node, and whether the left connection state tree node and the right connection state tree node are connected in an empty state.
7. The apparatus according to claim 6, wherein the link unit is specifically configured to,
finding out the connection state tree node corresponding to each first type expression in the connection state tree, wherein the table of the first type expression modified by the preset keyword is the same as the table of the connection state tree node corresponding to the first type expression modified by the preset keyword,
linking each said first type expression into the connection condition of its corresponding said connection state tree node.
8. The apparatus according to any of claims 5 to 7, wherein the apparatus further comprises:
and the prompting unit is used for prompting a user that an error occurs if the searching unit searches for other types of expressions except the first type of expression and the second type of expression in the filtering condition tree, and the other types of expressions comprise tables modified by the preset keywords.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611071273.0A CN106708954B (en) | 2016-11-29 | 2016-11-29 | Internal and external connection conversion method and conversion device for non-standard SQL statement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611071273.0A CN106708954B (en) | 2016-11-29 | 2016-11-29 | Internal and external connection conversion method and conversion device for non-standard SQL statement |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106708954A CN106708954A (en) | 2017-05-24 |
| CN106708954B true CN106708954B (en) | 2020-03-10 |
Family
ID=58935111
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201611071273.0A Active CN106708954B (en) | 2016-11-29 | 2016-11-29 | Internal and external connection conversion method and conversion device for non-standard SQL statement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106708954B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109241101B (en) * | 2018-08-31 | 2020-06-30 | 阿里巴巴集团控股有限公司 | Database query optimization method and device and computer equipment |
| CN109308300B (en) * | 2018-09-27 | 2021-11-12 | 上海达梦数据库有限公司 | Logic operation processing method and device, conversion plug-in and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101261602A (en) * | 2008-04-08 | 2008-09-10 | 杭州电子科技大学 | Program correctness verification method based on syntax tree |
| CN101561817A (en) * | 2009-06-02 | 2009-10-21 | 天津大学 | Conversion algorithm from XQuery to SQL query language and method for querying relational data |
| CN101593180A (en) * | 2008-05-30 | 2009-12-02 | 国际商业机器公司 | The SPARQL inquiry is changed into the method and apparatus of SQL query |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7171427B2 (en) * | 2002-04-26 | 2007-01-30 | Oracle International Corporation | Methods of navigating a cube that is implemented as a relational object |
| US7447686B2 (en) * | 2002-11-22 | 2008-11-04 | Sas Institute Inc. | Computer-implemented system and method for handling database statements |
-
2016
- 2016-11-29 CN CN201611071273.0A patent/CN106708954B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101261602A (en) * | 2008-04-08 | 2008-09-10 | 杭州电子科技大学 | Program correctness verification method based on syntax tree |
| CN101593180A (en) * | 2008-05-30 | 2009-12-02 | 国际商业机器公司 | The SPARQL inquiry is changed into the method and apparatus of SQL query |
| CN101561817A (en) * | 2009-06-02 | 2009-10-21 | 天津大学 | Conversion algorithm from XQuery to SQL query language and method for querying relational data |
Non-Patent Citations (1)
| Title |
|---|
| Oracle外连接中对非连接条件使用（+）需要注意的地方;iteye_12668;《https://blog.csdn.net/iteye_12668/article/details/82573771》;20140324;1 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106708954A (en) | 2017-05-24 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10719662B2 (en) | Knowledge map-based question-answer method, device, and storage medium | |
| CN104199831B (en) | Information processing method and device | |
| CN107491561B (en) | Ontology-based urban traffic heterogeneous data integration system and method | |
| CN104965735B (en) | Device for generating upgrading SQL scripts | |
| CN106934062A (en) | A kind of realization method and system of inquiry elasticsearch | |
| CN107491476B (en) | Data model conversion and query analysis method suitable for various big data management systems | |
| CN109522008B (en) | Intelligent contract construction method for block chain | |
| CN114756569B (en) | Multi-layer parsing method for structured query statement, computer equipment and storage medium | |
| CN106708954B (en) | Internal and external connection conversion method and conversion device for non-standard SQL statement | |
| CN112732752A (en) | Query statement optimization method, device, equipment and storage medium | |
| US8495055B2 (en) | Method and computer program for evaluating database queries involving relational and hierarchical data | |
| CN113467785A (en) | SQL translation method and system for mimicry database | |
| CN115544062A (en) | Apache call data blood relationship and influence analysis based method and system | |
| CN115292350A (en) | SQL statement processing method and device | |
| CN113326286A (en) | Semantic analysis method supporting dialect SQL blood margin analysis | |
| US8001110B2 (en) | Apparatus, method, and computer program product for processing databases | |
| CN108509187B (en) | Method and system for automatically generating MIB function code of software platform | |
| CN116610697A (en) | Query method, storage medium and device for database query statement | |
| CN115563148A (en) | Database query method and device | |
| Leeka et al. | RQ-RDF-3X: going beyond triplestores | |
| CN110147396A (en) | A kind of mapping relations generation method and device | |
| CN113792067A (en) | System and method for automatically generating SQL (structured query language) based on recursive algorithm | |
| CN109828972B (en) | Data integration method based on directed graph structure | |
| CN116089476B (en) | Data query method and device and electronic equipment | |
| CN111159185A (en) | Hive index method based on conditional push-down elastic search |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20220418 Address after: Room 403, 4th floor, building 23, East District, yard 10, Xibeiwang East Road, Haidian District, Beijing 100089 Patentee after: BEIJING VSETTAN DATA TECHNOLOGY CO.,LTD. Address before: 100192 South Zone 1, floor 11, block a, No. 8 Xueqing Road (Science and technology wealth center), Haidian District, Beijing Patentee before: BEIJING VSETTAN DATA TECHNOLOGY CO.,LTD. Patentee before: Huasheng Xintai Information Industry Development Co., Ltd |
|
| TR01 | Transfer of patent right |