SQL annotation generation method, computer readable medium and electronic equipment

Abstract

The invention discloses an SQL annotation generation method, a computer readable medium and electronic equipment, and the method comprises the steps: obtaining a table list in an initial statement, and loading a table structure corresponding to the table list; the table list refers to a list of table structures contained in the initial statement; expanding the initial statement based on the table structure to obtain a fully expanded field of the initial statement; performing grammar analysis on the fully-expanded field, and determining a sub-query list in the fully-expanded field; the sub-query is a query clause in a fully expanded field; according to the structure and the type of the sub-query, optimizing a field corresponding to the sub-query to obtain a statement to be annotated; adding corresponding annotation information to a target position of the to-be-annotated statement based on a table structure and a sub-query contained in the to-be-annotated statement; therefore, the readability of the code statement can be improved and the logic of the code statement is clearer by splitting the multilayer nested SQL initial statement, intelligently identifying the table and the sub-query to which the field in the statement belongs and adding the corresponding annotation information to the target position.

Description

Technical Field

[0001] The present invention belongs to the technical field of big data management, and in particular, relates to a SQL comment generation method, a computer-readable medium and an electronic device. Background Art

[0002] In big data batch computing, complex SQL query statements are becoming more and more common. These complex queries often involve multiple layers of SQL nesting (such as multi-table joins and multi-table unions) and various complex expression calculation logics. Understanding and maintaining these codes has become a challenge.

[0003] When writing or reading code, you often need to switch back and forth between the code and the table structure to help understand the calculation logic of SQL. When complex SQL involves multiple tables, this process is very inefficient and prone to errors. In addition, the non-standard code format and the use of all fields make the field dependencies less clear, and the influence of some unused fields in SQL makes the code less readable. Although existing database management systems provide powerful query functions, they often lack intelligent support for comment generation. In addition, although there are some tools that can assist in parsing SQL, they are usually unable to automatically generate accurate and comprehensive comments.

[0004] It should be noted that the information disclosed in the above background technology section is only used to enhance the understanding of the background of the present application, and therefore may include information that does not constitute the prior art known to ordinary technicians in the field. Summary of the invention

[0005] The object of the present invention is to provide a SQL comment generation method, a computer-readable medium and an electronic device, which are used to solve the problem that the automatically generated SQL comments in the related art are inaccurate or not comprehensive enough.

[0006] According to one aspect of an embodiment of the present application, a method for generating SQL comments is provided, the method comprising: obtaining a table list in an initial statement, and loading a table structure corresponding to the table list; the table list refers to a list of table structures contained in the initial statement;

[0007] Expand the initial statement based on the table structure to obtain the fully expanded fields of the initial statement;

[0008] Perform syntax analysis on the fully expanded field to determine the subquery list in the fully expanded field; the subquery is a query clause in the fully expanded field;

[0009] According to the structure and type of the subquery, optimize the fields corresponding to the subquery to obtain the statement to be commented;

[0010] Based on the table structure and subquery contained in the statement to be annotated, add corresponding annotation information to the target location of the statement to be annotated.

[0011] In one embodiment of the present application, a table list in an initial statement is obtained, and a table structure corresponding to the table list is loaded, including: obtaining an initial statement to which comment information needs to be added; parsing the initial statement to obtain a table list in the initial statement; loading the table structure in the table list in a table structure management module to obtain a table creation statement for the corresponding table.

[0012] In one embodiment of the present application, an initial statement is expanded based on a table structure to obtain fully expanded fields of the initial statement, including: determining whether there is a full-field query in the syntax tree after parsing the initial statement; when there is a full-field query in the syntax tree, determining the statement position and source type of each query full-field according to the hierarchical structure of the syntax tree; according to the source type, obtaining a first field list corresponding to the source type; replacing the query full field with the corresponding first field list; returning to execute the step of determining whether there is a full-field query in the syntax tree after parsing the initial statement, until there is no full-field query in the syntax tree and the fully expanded field is obtained.

[0013] In one embodiment of the present application, according to the source type, a first field list corresponding to the source type is obtained, including: when the source type is a table, loading the table structure of the table, and obtaining the first field list corresponding to the table; when the source type is a subquery, according to the statement type of the subquery, obtaining the first field list corresponding to the subquery.

[0014] In one embodiment of the present application, according to the structure and type of the subquery, the fields corresponding to the subquery are optimized to obtain the statement to be annotated, including: if the source statement of the subquery nests the subquery, the used fields of the subquery are obtained; the used fields are deduplicated to obtain a second field list; deduplication refers to deleting duplicate fields in the used fields; according to the type of the nested subquery of the source statement, the second field list is optimized to obtain the statement to be annotated.

