Apparatus and method for synthesizing a query for accessing a relational data base

• US 4,506,326 A
• Filed: 02/28/1983
• Issued: 03/19/1985
• Est. Priority Date: 02/28/1983
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Sordi, Joseph J., Traiger, Irving L., Shaw, Philip S.
• Primary Examiner(s): Zache, Raulfe B.
• Application Number:
  US06/470,821
• Time in Patent Office: 750 Days
• Field of Search: 364/200 MS File, 364/900 MS File, 364/300
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A linear query for accessing a relation data base in computer storage is synthesized from a graphic query input at a user terminal. The graphic query may be one of a combined print query, a target print query, an insert query, a delete query, or an update query. According to one embodiment, the linear query is expressed in Structured Query Language (SQL) syntax, and the graphic query in Query By Example (QBE) syntax. Responsive to a QBE combined print query or target print query, an SQL select query is generated comprising the UNION of one or more generated select statements. Responsive to a QBE delete query, an SQL delete query is generated from the logical OR of generated condition statements including an outer query DELETE and a SELECT* subquery. Responsive to a QBE update query, an SQL update query is generated including a SET clause and any generated WHERE clause and subquery. Responsive to a QBE insert query, an SQL insert query is generated to include an INSERT statement and any generated SELECT statement.

Claims

1. A method for operating a computing apparatus to translate into a linear query a graphic language query expressed as one or more elements, including example elements and implied operand predicates, appearing in rows and columms of an example table including one or more source and target tables and, optionally, in condition blocks, comprising the steps of:

generating row names into a row name table having one entry for each row in a source table;

for each data field within each row named in said row name table which contains an example element definition or implied operand predicate, generating and loading into a column data table an entry specifying the row name, column name, and data; and

generating into a conditions table an entry containing a basic predicate for each column data table entry containing an implied operand predicate or an example element having an implied condition;

thereby establishing in said row name table, column data table, and conditions table a data structure for synthesizing into a linear query a graphic language query specified as a target point query, or a combined print query, or an insert query, or an update query, or a delete query.

2. The method of claim 1, comprising the further steps of determining from said column data table the row name and column name for example elements referenced in the condition block, and responsive thereto generating into said conditions table a condition entry for each condition specified in a condition block.

3. The method of claim 1, comprising the further step of substituting parameters passed in said graphic language query prior to building said data structure.

4. The method of claim 1, comprising the further step, responsive to said graphic language query being a combined print query expressed as a print command appearing in a source table, of synthesizing a linear select query.

5. The method of claim 4 wherein said linear select query is synthesized according to the steps of:

identifying as simple P. rows those rows in a source table containing a print command which is not linked to any other row, and as dependent P. rows those rows in a source table which are linked to one or more other rows;

generating for all simple P. rows a single select statement including in a FROM clause the name of said source table, and in a WHERE clause the ANDing of conditions that reference a simple P. row and, if there are more than one simple P. rows, for each simple P. row in excess of one the ORing of conditions that reference one simple P. row with those conditions that reference another simple P. row;

generating for each dependent P. row a select statement including (1) in a FROM clause the table name of the table containing the P. row and the table name of the table containing a linked P. row for each such linked P. row, each table name being associated with a correlation variable, and (2) in a WHERE clause the ANDing of all conditions that reference the linked rows in the FROM clause; and

generating as said linear select query the UNION of all generated select statements.

6. The method of claim 1, comprising the further step, responsive to said graphic language query being a target print query expressed as a print command appearing in a target table, of synthesizing a linear select query.

7. The method of claim 6 wherein said linear select query is synthesized according to the steps of:

generating for each target row of a target table containing a print command P. a select statement including (1) in a SELECT clause a list of constants and expressions that appear in or are referenced by the data fields of the target row for which the select statement is being generated;

(2) in a FROM clause the table name and associated correlation variable for each table containing the target or a linked row; and

(3) in a WHERE clause the logical ANDing of any conditions that reference rows that are referenced in the target row;

responsive to the target print query including a sort specification, generating an ORDER BY clause; and

