US 7,680,787 B2 · Database query generation method and system

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.

Claims15

Dependent claims

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.

6 more elements

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.

6 more elements

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.

9 more elements

Dependencies are inferred from the claim text.

Database query generation method and system

Life of this patent

Database query generation method and system

Life of this patent

Abstract

Claims15