[0015] In one embodiment of the present application, according to the type of the nested subquery of the source statement, the second field list is optimized to obtain the statement to be annotated, including: if the nested subquery is a select statement, deleting the unused fields in the select statement; if the nested subquery is a union statement, deleting the unused fields in each union clause starting from the first union clause; if the nested subquery is a join statement, deleting the unused fields of the associated subquery of the field used by the join statement; and combining the remaining fields according to a preset format to obtain the statement to be annotated.

[0016] In one embodiment of the present application, based on the table structure and subquery contained in the statement to be annotated, corresponding annotation information is added to the target position of the statement to be annotated, including: obtaining the table annotation information of the table structure contained in the statement to be annotated, and the field annotation information corresponding to the subquery; when there is already annotation information after the table in the statement to be annotated, determining whether to add the table annotation information to the target position of the corresponding table according to whether the existing annotation information contains the table annotation information; when there is already annotation information after the subquery in the statement to be annotated, determining whether to add the field annotation information to the target position of the corresponding table according to whether the existing annotation information contains the field annotation information.

[0017] In one embodiment of the present application, obtaining table annotation information of a table structure contained in a statement to be annotated includes: obtaining a table list and table positions contained in the statement to be annotated; and loading and obtaining table annotation information corresponding to the table position from a table structure management module according to the table list.

[0018] According to one aspect of an embodiment of the present application, a computer medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the SQL comment generation method provided by any embodiment of the present application is implemented.

[0019] According to one aspect of an embodiment of the present application, an electronic device is provided, including: a processor; a memory for storing executable instructions of the processor; the processor executes the executable instructions to enable the electronic device to implement the SQL comment generation method provided by any embodiment of the present application.

[0020] In the technical solution of the present application, a table list in an initial statement is obtained, and a table structure corresponding to the table list is loaded; the table list refers to a list of table structures contained in the initial statement; the initial statement is expanded based on the table structure to obtain the fully expanded field of the initial statement; the fully expanded field is parsed to determine the subquery list in the fully expanded field; the subquery is a query clause in the fully expanded field; according to the structure and type of the subquery, the field corresponding to the subquery is optimized to obtain the statement to be annotated; based on the table structure and subquery contained in the statement to be annotated, corresponding annotation information is added to the target position of the statement to be annotated; in this way, the multi-layer nested SQL initial statement can be split, the useless fields in the initial statement are deleted, the effective expansion of the full field is realized, the table and subquery to which the fields in the statement belong are intelligently identified, and the corresponding annotation information is added to the target position, which reduces the complexity of the code structure, improves the readability of the code statement, and makes the logic of the code statement clearer.

[0021] It should be understood that the above general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] The drawings herein are incorporated into the specification and constitute a part of the specification, illustrate embodiments consistent with the present application, and together with the specification are used to explain the principles of the present application. Obviously, the drawings described below are only some embodiments of the present application, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0023] Figure 1 The flowchart of the SQL comment generation method provided in one embodiment of the present application is schematically shown.

[0024] Figure 2 A schematic diagram of an initial statement provided in an embodiment of the present application is schematically shown.

[0025] Figure 3 A schematic diagram of a table creation statement provided in an embodiment of the present application is schematically shown.

[0026] Figure 4 The schematic diagram schematically shows the initial statement after processing provided by an embodiment of the present application.

[0027] Figure 5 A schematic diagram of a fully expanded field provided in an embodiment of the present application is schematically shown.

[0028] Figure 6 The schematic diagram of the full expansion field optimization provided by an embodiment of the present application is schematically shown.

[0029] Figure 7 A schematic diagram of a statement to be annotated provided in an embodiment of the present application is schematically shown.

[0030] Figure 8 A schematic diagram schematically shows an SQL annotation result provided by an embodiment of the present application.

[0031] Fig. 9 The structural block diagram of an electronic device provided by an embodiment of the present application is schematically shown.

[0032] Fig.10 The structure block diagram of a computer system for implementing an electronic device according to an embodiment of the present application is schematically shown. DETAILED DESCRIPTION

[0033] Example embodiments will now be described more fully with reference to the accompanying drawings. However, example embodiments can be implemented in a variety of forms and should not be construed as limited to the examples set forth herein; rather, these embodiments are provided so that this application will be more comprehensive and complete and fully convey the concept of the example embodiments to those skilled in the art.

