CN101620606A - The method and system of automatically generated data library inquiry - Google Patents

Abstract

The invention discloses a kind of method and system of automatically generated data library inquiry,, can generate the inquiry that is used for database automatically based on the predicate parameter instantiation by considering row group data dependence and the constraint of row collection number.The method that generates data base querying according to an embodiment of the invention comprises: receive input inquiry and the constraint of row collection number; And the local predicate parameter of input inquiry is carried out instantiation according to the row group data dependence of database, to generate the candidate test query that satisfies the constraint of row collection number.The step of described generation test query comprises step: for input inquiry is built with to query graph; Oriented query graph is traveled through to make up directed spanning tree; Directed spanning tree is traveled through to generate the intermediate result table; And the candidate test query that generates the constraint of satisfied row collection number according to the intermediate result table.

Description

The method and system of automatically generated data library inquiry

Technical field

The present invention relates to the measuring technology of database software system.More particularly, the present invention relates to the method and system of automatically generated data library inquiry, by considering row group data dependences (ColumnGroup Data Correlation) and row collection number constraint (Cardinality Constraint), can generate the inquiry that is used for database automatically based on predicate parameter instantiation (instantiation).

Background technology

Automatically generate the inquiry of the new function (such as the optimizer algorithm) be used for the test database engine and be used for the assessment data storehouse that to use the inquiry of the performance of (for example E-business applications) be a very discouraging job in practice.In many test situation, need design test inquiry very carefully, this often makes that test job is very arduous and expend the plenty of time.

To new design and/or function newly developed in database engine, carry out the test of performance and quality such as new optimizer algorithm, verify that improved relatively common method is to generate one group of test data and inquiry, relatively applies before the new function and the behavior of Database Systems afterwards then.Obviously, for given test database, reasonably testing scheme should be contained pattern and the various inquiries of feature with wide region.In addition, at fc-specific test FC purpose/scheme, wish very much to control the input (for example, parameter value, predicate filters, and alternative condition or the like) and the output (for example, intermediate queries result and row collect number or the like) of test query.But for the current equipment of industry and sphere of learning, the test query of obtaining expectation as mentioned above almost is impossible, and this is because the inquiry that is generated is independent of test database.As a result, the negative consequence that " trial-failure " brought of under most of situations, having to stand.

For the robustness that the performance and the test database of the application of assessment data storehouse are used, because many well-known advantages, such as efficient, dirigibility and security, the dynamic queries with parameter tags (parameter marker) or master variable (host variable) is used in therefore current application mostly.The use of this type of dynamic sql has proposed huge challenge to the data base optimization device based on cost, and as a result of may cause inquiring about potential performance degradation, and this is because often determine invalid access path.Because under the situation of operation parameter mark obfuscation search condition, only simply based on the kind of default value and rewriting (interpolation), conjecture work, the result can not distinguish effectively and the ineffective access path in the estimation of optimizer.Therefore, it is extremely important that test has the query performance that potential input changes.Yet,, test very difficulty of significant input value for all parameter tags, this is because all parameters all dynamically arrange between application run-time.Even when operation is used, test, not only significantly increase the complicacy and the fussy degree of test job, and be difficult to guarantee to probe into all main may situation (such as, maximum/minimum value, data skew and data dependence or the like).

Therefore, for the relevant test of data base management system (DBMS) (DBMS, database management system), be starved of the test query that can generate expectation with automatic and controllable mode.

Current industry and sphere of learning have been carried out some researchs at automatic generated query and have been proposed the corresponding method of generated query automatically.But, to inquire about automatically in the generation solution at these, great majority are not at the purpose of testing.Such as, in U.S. Pat 6,285, proposed a kind of user of permission in 998 and specified expectation value fast and preserve inquiry so that the querying method that re-uses at data base querying.Though can generate and easily re-use inquiry easily, this method can not solve the problem of test database performance.

In addition, generate the field,, have two kinds of main schemes from relevant for the aspect that concerns between test database and the test query in test query.First kind of scheme do not considered data and generated query, this type of solution can not be handled two kinds of situations discussed above, promptly, new design and/or function newly developed in database engine are carried out the robustness that the test of performance and quality and the performance that uses in the assessment data storehouse and test database are used, and this is because this type of solution all is designed to generate the inquiry of general purpose.For example, at J.Tuya, " the Mutating database queries " that M.J.Su á rez-Cabal and C.la Riva deliver, Journal of Information and SoftwareTechnology, 2007, the test concept of will suddenling change among the Elsevier is introduced in the DBMS test, by inquiry is generated the covering scope that one group of mutation factor is guaranteed the SQL test.But, all uncorrelated with the test data of bottom by the great majority inquiry that this method generates.As another example,, a kind of method that generates the mass efficient SQL statement has at short notice randomly been proposed in 112, US6,581,052, US6,826,558 and US7,007,007 in U.S. Pat 6,138.But this generation is to carry out in mode at random, therefore is difficult to the input and output of control test case.

Second kind of scheme is then by considering that test data generates inquiry, and still all there are many restrictions in actual applications in the solution that proposes at present.For example, at N.Bruno, " the Generating Queries with Cardinality Constraints forDBMS Testing " that S.Chaudhuri and D.Thomas deliver, IEEE Transactions on Knowledge and Data Engineering has proposed the method that a kind of generation has the inquiry of row collection number constraint in 2006.But, the hypothesis that this work but is mutually independent based on each predicate in the inquiry, this is very rare in actual applications, thereby also is difficult to obtain ideal results.

Summary of the invention

In view of the foregoing, the present invention is directed to the test relevant with DBMS a kind of method and system of automatically generated data library inquiry is provided, the strategy of its automatically generated data library inquiry is based on the parameter instantiation of each the local predicate in the given inquiry with the constraint of particular row collection number.

According to an aspect of the present invention, provide a kind of method that generates data base querying, comprise step: receive input inquiry and the constraint of row collection number; And the local predicate parameter of input inquiry is carried out instantiation according to the row group data dependence of database, to generate the candidate test query that satisfies the constraint of row collection number.

Preferably, the step of generation test query comprises step: for input inquiry is built with to query graph; Oriented query graph is traveled through to make up directed spanning tree; Directed spanning tree is traveled through to generate the intermediate result table; And the candidate test query that generates the constraint of satisfied row collection number according to the intermediate result table.

According to another aspect of the present invention, provide a kind of system that generates data base querying, comprising: oriented query graph makes up device, is used to input inquiry to be built with to query graph; Directed spanning tree makes up device, is used for oriented query graph is traveled through to make up directed spanning tree; The matrix maker is used for directed spanning tree is traveled through to generate the intermediate result table; And candidate's Query Builder, be used for generating the candidate test query that satisfies the constraint of row collection number according to the intermediate result table.

Preferably, closing according to referential integrity is that input inquiry makes up the oriented query graph with root summit; By depth-first traversal oriented query graph is traveled through to make up directed spanning tree; And directed spanning tree is traveled through to generate the intermediate result table by BFS (Breadth First Search).

According to a preferred embodiment of the present invention, the step that generates the intermediate result table travels through directed spanning tree according to hierarchical sequence, and comprise step: at the directed spanning tree traversal of each layer, the reference row of local predicate are as the row group of intermediate result table in the cascade input inquiry; By retrieval aggregated data information from database that the row class value is divided into groups; And each row class value of reservation all satisfies the row class value of row collection number constraint and write down row collection number in the intermediate result table in the intermediate result table.

According to a further advantageous embodiment of the invention, row collection number constraint c is the row collection number constraint c ' in tolerance interval, and described method also comprises basis The step that the candidate test query that is generated is estimated is therefrom to select the test query of one group of expectation.

In addition, according to one embodiment of present invention, local predicate comprises and equals predicate, one-sided predicate and bilateral predicate.

According to another preferred embodiment of the present invention, under the oriented query graph that makes up for input inquiry was situation more than a root summit, described method also comprised step: be built with after query graph, identifying abutment and bridge joint arc in the oriented query graph; After making up directed spanning tree, link related top in the different directed spanning trees by virtual arc, convert oriented spanning forest to will make up the constructed a plurality of directed spanning trees of directed spanning tree step; And in intermediate result table generation step, generate intermediate result at each directed spanning tree respectively, regard each intermediate result table as temporary table then, with virtual arc as the connecting relation between the directed spanning tree, with the final intermediate result table of further generation.

According to a further advantageous embodiment of the invention, inquiry comprises subquery, and if subquery be correlated subquery, be uncorrelated subquery with its decorrelation; Described method also comprised step before generating candidate test query: begin up to the outermost layer subquery from the subquery of bottommost layer, successively iteration generates the intermediate result table.

In addition, according to one embodiment of present invention, row collection number is constrained to the constraint of a plurality of row collection number, is respectively applied for each layer of directed spanning tree.

