Virtual columns

• US 7,302,447 B2
• Filed: 01/14/2005
• Issued: 11/27/2007
• Est. Priority Date: 01/14/2005
• Status: Expired due to Fees

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Kolz, Daniel P., Dettinger, Richard D.
• Primary Examiner(s): HWANG, JOON H
• Application Number:
  US11/035,568
• Publication Number:
  US 20060161557A1
• Time in Patent Office: 1,047 Days
• Field of Search: None
• US Class Current: 707/714
• 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 or 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 selecting only the selected data in the source physical table mapped to by the access method of the logical field defined as the virtual column;

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;

creating a second data structure selecting all remaining data from the source physical table, except the selected data selected by the first data structure, wherein the database abstraction model includes logical fields each having respective access methods initially mapping to respective portions of all the remaining data in the source physical table; and

changing the respective access methods to map to the second data structure, instead of the source physical table according to the initial mapping.

2. The method of claim 1, further comprising:

transforming the abstract query into an executable query executable by a query engine of the underlying physical database.

3. The method of claim 2, wherein the executable query joins the first data structure and the second data structure to the target physical table according to a relationship specification defined in the database abstraction model, the relationship specification defining relationships between the source and target physical tables of the underlying physical tables of the underlying physical database.

4. A computer readable storage medium containing a program which, when executed by one or more processors, 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 received abstract query, creating a first data structure selecting 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 wherein the database abstraction model and the first data structure are stored on one or more computer readable storage media;

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;

creating a second data structure selecting all remaining data from the source physical table, except the selected data selected by the first data structure, wherein the database abstraction model includes logical fields each having respective access methods initially mapping to respective portions of all the remaining data in the source physical table; and

changing the respective access methods to map to the second data structure, instead of the source physical table according to the initial mapping.

5. The computer readable storage medium of claim 4, further comprising:

transforming the abstract query into an executable query executable by a query engine of the underlying physical database.

6. The computer readable storage medium of claim 5, wherein the executable query joins the first data structure and the second data structure to the target physical table according to a relationship specification defined in the database abstraction model, the relationship specification defining relationships between the source and target physical tables of the underlying physical database.

7. 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 the 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, performs;

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 selecting only the selected data in the source physical table mapped to by the access method of the logical field defined as the virtual column, wherein creating the first data structure comprises creating an abstract query definition specifying one or more output fields accord mci to the 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 is joined in a resultant executable query derived from the received abstract query;

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;

creating a second data structure selecting all remaining data from the source physical table, except the selected data selected by the first data structure;

wherein the database abstraction model includes logical fields each having respective access methods initially mapping to respective portions of all the remaining data in the source physical table; and

changing the respective access methods to map to the second data structure, instead of the source physical table according to the initial mapping.