Patent Application
20080162428
Embodiments of the invention generally relate to computer systems, and more particularly, to a method and system for dynamically creating fields to query a database.
Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result.
Graphical query interfaces have been developed that help a user to query a database. One problem with the existing graphical querying interfaces is that a user, who does not have detailed knowledge of the database relationships, may define a query that could return millions of records, or could define a query that returns no records. As a result, the user may spend time building a query only to find out after executing the query that the query did not return the desired data. The user must then guess at what changes to make to the query so that it returns the desired data.
Some graphical querying interfaces list all available columns in a database from which the user may select one or more columns to build a query. The user may also select a field in a column to further refine the query conditions. Building a query by selecting columns and records is a very tedious and time consuming process especially when the database being queried is very large and contains hundreds of columns and thousands of records.
Thus the result of the prior art is the generation of queries by a tedious and time consuming process that are not terribly useful because they return a dataset that is too large or too small to be useful.
Embodiments of the invention are generally directed to a method and system for dynamically creating fields to query a data base. The dynamic fields metadata for a selected condition type is retrieved. Dynamic fields are created for the selected condition type based on the retrieved metadata and the user interface (UI) elements are generated for the created dynamic fields.
These and other benefits and features of embodiments of the invention will be apparent upon consideration of the following detailed description of preferred embodiments thereof, presented in connection with the following drawings in which like reference numerals are used to identify like elements throughout.
The claims set forth the embodiments of the invention with particularity. The embodiments of the invention, together with its advantages, may be best understood from the following detailed description taken in conjunction with the accompanying drawings. The invention is illustrated by way of example and not by way of limitation in the figures of the accompanying drawings. It should be noted that references to “an” or “one” embodiment in this disclosure are not necessarily to the same embodiment, and such references mean at least one.
According to one embodiment of the invention each condition type 121 generally includes a number of fields, each field being associated with a column in the database 130. Each field of a condition type 121 is typically a semantic key. The semantic keys of a condition type 121 may include one or more static fields wherein static fields are associated with the columns that are usually common across the database 130. A user 140 typically builds a query by specifying various logical conditions between the fields of a condition type 121. The query is then used to search and retrieve desired results from the database 130. The logical conditions between the fields of the condition type 121 may be Boolean conditions such as AND, OR, NOT, XOR or XNOR. The user 140 may create and configure user created condition types based upon the search requirements and store the user created condition types in the business configuration 120 or choose from a standard set of condition types provided by an application vendor.
According to an embodiment of the invention user 140 may select a condition type 121 from a list of condition types, including user created condition types, presented to him by the GUI 102. Responsive to the user 140 selecting a condition type 121, application 101 retrieves the metadata for all the semantic keys of the selected condition type 121 from the business configuration 120 and saves the metadata in the business object 108. The metadata for each field of a condition type 121 may include a UT type, name of the field, type of field, label of field, tool tip or sync code. According to one embodiment of the invention the business object 108 may include a root node and a property valuation node wherein the root node stores the static fields and the property valuation node stores rest of the semantic keys. The semantic keys metadata may be stored in a tabular format with the metadata for each semantic key being stored as one separate record in a row of a table. View control 104 usually creates the UI elements for all the semantic keys of the selected condition type 121 from the retrieved metadata. Creation of UI elements typically includes dynamically building a context, dynamically creating a form having dynamic input fields for the retrieved semantic keys and push buttons for performing tasks and dynamically associating the push buttons with actions. A context is created for the selected condition type 121 and stored in the context storage 106. A context is created for session maintenance so that same data need not be retrieved repeatedly at the change of each user screen in GUI 102. The tasks for which push buttons are created include clearing the fields, saving a query, deleting a query, modifying a query and searching based upon a created query. The push buttons may be associated with actions such as mouse click or press of a button on a key board. The user 140 may enter values in each of the created dynamic input fields to build the query and search the database 130 and retrieve the desired results.
Each semantic key is bound to a generic query 110 in order to build a query for the selected condition type 121. The generic query 110 includes a number of empty key-value pairs. The key field of a key-value pair is bound to a semantic key of the selected condition type 121 and the value field of the key-value pair is bound to the dynamic input field for the semantic key in the GUI 102. The values entered in a dynamic input field for a semantic key are thus bound to the value field of the key-value pair bound to the semantic key. Similarly each semantic key of the selected condition type 121 and its corresponding dynamic input field is bound to a key-value pair of the generic query 110. The user 140 may then specify logical conditions between the key-value pairs to build the required query. The user 140 may also choose to ignore one or more of the key-value pairs and choose to build a query based upon only few of the semantic keys of the selected condition type 121. The built query may then be used to search the database 130 and retrieve desired results.
The particular methods associated with embodiments of the invention are described in terms of computer software and hardware with reference to a flowchart. The methods to be performed by a computing device (e.g., an application server) may constitute state machines or computer programs made up of computer-executable instructions. The computer-executable instructions may be written in a computer programming language or may be embodied in firmware logic. If written in a programming language conforming to a recognized standard, such instructions can be executed on a variety of hardware platforms and for interface to a variety of operating systems. In addition, embodiments of the invention are not described with reference to any particular programming language. It will be appreciated that a variety of programming languages may be used to implement the teachings of the invention as described herein. Furthermore, it is common in the art to speak of software, in one form or another (e.g., program, procedure, process, application, etc.), as taking an action or causing a result. Such expressions are merely a shorthand way of saying that execution of the software by a computing device causes the device to perform an action or produce a result.
Elements of the invention may also be provided as a machine-readable medium for storing the machine-executable instructions. The machine-readable medium may include, but is not limited to, flash memory, optical disks, CD-ROMs, DVD ROMs, RAMs, EPROMs, EEPROMs, magnetic or optical cards, propagation media or other type of machine-readable media suitable for storing electronic instructions. For example, the invention may be downloaded as a computer program which may be transferred from a remote computer (e.g., a server) to a requesting computer (e.g., a client) by way of data signals embodied in a carrier wave or other propagation medium via a communication link (e.g., a modem or network connection).
