CN114357038A

CN114357038A - Structured query language sentence display method and electronic equipment

Info

Publication number: CN114357038A
Application number: CN202210182391.8A
Authority: CN
Inventors: 吕冬冬
Original assignee: Beijing Shell Time Network Technology Co ltd
Current assignee: Beijing Shell Time Network Technology Co ltd
Priority date: 2022-02-25
Filing date: 2022-02-25
Publication date: 2022-04-15

Abstract

The embodiment of the disclosure discloses a Structured Query Language (SQL) statement display method and electronic equipment. The method comprises the following steps: acquiring a log file corresponding to a test environment; generating N SQL sentences according to the analysis result of the log file; wherein N is an integer greater than or equal to 2; determining N quality evaluation values corresponding to N SQL sentences; and sequencing and displaying the N SQL sentences according to the N quality evaluation values. On the premise of ensuring the realization of quality detection of a large number of SQL sentences, the embodiment of the disclosure can save manpower and time greatly.

Description

Structured query language sentence display method and electronic equipment

Technical Field

The present disclosure relates to the field of database technologies, and in particular, to a method for displaying Structured Query Language (SQL) statements and an electronic device.

Background

In the development process of the business system, operations such as adding and modifying SQL statements in the database are often involved, and the influence of the operations on the business system is unknown, so before the business system is brought online, quality detection of the SQL statements is necessary.

The currently common quality detection methods are as follows: a large number of SQL sentences in a test environment are pasted manually, then the SQL sentences are analyzed manually by using the existing tools, the whole process needs a large number of manual work, and time and labor are wasted.

Disclosure of Invention

The present disclosure is proposed to solve the above technical problems. The embodiment of the disclosure provides an SQL statement display method and electronic equipment.

According to an aspect of the embodiments of the present disclosure, there is provided a method for displaying an SQL statement, including:

acquiring a log file corresponding to a test environment;

generating N SQL sentences according to the analysis result of the log file; wherein N is an integer greater than or equal to 2;

determining N quality evaluation values corresponding to the N SQL sentences;

and sequencing and displaying the N SQL sentences according to the N quality evaluation values.

In an optional example, the generating N SQL statements according to the parsing result of the log file includes:

acquiring M pieces of original log information from the analysis result of the log file; wherein M is an integer greater than or equal to N;

converting the M original log information into an array format to generate corresponding M conversion log information;

screening N pieces of conversion log information of which the first designated positions are all data for representing the type of the current conversion log information as an SQL log type from the M pieces of conversion log information;

respectively extracting SQL sentences from respective second appointed positions of the N pieces of conversion log information;

and formatting the N SQL sentences extracted from the N pieces of conversion log information respectively to generate the corresponding N SQL sentences after formatting processing.

In an optional example, the obtaining a log file corresponding to the test environment includes:

establishing remote connection with a test environment;

determining a log path corresponding to the test environment;

and reading the log file corresponding to the test environment according to the log path through the remote connection.

In an optional example, before obtaining the log file corresponding to the test environment, the method further includes:

acquiring P pieces of environment information to be selected; wherein P is an integer greater than or equal to 2;

displaying a candidate environment list comprising corresponding P candidate environments according to the P candidate environment information;

receiving the selection operation of a user on a target environment to be selected; the target candidate environment is any one of the P candidate environments;

and taking the target candidate environment as the test environment.

In an optional example, the determining N quality assessment values corresponding to the N SQL statements includes:

carrying out syntax analysis on a target SQL statement, and processing the target SQL statement into an abstract syntax tree; wherein the target SQL statement is any one of the N SQL statements;

determining K query condition data corresponding to K preset query types according to the abstract syntax tree; k is an integer greater than or equal to 2, and the query condition data corresponding to any preset query type is used for representing whether the query of the preset query type exists in the target SQL statement;

determining K deduction values corresponding to the K preset query types according to the K query condition data;

calculating the sum of the K deduction values;

and determining a quality assessment value corresponding to the target SQL statement according to the difference value between the first preset numerical value and the sum.

In an optional example, the determining, according to the K query condition data, K score values corresponding to the K preset query types includes:

determining zero as a deduction value corresponding to a target preset query type under the condition that query condition data corresponding to the target preset query type is used for representing that no query of the target preset query type exists in the target SQL statement; otherwise, determining the severity level corresponding to the target preset query type; the target preset query type is any one of the K preset query types;

determining zero as a deduction value corresponding to the target preset query type under the condition that the severity level corresponding to the target preset query type is a preset severity level;

and under the condition that the severity grade corresponding to the target preset query type is higher than the preset severity grade, determining a deduction value corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and a second preset value.

In an optional example, the determining, according to the level numerical value of the severity level corresponding to the target preset query type and the second preset numerical value, a score corresponding to the target preset query type includes:

and taking the product of the grade value of the severity grade corresponding to the target preset query type and the second preset value as the deduction value corresponding to the target preset query type.

In one optional example, the method further comprises:

screening R preset query types meeting preset conditions from the K preset query types; wherein, R is an integer greater than or equal to 1, and any preset query type meeting the preset condition means: the query condition data corresponding to the preset query type is used for representing the query of the preset query type in the target SQL statement;

displaying N controls corresponding to the N SQL sentences displayed in an ordered mode;

under the condition of receiving input operation on a control corresponding to the target SQL statement, determining R severity levels corresponding to the R preset query types and R query risk prediction information corresponding to the R preset query types;

displaying the interpretation data of the quality evaluation value corresponding to the target SQL statement; wherein the interpretation data comprises: the system comprises R preset query types, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types.

According to another aspect of the embodiments of the present disclosure, there is provided an SQL statement display apparatus, including:

the first acquisition module is used for acquiring a log file corresponding to a test environment;

