CN108388589A - A kind of device that database sql query statements automatically generate - Google Patents
A kind of device that database sql query statements automatically generate Download PDFInfo
- Publication number
- CN108388589A CN108388589A CN201810087204.1A CN201810087204A CN108388589A CN 108388589 A CN108388589 A CN 108388589A CN 201810087204 A CN201810087204 A CN 201810087204A CN 108388589 A CN108388589 A CN 108388589A
- Authority
- CN
- China
- Prior art keywords
- function
- information
- database
- type
- query
- 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.)
- Granted
Links
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 present invention provides a kind of device that database sql query statements automatically generate, including table and function processing module, the function information of the built-in function of table structural information and all supports for obtaining the table to be inquired from database, type matching is carried out to table structural information and function information, and the identical table structural information of type and function information are preserved；The total generation modules of query are generated the query statement of each needs for being randomly selected row and function from the table structural information and function information of preservation using random algorithm, and the query statement nesting of each needs are spliced into a complete sql query statement；Executable text generation module, for being exported complete sql query statements and generating an executable script file, and executes the executable script file of generation in the database.Device can realize that the sql query statements of batch automatically generate through the invention, can greatly improve sql and can perform the manufacture efficiency of script, and reduce human cost.
Description
Technical field
The present invention relates to database field, more particularly to the device that a database sql query statements automatically generate.
Background technology
Database (Database) be according to data structure come tissue, storage and manage data warehouse, it result from away from
Before modern more than 60 years, after the development of information technology and market, especially nineteen nineties, data management is not
It is only to store and manage data, and be transformed into the mode of the required various data managements of user again.
In recent years, with the development of big data, the type of database becomes more and more, sql grammers and application method
Also different, the requirement to function and performance is also higher and higher, and therefore, it is essential to carry out function and performance test to database
A link.For the prior art when carrying out the test of database function and performance, many or needs are by developer and test
Person oneself writes sql query statements and tests, and do not simply failed to comprising all sql query statements using manual compiling and
Case, and testing efficiency is extremely low, syntax error also easily occurs, cannot be satisfied the testing requirement to big data at all.When
So, in the prior art there is also there is some sql query statement Auto-Generation Tools, still, existing sql query statements are given birth to automatically
The all only simple main query statement of the sql sentences that it is generated at tool, simple sql sentences can not include all
Sql query functions and OLAP sentences, therefore, also easily cause the omission of test case.
Invention content
The technical problem to be solved in the present invention is to provide a kind of device that database sql query statements automatically generate, can
It realizes that the sql query statements of batch automatically generate, can greatly improve sql and can perform the manufacture efficiency of script, and reduce manpower
Cost.
The invention is realized in this way：A kind of device that database sql query statements automatically generate, described device includes table
With function processing module, the total generation modules of query and executable text generation module；
The table and function processing module for the table structural information of the table to be inquired of acquisition from database and own
The function information of the built-in function of support carries out type matching to table structural information and function information, and by the identical table of type
Structural information and function information are preserved；
The total generation modules of query, for using random algorithm from the table structural information and function information of preservation with
Machine chooses row and function generates the query statement of each needs, and the query statement nesting of each needs is spliced into one completely
Sql query statements；
The executable text generation module, for being exported complete sql query statements and generating an executable foot
This document, and the executable script file of generation is executed in the database.
Further, in the table and function processing module, the table knot that the table to be inquired is obtained from database
Structure information and the function information of the built-in function of all supports are specially：
From the type information for the row name and each row in table for obtaining the table to be inquired in database, and by the table knot of acquisition
Structure information editing is at the first text file；The function name of the built-in function of all supports is obtained from database, function returns
The type information of number of parameters and parameters that Value Types, function include, and the function information of acquisition is compiled second
Text file；The built-in function of all supports includes operator, aggregate function, OLTP functions or OLAP function.
Further, described that type matching carried out to table structural information and function information, and by the identical table structure of type
Information and function information are preserved specially：
The type information of each row in first text file and function return value type in the second text file are compared
To matching, and if type matching, preserve row and function return value；If type mismatches, row are not preserved and function returns
Return value；By the parameters that built-in function is included in the type information of each row in the first text file and the second text file
Type information be compared matching, and if the parameters that the type information of each row is included with built-in function type
The equal Corresponding matching of information will then preserve in the corresponding parameter list of the type information of each row filling built-in function；Otherwise just will not
It is preserved in the corresponding parameter list of type information filling built-in function of each row.
Further, the total generation modules of the query include select generation modules, from generation modules and
Condition generation modules；
The select generation modules, for random from the table structural information and function information of preservation using random algorithm
It chooses row and built-in function generates select query statements or generates the select subquery languages in each nested query sentence
Sentence；
The from generation modules, for generating from query statements using random algorithm or generating each nested query
From subquery sentences in sentence；
The condition generation modules, for generating condition query statements using random algorithm or generating each
Condition subquery sentences in a nested query sentence.
The invention has the advantages that：1, it can realize that the sql query statements of batch automatically generate by the device, it can be very big
The manufacture efficiency for improving sql and can perform script, and reduce human cost；Can include all functions of database when 2, testing
And type function, test case will not be omitted, test quality and database quality can be improved；3, it is applicable to most of traditional number
According to the test in library.
Description of the drawings
The present invention is further illustrated in conjunction with the embodiments with reference to the accompanying drawings.
Fig. 1 is a kind of functional block diagram for the device that database sql query statements automatically generate of the present invention.
Fig. 2 is a kind of execution flow diagram for the device that database sql query statements automatically generate of the present invention.
Specific implementation mode
It please refers to shown in Fig. 1 and Fig. 2, a kind of device that database sql query statements automatically generate of the present invention, described device
Including table and the total generation module of function processing module, query and executable text generation module；
The table and function processing module for the table structural information of the table to be inquired of acquisition from database and own
The function information of the built-in function of support carries out type matching to table structural information and function information, and by the identical table of type
Structural information and function information are preserved；
The total generation modules of query, for using random algorithm from the table structural information and function information of preservation with
Machine chooses row and function generates the query statement of each needs, and the query statement nesting of each needs is spliced into one completely
Sql query statements；
The executable text generation module, for being exported complete sql query statements and generating an executable foot
This document, and the executable script file of generation is executed in the database, you can realize the test to database function and performance.
Wherein,
In the table and function processing module, it is described from database obtain the table to be inquired table structural information and
The function information of the built-in function of all supports is specially：
From the type information for the row name and each row in table for obtaining the table to be inquired in database, and by the table knot of acquisition
Structure information editing is at the first text file；The function name of the built-in function of all supports is obtained from database, function returns
The type information of number of parameters and parameters that Value Types, function include, and the function information of acquisition is compiled second
Text file；The built-in function of all supports includes operator (such as plus sige, minus sign, equal sign, comma), aggregate function
(such as MAX, MIN, SUM, AVG), OLTP functions or OLAP function.
Further include the first text file of modification and the second text text when editing the first text file and the second text file
The format of part, so that the input of the format of the first text file and the second text file and sql query statement Auto-Generation Tools
Format is consistent；It needs to change the first text file and the second text file according to specified format, makes sql query statements
Auto-Generation Tool outputs and inputs to obtain unification.
It is described that type matching carried out to table structural information and function information, and by the identical table structural information of type and function
Information is preserved specially：
The type information of each row in first text file and function return value type in the second text file are compared
To matching, and if type matching, preserve row and function return value；If type mismatches, row are not preserved and function returns
Return value；By the parameters that built-in function is included in the type information of each row in the first text file and the second text file
Type information be compared matching, and if the parameters that the type information of each row is included with built-in function type
The equal Corresponding matching of information will then preserve in the corresponding parameter list of the type information of each row filling built-in function；Otherwise just will not
It is preserved in the corresponding parameter list of type information filling built-in function of each row.By the way that the row of type matching and function are protected
After depositing, sql query statements Auto-Generation Tool can carry out sql and inquire language using random algorithm to randomly select row and function
Sentence generates.
The total generation modules of query include that select generation modules, from generation modules and condition generate mould
Block；
The select generation modules, for random from the table structural information and function information of preservation using random algorithm
It chooses row and built-in function generates select query statements or generates the select subquery languages in each nested query sentence
Sentence；
In the specific implementation, the select generation modules include select subqueries unit and comprising function, column information
Funcall units, the select generation modules are specially：Internal element is randomly selected using random algorithm, and
If choosing select subquery units, recycles and carry out the total generation modules of query, and start to regenerate nested query language
Sentence illustrates to include subquery sentence that is, if choosing select subquery units, at this time with regard to needing to restart to carry out son
The generation of query statement；If choosing funcall units, handling function generation is randomly selected from funcall units
Select subquery sentences in select query statements or generation nested query sentence, enter from generation modules later.
The from generation modules, for generating from query statements using random algorithm or generating each nested query
From subquery sentences in sentence；
In the specific implementation, the from generation modules include from subqueries unit and comprising all kinds of table information
Joined_table units, the from generation modules are specially：Internal element is randomly selected using random algorithm, and
If choosing from subquery units, recycles and carries out the total generation modules of query, and start to regenerate nested query sentence,
I.e. if choosing from subquery units, illustrate to include subquery sentence, at this time with regard to needing to restart to carry out subquery
The generation of sentence；If choosing joined_table units, is randomly selected from joined_table units and choose table letters
Breath generates from query statements or generates the from subquery sentences in nested query sentence, enters condition later and gives birth to
At module.
The condition generation modules, for generating condition query statements using random algorithm or generating each
Condition subquery sentences in a nested query sentence；
In the specific implementation, the condition generation modules include condition subqueries unit and comprising all kinds of sentences
Formula judges that the bool_term units of information, the from generation modules are specially：Using random algorithm to internal element carry out with
Machine is chosen, and if choosing condition subquery units, recycles and carry out the total generation modules of query, and start to regenerate
Nested query sentence illustrates to include subquery sentence, just need at this time that is, if choosing condition subquery units
Restart the generation of progress subquery sentence；If choosing bool_term units, selected at random from bool_term units
It takes to choose and judges that information generates condition query statements or generates the condition subquery languages in nested query sentence
Sentence；
Meanwhile each of needs being looked into select generation modules, from generation modules and condition generation modules
It askes after sentence and subquery sentence generated, the total generation modules of query are by will be by the query statement and subquery of each needs
Nesting of statements is spliced into a complete sql query statement.
In conclusion the invention has the advantages that：1, it can realize that the sql query statements of batch are given birth to automatically by the device
At can greatly improve the manufacture efficiency that sql can perform script, and reduce human cost；Can include database when 2, testing
All functions and type function will not omit test case, test quality and database quality can be improved；3, it is applicable to big
The test of parts of traditional database.
Although specific embodiments of the present invention have been described above, those familiar with the art should manage
Solution, we are merely exemplary described specific embodiment, rather than for the restriction to the scope of the present invention, it is familiar with this
The technical staff in field modification and variation equivalent made by the spirit according to the present invention, should all cover the present invention's
In scope of the claimed protection.
Claims (4)
1. a kind of device that database sql query statements automatically generate, it is characterised in that：Described device includes table and function processing
The total generation module of module, query and executable text generation module；
The table and function processing module, for obtaining the table structural information of the table to be inquired and all supports from database
Built-in function function information, type matching carried out to table structural information and function information, and by the identical table structure of type
Information and function information are preserved；
The total generation modules of query, for being selected at random from the table structural information and function information of preservation using random algorithm
It takes row and function to generate the query statement of each needs, and the query statement nesting of each needs is spliced into a complete sql and is looked into
Ask sentence；
The executable text generation module, for complete sql query statements to be exported to and generated an executable script text
Part, and the executable script file of generation is executed in the database.
2. the device that a kind of database sql query statements according to claim 1 automatically generate, it is characterised in that：Institute
It states in table and function processing module, it is described out of, table structural information and all supports that obtain the table to be inquired in database
The function information for setting function is specially：
The type information of each row from the row name and table for obtaining the table to be inquired in database, and the table structure of acquisition is believed
Breath compiles the first text file；Function name, the function return value class of the built-in function of all supports are obtained from database
The type information of number of parameters and parameters that type, function include, and the function information of acquisition is compiled into the second text
File；The built-in function of all supports includes operator, aggregate function, OLTP functions or OLAP function.
3. the device that a kind of database sql query statements according to claim 2 automatically generate, it is characterised in that：It is described
Type matching is carried out to table structural information and function information, and the identical table structural information of type and function information are preserved
Specially：
The type information of each row in first text file is compared with function return value type in the second text file
Match, and if type matching, preserves row and function return value；If type mismatches, row and function return value are not preserved；
By the class for the parameters that built-in function is included in the type information of each row in the first text file and the second text file
Type information is compared matching, and if the parameters that the type information of each row is included with built-in function type information
Equal Corresponding matching will then preserve in the corresponding parameter list of the type information of each row filling built-in function；It otherwise just will be each
It is preserved in the corresponding parameter list of type information filling built-in function of row.
4. the device that a kind of database sql query statements according to claim 1 automatically generate, it is characterised in that：It is described
The total generation modules of query include select generation modules, from generation modules and condition generation modules；
The select generation modules, for being randomly selected from the table structural information and function information of preservation using random algorithm
Row and built-in function generate select query statements or generate the select subquery sentences in each nested query sentence；
The from generation modules, for generating from query statements using random algorithm or generating each nested query sentence
In from subquery sentences；
The condition generation modules, for generating condition query statements using random algorithm or generating each embedding
Cover the condition subquery sentences in query statement.
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 true CN108388589A (en) | 2018-08-10 |
| CN108388589B 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) |
Cited By (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 |
Cited By (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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108388589B (en) | 2022-09-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111061757B (en) | Language conversion method and device of database, electronic equipment and storage medium | |
| US9009140B2 (en) | Optimization of database query | |
| CN106970819B (en) | C program code specification checking device based on PRD L rule description language | |
| US7111025B2 (en) | Information retrieval system and method using index ANDing for improving performance | |
| CN110109940A (en) | By the figure traversal language conversion of functional expression at the structured query language of extension | |
| Sun et al. | Modeling data for business processes | |
| WO2011101206A1 (en) | A method and a system for searching for parts of a computer program which affects a given symbol | |
| CN109614315A (en) | A kind of automatic generation method and system of data synchronism detection use-case | |
| CN109710220B (en) | Relational database query method, relational database query device, relational database query equipment and storage medium | |
| CN113204571B (en) | SQL execution method and device related to write-in operation and storage medium | |
| Steer et al. | Cytosm: Declarative property graph queries without data migration | |
| CN108431766A (en) | Method and system for object-oriented/functional language to be mapped to database language | |
| US10133777B2 (en) | Eliminating redundancy when generating intermediate representation code | |
| CN108388589A (en) | A kind of device that database sql query statements automatically generate | |
| CN111177181A (en) | SQL text auditing method, system, storage medium and device | |
| Ghit et al. | Sparkfuzz: searching correctness regressions in modern query engines | |
| Panchenko et al. | Precise and scalable querying of syntactical source code patterns using sample code snippets and a database | |
| US10409815B2 (en) | SQLScript compilation tracing system | |
| Meurice et al. | Establishing referential integrity in legacy information systems-reality bites! | |
| CN108399196A (en) | The sql automatic execution methods and system of database sql sentence Auto-Generation Tools | |
| Zhang et al. | Application of micro-specialization to query evaluation operators | |
| Diestelkämper et al. | Debugging missing answers for spark queries over nested data with breadcrumb | |
| CN110580170A (en) | software performance risk identification method and device | |
| CN115544062A (en) | Apache call data blood relationship and influence analysis based method and system | |
| CN108460092A (en) | Include the sql query statements automatic generation method and system of database built-in function |
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 |