According to a further advantageous embodiment of the invention, be under the situation of the dark tree in a left side at directed spanning tree, according to the queried access plan connection come successively to generate the intermediate result table in proper order.

Method and system according to automatically generated data library inquiry of the present invention, by considering that the data in the target test database make the inquiry that is generated can satisfy the constraint of particular row collection number, this makes the user constraint of row collection number can be appointed as input, and the test query that can generate expectation thus is as the output that is used for the specific purpose test.

According to the method and system of automatically generated data library inquiry of the present invention, utilize the data of the table by (similar) outer key connecting to rely on and be built with, and and then generate oriented spanning forest or directed spanning tree to query graph.By traveling through oriented spanning forest or directed spanning tree, can from the target test database, retrieve the aggregated data information of row group of link according to the method and system in automatically generated data of the present invention storehouse, thereby make that the data base querying that generated and the correlativity of target test database are strengthened.

According to the method and system of automatically generated data library inquiry of the present invention, when retrieval aggregated data information, can face the data dependence of row group.That is to say that the reference row of the local predicate that will (show or one group of table for one) are linked as the row group, and from the target test database, search data and retrieval aggregated data information by dividing into groups to come according to the row class value.Like this, the situation of just having avoided each predicate of the very rare in actual applications inquiry of hypothesis to be mutually independent.

Method and system according to automatically generated data library inquiry of the present invention, carry out instantiation according to the aggregated data information that is retrieved by parameter value and generate last candidate's inquiry automatically, make that thus the resulting candidate's inquiry and the correlativity of target test database are strengthened the local predicate of input inquiry.

Therefore, method and system according to automatically generated data library inquiry of the present invention, not only can generate the inquiry of satisfying the constraint of particular row collection number automatically, but also come the parameter value of each local predicate in the replacement query by the significant data (value) that polymerization from the target test database is set, make that the correlativity of resulting inquiry and target test database is strengthened.

In addition, the present invention also is provided for realizing the computer program of the method for above-mentioned automatically generated data library inquiry.

In addition, the present invention also provides the computer program of computer-readable medium form at least, records the computer program code of the method that is used to realize above-mentioned automatically generated data library inquiry on it.

Description of drawings

With reference to below in conjunction with the explanation of accompanying drawing, can understand above and other purpose of the present invention, characteristics and advantage more easily to the embodiment of the invention.In the accompanying drawings, technical characterictic or parts identical or correspondence will adopt identical or corresponding Reference numeral to represent.In the accompanying drawing:

Fig. 1 illustrates the principle schematic according to the method and system of automatically generated data library inquiry of the present invention;

The SQL that Fig. 2 (a) illustrates initial query Q represents;

The SQL that Fig. 2 (b) illustrates the inquiry Q+ that considers transitive closure represents;

The SQL that Fig. 2 (c) illustrates newly-generated inquiry Q ' represents;

Fig. 3 illustrates the overview flow chart according to the method for automatically generated data library inquiry of the present invention;

Fig. 4 (a) illustrates oriented query graph G according to an embodiment of the invention _Q

Fig. 4 (b) illustrates the oriented query graph G shown in Fig. 4 (a) _QTravel through the directed spanning tree T that the back generates _Q

Fig. 4 (c) illustrates the directed spanning tree T shown in Fig. 4 (b) _QHierarchical tree structure T of equal value _Q

Fig. 5 (a) illustrates the oriented query graph with a plurality of summits;

Fig. 5 (b) illustrates the oriented query graph shown in Fig. 5 (a) is traveled through the oriented spanning forest that the back generates;

Fig. 5 (c) illustrates the oriented spanning forest after the conversion that the oriented spanning forest to Fig. 5 (b) carries out obtaining after the conversion;

Fig. 6 illustrates the directed spanning tree that constitutes with virtual vertex;

Fig. 7 illustrates the structure of the oriented spanning forest with the inquiry of three stratons;

Fig. 8 illustrates the queried access plan of being undertaken by the dark tree in a left side; And

Fig. 9 illustrates the overall block-diagram according to the system of automatically generated data library inquiry of the present invention.

Embodiment

Embodiments of the invention are described with reference to the accompanying drawings.Should be noted that for purpose clearly, omitted the parts that have nothing to do with the present invention, those of ordinary skills are known and the expression and the description of processing in accompanying drawing and the explanation.

With reference now to accompanying drawing,, particularly Fig. 1 at first describes concrete technical matters to be solved by this invention.Can simply the problem to be solved in the present invention be reduced the inquiry that generation has the constraint of particular row collection number at this.

As shown in Figure 1, method and system according to automatically generated data library inquiry of the present invention, given test database D, inquiry Q and user-defined row collection number are retrained C as input, inquire about Q as output by finding the parameter value that makes inquiry satisfy constraint C to generate one group of candidate according to predicate parameter instantiation principle by automatic inquiry generation module.Newly-generated this group candidate inquiry can be expressed as Q ' | each Q ' all satisfies constraint C}.

For an inquiry Q, its simple SQL statement statement (has been omitted clauses such as GROUP BY, ORDER BY) for simplicity at this shown in Fig. 2 (a), the SQL that initial query is shown represents, wherein S is the set of base table or polymerization row, T1, ..., Tn is the tabulation of reference table of inquiry Q, (1≤i≤n) is the local predicate set of table Ti and can be expressed as disjunctive normal form Ti.LG Ti.L _I1=p _I1∧ ... ∧ Ti.LC _Ik=p _Ik, each LC _IjBe local predicate Ti.LC _Ij=p _IjWith reference to row and p _IjBe that (1≤i≤n, 1≤j≤k), J connects the predicate set to correlation parameter.Here use Ti.LC to represent all set in the local predicate of Ti, use Ti.JC to represent all set in the connection predicate of Ti, and use Ti.J to represent the set of the connection predicate of all table Ti with reference to row with reference to row.Therefore, J=T1.J ∪ ... ∪ Tn.J.In some cases, the parameter p of predicate in the inquiry _IjMay not concrete value, but parameter tags or master variable, for example local predicate Ti.LC _Ij=p _IjCan be Ti.LC _Ij=? perhaps Ti.LC _Ij=: hv1.

In order to keep connecting relation and to avoid in processing procedure, omitting possible connection, need come to find its transitive closure inquiry Q+ by increasing implicit in logic connection at initial query Q.Fig. 2 (b) be the resulting transitive closure inquiry of the transmission Q+ that consider to connect SQL statement represent that wherein connecting predicate set J+ is the transitive closure that connects predicate set J.

Inquiry generation strategy of the present invention can be specifically described now and be, the given inquiry Q and its transitive closure Q+ that represent respectively for Fig. 2 (a) and Fig. 2 (b), automatically generate one group of test query that satisfies constraint C, the form of each newly-generated inquiry Q ' is similar to the form shown in Fig. 2 (c).More particularly, the parameter value of the local predicate by revising each table generates inquiry.Therefore, for each table Ti and initial local predicate Ti.L thereof of inquiry Q, the new local predicate set Ti.L ' that newly inquires about among the Q ' is turned to Ti.LC by parameter _I1=p _I1' ∧ ... ∧ Ti.LC _Ik=p _Ik', each parameter value p wherein _Ij' (1≤j≤k) method and system by the automatically generated data library inquiry of the present invention that below will describe in detail comes assignment.At this this processing procedure is called the predicate parameter instantiation.

In that local predicate carries out in the process of parameter instantiation at each, with N.Bruno above-mentioned, S.Chaudhuri and D.Thomas are at " Generating Queries withCardinality Constraints for DBMS Testing ", IEEE Transactions onKnowledge and Data Engineering, hypothesis difference that is mutually independent of each predicate in the very rare inquiry in the practical application in 2006 is considered the correlativity of row in inquiry generative process of the present invention.

For the ease of being expressly understood essence of the present invention and principle more, will describe in conjunction with a concrete application example in the following description.This example is that four tunnel under the IBM WebSphere Commerce environment connects inquiry Q, and is as follows:

SELECT?ATTRVALUE.SEQUENCE

′ATTRIBUTE.USAGE

′ATTRIBUTE.DESCRIPTION

′ATTRTYPE.OID

′CATENTRY.PARTNUMBER

′CATENTRY.URL

′CATENTRY.CATENTRY_ID

′CATENTRY.ONAUCTION

′CATENTRY.LASTUPDATE

′CATENTRY.MARKFORDELETE

′CATENTRY.STARTDATE

′CATENTRY.ENDDATE

FROM?ATTRVALUE

′ATTRIBUTE

′ATTRTYPE

′CATENTRY

WHERE?ATTRVALUE.STRINGVALUE＝？

AND?ATTRVALUE.NAME＝？

AND?ATTRIBUTE.QTYUNIT_ID＝？

AND?ATTRTYPE.DESCRIPTION＝？

AND?CATENTRY.FIELD4＝？

AND?ATTRVALUE.ATTRIBUTE_ID＝

