Method and apparatus for generating database queries from a meta-query pattern5664173Abstract A grammar, parsing method, and associated apparatus for automatically generating test commands to test an SQL database engine interface while reducing storage requirements and improving access time for such test commands as compared with prior test tools. The test tools and methods include a grammar for concise syntactic representation of a meta-query (also referred to as meta-language statement, query pattern, or query template). The meta-query defines an statement similar to the SQL language but includes query elements and query list elements used to generate a plurality of SQL test commands to be applied to the SQL database engine under test. Test commands are generated from the meta-query to reduce storage requirements of prior test methods. Query elements are variable space holders in the meta-query and are replaced by a value appropriate to the SQL database engine under test when the meta-query is used to generate test commands. Query list elements define a list of values to be inserted in place of the query list element when generating the test commands from the meta-query. Claims What is claimed is: Description FIELD OF THE INVENTION
TABLE 1
______________________________________
Query Element
Replacement Information
______________________________________
<qualifier>
The current qualifier for the database engine driver
under test (i.e. the ODBC connection option -
SQL.sub.-- CURRENT.sub.-- QUALIFIER)
<tableN> Name of a table in test data for the SQL command
(where N is a number from 1 through the number of
tables in the test data)
<columnN> Name of a column in a table in the test data for the
SQL command (where N is a number from 1 through
the number of columns in the associated table)
<alias> Name of an alias for a column in the test data for the
SQL command
<data> A constant data value for use in the SQL command
<column name>
Name of generated column (i.e. one that doesn't
currently exist in the created table and used in the
"ALTER TABLE" queries so that the column name
won't conflict with an existing column name)
<column def >
Data type of the <column name> element
______________________________________
The meta-language (query templates/query patterns) of the present invention also includes "query list elements" which, when used in a meta-language statement, cause the generation of a plurality of SQL commands; one for each element in the query element list. This feature of the meta-language permits the compact representation of a large set of test commands in a concise, single, meta-language statement. This representation of a collection of test commands is simpler to maintain or modify and requires significantly less storage than the methods employed in the past to test a database API. A query list element provides a list of alternate values to be used in generating test commands from the meta-language statement (query template). Each of the alternate values in the query list element is used to replace the query list element in the generation of one (or more) test commands. In other words, a query list element that indicates four alternate values will generate (at least) four test commands, (at least) one each for each of the four alternate values in the query list element. If multiple query list elements are included in a meta-language statement, then the query profiler (200 of FIG. 2) will generate a test command for each combination of the elements in all the query list elements of the statement. Query list elements are replaced in generation of the test command by the alternate values supplied in the query list element when test commands are generated to test the database API 122. The query list elements are comma separated values delimited by a pair of square braces (a "[" preceding the list and a "]" following the list). The following Table 2 provides exemplary query list elements presently contemplated in the best known mode of the present invention. One of ordinary skill in the art will readily recognize that this exemplary list may be extended to include other query list elements which hold the place of language elements in the generated SQL queries.
TABLE 2
______________________________________
Query List Element
Replacement Information
______________________________________
[=,<,<=,>,>=,!=,!<,!>
Generates eight test commands; one with each
] of the eight listed logical test (comparison)
operators (as used in the condition clause)
[*=,=*] Generates two test commands; one with each
of two Microsoft SQL Server syntax outer join
operators
[-,+,*,/,%] Generates five test commands: one with each
of the five listed arithmetic operators
[SQL.sub.-- DATE,
Generates two test commands: one with each
SQL.sub.-- TIME.sub.-- STAMP]
of the two listed standard data types
______________________________________
The following meta-language statement examples provide further clarification of the power and syntax of the meta-language for the specification of large numbers of test SQL commands. In particular it is to be noted that the meta-language may be applied to many SQL commands (not merely the "SELECT" command).
______________________________________
SELECT <tablel>.<column1> FROM <tablel>,<table2> WHERE
<table>.<column1> [=,<,<=,>,>=,!=,!<,!>] <table2>.<column1>
______________________________________
This exemplary meta-language statement generates eight queries selecting rows from column 1 of table 1 (in the test data) where the column 1 value in table 1 of each row compares using the selected one of eight comparison operators with the same row and column of table 2. The eight generated queries are:
______________________________________
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> = <table2>.<columnl>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> < <table2>.<columnl>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> <= <table2>.<column1>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> > <table2>.<column1>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> >= <table2>.<column1>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> != <table2>.<column1>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> !< <table2>.<column1>
SELECT <table1>.<column1> FROM <table1>,<table2>
WHERE
<table1>.<column1> !> <table2>.<column1>
______________________________________
In addition, the query profiler 200 of FIG. 2 will generate these eight test commands for all data types supported by the selected database engine driver (124, 126, or 128 of FIG. 2). For example, Microsoft Access version 2.0 supports 15 distinct data types. Therefore, this exemplary meta-language statement generates 8*15 or 120 test commands when testing the database API 122 in conjunction with a Microsoft Access database engine driver. As a further example, consider: CREATE INDEX <table 1 > ON <table 1 > ( <column name> ASC ) WITH IGNORE NUL L This exemplary meta-language statement generates a new table index with a column name appropriate to the data type currently being processed by the query profiler 200. As noted above, Microsoft Access, for example, supports 15 data types and therefore, this meta-language statement generates 15 SQL commands when testing the database API 122 in conjunction with the Microsoft Access database engine driver. QUERY PROFILER: Query profiler 200 of FIG. 2 is operable on a data processing system to parse the meta-language statements and to generate test SQL commands for application to the database API 122. FIG. 3 is a block diagram depicting a typical computing environment in which query profiler 200 operates. Data processing system 310 provides the central processing, memory, and mass storage components for operation of query profiler 200, database API 122, and database engine drivers 124 and 126. Database engine drivers 124 and 126 store and retrieve information on local disks 300 and 302. Data processing system 310 may be connected to other data processing systems 308 over network attachment 306. Additional database engine drivers 128 and local disks 304 may reside within the data processing system 308. Database API 122 may interact with a remote database engine driver 128 through any of several well known network computing architectures. Further, one of ordinary skill in the computing arts will readily recognize that the computing environment depicted in FIG. 3 is only exemplary of one such architecture in which the structures and methods of the present invention may operate. The present invention is equally applicable to computing environments without networked connections to other data processing system or to distributed computing environment utilizing other topological configurations or connectivity technologies. FIG. 4 is a flowchart depicting the methods of the present invention as implemented by the query profiler 200. Element 400 of FIG. 4 invokes functions in the database API (122 of FIG. 2) required to associate the test procedure with a particular database engine driver module under test (124, 126, or 126 of FIG. 2). Element 402 then generates test data in tables created and managed by the database engine driver under test. This test data is used by the selected database engine 124, 126, or 128 through the database API 122 at the direction of the query profiler 200 in its interpretation of the meta-language statements. Since the query profiler generates the test data, it can predict the expected result of each SQL command generated from the meta-language statements and applied to the database API and engine. The specific form of the generated tables is a matter of design choice made by the test engineering staff in creating the test procedures. One or more tables may be created and each table may have one or more columns as desired by the test engineers to adequately test the database API interface to the database engine driver. Elements 404 and 406 initialize for the Iooping functions performed by elements 408-420. The test SQL commands generated for testing the API interface to the engine are generated for each data type supported by the underlying database engine. Element 404 sets the variable "N" to the number of data types supported by the selected database engine. Element 406 loads all the query patterns from a text file in which they are stored. The query patterns are previously designed by the test engineers to compactly specify the voluminous test commands required to adequately test the interface between the database API and a database engine driver module. As discussed above, the query patterns are written in simple textual form in the syntax of the meta-language discussed above. Element 406 serves to read the text file storing the pre-defined query patterns in preparation for parsing the meta-language statements and generating the specified SQL commands therein. Elements 408-422 are repetitively operable for each data type supported by the selected database engine driver. Element 408 tests whether the counter variable "N" (indicating the number of supported data types) has been decremented to zero. On each iteration of the loop (elements 408-422), element 422 is operable to decrement the counter variable "N." Elements 410-420 are therefore operable to generate the test commands specified by all query patterns for a single data type supported by the selected database engine driver. Element 410 sets the variable "M" to the number of query patterns pre-defined by the test engineers in the text file. In other words, the number of records to be processed in the meta-language file. Each record provides another query pattern in the meta-language syntax described above. Each record is therefore processed in turn to generate all the test SQL commands required to test the database API in conjunction with the selected database engine driver. Elements 412-420 are repetitively operable for each record (meta-language statement or query pattern) retrieved from the text file. Element 412 tests whether the counter variable "M" (indicating the number of meta-language statements in the text file) has been decremented to zero. On each iteration of the loop (elements 412-420), element 420 is operable to decrement the counter variable "M." Elements 414-418 are therefore operable to generate the test commands specified a single query patterns for a single data type supported by the selected database engine driver. Element 414 parses the meta-language statement to process all query elements and query list elements. Parsing of the meta-language statement includes locating all query elements and replacing them by values appropriate to the particular data type presently being processed and as appropriate for the selected database engine driver. Additionally, the parsing process locates any query list elements in the meta-language statement and generates one SQL command for each element in the list. Each of the generated SQL commands are thereby generated by substitution of actual values for the variable elements of the meta-language statement. Element 416 then applies the SQL commands generated by element 414 to the database API 122. The SQL commands so applied are in turn transformed and transferred to the selected database engine driver 124, 126, or 128 of FIG. 2 for actual processing upon the test data stored on the mass storage devices (300, 302, and 304 of FIG. 3). Element 418 captures, records, and analyzes the results of the SQL command processing returned by the database engine driver. Processing of these results is discussed below in additional detail. As noted above, element 420 is next operable to decrement the Loop counter variable "M" and element 422 decrements the loop counter variable "N" to control the iterative looping of the method. When element 412 determines that all records in the meta-language text file have been processed, it returns control to element 422 to process another supported data type. Likewise, when element 408 determines that all supported data types have been processed, the method completes processing. FIGS. 5-7 combine to provide a flowchart providing additional detail of the operation of element 414 of FIG. 4 which generates all SQL commands from a single query template (meta-language statement). Element 500 of FIG. 5 places the query pattern (meta-language statement) to be parsed into a memory input buffer. Element 502 of FIG. 5 then initially invokes the reentrant parser to parse the tokens of the meta-language statement. Tokens in the meta-language statement (query pattern or template) are, in their simplest form, fields of non-space characters separated by spaces. Each token is therefore either a query element (if it is delimited by angle braces), or a query list element (if it is delimited by square braces), or is a constant textual string which forms a constant portion of the desired SQL command to be generated. There may be a plurality of query elements or query list elements in a single meta-language statement. In addition, the elements of a query list element may themselves be other query elements or query list elements (i.e. nested variable portions of the query template). For this reason, the parser of the query profiler of the present invention is reentrant so as to permit parsing of nested variable elements within the template. FIG. 6 depicts the details of the reentrant parser of the query profiler. The parser is entered in a reentrant manner: i.e. saving previous status and allocating local variables on a stack. Element 504, sets the counter variable "J" to the number of tokens found in the input buffer counter varible J is provided as a parameter to the reentrant function. Elements 506 and 520 are operable to loop on the invocation of elements 510-518 (and 530-542 of FIG. 7 below) for each token found in the input buffer. If element 506 determines that all tokens in the input buffer have been processed, element 508 is operable to generate the completed SQL command in the output buffer. The completed command is then applied to the database API (122 of FIG. 2) as discussed above with respect to FIG. 4. If further tokens remain to be processed, element 510 is operable to get the next token from the input buffer for further processing. Element 512 determines if the token to be processed is a query element type of token (i.e. delimited by angle braces). If so, element 514 is operable to copy the replacement value for the query element (as discussed above) into the output buffer. This replacement value stands in place of the query element in the SQL command being generated from the query template. Processing then continues at element 520 by looping through the process. If the token is not a query element, the element 512 determines whether the token is a query list element (i.e. delimited by square braces). If not, the token must be a constant portion of the query pattern and so is simply copied to the output buffer to become a constant part of the generated SQL command. If the token is a query list element, processing continues at element 530 of FIG. 7. Element 530 of FIG. 7 separates the query list elements into the individual values (the comma separated values of the list). Element 532 sets the counter variable "K" to the number of value elements in the list. If element 534 determines that there are no more values in the list to be processed, then processing continues by returning to element 520 of FIG. 6. For each value in the list, elements 534-542 are invoked to generate an SQL command in the output buffer. Element 536 first clears the output buffer generated up to this point (by earlier operation of elements 506-520 of FIG. 6). Next, element 538 creates a new input buffer with the current input buffer but with the query list element (now being processed) replaced by the next value from the list Element 540 then invokes the reentrant parser function to re-parse the new input buffer with the currently processed query list element replaced by its next value from the list. After processing of the revised meta-language statement (the new input buffer) is complete, and the associated SQL commands are generated, processing continues in the present invocation of the parser with element 542 decrementing the loop count variable "K" to indicate another value in the list is processed. Upon completion of the processing of the present query list element, processing continues at element 520 if FIG. 6 to process the remaining tokens of the meta-language statement. Processing continues in this manner for each value in the query list element until all SQL commands represented by the query pattern (meta-language statement) have been generated. One of ordinary skill in the art will recognize that other forms of recursive of reentrant designs of the method of the present invention may achieve the same purpose. Such design choices for reentrant or recursive methods are well known to those of ordinary skill in the software arts. In addition, the methods of the present invention may be simplified by restricting the meta-language syntax to prohibit the nesting of, or even a plurality of, query list elements. Such a design choice eliminates the need for recursion in the processing of the meta-language. Again, such design choices are well known to those of ordinary skill in the software arts. BNF DESCRIPTION OF GRAMMAR RULES: The meta-language of the present invention may be understood as a set of grammatical rules for the formation of legal statements within the grammar. A BNF format description is a common format in which to express such rules. The following BNF rule description includes the entire SQL standard language grammatical rules from which the rules of the present invention are an extension. The extensions to the SQL grammar defined by the rules of the present invention are highlighted in bold characters to distinguish them from the standard rules which comprise the standard SQL language. For added clarity, the enhancements to the SQL BNF grammar rules all have identifiers that begin with the characters "QP". ##SPC1## While the invention has been illustrated and described in detail in the drawings and foregoing description, such illustration and description is to be considered as exemplary and not restrictive in character, it being understood that only the preferred embodiment and minor variants thereof have been shown and described and that all changes and modifications that come within the spirit of the invention are desired to be protected.
|
Same subclass Same class Consider this |
||||||||||