VIRTUAL COLUMNS

• US 20080016032A1
• Filed: 09/28/2007
• Published: 01/17/2008
• Est. Priority Date: 07/22/2004
• Status: Active Grant

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Kolz, Daniel, Dettinger, Richard
• Granted Patent: US 8,543,588 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Embodiments are provided for associating a virtual column with a target physical table. The data for the virtual column originates from a source physical table where the data physically resides. In this way, users can access the data in a manner consistent with the users'"'"' logical perspective of the data.

Claims

1. A computer-implemented method for logically attributing a field of a source physical table to a target physical table both residing in an underlying physical database, the method being implemented by execution of one of more processors and comprising:

providing a database abstraction model defining a plurality of logical fields for composing an abstract query, wherein each logical field specifies (i) a name for the logical field and (ii) an access method that maps the logical field to data in the underlying physical database, wherein at least one of the logical fields is defined as a virtual column of the target physical table different than the source physical table where the data for the at least one logical field physically resides, the logical field defined as the virtual column comprising an access method that maps initially to selected data in the source physical table;

receiving an abstract query requesting results for at least one of the plurality of logical fields, including the logical field defined as the virtual column;

for the received abstract query, creating a first data structure configured to select only the selected data in the source physical table mapped to by the access method of the logical field defined as the virtual column; and

for the received abstract query, changing the access method of the logical field defined as the virtual column to map to the first data structure instead of the source physical table; and

wherein the database abstraction model and the first data structure are stored on one or more computer readable storage media.

2. The method of claim 1, wherein creating the first data structure comprises creating an abstract query definition containing data selection criteria including at least one of:

(i) a reference to a physical field in a physical table of the underlying physical database; and

(ii) a reference to a logical field defined in the database abstraction model.

3. The method of claim 1, wherein creating the first data structure comprises creating an abstract query definition containing data selection criteria including at least one reference to a physical field in a physical table of the underlying physical database and at least one reference to a logical field defined in the database abstraction model.

4. The method of claim 1, wherein creating the first data structure comprises creating an abstract query definition on the basis of the access method of the logical field defined as the virtual column.

5. The method of claim 1, wherein creating the first data structure comprises:

creating an abstract query definition specifying one or more output field according the to access method of the logical field defined as the virtual column; and

further specifying a field of the target physical table to which the first data structure will be joined in a resultant executable query derived from the abstract query.

6. The method of claim 1, wherein creating the first data structure comprises:

creating an abstract query definition on the basis of the access method of the logical field defined as the virtual column and on the basis of a relationship specification of the data abstraction model describing a relationship between the source physical table and the target physical table.

7. The method of claim 6, wherein the described relationship is a one-to-one relationship between the source physical table and the target physical table.

11. The method of claim 1, wherein the logical field defined as the virtual column is added to the abstract query during a preprocessing stage, the preprocessing stage comprising:

receiving a pre-modified abstract query requesting results for at least one of the plurality of logical fields, excluding the logical field defined as the virtual column;

examining each logical field defined in the data abstraction model to determine whether the logical field being examined is defined as a virtual column;

for each examined logical field defined as a virtual column, determining whether the examined logical field maps to at least some of the same data mapped to by at least one other logical field referenced in the pre-modified abstract query; and

if so, adding the examined logical field to the pre-modified abstract query.

12. The method of claim 11, the determining comprises examining the respective access methods of the examined logical field and the at least one other logical field referenced in the pre-modified at the current query.

8-10. -10. (canceled)

13. A computer readable medium containing a program which, when executed, performs an operation for logically attributing a field of a source physical table to a target physical table both residing in an underlying physical database, the operation comprising:

providing a database abstraction model defining a plurality of logical fields for composing an abstract query, wherein each logical field specifies (i) a name for the logical field and (ii) an access method that maps the logical field to data in the underlying physical database, wherein at least one of the logical fields is defined as a virtual column of the target physical table different than the source physical table where the data for the at least one logical field physically resides, the logical field defined as the virtual column comprising an access method that maps initially to selected data in the source physical table;

receiving an abstract query requesting results for at least one of the plurality of logical fields, including the logical field defined as the virtual column;

for the abstract query, creating a first data structure configured to select only the selected data in the source physical table mapped to by the access method of the logical field defined as the virtual column; and

for the abstract query, changing the access method of the logical field defined as the virtual column to map to the first data structure instead of the source physical table.

14. The computer readable medium of claim 13, wherein creating the first data structure comprises creating an abstract query definition on the basis of the access method of the logical field defined as the virtual column.

15. The computer readable medium of claim 13, wherein creating a first data structure comprises:

creating an abstract query definition on the basis of the access method of the logical field defined as the virtual column and on the basis of a relationship specification of the data abstraction model describing a relationship between the source physical table and the target physical table.

16. The computer readable medium of claim 15, wherein the described relationship is a one-to-one relationship between the source physical table and the target physical table.

17. The computer readable medium of claim 13, wherein creating a first data structure comprises:

creating an abstract query definition specifying one or more output field according the to access method of the logical field defined as the virtual column; and

further specifying a field of the target physical table to which the first data structure will be joined in a resultant executable query derived from the abstract query.

21. The computer readable medium of claim 13, wherein the logical field defined as the virtual column is added to the abstract query during a preprocessing stage, the preprocessing stage comprising:

receiving a pre-modified abstract query requesting results for at least one of the plurality of logical fields, excluding the logical field defined as the virtual column;

examining each logical field defined in the data abstraction model to determine whether the logical field being examined is defined as a virtual column;

for each examined logical field defined as a virtual column, determining whether the examined logical field maps to at least some of the same data mapped to by at least one other logical field referenced in the pre-modified abstract query; and

if so, adding the examined logical field to the pre-modified abstract query.

22. The computer readable medium of claim 21, the determining comprises examining the respective access methods of the examined logical field and the at least one other logical field referenced in the pre-modified at the current query.

18-20. -20. (canceled)

23. A system, comprising:

a processor;

a computer readable memory containing a database abstraction model defining a plurality of logical fields for composing an abstract query, wherein each logical field specifies (i) a name for the logical field and (ii) an access method that maps the logical field to data in an underlying physical database containing a plurality of physical tables including a source physical table and a target physical table, wherein at least one of the logical fields is defined as a virtual column of the target physical table, wherein data for the at least one logical field physically resides in the source physical table, and wherein the logical field defined as the virtual column comprises an access method that maps initially to selected data in the source physical table; and

a runtime component which, when executed by the processor, is configured to;

receive an abstract query requesting results for at least one of the plurality of logical fields, including the logical field defined as the virtual column;

for the received abstract query, create a first data structure configured to select only the selected data in the source physical table mapped to by the access method of the logical field defined as the virtual column; and

for the received abstract query, change the access method of the logical field defined as the virtual column to map to the first data structure instead of the source physical table.

24. The system of claim 23, further comprising a query engine configured to execute physical queries against the database, and wherein the runtime component is further configured to transform the abstract query into a physical query executable by the query engine.

25. (canceled)