[0034] In addition, the features, structures or characteristics described in the present application may be combined in one or more embodiments in any suitable manner. In the following description, many specific details are provided to provide a full understanding of the embodiments of the present application. However, those skilled in the art will appreciate that the technical scheme of the present application can be put into practice without one or more of the specific details, or other methods, components, devices, steps, etc. may be adopted. In other cases, known methods, devices, realizations or operations are not shown or described in detail to avoid blurring the various aspects of the present application.

[0035] The block diagrams shown in the accompanying drawings are merely functional entities and do not necessarily correspond to physically independent entities. That is, these functional entities may be implemented in software form, or in one or more hardware modules or integrated circuits, or in different networks and / or processor devices and / or microcontroller devices.

[0036] The flowcharts shown in the accompanying drawings are only exemplary and do not necessarily include all the contents and operations / steps, nor must they be executed in the order described. For example, some operations / steps can be decomposed, and some operations / steps can be combined or partially combined, so the actual execution order may change according to actual conditions.

[0037] Big data processing refers to the efficient and high-performance processing and analysis of massive data in order to discover the value hidden in the big data. The main goal of big data processing is to convert massive data into valuable information to meet the needs of enterprises or individuals. Big data processing includes but is not limited to data storage, data analysis, data mining, data visualization, etc. Structured Query Language (SQL) is a switch language often used to process and analyze data in big data processing. At present, complex SQL query statements are becoming more and more common in big data batch computing. These SQL query statements usually involve multiple layers of SQL nesting and various complex expression calculation logic. When developers and testers are writing or reading code, they often need to switch frequently between the code and the table structure on which they depend to confirm in order to understand the calculation logic of SQL. However, the non-standard code format and the use of all fields make the dependency relationship of the fields less clear. In addition, the influence of some unused fields in SQL makes the code less readable. In order to automatically generate more accurate and comprehensive SQL comments, such as Figure 1 As shown, the present application provides a method for generating SQL comments, which includes S110 to S150, and the specific process is as follows.

[0038] S110, obtaining a table list in the initial statement, and loading a table structure corresponding to the table list; the table list refers to a list of table structures contained in the initial statement.

[0039] Specifically, the initial statement refers to the SQL statement that is not automatically annotated, such as Figure 2 As shown in the figure, the initial statement basically does not annotate the specific fields or calculation purposes of the nested tables or query statements, making the SQL statement difficult to read and understand, and inconvenient for subsequent maintenance. Specifically, Figure 2 There are 4 SELECT * full-field queries in the initial statement, and there are 3 source tables, including: order_trans_record, order_trans_special_record and customer. However, when reading the initial statement, it is impossible to directly know the meaning of the fields queried from the above 3 source tables, nor is it clear what the purpose of the calculations performed by querying these fields is. The "table" in the table structure refers to the "table" in the relational database. The data in the relational database is stored in a table structure, which is the most basic storage structure in data analysis tools. In a relational database, a database can have multiple data tables, and each data table is a data set with a two-dimensional table structure consisting of fixed columns and arbitrary rows. The columns in the table are called fields, and the rows in the table are called records, and the fields are the basic storage units and calculation units. The table list is a list of table structures contained in the initial statement. For example, if Table A, Table B and Table C are used in a certain initial statement, then the table list of this initial statement includes Table A, Table B and Table C. The table structure corresponding to the loading table list can also be the table creation statements corresponding to loading tables A, B, and C. The table creation statements include the table fields and annotations of the data recorded in the corresponding fields. Figure 3 The table creation statement corresponding to the table order_trans_record shown in the figure includes multiple fields and annotations of the data recorded in these fields. For example, the field "order_id" has the corresponding annotation "Order ID".

[0040] In one embodiment of the present application, a table list in an initial statement is obtained, and a table structure corresponding to the table list is loaded, including: obtaining an initial statement to which comment information needs to be added; parsing the initial statement to obtain a table list in the initial statement; loading the table structure in the table list in a table structure management module to obtain a table creation statement for the corresponding table.

[0041] Specifically, the Druid-sqlparse engine can be used to parse the initial statement to generate a corresponding abstract syntax tree (AST). The abstract syntax tree is an abstract representation of the grammatical structure of the source code, which expresses the grammatical structure of the programming language in a tree-like form, and each node on the tree represents a structure in the source code. It should be understood that other SQL parsing tools can also be used to parse the initial statement, and this application does not impose specific restrictions on this. Next, the initial statement after splitting is split, and the syntax of the use database is parsed, and the default database name in the use database can be analyzed; if it is analyzed that there is no configuration in the initial statement, the default database is default. In general, each project will have its corresponding default database. The default database means that if there is no database specified before the table name in the initial statement, the table is assumed to be under a certain database. The table structure management module obtains the table structure through "library name + table name", so it is necessary to determine the library name information corresponding to the table in the initial statement. For example, if hive is used to extract, transform and load data, the default database is generally specified by the syntax of use database name before SQL. If not specified, the default database is default, so as to obtain each table used in the initial statement, count these tables to obtain the table list in the initial statement, and finally load the table structure in the table list in the table structure management module to obtain the table creation statements for these tables. It should be understood that the table structure information of all tables can be collected in advance through the metadata of the database, and the table structure information can be entered into the table structure management module.

