Method and system for composing a query for a database and traversing the database

• US 6,985,899 B2
• Filed: 11/27/2001
• Issued: 01/10/2006
• Est. Priority Date: 11/30/2000
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chan, Victor, Hubbard, Mark W., Wang, Fen
• Primary Examiner(s): Gaffin, Jeffrey
• Assistant Examiner(s): Veillard, Jacques
• Application Number:
  US09/996,127
• Publication Number:
  US 20020120620A1
• Time in Patent Office: 1,505 Days
• Field of Search: 707 2- 10, 707100-1041, 707/200, 707/203, 706/45, 706/922, 709/203, 709/229, 717/116, 717/140
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: Y10S 706/922 Computer program preparation
• 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/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A system and method of composing a query object for application against a database is provided. The method composes a selection clause for the query. Next, a criteria clause for the query is generated, with the criteria clause comprising input criteria related to the query, additional criteria specified against the query, and generated criteria based on a joint relationship. Next a source clause utilizing elements in the database accessed by the query is generated. A database traversal system and method is provided. The method identifies all tables directly accessible by each table and creates a data structure comprising an entry for each table. The entry comprises an identification field for each table and a link field identifying all tables directly accessible by each table. The data structure is traversed and an optimum path of the traversal paths utilizing data obtained from traversing the data structure is identified.

Claims

1. A computer-implemented method of composing a query for application against a database, said method comprising:

a) composing a selection clause for said query, said selection clause comprising a results set related to said query;

b) composing a criteria clause for said query, said criteria clause comprising input criteria related to said query and additional criteria specified against said query, wherein the input criteria are associated with tables, and wherein predetermined relationships among the tables are stored in a relationship dictionary; and

c) composing a source clause utilizing elements in said database identified by said query.

2. A method of composing a query for application against a database as claimed in claim 1, wherein said method further comprises the step of:

d) composing an ordering scheme for results of said query.

3. A method of composing a query for application against a database as claimed in claim 2, wherein said method further comprises the step of:

e) composing a grouping scheme for results of said query.

4. A method of composing a query for application against a database as claimed in claim 1, wherein said composing said criteria clause further comprises resolving joint relationships amongst said input criteria and said additional criteria.

5. A method of composing a query for application against a database as claimed in claim 4, wherein said composing said criteria clause further comprises adding said joint relationships to said criteria clause.

6. A method of composing a query for application against a database as claimed in claim 5, wherein said composing said source clause further comprises resolving a source after analyzing said selection clause and said criteria clause.

7. A method of composing a query for application against a database as claimed in claim 6, wherein said query is produced in SQL format.

8. A method of composing a query for application against a database as claimed in claim 7, wherein said method applies said query against said database and results of said query are provided to an output device.

9. A computer-implemented query transaction system comprising:

a computer;

access to a database associated with said computer; and

a query processing program operating on said computer and generating a query for said database, said query processing program comprising;

a selection clause composing module for creating a selection clause for said query, said selection clause module producing a results set related to said query;

a criteria clause composing module for creating a criteria clause for said query, said criteria clause module processing input criteria related to said query and additional criteria specified against said query, wherein the input criteria are associated with tables, and wherein predetermined relationships among the tables are stored in a relationship dictionary; and

a source clause composing module utilizing elements in said database identified by said query.

10. A query transaction system as claimed in claim 9, wherein said query processing program further comprises an ordering module for results of said query.

11. A query transaction system as claimed in claim 9, wherein said query processing program further comprises a grouping module for results of said query.

12. A query transaction system as claimed in claim 9, wherein said criteria clause composing module further comprises a joint relationships resolving module associating said input criteria to said additional criteria.

13. A query transaction system as claimed in claim 12, wherein said criteria clause composing module further comprises a module adding said joint relationships to said criteria clause.

14. A query transaction system as claim in claim 13, wherein said source clause composing module resolves said source after analyzing said selection clause and said criteria clause.

15. A computer readable information storage medium including a computer readable program encoded on said medium, said program comprising a method of composing a query for application against a database, said method comprising:

composing a selection clause for said query, said selection clause comprising a results set related to said query;

composing a criteria clause for said query, said criteria clause comprising input criteria related to said query and additional criteria specified against said query, wherein the input criteria are associated with tables, and wherein predetermined relationships among the tables are stored in a relationship dictionary; and

composing a source clause utilizing elements in said database identified by said query.

16. The computer readable information storage medium in claim 15, wherein said method of said computer program further comprises composing an ordering scheme for results of said query.

17. The computer readable information storage medium in claim 16, wherein said method of said computer program further comprises composing a grouping scheme for results of said query.

18. The computer readable information storage medium in claim 15, wherein said method of said computer program composes said criteria clause by resolving joint relationships amongst said input criteria and said additional criteria.

19. The computer readable information storage medium in claim 18, wherein said method of said computer program composes said criteria clause by adding said joint relationships to said criteria clause.

20. The computer readable information storage medium in claim 19, wherein said method of composing said source clause further comprises resolving a source related to said database after analyzing said selection clause and said criteria clause.

21. The computer readable information storage medium in claim 20, wherein said query is applied against said database and results of said query are provided to an output device.