A method for analyzing an SQL statement

Abstract

This application relates to the field of electronic digital data processing technology, and in particular to a method for analyzing and processing SQL statements. The method includes the following steps: S100, obtaining the SQL statement A = {A1, A2, ..., A...} input by the user. n A N}, A n S200: Iterate through A, processing the nth SQL statement entered by the user, where n ranges from 1 to N, and N is the number of SQL statements entered by the user; n After analysis, we obtain A. n This invention identifies the relationships between columns and table names that have relationships, as well as the relationships between table names themselves. It displays triples constructed based on these relationships on the user interface. This allows users to quickly understand the relationships between tables, improving the user experience.

Description

Technical Field

[0001] This invention relates to the field of electronic digital data processing technology, and in particular to a method for analyzing and processing SQL statements. Background Technology

[0002] In some application scenarios, users input a large number of SQL statements, sometimes reaching tens of thousands. These scenarios involve a large number of tables, and the relationships between these tables are difficult to understand. This poses significant challenges for users in modifying tables and tracing their origins (i.e., users are unclear about which related tables need to be modified to change certain tables, and it's difficult to trace which tables' data were used to construct certain tables), resulting in a poor user experience. Improving the user experience by enabling users to quickly understand the relationships between tables is a pressing issue that needs to be addressed. Summary of the Invention

[0003] The purpose of this invention is to provide a method for analyzing and processing SQL statements, so as to display the relationship between tables in the SQL statement entered by the user on the user interface, enabling the user to quickly obtain the relationship between the tables and improve the user experience.

[0004] According to the present invention, a method for analyzing and processing SQL statements is provided, comprising the following steps:

[0005] S100, Obtain the SQL statement A = {A1, A2, ..., A...} input by the user. n A N}, A n This refers to the nth SQL statement entered by the user, where n ranges from 1 to N, and N is the number of SQL statements entered by the user.

[0006] S200, iterate through A, iterate through A, for A n After analysis, we obtain A. n The system contains column names and table names that have relationships, as well as table names and table names that have relationships, and displays triples constructed based on the column names and table names that have relationships on the user interface.

[0007] Compared with the prior art, the present invention has significant advantages. Through the above technical solution, the SQL statement analysis and processing method provided by the present invention achieves considerable technical progress and practicality, and has broad industrial application value. It has at least the following advantages:

[0008] This invention obtains the column names and table names that have relationships in an SQL statement, and constructs a triplet using the column names and table names as the two entities of the triplet. This invention also obtains the table names and table names that have relationships in an SQL statement, and constructs a triplet using the table names and table names as the two entities of the triplet. By displaying the above triplets on the user interface, this invention shows the user the relevant columns included in the tables in the user-input SQL statement and the relationships between tables, enabling the user to quickly understand how the tables are constructed and the relationships between tables. This facilitates user modification of tables and tracing the origin of tables, improving the user experience. Attached Figure Description

[0009] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0010] Figure 1 A flowchart illustrating the SQL statement analysis and processing method provided in this embodiment of the invention. Detailed Implementation

[0011] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0012] According to the present invention, a method for analyzing and processing SQL statements is provided, comprising the following steps:

[0013] S100, Obtain the SQL statement input by the user A = {A1, A2, ..., A...} n A N}, A n This refers to the nth SQL statement entered by the user, where n ranges from 1 to N, and N is the number of SQL statements entered by the user.

[0014] Those skilled in the art will understand that any SQL statement segmentation method in the prior art falls within the protection scope of this invention. For example, some existing database systems require the use of a semicolon at the end of each SQL statement. As one method of SQL statement segmentation in the prior art, the semicolon can be used as a marker for the end of an SQL statement, that is, the SQL statements are segmented by the semicolons in the SQL statements entered by the user.

[0015] S200, traversing A, obtaining A n n The column name and the table name having the relationship and the table name and the table name having the relationship are displayed on the user interface, and the triples constructed according to the column name and the table name having the relationship and the triples constructed according to the table name and the table name having the relationship are displayed on the user interface.

[0016] As a preferred embodiment, when the triples constructed according to the column name and the table name having the relationship and the triples constructed according to the table name and the table name having the relationship are displayed on the user interface, the entity corresponding to the column name in the triples is represented by a first preset color, and the entity corresponding to the table name in the triples is represented by a second preset color, the first preset color being different from the second preset color. In this way, the user can distinguish the entity corresponding to the column name and the entity corresponding to the table name by color, facilitating quick search of the column name and the table name. Optionally, the first preset color is black, and the second preset color is red.