[0042] S120: Expand the initial statement based on the table structure to obtain fully expanded fields of the initial statement.

[0043] Specifically, expanding the initial statement based on the table structure means fully expanding the table used in the initial statement according to the fields stored in the table structure, thereby obtaining the fully expanded fields of the initial statement. It can also be understood as adding all the complete fields of the table used in the initial statement to the initial statement to obtain the fully expanded fields. Figure 4 As shown, add the complete fields of order_trans_record, order_trans_special_record, and customer to the initial statement to obtain Figure 2 The fully expanded fields of the initial statement are shown.

[0044] In one embodiment of the present application, an initial statement is expanded based on a table structure to obtain fully expanded fields of the initial statement, including: determining whether there is a full-field query in the syntax tree after parsing the initial statement; when there is a full-field query in the syntax tree, determining the statement position and source type of each query full-field according to the hierarchical structure of the syntax tree; according to the source type, obtaining a first field list corresponding to the source type; replacing the query full field with the corresponding first field list; returning to execute the step of determining whether there is a full-field query in the syntax tree after parsing the initial statement, until there is no full-field query in the syntax tree and the fully expanded field is obtained.

[0045] Specifically, the syntax tree after parsing the initial statement refers to the abstract syntax tree obtained by parsing the initial statement using an SQL parsing tool. A full-field query refers to querying all fields in a table, rather than just querying specific fields. When you need to obtain all field values ​​of one or more records, you can use a full-field query. For example, SELECT* is a full-field query, where the SELECT statement is a data query statement, and * represents a full-field query. To determine whether there is a full-field query in the syntax tree after parsing the initial statement, you need to confirm whether there is a SELECT* statement in the syntax tree. As mentioned earlier, the abstract syntax tree represents the grammatical structure of a programming language in a tree-like form. From the syntax tree, you can know the hierarchical relationship and dependency relationship between these tables, and you can also know the type of the source table for each full-field query. Based on the hierarchical structure of the syntax tree, the statement position and source type of each query field can be determined. The specific process is to trace back from the leaf node of the syntax tree (that is, the bottom query) to find the SQL position of the first query field and obtain the source type of the query field; in the process of layer-by-layer tracing back, the SQL position and source type of other query fields can also be obtained, where the source type refers to the statement type after the FROM clause. Figure 2 As shown in the fully expanded fields, the full-field query of SELECT* is followed by a FROM clause. The FROM clause is used to specify the data source of the full-field query. The first field list is a list of fields in the data source, or a set of fields in the data source. Then the query full-field* on the leaf node is replaced with the first field list, and then the step of determining whether there is a full-field query in the syntax tree after parsing the initial statement is returned to execute, until there is no full-field query in the syntax tree, and the fully expanded fields are obtained. Figure 2 As shown, there are 4 SELECT * full-field queries. First, replace the query full-field * on the leaf node, and then replace * with the complete fields in order_trans_record, order_trans_special_record, and customer. You can get the following Figure 4 The SQL after the low-level full-field query is expanded is shown in the figure. At this time, there is still a * in the high-level that has not been expanded. Therefore, the step of executing the full field expansion is returned to obtain the following Figure 5 The fully expanded fields of the initial statement are shown.

[0046] In one embodiment of the present application, according to the source type, a first field list corresponding to the source type is obtained, including: when the source type is a table, loading the table structure of the table, and obtaining the first field list corresponding to the table; when the source type is a subquery, according to the statement type of the subquery, obtaining the first field list corresponding to the subquery.

