Transformation of a physical query into an abstract query

• US 7,805,435 B2
• Filed: 02/19/2008
• Issued: 09/28/2010
• Est. Priority Date: 12/17/2004
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kolz, Daniel P., Dettinger, Richard D.
• Primary Examiner(s): Woo; Isaac M
• Application Number:
  US12/033,772
• Publication Number:
  US 20080147628A1
• Time in Patent Office: 952 Days
• Field of Search: 707600-831
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

Embodiments of the invention provide methods, systems, and articles of manufacture for transforming a physical query (e.g., written in SQL) into an abstract query than can be run against an abstract database described by a database abstraction model. In one embodiment, the transformation occurs as a multiple step process: first, transformation of the physical query into an intermediate representation, such as an abstract query plan, and second, transformation of the intermediate representation into an abstract query. There are thousands upon thousands of existing physical (e.g., SQL) queries. Rather than just abandon these existing queries in favor of an abstract database, embodiments of the present invention provide for reverse query processing, that is, for creating an abstract query of the abstract database from a physical query (e.g., an SQL query) of the physical database underlying the abstraction.

Claims

1. A computer-readable storage medium containing a program which, when executed, performs operations, comprising:

receiving, from a requesting entity, a physical query composed in a query language used to query a physical database;

generating, from the physical query, an intermediate representation of the physical query that indicates (i) data sources within the physical database containing data queried by the physical query, (ii) any conditions specified by the physical query on the data queried and (iii) relationships between the data sources queried by the physical query; and

generating, from the intermediate representation, an abstract query composed from a plurality of logical fields, wherein each logical field specifies (i) a name used to identify the logical field, and (ii) a reference to an access method selected from at least two different access method types, wherein each of the different access methods types defines a different manner of exposing the data corresponding to the name of the logical field.

2. The computer-readable storage medium of claim 1, further comprising, returning the abstract query to the requesting entity, and providing an indication of whether any data source queried by the physical query could not be used to generate a corresponding portion of the abstract query.

3. The computer-readable storage medium of claim 1, wherein the physical query comprises an SQL statement and the physical database comprises a relational database, and wherein the relationships between data sources are indicated by a relational schema of the relational database, and wherein each data source comprises a relational table.

4. The computer-readable storage medium of claim 1, wherein generating an intermediate representation of the physical query, comprises:

resolving any indirect references to the physical database included in the physical query;

dividing the physical query into a set of regions, and for each region creating an abstract query plan fragment corresponding to the region, wherein each abstract query plan fragment includes a set of one or more table instances, wherein each table instance includes;

(i) a table from the physical database, wherein the table is referenced by the corresponding region of the physical query;

(ii) an indication of the columns, in the table, that are referenced by the corresponding region of the physical query; and

(iii) any conditions specified by the physical query that are applied against the tables and columns for the corresponding region; and

joining the created abstract query plan fragments together to form an abstract query plan, wherein the fragments are joined according to relationships between tables in the physical database.

5. The computer-readable storage medium of claim 4, wherein an indirect reference comprises one of a view, common table expression, or temporary relational table.

6. The computer-readable storage medium of claim 4, wherein each region of the physical query is identified by an SQL select statement sub query within the physical query.

7. The computer-readable storage medium of claim 4, wherein generating an abstract query comprises:

identifying each table in the abstract query plan that includes at least one of (i) an output column of data in the physical database selected for output and (ii) a column of data specified in a condition included in the abstract query plan;

matching the at least one column of data to a logical field with an access method that maps the logical field to the at least one column of data and including the matching logical field in a results section of the abstract query; and

identifying each condition in the abstract query plan evaluated in reference to a column in the physical database, and (i) matching the column with the access method provided for a logical field that maps to the column and (ii) including the logical field in a condition section of the abstract query.

8. A system, comprising at least one processor, for processing queries, and further comprising:

a database abstraction model that provides a set of logical fields used to compose an abstract query, wherein each logical field specifies (i) an access method that maps the logical field to data in an underlying physical database;

a runtime component, which when executed by the one or more processors is, configured to receive;

a physical query having a format that is executable by a query engine to access the underlying physical database; and

in response to the physical query, to generate, from the physical query, an intermediate representation of the physical query that indicates (i) data sources within the physical database containing data queried by the physical query, (ii) conditions specified by the physical query on the data queried and (iii) the relationships between the data sources queried by the physical query; and

