System and method of providing ad hoc query capabilities to complex database systems

• US 20060173813A1
• Filed: 01/04/2006
• Published: 08/03/2006
• Est. Priority Date: 01/04/2005
• Status: Abandoned Application

• Current Assignee: San Antonio Independent School District
• Original Assignee: San Antonio Independent School District
• Inventors: Zorola, Marcos
• Application Number:
  US11/325,183
• Publication Number:
  US 20060173813A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

A system and method for providing ad hoc query capabilities to complex homogenous and heterogeneous database systems. The method utilizes a universal join table adapted to join with any table within a database system or across multiple database systems. Using the universal join table, a logical flat hierarchy is laid on top of the database system to simplify complex searches across one or more database systems.

Claims

1. A method for providing ad hoc query capabilities to a database system, the database system comprised of a plurality of data tables to store application data, a binding table to function as a common joining table for said plurality of data tables, a relations table to store information required to join said binding table with each of said plurality of data tables, and a grouping table to store data to limit the result set of a search and to provide logical grouping information for the fields of said plurality of data tables, said grouping table comprising at least a group identifier, a joining key, and a group field, the method comprising the steps of:

configuring said binding, relations, and grouping tables;

allowing a user to select one search type from a plurality of search types;

displaying the group names stored in said grouping table to the user;

allowing the user to optionally select a group name on which to limit the search results, said group name selected from the grouping table;

displaying the field names in said plurality of data tables which are available for searching;

allowing the user to select the fields to be returned as a result set and assign zero or more conditionals to each selected field to filter the result set;

inserting each selected field and said zero or more qualifiers into a selected fields list;

inserting said binding table as the first element in a table list;

distinctly inserting each table associated with each field in said selected fields list into said table list, wherein the table associated with the first selected field is the base table;

assigning a unique qualifier to each table said table list;

iterating over said selected fields list to generate the SELECT and WHERE clauses for a database query, wherein said WHERE clause is generated only if one or more conditionals have been assigned to a selected field;

iterating over said table list starting from the base table, for each table in said table list, querying said relations table for the method of joining the tables in said table list to said binding table and generating the joining code for said database query;

if the user has selected to group the search results, generating code to join said grouping table with said binding table and the grouping code for said database query in accordance with the user'"'"'s selected grouping choice;

executing said generated database query code against said plurality of database tables; and

displaying the result set to the user.

2. The method of claim 1 wherein the search type is an aggregation of the searched data.

3. The method of claim 1 wherein the search type is an listing of the searched data.

4. The method of claim 1 wherein the step of generating said SELECT and WHERE clauses occurs after the steps of generating said joining code.

5. A method for providing ad hoc query capabilities to a database system, the database system comprised of a plurality of data tables to store application data, a binding table to function as a common joining table for said plurality of data tables, a relations table to store information required to join said binding table with each of said plurality of data tables, and a grouping table to store data to limit the result set of a search and to provide logical grouping information for the fields of said plurality of data tables, said grouping table comprising at least a group identifier, a joining key, and a group field, the method comprising the steps of:

configuring said binding, relations, and grouping tables;

allowing a user to select one search type from a plurality of search types;

defining search criteria, said search criteria comprising tables and fields to be searched, optional conditionals to filter search results, and optional groupings to further limit search results;

building lists of fields and tables to be searched;

generating a database query to produce a result set comprising all data from the tables in said table list that match said search criteria;

executing the generated query code against said plurality of database tables; and

displaying the result set to the user.

6. The method of claim 5 wherein the search type is an aggregation of the searched data.

7. The method of claim 5 wherein the search type is an listing of the searched data.

8. The method of claim 5 wherein the step of defining search criteria across disparate tables comprises the steps of:

displaying the group names stored in said grouping table to the user;

allowing the user to optionally select a group name on which to limit the search results, said group name selected from the grouping table;

displaying the field names in said plurality of data tables which are available for searching; and

allowing the user to select the fields to be returned as a result set and assign zero or more conditionals to each selected field to filter the result set.

9. The method of claim 8 wherein the step of building lists of fields and tables to be searched comprises the steps of:

inserting each selected field and said zero or more qualifiers into a selected fields list;

inserting said binding table as the first element in a table list;

distinctly inserting each table associated with each field in said selected fields list into said table list, wherein the table associated with the first selected field is the base table; and

assigning a unique qualifier to each table said table list.

10. The method of claim 9 wherein the step of generating a database query comprises the steps of:

iterating over said selected fields list to generate the SELECT and WHERE clauses for a database query, wherein said WHERE clause is generated only if one or more conditionals have been assigned to a selected field;

iterating over said table list starting from the base table, for each table in said table list, querying said relations table for the method of joining the tables in said table list to said binding table and generating the joining code for said database query; and

if the user has selected to group the search results, generating code to join said grouping table with said binding table and the grouping code for said database query in accordance with the user'"'"'s selected grouping choice.

11. The method of claim 10 wherein the step of generating said SELECT and WHERE clauses occurs after the steps of generating said joining code.

12. A system for providing ad hoc query capabilities to a homogenous or heterogeneous group of database tables, the system comprising:

a plurality of data tables wherein each of the plurality of data tables comprises fields representing the data of the system;

a binding table configured to function as a common joining mechanism for each of the plurality of data tables;

a relations table to store information required to join the binding table to each of the plurality of data tables;

a grouping table comprising data to limit the result set of a search and to provide logical grouping information for the fields of the plurality of data tables to a user wherein the grouping table stores at least a group identifier, a joining key, and a group field;

a means for interacting with a user to select one or more fields from one or more of the plurality of data tables to be queried and define zero or more conditional statements to filter the search result set from the queried tables;

a table list comprising the binding table and one or more of the plurality of data tables; and

a means for displaying search results to the user.

13. The system of claim 12 where in the means for interacting with the user is a computer terminal in electronic communication with a local area computer network.

14. The system of claim 12 where in the means for interacting with the user is a computer terminal in electronic communication with a wide area computer network.

15. The system of claim 12 where in the means for interacting with the user is a computer terminal in electronic communication with a computer network via the internet.

16. The system of claim 12 where in the means for interacting with the user is a personal digital assistant in electronic communication with a computer network.

17. The system of claim 12 where in the means for displaying search results to the user is a computer terminal display.

18. The system of claim 12 where in the means for displaying search results to the user is a hard copy printer.