[0047] Specifically, the FROM clause may be followed by a specified table or a subquery. Based on the source type, the corresponding field expansion steps are also different. When the source type is a table, directly load the table structure of the specified table and obtain the first field list corresponding to the specified table. Figure 2 As shown in the figure, if there is a table after FROM, the table structures of order_trans_record, order_trans_special_record and customer are directly loaded, and the complete field lists corresponding to these tables are obtained as the first field list. The source type is a subquery, which means that the FROM clause is not followed by a specified table, but a query clause. Figure 2 As shown, the FROM clause after the first full-field query includes multiple query clauses. The types of query clauses include select statements, union statements, and join statements, where the select statement can be a SELECT query. When the subquery is a select statement, the corresponding first field list is obtained from the specified table queried by the SELECT statement. When the subquery is a union statement, the query field list of the first union clause is obtained, and then it is determined again whether the first union clause is followed by a table or a subquery, and the corresponding first field list is obtained according to the source type after the union clause. The specific process of obtaining the first field list by the subquery of the Union statement is similar to the process under the FROM statement, and will not be repeated here. When the subquery is a join statement, it is determined whether there is a table alias before the full field*; if there is a table alias before the full field*, the corresponding field list under the table alias is obtained; if there is no table alias before the full field*, the field lists of all subqueries under the join statement are obtained, and all fields are summarized into a complete field list as the first field list.

[0048] S130, performing syntax analysis on the fully expanded field to determine a subquery list in the fully expanded field; a subquery is a query clause in the fully expanded field.

[0049] Specifically, the SQL parsing tool is used to parse the fully expanded field again to obtain the corresponding AST syntax tree, and then the subquery list in the fully expanded field can be determined based on the AST syntax tree. The subquery list refers to a list composed of query clauses in the fully expanded field. Figure 5 As shown in the fully expanded fields, multiple subqueries are nested after the first FROM clause. By combining these subqueries into a list, a subquery list can be obtained.

[0050] S140. According to the structure and type of the subquery, optimize the fields corresponding to the subquery to obtain a statement to be annotated.

[0051] Specifically, the structure of a subquery refers to the hierarchical structure between subqueries. The subquery structure can be obtained by tracing back the AST syntax tree from the leaf node to the top layer. The type of a subquery refers to the statement type of the query clause. The types of subqueries include select statements, union statements, and join statements. Based on the structure of the subquery, the order of optimizing the subqueries can be determined; based on the type of the subquery, the method of optimizing the subquery can be determined. After optimizing the fields in the subquery, the statement to be annotated can be obtained.

[0052] In one embodiment of the present application, according to the structure and type of the subquery, the fields corresponding to the subquery are optimized to obtain the statement to be annotated, including: if the source statement of the subquery nests the subquery, the used fields of the subquery are obtained; the used fields are deduplicated to obtain a second field list; deduplication refers to deleting duplicate fields in the used fields; according to the type of the nested subquery of the source statement, the second field list is optimized to obtain the statement to be annotated.

[0053] Specifically, the source statement of a subquery refers to the FROM statement corresponding to the subquery SELECT. The source statement nests the subquery, which means that the FROM statement is still a query statement. The used fields of a subquery refer to all the fields used in each subquery, such as select, group by, order by, and where fields. Obtaining the used fields of a subquery means obtaining all the used fields of all subqueries in the subquery list. The obtained used fields may be repeated. For example, field A is used in select and also in group by. The second field A is a repeated field. If deduplication is not performed, the final SQL statement will be redundant. Therefore, deduplication of the used fields is required. Deleting repeated fields in the used fields means deleting repeated used fields and retaining one field. For example, if field A is used in select and also in group by, two fields A can be obtained. When deduplication is performed on the used fields, one field A will be deleted and one field A will be retained, so that only one field A is included in the second field list. The nested subquery of the source statement refers to the subquery nested in the FROM statement. For example, if the FROM statement is followed by a select statement, union statement, or join statement, then the select statement, union statement, or join statement is a nested subquery. The type of the nested subquery determines whether the FROM statement is followed by a select statement, union statement, or join statement. Different types of nested subqueries have different corresponding optimization steps. If the source statement of the subquery does not have a nested subquery, there is no need to optimize the unused fields in the nested subquery.

[0054] In one embodiment of the present application, according to the type of the nested subquery of the source statement, the second field list is optimized to obtain the statement to be annotated, including: if the nested subquery is a select statement, deleting the unused fields in the select statement; if the nested subquery is a union statement, deleting the unused fields in each union clause starting from the first union clause; if the nested subquery is a join statement, deleting the unused fields of the associated subquery of the field used by the join statement; and combining the remaining fields according to a preset format to obtain the statement to be annotated.

