System and method for generating an optimized set of relational queries for fetching data from a relational database management system in response to object queries received from an object oriented environment

• US 5,765,159 A
• Filed: 05/21/1997
• Issued: 06/09/1998
• Est. Priority Date: 12/29/1994
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Srinivasan, Venkatachary
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US08/870,581
• Time in Patent Office: 384 Days
• Field of Search: 395/613, 395/603, 395/604, 395/611, 395/614, 707/3, 707/4, 707/102, 707/103, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

The processing of object-oriented queries so as to retrieve data from a relational database management system (RDBMS) is described. An object-oriented query and at least one prefetch path are received from an object-oriented source (such as an application or user) . The prefetch path identifies one or more objects which are desired to be constructed. The object-oriented query is translated to a translated object query, where the translated object query is a relational database query capable of retrieving from the RDBMS data to initialize base attributes of top-level objects identified by the object-oriented query. A set of relational queries is generated from the translated object query and the prefetch path. These relational queries, which are capable of retrieving from the RDBMS data needed to construct the objects identified by the prefetch path, are processed by the RDBMS.

Claims

1. A method of processing object-oriented queries so as to retrieve data from a relational database management system (RDBMS), comprising the steps of:

(1) receiving an object-oriented query and at least one prefetch path from an object-oriented source, said at least one prefetch path identifying one or more objects which are desired to be constructed;

(2) translating said object-oriented query to a translated object query, said translated object query being a relational database query capable of retrieving from said RDBMS data to initialize base attributes of top-level objects identified by said object-oriented query;

(3) generating from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said RDBMS data to construct said objects identified by said at least one prefetch path; and

(4) causing said RDBMS to process said set of relational queries.

2. The method of claim 1 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein step (3) comprises the steps of:

(a) determining whether said prefetch path component defines a (0,1)-to-(0,1) relationship;

(b) if said prefetch path component defines a (0,1)-to-(0,1) relationship, then;

(i) generating a first relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class not pointing to objects along said prefetch path component, and which satisfy said condition list; and

(ii) generating a second relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said top-level class which point to objects along said prefetch path component, and which satisfy said condition list, said second relational query also operating to retrieve from the RDBMS data needed to construct said objects along said prefetch path component.

3. The method of claim 1 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein step (3) comprises the steps of:

(a) determining whether said prefetch path component defines a (1,1)-to-(1,1) relationship; and

(b) if said prefetch path component defines a (1,1)-to-(1,1) relationship, then generating a relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class which point to objects along said prefetch path component, and which satisfy said condition list, said relational query also operating to retrieve from the RDBMS data needed to construct said objects along said prefetch path component.

4. The method of claim 1 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein step (3) comprises the steps of:

(a) determining whether said prefetch path component defines a one-to-many relationship;

(b) if said prefetch path component defines a one-to-many relationship, then;

(i) generating a first relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class that satisfy said condition list; and

(ii) generating a second relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects along said prefetch path component that are pointed to by said objects of said top-level class.

5. The method of claim 4 in which said top-level class maps to a top-level table, and said prefetch path component identifies a prefetch class that maps to a prefetch table, wherein step (b) further comprises the steps of:

(iii) generating a first relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said first relational query according to a foreign key in said top-level table that identifies tuples in said prefetch table; and

(iv) generating a second relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said second relational query according to a primary key of said prefetch table.

6. The method of claim 5, further comprising the step of:

(5) merging said sorted tuples retrieved by operation of said first and second relational queries.

7. The method of claim 1 in which said object-oriented query includes a condition list, said at least one prefetch path includes at least one prefetch path component, said object-oriented query identifies a top-level class that maps to a top-level table, said prefetch path component identifies a prefetch class that maps to a prefetch table, and in which an intermediate table is maintained by the RDBMS that stores a top-level foreign key from said top-level table that references said prefetch table, and a prefetch foreign key from said prefetch table that references said top-level table, wherein step (3) comprises the steps of:

(a) determining whether said prefetch path component defines a many-to-many relationship;

(b) if said prefetch path component defines a manyto-many relationship, then;

(i) generating a first relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said top-level class that satisfy said condition list;

