Database query construction and handling

• US 7,984,058 B2
• Filed: 05/03/2006
• Issued: 07/19/2011
• Est. Priority Date: 06/02/2005
• Status: Active Grant

• Current Assignee: Callidus Software, Inc. (SAP SE)
• Original Assignee: Genius.com, Inc.
• Inventors: Flynn, Frank, DeLorenzo, Michael, Seidl, Robert
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Choi; Yuk Ting
• Application Number:
  US11/417,948
• Publication Number:
  US 20060277157A1
• Time in Patent Office: 1,903 Days
• Field of Search: None
• US Class Current: 707/759
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/24575 using context

Abstract

A method and apparatus is disclosed herein for constructing a complex database query. In one embodiment, the method comprises receiving a selection of meta data indicative of searchable data values stored in two or more database tables. The method also includes constructing a complex database query to search the two or more database tables based on the received selection of meta data.

Claims

1. A computer implemented method, comprising:

receiving, by a processor, a selection of meta data from a client system indicative of searchable data values stored in two or more database tables, wherein a first database table stores search groups and a second database table stores search conditions for each group;

constructing, by the processor, a complex database query, by concatenating structured query language (SQL) snippets together to form the complex database query, to search the two or more database tables based on the received selection of meta data;

identifying, by the processor, each condition of the complex database query with a unique condition identifier and a unique group identifier for a group to which the condition is a member;

storing, by the processor, query meta data with the unique condition identifier and the unique group identifier that represents the complex database query;

in response to a determination that one or more data items obtained from evaluation of the complex database query are being displayed by the client system, reconstructing, by the processor, the complex database query from the stored query meta data at periodic intervals to update the one or more database items independent of client system requests to update the one or more data items;

writing the complex database query to a tracking server, the tracking server to provide modified links to the client system for entry to a website, the modified links in a format that resembles a website address for the website served from a web server, but to resolve at the tracking server through which client system interactions with the website can be tracked;

for each periodic interval, the tracking server evaluating the complex database query against a tracking database coupled with the tracking server, the tracking database to store user clickflow data for client system selections of the modified links monitored and updated by the tracking server in real time; and

sending a result of the evaluation to the client system to update the display of the one or more data items to provide real time updates for results of the evaluated complex database query to a user without receiving a query request from the user.

2. The computer implemented method of claim 1, further comprising:

receiving resultant meta information, in response to evaluating the complex database query, corresponding to the constructed complex database query; and

dynamically generating a graphical user interface based on the received resultant meta information.

3. The computer implemented method of claim 1, wherein the complex query is a complex multi-table and multi-database query.

4. The computer implemented method of claim 1, wherein the meta data is a condition defined by a field, an operator, and a value.

5. The computer implemented method of claim 1, wherein the query is a compound query comprising a plurality of conditions.

6. The computer implemented method of claim 1, further comprising:

validating the selection of metadata, prior to constructing the complex database query, when the received selection includes user-inputted data.

7. The computer implemented method of claim 6, further comprising:

notifying a user when the selection of metadata includes invalid meta data.

8. The computer implemented method of claim 1, further comprising:

obtaining metadata indicative of all searchable data values associated with an identification of a user using a user interface;

dynamically constructing a user interface, prior to the receiving, to present the metadata indicative of all searchable data values.

9. The computer implemented method of claim 1, wherein the constructing is performed by a Hypertext Preprocessor (PHP) script.

10. The computer implemented method of claim 1, further comprising:

joining the two or more database tables into a single searchable database table.

11. The computer implemented method of claim 1, wherein the searchable data is clickflow data indicative of a user'"'"'s interactions with a web page.

12. The computer implemented method of claim 1, further comprising:

adding new searchable data values to the two or more database tables; and

updating metadata definitions for the new searchable data values.

37. The computer implemented method of claim 1, further comprising:

constructing multiple conditions for the reconstructed complex database query, wherein each condition is constructed by,

replacing a default value of a condition query with a meta data value for the complex database query, wherein the meta data value is obtained from the query meta data,

correlating the meta data value with an actual value to be queried, and

appending the actual value to be queried to an SQL snippet that corresponds to the current query; and

concatenating the constructed SQL snippets together to form the reconstructed complex database query.

13. A computer readable storage medium that provides instructions, which when executed by a processing system, causes said processing system to perform a method comprising:

receiving a selection of meta data from a client system indicative of searchable data values stored in two or more database tables, wherein a first database table stores search groups and a second database table stores search conditions for each group;

constructing a complex database query, by concatenating structured query language (SQL) snippets together to form the complex database query, to search the two or more database tables based on the received selection of meta data;

identifying each condition of the complex database query with a unique condition identifier and a unique group identifier for a group to which the condition is a member;

storing query meta data with the unique condition identifier and the unique group identifier that represents the complex database query;