[0055] Specifically, unused fields refer to unused fields in the fully expanded fields. Before this step, the used fields of the subquery list have been obtained, so the unused fields are the fields in the fully expanded fields except the used fields. For example, the col1 field corresponding to the query table test1, and the full table of the table test1 includes the fields col1 and col2, the initial statement is "selectcol1 from(select*from(select*from test1)t1)t2;", the initial statement is fully expanded to obtain the corresponding fully expanded field "selectcol1 from(selectcol1,col2 from(select col1,col2 from test1)t1)t2;". Then the fully expanded field is optimized. In the first round of removing unused fields, the bottom-level subquery is "select col1...", and the query on the upper layer is "selectcol1,col2...", so the col1 field queried on the upper layer (i.e., the second layer) of the bottom layer cannot be deleted, and the col2 field is not queried, that is, the col2 field is an unused field. Then after the first round of optimization, the SQL statement is "select col1 from(select col1 from(select col1,col2from test1)t1)t2;". When removing unused fields in the second round, the second layer now only uses the col1 field, and the previous layer of the second layer (i.e. the third layer) is "selectcol1,col2……", in which the col2 field is not queried either. Therefore, the SQL statement after the second round of optimization is "select col1 from(select col1 from(select col1 from test1)t1)t2;)". Figure 5 , Figure 6 and Figure 7 As shown, when the nested subquery is a union statement, the query field list of the first union clause is obtained; the unused fields in the first union clause are deleted, and the position of the currently deleted fields is recorded; the remaining union clauses are traversed and processed, and the unused fields of the union clauses are deleted starting from the recorded field positions. When the nested subquery is a join statement, the source of the used fields is determined to obtain the used field list of all associated subqueries; the fields of each associated subquery are deleted in turn. Through the above steps, the unused fields of the select statement, union statement, and join statement in the nested subquery are deleted, and finally the remaining fields are combined according to the preset format to obtain the statement to be commented.

[0056] S150: Based on the table structure and subquery contained in the statement to be annotated, add corresponding annotation information to the target position of the statement to be annotated.

[0057] Specifically, the table structure and subquery of the statement to be annotated have been obtained in the previous step. The table structure can be used to determine the recording purpose of the fields used by the statement to be annotated, and the recording purpose can be used as annotation information. In addition, the FROM statement is usually followed by a specified table, and the corresponding table name can be obtained through the specified table. The table name can also be used as annotation information. The subquery of the statement to be annotated refers to the select statement. Based on the subquery of the statement to be annotated, the query list of the select statement can be determined, that is, the queried fields are determined, and then the annotation information corresponding to these query fields is obtained. Finally, the obtained annotation information is added to the target position of the statement to be annotated, and the target position is the position of the statement corresponding to the annotation information.

[0058] In the technical solution of the present application, a table list in an initial statement is obtained, and a table structure corresponding to the table list is loaded; the table list refers to a list of table structures contained in the initial statement; the initial statement is expanded based on the table structure to obtain the fully expanded field of the initial statement; the fully expanded field is parsed to determine the subquery list in the fully expanded field; the subquery is a query clause in the fully expanded field; according to the structure and type of the subquery, the field corresponding to the subquery is optimized to obtain the statement to be annotated; based on the table structure and subquery contained in the statement to be annotated, corresponding annotation information is added to the target position of the statement to be annotated; in this way, the multi-layer nested SQL initial statement can be split, the useless fields in the initial statement are deleted, the effective expansion of the full field is realized, the table and subquery to which the fields in the statement belong are intelligently identified, and the corresponding annotation information is added to the target position, which reduces the complexity of the code structure, improves the readability of the code statement, and makes the logic of the code statement clearer.

[0059] In one embodiment of the present application, based on the table structure and subquery contained in the statement to be annotated, corresponding annotation information is added to the target position of the statement to be annotated, including: obtaining the table annotation information of the table structure contained in the statement to be annotated, and the field annotation information corresponding to the subquery; when there is already annotation information after the table in the statement to be annotated, determining whether to add the table annotation information to the target position of the corresponding table according to whether the existing annotation information contains the table annotation information; when there is already annotation information after the subquery in the statement to be annotated, determining whether to add the field annotation information to the target position of the corresponding table according to whether the existing annotation information contains the field annotation information.

[0060] Specifically, after obtaining the table annotation information and field annotation information, it is also necessary to add annotations in combination with the annotation status in the statement to be annotated determined by the statement recognition tool, so as to reduce the addition of duplicate annotations and improve the accuracy of automatic annotations. It can be understood that when there is already annotation information after the table in the statement to be annotated, the existing annotation information and the table annotation information to be added are compared; if they are different, the table annotation information can be added after the row where the table is located, so as to explain the meaning, type or purpose of the table together with the existing annotation information; if they are the same, the table annotation information to be added is discarded. Similarly, the field annotation information to be added must also be compared with the existing field annotation information to determine whether it needs to be added to the row where the corresponding field is located. This process is similar to the process of adding table annotation information, and will not be repeated here.

