Well organized query result sets

• US 20060136382A1
• Filed: 12/17/2004
• Published: 06/22/2006
• Est. Priority Date: 12/17/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kolz, Daniel P., Dettinger, Richard D., Stevens, Richard J.
• Granted Patent: US 8,131,744 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/248 Presentation of query results

Abstract

Methods, apparatus, and articles of manufacture for processing an abstract query and generating well organized user results are disclosed. Well organized user results preserve the cardinality of one-to-one and one-to-many between data elements retrieved using a database query. Embodiments of the present invention use an abstract database that separates a user from the physical representation of data stored in a data repository by providing a plurality of logical fields based on the substantive information stored in the underlying data repository. During query processing, query results generated from an abstract query are generated and organized according to the one-to-one and one-to-many relationships. Subsequently, query results are presented to users according to the intuitive relationships between data understood by users of an abstract database.

Claims

1. A method of generating a set of well organized query results, comprising:

receiving, from a requesting an entity, 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 an underlying database;

retrieving a first set of query results for the abstract query from the underlying database; and

transforming the first set of query results into a second set of query results wherein the second set of query results are organized according to a set of one-to-one and one-to-many relationships specified for the plurality of logical fields.

2. The method of claim 1, wherein retrieving the first set of query results, comprises generating, from the abstract query, an intermediate representation of the abstract query that indicates (i) a set of logical fields included in the abstract query, (ii) an indication of data sources within the database mapped to by the set of logical fields, and (iii) the set of one-to-one and one-to-many relationships;

and further comprising, generating, from the intermediate representation, a resolved query issued against the database.

3. The method of claim 2, wherein generating, from the intermediate representation, a resolved query of the database, comprises generating an SQL query, and wherein the database is a relational database.

4. The method of claim 2, further comprising, providing the second set of query results to an output formatter configured to display the query results to the requesting entity according to the one-to-one and one-to-many relationships.

5. The method of claim 3, further comprising:

detecting, prior to executing the resolved query against the relational database, whether the resolved query would retrieve produce a Cartesian product in the fist set of query results; and

, if detected,

presenting the user with an indication that the resolved query will produce a Cartesian product if issued against the relational database.

6. The method of claim 5, wherein the output formatter comprises an application configured to generate a markup code representation of the second set of query results capable of being displayed by an application operated by the requesting entity.

7. The method of claim 5, wherein the abstract query includes an indication of one-to-one or one-to-many relationships between the logical fields included in the abstract query.

8. A method of processing an abstract query, comprising:

providing a data abstraction layer comprising (i) a plurality of logical fields, wherein each logical field specifies an access method that maps the logical field to data in an underlying database;

(ii) a indication of one-to-one and one-to many relationships between the logical fields included in the plurality of logical fields;

receiving, from a requesting entity, an abstract query composed from a plurality of logical fields;

generating a data structure that indicates the one-to-one and one-to-many relationships among the plurality of logical fields included in the abstract query;

retrieving a first set of query results for the abstract query from the underlying database;

transforming the set of query results into a well organized set of query results according to the one-to-one and one-to-many relationships indicated by the data structure; and

providing the second set of query results to an output formatter configured to format the query results according to the one-to-one and one-to-many relationships indicated by the data structure; and

returning the formatted query results to the a requesting entity.

9. The method of claim 8, wherein generating, from the intermediate representation, a resolved query of the database, comprises generating an SQL query, and wherein the database is a relational database.

10. The method of claim 8, further comprising:

detecting, prior to executing the resolved query against the database, whether the resolved query would retrieve produce a Cartesian product in the fist set of query results; and

, if detected,

presenting the user with an indication that the resolved query will produce a Cartesian product if issued against the relational database.

11. The method of claim 8, wherein the output formatter comprises an application configured to generate a markup code representation of the second set of query results capable of being displayed by an application operated by the requesting entity.

12. A system for providing well organized query results, comprising:

an abstract data layer that provides a set of logical fields used to compose an abstract query composed from the set of logical fields, wherein each logical field specifies (i) an access method that maps the logical field to data in an underlying data source, and (ii) a set of one-to-one and one-to-many relationships between the fields included in the plurality of logical fields;

a runtime component configured (i) to retrieve, in response to receiving an abstract query, a first set of query results for the abstract query, and (ii) to transform the first set of query results into a second set of query results, wherein the second set of query results is organized according to the set of one-to-one and one-to-many relationships.

13. The system of claim 12, wherein the runtime component is further configured to generate, from the abstract query, an intermediate representation of the abstract query that indicates (i) the logical fields included in the abstract query, (ii) an indication of data sources within the database mapped to by the logical fields, and (iii) the set of one-to-one and one-to-many relationships;

and wherein the runtime component is configured to generate, from the intermediate representation, a resolved query issued against the database.

14. The system of claim 13, wherein the first set of query results comprises a relational table, and wherein the resolved query comprises and SQL statement.

15. The system of claim 13, further comprising, an output formatter configured to generate a markup code representation of the second set of query results.

16. The system of claim 13, wherein the runtime component is further configured to detect, prior to issuing the resolved query against the database, whether the resolved query would retrieve produce a Cartesian product in the fist set of query results.

17. A computer-readable medium containing a program which, when executed by a processor, performs operations, comprising:

accessing a data abstraction layer comprising (i) a plurality of logical fields, wherein each logical field specifies an access method that maps the logical field to data in an underlying database;

(ii) a indication of one-to-one and one-to many relationships between the logical fields included in the plurality of logical fields;

receiving, from a requesting entity, an abstract query composed from a plurality of logical fields;

generating a data structure that indicates the one-to-one and one-to-many relationships among the plurality of logical fields included in the abstract query;

retrieving a first set of query results for the abstract query from the underlying database;

transforming the set of query results into a well organized set of query results according to the one-to-one and one-to-many relationships indicated by the data structure; and

providing the second set of query results to an output formatter configured to format the query results according to the one-to-one and one-to-many relationships indicated by the data structure; and

returning the formatted query results to a requesting entity.

18. A computer-readable medium containing a program which, when executed by a processor, performs operations, comprising:

receiving, from a requesting an entity, 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 an underlying database;

retrieving a first set of query results for the abstract query from the underlying database; and

transforming the first set of query results into a second set of query results wherein the second set of query results are organized according to a set of one-to-one and one-to-many relationships specified for the plurality of logical fields.

19. The computer-readable medium of claim 18, wherein retrieving the first set of query results, comprises generating, from the abstract query, an intermediate representation of the abstract query that indicates (i) a set of logical fields included in the abstract query, (ii) an indication of data sources within the database mapped to by the set of logical fields, and (iii) the set of one-to-one and one-to-many relationships;

and further comprising, generating, from the intermediate representation, a resolved query issued against the database.

20. The computer-readable medium of claim 18, wherein generating, from the intermediate representation, a resolved query of the database, comprises generating an SQL query, and wherein the database is a relational database.