the generating module is used for generating N SQL sentences according to the analysis result of the log file; wherein N is an integer greater than or equal to 2;

the first determining module is used for determining N quality evaluation values corresponding to the N SQL sentences;

and the first display module is used for carrying out sequencing display on the N SQL sentences according to the N quality evaluation values.

In one optional example, the generating module includes:

the acquisition submodule is used for acquiring M pieces of original log information from the analysis result of the log file; wherein M is an integer greater than or equal to N;

the conversion submodule is used for converting the M original log information into an array format so as to generate corresponding M conversion log information;

the screening submodule is used for screening N pieces of conversion log information of which the first designated positions are all data which are used for representing the type of the current conversion log information and are SQL log types from the M pieces of conversion log information;

the extraction submodule is used for respectively extracting SQL sentences from respective second appointed positions of the N pieces of conversion log information;

and the first processing submodule is used for respectively formatting the N SQL sentences extracted from the N pieces of conversion log information so as to generate the corresponding formatted N SQL sentences.

In an optional example, the first obtaining module includes:

the establishing submodule is used for establishing remote connection with a test environment;

the first determining submodule is used for determining a log path corresponding to the test environment;

and the reading sub-module is used for reading the log file corresponding to the test environment according to the log path through the remote connection.

In one optional example, the apparatus further comprises:

the second acquisition module is used for acquiring P pieces of environment information to be selected before acquiring the log file corresponding to the test environment; wherein P is an integer greater than or equal to 2;

the second display module is used for displaying a candidate environment list comprising corresponding P candidate environments according to the P candidate environment information;

the receiving module is used for receiving the selection operation of a user on the target environment to be selected; the target candidate environment is any one of the P candidate environments;

and the second determination module is used for taking the target candidate environment as the test environment.

In one optional example, the first determining module comprises:

the second processing submodule is used for carrying out syntax analysis on the target SQL statement and processing the target SQL statement into an abstract syntax tree; wherein the target SQL statement is any one of the N SQL statements;

the second determining submodule is used for determining K query condition data corresponding to K preset query types according to the abstract syntax tree; k is an integer greater than or equal to 2, and the query condition data corresponding to any preset query type is used for representing whether the query of the preset query type exists in the target SQL statement;

a third determining submodule, configured to determine, according to the K pieces of query condition data, K deduction values corresponding to the K preset query types;

a calculation submodule for calculating a sum of the K deduction values;

and the fourth determining submodule is used for determining the quality assessment value corresponding to the target SQL statement according to the difference value between the first preset numerical value and the sum.

In an optional example, the third determining sub-module includes:

the first determining unit is used for determining zero as a deduction value corresponding to a target preset query type under the condition that query condition data corresponding to the target preset query type is used for representing that no query of the target preset query type exists in the target SQL statement; otherwise, determining the severity level corresponding to the target preset query type; the target preset query type is any one of the K preset query types;

a second determining unit, configured to determine zero as a deduction value corresponding to the target preset query type when the severity level corresponding to the target preset query type is a preset severity level;

a third determining unit, configured to determine, according to the grade value of the severity grade corresponding to the target preset query type and the second preset value, a deduction value corresponding to the target preset query type when the severity grade corresponding to the target preset query type is higher than the preset severity grade.

In an optional example, the third determining unit is specifically configured to:

In one optional example, the apparatus further comprises:

the screening module is used for screening R preset query types meeting preset conditions from the K preset query types; wherein, R is an integer greater than or equal to 1, and any preset query type meeting the preset condition means: the query condition data corresponding to the preset query type is used for representing the query of the preset query type in the target SQL statement;

the third display module is used for displaying N controls corresponding to the N SQL sentences which are displayed in a sequencing mode;

a third determining module, configured to determine, when an input operation on a control corresponding to the target SQL statement is received, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types;

the fourth display module is used for displaying the interpretation data of the quality assessment value corresponding to the target SQL statement; wherein the interpretation data comprises: the system comprises R preset query types, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types.

According to still another aspect of an embodiment of the present disclosure, there is provided an electronic apparatus including:

a memory for storing a computer program product;

and the processor is used for executing the computer program product stored in the memory, and when the computer program product is executed, the SQL statement display method is realized.

According to another aspect of the embodiments of the present disclosure, there is provided a computer-readable storage medium, on which computer program instructions are stored, and when the computer program instructions are executed by a processor, the SQL statement display method is implemented.

According to yet another aspect of the embodiments of the present disclosure, there is provided a computer program product including computer program instructions, which when executed by a processor, implement the SQL statement display method described above.

In the embodiment of the disclosure, after the log file corresponding to the test environment is obtained, N SQL statements may be generated according to the analysis result of the log file, and then N quality assessment values corresponding to the N SQL statements may be determined, and the N SQL statements may be displayed in an order according to the N quality assessment values. That is, in the embodiment of the present disclosure, in combination with the acquisition operation of the log file corresponding to the test environment and the parsing operation of the log file, a large number of SQL statements can be automatically generated, and then, in combination with the determination operation of the quality assessment value and the sorting display operation of the SQL statements, the quality of the SQL statements can be directly presented to the analyst, and the analyst can perform analysis and optimization of the business system accordingly, and the analyst does not need to manually paste and analyze a large number of SQL statements, so that, compared with the related art, on the premise of ensuring that quality detection of a large number of SQL statements is performed, the embodiment of the present disclosure can greatly save labor and time.

Drawings

Fig. 1 is a schematic flow chart diagram of an SQL statement display method according to an exemplary embodiment of the present disclosure.

Fig. 2 is an overall design schematic diagram of an embodiment of the present disclosure.

Fig. 3 is a schematic flow chart of an SQL statement display method according to another exemplary embodiment of the present disclosure.

