System and method for filtering database results using dynamic composite queries

• US 7,958,105 B2
• Filed: 03/07/2008
• Issued: 06/07/2011
• Est. Priority Date: 03/07/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Rayfield, James T., Leff, Avraham, Salo, Timo J., Smith, Brandon J. W.
• Primary Examiner(s): Thai; Hanh B
• Application Number:
  US12/044,395
• Publication Number:
  US 20090228440A1
• Time in Patent Office: 1,187 Days
• Field of Search: 707/609, 707/625, 707/694, 707/706, 707/713, 707/781, 709/203, 713/201
• US Class Current: 707/706
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries

Abstract

A method, system and computer program product for retrieving data records to a client computer. An identifier of a base query is received from the client computer. The base query is configured to select a set of data records from a database that the client computer has permission to access. A modifying clause is also received from the client computer. The modifying clause comprises a directive restricting the set of data records to a subset of data records. The modifying clause may optionally comprise an expression for sorting. A combined query is generated from the base query and the modifying clause. The query is configured to return the subset of data records. The combined query is further configured to sort the subset of data records based on the value of the expression if the expression is present. The combined query is executed at the database.

Claims

1. A method for retrieving data records to a client computer, the method comprising:

receiving a request from the client computer, the request including an identifier of a base query and a modifying clause, the base query configured to select a set of data records from a database that the client computer has permission to access, wherein the base query is not received from the client, the modifying clause comprising a directive restricting the set of data records to a subset of data records;

generating a combined query from the base query and the modifying clause, the query configured to return the subset of data records; and

executing the combined query at the database.

2. The method of claim 1, wherein:

the directive further comprises a criterion to be satisfied by the subset of data records; and

the combined query is configured to exclude from the subset of data records any data record for which the criterion is not satisfied.

3. The method of claim 2, wherein the criterion is satisfied only for data located at one or more specified fields in a relational data schema.

4. The method of claim 2, wherein the criterion is satisfied only for data located at one or more specified records in a relational data schema.

5. The method of claim 1, wherein the directive includes at least one dynamically generated, non-constant variable.

6. The method of claim 1, wherein:

the modifying clause further comprises an expression; and

the combined query is further configured to sort the subset of data records based on the value of the expression.

7. The method of claim 1, further comprising parsing the modifying clause according to a grammar defining a structured syntax and vocabulary.

8. A system for retrieving data records to a client computer, the system comprising:

a server, the server comprising;

a computer processor;

a receiving unit configured to receive a request from the client computer, the request including an identifier of a base query and a modifying clause, the base query configured to select a set of data records from a database that the client computer has permission to access, the modifying clause comprising a directive restricting the set of data records to a subset of data records, wherein the base query is not received from the client; and

a generating unit configured to generate a combined query from the base query and the modifying clause, the query configured to return the subset of data records;

a database, the database configured to execute the combined query.

9. The system of claim 8, wherein:

the directive further comprises a criterion to be satisfied by the subset of data records; and

the combined query is configured to exclude from the subset of data records any data record for which the criterion is not satisfied.

10. The system of claim 8, wherein the directive is a non-constant.

11. The system of claim 8, wherein:

the modifying clause further comprises an expression;

the combined query is further configured to sort the subset of data records based on the value of the expression.

12. The system of claim 8, wherein the server further comprises a parsing unit configured to parse the modifying clause according to a grammar defining a structured syntax and vocabulary.

13. A computer program product embodied in non-transitory computer readable memory comprising:

computer readable program codes coupled to the non-transitory computer readable memory for retrieving data records to a client computer, the computer readable program codes configured to cause the program to;

receive a request from the client computer, the request including an identifier of a base query and a modifying clause, the base query configured to select a set of data records from a database that the client computer has permission to access, wherein the base query is not received from the client, the modifying clause comprising a directive restricting the set of data records to a subset of data records;

generate a combined query from the base query and the modifying clause, the query configured to return the subset of data records; and

execute the combined query at the database.

14. The computer program product of claim 13, wherein:

the directive further comprises a criterion to be satisfied by the subset of data records; and

the combined query is configured to exclude from the subset of data records any data record for which the criterion is not satisfied.

15. The computer program product of claim 14, wherein the criterion is satisfied only for data located at one or more specified fields in a relational data schema.

16. The computer program product of claim 14, wherein the criterion is satisfied only for data located at one or more specified records in a relational data schema.

17. The computer program product of claim 13, wherein the directive is a non-constant.

18. The computer program product of claim 13, wherein:

the modifying clause further comprises an expression; and

the combined query is further configured to sort the subset of data records based on the value of the expression.

19. The computer program product of claim 13, further comprising parsing the modifying clause according to a grammar defining a structured syntax and vocabulary.

20. The computer program product of claim 13, wherein the base query is coded in Structured Query Language.