[0017] As another preferred embodiment, when the triples constructed according to the column name and the table name having the relationship and the triples constructed according to the table name and the table name having the relationship are displayed on the user interface, the entity corresponding to the column name in the triples is represented by a first preset shape, and the entity corresponding to the table name in the triples is represented by a second preset shape, the first preset shape being different from the second preset shape. In this way, the user can distinguish the entity corresponding to the column name and the entity corresponding to the table name by shape, facilitating quick search of the column name and the table name. Optionally, the first preset shape is a rectangle, and the second preset shape is an ellipse.

[0018] According to the application, the SQL statements can be data definition class SQL statements, data query class SQL statements, data manipulation class SQL statements, transaction control class SQL statements and data control class SQL statements. The data definition class SQL statements are mainly used for defining logical structures, including defining tables, views and indexes. For example, the SQL statements for defining tables include: create table . The data query class SQL statements are used for querying various data objects in the database. For example, simple data query SQL statements include: select column name from table name. The data manipulation class SQL statements are used for changing data in the database, including inserting, deleting and modifying. The transaction control class SQL statements are used for rapid prototyping, script programming, GUI and testing, etc. The data control class SQL statements are used for granting or recovering certain privileges for accessing the database, controlling the time and effect of data manipulation transactions, and monitoring data, etc.

[0019] According to the application, by traversing A n ​The type of the SQL statement can be obtained by parsing. It should be understood that different types of SQL statements include different specific strings, and the type of the SQL statement can be parsed by matching whether the words in the SQL statement are specific strings. As an embodiment of a method for determining the type of the SQL statement, the SQL statement is subjected to word segmentation, and each word obtained is matched with a preset string. If the matching result is that the SQL statement only includes a string corresponding to a type of SQL statement, it is determined that the SQL statement is the type of SQL statement corresponding to the included string. If the matching result is that the SQL statement includes strings corresponding to different types of SQL statements, it is determined that the SQL statement is a multi-type SQL statement. The preset strings in the present application include create, select, insert, delete, drop, and alter, and the preset strings are used to distinguish the types of the SQL statements. Therefore, the preferred preset strings are words unique to each type of SQL statement. For example, when the SQL statement only includes a string corresponding to a data query type, it is determined that the SQL statement is a data query type SQL statement. When the SQL statement only includes a string corresponding to a data definition type, it is determined that the SQL statement is a data definition type SQL statement. When the SQL statement includes a string corresponding to a data manipulation type and a string corresponding to a data query type, it is determined that the SQL statement is a dual-type SQL statement. It should be understood that the dual-type refers to a data manipulation type and a data query type.

[0020] As an embodiment, if the SQL statement is a multi-type SQL statement, the SQL statement can be split by using an existing SQL syntax parsing method to obtain clauses of the SQL statement. Then, the table names and column names existing in each clause are parsed, and corresponding triples are constructed according to the table names and column names corresponding to each clause. Then, triples between the column names in each clause and the temporary table names obtained after executing the corresponding clauses are constructed. Finally, triples between the table names obtained after executing the SQL statement and the temporary table names obtained after executing each clause are constructed.

[0021] Optionally, Apache Calcite is used to parse A n , and the column names and table names having a relationship and the table names having a relationship in the SQL statement can be obtained. Those skilled in the art know that any SQL parsing method in the prior art falls within the protection scope of the present application. According to the present application, if the table corresponding to a table name in A n is constituted by the column corresponding to a column name in A n , it is determined that the column name in A n has a relationship with the table name in A n . If the table corresponding to a first table name in A n is constituted by the table corresponding to a second table name in A nIf the table corresponding to the second table name exists, it is determined that A n If the table corresponding to the first table name exists in A n If the relationship exists between the second table name.

[0022] According to the present application, displaying on the user interface the triples constructed according to the column names and the table names according to the existence relationship and the triples constructed according to the table names and the table names according to the existence relationship includes:

