Determining query entities for an abstract database from a physical database table

• US 8,326,852 B2
• Filed: 03/13/2007
• Issued: 12/04/2012
• Est. Priority Date: 03/13/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Dettinger, Richard D., Kulack, Frederick A.
• Primary Examiner(s): Kim, Charles
• Assistant Examiner(s): MUELLER, KURT A
• Application Number:
  US11/685,296
• Publication Number:
  US 20080228800A1
• Time in Patent Office: 2,093 Days
• Field of Search: 707/758
• US Class Current: 707/758
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema

Abstract

Embodiments of the invention provide techniques for determining query entities in physical tables for an abstract database. A query entity may be created to store a particular set of data. The query entity may be defined in terms of the structure of the physical database, such as an SQL statement, used to generate the query entity as needed. The fields included in a given query entities may be determined based on the presence (or absence) of values in the columns of a physical database table. In addition, logical fields having access method which map to the fields included in a query entity may be generated.

Claims

1. A computer-implemented method of determining, from a physical database table having one or more fields and stored in a physical database, a set of query entities for a database abstraction model, comprising:

identifying, from actual fields of the physical database table, a selection of an entity field containing a plurality of values,

determining a plurality of unique values within the plurality of values for presentation to a user,

identifying a user selection of one or more of the unique values from the plurality of unique values

generating, by operation of one or more computer processors, one or more query entity definitions based on the selected one or more unique values, comprising, for each selected unique value;

determining one or more fields of the physical database table for which a plurality of records containing the selected unique value have data values, wherein records in the plurality of records do not have data values for at least one other field of the physical database table,

programmatically populating, in a user interface, the determined one or more fields of the physical database table, wherein the user interface includes an indication, for each of the at least one other fields, that the records in the plurality of records do not have data values for the respective other field,

receiving, from the user interface, a user selection of one or more additional fields selected from the at least one other field of the physical database table to include in the respective query entity definition, and

receiving, from the user interface, a user confirmation to create a query entity definition whereby the query entity definition for the selected unique value is generated and contains the determined one or more fields and the selected additional fields, and

storing the generated one or more query entity definitions as one or more objects of the database abstraction model, wherein the database abstraction model logically describes the physical database that includes the physical database table.

2. The method of claim 1, wherein the query entity definition is an SQL statement used to generate a database table having the entity field, the determined one or more fields of the physical database table, and the selected one or more other fields of the physical database table.

3. The method of claim 1, wherein the query entity definition further includes an identification field used to link the entity query definition to other physical database tables in the physical database.

4. The method of claim 1, further comprising, generating a logical field having an access method mapping the logical field to one of the selected additional fields of one of the query entity definitions.

5. The method of claim 4, wherein the access method is one of a simple, filtered and composed access method type.

6. The method of claim 1, wherein the user interface is configured to allow removal of fields from the query entity definition.

7. The method of claim 6, further comprising:

receiving a request to access a given query entity definition of the one or more query entity definitions;

retrieving the given query entity definition stored in the data abstraction model;

executing the query entity definition; and

presenting a display of data records returned for the executed query entity definition.

9. The computer-implemented method of claim 1, wherein generating one or more query entity definitions further comprises:

providing, in the user interface, an indication for a first field of the physical database table indicating that some, but not all, of the records in the plurality of records have data values for the first field.

8. A computer-readable storage medium containing a program which, when executed, performs an operation for determining, from a physical database table having one or more fields and stored in a physical database, a set of query entities for a database abstraction model, comprising:

identifying, from actual fields of the physical database table, a selection of an entity field containing a plurality of values,

determining a plurality of unique values within the plurality of values for presentation to a user,

identifying a user selection of one or more of the unique values from the plurality of unique values

generating, by operation of one or more computer processors, one or more query entity definitions based on the selected one or more unique values, comprising, for each selected unique value;

determining one or more fields of the physical database table for which a plurality of records containing the selected unique value have data values, wherein records in the plurality of records do not have data values for at least one other field of the physical database table,

programmatically populating, in a user interface, the determined one or more fields of the physical database table, wherein the user interface includes an indication, for each of the at least one other fields, that the records in the plurality of records do not have data values for the respective other field,

