Patent Application
20080091733
A “data store” as used herein may include a database, a collection of databases organized as a data warehouse, a directory, a collection of directories cooperating with one another, or various combinations of the same. According to an embodiment, the data store is a Teradata® warehouse product or service distributed by NCR Corporation of Dayton, Ohio.
A “data query language” refers to an Application Programming Interface (API) used to access and perform operations on the data store. In an embodiment, the data query language is SQL. Although it is noted that any commercial data query language API or proprietary data query language API may use and benefit from the teachings presented herein with respect to the data query language service.
Within this context, the processing of the data query language service is now discussed with reference to the
According to an embodiment, the parameter variable may be readily identified in the data query language statement using a special character, such as but not limited to “@.” Additionally, a string of characters may be used, such as but not limited to “##.” For example, a modified SQL statement may appear as “SELECT @NAME FROM X.” The “@NAME” permits the variable parameter variable of “NAME” to be parsed and recognized within the SQL statement and replaced with a specific value at runtime.
The parameter variable is not case sensitive, such that “NAME” is indistinguishable from “name.” Although, if desired, the parameter variable may be case sensitive, such that “NAME” and “name” are not considered the same parameter variable. The parameter variable may also be selectable from predefined and available lists of parameter variables. Alternatively, the user may custom-define a particular parameter variable.
The parameter types for the parameter variables can include a variety of data types, such as standard data types available within the data query language itself, such as SQL data types. Additionally, some data types may be user defined or custom defined. The parameter types are selectable by a user and are associated with the parameter variables. That is, each parameter variable includes a corresponding parameter type. The type permits the data query language service and any subsequent execution service to enforce data typing on values that are subsequently supplied for parameter variables. So, a parameter type of Boolean for a parameter variable of “Yes_or_No” does not support a subsequent attempt to supply a value of “1000,” which is an integer.
Some example parameter types include:
By separating the parameter types from the actual parameter values of the parameter variables, reuse and flexibility is increased. A user can focus on their individual goals using their individual values. Typically, one person creates SQL and parameter type definitions while another person actually uses the SQL. So, using a goal-oriented architecture, the user-interface is streamlined, such that single or multiple users for creation and use of the SQL can occur.
At 120, the data query language service iterates the processing of 110 zero or more additional times for purposes of acquiring additional parameter types for additional parameter variables and perhaps additional data query language statements. So, a user may have multiple data query language statements having multiple parameter variables embedded therein. It is noted that any given parameter variable may be reused and shared within the context of all the data query language statements. So, if a user defined a parameter variable of “NAME” in one SQL statement; the same parameter variable may be referenced and used in additional SQL statements.
At 130, the parameter variable, its parameter type, and the data query language statement are used to create a rule or application object. If there were any additional parameter variables, additional parameter types, and additional data query language statements, then these are also included within the definition and creation of the rule.
Essentially, the rule is a collection of data query language statements having shared parameter variables and types (parameter definitions). In some cases, this collection is for SQL statements having the shared parameter definitions. These statements may be subsequently executed in specified order or executed conditionally based on the success or failure of certain queries.
In an embodiment, a single rule is represented using one or more SQL statements and parameter definitions are stored as local referenced objects. In cases, where a rule includes multiple data query language statements the order of execution is specified, such as through conditional branching based on success or failure of execution of a particular data query language statement (e.g., if SQL_Statement—1 succeeds run SQL_Statement—2; if SQL_Statement—1 fails, run SQL_Statement—3, etc.).
The data query language statements may include any operation supported by the API of the data query language being used. Also, parameter variables are shared amongst each of the data query language statements in the rule.
According to an embodiment, at 140, the rule may be bound to an object or plan. That is, the rule may be referenced or incorporated within another application or service, such as a report, etc.
In some cases, at 141, the data query language service may receive an instruction to execute the object, plan, application, or service that the rule is bound to, referenced in, or embedded in. At this point values for the parameter variable and any additional parameter variables are acquired by interactively prompting a user to supply the value or values at runtime or execution time.
In other cases, at 142, the data query language service may dynamically acquire the value or values for the parameter variable and any additional parameter variables from another service, application, data store table, file, or even from command line parameters supplied when executing the object, plan, application, or service to which the rule is bound to, referenced in, or embedded in.
It may also be the case, at 150 that the rule itself is an application, such that it is an independent or standalone executable entity that does not rely on another object, plan, application, or service to execute. So, at 150, the rule may be executed upon instruction to do so. Similarly, the parameter values for the parameter variables may be obtained in manners depicted in 141 and 142 and discussed above.
The GUI tool compliments and may utilize the data query language service represented by the method 100 of the
At 210, the GUI tool is provided to a user for interaction and for defining user-defined parameter variables, which are to be interspersed into one or more SQL statements. These modified SQL statements will acquire parameter values for the defined parameter variables when the executed as a rule object. The rule may be a standalone application or embedded, bound, or referenced within another application or service.
At 220, the GUI tool is used to interact with a user for purposes of defining the rule, which includes the interspersed parameter variables within the one or more SQL statements as modified SQL statements.
According to an embodiment, at 221, the GUI tool may also be used to receive from the user parameter types of the parameter variables. At 222, the parameter types may be supplied to the user via the GUI tool as one or more drop down menus for user selection. That is, a list of predefined parameter types may be supplied in a drop down menu within the GUI for the user to select.
At 230, the rule is stored for subsequent use. In some cases, at 240, the GUI tool may also be used to identify scheduling constraints with the rule. A scheduling constraint may be used to identify when and how frequently a rule is to be subsequently executed.
In an embodiment, at 250, the rule may be subsequently executed as part of another application or executed as its own standalone application. Additionally, at 251 and at runtime, parameter values for the parameter variables may be dynamically acquired and resolved from a variety of mechanisms. For instance, the runtime user may be dynamically prompted at runtime of the rule to supply the values for the parameter variables. In other cases, the parameter values may be acquired as runtime or command line parameters, acquired from a data store table, acquired from a file, acquired from another automated application or automated service.
The reusable SQL statement system 300 includes a GUI tool 301 and a rule generator service 302. The reusable SQL statement system 300 may also include a scheduling service 303 and/or an execution service 304. Each of these will now be discussed in turn.
The GUI tool 301 is used to interact with a user that is developing a rule. A rule is a collection of modified SQL statements. The user supplies one or more SQL statements and defines a variety of parameter variables to intersperse within the SQL statements. The parameter variables are also associated with parameter types or data types. The combination of a parameter variable and its parameter type is a parameter definition. A parameter definition is reusable and capable of being referenced and shared throughout the SQL statements; that is, a single parameter definition is not tied to and does not have to be redefined to be used in other SQL statements supplied by the user via interaction with the GUI tool 301.
According to an embodiment, the GUI tool 301 is segmented into a variety of visual frames for user inspection and interaction. For example, one frame may dynamically present the collection of modified SQL statements as a user defines a parameter variable and a particular SQL statement for the rule being constructed.
An example GUI tool 301 was presented and described in detail above with reference to the method 200 of the
The rule generator service 302 interacts on the backend with the GUI tool 301 for purposes of generating, creating, or assembling a rule. The rule may be bound to, referenced within, or embedded within another application, object, plan, or service. Alternatively, the rule may be a standalone and independently executable application or service. The rule is a collection of the modified SQL statements having the parameter definitions referenced therein. Example processing associated with the rule generator service 302 was presented above with reference to the method 100 of the
In some cases, the reusable SQL statement system 300 may also include a scheduling service 303. The scheduling service 303 is for receiving and processing scheduling constraints for the generated rule or for the application or service that uses the generated rule. So, the rule may be executed at specific times, dates, or even intervals.
The reusable SQL statement system 300 may also include an execution service 304. The execution service 304 is for dynamically interpreting or inspecting the rule when it is executed to dynamically acquire parameter values for the interspersed parameter variables. The execution service 304 may also be used to enforce any supplied values to ensure they conform to identified parameter types or the parameter variables.
The execution service 304 is dynamically invoked when the rule is executed or referenced within an executable application or service. At this point, the execution service 304 may acquire the parameter values for the parameter variables in a number of manners, such as via interactive and dynamic prompting of a runtime user, from a file, from a data store table, from command line parameters, and the like.
It is to be understood that the screenshot is presented for purposes of comprehension only and as one example. The embodiments of the invention are not tied to any particular presentation within the GUI tool.
The above description is illustrative, and not restrictive. Many other embodiments will be apparent to those of skill in the art upon reviewing the above description. The scope of embodiments should therefore be determined with reference to the claims, along with the full scope of equivalents to which such claims are entitled.
The Abstract is provided to comply with 37 C.F.R. §1.72(b) and will allow the reader to quickly ascertain the nature and gist of the technical disclosure. It is submitted with the understanding that it will not be used to interpret or limit the scope or meaning of the claims.
In the foregoing description of the embodiments, various features are grouped together in a single embodiment for the purpose of streamlining the disclosure. This method of disclosure is not to be interpreted as reflecting that the claimed embodiments have more features than are expressly recited in each claim. Rather, as the following claims reflect, inventive subject matter lies in less than all features of a single disclosed embodiment. Thus the following claims are hereby incorporated into the Description of the Embodiments, with each claim standing on its own as a separate exemplary embodiment.