ATTRIBUTE.ATTRIBUTE_ID

AND?ATTRVALUE.ATTRTYPE_ID＝

ATTRTYPE.ATTRTYPE_ID

AND?ATTRVALUE.CATENTRY_ID＝

CATENTRY.CATENTRY_ID

AND?ATTRIBUTE.CATENTRY_ID＝

CATENTRY.CATENTRY_ID；

In order to make the clear more succinct and outstanding central principle of the present invention of problem, utilize the symbolic interpretation mapping shown in the following table 1 that above-mentioned inquiry Q is carried out symbolism and represent.

Table 1 symbolic interpretation mapping table

Symbol	Searching elements
		??T1	??ATTRVALUE
??T2	??ATTRIBUTE
		??T3	??ATTRTYPE
??T4	??CATENTRY
		??T1.C1	??ATTRVALUE.STRINGVALUE
??T1.C8	??ATTRVALUE.NAME
		??T2.C5	??ATTRIBUTE.QTYUNIT_ID
??T3.C6	??ATTRIBUTE.QTYUNIT_ID
		??T4.C7	??CATENTRY.FIELD4
??T1.C2	??ATTRVALUE.ATTRIBUTE_ID
		??T1.C3	??ATTRVALUE.ATTRTYPE_ID
??T1.C4	??ATTRVALUE.CATENTRY_ID
		??T2.C2	??ATTRIBUTE.ATTRIBUTE_ID
??T2.C4	??ATTRIBUTE.CATENTRY_ID
		??T3.C3	??ATTRTYPE.ATTRTYPE_ID
??T4.C4	??CATENTRY.CATENTRY_ID
		??S	??ATTRVALUE.SEQUENCE ??′ATTRIBUTE.USAGE ??′ATTRIBUTE.DESCRIPTION ??′ATTRTYPE.OID ??′CATENTRY.PARTNUMBER ??′CATENTRY.URL ??′CATENTRY.CATENTRY_ID ??′CATENTRY.ONAUCTION ??′CATENTRY.LASTUPDATE ??′CATENTRY.MARKFORDELETE ??′CATENTRY.STARTDATE ??′CATENTRY.ENDDATE

According to the symbolic interpretation of last table 1, above-mentioned inquiry Q is carried out shown in SQL statement after the symbolism is expressed as follows:

SELECT?S

FROM??T1，T2，T3，T4

WHERE?T1.C1＝？AND

T1.C8＝？AND

T2.C5＝？AND

T3.C6＝？AND

T4.C7＝？AND

T1.C2＝T2.C2?AND

T1.C3＝T3.C3?AND

T1.C4＝T4.C4?AND

T2.C4＝T4.C4；

Correspondingly, shown in the SQL statement of its transitive closure inquiry Q+ is expressed as follows:

SELECT?S

FROM??T1，T2，T3，T4

WHERE?T1.C1＝？AND

T1.C8＝？AND

T2.C5＝？AND

T3.C6＝？AND

T4.C7＝？AND

T1.C2＝T2.C2?AND

T1.C4=T2.C4 AND

＜--transmit

T1.C3＝T3.C3?AND

T1.C4＝T4.C4?AND

T2.C4＝T4.C4；

Here, use symbol

The connection predicate that expression increases by transitive closure.

So far, after handling through above-mentioned simplification, problem becomes at inquiry Q how to generate one group of new inquiry Q ' that satisfies user-defined row collection number constraint C, be about in the above-mentioned inquiry predicate parameter "? " carry out generating the candidate after instantiation is handled and inquire about Q '.

Below in conjunction with accompanying drawing and at the method and system of above-mentioned example in detail automatically generated data library inquiry of the present invention.With reference to figure 3, the overview flow chart according to the method for automatically generated data library inquiry of the present invention is shown.

As shown in Figure 3, in step S31, be that given inquiry Q generates oriented query graph G at first by considering that (similar) referential integrity closes _Q

For inquiry Q, suppose G _Q=＜V _Q, A _QBe the digraph of its summit mark, wherein V _QBe vertex set, a table of each summit representative inquiry Q institute reference, A _QBe arc set＜Tu, Tv〉| Tu, Tv ∈ V _Q∧ Tu ≠ Tv}, each arc representative is based on the summit Tu of referential integrity and the connecting relation between the Tv.First coordinate Tu represents sublist or detail table, and second coordinate Tv represents parent table or master meter.Between father and son's attribute, always exist connecting relation with reflection referential integrity relation in this supposition.The direction of arc has then hinted the dependence between sub-attribute and the father's attribute.That is to say that father's attribute has determined its sub-attribute, and each value of sub-attribute always can find in parent table uniquely thus.In some cases, each is shown by similar outer key connecting, for example connects one to one.As another example, the value of the attribute of table is the subclass or the superset (superset) of value of the connection attribute of another table.All these connections can represented by oriented query graph of the present invention.

Usually, digraph G _QIn summit Tu by (k+1+m) tuple (lc ₁..., lc _k, jc ₀, jc ₁..., jc _m) mark element jc wherein ₀The connection father attribute (row) of expression Tu, each element jc _p(expression of 1≤p≤m) connects in the sub-attribute (row), and each element lc _i(the row in of the local predicate of the expression of 1≤i≤k) Tu.At this, by father's attribute is added asterisk ( ^*) and the row of local predicate are underlined (_) and with above-mentioned (k+1+m) tuple (lc ₁..., lc _k, jc ₀, jc ₁..., jc _m) be expressed as ( Lc ₁ ..., Lc _k , jc ₀ ^*, jc ₁..., jc _m), so that in query graph, clearly distinguish row in these three kinds of tables.

Fig. 4 (a) shows the oriented query graph of the example that provides above being constructed according to above-mentioned rule.As shown in Fig. 4 (a), T1.C1 and T1.C8, T2.C5, T3.C6, T4.C7 are respectively the row of the local predicate of table T1, T2, T3, T4, therefore add underscore in its bottom.T2.C2, T3.C3, T4.C4 connect father's attribute (row), thus add asterisk ( ^*) to show difference.Remaining T1.C2, T1.C3, T1.C4, T2.C4 connects sub-attribute (row).

Should be noted that, at oriented query graph G _QIn, if the in-degree id of a summit Tr (Tr) is 0, then this summit Tr is called figure G _QThe root summit.For root summit Tr, the tuple of its mark always ( Lc ₁ ..., Lc _k , jc ₁..., jc _m), show father's attribute that Tr is not referenced, therefore do not have jc ₀In the example in the above, T1 is the root summit.

In addition, for a summit Tf, if its out-degree od (Tf) is 0, then the tuple of its mark always ( Lc ₁ ..., Lc _k , jc ₀ ^*), this shows that Tf does not have sub-attribute, therefore lacks jc ₁..., and jc _mIn the example in the above, T3 and T4 are the summits of this type.

In step S31, made up oriented query graph G _QAfterwards, treatment scheme advances to step S32, at this by traversal queries figure G _QMake up directed spanning tree T _Q

In this step S32, according to one embodiment of present invention, (DFS, Depth First Search) travels through the oriented query graph G that generates in step S31 by depth-first traversal _Q, so that generate the directed spanning tree T that root is arranged _Q

According to one embodiment of present invention, be used to travel through oriented query graph G _QThe algorithm of depth-first traversal as follows:

The depth-first traversal algorithm

Input: oriented query graph G _QWith summit Tv

Output: directed tree T

dfs(G _Q，Tv){

visit(Tv)；

add?Tv?to?tree?T；

for?each?successor?Tw?of?Tv?do

if?Tw?is?unvisited{

dfs(G _Q，Tw)；

add?arc<Tv，Tw>to?tree?T；

}

return?T；

}

According to one embodiment of present invention, be used to generate directed spanning tree T _QAlgorithm as follows:

Be built with to spanning tree algorithm

Input: oriented query graph G _QAnd root summit T _r

Output: directed spanning tree (forest) T that root is arranged _Q

constructSpanningTree(G _Q，T _r){

tree?T _Q＝dfs(G _Q，T _r)；

return?T _Q；

}

