Web-based user interface for searching metadata-driven relational databases

• US 20060155689A1
• Filed: 07/22/2005
• Published: 07/13/2006
• Est. Priority Date: 07/23/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Backstrom, Carl, Blakeley, Michael, Kutz, Syme
• Granted Patent: US 7,844,587 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques

Abstract

Web-based user interface systems and methods for searching metadata-driven relational databases. Instead of requiring developers to determine the exact type and number of search criteria for each application, the present invention advantageously provides a user-customizable search interface that, in certain aspects, leverages dynamic HTML, JavaScript, and dynamic SQL. A user may choose the exact type and number of search parameters, enabling the user to build a custom report without requiring the user to write any code. The system dynamically builds a SQL search query based on the user identified search parameters. Once a user has built a custom search using the interface, that search may be saved for re-use, and shared with other users.

Claims

1. A computer-implemented method of dynamically building SQL queries for use in a relational database system, the method comprising:

providing a query builder page to a client system over a network connection, said page including at least one search parameter selection field including a plurality of searchable types, at least one operator selection field and at least one value selection field;

receiving a user selection of a first searchable type in the at least one search parameter selection field from the client system;

receiving a user selection of a first operator in the at least one operator parameter selection field from the client system;

receiving a user selection of a first value in the at least one search value parameter selection field from the client system; and

dynamically building a SQL query based on the user selections in the parameter selection field, operator selection field and value selection field.

2. The method of claim 1, further comprising saving the user selected parameters to a memory unit.

3. The method of claim 1, wherein the query builder page includes a logical operator selection field including a plurality of logic operator types, and a selectable option for providing additional search parameter selection fields on the page.

4. The method of claim 1, wherein the value selection field includes one of a text entry box and a drop down list including a plurality of values.

5. The method of claim 1, wherein the plurality of searchable types are determined based on whether the types are defined in a database metadata table as being searchable.

6. The method of claim 1, further including:

receiving a user selection of a logical operator parameter from the client system; and

receiving a user selection of a second searchable type in a second search parameter selection field from the client system,

wherein the SQL query is built using the logical operator parameter and the second searchable type.

7. The method of claim 6, wherein the logical operator includes one of an AND operator and an OR operator.

8. The method of claim 1, wherein the at least one operator selection field includes one of a mathematical operator and a [. . . ] operator.

9. The method of claim 1, wherein building a SQL query includes generating a SQL query using one or more lookup tables that define the searchable types and values.

10. The method of claim 9, wherein generating includes pulling rows out of a lookup table using JavaScript responsive to the user selections.

11. A database system, comprising:

a metadata-driven relational database, including a plurality of metadata lookup tables;

a server module for providing a query building page to a client system over a network connection, wherein the page includes at least one search parameter selection field including a plurality of searchable types, at least one operator selection field and at least one value selection field; and

a SQL engine for automatically generating SQL queries in response to user selections in the selection fields of the query building page.

12. The system of claim 11, further comprising a memory unit for storing the user selections.

13. The system of claim 11, wherein the value selection field includes one of a text entry box and a drop down list including a plurality of values.

14. The system of claim 11, wherein the metadata tables include lookup values for said plurality of searchable types

15. The system of claim 11, wherein the SQL engine generates queries using JavaScript to pull rows out of the metadata tables.

16. A computer-readable medium including code for controlling a processor to dynamically generate a SQL query for searching a database, the code including instructions to:

provide a query builder page to a client system over a network connection, said page including at least one search parameter selection field including a plurality of searchable types, at least one operator selection field and at least one value selection field;

receive user selected search parameters from the client system, the user selected search parameters including a user selection of a first searchable type in the at least one search parameter selection field, a user selection of a first operator in the at least one operator parameter selection field, and a user selection of a first value in the at least one search value parameter selection field; and

automatically build a SQL query based on the received user selected search parameters.

17. The computer-readable medium of claim 16, further including instructions to store the user selected search parameters to a memory unit in the database system.

18. The computer-readable medium of claim 17, further including instructions to retrieve the user selected search parameters and generate a second SQL query based on the user selected search parameters in response to a request by the user.

19. The computer-readable medium of claim 18, wherein the user request is a request to share the search parameters with a second user.

20. The computer-readable medium of claim 18, wherein the user request is a request to re-execute the original search, and wherein the code includes instructions to optimize the second SQL query.

21. The computer-readable medium of claim 16, wherein the value selection field includes one of a text entry box and a drop down list including a plurality of selectable values.

22. The computer-readable medium of claim 16, wherein the query builder page includes a logical operator selection field including a plurality of logic operator types, and a selectable option for providing additional search parameter selection fields on the page.

23. The computer-readable medium of claim 16, wherein the plurality of searchable types are determined based on whether the types are defined in a database metadata table as being searchable.

24. A computer-implemented method of performing a web-based search on a relational database system, the method comprising:

receiving a query builder page from a database server of the relational database system over a network connection, said page including at least one search parameter selection field including a plurality of searchable types, at least one operator selection field and at least one value selection field;

receiving user selected search parameters, the user selected search parameters including a first searchable type in the at least one search parameter selection field from the client system, a first operator in the at least one operator parameter selection field, and a first value in the at least one search value parameter selection field;

transmitting the user selected search parameters to the database server; and

receiving a search response page from the database server responsive to transmission of the user selected search parameters,

wherein the database server dynamically builds a SQL query based on the user selected search parameters and executes the SQL query to generate the search response page.

25. The method of claim 24, further comprising saving the user selected search parameters to a memory unit.

26. The method of claim 24, wherein the query builder page includes a logical operator selection field including a plurality of logic operator types, and a selectable option for providing additional search parameter selection fields on the page.

27. The method of claim 24, wherein the value selection field includes one of a text entry box and a drop down list including a plurality of selectable values.

28. The method of claim 24, wherein the plurality of searchable types are determined based on whether the types are defined in a database metadata table as being searchable.

29. The method of claim 24, wherein the SQL query is built using one or more lookup tables that define the searchable types and values.

30. The method of claim 29, wherein the database server builds the SQL query by pulling rows out of a lookup table using JavaScript in response to receiving the user selected search parameters.