Fig. 4 is a flowchart illustrating an SQL statement display method according to still another exemplary embodiment of the present disclosure.

Fig. 5 is a flowchart illustrating an SQL statement display method according to another exemplary embodiment of the present disclosure.

Fig. 6 is a flowchart illustrating an SQL statement display method according to another exemplary embodiment of the present disclosure.

FIG. 7 is a schematic diagram of a test environment list in an embodiment of the present disclosure.

Fig. 8 is a schematic diagram illustrating an evaluation display flow of an SQL statement in an embodiment of the disclosure.

Fig. 9 is a schematic diagram illustrating an ordering of SQL statements in an embodiment of the disclosure.

Fig. 10 is a schematic diagram for explaining data in the embodiment of the present disclosure.

Fig. 11 is a schematic structural diagram of an SQL statement display apparatus according to an exemplary embodiment of the present disclosure.

Fig. 12 is a block diagram of an electronic device provided in an exemplary embodiment of the present disclosure.

Detailed Description

Hereinafter, example embodiments according to the present disclosure will be described in detail with reference to the accompanying drawings, and the described embodiments are only a part of the embodiments, not all of the embodiments, and the present disclosure is not limited by the example embodiments described herein.

It should be noted that: the relative arrangement of the components and steps, the numerical expressions, and numerical values set forth in these embodiments do not limit the scope of the present disclosure unless specifically stated otherwise.

It will be understood by those within the art that the terms "first", "second", etc. in the embodiments of the present disclosure are used merely to distinguish one step, device or module from another, and do not denote any particular technical meaning or necessary logical order. "plurality" may mean two or more, and "at least one" may mean one, two or more.

It is also to be understood that any reference to any component, data, or structure in the embodiments of the disclosure, may be generally understood as one or more, unless explicitly defined otherwise or stated otherwise.

The term "and/or" in this disclosure is only one kind of association relationship describing the associated object, and means that there may be three kinds of relationships, for example, a and/or B, which may mean: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" in the present disclosure generally indicates that the former and latter associated objects are in an "or" relationship.

It should also be understood that the description of the various embodiments of the present disclosure emphasizes the differences between the various embodiments, and the same or similar parts may be referred to each other, so that the descriptions thereof are omitted for brevity. It should be understood that the dimensions of the various features shown in the drawings are not drawn to scale for ease of illustration.

The following description of at least one exemplary embodiment is merely illustrative in nature and is in no way intended to limit the disclosure, its application, or uses. Techniques, methods, and apparatus known to those of ordinary skill in the relevant art may not be discussed in detail, and the techniques, methods, and so on, as appropriate, should be considered part of the specification.

It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, further discussion thereof is not required in subsequent figures.

The disclosed embodiments are applicable to electronic devices such as terminal devices, computer systems, servers, etc., which are operational with numerous other general purpose or special purpose computing system environments or configurations. Examples of well known terminal devices, computing systems, environments, and/or configurations that may be suitable for use with electronic devices, such as terminal devices, computer systems, servers, and the like, include, but are not limited to: personal computer systems, server computer systems, thin clients, thick clients, hand-held or laptop devices, microprocessor-based systems, networked personal computers, minicomputer systems, mainframe computer systems, distributed cloud computing environments that include any of the above systems, and the like.

Electronic devices such as terminal devices, computer systems, servers, etc. may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. The computer system/server may be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computer system storage media including memory storage devices.

Exemplary method

Fig. 1 is a schematic flow chart diagram of an SQL statement display method according to an exemplary embodiment of the present disclosure. The method shown in fig. 1 may include step 101, step 102, step 103 and step 104, which are described separately below.

Step 101, obtaining a log file corresponding to a test environment.

It should be noted that before the service system is on line, the integrated environment builder in fig. 2 may be used to build a test environment, and perform a test on the service system based on the test environment, and in addition, the related test information may be stored in a log file corresponding to the test environment. In this way, in step 101, the log file corresponding to the test environment can be directly obtained from the stored information.

102, generating N SQL sentences according to the analysis result of the log file; wherein N is an integer greater than or equal to 2.

In step 102, the log file may be parsed to obtain a parsing result of the log file, and then, the N SQL statements may be automatically generated according to the parsing result of the log file. Here, N may be 2, 10, 20, 50, 70, or other integer greater than 2, which is not listed here.

And 103, determining N quality evaluation values corresponding to the N SQL sentences.

In step 103, N quality assessment values corresponding to the N SQL statements one to one may be obtained by analyzing and evaluating the N SQL statements, and a quality assessment value corresponding to any SQL statement may be used to represent the quality of the SQL statement.

Optionally, in step 103, a forward evaluation mode may be adopted to determine N quality evaluation values corresponding to N SQL statements, where the quality evaluation value corresponding to the SQL statement with higher quality is larger; alternatively, in step 103, a reverse evaluation mode may be adopted to determine N quality evaluation values corresponding to N SQL statements, where the quality evaluation value corresponding to the SQL statement with lower quality is larger.

And 104, sequencing and displaying the N SQL sentences according to the N quality evaluation values.

In step 103, under the condition that a forward evaluation manner is adopted to determine N quality evaluation values corresponding to N SQL statements, in step 104, when the N SQL statements are displayed in a sorted manner, the sorted position of the SQL statement with the larger quality evaluation value may be the front; in step 103, under the condition that a reverse evaluation manner is adopted to determine N quality evaluation values corresponding to N SQL statements, in step 104, when the N SQL statements are displayed in an ordered manner, the ordered position of the SQL statement with the smaller quality evaluation value may be located at the front.

On the basis of the embodiment shown in fig. 1, as shown in fig. 3, step 103 includes:

step 1031, performing Syntax analysis on the target SQL statement, and processing the target SQL statement into an Abstract Syntax Tree (AST); the target SQL statement is any one of N SQL statements;

step 1032, determining K query condition data corresponding to K preset query types according to the abstract syntax tree; k is an integer greater than or equal to 2, and the query condition data corresponding to any preset query type is used for representing whether the query of the preset query type exists in the target SQL statement;

1033, determining K deduction values corresponding to the K preset query types according to the K query condition data;

step 1034, calculating the sum of the K deduction values;

in step 1035, a quality assessment value corresponding to the target SQL statement is determined according to a difference between the first preset value and the sum.

Here, a parser may be called to parse the target SQL statement, including but not limited to Vitess, TiDB, etc. in fig. 2; wherein, Vitess is a database solution for deploying, expanding and managing large open source database instance clusters, and TiDB is an open source distributed relational database.

Here, K may be 2, 5, 7, 10, or other integer greater than 2, which is not listed here. Specifically, the K preset query types include, but are not limited to, the following query types: LIMIT queries with OFFSET, queries with NOT IN/NOT light, OR query index columns, Select type queries, LIMIT queries without using ORDER By.

Here, the first preset value may be 100.

In the embodiment of the present disclosure, the target SQL statement may be processed into an abstract syntax tree by using an open-source syntax parser such as Vitess and TiDB. It should be noted that the abstract syntax tree is an abstract representation of a source code syntax structure, and represents the syntax structure of a programming language in a tree form, each node on the tree represents a structure in the source code, and with reference to the abstract syntax tree, it can be very conveniently determined which query types of queries specifically exist in a target SQL statement, and accordingly, K query condition data corresponding to K preset query types one by one can be determined, so as to further determine K deduction values corresponding to the K preset query types.

In a specific embodiment, determining K score values corresponding to K preset query types according to K query condition data includes:

determining zero as a deduction value corresponding to the target preset query type under the condition that the query condition data corresponding to the target preset query type is used for representing that no query of the target preset query type exists in the target SQL statement; otherwise, determining the severity level corresponding to the target preset query type; the target preset query type is any one of K preset query types;

and under the condition that the severity grade corresponding to the target preset query type is higher than the preset severity grade, determining the deduction value corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and a second preset value.

Here, the severity level may be divided into S in total, the S severity levels are L0, L1, L2, L3, … …, L (S-1), respectively, in order of severity level from low to high, and the preset severity level may be L0.

Here, the second preset value may be 5, 7, 8, 10 or other values, which are not listed here.

In this embodiment, the correspondence between the preset query type and the severity level may be preset.

When the query condition data corresponding to the target preset query type is used for representing that the query of the target preset query type does not exist in the target SQL statement, it indicates that the quality of the target SQL statement is not affected by the query of the preset query type, and then zero may be directly determined as the deduction value corresponding to the target preset query type.

If the query condition data corresponding to the target preset query type is used for representing that the query of the target preset query type exists in the target SQL statement, which indicates that the quality of the target SQL statement may be affected by the query of the target preset query type, then the severity level corresponding to the target preset query type may be determined according to the preset corresponding relationship between the preset query type and the severity level.

After determining the severity level corresponding to the target preset query type, the severity level corresponding to the target preset query type may be compared with a preset severity level (e.g., L0).

If the comparison result indicates that the severity level corresponding to the target preset query type is the preset severity level, which indicates that the quality of the target SQL statement is affected by the query of the target preset query type, but the affected degree is very small, then zero may be determined as the deduction value corresponding to the target preset query type.

If the comparison result shows that the severity level corresponding to the target preset query type is higher than the preset severity level, which indicates that the quality of the target SQL statement is affected by the query of the target preset query type and the affected degree is not small, the deduction value corresponding to the target preset query type may be determined according to the level value of the severity level corresponding to the target preset query type and the second preset value.

Optionally, determining a score value corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and the second preset value, including:

Assuming that the second preset numerical value is 5, if the severity level corresponding to the target preset query type is L2, the severity level corresponding to the target preset query type has a numerical value of 2, and the deduction value corresponding to the target preset query type is 2 × 5 — 10; when the severity level corresponding to the target preset query type is L3, the severity level corresponding to the target preset query type has a level value of 3, and the deduction value corresponding to the target preset query type is 3 × 5 — 15.

It is easy to see that, the deduction value corresponding to the target preset query type can be very conveniently determined by introducing multiplication operation. It should be noted that, according to the actual situation, when determining the score corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and the second preset value, other types of operations may be introduced, such as an exponential operation, an addition operation, and the like, for example, performing an exponential operation using the grade value as a base number and the second preset value as an index, and using the operation result as the score corresponding to the target preset query type, which is also feasible.

In this embodiment, the deduction value corresponding to the target preset query type can be reasonably determined by referring to the query condition data corresponding to the target preset query type and the severity level corresponding to the target preset query type.

It should be noted that, in the manner provided above, K deduction values corresponding to K preset query types one to one may be determined according to K query condition data, and then, a sum of the K deduction values may be calculated, and a difference between a first preset value (for example, 100) and the sum is further calculated, so as to determine the quality assessment value of the target SQL statement according to the difference. Alternatively, the difference value may be used as the quality assessment value of the target SQL statement when the difference value is greater than zero, and zero may be used as the quality assessment value of the target SQL statement when the difference value is less than or equal to zero; alternatively, the difference value may be directly used as the quality assessment value of the target SQL statement regardless of whether the difference value is greater than or less than or equal to zero.

