System for enabling access to a relational database from an object oriented program

Abstract

Logical tables and logical keys are employed to facilitate interaction between user applications and a relational database. Each logical table is a group of at least one column from a table or view associated with a relational database, and can be utilized like a relational table or view. Each logical primary or foreign key is a key between logical and physical tables that behaves like an actual primary or foreign key. A normalization process allows creation of integrator relational schema objects from existing captured tables. Once a denormalized table is captured from a database the normalization process allows the user to define different logical tables using subsets of the columns of the table. One or more columns of the logical table are designated as primary key. The logical tables interact with the mapping process in the same manner as physical tables.

Claims

What is claimed:

1. A method for enabling an object oriented user application to access a relational database having one or more physical tables segmented into rows and columns, comprising:

defining a logical table comprising a subset of columns from at least one of the one or more physical tables;

designating one column of the logical table as a logical primary key column;

forming a normalized relational schema object representing the logical table;

generating, responsive to the normalized relational schema object, one or more object classes associated with the normalized relational schema object; and

employing an object of an object class including the one or more object classes associated with the normalized relational schema object and a respective corresponding logical primary key value to access data in the at least one of the physical tables in the relational database.

2. The method of claim 1 including the further step of employing normalization to create foreign key relationships from a first of the one or more physical tables or the logical table to another physical or logical table having a physical or logical primary key.

3. The method of claim 1 including the further step of supplying a discriminator constraint for the logical table.

4. An apparatus for facilitation of data exchange between an object oriented user application and a relational database having one or more physical tables segmented into rows and columns, comprising:

an integrator operative to define a logical table comprising a subset of columns from at least one of the one or more physical tables, the integrator being further operative to designate at least one column of the logical table as a logical primary key column, and being further operative to form a normalized relational schema object representing the logical table, and being further operative, responsive to said normalized relational schema object, to generate one or more object classes associated with said normalized relational schema object; and

a repository which employs the one or more object classes and the logical table and respective corresponding logical primary key values to facilitate access of data in the at least one of the one or more physical tables in the relational database by the object oriented user application.

5. The apparatus of claim 4 wherein the at least one of the one or more physical tables is a denormalized table.

6. The apparatus of claim 5 further comprising a normalization processor operative to create foreign key relationships from the at least one of the one or more physical tables or the logical table to another physical or logical table having a physical or logical primary key.

7. The apparatus of claim 4 further including a discriminator constraint associated with the logical table.

8. A method for enabling access to a relational database from an object oriented program, comprising:

capturing one or more denormalized relational schema from said relational database, said one or more relational schema corresponding to a physical table segmented into rows and columns;

forming a normalized relational schema object representing a logical table, said logical table comprising a subset of said columns of said physical table; and

generating, responsive to said normalized relational schema object, one or more object class definitions associated with said normalized relational schema object.

9. The method of claim 8, further comprising:

creating, responsive to said capturing said one or more denormalized relational schema from said relational database, a respective one or more denormalized relational schema objects; and

wherein said forming said normalized relational schema object is responsive to said one or more denormalized relational schema objects.

10. The method of claim 8, wherein said one or more object class definitions are represented by at least one type definition for an object oriented programming language.

11. The method of claim 10, wherein said object oriented programming language is C++.

12. The method of claim 10, wherein said at least one type definition is predefined.

13. The method of claim 8, wherein said subset of said columns of said physical table is user defined.

14. The method of claim 8, wherein an identity of said normalized relational schema object is defined by a primary key constraint in one or more of said subset of said columns of said physical table.

15. A method for enabling access to a relational database from an object oriented program, comprising:

inputting a predetermined one or more denormalized relational schema objects, said set of one or more denormalized relational schema objects corresponding to a physical table in said relational database segmented into rows and columns;

forming a normalized schema object, responsive to said one or more denormalized relational schema objects, said normalized relational schema object representing a logical table comprising a subset of said columns of said physical table; and

