Table Substitution

• US 20070276861A1
• Filed: 08/14/2007
• Published: 11/29/2007
• Est. Priority Date: 12/18/2001
• Status: Active Grant

• Current Assignee: Oracle America, Inc. (Oracle Corporation)
• Original Assignee: Oracle America, Inc. (Oracle Corporation)
• Inventors: Kurinskas, Scott, Boyd, Jeffrey, Pryce, Alex
• Granted Patent: US 9,600,545 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• 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

A method and system that enables data to be shared across application systems via a table substitution scheme. Tables are identified in the legacy application system that are candidates for substitution of tables in the core application system containing similar data. Table structure meta data corresponding to these “substituted” tables are then extracted from the legacy system schema, and stored in a repository. In accordance with the meta data, business logic is defined to remap columns in the core tables to corresponding columns in the substituted tables. Accordingly, when the core application system performs data access (e.g., reads, inserts, updates, etc.), it accesses the remapped columns in the substituted tables instead of original columns in the core tables. At the same time, the legacy application system is enabled to concurrently access the substituted tables without require any changes to the business logic of the legacy application system.

Claims

1. A method for sharing data across a legacy application system and a core application system, comprising:

identifying a table in the legacy application system containing data similar to data contained in a set of related core application system tables, said legacy application table comprising a substituted table;

determining a table structure of each of the substituted table and the set of related core application system tables;

defining business logic to remap columns in the set of related core application system tables to corresponding columns in the substituted table based on the table structure of each table; and

performing core application system data operations via the business logic that access the remapped columns in the substituted table instead of the columns in the core application system table, wherein the legacy application system is configured to concurrently access the substituted table without requiring any changes to business logic that is used by the legacy application system.

2. The method of claim 1, wherein the set of related core application system tables comprise a base table and at least one child table, and wherein performing the core application system data operations includes entering data corresponding to a new record, and in response thereto writing data pertaining to the new record to the substituted table and writing a portion of the same data to said at least one child table.

3. The method of claim 2, further comprising:

defining a linking table that links rows in the substituted table to corresponding rows in said at least one child table; and

inserting data into the linking table to define a link between the data pertaining to the new record that is written to the substituted table and the portion of the same data that are written to said at least one child table.

4. The method of claim 3, further comprising:

generating a primary key for the substituted table using a primary key generation scheme corresponding to the legacy application system; and

generating a primary key for said at least one child table using a primary key generation scheme corresponding to the core application system.

5. The method of claim 1, wherein defining business logic to remap columns in the set of related core application system tables to corresponding columns in the substituted table comprises:

determining columns in the set of core application system tables that are required by the core application system;

for each of such columns,

identifying a corresponding column in the substituted table containing similar data; and

remapping any core application system objects that reference the column in the set of related core application system tables to hence forth map to the corresponding column in the substituted table that is identified.

6. The method of claim 5, wherein a portion of the columns of the set of core application system tables do not have a corresponding column in the substituted table, further comprising:

defining business logic to create a pseudo column that appears to exist to the core application objects, but doesn'"'"'t correspond to an actual column in the substituted table.

10. The method of claim 1, further comprising:

remapping columns in a core application system table to corresponding columns in said legacy application system table; and

performing core application system data operations that access the columns in the substituted table in place of the columns in the core application system table that are remapped.

11. The method of claim 10, wherein the columns are remapped by performing the operations of:

selecting a table in the legacy data system to be used as a substituted table;

selecting a table in the core application system table from which columns are to be remapped;

determining a table structure of each of the substituted table and the core application system table; and

defining business logic to remap the columns in the core application system table to corresponding columns in the substituted table based on the table structure of each respective table.

12. The method of claim 11, wherein determining the table structure of the substituted table comprises:

generating a DDL (data definition language) file corresponding to a database schema used by the legacy application system;

parsing the DDL file looking for statements pertaining to the substituted table, including create table, view and index statements; and

extracting basic SQL (structured query language) meta data from the DDL statements pertaining to the substituted table.

13. The method of claim 12, further comprising:

defining respective DDL templates describing a DDL format particular to each of a plurality of database server software products;

enabling a user to identify a database server software product used by the legacy application system; and

using the DDL template that is particular to that database server software product identified by the user to parse the DDL file.

14. The method of claim 11, wherein defining business logic to remap columns in the core application system table to corresponding columns in the substituted table comprises:

determining columns in the core application system table that are required by the core application system;

for each of such columns,

identifying a corresponding column in the substituted table containing similar data; and