receiving, from the user interface, a user selection of one or more additional fields selected from the at least one other field of the physical database table to include in the respective query entity definition, and

receiving, from the user interface, a user confirmation to create a query entity definition whereby the query entity definition for the selected unique value is generated and contains the determined one or more fields and the selected additional fields, and

storing the generated one or more query entity definitions as one or more objects of the database abstraction model, wherein the database abstraction model logically describes the physical database that includes the physical database table.

10. The computer-readable storage medium of claim 8, wherein the query entity definition is an SQL statement used to generate a database table having the entity field, the determined one or more fields of the physical database table, and the selected one or more other fields of the physical database table.

11. The computer-readable storage medium of claim 8, wherein the query entity definition further includes an identification field used to link the entity query definition to other physical database tables in the physical database.

12. The computer-readable storage medium of claim 8, wherein the operation further comprises, generating a logical field having an access method mapping the logical field to one of the selected additional fields of one of the query entity definitions.

13. The computer-readable storage medium of claim 12, wherein the access method is one of a simple, filtered and composed access method type.

14. The computer-readable storage medium of claim 8, wherein the user interface is configured to allow removal of fields from the query entity definition.

15. The computer-readable storage medium of claim 14, wherein the operation further comprising:

receiving a request to access a given query entity definition of the one or more query entity definitions;

retrieving the given query entity definition stored in the data abstraction model;

executing the query entity definition; and

presenting a display of data records returned for the executed query entity definition.

16. The computer-readable storage medium of claim 8, wherein generating one or more query entity definitions further comprises:

providing, in the user interface, an indication for a first field of the physical database table indicating that some, but not all, of the records in the plurality of records have data values for the first field.

17. A system, comprising:

a processor; and

a memory containing a program which, when executed by the processor, performs an operation for determining, from a physical database table having one or more fields and stored in a physical database, a set of query entities for a database abstraction model, comprising;

identifying, from actual fields of the physical database table, a selection of an entity field containing a plurality of values,

determining a plurality of unique values within the plurality of values for presentation to a user,

identifying a user selection of one or more of the unique values from the plurality of unique values

generating one or more query entity definitions based on the selected one or more unique values, comprising, for each selected unique value;

determining one or more fields of the physical database table for which a plurality of records containing the selected unique value have data values, wherein records in the plurality of records do not have data values for at least one other field of the physical database table,

programmatically populating, in a user interface, the determined one or more fields of the physical database table, wherein the user interface includes an indication, for each of the at least one other fields, that the records in the plurality of records do not have data values for the respective other field,

receiving, from the user interface, a user selection of one or more additional fields selected from the at least one other field of the physical database table to include in the respective query entity definition, and

receiving, from the user interface, a user confirmation to create a query entity definition whereby the query entity definition for the selected unique value is generated and contains the determined one or more fields and the selected additional fields, and

storing the generated one or more query entity definitions as one or more objects of the database abstraction model, wherein the database abstraction model logically describes the physical database that includes the physical database table.

18. The system of claim 17, wherein the query entity definition is an SQL statement used to generate a database table having the entity field, the determined one or more fields of the physical database table, and the selected one or more other fields of the physical database table.

19. The system of claim 17, wherein the query entity definition further includes an identification field used to link the entity query definition to other physical database tables in the physical database.

20. The system of claim 17, system, wherein the operation further comprises, generating a logical field having an access method mapping the logical field to one of the selected additional fields of one of the query entity definitions.

21. The system of claim 20, wherein the access method is one of a simple, filtered and composed access method type.

22. The system of claim 17, wherein the user interface is configured to allow removal of fields from the query entity definition.

23. The system of claim 22, wherein the operation further comprising:

receiving a request to access a given query entity definition of the one or more query entity definitions;

retrieving the given query entity definition stored in the data abstraction model;

executing the query entity definition; and

presenting a display of data records returned for the executed query entity definition.

24. The system of claim 17, wherein generating one or more query entity definitions further comprises:

providing, in the user interface, an indication for a first field of the physical database table indicating that some, but not all, of the records in the plurality of records have data values for the first field.