According to depth-first traversal algorithm dfs{ recited above } and be built with to spanning tree algorithm constructSpanningTree{ the oriented query graph G shown in Fig. 4 (a) _QTravel through the directed spanning tree T that the back generates _QShown in Fig. 4 (b).Fig. 4 (c) is the equivalent representation of Fig. 4 (b), to the directed spanning tree T shown in Fig. 4 (b) _QAdjust, so that form hierarchical tree structure commonly used.

In step S32 by traversal queries figure G _QMade up directed spanning tree T _QAfterwards, treatment scheme advances to step S33, travels through directed spanning tree T at this by BFS (Breadth First Search) (BFS, Breadth First Search) _Q, so that iteration generates intermediate result M.

The top predicate parameter instantiation of mentioning is also mainly finished in this step.Make up temporary table M at each layer tree traversal, so that (hierarchical sequence according to tree may be the local predicate of a table to local predicate in the cascade inquiry, also may be the local predicate of one group of table) with reference to row as the row group of temporary table M, the relevant SQL statement of structure then is so that search data and by the row class value is divided into groups to retrieve aggregated data information from the target test database.

At first discuss how to make up above-mentioned middle temporary table M below, then it is expanded to a plurality of tables generally speaking at single table.

At first, consider the table T among the inquiry Q, have k local predicate T.LC ₁=p ₁∧ ... T.LC _k=p _kSituation, T.LC wherein _iBe row and the p of table T _iBe relevant parameters, 1≤i≤k.The present matrix table M of simulation n * (k+1), as shown in table 2, wherein n is the row group (T.LC that satisfies the collection number constraint C of nominated bank ₁..., T.LC _k) the quantity of different value.Here, with eigenwert M[v _H1..., v _Hk, card _h] (1≤h≤n) is defined as row group (T.LC ₁..., T.LC _k) a data set, and this means when value in selecting the h tuple generates test query each p _iTo be instantiated as v _Hi(1≤i≤k).Therefore, the local predicate of table T is turned to T.LC by parameter accurately ₁=v _H1∧ ... ∧ T.LC _k=v _HkValue card _hBe (the T.LC of row group ₁..., T.LC _k) h maximum row collection number in the data set.

Consider that the specified row collection number of user retrains C (elder generation considers that single row collection number retrains the situation of c, will be described in more detail below for a plurality of situations of going the constraint of collection number for the sake of brevity) here, only keeps the card that satisfies condition in middle table M _j〉=c (tuple of 1≤j≤n), and other tuples that will show T abandon.

Table 2 intermediate result table/matrix M

??#1	...	??#i	......	??#k	??#(k+1)
						??T.LC 1	...	??T.LC i	......	??T.LC k	??CARD
??v 11??v 21??... ??v h1??... ??... ??v n1	... ... ... ... ... ... ...	??v 1i??v 2i??... ??v hi??... ??... ??v ni	...... ...... ...... ...... ...... ...... ......	??v 1k??v 2k??... ??v hk??... ??... ??v nk	??card 1??card 2??... ??card h??... ??... ??card n

Above-mentioned matrix M at table T can be retrieved all satisfactory record and structures by following SQL statement:

SELECT?M.LC，M.CARD

FROM(

SELECT?T.LC，COUNT( ^＊)AS?CARD

FROM??T

WHERE?1＝1

GROUP?BY?T.LC?HAVING?COUNT( ^＊)＞＝C

ORDER?BY?CARD?DESC??)AS?M；

Like this, can regard constructed matrix M as temporary table, wherein n * (k+1) matrix M is that a row collection number with (k+1) degree of freedom and M is the table of n.At this, represent the row set (under the situation of single table, M.LC equals T.LC) in the local predicate of M with M.LC, with the set that M.L represents the local predicate of M, represent the row set of the connection predicate of M with M.JC.For the situation of single table, M.JC equals transitive closure and connects predicate set T.JC+.In fact, the row that will connect are not building up among the intermediate result M, and here definition row set M.JC only is used to preserve the information of the row of all connections.

To be generalized to more general situation to top situation below at single table.Consider now matrix M with n * (k+1) ₁As existing intermediate result table and also have one group of table in addition such as three table T _α, T _β, and T _γSituation.

For each tuple M ₁[v _H1..., v _Hk, card _h] (1≤h≤n), the inquiry below making up, wherein each local predicate M ₁.LC _i=p _iParameter p _iWith value v _HiCarried out instantiation.

SELECT?M.LC，M.CARD

FROM(

SELECT?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC，

COUNT( ^＊)??AS?CARD

FROM??M ₁，T _α，T _β，T _γ

WHERE??1＝1?AND

M ₁.LC ₁＝v ₁₁?AND

...

M ₁.LC _k＝v _1k?AND

M ₁.J

GROUP?BY?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC

HAVING?COUNT( ^＊)＞＝C

ORDER BY CARD DESC // first tuple

UNION

...

UNION

SELECT?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC，

COUNT( ^＊)??AS?CARD

FROM??M ₁，T _α，T _β，T _γ

WHERE?1＝1?AND

M ₁.LC ₁＝v _h1?AND

...

M ₁.LC _k＝v _hk?AND

M ₁.J

GROUP?BY?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC

HAVING?COUNT( ^＊)＞＝C

ORDER BY CARD DESC // h tuple

UNION

...

UNION

SELECT?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC，

COUNT( ^＊)??AS?CARD

FROM?M ₁，T _α，T _β，T _γ

WHERE?1＝1?AND

M ₁.LC ₁＝v _n1?AND

...

M ₁.LC _k＝v _nk?AND

M ₁.J

GROUP?BY?M ₁.LC，T _α.LC，T _β.LC，T _γ.LC

HAVING?COUNT( ^＊)＞＝C

ORDER BY CARD DESC // n tuple

)AS?M；

Resulting result will form (k+|T according to the inquiry of this structure _αLC|+|T _βLC|+|T _γLC|+1) dimension matrix, the row collection number of this matrix will be determined by the quantity of the qualified row of above-mentioned inquiry.The row set M.LC of the local predicate of M is M ₁.LC ∪ T _αLC ∪ T _βLC ∪ T _γLC, local predicate set M.L is M ₁.L ∪ T _αL ∪ T _βL ∪ T _γL, and the row set M.JC of connection predicate is M ₁.JC ∪ T _αJC+ ∪ T _βJC+ ∪ T _γJC+.

According to one embodiment of present invention, aforesaid operations is summed up as the method that generates the intermediate result matrix, is called genMatrix ().This method has three input parameters, intermediate result table M _In, one group of table T1 ..., the row collection number constraint c of Tn} and user's appointment, and this method will be returned new intermediate result M _OutAs output.In fact, this method will make up inquiry according to initial conditions, so that retrieve the aggregated data information of related column group from database.For single form T, more particularly, this method will be embodied as call genMatrix (Φ, T, c) so that retrieve result in the middle of it.

Total the above as can be seen, in this step S33, as input, (BFS) travels through this directed spanning tree according to hierarchical sequence with the directed spanning tree of oriented query graph, and iteration is called genMatrix () during the processing of each layer, so that generate intermediate result table M.

Should also be noted that to allow row collection number to be constrained to certain acceptable scope, and must as described abovely be an accurate number not necessarily.Under the situation of absolute deviation d (d 〉=0) of collection number constraint c and appointment of being expert at, allows final row collect number c ' size can [c-d, c+d] (c-d≤c '≤c+d) scope in.Like this, when calling genMatrix () the last time, the HAVING clause in the inquiry of corresponding structure will be modified to HAVING COUNT ( ^*) BETWEEN (c-d) AND (c+d).Certainly, the user also can oneself specify qualified row collection number c ' be [c, c+d] (c≤c '≤c+d).

Further describe processing among the step S33 in detail below in conjunction with the instantiation that above provides.Here with the directed spanning tree of Fig. 4 (c) as input, and supposition row collection number constraint c is 1000, deviation d is 100, and wishes that the row collection number scope that obtains at last is [1000,1100].On this basis, will generate the final intermediate result M that satisfies the constraint of row collection number by calling genMatrix () according to the traversal of following each level.

First level traversal generates intermediate result M as shown in table 3 below by calling genMatrix (Φ, T1,1000) ₁

Table 3 M ₁(T1)

????T1.C1	????T1.C8	??CARD
			????Brown	????Minor	??143139
????Black	????Major	??123136
			????Red	????Major	??79686

????Green	????Major	????56229
			????Blue	????Major	????23998
????Violet	????Minor	????9328
			????White	????Major	????1816
????Yellow	????Major	????1386

The relevant SQL statement of first level traversal can followingly make up:

SELECT?M ₁.LC，M ₁.CARD

FROM(

SELECT??T1.C1，T1.C8，COUNT( ^＊)?AS?CARD

FROM??T1

WHERE?1＝1

GROUP?BY?T1.C1，T1.C8

HAVING?COUNT( ^＊)＞＝1000

ORDER?BY?CARD?DESC)?AS?M ₁；

Second level traversal is by calling genMatrix (M ₁, T2, T3}, 1000) generation intermediate result M as shown in table 4 below ₂

Table 4 M ₂(T1, T2, T3)

??T1.C1	??T1.C8	??T2.C5	?T3.C6	????CARD
					?Brown	??Minor	??2020	?Alabama	????2056
?Brown	??Minor	??2020	?Maine	????1560
					?Brown	??Minor	??2020	?Arizona	????1260
?Black	??Major	??1098	?NewYork	????2105
					?Black	??Major	??4648	?Utah	????1325
?Black	??Major	??1098	?California	????1309
					?Red	??Major	??4648	?California	????1207
