Database query generation method and system

• US 7,680,787 B2
• Filed: 04/04/2007
• Issued: 03/16/2010
• Est. Priority Date: 04/06/2006
• Status: Active Grant

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Di Giulio, Domenico
• Primary Examiner(s): Alam; Shahid A
• Application Number:
  US11/696,382
• Publication Number:
  US 20070239667A1
• Time in Patent Office: 1,077 Days
• Field of Search: 707/2, 707/5, 707/10, 707/102, 715/234
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A process for generating SQL queries to retrieve requested information from a database, whilst applying requested search filters, involving defining a “base” query, which is the simplest SQL query that can be executed to retrieve the information requested by the user (i.e. without applying any search filter), and then use a separate module for each search filter to detect the existence of the table that is required to apply that filter, and possibly add a “WHERE” condition that includes the required table using a sub-query. For each module, if the table required to filter a particular attribute exists in the current query, the “WHERE” condition is added on that table, without adding any sub-query. If on the other hand the required table does not exist in the current query, a sub-query that navigates associations from the tables involved in the current query to the required on is added, and the “WHERE” condition is added to the required table within the sub-query.

Claims

1. A computer implemented method of interrogating a database comprising a plurality of tables, said method comprising the steps of:

defining a set of anticipated database queries directed to one or more of said tables in the database, the database executing in the data processing system;

generating a base query directed only to tables common to all of said anticipated database queries;

for each anticipated database query, generating a subquery module directed to tables not covered by said base query and required by a respective anticipated database query, wherein the subquery module includes logic to create a subquery and logic to create a filter;

receiving a request for information from said database;

selecting a subquery module directed to tables not covered by said base query and required by a respective anticipated database query;

adding an output of said selected subquery module to said base query to form a refined query, wherein the logic included in the subquery module further includes logic for not creating the subquery when a table used in the filter already exists in the refined query;

submitting said refined query to said database; and

responsive to submitting said refined query, receiving data from said database.

2. The method of claim 1 wherein said step of selecting comprises selecting a plurality of subquery modules corresponding in aggregate all tables not covered by said base query and required by a respective anticipated database query, and wherein said refined query comprises said base query and outputs of said plurality of subquery modules.

3. The method of claim 1 wherein said base query, an output of said subquery module and said refined query are expressed in the structured query language.

4. The method of claim 3 wherein an output of said subquery module is added to said base module by means of the structured query language expression “

where”

.

5. The method of claim 1 comprising the further steps of:

generating a data map describing the structure of table instances implicated in said base query;

traversing said datamap so as to determine the linkage between said base query and said subquery module; and

adding a subquery predicate to said base query as a function of said linkage.

6. A computer program product in a computer readable tangible storage medium comprising instructions for carrying out the steps of a method when said computer program is executed on a computer, the method comprising the steps of:

defining a set of anticipated database queries directed to one or more of said tables, generating a base query directed only to tables common to all of said anticipated database queries;

for each anticipated database query, generating a subquery module directed to tables not covered by said base query and required by a respective anticipated database query, wherein the subquery module includes logic to create a subquery and logic to create a filter;

receiving a request for information from said database;

selecting a subquery module directed to tables not covered by said base query and required by a respective anticipated database query; and

adding an output of said selected subquery module to said base query to form a refined query, wherein the logic included in the subquery module further includes logic for not creating the subquery when a table used in the filter already exists in the refined query;

submitting said refined query to said database; and

responsive to submitting said refined query, receiving data from said database.

7. The computer program product of claim 6 wherein said step of selecting comprises selecting a plurality of subquery modules corresponding in aggregate all tables not covered by said base query and required by a respective anticipated database query, and wherein said refined query comprises said base query and outputs of said plurality of subquery modules.

8. The computer program product of claim 6 wherein said base query, an output of said subquery module and said refined query are expressed in the structured query language.

9. The computer program product of claim 8 wherein an output of said subquery module is added to said base module by means of the structured query language expression “

where”

.

10. The computer program product of claim 6 comprising the further steps of:

generating a data map describing the structure of table instances implicated in said base query;

traversing said datamap so as to determine the linkage between said base query and said subquery module; and

adding a subquery predicate to said base query as a function of said linkage.

11. A system for interrogating a database comprising a plurality of tables, comprising:

a storage device including a storage medium, wherein the storage device stores computer usable program code; and

a processor, wherein the processor executes the computer usable program code, and wherein the computer usable program code comprises;

computer usable code for defining a set of anticipated database queries directed to one or more of said tables;

computer usable code for generating a base query directed only to tables common to all of said anticipated database queries;

computer usable code for generating a subquery module directed to tables not covered by said base query and required by a respective anticipated database query, wherein the subquery module includes logic to create a subquery and logic to create a filter;

computer usable code for receiving a request for information from said database;

computer usable code for selecting a subquery module directed to tables not covered by said base query and required by a respective anticipated database query;

computer usable code for adding an output of said selected subquery module to said base query to form a refined query, wherein the logic included in the subquery module further includes logic for not creating the subquery when a table used in the filter already exists in the refined query;

computer usable code for submitting said refined query to said database; and

computer usable code for, responsive to submitting said refined query, receiving data from said database.

12. The system of claim 11 wherein said computer usable code for selecting comprises computer usable code for selecting a plurality of subquery modules corresponding in aggregate all tables not covered by said base query and required by a respective anticipated database query, and wherein said refined query comprises said base query and outputs of said plurality of subquery modules.

13. The system of claim 11 wherein said base query, an output of said subquery module and said refined query are expressed in the structured query language.

14. The system of claim 13 wherein an output of said subquery module is added to said base module by means of the structured query language expression “

where”

.

15. The system of claim 11 further comprising:

computer usable code for generating a data map describing the structure of table instances implicated in said base query;

computer usable code for traversing said datamap so as to determine the linkage between said base query and said subquery module; and

computer usable code for adding a subquery predicate to said base query as a function of said linkage.