[0023] S210, if A n is a data query SQL statement, displaying on the user interface A n corresponding first triples T n ={T n,1 , T n,2 , …, T n,q , …, T n,Q}, and entering S220; T n,q is the first triple constructed according to the qth query block in A n , q ranges from 1 to Q, Q is the number of query blocks in A n ; T n,q =(t 1 n,q , t 2 n,q , …, t m n,q …, t M n,q ), t m n,q is the first triple constructed according to the mth column name corresponding to the qth query block in A n , m ranges from 1 to M, M is the number of column names corresponding to the qth query block in A n ; t m n,q =(b n,q , r n,q , c m n,q ), b n,q is the entity corresponding to the table name corresponding to the qth query block in A n , c m n,q is the entity corresponding to the mth column name corresponding to the qth query block in A n , and r n,q is the relationship between b n,q and c m n,q .

[0024] According to the present application, r n,q is used to represent b n,q and c m n,qbetween the ownership relationship. Optionally, r n,q is the ownership.

[0025] It should be noted that in the SQL statement, a select-from-where statement is called a query block. The column name corresponding to the qth query block in the present application is the column name between select and from in the qth query block, and M is the number of column names between select and from in the qth query block.

[0026] S220, if Q≥2, then displaying A n corresponding second triple D n and third triple G n ; D n ={D n,1 , D n,2 , …, D n,q , …, D n,Q}, D n,q is the second triple constructed according to the qth query block in A n ; D n,q =(d 1 n,q , dt 2 n,q , …, d m n,q …, d M n,q ), d m n,q is the second triple constructed according to the mth column name corresponding to the qth query block in A n ; d m n,q =(e n,q , f n,q , c m n,q ), e n,q is the entity corresponding to the table name of the temporary query result table obtained by executing the qth query block in A n , f n,q is the relationship between e n,q and c m n,q ; G n ={G n,1 , G n,2 , …, G n,q , …, G n,Q}, G n,q is the third triple constructed according to the qth query block in A n ; G n,q =(e n,q , h n,q , k n), k n To execute A n The table name of the target query result table corresponds to an entity, h n,q For e n,q The relationship between k n .

[0027] According to the present application, f n,q For characterizing e n,q The ownership relationship between c m n,q , f n,q is the ownership; h n,q For characterizing e n,q The association relationship between k n , h n,q is the association.

[0028] According to the present application, if Q = 1, A n The corresponding fourth triple L n = {L n,1 , L n,2 , …, L n,i , …, L n,S}, L n,i is the i-th fourth triple constructed according to A n , i ranges from 1 to S, S is the number of column names corresponding to A n ; L n,i = (c' n,i , h' n,i , k n ), c' n,i is the entity corresponding to the i-th column name of A n , h' n,i is the relationship between c' n,i and k n .

[0029] According to the present application, h' n,i For characterizing the ownership relationship between c' n,i and k n , h' n,i is the ownership.

[0030] As a first embodiment, A n is select name, class from student where teacher_id = (select id from teacher where course >= 90) as table 4. Analyzing A n , it can be known that A nIt is a data query type SQL statement, Q = 2, that is, A n It includes 2 query blocks (1 select - from - where is 1 query block). The first query block corresponds to select name,class from student, and the second query block corresponds to select id from teacher. For the first query block, the column names between select and from include name and class, and its corresponding table name is student. Then the first triples constructed are (name, belongs to, student) and (class, belongs to, student), the second triples constructed are (name, belongs to, temporary table1) and (class, belongs to, temporary table 1), and the third triples constructed are (temporary table 1, associated with, table 4). It should be understood that temporary table 1 is the temporary query result table obtained by executing the first query block, and table 4 is the table obtained by executing A n For the second query block, the column name between select and from includes id, and its corresponding table name is teacher. Then the first triples constructed are (id, belongs to, teacher), the second triples constructed are (id, belongs to, temporary table 2), and the third triples constructed are (temporary table 2, associated with, table 4). It should be understood that temporary table 2 is the temporary query result table obtained by executing the second query block.

[0031] As a second embodiment, A n is select name from student where class in ‘Class One’ and english = 100 as table 5. Analyzing A n It can be seen that A n is a data query type SQL statement, Q = 1, that is, A n It includes 1 query block, and this query block corresponds to select name from student. For this query block, the column name between select and from includes name, and its corresponding table name is student. Then the first triples constructed are (name, belongs to, student), and the fourth triples constructed are (name, belongs to, table 5).

[0032] In order to solve the problem that the user interface cannot display all the triples due to the large number of SQL statements input by the user, preferably, the application displays the triples constructed according to the column names and table names of the existing relationship and the triples constructed according to the table names and table names of the existing relationship on the user interface, and the triples constructed according to the column names and table names of the existing relationship and the triples constructed according to the table names and table names of the existing relationship include:

[0033] If N>N0, the user interface is divided into N sub-regions; N0 is a preset threshold of the number of SQL statements. Optionally, the area size of each sub-region in the N sub-regions is the same.

[0034] The entity set J1 corresponding to A1 is displayed on the first sub-region of the user interface, J1={J 1,1 ,J 1,2 ,…,J 1,R1}, J 1,j1 is the j1th entity corresponding to A1, and the value range of j1 is 1 to R1, and R1 is the number of entities corresponding to A1; the entities corresponding to A1 include the entities corresponding to the column names and the entities corresponding to the table names in A1; during the process of displaying the entity set J1 corresponding to A1 on the first sub-region of the user interface, the area size of the shape used to represent each entity corresponding to A1 is inversely proportional to R1.

[0035] According to the application, the larger R1 is, the smaller the area size of the shape used to represent each entity corresponding to A1 is, so as to display all the entities corresponding to A1 in the first sub-region.

[0036] The column names and the table names having the existing relationship in A1 are connected through a first connecting line, and the table names having the existing relationship in A1 are connected through a second connecting line; the second connecting line is the same as or different from the first connecting line.

[0037] The entity set J2 corresponding to A2 is displayed on the second sub-region of the user interface, J2={J 2,1 ,J 2,2 ,…,J 2,R2}, J 2,j2 is the j2th entity corresponding to A2, and the value range of j2 is 1 to R2, and R2 is the number of entities corresponding to A2; the entities corresponding to A2 include the entities corresponding to the column names and the entities corresponding to the table names in A2, and do not include the entities corresponding to A1; the second sub-region is different from the first sub-region; during the process of displaying the entity set J2 corresponding to A2 on the second sub-region of the user interface, the area size of the shape used to represent each entity corresponding to A2 is inversely proportional to R2.

[0038] According to the application, the larger R2 is, the smaller the area size of the shape used to represent each entity corresponding to A2 is, so as to display all the entities corresponding to A2 in the second sub-region.

[0039] The column name and the table name existing in the relationship in A2 are connected through a first connecting line, and the table name existing in the relationship in A1 and the table name are connected through a second connecting line.

[0040] By analogy, until all the triples corresponding to the SQL statements input by the user are displayed on the user interface.

[0041] In the case that the SQL statements input by the user are more, the user interface is divided into N regions, the entity corresponding to each SQL statement is displayed in each region, and the area size of the shape used to represent the entity is adjusted according to the number of the entity corresponding to each SQL statement, so that each triple corresponding to each SQL statement can have a corresponding display region, and the problem that all triples cannot be displayed on the user interface due to a large number of SQL statements input by the user can be solved.

[0042] In the case that the SQL statements input by the user are less, i.e., N≤N0, the user interface does not need to be divided into regions. N0 can be set according to user experience.

[0043] Although some specific embodiments of the present application have been described in detail through examples, those skilled in the art should understand that the above examples are only for illustration, but not for limiting the scope of the present application. Those skilled in the art should also understand that various modifications can be made to the embodiments without departing from the scope and spirit of the present application. The scope of the present application is defined by the appended claims.

Claims