?Green	??Major	??1354	?New?Jersey	????1022

The relevant SQL statement of second level traversal can followingly make up (only showing the part SQL statement for the sake of brevity):

SELECT?M ₂.LC，M ₂.CARD

FROM(

SELECT?T1.C1，T1.C8，T2.C5，T3.C6，

COUNT( ^＊)?AS?CARD

FROM??T1，T2，T3

WHERE?1＝1??AND

T1.C1＝′Brown′AND

T1.C8＝′Minor′AND

T1.C2＝T2.C2?AND

T1.C4＝T2.C4?AND

T1.C3＝T3.C3

GROUP?BY?T1.C1，T1.C8，T2.C5，T3.C6

HAVING?COUNT( ^＊)＞＝1000

ORDER?BY?CARD?DESC

UNION

...

UNION

SELECT?T1.C1，T1.C8，T2.C5，T3.C6，

COUNT( ^＊)?AS?CARD

FROM??T1，T2，T3

WHERE?1＝1AND

T1.C1＝′Yellow′AND

T1.C8＝′Major′AND

T1.C2＝T2.C2AND

T1.C4＝T2.C4AND

T1.C3＝T3.C3

GROUP?BY?T1.C1，T1.C8，T2.C5，T3.C6

HAVING?COUNT( ^＊)＞＝1000

ORDER?BY?CARD?DESC)AS?M ₂；

The tri-layer traversal is by calling genMatrix (M ₂, T4, [1000,1100]) and generation intermediate result M as shown in table 5 below ₃

Table 5 M ₃(T1, T2, T3, T4, net result)

??T1.C1	??T1.C8	??T2.C5	??T3.C6	??T4.C7	??CARD
						??Brown	??Minor	??2020	??Alabama	??DK	??1056
??Brown	??Minor	??2020	??Maine	??MV	??1030
						??Brown	??Minor	??2020	??Alabama	??KM	??1000
??Black	??Major	??1098	??California	??WW	??1100
						??Black	??Major	??1098	??New?York	??WD	??1100
??Black	??Major	??1098	??New?York	??DH	??1005

The relevant SQL statement of tri-layer traversal can followingly make up (only showing the part SQL statement for the sake of brevity):

SELECT?M ₃.LC，M ₃.CARD

FROM(

SELECT?T1.C1，T1.C8，T2.C5，T3.C6，

T4.C7，COUNT( ^＊)AS?CARD

FROM???T1，T2，T3，T4

WHERE??1＝1?AND

T1.C1＝′Brown′AND

T1.C8＝′Minor′AND

T2.C5＝2020?AND

T3.C6＝′Alabama′AND

T2.C4＝T4.C4

GROUP?BY??T1.C1，T1.C8，T2.C5，T3.C6，

T4.C7?HAVING?COUNT( ^＊)BETWEEN

1000?AND?1100

ORDER?BY?CARD?DESC

UNION

...

UNION

SELECT?T1.C1，T1.C8，T2.C5，T3.C6，

T4.C7，COUNT( ^＊)AS?CARD

FROM???T1，T2，T3，T4

WHERE??1＝1?AND

T1.C1＝′Green′AND

T1.C8＝′Major′AND

T2.C5＝1354?AND

T3.C6＝′New?Jersey′AND

T2.C4＝T4.C4

GROUP?BY?T1.C1，T1.C8，T2.C5，T3.C6，

T4.C7?HAVING?COUNT( ^＊)BETWEEN

1000?AND?1100

ORDER?BY?CARD?DESC)?AS?M ₃；

Get back to Fig. 3 now, generate after the intermediate result M in step S33, treatment scheme advances to step S34, has judged whether to travel through all summits.If do not travel through all summits, then return step S33, continue traversal directed spanning tree T _Q, up to having traveled through all summits.

If judge to have traveled through all summits in step S34, and generated the final intermediate result M that satisfies final row collection number constraint c ', then treatment scheme advances to step S35, generates candidate test query Q ' according to final intermediate result M.Like this, connect inquiry Q and row collection number constraint c for the n road, can retrieve the row group that makes Q satisfy c (T1.LC ..., the qualified tuple of Tn.LC) all.Correspondingly, promptly can automatically generate candidate test query Q ' according to final intermediate result M by each parameter value of instantiation.

In addition, in the evaluation factor of this definition relative deviation, wherein as test case

(if c or c ' are 0, then at random it are substituted with 1).The user can select one group of subclass inquiry as desired end product from the candidate's test case that is generated according to relative deviation easily.

For the instantiation of above mentioning, in this step S35 according to the final intermediate result M shown in the table 5 ₃Can obtain candidate test query Q ' as shown in table 6 below.

Table 6 candidate test query

Should be noted that being the disjunctive normal form with " equaling " predicate with the local predicate of each table in the example of being discussed in the above is that example is described, i.e. T.LC ₁=p ₁∧ ... ∧ T.LC _k=p _kBut the present invention is not limited only to this, but comprises various forms of predicates, such as one-sided predicate (T.LC for example ₁≤ p ₁Or T.LC ₁〉=p ₁) and bilateral predicate (T.LC for example ₁BETWEEN p ₁ANDp ₂Or p ₁≤ T.LC ₁≤ p ₂).

For this situation, need make amendment so that support the scope predicate to above-described method genMatrix ().Be that example describes to have single table T, two one-sided scope predicates and the inquiry Q that collects number constraint c (deviation is not set for the sake of brevity) that goes below, its local predicate set T.L is expressed as T.LC ₁=p ₁∧ ... ∧ T.LC _K-2=p _K-2∧ T.LC _K-1≤ p _K-1∧ T.LC _k〉=p _kAt first, the SQL statement shown in below the structure is so that retrieve aggregation information from database.

SELECT?T.LC ₁，

...

T.LC _k-2，

T.LC _k-1，

T.LC _k，

COUNT( ^＊)AS?CARD

FROM??T

WHERE?1＝1

GROUP?BY?T.LC ₁，

...

T.LC _k-2，

T.LC _k-1，

T.LC _k

ORDER?BY?T.LC ₁，

...

T.LC _k-2，

T.LC _k-1???ASC，

T.LC _k??DESC，

CARD??????ASC?；

For the result of this SQL statement, it can be regarded as the matrix M of interim n * (k+1) _TConsider matrix M _TIn the rectangular area R of following y * (k+1) _T: for matrix M _TIn each the value m _IpAnd m _Jp(i ≠ j, 1≤p≤k-2) always have m for 1≤i≤y, 1≤j≤y _Ip=m _Jp, for matrix M _TIn each the value m _{I (k-1)}And m _{J (k-1)}(1≤i≤j≤y) always has m _{I (k-1)}≤ m _{J (k-1)}, and for matrix M _TIn each the value m _IkAnd m _Jk(1≤i≤j≤y), if m _{I (k-1)}=m _{J (k-1)}M is then always arranged _Ik〉=m _Jk

Following table 7 shows such rectangular area, for example, and T.LC ₁All values equal a, T.LC ₂All values equal b, T.LC _K-2All values equal z, T.LC _K-1All values have monotonic nature, u when si≤sj _Si≤ u _Sj, and at u _Si=u _SjCondition under T.LC _kAll values have monotonic nature, v when si≤sj _Si〉=v _Sj

Table 7 rectangular area R _T

At this rectangular area R _T, by using function

The value of (k+1) row (row CARD) is calculated as sy * 1 vector $A\left(\begin{array}{c}\mathrm{subsum}\left({\mathrm{card}}_{\mathrm{sl}}\right)\\ ...\\ \mathrm{subsum}\left({\mathrm{card}}_{\mathrm{si}}\right)\\ ...\\ \mathrm{subsum}\left({\mathrm{card}}_{\mathrm{sy}}\right)\end{array}\right),$ Replace matrix area R with this vector A then _T(k+1) row original value, shown in following table 8.

Rectangular area R after table 8 is replaced _T

In this revised rectangular area, abandoned subsum (card _SiThe tuple of)＜c is so that form new rectangular area R _STherefore, can infer when local predicate is instantiated as R _SThe capable k tuple of si R _S[a, b ..., z, u _Si, v _Si] time, subsum (card _Si) value can equal final row collection number constraint c ' more accurately.The correlation candidate inquiry Q ' that is generated at initial query Q is as follows, and the constraint c ' of its reality is greater than row collection number constraint c (Q ' satisfy c).

SELECT?S

FROM??T

WHERE?T.LC ₁＝a?AND

T.LC ₂＝b?AND

...

T.LC _k-2＝z??AND

T.LC _k-1≤u _si?AND

T.LC _k≥v _si；

In whole matrix M _TIn, genMatrix () method will be searched all such rectangular areas, call subsum () function respectively at each such zone, and abandon underproof tuple, unite all remaining tuples at last to generate new matrix M _SAs net result.