[0061] In one embodiment of the present application, obtaining table annotation information of a table structure contained in a statement to be annotated includes: obtaining a table list and table positions contained in the statement to be annotated; and loading and obtaining table annotation information corresponding to the table position from a table structure management module according to the table list.

[0062] Specifically, the table position refers to the position of the table statement in the statement to be annotated. As mentioned above, the table structure information of all tables is collected in advance through the metadata of the database, and the table structure information is entered into the table structure management module. Therefore, the record purpose corresponding to each field can be loaded and obtained from the table structure management module, and the record purpose can be used as table annotation information. In addition, information such as the name, type or purpose of the table can also be used as table annotation information. Then, add these table annotation information to the table position of the table statement. Figure 8 As shown, order_trans_record is the order transaction flow table, so the annotation information "—[Table: Order Transaction Flow Table]" can be added after the statement to be annotated order_trans_record.

[0063] In another embodiment, after the annotation information is added to the statement to be annotated, the position of the annotation information may be adjusted so that the annotation information is arranged in a unified format, thereby improving the aesthetics and readability of the code.

[0064] The electronic equipment of this application is introduced below, such as Fig. 9 As shown, the present application provides an electronic device 900, which includes: a processor 910 and a memory 920, the memory 920 is used to store executable instructions of the processor; the processor 910 executes the executable instructions to enable the electronic device to implement the SQL comment generation method provided by any embodiment of the present application.

[0065] Specifically, the SQL comment generation method provided by the present application is stored in the memory 920 of the electronic device, and the SQL comment generation method is executed by the processor 910 to split the multi-layer nested SQL initial statement, delete the useless fields in the initial statement, realize the effective expansion of all fields, intelligently identify the tables and subqueries to which the fields in the statement belong, and add the corresponding annotation information to the target location, thereby reducing the complexity of the code structure, and can improve the readability of the code statement, making the logic of the code statement clearer.

[0066] It should be known that the specific implementation content of the electronic device in the present application has been explained in detail in the corresponding method embodiment and will not be repeated here.

[0067] Fig.10 The structure block diagram of a computer system for implementing an electronic device according to an embodiment of the present application is schematically shown.

[0068] It should be noted that Fig.10 The computer system 1000 of the electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0069] like Fig.10 As shown, the computer system 1000 includes a processor 1001, which can be a CPU (Central Processing Unit) or an MCU (Microcontroller Unit). The processor 1001 can perform various appropriate actions and processes according to the program stored in the read-only memory 1002 (Read-Only Memory, ROM) or the program loaded from the storage part 1008 to the random access memory 1003 (Random Access Memory, RAM). In the random access memory 1003, various programs and data required for system operation are also stored. The processor 1001, the read-only memory 1002 and the random access memory 1003 are connected to each other through a bus 1004. The input / output interface 1005 (Input / Output interface, i.e., I / O interface) is also connected to the bus 1004.

[0070] The following components are connected to the input / output interface 1005: an input section 1006 including a keyboard, a mouse, etc.; an output section 1007 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 1008 including a hard disk, etc.; and a communication section 1009 including a network interface card such as a LAN card, a modem, etc. The communication section 1009 performs communication processing via a network such as the Internet. A drive 1010 is also connected to the input / output interface 1005 as needed. A removable medium 1011, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 1010 as needed, so that a computer program read therefrom is installed into the storage section 1008 as needed.

[0071] In particular, according to an embodiment of the present application, the process described in each method flow chart can be implemented as a computer software program. For example, an embodiment of the present application includes a computer program product, which includes a computer program carried on a computer readable medium, and the computer program contains a program code for executing the method shown in the flow chart. In such an embodiment, the computer program can be downloaded and installed from a network through the communication part 1009, and / or installed from a removable medium 1011. When the computer program is executed by the processor 1001, various functions defined in the system of the present application are executed.

[0072] It should be noted that the computer-readable medium shown in the embodiment of the present application may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, a system, device or device of electricity, magnetism, light, electromagnetic, infrared, or semiconductor, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, a computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by an instruction execution system, device or device or used in combination with it. In the present application, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, wherein a computer-readable program code is carried. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer readable signal media may also be any computer readable medium other than computer readable storage media, which may send, propagate, or transmit programs for use by or in conjunction with an instruction execution system, apparatus, or device. The program code contained on the computer readable medium may be transmitted using any suitable medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0073] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the above-mentioned module, program segment or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0074] It should be noted that, although several modules or units of the equipment for action execution are mentioned in the above detailed description, this division is not mandatory. In fact, according to the embodiments of the present application, the features and functions of two or more modules or units described above can be embodied in one module or unit. On the contrary, the features and functions of one module or unit described above can be further divided into being embodied by multiple modules or units.