1. A method of parsing an SQL statement, characterized by, The method comprises the following steps: S100, obtaining an SQL statement A={A1, A2, …, An} input by a user, An being an nth SQL statement input by the user, n being in a range of 1 to N, N being a number of SQL statements input by the user; n , N , n ​ S200, traversing A, obtaining A n n column names and table names and table names, and displays on a user interface triples constructed from the column names and table names and triples constructed from the table names and table names.​ In S200, displaying the triples of column names and table names and the triples of table names and table names according to the existence relationship on the user interface comprises: S210, if A n is a data query type SQL statement, display A n on the user interface, and go to S220; otherwise, go to S230. n { T n,1 , T n,2 , …, T n,q , …, T n,Q}, and go to S220; T n,q is the first triple built according to the qth query block in A n , q ranges from 1 to Q, Q is the number of query blocks in A n ; T n,q = (t 1 n,q , t 2 n,q , …, t m n,q …, t M n,q ), t m n,q is the first triple built according to the mth column name corresponding to the qth query block in A n , m ranges from 1 to M, M is the number of column names corresponding to the qth query block in A n ; t m n,q = (b n,q , r n,q , c m n,q ), b n,q is the entity corresponding to the table name corresponding to the qth query block in A n , c m n,q is the entity corresponding to the mth column name corresponding to the qth query block in A n , r n,q is the relationship between b n,q and c m n,q . S220, if Q≥2, then display A on the user interface n the corresponding second triple D n and the third triple G n ; D n ={D n,1 , D n,2 , …, D n,q , …, D n,Q}, D n,q is the second triple constructed according to the qth query block in A n ; D n,q =(d 1 n,q , dt 2 n,q , …, d m n,q …, d M n,q ), d m n,q is the second triple constructed according to the mth column name corresponding to the qth query block in A n ; d m n,q =(e n,q , f n,q , c m n,q ), e n,q is the entity corresponding to the table name of the temporary query result table obtained by executing the qth query block in A n , f n,q is the relationship between e n,q and c m n,q ; G n ={G n,1 , G n,2 , …, G n,q , …, G n,Q}, G n,q is the third triple constructed according to the qth query block in A n ; G n,q =(e n,q , h n,q , k n ), k n is the entity corresponding to the table name of the target query result table obtained by executing A n , h n,q is the relationship between e n,q and k n ; S220 also includes, if Q = 1, displaying A n a corresponding fourth triple L n = {L n,1 , L n,2 , …, L n,i , …, L n,S}, L n,i is the i-th fourth triple constructed according to A n , i ranges from 1 to S, S is the number of corresponding column names of A n ; L n,i = (c n,i , h n,i , k n ), c n,i is the entity corresponding to the i-th column name of A n , h n,i is the relationship between c n,i and k n .

2. The analysis processing method of an SQL statement according to claim 1, characterized by, In S200, Apache Calcite is used to parse A n .

3. The analysis processing method of an SQL statement according to claim 1, characterized by, In S200, displaying the triples of column names and table names and the triples of table names and table names according to the existence relationship on the user interface comprises: The entity corresponding to the column name in the triple is represented by a first preset color, and the entity corresponding to the table name in the triple is represented by a second preset color, wherein the first preset color is different from the second preset color.

4. The analysis processing method of an SQL statement according to claim 3, characterized by, The first preset color is black, and the second preset color is red.

5. The analysis processing method of an SQL statement according to claim 1, characterized by, In S200, displaying the triples of column names and table names and the triples of table names and table names according to the existence relationship on the user interface comprises: The entity corresponding to the column name in the triple is represented by a first preset shape, and the entity corresponding to the table name in the triple is represented by a second preset shape, wherein the first preset shape is different from the second preset shape.

6. The analysis processing method of an SQL statement according to claim 1, characterized by, In S200, displaying the triples of column names and table names and the triples of table names and table names according to the existence relationship on the user interface comprises: If N>N0, the user interface is divided into N sub-regions; N0 is a preset threshold of the number of SQL statements; displaying a first sub-region of the user interface, and displaying an entity set J1 corresponding to A1 on the first sub-region of the user interface, J1 = {J 1,1 , J 1,2 , …, J 1,R1}, J 1,j1 is the j1th entity corresponding to A1, j1 ranges from 1 to R1, R1 is the number of entities corresponding to A1; the entities corresponding to A1 include entities corresponding to column names and entities corresponding to table names in A1; during the process of displaying the entity set J1 corresponding to A1 on the first sub-region of the user interface, the area size of the shape representing each entity corresponding to A1 is negatively related to R1; The column names and table names of the existence relationship in A1 are connected by a first connecting line, and the table names of the existence relationship in A1 are connected by a second connecting line; the second connecting line is the same as or different from the first connecting line; displaying, on the second sub-region of the user interface, an entity set J2 corresponding to A2, J2 = {J 2,1 , 2,2 , …, J 2,R2} where J 2,j2 is the j2th entity corresponding to A2, j2 ranges from 1 to R2, and R2 is the number of entities corresponding to A2; the entities corresponding to A2 include the entities corresponding to the column names and the entities corresponding to the table names in A2, and do not include the entities corresponding to A1; the second sub-region is different from the first sub-region; during the process of displaying the entity set J2 corresponding to A2 on the second sub-region of the user interface, the area size of the shape representing each entity corresponding to A2 is negatively correlated with R2; The column names and table names of the existence relationship in A2 are connected by a first connecting line, and the table names of the existence relationship in A1 are connected by a second connecting line.

Images