In the embodiment of the disclosure, the target SQL sentence is processed into the abstract syntax tree by calling the syntax parser, so that the K query condition data corresponding to the K preset query types can be conveniently, reliably and conveniently determined, the K score values corresponding to the K preset query types can be reasonably determined according to the K query condition data, and the quality evaluation value corresponding to the target SQL sentence can be conveniently determined by combining addition operation and subtraction operation, and can accurately and reliably represent the quality of the target SQL sentence.

On the basis of the embodiment shown in fig. 2, as shown in fig. 4, the method further includes:

105, screening R preset query types meeting preset conditions from the K preset query types; wherein, R is an integer greater than or equal to 1, and any preset query type meeting a preset condition means: the query condition data corresponding to the preset query type is used for representing the query of the preset query type in the target SQL statement;

106, displaying N controls corresponding to the N SQL sentences which are displayed in a sequencing mode;

step 107, under the condition of receiving input operation on a control corresponding to the target SQL statement, determining R severity levels corresponding to R preset query types and R query risk prediction information corresponding to the R preset query types;

step 108, displaying the interpretation data of the quality assessment value corresponding to the target SQL statement; wherein interpreting the data comprises: r preset query types, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types.

Here, the input operation to any control includes, but is not limited to, a click operation, a touch operation, and the like, a drag operation, and the like to the control.

Here, in addition to the preset correspondence between the preset query type and the severity level, the correspondence between the preset query type and the query risk prediction information may be preset; the query risk prediction information corresponding to any preset query type is used to indicate a risk that the query of the preset query type may cause, for example, the query risk prediction information corresponding to the Select type query may be: when the table structure changes, selecting all columns By using wildcards will cause the meaning and behavior of the query to change, possibly causing the query to return more data, and the query risk prediction information corresponding to LIMIT queries that do not use ORDER By may be: limit without ORDER By may result in non-deterministic results, depending on the query execution plan.

In the embodiment of the disclosure, referring to the abstract syntax tree, it can be very conveniently determined which query types specifically exist in the target SQL statement, and accordingly, which preset query types of the K preset query types satisfy the preset conditions can be determined, and all preset query types satisfying the preset conditions can be used as R preset query conditions screened in step 105.

And when the N SQL sentences are displayed in a sequencing mode according to the N quality evaluation values, N controls can be displayed corresponding to the N SQL sentences displayed in the sequencing mode, and the N controls can be in one-to-one correspondence with the N SQL sentences.

Under the condition that input operation on a control corresponding to a target SQL statement is received, R severity levels corresponding to R preset query types one to one can be determined according to the preset corresponding relation between the preset query types and the severity levels, and R query risk prediction information corresponding to the R preset query types one to one can also be determined according to the preset corresponding relation between the preset query types and the query risk prediction information. Then, interpretation data of the quality assessment value corresponding to the target SQL statement can be displayed, and the analysis data comprises R preset query types, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types.

Based on the embodiment shown in fig. 1, as shown in fig. 5, step 102 includes:

step 1021, obtaining M original log information from the analysis result of the log file; wherein M is an integer greater than or equal to N;

step 1022, all the M original log information are converted into an array format to generate corresponding M converted log information;

step 1023, screening N pieces of conversion log information of which the first designated positions are data for representing the type of the current conversion log information and the type of the SQL log information from the M pieces of conversion log information;

step 1024, respectively extracting SQL statements from respective second designated positions of the N pieces of conversion log information;

step 1025, formatting the N SQL statements extracted from the N pieces of conversion log information, respectively, to generate corresponding formatted N SQL statements.

In the embodiment of the present disclosure, the analysis result of the log file may include a large amount of log information, and the log information in the analysis result may be directly used as the M original log information in step 1021, or a part of log information may be screened from the log information in the analysis result, and the screened log information may be used as the M original log information in step 1021.

Next, the M pieces of original log information may be all converted into an array format to generate M pieces of converted log information corresponding to the M pieces of original log information one to one; the first designated location of any conversion log information may be data for characterizing the type of the conversion log information, and the second designated location of any conversion log information may be service data of the type.

Then, for the M pieces of conversion log information, it may be determined which first designated locations of the conversion log information are data for representing that the type of the current conversion log information is the SQL log type, and the determined conversion log information may be used as the N pieces of conversion log information screened in step 1023.

The types of the N pieces of conversion log information are all SQL log types, and the service data at the second designated positions of the N pieces of conversion log information may specifically be SQL statements, so the SQL statements may be extracted from the second designated positions of the N pieces of conversion log information, and thus the N pieces of SQL statements may be extracted from the N pieces of conversion log information in total. Then, formatting processing may be performed on the N SQL statements extracted from the N pieces of conversion log information, where the formatting processing includes, but is not limited to, removing blank characters, removing meaningless fields, and the like, so that the formatted N SQL statements may be generated, and the generated N SQL statements may be used as the subsequent N SQL statements that need to be displayed in an ordered manner.