(ii) generating a second relational query which, when processed by the RDBMS, operates to retrieve from said RDBMS said top-level foreign key and said prefetch foreign key from tuples of said intermediate table that satisfy said condition list; and

(iii) generating a third relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said prefetch class that satisfy said condition list and that are pointed to by said top-level class objects as indicated by said retrieved top-level and prefetch foreign keys.

8. The method of claim 7, wherein step (b) further comprises the steps of:

(iv) generating a first relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said first relational query according to a primary key of said top-level table;

(v) generating a second relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said second relational query according to said top-level foreign key; and

(vi) generating a third relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said third relational query according to a primary key of said prefetch table.

9. The method of claim 8, further comprising the step of:

(5) merging said sorted tuples retrieved by operation of said first, second, and third relational queries.

10. The method of claim 1, wherein step (3) comprises the steps of:

(a) generating a nested path representation of said at least one prefetch path; and

(b) traversing said nested path representation to generate said set of relational queries.

11. The method of claim 1, wherein said at least one prefetch path specifies a class pointed to by a relationship attribute, whereby said one or more objects identified by the prefetch path correspond to said class pointed to by said relationship attribute.

12. A method of processing object-oriented queries so as to retrieve data from a non-object oriented database management system (DBMS), comprising the steps of:

(1) receiving a translated object query generated from an object-oriented query having at least one prefetch path, said at least one prefetch path identifying one or more objects which are desired to be constructed, said translated object query being a relational database query capable of retrieving from said DBMS data to initialize base attributes of top-level objects identified by said object-oriented query;

(2) generating from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said DBMS data to construct said objects identified by said at least one prefetch path; and

(3) causing said DBMS to process said set of relational queries.

13. A database system, comprising:

a relational database management system (RDBMS);

an object-relational database gateway for receiving an object-oriented query and at least one prefetch path from an object-oriented source, said at least one prefetch path identifying one or more objects which are desired to be constructed;

said gateway comprising;

a first query generator for translating said object-oriented query to a translated object query, said translated object query being a relational database query capable of retrieving from said RDBMS data to initialize base attributes of top-level objects identified by said object-oriented query;

a second query generator for generating from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said RDBMS data to construct said objects identified by said at least one prefetch path; and

means for causing said RDBMS to process said set of relational queries.

14. The system of claim 13 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein said second query generator comprises:

means for determining whether said prefetch path component defines a (0,1)-to-(0,1) relationship;

means for generating first and second relational queries when said prefetch path component defines a (0,1)-to-(0,1) relationship, wherein;

(i) said first relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class not pointing to objects along said prefetch path component, and which satisfy said condition list; and

(ii) said second relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said top-level class which point to objects along said prefetch path component, and which satisfy said condition list, said second relational query also operating to retrieve from the RDBMS data needed to construct said objects along said prefetch path component.

15. The system of claim 13 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein said second query generator comprises:

means for determining whether said prefetch path component defines a (1,1)-to-(1,1) relationship; and

means for generating, if said prefetch path component defines a (1,1)-to-(1,1) relationship, a relational query which, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class which point to objects along said prefetch path component, and which satisfy said condition list, said relational query also operating to retrieve from the RDBMS data needed to construct said objects along said prefetch path component.

16. The system of claim 13 in which said object-oriented query includes a condition list, and in which said at least one prefetch path includes at least one prefetch path component, wherein said second query generator comprises:

means for determining whether said prefetch path component defines a one-to-many relationship;

means for generating first and second relational queries when said prefetch path component defines a one-to-many relationship, wherein;

(i) said first relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of a top-level class that satisfy said condition list; and

(ii) said second relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects along said prefetch path component that are pointed to by said objects of said top-level class.

17. The system of claim 16 in which said top-level class maps to a top-level table, and said prefetch path component identifies a prefetch class that maps to a prefetch table, wherein said second query generator further comprises:

means for generating a first relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said first relational query according to a foreign key in said top-level table that identifies tuples in said prefetch table; and

means for generating a second relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said second relational query according to a primary key of said prefetch table.

18. The system of claim 17, further comprising:

means for merging said sorted tuples retrieved by operation of said first and second relational queries.