generating, responsive to said normalized relational schema object, one or more object classes associated with said normalized relational schema object.

16. A system for enabling access to a relational database from an object oriented program, comprising:

a capture process for capturing one or more denormalized relational schema from said relational database, said one or more relational schema corresponding to a physical table segmented into rows and columns;

a normalization process for forming a normalized relational schema object representing a logical table, said logical table comprising a subset of said columns of said physical table; and

a mapping process for generating, responsive to said normalized relational schema object, one or more object classes associated with said normalized relational schema object.

17. A system for enabling access to a relational database from an object oriented program, comprising:

a normalization process for inputting one or more denormalized relational schema objects, said set of one or more denormalized relational schema objects corresponding to a physical table segmented into rows and columns, said normalization process further forming a normalized schema object, responsive to said set one or more denormalized relational schema objects, said normalized relational schema object representing a logical table comprising a subset of said columns of said physical table; and

a mapping process for generating, responsive to said normalized relational schema object, one or more object classes associated with said normalized relational schema object.

18. A system for enabling an object oriented user application to access a relational database having one or more physical tables segmented into rows and columns, comprising:

means for defining a logical table comprising a subset of columns from at least one of the one or more physical tables;

means for designating one column of the logical table as a logical primary key column;

means for forming a normalized relational schema object representing the logical table;

means for generating, responsive to the normalized relational schema object, one or more object classes associated with the normalized relational schema object; and

means for employing an object of an object class including the one or more object classes associated with the normalized relational schema object and a respective corresponding logical primary key value to access data in the at least one of the physical tables in the relational database.

19. A system for enabling access to a relational database from an object oriented program, comprising:

means for capturing one or more denormalized relational schema from said relational database, said one or more relational schema corresponding to a physical table segmented into rows and columns;

means for forming a normalized relational schema object representing a logical table, said logical table comprising a subset of said columns of said physical table; and

means for generating, responsive to said normalizing relational schema object, one or more object classes associated with said normalized relational schema object.

20. A system for enabling access to a relational database from an object oriented program, comprising:

means for inputting a predetermined set of one or more denormalized relational schema objects, said set one or more denormalized relational schema objects corresponding to a physical table segmented into rows and columns;

means for forming a normalized schema object, responsive to said set one or more denormalized relational schema objects, said normalized relational schema object representing a logical table comprising a subset of said columns of said physical table; and

means for generating, responsive to said normalized relational schema object, one or more object classes associated with said normalized relational schema object.

Description

CORSS REFERENCE TO RELATED APPLICATIONS

Not Applicable

STATEMENT REGARDING FEDERALLY SPONSORED RESEARCH OR DEVELOPMENT

This invention was made with government support. The U.S. government has certain rights in this invention.

BACKGROUND OF THE INVENTION

A need exists for interfacing object oriented software applications with relational databases. One method of interfacing an object oriented application with a relational database is to adapt the queries made by the application to the relational database. More particularly, object operations are translated into relational database calls. However, this technique is processor-intensive and sacrifices some of the advantages associated with the object oriented model. As a result, the object oriented software application is unable to function efficiently.

Another method of interfacing an object oriented application with a relational database is to translate database information into a format which is compatible with the object oriented application. Relational databases typically separate data into a plurality of tables through a process known as "normalization" to minimize duplication. A normalized relational database includes a plurality of tables, wherein each table includes at least one field and one key, and each field in each table is uniquely dependent upon the table's key. These tables can be directly translated into objects. However, normalized tables are structured quite differently from objects. More particularly, the collection of information in the fields of the tables often fails to match the collection of information that would typically be found in a well designed object. Consequently, such objects can cause problems for the software application.

BRIEF SUMMARY OF THE INVENTION