in response to a determination that one or more data items obtained from evaluation of the complex database query are being displayed by the client system, reconstructing the complex database query from the stored query meta data at periodic intervals to update the one or more database items independent of client system requests to update the one or more data items;

writing the complex database query to a tracking server, the tracking server to provide modified links to the client system for entry to a website, the modified links in a format that resembles a website address for the website served from a web server, but to resolve at the tracking server through which client system interactions with the website can be tracked;

for each periodic interval, the tracking server evaluating the complex database query against a tracking database coupled with the tracking server, the tracking database to store user clickflow data for client system selections of the modified links monitored and updated by the tracking server in real time; and

sending a result of the evaluation to the client system to update the display of the one or more data items to provide real time updates for results of the evaluated complex database query to a user without receiving a query request from the user.

14. The computer readable medium of claim 13, further comprising:

receiving resultant meta information, in response to evaluating the complex database query, corresponding to the constructed complex database query; and

dynamically generating a graphical user interface based on the received resultant meta information.

15. The computer readable medium of claim 13, wherein the complex query is a complex multi-table and multi-database query.

16. The computer readable medium of claim 13, wherein the meta data is a condition defined by a field, an operator, and a value.

17. The computer readable medium of claim 13, wherein the query is a compound query comprising a plurality of conditions.

18. The computer readable medium of claim 13, further comprising:

validating the selection of metadata, prior to constructing the complex database query, when the received selection includes user-inputted data.

19. The computer readable medium of claim 18, further comprising:

notifying a user when the selection of metadata includes invalid meta data.

20. The computer readable medium of claim 13, further comprising:

obtaining metadata indicative of all searchable data values associated with an identification of a user using a user interface;

dynamically constructing a user interface, prior to the receiving, to present the metadata indicative of all searchable data values.

21. The computer readable medium of claim 13, wherein the constructing is performed by a Hypertext Preprocessor (PHP) script.

22. The computer readable medium of claim 13, further comprising:

joining the two or more database tables into a single searchable database table.

23. The computer readable medium of claim 13, wherein the searchable data is clickflow data indicative of a user'"'"'s interactions with a web page.

24. The computer readable medium of claim 13, further comprising:

adding new searchable data values to the two or more database tables; and

updating metadata definitions for the new searchable data values.

25. A system, comprising:

a database stored in a computer memory; and

a client, wherein the client is to;

receive a selection of meta data from a client system indicative of searchable data values stored in two or more database tables of the database stored in the computer memory, wherein a first database table stores search groups and a second database table stores search conditions for each group;

construct a complex database query, by concatenating structured query language (SQL) snippets together to form the complex database query, to search the two or more database tables based on the received selection of meta data;

identify each condition of the complex database query with a unique condition identifier and a unique group identifier for a group to which the condition is a member;

store query meta data with the unique condition identifier and the unique group identifier that represents the complex database query;

in response to a determination that one or more data items obtained from evaluation of the complex database query are being displayed by the client system, reconstruct the complex database query from the stored query meta data at periodic intervals to update the one or more database items independent of client system requests to update the one or more data items;

write the complex database query to a tracking server, the tracking server to provide modified links to the client system for entry to a website, the modified links in a format that resembles a website address for the website served from a web server, but to resolve at the tracking server through which client system interactions with the website can be tracked;

for each periodic interval, the tracking server to evaluate the complex database query against a tracking database coupled with the tracking server, the tracking database to store user clickflow data for client system selections of the modified links monitored and updated by the tracking server in real time; and

send a result of the evaluation to the client system to update the display of the one or more data items to provide real time updates for results of the evaluated complex database query to a user without receiving a query request from the user.

26. The system of claim 25, further comprising:

the client to;

receive resultant meta information, in response to the request, corresponding to the complex database query; and

dynamically generate a graphical user interface based on the received resultant meta information.

27. The system of claim 25, wherein the complex query is a complex multi-table and multi-database query.

28. The system of claim 25, wherein the meta data is a condition defined by a field, an operator, and a value.

29. The system of claim 25, wherein the query is a compound query comprising a plurality of conditions.

30. The system of claim 25, further comprising:

the client to;

validate the selection of metadata, prior to construction of the complex database query, when the received selection includes user-inputted data.

31. The system of claim 30, further comprising:

the client to;

notify a user when the selection of metadata includes invalid meta data.

32. The system of claim 25, further comprising:

the client to;

obtain metadata indicative of all searchable data values associated with an identification of a user using a user interface;

dynamically construct a user interface to present the metadata indicative of all searchable data values before the selection of meta data is received.

33. The system of claim 25, wherein the construction is performed by a Hypertext Preprocessor (PHP) script.

34. The system of claim 25, further comprising:

the client to;

join the two or more database tables into a single searchable database table.

35. The system of claim 25, wherein the searchable data is clickflow data indicative of a user'"'"'s interactions with a web page.

36. The system of claim 25, further comprising:

the client to;

adding new searchable data values to the two or more database tables; and

updating metadata definitions for the new searchable data values.