19. The system of claim 13 in which said object-oriented query includes a condition list, said at least one prefetch path includes at least one prefetch path component, said object-oriented query identifies a top-level class that maps to a top-level table, said prefetch path component identifies a prefetch class that maps to a prefetch table, wherein said RDBMS maintains an intermediate table that stores a top-level foreign key from said top-level table that references said prefetch table, and a prefetch foreign key from said prefetch table that references said top-level table, and wherein said second query generator comprises:

means for determining whether said prefetch path component defines a many-to-many relationship;

means for generating first, second, and third relational queries when said prefetch path component defines a many-to-many relationship, wherein;

(i) said first relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said top-level class that satisfy said condition list;

(ii) said second relational query, when processed by the RDBMS, operates to retrieve from said RDBMS said top-level foreign key and said prefetch foreign key from tuples of said intermediate table that satisfy said condition list; and

(iii) said third relational query, when processed by the RDBMS, operates to retrieve from the RDBMS data needed to construct objects of said prefetch class that satisfy said condition list and that are pointed to by said top-level class objects as indicated by said retrieved top-level and prefetch foreign keys.

20. The system of claim 19, wherein said second query generator further comprises:

means for generating a first relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said first relational query according to a primary key of said top-level table;

means for generating a second relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said second relational query according to said top-level foreign key; and

means for generating a third relational database command which, when processed by the RDBMS, causes the RDBMS to sort tuples retrieved by operation of said third relational query according to a primary key of said prefetch table.

21. The system of claim 20, further comprising:

means for merging said sorted tuples retrieved by operation of said first, second, and third relational queries.

22. The database system of claim 13, wherein said at least one prefetch path specifies a class pointed to by a relationship, whereby said one or more objects identified by the prefetch path correspond to said class pointed to by said relationship attribute.

23. A computer system, comprising:

a processor;

a relational database management system (RDBMS);

a controller for enabling said processor to process object-oriented queries so as to retrieve data from said RDBMS, comprising;

means for enabling said processor to receive an object-oriented query and at least one prefetch path from an object-oriented source, said at least one prefetch path identifying one or more objects which are desired to be constructed;

a first query generator for enabling said processor to translate said object-oriented query to a translated object query, said translated object query being a relational database query capable of retrieving from said RDBMS data to initialize base attributes of top-level objects identified by said object-oriented query;

a second query generator for enabling said processor to generate from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said RDBMS data to construct said objects identified by said at least one prefetch path; and

means for enabling said processor to cause said RDBMS to process said set of relational queries.

24. The computer system of claim 23, wherein said at least one prefetch path specifies a class pointed to by a relationship, whereby said one or more objects identified by the prefetch path correspond to said class pointed to by said relationship attribute.

25. A controller, for use in a computer system comprising a processor and a relational database management system (RDBMS), for enabling the processor to process object-oriented queries so as to retrieve data from said RDBMS, comprising:

means for enabling the processor to receive an object-oriented query and at least one prefetch path from an object-oriented source, said at least one prefetch path identifying one or more objects which are desired to be constructed;

a first query generator for enabling the processor to translate said object-oriented query to a translated object query, said translated object query being a relational database query capable of retrieving from said RDBMS data to initialize base attributes of top-level objects identified by said object-oriented query;

a second query generator for enabling the processor to generate from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said RDBMS data to construct said objects identified by said at least one prefetch path; and

means for enabling the processor to cause said RDBMS to process said set of relational queries.

26. A method of processing object-oriented queries so as to retrieve data from a relational database management system (RDBMS), comprising the steps of:

(1) receiving an object-oriented query and at least one prefetch path from an object-oriented source, said at least one prefetch path identifying one or more objects which are desired to be constructed;

(2) translating said object-oriented query to a translated object query, said translated object query being a relational database query capable of retrieving data from said RDBMS; and

(3) generating from said translated object query and said at least one prefetch path a set of relational queries capable of retrieving from said RDBMS data to construct said objects identified by said at least one prefetch path.

27. The method of claim 26 further comprising the step of:

(4) causing said RDBMS to process said set of relational queries.

28. The method of claim 26, wherein said translated object query retrieves data from said RDBMS data to initialize base attributes of top-level objects identified by said object-oriented query.