remapping any core application system objects that reference the core application system table column to hence forth map to the corresponding column in the substituted table that is identified.

15. The method of claim 14, wherein a portion of the columns of the core application system table do not have a corresponding column in the substituted, further comprising:

defining business logic to create a pseudo column that appears to exist to the core application objects, but doesn'"'"'t correspond to an actual column in the substituted table.

16. The method of claim 11, wherein the core application system provides a core primary key generation scheme for the core application system table that is being substituted for by the substituted table and the legacy application system provides a legacy primary key generation scheme for the substituted table, further comprising overriding the core primary key generation scheme with the legacy key generation scheme such that a new primary key in the substituted table is generated using the legacy primary key generation scheme in when a new record is inserted.

17. The method of claim 11, further comprising tuning a query corresponding to the substituted table so as to optimize the query, wherein the query is tuned based on tuning parameters pertaining to a database server used to host both the legacy application system and the core application system.

18. The method of claim 11, wherein the core application system is implemented with a multi-level software architecture comprising a user interface layer that includes a plurality of user-interface objects containing fields that enable users to interact with the core application system and an object manager layer that contains business logic that maps columns in the substituted table to corresponding fields in the user-interface objects.

19. The method of claim 18, wherein the multi-level software architecture further includes a data manager layer that interacts with the object manager layer and includes logic to enable the core application system to access data using any one of a plurality of different database servers such that switching to a new database server does not require any modification to the business logic in the object manager layer.

7. A method for sharing data across a legacy application system and a core application system, comprising:

identifying a set of related tables in the legacy application system containing data similar to data contained in a set of related core application system tables, said legacy application table comprising a set of substituted tables;

determining a table structure of each of the set of substituted tables and the set of related core application system tables;

defining business logic to remap columns in the set of related core application system tables to corresponding columns in the set of substituted tables based on the table structure of each table; and

performing core application system data operations via the business logic that access the remapped columns in the set of substituted tables instead of the columns in the set of related core application system tables,

wherein the legacy application system is enabled to concurrently access the set of substituted tables without requiring any changes to business logic that is used by the legacy application system.

8. The method of claim 7, wherein the set of substituted tables comprise a base table and at least one child table, and wherein performing the core application system data operations includes entering data corresponding to a new record, and in response thereto:

writing a first portion of data pertaining to the new record to the base table;

writing a second portion of data pertaining to the new record to said at least one child table.

9. The method of claim 7, wherein defining business logic to remap columns in the set of related core application system tables to corresponding columns in the set of substituted tables comprises:

determining columns in the set of core application system tables that are required by the core application system;

for each of such columns,

identifying a corresponding column in the set of substituted tables containing similar data; and

remapping any core application system objects that reference the column in the set of related core application system tables to hence forth map to the corresponding column in the set of substituted tables identified.

20. A machine-readable media on which a plurality of machine instructions are stored corresponding to a multi-layer software architecture implemented by one or more software modules, including:

a user interface layer providing a plurality of forms pertaining to various business objects used by a core application system, each form including a plurality of fields;

an object management layer that interfaces with the user interface layer including a first portion of business logic that maps columns in a set of core application system tables to corresponding fields in the plurality of forms and a second portion of business logic that maps columns in at least one legacy application system table to fields in at least one form corresponding to at least one business object, each of said at least one legacy application system table comprising a substituted table; and

a data management layer that interfaces with the object management layer and provides access to the set of core application system tables and said at least one legacy application system table through interaction with a database server instance that hosts the set of core application system tables and said at least one legacy application system table,

wherein the set of core application systems tables and said at least one legacy application system table respectively correspond to different database schemas.

21. The computer-readable media of claim 20, wherein the multi-level software architecture further includes a query adapter/tuner layer that contains logic to dynamically tune an SQL statement based on tuning parameters specific to a database server software product used to host the database server instance.

22. The computer-readable media of claim 21, wherein the query adapter/tuner layer is implemented as a dynamic link library containing at least one method that receives an SQL statement as an input, identifies a type of database access specified by the SQL statement, and dynamically generates a tuned SQL statement based on predefined rules for the type of database access identified that are defined for one or more tables referenced in the SQL statement.

23. The computer readable media of claim 20, wherein the object management layer is implemented using an object-oriented language that is used to define one or more business objects, each business object corresponding to at least one form in the user-interface layer that enable users to enter data pertaining to that business object, and a plurality of business components that define logic for mapping fields on the forms to columns in the set of core application system tables and said at least one legacy application system table.