Dynamic graphical user interface and query logic SQL generator used for developing Web-based database applications

• US 20050102284A1
• Filed: 11/10/2003
• Published: 05/12/2005
• Est. Priority Date: 11/10/2003
• Status: Abandoned Application

• Current Assignee: LSI Logic Corporation (Broadcom, Inc.)
• Original Assignee: LSI Logic Corporation (Broadcom, Inc.)
• Inventors: Srinivasan, Chandramouli, Balaji, Ekambaram, Ganesan, Balaji
• Application Number:
  US10/705,545
• Publication Number:
  US 20050102284A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence

Abstract

A method and system for dynamically generating database queries is disclosed. The method and system include storing web interface data, including query attributes for a database, in one more tables. The attributes are then retrieved from the tables and displayed in a graphical user interface web page for user selection. Based on the attributes selected by the user, a SQL query is dynamically generated. The method and system further include displaying results of the SQL query to the user in graphical format, thereby enabling dynamic generation of custom queries.

Claims

1. A method for dynamically generating database queries, comprising:

(a) storing query web interface data, including attributes for a database, in one more tables;

(b) retrieving the attributes from the table and displaying the attributes on a graphical user interface web page for user selection;

(c) dynamically generating a SQL query based on the attributes selected by the user; and

(d) displaying results of the SQL query to the user in graphical format, thereby enabling dynamic generation of custom queries.

2. The method of claim 1 wherein step (b) further includes the step of:

displaying the attributes as a functionally categorized listing of query attributes.

3. The method of claim 2 wherein the graphic format for displaying the results includes an X-axis and Y-axis.

4. The method of claim 3 wherein step (b) further includes the step of:

in order to generate the SQL query, requiring at least a first attribute to be plotted along the X-axis, a second attribute to be plotted along the Y-axis, wherein the first attribute comprises an X attributes and the second attribute comprises a Y attribute, and a process factor to apply to the Y attribute.

5. The method of claim 4 wherein step (b) further includes the step of:

allowing the user to select a series attribute, wherein the series attribute represents a query parameter that is used to group Y attribute values plotted.

6. The method of claim 5 wherein step (b) further includes the step of:

allowing the user to select a filter, wherein the filter is a group of attributes that are used to restrict the scope of a query.

7. The method of claim 2 wherein step (b) further includes the step of:

allowing the user to form a query from a basic query page or a query customization page.

8. The method of claim 7 wherein step (b) further includes the step of:

displaying on the basic query page rows of attributes, where each row includes a field for an attribute type, an attribute description, and process factors that are used to calculate Y attribute values.

9. The method of claim 7 wherein step (b) further includes the step of:

allowing queries to be formed as single attribute queries or multiple attribute queries, wherein a single attribute query only includes an X attribute where a result of a query is displayed as a distribution, and wherein in a multiple attribute query, the user chooses X and Y attributes.

10. The method of claim 1 wherein step (a) further includes the step of:

storing the attribute data in at least two attribute tables.

11. The method of claim 10 wherein step (c) further includes the step of:

generating the query by inserting into a SQL SELECT statement, table and column names for the selected attributes, an X attribute value set, and a series value set.

12. The method of claim 11 wherein step (c) further includes the step of:

retrieving the table and column names for the selected attributes from one or more of the attribute tables.

13. The method of claim 12 wherein step (c) further includes the step of:

inserting the X attribute value set, and a series value set into a WHERE clause of the SQL statement.

14. The method of claim 13 wherein step (c) further includes the step of:

creating respective SQL SELECT statements joined together by a UNION statement for each of the X attribute values.

15. A dynamic query generator system, comprising

a client computer coupled to a network;

a server coupled to the network in communication with the client computer; and

a query engine executing on the server, the query engine functioning to,

generate and display GUI pages on the client computer for user selection of database attributes,

using the inputs provided by the user to automatically generate a SQL query to retrieve data from a database, and

display results of the query to the user in graphical format, thereby enabling dynamic generation of custom queries.

16. The system of claim 15 wherein the query engine comprises a page builder, page builder tables, a query processor, a database layer, and presentation logic.

17. The system of claim 16 wherein the page builder tables store the attributes.

18. The system of claim 17 wherein the page builder displays the attributes on the GUI pages by accessing the attributes from the page builder tables.

19. The system of claim 18 wherein the query processor generates SQL statements based on the attributes chosen by the user, and the database layer comprises the SQL queries that are generated by the query processor to retrieve information from the database.

20. The system of claim 19 wherein the presentation logic implements a charting engine that displays the results of the executed SQL query to the user in tabular or chart format.