Query authoring by modifying a view of the results of the query

• US 8,195,641 B2
• Filed: 04/21/2008
• Issued: 06/05/2012
• Est. Priority Date: 04/21/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Rucker, Erik Jon, Hodes, Benjamin Neiman, Leonard, Arthur Charles, Boske, Kevin J
• Primary Examiner(s): PYO, MONICA M
• Application Number:
  US12/106,349
• Publication Number:
  US 20090265370A1
• Time in Patent Office: 1,506 Days
• Field of Search: 707/796, 707/798, 707/803, 707/805, 707999003-999005, 707/713, 707/759
• US Class Current: 707/713
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema

Abstract

Technologies and concepts are described herein for authoring a database query through the modification of a view of the data to be returned by the query. A user interface is provided for authoring a database query through the modification of a view of the data to be returned by the query that includes a view showing the results of a database query. A user interface is also provided for modifying the view to define the results of the query. As the view is modified, a query definition is generated which, when executed against the database, will return the results of the database query shown in the view. When the desired results are shown in the view, the query definition can be saved and run against the database to perform the database query.

Claims

1. A method for authoring an updated database query by a user modifying a view of the results of a database query, the method comprising:

generating the view of the results of the database query;

providing a user interface for the user to modify the view of the results of the database query to define results of the updated database query, wherein modifying the view of the results of the database query comprises

modifying an order of one or more fields shown in the view by dragging the one or more fields to indicate the order,

adding a field from a table in the database to the view by dragging the field from the table to the view,

filtering the view by indicating at least one field for filtering a plurality of records of the view,

and further comprises adding one or more fields in a second table to the view;

in response to modifying the view of the results of the database query and adding one or more fields in the second table to the view, generating a query definition based on a modification of the view of the results, wherein the query definition reflects a database join operation for adding the one or more fields in the second table;

and

running the generated query definition on a database to perform the updated database query.

2. The method of claim 1, further comprising:

providing a user interface for removing a field from the view; and

in response to the removal of a field from the view, updating the query definition to reflect the removal of the field.

3. The method of claim 1, further comprising:

providing a user interface for sorting the view based on a field contained in the view; and

in response to the sorting the view based on a field contained in the view, updating the query definition to reflect the sorting of the view.

4. The method of claim 1, further comprising:

providing a user interface for grouping the view; and

in response to grouping the view, updating the query definition to reflect the grouping of the view.

5. The method of claim 1, further comprising:

providing a user interface for aggregating the view; and

in response to aggregating the view, updating the query definition to reflect the aggregating of the view.

6. The method of claim 1, further comprising providing a user interface for defining and executing an action query based upon the query definition, the action query comprising one of a delete query, an update query, an append query, or a make table query.

7. A computer storage medium that is not a signal having computer executable instructions stored thereon which, when executed by a computer, cause the computer to:

display a query design surface comprising one or more columns, each of the columns corresponding to a field in a table of the database, the query design surface further comprising one or more rows each of the rows corresponding to a record within the table of the database, the query design surface configured to display a view of the results of a database query;

provide a user interface for allowing a user to interact with the query design surface to modify the view showing the results of the database query, wherein modifying the view of the results of the database query comprises

modifying an order of one or more fields shown in the view by dragging the one or more fields to indicate the order,

adding a field from the table in the database to the view by dragging the field from the table to the view,

filtering the view by indicating at least one field for filtering a plurality of records of the view and

further comprises adding one or more fields in a second table to the view, wherein the second table is different from the table; and

in response to modifying the view of the results of the database query and adding one or more fields in the second table to the view, generating a query definition based on a modification of the view of the results, wherein the query definition reflects a database join operation for adding the one or more fields in the second table and is capable of returning the results defined in the modified view when an updated database query is utilized to query a database.

8. The computer storage medium of claim 7, wherein the user interface comprises a field list displaying the one or more fields in the table in the database, and wherein the fields displayed in the field list may be added to the query design surface.

9. The computer storage medium of claim 8, wherein the user interface further comprises a user interface for sorting or filtering one or more fields shown in the query design surface.

10. The computer storage medium of claim 9, wherein the user interface further comprises a user interface for grouping or aggregating the one or more fields shown in the query design surface.

11. The computer storage medium of claim 10, wherein the user interface further comprises a user interface for performing a database join operation between the one or more fields shown in the query design surface and a field from a second database table.

12. A method for authoring a database query by modifying a view of the results of the database query, the method comprising:

displaying a query design surface, the query design surface configured to display a view of the results of the database query and comprising one or more columns and one or more rows, each of the columns corresponding to a field in a table of the database and each of the rows corresponding to a record within the table of the database;

providing a user interface for interacting with the query design surface to modify the view of the results of the database query to define an updated query definition providing the results shown in the modified view, the user interface comprising a field list for adding one or more fields from the table to the view, a control for sorting the view based on one or more fields shown in the view, and a control for filtering the view,

wherein the user interface further allows performing a database join operation between the one or more fields shown in the query design surface and a field from a second database table, wherein the second database table is different from the table of the database;

generating the updated query definition capable of returning the results of the database query shown in the modified view; and

running the updated query definition on the database.

13. The method of claim 12, wherein the user interface further comprises a user interface for grouping or aggregating the one or more fields shown in the query design surface.