For other the bilateral scope predicate and the situation of a plurality of table inquiry, also can revise genMatrix () and generate final intermediate result matrix according to similar principle.

In addition, in some cases, may there be root summit in the oriented query graph that in step S31, generates more than one.Fig. 5 (a) illustrates and runs counter to 10 tunnel of root summit expectation and be connected with to query graph.When using a plurality of oriented query graph traveled through, will be built with to spanning forest, it comprises that one group of directed spanning tree that root is arranged that disconnects substitutes single directed spanning tree.The quantity of the directed spanning tree in the oriented spanning forest is decided by the quantity on the root summit in the oriented query graph usually.For example, for the oriented query graph shown in Fig. 5 (a), in the oriented spanning forest that makes up, there are two directed spanning trees, shown in Fig. 5 (b).As can be seen from the example, after the oriented query graph at a plurality of summits generates oriented spanning forest, may lose some potential connecting relation.Therefore, at this situation, must improve to contain this situation method shown in Figure 3.

At first, after step S31, increase substep S31 ⁺, so that identify abutment and bridge joint arc in the oriented query graph.Generally, abutment (be also referred to as cutting summit) and bridge joint limit are the notions in the non-directed graph, are used for the connection feature of presentation graphs.In an embodiment of the present invention, these notions are used the present invention is described in the digraph.G in oriented query graph _Q, consider three summit Tu, Tv and Tw, if there are two arc＜Tu, Tw〉and＜Tv, Tw 〉, and the in-degree id of Tw (Tw) 〉=2, but from Tu to Tv or from Tv to Tu, do not have the path, then summit Tw is defined as the abutment, and add Tw to the abutment set $N_Q\&SubsetEqual;V_Q.$ Simultaneously, at this with arc＜Tu, Tw〉and＜Tv, Tw〉be called the bridge joint arc and add them to bridge joint arc set B _Q=＜Ti, Tj〉| Ti ∈ V _Q∧ Tj ∈ N _Q∧ Ti ≠ Tj}.In the example shown in Fig. 5 (a), N _Q={ T2} and B _Q=＜T0, T2 〉, and＜T1, T2 〉,＜T6, T2〉and,＜T3, T2 〉.

Then in step S32, by oriented query graph G _QIn each root summit travel through oriented query graph G _QBe built with to spanning forest F _QMake up the method for directed spanning tree according to an embodiment of the invention in this concise and to the point description.At the oriented query graph shown in Fig. 5 (a), at the oriented spanning forest F that has made up shown in Fig. 5 (b) _QAfterwards, T0 and T2 are on a directed spanning tree, and T1, T3 and T6 set disconnection mutually for these two in the oriented spanning forest that is generated on another directed spanning tree.Therefore, at the connecting relation of in the process of spanning forest, having lost them that is built with of step S32.In order to keep these connecting relations exactly, need to increase substep S32 ⁺, so that convert the oriented spanning forest that in step S32, makes up to new oriented spanning forest by the related top that connects in the different directed spanning trees.By the special arc of a class, promptly " virtual arc " links these all connecting relations at this, and the with dashed lines arc is represented in oriented spanning forest.Shown in Fig. 5 (c), in this example, through the virtual arc after the oriented spanning forest conversion be the arc that dots＜T1, T2 〉,＜T6, T2,＜T3, T2〉}.The algorithm that carries out oriented spanning forest conversion is as follows.

" be built with " to the spanning forest algorithm

Input: oriented query graph G _QAnd root vertex set R _Q

Output: comprise one or more directed spanning tree T that root is arranged _QOriented spanning forest F _Q

constructSpanningForest(G _Q，R _Q){

forest?F _Q＝Φ；

for?each?root?vertex?r∈R _Q?do

tree?T _Q＝dfs(G _Q，r)；

F _Q＝F _Q∪T _Q；

return?F _Q；

}

" the oriented spanning forest algorithm of conversion "

Input: oriented spanning forest F _QAnd bridge joint arc set B _Q

Output: the oriented spanning forest F after the conversion _Q' and new bridge joint arc set B _Q'

transformSpanningForest(F _Q，B _Q){

for?each?arc<i，j>∈B _Q?do

if?arc<i，j>exists?in?any?of?spanning?tree?T _QR

B _Q＝B _Q-<i，j>；//remove?arc<i，j>from?B _Q

if?vertex?i∈spanning?tree?T _QR?and?vertex?j∈spanning?tree?T _QS(i≠j∧T _QR≠T _QS)

F _Q＝F _Q∪<i，j>；//add?virtual?arc<i，j>to?F _Q

return(F _Q，B _Q)；

}

At last, generate intermediate result at each directed spanning tree respectively in step S33, can regard each intermediate result as temporary table then, virtual arc is then represented the connecting relation between the directed spanning tree.Like this, as a virtual vertex, as shown in Figure 6, iteration is called genMatrix () till having traveled through all virtual vertexs in the directed spanning tree then with each intermediate result.

In addition, should also be noted that among in front the embodiment that being is that example describes with the inquiry that does not have subquery.But the present invention is not limited only to this, but also can be applied in the inquiry with subquery.Only pay close attention to specific subquery of its a certain class rather than whole inquiry though have the situation of subquery for great majority, can use method recited above thus and make up candidate's inquiry of satisfying the constraint of row collection number at such specific subquery, but the present invention also can make up candidate's inquiry of satisfying the constraint of row collection number at general inquiry rank rather than specific subquery.

For the inquiry that comprises uncorrelated subquery, can be from the subquery of bottommost layer, make up this subquery oriented query graph, make up its directed spanning tree and call genMatrix (), so that generate intermediate result at this subquery.Then, this intermediate result as virtual vertex, and is handled its last layer subquery by add virtual arc in oriented spanning forest.Like this, can begin the subquery of iterative processing all levels from the subquery of bottommost layer.Fig. 7 shows an example handling multi-level subquery.

For the inquiry that comprises correlated subquery, can be at first that these are relevant subquery decorrelation become incoherent subquery with them, and then uses said method of the present invention and get final product.Decorrelation method about correlated subquery, see also U.S. Pat 5,548,755, Praveen Seshadri, Hamid Pirahesh, " the Complex Query Decorrelation " of T.Y.Cliff Leung, InProceedings of 12th International Conference on Data Engineering (ICDE1996), 1996, and P.Seshadri, J.M.Hellerstein, H.Pirahesh, T.Y.CliffLeung, R.Ramakrishnan, D.Srivastava, P.J.Stuckey, " the Cost-based optimization for magic:algebra and implementation " of S.Sudarshan, InACM SIGMOD Record, relevant documents such as 1996 omit its detailed description at this.