In accordance with the present invention logical tables and logical keys are employed to facilitate interaction between user applications and a relational database. Each logical table is a group of at least one column from a physical table or view associated with a relational database, and can be utilized like a physical table. Each logical key is a key between logical and physical tables that behaves like an actual foreign key. A normalization process allows creation of Ontos integrator relational schema objects from existing captured tables. Once a denormalized table is captured from a database the normalization process allows the user to define different logical tables using subsets of the columns of the table. One or more columns of the logical table are designated as primary key. The logical tables interact with the mapping process in the same manner as physical tables.

BRIEF DESCRIPTION OF THE DRAWING

Other features and advantages of the present invention will become apparent in light of the following detailed description of the drawing in which:

FIG. 1 is an integrator mapping process block diagram;

FIG. 2 is an integrator runtime block diagram;

FIG. 3 illustrates the integrator runtime system;

FIG. 4 illustrates creation of schema;

FIG. 5 illustrates the normalization process;

FIG. 6 illustrates an example of denormalization;

FIG. 7 further illustrates the normalization process;

FIG. 8 is an object model diagram;

FIG. 9 illustrates normalized schema form;

FIG. 10 illustrates integrator mapping schema;

FIG. 11 illustrates integrator mapping schema with logical tables;

FIG. 12 illustrates control flow for activating an object from normalized schema;

FIG. 13 illustrates control flow for creating an object in normalized schema;

FIG. 14 illustrates control flow for updating an object in normalized schema;

FIG. 15 illustrates control flow for deleting an object from normalized schema;

FIG. 16 illustrates control flow for inserting an object in a normalized schema collection;

FIG. 17 illustrates control flow for removing an object from a normalized schema collection;

FIG. 18 illustrates control flow for activating an object from denormalized schema;

FIG. 19 illustrates control flow for generating SQL for creating an object in denormalized schema;

FIG. 20 illustrates control flow for generating SQL for deleting an object in denormalized schema;

FIG. 21 illustrates control flow for generating SQL for inserting an object in denormalized schema;

FIG. 22 illustrates control flow for generating SQL for removing an object in denormalized schema; and

FIG. 23 illustrates denormalized schema.

DETAILED DESCRIPTION OF THE INVENTION

U.S. Pat. No. 5,542,078 issued Jul. 30, 1996 is incorporated herein by reference.

FIG. 1 illustrates a mapping process and its result. Mapping tools include an Ontos integrator ("integrator") repository 10 containing two major parts: relational schema objects 12 and mapping schema objects 14. The relational schema objects form one component, and the other component is a set of objects that maintains information on mappings between C++ types and relational schema objects. These objects are termed `Mapping Schema Objects.`

Distinct processes are provided by the mapping tools. A Capture Process 16 reads the relational schema from the relational database 18 and creates the Relational Schema Objects 12 in the integrator repository 10. The capture process allows a user to capture a schema, in its entirety or in part, from the supported relational database to create the Relational Schema Objects in the repository. These objects are used by the mapping process 20 to create mapping and runtime support structures in the repository 10. Both capture and mapping processes are available through a graphical tool Schema Mapper as well as through two independent command line interface tools. The capture and mapping processes are numbered to signify that the steps must be taken in that sequence.

FIG. 2 illustrates how the integrator repository 10 created by the mapping process is used by end users. A user program 22 accesses the relational database 18 through a runtime system 24. The runtime system uses the repository created by the processes described with reference to FIG. 1. The runtime system only reads the repository; it is not allowed to write any persistent information whatsoever to the repository.

FIG. 3 illustrates the integrator runtime system 24 (FIG. 2). The runtime system reads TypeMap objects 26, which are part of the Mapping Schema, and converts the requests from user programs into appropriate relational messages. The TypeMap objects in turn depend on the Relation Schema Objects to provide the necessary support to the Storage Manager object.

Through this process, the runtime system provides an object oriented view of the relational data in the relational database 18. The design of the runtime system is such that it does not prevent the user program 22 from independently accessing the relational database at the same time. The availability of such use, however, may be limited by the features of the relational database in use.

