Techniques for query generation, population, and management

• US 8,180,789 B1
• Filed: 12/05/2005
• Issued: 05/15/2012
• Est. Priority Date: 12/05/2005
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Wasserman, Brian, Draper, Linette, Ryan, Thomas, Hood, George, Brooks, Gordon
• Primary Examiner(s): Truong, Dennis
• Application Number:
  US11/294,174
• Time in Patent Office: 2,353 Days
• Field of Search: 707/4, 707/3, 707/102, 707/104.1, 707/999.004, 707/999.102, 707/707, 707/766, 707/802, 707/999.001
• US Class Current: 707/766
• CPC Class Codes: G06F 16/242 Query formulation

Abstract

Techniques are presented for query generation, population, and management. Queries are generically defined with metadata. The metadata identifies specific queries and specific parameters associated with a given query. When a query instance is desired, parameter values are dynamically acquired and used to populate portions of the metadata associated with a desired query. The query instance may then be executed as desired.

Claims

1. A computer-implemented method stored in a non-transitory computer-readable medium and executed by a computer to perform the method, comprising:

receiving a request to build a query;

generating a query identifier for the query;

interactively acquiring metadata defining the query for purposes of dynamically and in real time generating the query, the metadata is centrally managed over a network and is stored in environments that are independent of user environments that use the metadata to establish the query, the metadata for defining the query assembled in three tables, a first table is a query table and includes a query identifier, a query name, a total number of parameters for the query, a query author identifier, a query modify date, and a use field that is a flag indicating whether the query is activated for use or deactivated so the query cannot be used, a second table is a Structured Query Language (SQL) table that includes an index on the query identifier and includes an entry for the query identifier within the second table for query SQL for executing the query, the entry in the SQL table also includes a security level for a resource that makes the request, the query SQL is parameterized, each parameter including a parameter identifier, a third table is a query parameter table indexed on the query identifier and the parameter identifier and each entry in the third table includes a parameter title, a parameter tip, a parameter order, a parameter string, and a data type code for a particular parameter identifier associated with that entry, the parameter string is a specific replaceable parameter item, and the parameter title providing descriptive information to a user and used to populate screen information and the parameter order defining an order for the replaceable parameter'"'"'s items and the parameter string representing the replaceable parameter;

generating a query column table for when the query is executed including definitions for columns that house results of the query, the columns of the query column table used for generating reports;

storing the query identifier and the metadata in the three tables for subsequent retrieval; and

using a particular field of the metadata as an indication for whether to make the query and the metadata visible or invisible to other resources.

2. The method of claim 1 further comprising, activating or deactivating the query for execution.

3. The method of claim 1 further comprising, retrieving the query from the three tables in response to receiving the query identifier.

4. The method of claim 1 further comprising:

retrieving the metadata from the three tables in response to receipt of the query identifier;

supplying values to a number of parameters included in the metadata in response to receiving the values from a graphical user interface (GUI); and

generating an instance of the query having the values.

5. A computer-implemented method stored in a non-transitory computer-readable medium and executed by a computer to perform the method, comprising:

matching a request for a query with a query identifier;

querying three tables with the query identifier;

a first table is a query table and includes the query identifier, a query name, a total number of parameters for the query, a query author identifier, a query modify date, and a use field that is a flag indicating whether the query is activated for use or deactivated so the query cannot be used, a second table is a Structured Query Language (SQL) table that includes an index on the query identifier and includes an entry for the query identifier within the second table for query SQL for executing the query, the entry in the SQL table also includes a security level for a resource that makes the request, the query SQL is parameterized, each parameter including a parameter identifier, a third table is a query parameter table indexed on the query identifier and the parameter identifier and each entry in the third table includes a parameter title, a parameter tip, a parameter order, a parameter string, and a data type code for a particular parameter identifier associated with that entry, the parameter string is a specific replaceable parameter item, and the parameter title providing descriptive information to a user and used to populate screen information and the parameter order defining an order for the replaceable parameter'"'"'s items and the parameter string representing the replaceable parameter;

receiving metadata for the query from the three tables, the metadata centrally managed over a network in environments that are independent from that which is used by users to use the query;

populating values for parameters identified in the metadata, and at least one parameter value that identifies the parameter tip that provides advice to a user on how to enter a particular parameter value and the parameter tip is displayed via a graphical user interface (GUI) to the user that supplies the values interactively via the GUI,

dynamically and in real time generating a query instance for the query that defines a new query that is to be executed against a data store when the use field within the metadata indicates that the query is activated, the use field also indicates whether the metadata if visible for the query to other resources for global administration;

and generating a query column table to house results from the query and to generate reports.

6. The method of claim 5 further comprising, executing the query instance against the data store or one or more additional data stores.

7. The method of claim 6 further comprising, presenting the results associated with executing the query instance in the graphical user interface (GUI).

8. The method of claim 5 further comprising:

comparing security attributes included in the metadata against one or more settings associated with a requestor of the query instance;

denying the requestor access to the query instance if the comparison was unfavorable; and

granting the requestor access to the query instance if the comparison was favorable.

9. The method of claim 5, wherein populating further includes matching identifiers associated with the values to parameter identifiers included within the metadata for the parameters to properly associated selective ones of the values with their corresponding parameters.

10. The method of claim 5 further comprising, associating the query instance with a resource identifier and recording the association in the three tables.

11. The method of claim 5 further comprising, associating descriptive information for the query instance and recording the association in the three tables.