Query template definition and transformation

摘要

A database query template and metadata mapping provide an end-user to generate customized database reports via a user interface. The parameters and data available to the end-user can be modified by editing the database query template and metadata mapping without modifications of the underlying system. A database query template includes query language keywords, variables, and template-tokens and defines the metrics, dimensions, data comparisons, filters, and aggregations available to an end-user. Template tokens include restriction tokens for comparing and filtering data, options-section-tokens for defining and omitting sections of the query template, and descriptor tokens for identifying dimensions and metrics of the database. The database query template is transformed into a database query based on values input by a user. The metadata mapping associates the variables of the database query template to the database data model and can include data constraints for validation of user input.

主权项

1. A computer implemented method of generating a database query based on a query template comprising a plurality of query language keywords, a plurality of variables, and a plurality of template-tokens, the method comprising:
receiving at a computing device one or more values each associated with a respective one of the plurality of variables; and transforming the query template into the database query based on the one or more values, the plurality of variables, and the plurality of template-tokens, wherein the template-tokens include an optional-section-token defining a query-template-section and associated with one or more of the plurality of variables, and wherein transforming the template query into the database query comprises: processing each of said plurality of template-tokens, said processing including determining whether the template-token being processed is an optional-section token; in response to a determination that the template-token being processed is not an optional-section-token, replacing the token with a portion of a database query; in response to a determination that the template-token being processed is an optional-section token, determining whether all of said plurality of variables associated with the query-template section are associated with a null value; replacing the token with a portion of a database query based on a value of one or more variables from among said plurality of variables associated with the query-template section in response to determining that not all of said plurality of variables associated with the query-template section are associated with a null value; and omitting the query-template-section from the database query in response to determining that all of said plurality of variables associated with the query-template section are associated with a null value.