CN108388589B - Device for automatically generating sql query statement of database - Google Patents
Device for automatically generating sql query statement of database Download PDFInfo
- Publication number
- CN108388589B CN108388589B CN201810087204.1A CN201810087204A CN108388589B CN 108388589 B CN108388589 B CN 108388589B CN 201810087204 A CN201810087204 A CN 201810087204A CN 108388589 B CN108388589 B CN 108388589B
- Authority
- CN
- China
- Prior art keywords
- function
- information
- query
- generation module
- database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/36—Preventing errors by testing or debugging software
- G06F11/3668—Software testing
- G06F11/3672—Test management
- G06F11/3684—Test management for test design, e.g. generating new test cases
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computer Hardware Design (AREA)
- Quality & Reliability (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a device for automatically generating an sql query statement of a database, which comprises a table and function processing module, a query processing module and a query processing module, wherein the table and function processing module is used for acquiring table structure information of a table to be queried and function information of all supported built-in functions from the database, performing type matching on the table structure information and the function information, and storing the table structure information and the function information which are the same in type; the query total generation module is used for randomly selecting columns and functions from the stored table structure information and function information by using a random algorithm to generate all required query statements, and nesting and splicing all required query statements into a complete sql query statement; and the executable text generation module is used for outputting the complete sql query statement and generating an executable script file, and executing the generated executable script file in the database. The device can realize automatic generation of batch sql query sentences, greatly improve the manufacturing efficiency of sql executable scripts and reduce the labor cost.
Description
Technical Field
The invention relates to the field of databases, in particular to a device for automatically generating sql query statements of a database.
Background
Databases (databases) are warehouses that organize, store, and manage data according to data structures that have since sixty years ago, with the development of information technology and markets, particularly after the nineties of the twentieth century, data management has no longer been the only way to store and manage data, and has turned into the various ways of data management that users need.
In recent years, with the development of big data, the types of databases become more and more, the sql syntax and the use method are different, and the requirements on functions and performance are higher and higher, so that the function and performance test of the databases is an essential link. In the prior art, when the function and performance of the database are tested, developers and testers need to write sql query statements for testing, and manual writing cannot contain all the sql query statements and cases, is low in testing efficiency, is easy to generate grammatical errors, and cannot meet the testing requirement of large data at all. Certainly, some sql query statement automatic generation tools exist in the prior art, but the generated sql statements of the existing sql query statement automatic generation tools only have simple main query statements, and the simple sql statements cannot contain all sql query functions and OLAP statements, so that omission of test cases is easily caused.
Disclosure of Invention
The invention aims to solve the technical problem of providing a device for automatically generating sql query sentences of a database, which can realize the automatic generation of the sql query sentences in batches, greatly improve the manufacturing efficiency of sql executable scripts and reduce the labor cost.
The invention is realized by the following steps: a device for automatically generating sql query sentences of a database comprises a table and function processing module, a query total generation module and an executable text generation module;
the table and function processing module is used for acquiring the table structure information of the table to be inquired and the function information of all supported built-in functions from the database, performing type matching on the table structure information and the function information, and storing the table structure information and the function information which are the same in type;
the query total generation module is used for randomly selecting columns and functions from the stored table structure information and function information by using a random algorithm to generate all required query statements, and nesting and splicing all required query statements into a complete sql query statement;
and the executable text generation module is used for outputting the complete sql query statement and generating an executable script file, and executing the generated executable script file in the database.
Further, in the table and function processing module, the obtaining table structure information of the table to be queried and function information of all supported built-in functions from the database specifically includes:
acquiring the column name of a table to be inquired and the type information of each column in the table from a database, and editing the acquired table structure information into a first text file; acquiring function names, function return value types, parameter numbers contained in the functions and type information of all the parameters of all the supported built-in functions from a database, and editing the acquired function information into a second text file; the all supported built-in functions include operators, aggregation functions, OLTP functions, or OLAP functions.
Further, the performing type matching on the table structure information and the function information, and storing the table structure information and the function information with the same type specifically includes:
comparing and matching the type information of each column in the first text file with the type of the function return value in the second text file, and if the types are matched, saving the column and the function return value; if the types do not match, the column and function return values are not saved; comparing and matching the type information of each column in the first text file with the type information of each parameter contained in a built-in function in the second text file, and filling the type information of each column into a parameter table corresponding to the built-in function for storage if the type information of each column is correspondingly matched with the type information of each parameter contained in the built-in function; otherwise, the type information of each column is not filled in the parameter table corresponding to the built-in function for storage.
Furthermore, the query total generation module comprises a select generation module, a from generation module and a condition generation module;
the select generation module is used for randomly selecting columns and built-in functions from the stored table structure information and function information by using a random algorithm to generate select query statements or select sub-query statements in each nested query statement;
the from generating module is used for generating from query statements or generating from sub query statements in each nested query statement by using a random algorithm;
and the condition generating module is used for generating a condition query statement or generating a condition sub-query statement in each nested query statement by using a random algorithm.
The invention has the following advantages: 1. the device can realize automatic generation of the batch sql query sentences, greatly improve the manufacturing efficiency of the sql executable scripts and reduce the labor cost; 2. the test can include all functions and function types of the database, so that the test case is not omitted, and the test quality and the database quality can be improved; 3. the method is applicable to testing of most traditional databases.
Drawings
The invention will be further described with reference to the following examples and figures.
Fig. 1 is a schematic block diagram of an apparatus for automatically generating sql query statements according to the present invention.
Fig. 2 is a block diagram illustrating an execution flow of an apparatus for automatically generating sql query statements according to the present invention.
Detailed Description
Referring to fig. 1 and fig. 2, the apparatus for automatically generating sql query statements according to the present invention includes a table and function processing module, a query total generation module, and an executable text generation module;
the table and function processing module is used for acquiring the table structure information of the table to be inquired and the function information of all supported built-in functions from the database, performing type matching on the table structure information and the function information, and storing the table structure information and the function information which are the same in type;
the query total generation module is used for randomly selecting columns and functions from the stored table structure information and function information by using a random algorithm to generate all required query statements, and nesting and splicing all required query statements into a complete sql query statement;
the executable text generation module is used for outputting the complete sql query statement and generating an executable script file, and executing the generated executable script file in the database, so that the function and performance of the database can be tested.
Wherein the content of the first and second substances,
in the table and function processing module, the table structure information of the table to be queried and the function information of all supported built-in functions obtained from the database specifically include:
acquiring the column name of a table to be inquired and the type information of each column in the table from a database, and editing the acquired table structure information into a first text file; acquiring function names, function return value types, parameter numbers contained in the functions and type information of all the parameters of all the supported built-in functions from a database, and editing the acquired function information into a second text file; the built-in functions supported include operators (e.g., plus, minus, equal, comma, etc.), aggregation functions (e.g., MAX, MIN, SUM, AVG, etc.), OLTP functions, or OLAP functions.
When the first text file and the second text file are edited, the formats of the first text file and the second text file are modified so that the formats of the first text file and the second text file accord with the input format of the sql query sentence automatic generation tool; namely, the first text file and the second text file need to be modified according to a specified format, so that the input and the output of the sql query statement automatic generation tool are unified.
The performing type matching on the table structure information and the function information, and storing the table structure information and the function information with the same type specifically includes:
comparing and matching the type information of each column in the first text file with the type of the function return value in the second text file, and if the types are matched, storing the column and the function return value; if the types do not match, the column and function return values are not saved; comparing and matching the type information of each column in the first text file with the type information of each parameter contained in a built-in function in the second text file, and filling the type information of each column into a parameter table corresponding to the built-in function for storage if the type information of each column is correspondingly matched with the type information of each parameter contained in the built-in function; otherwise, the type information of each column is not filled in the parameter table corresponding to the built-in function for storage. After the columns and the functions with the matched types are stored, the automatic sql query statement generation tool can randomly select the columns and the functions by using a random algorithm to generate the sql query statement.
The query total generation module comprises a select generation module, a from generation module and a condition generation module;
the select generation module is used for randomly selecting columns and built-in functions from the stored table structure information and function information by using a random algorithm to generate select query statements or select sub-query statements in each nested query statement;
in specific implementation, the select generation module includes a select sub-query unit and a funcall unit including function and column information, and the select generation module specifically includes: randomly selecting the internal units by using a random algorithm, circularly performing a query total generation module if a select sub-query unit is selected, and starting to regenerate the nested query statement, namely if the select sub-query unit is selected, indicating that the nested query statement comprises the sub-query statement, and then restarting to generate the sub-query statement; if the funcall unit is selected, an operation function is randomly selected from the funcall unit to generate a select query statement or a select sub-query statement in a nested query statement, and then the operation function enters a from generation module.
The from generating module is used for generating from query statements or generating from sub query statements in each nested query statement by using a random algorithm;
in specific implementation, the from generation module includes a from sub query unit and a joined _ table unit containing various types of table information, and the from generation module specifically includes: randomly selecting the internal units by using a random algorithm, circularly performing a query total generation module if a from sub-query unit is selected, and starting to regenerate the nested query statement, namely if the from sub-query unit is selected, indicating that the nested query statement comprises the sub-query statement, and then restarting to generate the sub-query statement; if the joined _ table unit is selected, selecting table information randomly from the joined _ table unit to generate a from query statement or a from sub-query statement in a nested query statement, and then entering a condition generation module.
The condition generating module is used for generating a condition query statement or generating a condition sub-query statement in each nested query statement by using a random algorithm;
in specific implementation, the condition generating module includes a condition sub-query unit and a pool _ term unit containing various sentence judgment information, and the from generating module specifically is: randomly selecting the internal units by using a random algorithm, circularly performing a query total generation module if a condition sub-query unit is selected, and starting to regenerate the nested query statement, namely if the condition sub-query unit is selected, indicating that the nested query statement comprises the sub-query statement, and then restarting to generate the sub-query statement; if the bol _ term unit is selected, randomly selecting and selecting judgment information from the bol _ term unit to generate a condition query statement or a condition sub-query statement in a nested query statement;
meanwhile, after the select generation module, the from generation module and the condition generation module generate all the required query statements and sub-query statements, the query total generation module nests and splices all the required query statements and sub-query statements into a complete sql query statement.
In summary, the invention has the following advantages: 1. by the device, batch sql query sentences can be automatically generated, the manufacturing efficiency of sql executable scripts can be greatly improved, and the labor cost is reduced; 2. the test can include all functions and function types of the database, so that the test case is not omitted, and the test quality and the database quality can be improved; 3. the method is applicable to testing of most traditional databases.
Although specific embodiments of the invention have been described above, it will be understood by those skilled in the art that the specific embodiments described are illustrative only and are not limiting upon the scope of the invention, and that equivalent modifications and variations can be made by those skilled in the art without departing from the spirit of the invention, which is to be limited only by the appended claims.
Claims (2)
1. An apparatus for automatically generating sql query statements, comprising: the device comprises a table and function processing module, a query total generation module and an executable text generation module;
the table and function processing module is used for acquiring the table structure information of the table to be inquired and the function information of all supported built-in functions from the database, performing type matching on the table structure information and the function information, and storing the table structure information and the function information which are the same in type;
the table structure information of the table to be queried and the function information of all supported built-in functions obtained from the database are specifically as follows:
acquiring the column name of a table to be inquired and the type information of each column in the table from a database, and editing the acquired table structure information into a first text file; acquiring function names, function return value types, parameter numbers contained in the functions and type information of all the parameters of all the supported built-in functions from a database, and editing the acquired function information into a second text file; all supported built-in functions comprise an operator, an aggregation function, an OLTP function or an OLAP function;
the query total generation module is used for randomly selecting columns and functions from the stored table structure information and function information by using a random algorithm to generate all required query statements, and nesting and splicing all required query statements into a complete sql query statement;
the executable text generation module is used for outputting the complete sql query statement and generating an executable script file, and executing the generated executable script file in a database;
the query total generation module comprises a select generation module, a from generation module and a condition generation module;
the select generation module is used for randomly selecting columns and built-in functions from the stored table structure information and function information by using a random algorithm to generate select query statements or select sub-query statements in each nested query statement;
the from generating module is used for generating from query statements or generating from sub query statements in each nested query statement by using a random algorithm;
and the condition generating module is used for generating a condition query statement or generating a condition sub-query statement in each nested query statement by using a random algorithm.
2. The apparatus of claim 1, wherein the apparatus for automatically generating sql query statements comprises: the performing type matching on the table structure information and the function information, and storing the table structure information and the function information with the same type specifically includes:
comparing and matching the type information of each column in the first text file with the type of the function return value in the second text file, and if the types are matched, saving the column and the function return value; if the types do not match, the column and function return values are not saved; comparing and matching the type information of each column in the first text file with the type information of each parameter contained in a built-in function in the second text file, and filling the type information of each column into a parameter table corresponding to the built-in function for storage if the type information of each column is correspondingly matched with the type information of each parameter contained in the built-in function; otherwise, the type information of each column is not filled in the parameter table corresponding to the built-in function for storage.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810087204.1A CN108388589B (en) | 2018-01-30 | 2018-01-30 | Device for automatically generating sql query statement of database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810087204.1A CN108388589B (en) | 2018-01-30 | 2018-01-30 | Device for automatically generating sql query statement of database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108388589A CN108388589A (en) | 2018-08-10 |
| CN108388589B true CN108388589B (en) | 2022-09-16 |
Family
ID=63074070
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810087204.1A Active CN108388589B (en) | 2018-01-30 | 2018-01-30 | Device for automatically generating sql query statement of database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108388589B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112380180A (en) * | 2020-11-17 | 2021-02-19 | 平安普惠企业管理有限公司 | Data synchronization processing method, device, equipment and storage medium |
| CN112507011A (en) * | 2020-12-16 | 2021-03-16 | 江苏龙虎网信息科技股份有限公司 | Method for establishing program automation database model |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101464862A (en) * | 2007-12-21 | 2009-06-24 | 英业达股份有限公司 | SQL generating system and method |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method for automatic generation of structured query language (SQL) sentences |
| CN103744891A (en) * | 2013-12-23 | 2014-04-23 | 大唐软件技术股份有限公司 | Method and system for data query |
-
2018
- 2018-01-30 CN CN201810087204.1A patent/CN108388589B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101464862A (en) * | 2007-12-21 | 2009-06-24 | 英业达股份有限公司 | SQL generating system and method |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method for automatic generation of structured query language (SQL) sentences |
| CN103744891A (en) * | 2013-12-23 | 2014-04-23 | 大唐软件技术股份有限公司 | Method and system for data query |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108388589A (en) | 2018-08-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9009140B2 (en) | Optimization of database query | |
| US20070214104A1 (en) | Method and system for locking execution plan during database migration | |
| CN114625732B (en) | Query method and system based on structured query language SQL | |
| CN112988782B (en) | Hive-supported interactive query method and device and storage medium | |
| CN109614315B (en) | Automatic generation method and system of data synchronization test case | |
| CN104765731A (en) | Database query optimization method and equipment | |
| CN110688544A (en) | Method, device and storage medium for querying database | |
| CN108388589B (en) | Device for automatically generating sql query statement of database | |
| CN112685446A (en) | Complex SQL query method, device, processor and storage medium through Elasticissearch database | |
| US11934361B2 (en) | Automatic generation of a data model from a structured query language (SQL) statement | |
| CN106777278B (en) | Spark-based data processing method and device | |
| CN109408378B (en) | Test method and system for rapidly positioning SQL analysis errors under large data volume | |
| CN108399196B (en) | Automatic sql execution method and system of database sql statement automatic generation tool | |
| CN108008947B (en) | Intelligent prompting method and device for programming statement, server and storage medium | |
| CN108460092B (en) | Automatic generation method and system for sql query statement containing database built-in function | |
| CN113703777A (en) | Code generation method and device based on database table, storage medium and equipment | |
| CN113626464A (en) | Query support method and system based on stored data in ClickHouse database | |
| CN110580170B (en) | Method and device for identifying software performance risk | |
| WO2023086322A1 (en) | Late materialization of queried data in database cache | |
| CN115757479A (en) | Database query optimization method, machine-readable storage medium and computer device | |
| CN108153916B (en) | Method for realizing automatic generation tool of database sql query statement by using random algorithm | |
| CN115757174A (en) | Database difference detection method and device | |
| CN114925142A (en) | Multi-type database compatibility method, device, equipment and medium of ORM framework | |
| CN113448942B (en) | Database access method, device, equipment and storage medium | |
| CN108334318B (en) | Method for customizing automatic sql statement generation tool by self-defined depth breadth |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| CB02 | Change of applicant information | ||
| CB02 | Change of applicant information |
Address after: 350000 21 / F, building 5, f District, Fuzhou Software Park, 89 software Avenue, Gulou District, Fuzhou City, Fujian Province Applicant after: FUJIAN SINOREGAL SOFTWARE CO.,LTD. Address before: Floor 20-21, building 5, area F, Fuzhou Software Park, 89 software Avenue, Gulou District, Fuzhou City, Fujian Province 350000 Applicant before: FUJIAN SINOREGAL SOFTWARE CO.,LTD. |
|
| GR01 | Patent grant | ||
| GR01 | Patent grant |