generating as said linear select query any generated ORDER BY clause and the UNION of all generated select statements.

8. The method of claim 1, comprising the further step, responsive to said graphic language query being an insert query expressed as an insert command appearing in an example table, of synthesizing a linear insert query.

9. The method of claim 8, wherein said linear insert query is synthesized according to the steps of:

identifying as a simple I. row a row containing only an insert command and constant values, and as a complex I. row a row containing an insert command and one or more example elements linking the complex I. row to one or more linked rows;

responsive to said insert command appearing in a simple I. row, generating only an insert statement;

responsive to said insert command appearing in a complex I. row, generating an insert statement and a single select statement, the select statement including a SELECT clause, a FROM clause, and a WHERE clause;

the SELECT clause containing constants and expressions appearing in the data fields of the complex I. row and references to columns in other rows linked to the complex I. row;

the FROM clause including the table names of all tables containing the complex I. row and any linked row, each such table name being associated with a correlation variable; and

the WHERE clause including the logical ANDing of all conditions that reference rows that are referenced in the complex I. row;

generating said linear insert query to include said insert statement and any generated select statement.

10. The method of claim 1, comprising the further step, responsive to said graphic language query being a delete query expressed as a delete command appearing in a source table, of synthesizing a linear delete query.

11. The method of claim 10, wherein said linear delete query is synthesized according to the steps of:

identifying as a D. row each row in a source table containing a delete command;

identifying as a linked row (1) each row referenced by the same condition, and (2) each linked row linked to a same row;

identifying as a dependent row each linked row that is linked to a D. row;

generating a condition statement for each D. row, including (1) generating in an outer query a delete statement including (a) in a DELETE FROM clause the table name of the source table containing the D. row;

(b) in a WHERE clause the logical ANDing of conditions which reference only said each D. row and, using an EXISTS comparison operator, any generated sub-queries; and

(2) generating a sub-query for each set of dependent rows linked to said each D. row, a subquery including (a) in a SELECT* FROM clause the logical ANDing of all table names of tables within the set of dependent rows, each table name being associated with correlation variable; and

(b) in a WHERE clause the logical ANDing of conditions that reference the set of dependent rows; and

generating as said linear delete query the logical ORing of the condition statements generated for each D. row.

12. The method of claim 1, comprising the further step, responsive to said graphic language query being an update query expressed as an update command, of synthesizing a linear update query.

13. The method of claim 12, wherein said linear update query is synthesized according to the steps of:

identifying as a simple update query an update query U. appearing in a U. row that is not linked to another row, and as a dependent update query an update query U. appearing in a row that is linked to one or more other rows;

for a simple update query, generating (1) in a SET clause constant values and expressions in the U. row referencing other columns in the U. row; and

(2) in a WHERE clause the logical ANDing of any conditions that reference only the U. row;

for a dependent update query, generating in an outer query (1) in a SET clause constant values and expressions in the U. row referencing other columns in the U. row;

(2) in a WHERE clause the logical ANDing of any conditions that reference only the U. row and, using an EXISTS comparison operator, any generated subqueries; and

generating in a sub-query (1) in a SELECT* FROM clause the table names of tables containing a linked row for each such linked row, each table name being associated with a correlation variable; and

(2) in a WHERE clause the logical ANDing of all conditions that reference the linked rows; and

generating said linear update query to include a SET clause and any generated WHERE clause and sub-query.

14. A computing apparatus for translating into a linear query a graphic language query expressed as one or more elements, including example elements and implied operand predicates, appearing in rows and columns of an example table including one or more source and target tables and, optionally, in condition blocks, comprising:

means for generating row names into a row name table having one entry for each row in a source table;

means, operable for each data field within each row named in said row name table which contains an example element definition or implied operand predicate, for generating and loading into a column data table an entry specifying the row name, column name, and data;

means for generating into a conditions table an entry containing a basic predicate for each column data table entry containing an implied operand predicate or an example element having an implied condition;

thereby establishing in said row name table, column data table, and conditions table a data structure for synthesizing into a linear query a graphic language query specified as a target print query, or a combined print query, or an insert query, or an update query, or a delete query.