generating, from the intermediate representation, an abstract query composed from a plurality of logical fields, wherein each logical field specifies (i) a name used to identify the logical field, and (ii) a reference to an access method selected from at least two different access method types, wherein each of the different access methods types defines a different manner of exposing the data corresponding to the name of the logical field.

9. The system of claim 8, wherein the physical query comprises an SQL statement and the underlying physical database comprises a relational database, wherein the relationships between data sources are indicated by a relational schema of the relational database.

10. The system of claim 8, wherein the runtime is further configure to provide an indication to a requesting entity submitting the physical query whether any data source queried by the physical query could not be used to generate a corresponding portion of the abstract query.

11. The system of claim 8, wherein generating an intermediate representation of the physical query, comprises:

resolving any indirect references to the physical database included in the physical query;

dividing the physical query into a set of regions, and for each region creating an abstract query plan fragment corresponding to the region, wherein each abstract query plan fragment includes a set of one or more table instances, wherein each table instance includes;

(i) a table from the physical database, wherein the table is referenced by the corresponding region of the physical query;

(ii) an indication of the columns, in the table, that are referenced by the corresponding region of the physical query; and

(iii) any conditions specified by the physical query that are applied against the tables and columns for the corresponding region; and

joining the created abstract query plan fragments together to form an abstract query plan, wherein the fragments are joined according to relationships between tables in the physical database.

12. The system of claim 11, wherein an indirect reference comprises one of a view, common table expression, or temporary relational table.

13. The system of claim 11, wherein each region of the physical query is identified by an SQL select statement sub query within the physical query.

14. The system of claim 11, wherein generating, from the intermediate representation, an abstract query, comprises:

identifying each table in the abstract query plan that includes at least one of (i) an output column of data in the physical database selected for output and (ii) a column of data specified in a condition included in the abstract query plan;

matching the at least one column of data to a logical field with an access method that maps the logical field to the at least one column of data and including the matching logical field in a results section of the abstract query; and

identifying each condition in the abstract query plan evaluated in reference to a column in the physical database, and (i) matching the column with the access method provided for a logical field that maps to the column and (ii) including the logical field in a condition section of the abstract query.

15. A method of generating an abstract query from a physical query, comprising:

receiving, from a requesting entity, a physical query composed in a query language used to query a physical database;

generating, from the physical query, an intermediate representation of the physical query that indicates (i) data sources within the physical database containing data queried by the physical query, (ii) conditions specified by the physical query on the data queried and (iii) relationships between the data sources queried by the physical query; and

generating, from the intermediate representation, an abstract query composed from a plurality of logical fields, wherein each logical field specifies (i) a name used to identify the logical field, and (ii) an access method that maps the logical field to data in the physical database.

16. The method of claim 15, further comprising, returning the abstract query to the requesting entity, and providing an indication of whether any data source queried by the physical query could not be used to generate a corresponding portion of the abstract query.

17. The method of claim 15, wherein the physical query comprises an SQL statement and the physical database comprises a relational database;

and wherein the relationships between data sources are indicated by a relational schema of the relational database, and wherein each data source comprises a relational table

18. The method of claim 15, wherein generating the intermediate representation of the physical query, comprises:

resolving any indirect references to the physical database included in the physical query;

dividing the physical query into a set of regions; and

for each region,

creating an abstract query plan fragment corresponding to the region, wherein each abstract query plan fragment includes a set of one or more table instances, wherein each table instance includes;

(i) a table from the physical database, wherein the table is referenced by the corresponding region of the physical query;

(ii) an indication of the columns, in the table, that are referenced by the corresponding region of the physical query; and

(iii) any conditions specified by the physical query that are applied against the tables and columns for the corresponding region; and

joining the created abstract query plan fragments together to form an abstract query plan, wherein the fragments are joined according to relationships between tables in the physical database.

19. The method of claim 18, wherein an indirect reference comprises one of a view, common table expression, or temporary relational table.

20. The method of claim 18, wherein generating the abstract query, comprises:

identifying each table in the abstract query plan that includes at least one of (i) an output column of data in the physical database selected for output and (ii) a column of data specified in a condition included in the abstract query plan;

matching the at least one column of data to a logical field with an access method that maps the logical field to the at least one column of data;

including the matching logical field in a results section of the abstract query; and

identifying each condition in the abstract query plan evaluated in reference to a column in the physical database, and (i) matching the column with the access method provided for a logical field that maps to the column and (ii) including the logical field in a condition section of the abstract query.