The capture process first creates schema for which the user is required to supply a name, valid relational database user and password. The basic unit for the capture process is a relation, table, view or stored procedure. The capture process creates persistent objects in the schema for each table or view selected by the user. This process of creating a repository persistent object for a selected relational database object is termed `Capture of the object from the relational database into schema.` All of the columns associated with the tables are captured only if both the tables involved in the foreign key relationship are captured into the schema. The capture process thus creates a particular representation of the chosen parts of the relational schema in a particular schema.

The integrator schema object described here defines the scope of the mapping process. The mapping processes can process relational tables, views, columns and keys only one schema at a time.

FIG. 4 shows some of the detail of integrator relational Schema objects 28 created from the captured relational database. The diagram shows only major integrator objects. One integrator object is created for each captured relational object, i.e., one OC.sub.-- RdbTable object for each table and one OC.sub.-- RdbColumn for each column captured. The capture process 30 is not limited to tables or views having the relational primary key constraint associated with them. Further, stored procedures support is provided.

In addition to creating a new schema, the capture process also allows recapture of already captured objects into an existing schema. In this process, all the changes made to the relational object in the relational database subsequent to the capture are also made to object representations already captured into the integrator schema. This process is termed as schema update.

The capture process also allows removal of captured objects from an integrator schema. In this process, the relational object representations created in the integrator schema are simply removed from a schema. All the other remaining objects in the schema are adjusted to account for the loss of the removed object. This process is called schema delete. Schema update and delete processes have significant effects on the mapping process.

Other Relational Schema Objects not specifically shown in the diagram are the primary and foreign key constraints. These integrator schema objects related to OC.sub.-- RdbTable objects are created in integrator schema for each primary and foreign key associated with the captured table automatically. The user is not allowed to choose the key constraints to be captured. The primary and foreign key constraints are an important part of relational database technology, which essentially identifies data uniqueness and how data in two different tables relate to each other. These constraints essentially define relational data integrity. Hence, this information is captured as is defined in the database.

Denormalization is a well known concept in the database art which describes a condition in which two related tables are compressed together into a single table. In denormalization in a relational database, two tables P and A have three columns each and primary keys. One of the columns of table A is a foreign key to table P. When these tables are denormalized into a single table AP it has five columns. FIG. 6 shows how the tables and any data in them would look in each case.

FIG. 6 illustrates an example of denormalization. Essential differences in the normal tables A, P and the denormalized table AP are that the table AP may not have a primary key defined on it, since it can contain empty rows. Also, data may be duplicated in table AP for a number of real objects in table P.

FIG. 5 illustrates a normalization process 32. The integrator normalization process provides new means to create new integrator Relational Schema Objects from existing captured tables, instead of from the database as is done in the capture process. Once a denormalized table such as table AP is captured from a database, the normalization process allows the user to define different tables using subsets of columns of the table AP. This new table is hence a subset of a real physical table in the database and is henceforth referred to as a logical table. The real database table is referred to as a physical table. In the process one or more columns of the logical table are designated as primary key. This primary key is also referred to as a logical primary key. Thus from table AP, logical tables P.sub.-- L and A.sub.-- L can be created from the table AP. These logical tables P.sub.-- L and A.sub.-- L are treated by the mapping process (FIG. 1) exactly as if they are real physical tables in the database.

The logical table definition is based on a concept of `Discriminator.` For each logical table definition, the user is required to supply the Discriminator. The discriminator is a valid "where" constraint clause which must be syntactically valid in an SQL Select statement on the physical table. The discriminator defines the basic constraint on the physical table that a row must satisfy in order to be visible through the logical table. Hence, not all the rows of the physical table are part of every logical table defined on that physical table. The rows of the physical table that do not satisfy the discriminator condition of a logical table are not considered part of the logical table.

FIG. 7 illustrates the normalization process 32. There is no restriction on which columns can be used in the logical primary key. The normalization process also provides means to create foreign key relationships from a physical or logical table to another physical or logical table having a physical or logical primary key.