Should also be noted that in addition and the single row collection number constraint c that above describes can be expanded to the constraint of a plurality of row collection number, close C with row collection manifold here and represent.Consider to have the directed spanning tree T of l layer _Q, can formulate a plurality of row collection number constraint C={c ₁..., c _l.Each row collection number constraint c _i(the size of the intermediate result when 1≤i≤l) is used for that control is called genMatrix () the i time during the tree traversal.For example, for the directed spanning tree among Fig. 4 (c), during traveling through, can be C={c with row collection number constraint qualification _Q1, c _Q2, c _Q3.More specifically, the constraint of single row collection number can be regarded as the special case of a plurality of row collection number constraint, only each row collection number constraint c _iAll equate each other.Correspondingly, for have r directed spanning tree, each sets T _Qk(1≤k≤r) has the oriented spanning forest F of kl layer _Q, can the collection number constraint C of nominated bank be { c then ₁₁..., c _1l..., c _K1..., c _Kl..., c _R1..., c _Rl, c ₁..., c _R-1.For example, for the example shown in Fig. 5 (b), can nominated bank's collection number constrain set be C={c _R1, c _R2, c _R3, c _S1, c _S2, c _S3, c _S4, c ₁.

By this way, can provide flexible way to retrain the size of controlling each intermediate result by specifying a plurality of row collection numbers.

In addition, be also to be noted that the queried access plan all is patterned into the dark tree in a left side usually, as shown in Figure 8.In this case, according to one embodiment of present invention, the order of calling genMatrix () then is to carry out in proper order according to the connection of access plan, rather than travels through according to hierarchical sequence recited above.With example shown in Figure 8 is example, and its connection is T1-＞T2-＞T3 in proper order, at the collection number constraint C={c of this nominated bank ₁, c ₂, c ₃, c ₄, c ₅Control the size of each intermediate result.

According to the connection order of access plan, for the dark tree in a left side shown in Figure 8, genMatrix () at first makes up following SQL statement, so that generate satisfied row collection number constraint c as shown in table 9 at table T1 ₁Intermediary matrix M ₁

SELECT?M ₁.LC，M ₁.CARD

FROM(

SELECT?T1.LC11，COUNT( ^＊)?AS?CARD

FROM??T1

WHERE?1＝1

GROUP?BY?T1.LC11??HAVING?COUNT( ^＊)＞＝C ₁

ORDER?BY?CARD?DESC)?AS?M ₁；

Table 9 M ₁(T1)

T1.LC11	?CARD
		u1	?card u1
...	?...
		ux	?card ux

Then, genMatrix () connects intermediate result M ₁Retrain c with table T2 to generate satisfied row collection number as shown in table 10 ₂Intermediate result M ₂, its corresponding SQL statement is as follows.

SELECT?M ₂.LC，M ₂.CARD

FROM(

SELECT?T1.LC11，T2.LC21，

COUNT( ^＊)?AS?CARD

FROM??T1，T2

WHERE?1＝1?AND

T1.LC11＝u1

T1.JC12＝T2.JC22

GROUP?BY?T1.LC11，T2.LC21

HAVING?COUNT( ^＊)＞＝C ₂

ORDER?BY?CARD?DESC??//u1

UNION

...

UNION

SELECT?T1.LC11，T2.LC21，

COUNT( ^＊)?AS?CARD

FROM??T1，T2

WHERE?1＝1?AND

T1.LC11＝ux

T1.JC12＝T2.JC22

GROUP?BY?T1.LC11，T2.LC21

HAVING?COUNT( ^＊)＞＝C ₂

ORDER?BY?CARD?DESC??//ux

)AS?M ₂；

Table 10 M ₂(T1, T2)

T1.LC11	?T2.LC21	?CARD
			?u1	?v1	?card uv1
?...	?...	?...

uy

vy

card uvy

Then, genMatrix () passes through T2.LC21 intermediate result M ₂In value carry out instantiation and come to make up following SQL statement at table T2 satisfy row collection number constraint c so that generate ₃Intermediate result M ₃, as shown in table 11.

SELECT?M ₃.LC，M ₃.CARD

FROM(

SELECT?T2.LC21，COUNT( ^＊)?AS?CARD

FROM???T2

WHERE??T2.LC21＝v1

GROUP?BY?T2.LC21?HAVING?COUNT( ^＊)＞＝C ₃

ORDER?BY?CARD?DESC??//v1

UNION

...

UNION

SELECT?T2.LC21，COUNT( ^＊)?AS?CARD

FROM???T2

WHERE??T2.LC21＝vy

GROUP?BY?T2.LC21?HAVING?COUNT( ^＊)＞＝C ₃

ORDER?BY?CARD?DESC??//vy

)?AS?M ₃；

Table 11 M ₃(T2)

??T2.LC21	????CARD
		????v1	????card v1
????...	????...
		????vz	????card vz

During association list T1 and T2, retrain c in order to satisfy row collection number ₃, need abandon the intermediate result matrix M ₂The value of middle T2.LC21 vi

Matrix M ₃The tuple of the arbitrary value of middle T2.LC21 vj.Thus, the intermediate result M after can obtaining deleting ₂', as shown in table 12 below.

Table 12 M ₂' (T1, T2)

????T1.LC11	????T2.LC21	????CARD
			????u1′	????v1′	????card uv1′
????...	????...	????...
			????uz′	????vz′	????card uvz′

Similarly, use intermediate result M ₂' association list T3 is so that make the result satisfy row collection number constraint c ₄And generate new intermediate result M ₄, as shown in table 13.Corresponding SQL statement is as follows:

SELECT?M ₄.LC，M ₄.CARD

FROM(

SELECT?T1.LC11，T2.LC21，T3.LC31，

T3.LC32，COUNT( ^＊)?AS?CARD

FROM???T1，T2，T3

WHERE??1＝1?AND

T1.LC11＝u1′??AND

T2.LC21＝v1′

GROUP?BY?T1.LC11，T2.LC21，T3.LC31，

T3.LC32?HAVING?COUNT( ^＊)＞＝C ₄

ORDER?BY?CARD?DESC???????//uv1′

UNION

...

UNION

SELECT?T1.LC11，T2.LC21，T3.LC31，

T3.LC32，COUNT( ^＊)?AS?CARD

FROM??T1，T2，T3

WHERE?1＝1?AND

T1.LC11＝uz′AND

T2.LC21＝vz′

GROUP?BY?T1.LC11，T2.LC21，T3.LC31，

T3.LC32?HAVING?COUNT( ^＊)＞＝C ₄

ORDER?BY?CARD?DESC????????//uvz′

)?AS?M ₄；

Table 13 M ₄(T1, T2, T3)

?T1.LC11	?T2.LC21	?T3.LC31	?T3.LC32	?CARD
					?u1″	?v1″	?ω1	?υ1	?card uvωυ1
?...	...	...	?...	?...
					?uw″	?vw″	?ωw	?υw	?card uvωυw

In order to satisfy row collection number constraint c ₅, genMatrix () uses intermediate result M ₄In value instantiation row group (T3.LC31, T3.LC32), thereby at the table T3 make up following SQL statement so that generate new intermediate result M ₅

SELECT?M ₅.LC，M ₅.CARD

FROM(

SELECT?T3.LC31，T3.LC32，

COUNT( ^＊)?AS?CARD

FROM??T3

WHERE?T3.LC31＝ω1?AND

T3.LC32＝u1

GROUP?BY?T3.LC31，T3.LC32

HAVING?COUNT( ^＊)＞＝C ₅

ORDER?BY?CARD?DESC?????????//(ω1，u1)

UNION

...

UNION

SELECT?T3.LC31，T3.LC32，

COUNT( ^＊)AS?CARD

FROM??T3

WHERE?T3.LC31＝ωw?AND

T3.LC32＝uw

GROUP?BY?T3.LC31，T3.LC32

HAVING?COUNT( ^＊)＞＝C ₅

ORDER?BY?CARD?DESC??????????//(ωw，uw)

)?AS?M ₅；

At last, abandon intermediate result M ₄Middle row group (T3.LC31, T3.LC32) value of (ω i, υ i) The intermediate result matrix M ₅(T3.LC31, the T3.LC32) tuple of the arbitrary value of (ω j, υ j) is so that the final intermediate result M that acquisition is deleted for middle row group ₄'.As can be seen, use this intermediate result M ₄' carrying out the predicate parameter instantiation can satisfy row collection number constrain set C={c ₁, c ₂, c ₃, c ₄, c ₅Requirement.

Below describe each embodiment of the method that realizes automatically generated data library inquiry of the present invention in conjunction with the accompanying drawings in detail, describe the system of automatically generated data library inquiry of the present invention below in conjunction with Fig. 9.

As shown in Figure 9, the system of automatically generated data library inquiry according to an embodiment of the invention mainly comprises query graph generation module 910, inquiry generation module 920 and test case generation module 930.

Query graph generation module 910 is used for the inquiry Q of input is analyzed, so that generate its oriented query graph and directed spanning tree.Query graph generation module 910 mainly comprises: oriented query graph makes up device 912, is used for the inquiry Q of input is built with to query graph; Directed spanning tree/forest makes up device 914, is used for making up the constructed oriented query graph of device according to oriented query graph and makes up directed spanning tree, perhaps is built with to spanning forest and to it and changes; Graph visualization device 916 is used for the oriented query graph that oriented query graph structure device 912 is made up and directed spanning tree/forest structure device 914 makes up and the directed spanning tree/forest of conversion carries out visualization display; And graph visualization device user interface 918, be used to realize mutual with the user.

Inquiry generation module 920 is used for directed spanning tree/forest that query graph generation module 910 is generated is traveled through so that generate the candidate test query that satisfies row collection number constraint condition.Inquiry generation module 920 mainly comprises: symbolism inquiry announcer 922 is used for input inquiry is converted into the inquiry that symbolism is represented; Matrix maker 924 is used for directed spanning tree/forest that query graph generation module 910 is generated is carried out BFS (Breadth First Search), makes up SQL statement so that iteration generates the intermediate result table; Predicate parameter instantiation unit 926, be used for during matrix maker 924 iteration generate the intermediate result table, according to the inferior intermediate result table that travels through of last layer the predicate parameter in the SQL statement of new structure is carried out instantiation and represent, thereby make matrix maker 924 generate the intermediate result table of final satisfied row collection number constraint condition; And candidate's Query Builder 928, be used for generating one group of candidate test query according to the final intermediate result that matrix maker 924 generates, if necessary, can select to export the preferred test query that satisfies the relative deviation requirement.

Wherein, symbolism inquiry announcer 922 is optional module, can carry out symbolism and directly handle the inquiry of importing input inquiry, also can after input inquiry being carried out the symbolism processing, send to query graph generation module 910, to make things convenient for the processing of query graph generation module.Certainly, when finally generating test query, anti-symbolism processing is carried out in the inquiry that symbolism should be represented, turns back to employed searching elements in the actual queries.

Test case generation module 930 generates test case according to the test query that inquiry generation module 920 is generated, and mainly comprises: candidate's query evaluation device 932 is used to estimate the test case candidate that inquiry generation module 920 is generated; Test case generator 934 is used for the evaluation result according to candidate's query evaluation device 932, for the user provides satisfactory test case; And test case generator user interface 936, be used for and user interactions.

More than corresponding steps among each embodiment of method of the concrete processing procedure of each module and unit and top automatically generated data library inquiry with reference to description of drawings similar, for the sake of brevity, omit its detailed description at this.

In addition, in database layer 950, provide all relevant informations, the row collection number constrained qualification of the test job load (group polling) of test query thesaurus 952 in order to preserve the user and provide and generated can reusable test case or the like.Certainly, in database layer 950, also preserve the test database 954 that is used to inquire about test.

In addition, have favorable expansibility according to the system of automatically generated data library inquiry of the present invention, can be integrated with other user instruments and other external modules 960 easily by data abstraction layer 940.For example, the technical scheme that provided of instrument such as the test data generating 962 in the external module 960, test automation instrument 964, performance testing tool 966, database testing tool 968 and each embodiment of the present invention has good compatibility.

Should also be noted that according to the method and system of automatically generated data library inquiry of the present invention and also support based on key word with based on the function of search of similarity.

Ultimate principle of the present invention has below been described in conjunction with specific embodiments, but, it is to be noted, for those of ordinary skill in the art, can understand the whole or any steps or the parts of method and apparatus of the present invention, can (comprise processor at any computing equipment, storage medium etc.) or in the network of computing equipment, with hardware, firmware, software or their combination are realized, this is that those of ordinary skills use their basic programming skill just can realize under the situation of having read explanation of the present invention, has therefore omitted detailed description here.

Therefore, based on above-mentioned understanding, purpose of the present invention can also realize by program of operation or batch processing on any messaging device.Described messaging device can be known common apparatus.Therefore, purpose of the present invention also can be only by providing the program product that comprises the program code of realizing described method or equipment to realize.That is to say that such program product also constitutes the present invention, and the storage medium that stores such program product also constitutes the present invention.Obviously, described storage medium can be any storage medium that is developed in any known storage medium or future, therefore also there is no need at this various storage mediums to be enumerated one by one.

In equipment of the present invention and method, obviously, each parts or each step can decompose and/or reconfigure.These decomposition and/or reconfigure and to be considered as equivalents of the present invention.And, carry out the step of above-mentioned series of processes and can order following the instructions naturally carry out in chronological order, but do not need necessarily to carry out according to time sequencing.Some step can walk abreast or carry out independently of one another.

Preferred implementation of the present invention has more than been described.Those of ordinary skill in the art knows that protection scope of the present invention is not limited to detail disclosed herein, and can have various variations and equivalents in spirit scope of the present invention.

Claims (20)

1. method that generates data base querying comprises step:

Receive input inquiry and the constraint of row collection number; And

Row group data dependence according to database carries out instantiation to the local predicate parameter of input inquiry, to generate the candidate test query that satisfies the constraint of row collection number.

2. method according to claim 1, the step that wherein generates test query comprises step:

For input inquiry is built with to query graph;

Oriented query graph is traveled through to make up directed spanning tree;

Directed spanning tree is traveled through to generate the intermediate result table; And

Generate the candidate test query that satisfies the constraint of row collection number according to the intermediate result table.

3. method according to claim 2, wherein:

Closing according to referential integrity is that input inquiry makes up the oriented query graph with root summit;

By depth-first traversal oriented query graph is traveled through to make up directed spanning tree; And

By BFS (Breadth First Search) directed spanning tree is traveled through to generate the intermediate result table.

4. method according to claim 3, the step that wherein generates the intermediate result table travels through directed spanning tree according to hierarchical sequence, and comprises step:

At the directed spanning tree traversal of each layer, the reference row of local predicate are as the row group of intermediate result table in the cascade input inquiry;

By retrieval aggregated data information from database that the row class value is divided into groups; And

Each row class value of reservation all satisfies the row class value of row collection number constraint and write down row collection number in the intermediate result table in the intermediate result table.

5. method according to claim 4, wherein row collection number constraint c is the row collection number constraint c ' in tolerance interval, and described method also comprises basis The step that the candidate test query that is generated is estimated is therefrom to select the test query of one group of expectation.

6. according to the arbitrary described method of claim 1 to 5, wherein local predicate comprises and equals predicate, one-sided predicate and bilateral predicate.

7. according to the arbitrary described method of claim 2 to 5, wherein under the oriented query graph that makes up for input inquiry was situation more than a root summit, described method also comprised step:

Be built with after query graph, identifying abutment and bridge joint arc in the oriented query graph;

After making up directed spanning tree, link related top in the different directed spanning trees by virtual arc, convert oriented spanning forest to will make up the constructed a plurality of directed spanning trees of directed spanning tree step; And

Generate in the step at the intermediate result table and to generate intermediate result at each directed spanning tree respectively, regard each intermediate result table as temporary table then, with virtual arc as the connecting relation between the directed spanning tree, with the final intermediate result table of further generation.

8. according to the arbitrary described method of claim 2 to 5, wherein inquiry comprises subquery, and if subquery be correlated subquery, be uncorrelated subquery with its decorrelation;

Described method also comprised step before generating candidate test query: begin up to the outermost layer subquery from the subquery of bottommost layer, successively iteration generates the intermediate result table.

9. according to the arbitrary described method of claim 2 to 5, wherein row collection number is constrained to the constraint of a plurality of row collection number, is respectively applied for each layer of directed spanning tree.

10. according to the arbitrary described method of claim 2 to 5, be under the situation of the dark tree in a left side wherein at directed spanning tree, according to the queried access plan connection come successively to generate the intermediate result table in proper order.

11. a system that generates data base querying comprises:

Oriented query graph makes up device, is used to input inquiry to be built with to query graph;

Directed spanning tree makes up device, is used for oriented query graph is traveled through to make up directed spanning tree;

The matrix maker is used for directed spanning tree is traveled through to generate the intermediate result table; And

Candidate's Query Builder is used for generating the candidate test query that satisfies the constraint of row collection number according to the intermediate result table.

12. system according to claim 11, wherein:

It is that input inquiry makes up the oriented query graph with root summit according to the referential integrity pass that oriented query graph makes up device;

Directed spanning tree makes up device and by depth-first traversal oriented query graph is traveled through to make up directed spanning tree; And

The matrix maker travels through to generate the intermediate result table directed spanning tree by BFS (Breadth First Search).

13. system according to claim 12, wherein the matrix maker travels through directed spanning tree according to hierarchical sequence, and:

At the directed spanning tree traversal of each layer, the reference row of local predicate are as the row group of intermediate result table in the cascade input inquiry;

By retrieval aggregated data information from database that the row class value is divided into groups; And

Each row class value of reservation all satisfies the row class value of row collection number constraint and write down row collection number in the intermediate result table in the intermediate result table.

14. system according to claim 13 also comprises candidate's query evaluation device, is used for basis

The candidate test query that candidate's Query Builder generates is estimated therefrom to select the test query of one group of expectation, and wherein c is the constraint of row collection number, and c ' is the row collection number constraint in tolerance interval.

15. according to the arbitrary described system of claim 11 to 14, wherein local predicate comprises and equals predicate, one-sided predicate and bilateral predicate.

16. according to the arbitrary described system of claim 11 to 14, wherein:

Making up device at oriented query graph is that the constructed oriented query graph of input inquiry is under the situation more than a root summit, and described oriented query graph makes up device and is being built with after query graph, identifies abutment and bridge joint arc in the oriented query graph;

Described directed spanning tree makes up device after making up directed spanning tree, links related top in the different directed spanning trees by virtual arc, converts oriented spanning forest to will make up the constructed a plurality of directed spanning trees of directed spanning tree step; And

Described matrix maker generates intermediate result at each directed spanning tree respectively, regards each intermediate result table as temporary table then, with virtual arc as the connecting relation between the directed spanning tree, with the final intermediate result table of further generation.

17. according to the arbitrary described system of claim 11 to 14, wherein inquiry comprises subquery, and if subquery be correlated subquery, be uncorrelated subquery with its decorrelation; And

Described matrix maker begins up to the outermost layer subquery from the subquery of bottommost layer, and successively iteration generates the intermediate result table.

18. according to the arbitrary described system of claim 11 to 14, wherein row collection number is constrained to the constraint of a plurality of row collection number, is respectively applied for each layer of directed spanning tree.

19. according to the arbitrary described system of claim 11 to 14, be under the situation of the dark tree in a left side wherein at directed spanning tree, described matrix maker according to the queried access plan connection come successively to generate the intermediate result table in proper order.

20., also comprise according to the arbitrary described system of claim 11 to 14:

The test query thesaurus, in order to relevant information, the row collection number constrained qualification of preserving input inquiry and generated can reusable test case.

Images