[0075] Through the description of the above implementation methods, it is easy for those skilled in the art to understand that the example implementation methods described here can be implemented by software, or by software combined with necessary hardware. Therefore, the technical solution according to the implementation method of the present application can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (which can be a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, including several instructions to enable a computing device (which can be a personal computer, a server, a touch terminal, or a network device, etc.) to execute the implementation method according to the present application.

[0076] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary technical means in the art that are not disclosed in the present application.

[0077] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.

Claims

1. A method for generating SQL comments, characterized in that: include: Obtaining a table list in an initial statement, and loading a table structure corresponding to the table list; The table list refers to a list of table structures contained in the initial statement; Expand the initial statement based on the table structure to obtain fully expanded fields of the initial statement; Performing syntax analysis on the fully expanded field to determine a subquery list in the fully expanded field; The subquery is a query clause in the fully expanded field; According to the structure and type, the fields corresponding to the subquery are optimized to obtain the statement to be annotated; Based on the table structure and subquery contained in the statement to be annotated, corresponding annotation information is added to the target position of the statement to be annotated.

2. The SQL comment generation method according to claim 1, characterized in that: The obtaining of the table list in the initial statement and loading of the table structure corresponding to the table list include: Get the initial statement to which annotation information needs to be added; Parsing the initial statement to obtain a table list in the initial statement; The table structure in the table list is loaded into the table structure management module to obtain the table creation statement of the corresponding table.

3. The SQL comment generation method according to claim 1, characterized in that: The step of expanding the initial statement based on the table structure to obtain a fully expanded field of the initial statement includes: Determine whether there is a full-field query in the syntax tree after parsing the initial statement; When there is a full-field query in the syntax tree, determining the statement position and source type of each full-field query according to the hierarchical structure of the syntax tree; According to the source type, obtaining a first field list corresponding to the source type; Replace the query full field with the corresponding first field list; Return to the step of determining whether there is a query for the full field in the syntax tree after parsing the initial statement, until there is no query for the full field in the syntax tree, and obtain the fully expanded field.

4. The SQL comment generation method according to claim 3, characterized in that: The acquiring, according to the source type, a first field list corresponding to the source type includes: When the source type is a table, the table structure of the table is loaded, and a first field list corresponding to the table is obtained; When the source type is a subquery, a first field list corresponding to the subquery is obtained according to a statement type of the subquery.

5. The SQL comment generation method according to claim 1, characterized in that: The step of optimizing the fields corresponding to the subquery according to the structure and type of the subquery to obtain the statement to be annotated includes: If the source statement of the subquery nests a subquery, obtaining the used fields of the subquery; Deduplication processing is performed on the used fields to obtain a second field list; the deduplication processing refers to deleting repeated fields in the used fields; According to the type of the nested subquery of the source statement, the second field list is optimized to obtain the statement to be annotated.

6. The SQL comment generation method according to claim 5, characterized in that: The step of optimizing the second field list according to the type of the nested subquery of the source statement to obtain the statement to be annotated includes: If the nested subquery is a select statement, deleting unused fields in the select statement; If the nested subquery is a union statement, unused fields in each union clause are deleted starting from the first union clause; If the nested subquery is a join statement, deleting unused fields of the associated subquery of the fields used by the join statement; The remaining fields are combined according to a preset format to obtain the statement to be commented out.

7. The SQL comment generation method according to claim 1, characterized in that: The adding corresponding annotation information to the target position of the statement to be annotated based on the table structure and subquery contained in the statement to be annotated includes: Acquire table annotation information of the table structure contained in the statement to be annotated, and field annotation information corresponding to the subquery; When there is annotation information after the table in the statement to be annotated, determining whether to add the table annotation information to the target position of the corresponding table according to whether the existing annotation information includes the table annotation information; When there is annotation information after the subquery in the statement to be annotated, it is determined whether to add the field annotation information to the target position of the corresponding table according to whether the existing annotation information contains the field annotation information.

8. The SQL comment generation method according to claim 7, characterized in that: The step of obtaining the table annotation information of the table structure contained in the statement to be annotated includes: Obtaining a list of tables and table positions contained in the statement to be commented out; According to the table list, the table annotation information corresponding to the table position is loaded and obtained from the table structure management module.

9. A computer readable medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for generating SQL comments described in any one of claims 1 to 8 is implemented.

10. An electronic device, characterized in that: include: processor; A memory, configured to store executable instructions of the processor; The processor executes the executable instructions to enable the electronic device to implement the SQL comment generation method as described in any one of claims 1 to 8 above.

Images