Such table and key definitions exist only in integrator schema and do not exist in the relational database. The integrator runtime system, however, provides support such that to the user it appears as if the tables and keys are really defined in the relational database. The integrator runtime support provides all the functionality required to maintain the data integrity as defined by the logical foreign key relationships. The relational database itself is responsible for providing data integrity support for the physical keys. Subsequent sections describe integrator data integrity assurance for the denormalized schema structure.

Any captured physical table can be used to define logical tables. The physical table may or may not have a primary key or foreign keys of its own. In either case, the integrator ignores all key constraints while creating the logical table. The user must define new constraints for the new logical tables even if they are the same as the constraints on the physical table. There is no restriction on the number of logical tables that can be created on a single physical table. The mapping process either creates new types from a set of tables and foreign keys, or maps a set of tables and foreign keys to an existing type and its properties.

The integrator denormalization support provides maximum reuse of existing mapping technology. The process uses the integrator schema core objects created by the capture. Since the normalization process creates schema core objects in substantially the same manner as the capture process, no change is required in the mapping process to support the denormalized schema. A classify process involves creating C++ type definitions in the integrator schema from C++ header file(s). This is a significant step in integrator processes in which integrator allows mapping of tables to predefined types as well as to new types.

A validate process creates standard query language ("SQL") statements required at runtime for the normal table structure. This is user visible and a clearly separate and required user action. The process prepares a number of SQL statements that are required by the runtime system. These SQL statements contain placeholder flags which are filled by the runtime system for each message required to be sent to the database. This is a performance measure to save time at runtime by doing the work of generating the SQL statements beforehand.

A code generation process generates C++ code for the types in the integrator schema. This step is required in the use of the integrator product.

Runtime process support means that the integrator libraries provide support to create, modify and delete appropriate relational rows mapped integrator objects. The logical tables feature essentially means that integrator allows a single physical table to be split into virtually any number of separate tables and still preserves the data integrity as defined by the foreign key relationships.

The runtime libraries check for the denormalized structures in schema and take different routes for sending appropriate messages to the relational database. The primary route of the prepared statements provides access to the normal schema structure in which the data integrity is provided by the underlying relational database, whereas the denormalized tables are handled by an entirely different set of objects and the data integrity is ensured by integrator itself. This is a significant responsibility assumed and shouldered by integrator. In short, integrator guarantees that the user-defined logical tables and keys will behave exactly as if they were real tables and keys defined in the relational database.

The processing required in integrator to ensure the data integrity is significant. The create, modify, collection add and remove, and delete messages each require sufficiently different processing that may warrant a separate diagrammatic explanation. Interaction of create, modify and delete with the mapped denormalized collections is also a significant process.

Referring to FIGS. 8 and 9, the invention will now be described by way of example illustrated in a simple object model for which the relational database structures for normalized and two denormalized forms are shown. FIG. 8 is an object model based upon a baseball analogy in which Team 40 is an aggregation of Player 42, Division 44 is an aggregation of Team and Ball Park 46 has many Team 40. Further, Pitcher 48 is associated with Player through inheritance.

FIG. 9 illustrates normalized schema form. The relational schema are illustrated in a normal form that can be designed to provide the object model. The primary key column for a table is indicated by "(PK)."