In a specific example, some original log information obtained from the parsing result of the log file is as follows: 2021-10-2923:41:27| db-list | { "sql": select from ' bank _ signal ' where (' buss _ code { ' JY 0371012110069 ' and ' is _ valid `) '1') limit 1', "bind _ sql": select from ' bank _ signal ' where. It is easy to see that the original log information includes time, database name, used memory, Identity (ID) of process, machine (IP), and so on.

In specific implementation, the original log information in the upper segment may be divided according to the separator "|" in the original log information to convert the original log information into an array format, so as to generate corresponding converted log information. Consistent with the original log information, data at the second item (which may be used as the first designated location) of the conversion log information is "db-list", which means that the type of the conversion log information is the SQL type, but not other service types, then data at the third item (which may be used as the second designated location) of the conversion log information may be extracted, the extracted data is specifically an SQL statement in the JSON format, and by formatting the SQL statement in the JSON format, a subsequent SQL statement for ordered display may be generated, and the generated SQL statement is specifically: select from 'bank _ signs' where ('bussiness _ code)' JY0371012110070069 'and' is _ valid '1') limit 1; the JSON is called JavaScript Object Notation, is a JS Object and is a lightweight data exchange format.

In the embodiment of the disclosure, after the analysis result of the log file is obtained, N pieces of conversion log information which can be used for extracting the SQL statement can be obtained by converting the original log information in the analysis result into the array format and screening the conversion log information of which the type meets the requirement, and then, the N pieces of SQL statement used for sequencing display can be generated conveniently and reliably through the extraction processing and the formatting processing of the SQL statement. Therefore, the method and the device have the SQL screening function aiming at the log of the test environment, and the SQL statement can be automatically generated based on the screening function.

On the basis of the embodiment shown in fig. 1, as shown in fig. 6, step 101 includes:

step 1011, establishing remote connection with the test environment;

step 1012, determining a log path corresponding to the test environment;

and 1013, reading the log file corresponding to the test environment according to the log path through remote connection.

In embodiments of the present disclosure, a remote connection may be established with a test environment via a Linux (which is a type of computer operating system) system command. In addition, the corresponding relation between the test environment and the log path can be preset, so that the log path corresponding to the test environment can be determined according to the corresponding relation. Then, the log file can be searched according to the log path through remote connection, the searched log file can be used as the log file corresponding to the test environment, the log file corresponding to the test environment can be subsequently read, and the steps 102 to 104 can be executed for the read log file.

Therefore, in the embodiment of the disclosure, the log file corresponding to the test environment can be very conveniently and reliably acquired by establishing the remote connection and reading the file according to the log path.

displaying a candidate environment list comprising P corresponding candidate environments according to the P pieces of candidate environment information;

receiving the selection operation of a user on a target environment to be selected; the target candidate environment is any one of P candidate environments;

and taking the target candidate environment as a test environment.

Here, P may be 2, 3, 4, 5 or an integer greater than 5, which is not listed here.

Here, the candidate context information includes, but is not limited to, a master library domain (i.e., domain), a slave library domain, a port, a user name, a password, and the like.

It should be noted that P pieces of candidate environment information may be added to the database, so that the P pieces of candidate environment information may be obtained by reading the information in the database in the following step, so as to display a candidate environment list including the corresponding P pieces of candidate environments; the P candidate environments and the P candidate environment information may be in a one-to-one correspondence relationship.

Next, a selection operation of a user (e.g., an analyst) on a target candidate environment in the list of environments to be selected may be received, where the selection operation includes, but is not limited to, a touch input operation (e.g., a click operation, a press operation, etc.), a voice input operation, a keyboard input operation, and the like. And responding to the selection operation of the user, directly taking the target environment to be selected as a test environment, and further performing quality detection on the SQL statement aiming at the test environment.

Therefore, in the embodiment of the disclosure, through the acquisition of the information of the environment to be selected and the display of the environment to be selected list, the user can conveniently and reliably select the test environment which needs to perform the quality detection of the SQL statement according to the actual situation.

In an alternative example, before the business system is on-line, a plurality of candidate environments may be constructed, and various information of the candidate environments (equivalent to the candidate environment information in the foregoing) may be added to the database in batch through the add environment interface.

When a user (e.g., an analyst) enters the SQL evaluation system, the information of the candidate environments may be automatically read from the database, and accordingly, a list including a plurality of candidate environments (corresponding to the list of candidate environments in the above text) is presented to the analyst, specifically, referring to fig. 7, "test 4-nts. lianjia.com (nts)" to "test 11-nts. lianjia.com (nts)" in fig. 7 may serve as the 7 candidate environments.

After an analyst selects one candidate environment from the 7 candidate environments in fig. 7 according to actual requirements, the candidate environment may serve as a test environment, and then may automatically read SQL statements from the test environment, specifically, the test environment may be remotely connected through a Linux system command, then the log file may be read according to a log path, and then all SQL statements may be screened out by analyzing the log file and according to SQL matching rules (a specific screening manner refers to a manner of generating N formatted SQL statements in the embodiment shown in fig. 5).

Next, all the screened SQL statements may be evaluated by calling an evaluation interface of Security Organization Automation and Response (SOAR) in fig. 8, so as to obtain quality evaluation values corresponding to all the screened SQL statements (a specific evaluation manner refers to the relevant description in the embodiment shown in fig. 3). Alternatively, when evaluating any SQL statement, it may be scored on the basis of 100 points full, and scored until 0 points, wherein the severity level L0 is not scored, the severity level L1 is scored 5 points, the severity level L2 is scored 10 points, and so on.

Then, the vue-markdown component located at the front end in fig. 8 may be used for processing, so as to subsequently generate and output a page in hypertext Markup Language (html) format, and all screened SQL statements may be displayed in a sorted manner according to the sequence of the corresponding quality assessment values from large to small; vue is a progressive framework for constructing a user interface, markdown is a markup language that can be written using a conventional text editor.

Alternatively, the ordering effect of a page in html format may be as shown in fig. 9. It should be noted that, as shown in fig. 9, a "view SQL detail" may be displayed on the right of each SQL statement, each "view SQL detail" may be used as a control displayed corresponding to the current SQL statement, and if an analyst clicks a certain control, the interpretation data of the SQL statement corresponding to the control may be displayed.

Assuming that the SQL statement corresponding to the control clicked by the analyst is a selection role, from apps left join roles on roles, apps _ id is apps _ app _ id, the interpretation data may specifically refer to fig. 10, "Select type query" at the bottom of fig. 10 is a preset query type, "L1" at the bottom of fig. 10 is a severity level corresponding to the preset query type, "when the table structure changes, selecting all columns using wildcards at the bottom of fig. 10 will cause the meaning and behavior of the query to change, and may cause the query to return more data" as query risk prediction information corresponding to the preset query type.

In summary, the embodiments of the present disclosure can automatically generate SQL statements from a large-scale test environment, automatically perform quality evaluation on the SQL statements, and perform sequencing display on the SQL statements according to evaluation results, so that analysts can perform special analysis and optimization, which is beneficial to reduce the number of slow on-line SQL, and implement quantization of quality of query SQL.

Any of the SQL statement display methods provided by embodiments of the present disclosure may be performed by any suitable device having data processing capabilities, including but not limited to: terminal equipment, a server and the like. Alternatively, any SQL statement display method provided by the embodiments of the present disclosure may be executed by a processor, for example, the processor executes any SQL statement display method mentioned by the embodiments of the present disclosure by calling a corresponding instruction stored in a memory. And will not be described in detail below.

Exemplary devices

Fig. 11 is a schematic structural diagram of an SQL statement display apparatus according to an exemplary embodiment of the present disclosure, where the apparatus shown in fig. 11 includes a first obtaining module 1101, a generating module 1102, a first determining module 1103, and a first displaying module 1104.

A first obtaining module 1101, configured to obtain a log file corresponding to a test environment;

a generating module 1102, configured to generate N SQL statements according to an analysis result of the log file; wherein N is an integer greater than or equal to 2;

a first determining module 1103, configured to determine N quality assessment values corresponding to N SQL statements;

and the first display module 1104 is configured to display the N SQL statements in an ordered manner according to the N quality assessment values.

In an alternative example, the generating module 1102 includes:

the screening submodule is used for screening N pieces of conversion log information of which the first designated positions are all data which are used for representing the type of the current conversion log information and are in the SQL log type from the M pieces of conversion log information;

and the first processing submodule is used for respectively carrying out formatting processing on the N SQL sentences extracted from the N pieces of conversion log information so as to generate the corresponding N SQL sentences after the formatting processing.

In an optional example, the first obtaining module 1101 includes:

and the reading sub-module is used for reading the log file corresponding to the test environment according to the log path through remote connection.

In one optional example, the apparatus further comprises:

the second display module is used for displaying a candidate environment list comprising P corresponding candidate environments according to the P pieces of candidate environment information;

the receiving module is used for receiving the selection operation of a user on the target environment to be selected; the target candidate environment is any one of P candidate environments;

and the second determining module is used for taking the target candidate environment as a test environment.

In an optional example, the first determining module 1103 includes:

the second processing submodule is used for carrying out syntax analysis on the target SQL statement and processing the target SQL statement into an abstract syntax tree; the target SQL statement is any one of N SQL statements;

the third determining submodule is used for determining K deduction values corresponding to K preset query types according to the K query condition data;

the calculation submodule is used for calculating the sum of the K deduction values;

and the fourth determining submodule is used for determining the quality evaluation value corresponding to the target SQL statement according to the difference value between the first preset value and the sum.

In one optional example, the third determining sub-module includes:

the first determining unit is used for determining zero as a deduction value corresponding to the target preset query type under the condition that the query condition data corresponding to the target preset query type is used for representing that no query of the target preset query type exists in the target SQL statement; otherwise, determining the severity level corresponding to the target preset query type; the target preset query type is any one of K preset query types;

the second determining unit is used for determining zero as the deduction value corresponding to the target preset query type under the condition that the severity level corresponding to the target preset query type is the preset severity level;

and the third determining unit is used for determining the deduction value corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and the second preset value under the condition that the severity grade corresponding to the target preset query type is higher than the preset severity grade.

In one optional example, the apparatus further comprises:

the screening module is used for screening R preset query types meeting preset conditions from the K preset query types; wherein, R is an integer greater than or equal to 1, and any preset query type meeting a preset condition means: the query condition data corresponding to the preset query type is used for representing the query of the preset query type in the target SQL statement;

the third determining module is used for determining R severity levels corresponding to R preset query types and R query risk prediction information corresponding to the R preset query types under the condition of receiving input operation on a control corresponding to the target SQL statement;

the fourth display module is used for displaying the interpretation data of the quality assessment value corresponding to the target SQL statement; wherein interpreting the data comprises: r preset query types, R severity levels corresponding to the R preset query types, and R query risk prediction information corresponding to the R preset query types.

Exemplary electronic device

Next, an electronic apparatus according to an embodiment of the present disclosure is described with reference to fig. 12. The electronic device may be either or both of the first device and the second device, or a stand-alone device separate from them, which stand-alone device may communicate with the first device and the second device to receive the acquired input signals therefrom.

Fig. 12 illustrates a block diagram of an electronic device 1200 in accordance with an embodiment of the disclosure.

As shown in fig. 12, the electronic device 1200 includes one or more processors 1201 and memory 1202.

The processor 1201 may be a Central Processing Unit (CPU) or other form of processing unit having data processing capabilities and/or instruction execution capabilities, and may control other components in the electronic device 1200 to perform desired functions.

Memory 1202 may store one or more computer program products, and memory 1202 may include various forms of computer-readable storage media, such as volatile memory and/or non-volatile memory. The volatile memory may include, for example, Random Access Memory (RAM), cache memory (cache), and/or the like. The non-volatile memory may include, for example, Read Only Memory (ROM), hard disk, flash memory, etc. One or more computer program products may be stored on the computer-readable storage medium and executed by the processor 1201 to implement the SQL statement exposure methods of the various embodiments of the present disclosure described above and/or other desired functions.

In one example, the electronic device 1200 may further include: an input device 1203 and an output device 1204, which are interconnected by a bus system and/or other form of connection mechanism (not shown).

For example, when the electronic device 1200 is a first device or a second device, the input means 1203 may be a microphone or a microphone array. When the electronic device 1200 is a stand-alone device, the input means 1203 may be a communication network connector for receiving the acquired input signals from the first device and the second device.

The input means 1203 may also comprise, for example, a keyboard, a mouse, etc.

The output device 1204 can output various kinds of information to the outside. The output devices 1204 may include, for example, a display, speakers, a printer, and a communication network and remote output devices connected thereto, among others.

Of course, for simplicity, only some of the components of the electronic device 1200 relevant to the present disclosure are shown in fig. 12, omitting components such as buses, input/output interfaces, and the like. In addition, electronic device 1200 may include any other suitable components depending on the particular application.

Exemplary computer program product and computer-readable storage Medium

In addition to the above methods and apparatus, embodiments of the present disclosure may also be a computer program product comprising computer program instructions that, when executed by a processor, cause the processor to perform the steps in the SQL statement exposure method according to various embodiments of the present disclosure described in the "exemplary methods" section above of this specification.

The computer program product may write program code for carrying out operations for embodiments of the present disclosure in any combination of one or more programming languages, including an object oriented programming language such as Java, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computing device, partly on the user's device, as a stand-alone software package, partly on the user's computing device and partly on a remote computing device, or entirely on the remote computing device or server.

Embodiments of the present disclosure may also be a computer-readable storage medium having stored thereon computer program instructions that, when executed by a processor, cause the processor to perform the steps in the SQL statement display method according to various embodiments of the present disclosure described in the "exemplary methods" section above in this specification.

The computer-readable storage medium may take any combination of one or more readable media. The readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may include, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the foregoing. More specific examples (a non-exhaustive list) of the readable storage medium include: an electrical connection having one or more wires, a portable disk, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

The foregoing describes the general principles of the present disclosure in conjunction with specific embodiments, and it is noted that the advantages, effects, etc., presented in the present disclosure are exemplary only and not limiting, and should not be considered essential to the various embodiments of the present disclosure. The foregoing disclosure of specific details is for purpose of illustration and description only and is not intended to be limiting, since the foregoing disclosure is not intended to be exhaustive or to be limited to the precise details set forth.

The embodiments in the present specification are described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same or similar parts in the embodiments are referred to each other. The system embodiment basically corresponds to the method embodiment, so that the description is simpler, and the related points can be referred to the method embodiment.

The block diagrams of devices, apparatuses, systems involved in the present disclosure are only given as illustrative examples and are not intended to require or imply that the connections, arrangements, configurations, etc. must be made in the manner shown in the block diagrams. The devices, apparatuses, devices, systems may be connected, arranged, configured in any manner. Words such as "including," "comprising," "having," and the like are open-ended words that mean "including, but not limited to," and are used interchangeably therewith. The words "or" and "as used herein mean, and are used interchangeably with, the word" and/or, "unless the context clearly dictates otherwise. The word "such as" is used herein to mean, and is used interchangeably with, the phrase "such as but not limited to".

The methods and apparatus of the present disclosure may be implemented in a number of ways. For example, the methods and apparatus of the present disclosure may be implemented by software, hardware, firmware, or any combination of software, hardware, and firmware. The above-described order for the steps of the method is for illustration only, and the steps of the method of the present disclosure are not limited to the order specifically described above unless specifically stated otherwise. Further, in some embodiments, the present disclosure may also be embodied as programs recorded in a recording medium, the programs including machine-readable instructions for implementing the methods according to the present disclosure. Thus, the present disclosure also covers a recording medium storing a program for executing the method according to the present disclosure.

It is also noted that in the devices, apparatuses, and methods of the present disclosure, each component or step can be decomposed and/or recombined. These decompositions and/or recombinations are to be considered equivalents of the present disclosure.

The previous description of the disclosed aspects is provided to enable any person skilled in the art to make or use the present disclosure. Various modifications to these aspects will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other aspects without departing from the scope of the disclosure. Thus, the present disclosure is not intended to be limited to the aspects shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

The foregoing description has been presented for purposes of illustration and description. Furthermore, this description is not intended to limit embodiments of the disclosure to the form disclosed herein. While a number of example aspects and embodiments have been discussed above, those of skill in the art will recognize certain variations, modifications, alterations, additions and sub-combinations thereof.

Claims

1. A method for displaying Structured Query Language (SQL) statements is characterized by comprising the following steps:

acquiring a log file corresponding to a test environment;

determining N quality evaluation values corresponding to the N SQL sentences;

2. The method according to claim 1, wherein the generating N SQL statements according to the parsing result of the log file comprises:

3. The method of claim 1, wherein obtaining the log file corresponding to the test environment comprises:

establishing remote connection with a test environment;

determining a log path corresponding to the test environment;

4. The method of claim 1, wherein before obtaining the log file corresponding to the test environment, the method further comprises:

and taking the target candidate environment as the test environment.

5. The method according to claim 1, wherein the determining N quality assessment values corresponding to the N SQL statements comprises:

calculating the sum of the K deduction values;

6. The method according to claim 5, wherein the determining, according to the K pieces of query condition data, K pieces of score values corresponding to the K preset query types includes:

7. The method according to claim 6, wherein the determining the score value corresponding to the target preset query type according to the grade value of the severity grade corresponding to the target preset query type and a second preset value comprises:

8. The method of claim 5, further comprising:

9. An electronic device, comprising:

a memory for storing a computer program product;

a processor for executing a computer program product stored in the memory, and when executed, implementing the method of any of the preceding claims 1 to 8.

10. A computer readable storage medium having computer program instructions stored thereon, which when executed by a processor, implement the method of any of the preceding claims 1 to 8.