Tables 1-5 describe a small data set that more clearly demonstrates the table structure. The same data is referenced to demonstrate schemas in subsequent sections.

                  TABLE 1
    ______________________________________
    Division
               NAMES
    ______________________________________
               American East
               New England
               American Midwest
    ______________________________________


              TABLE 2
    ______________________________________
    Ball.sub.-- Park
    NAME                OWNER
    ______________________________________
    Boston Garden       City of Boston
    Darlington Stadium  City of Darlington
    NY Stadium          City of New York
    ______________________________________


                                  TABLE 3
    __________________________________________________________________________
    Team
    NAME CITY SEASON.sub.-- LOSSES
                       SEASON.sub.-- WINS
                                HOME.sub.-- FIELD
                                        IN.sub.-- DIVISION
    __________________________________________________________________________
    Tigers
         Darlington
              7        25       Darlington
                                        American East
    Red Sox
         Boston
              1        31       Boston Garden
                                        New England
    New Sox
         Boston
              4        28       Boston Garden
                                        American East
    __________________________________________________________________________


              TABLE 4
    ______________________________________
    Player
    NAME             PLAYS FOR AGE
    ______________________________________
    John Andrews     Tigers    22
    Keith McSally    Tigers    31
    John Adams       Red Sox   24
    Mike Wallace     Red Sox   30
    Terry Stone      New Sox   21
    ______________________________________


              TABLE 5
    ______________________________________
    Pitcher
                          CURRENT   CURRENT
               PITCHER    SEASON    SEASON
    NAMES      NUMBER     LOSSES    WINS
    ______________________________________
    John Andrews
               22         7         25
    Keith McSally
               48         5         27
    John Adams 39         1         31
    Terry Stone
               54         10        22
    ______________________________________

                                      TABLE 6
    __________________________________________________________________________
    Team.sub.-- Division.sub.-- Ball.sub.-- Park
    TEAM.sub.-- NAME
            CITY SEASON.sub.-- LOSSES
                          SEASON.sub.-- WINS
                                  BALL.sub.-- PARK.sub.-- NAME
                                             OWNER
                                                  DIVISION.sub.-- NAME
    __________________________________________________________________________
    Tigers  Darlington
                 7        25      Darlington City of
                                                  American
                                  Stadium    Darlington
                                                  East
    Red Sox Boston
                 1        31      Boston     City of
                                                  New
                                  Garden     Boston
                                                  England
    New Sox Boston
                 4        28      Boston     City of
                                                  American
                                  Garden     Boston
                                                  East
                                  NY Stadium City of
                                             New York
                                                  American
                                                  Midwest
    __________________________________________________________________________

                  TABLE 7
    ______________________________________
    Player.sub.-- Pitcher
                                   CURRENT CURRENT
            PLAYS           PITCHER
                                   SEASON  SEASON
    NAME    FOR      AGE    NUMBER LOSSES  WINS
    ______________________________________
    John    Tigers   22     22     7       25
    Andrews
    Keith   Tigers   31     48     5       27
    McSally
    John Adams
            Red Sox  24     39     1       31
    Mike    Red Sox  30
    Wallace
    Terry Stone
            New Sox  30     54     10      22
    ______________________________________

                  TABLE 8
    ______________________________________
    Division
               NAME
    ______________________________________
               American East
               New England
               American Midwest
    ______________________________________


              TABLE 9
    ______________________________________
    Ball.sub.-- Park
    NAME                OWNER
    ______________________________________
    Boston Garden       City of Boston
    Darlington Stadium  City of Darlington
    NY Stadium          City of New York
    ______________________________________

• Inventors
  Pandit, Jayant G.;
• Assignee
  Ontos, Inc. (Lowell, MA)
• Published
  Aug-10-1999
• Current US Classes:
  707/100
  707/2
  707/3
  707/4
• Application #
  879241
• International Classes
  G06F 017/30
• Field of Search
  707/2 707/3 707/4 707/100 707/103
• Examiner
  Kulik; Paul V.
• Agent
  Weingarten, Schurgin, Gagnebin & Hayes LLP
• US Patent References:
  4853843
  4864497
  4953080
  4989132
  5008853
  5010478
  5079695
  5093914
  5113522
  5125091
  5129083
  5129084
  5136712
  5142674
  5157777
  5161223
  5161225
  5170466
  5175848
  5185885
  5187786
  5187787
  5187790
  5191522
  5193180
  5206951
  5227967
  5265206
  5283894
  5291583
  5291593
  5295256
  5327559
  5369761
  5386564
  5388264
  5499371
  5542078
  5701453
  5734887
  5778375