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

• US 9,390,125 B2
• Filed: 03/09/2012
• Issued: 07/12/2016
• Est. Priority Date: 04/21/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Rucker, Erik Jon, Hodes, Benjamin Neiman, Leonard, Arthur Charles, Boske, Kevin J.
• Primary Examiner(s): PYO, MONICA M
• Application Number:
  US13/416,221
• Publication Number:
  US 20120166427A1
• Time in Patent Office: 1,586 Days
• Field of Search: 707/759, 707/805
• US Class Current: 1/1
• 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 a database query by modifying a view of results of the database query, the method comprising:

generating a query design surface that displays the view of the results of a database query definition, the query design surface comprising a grid displaying rows of database records from at least one database table returned by the database query and columns of fields from the at least one database table returned by the database query;

providing at least one user interface control element for modifying the columns of fields of the query design surface, the at least one user interface control element comprising a field list displaying first available fields from a first database table and second available fields from a second database table for adding one or more fields to the view of the results;

receiving input via the at least one user interface control element that adds a field to be displayed in the query design surface from the field list of one of the first available fields from the first database table or the second available fields from the second database table, wherein the received input comprises user input dragging the field to be displayed in the query design surface from the field list onto the query design surface; and

generating an updated database query definition based on receiving the input that modifies the query design surface to reflect the adding of the field from one of the first database table or the second database table to the fields displayed in query design surface.

2. The method of claim 1, further comprising:

receiving input via the at least one user interface control element for modifying an order of records within the columns of fields shown in the query design surface;

receiving input via the at least one user interface control element for modifying a new order of the records within the columns of fields; and

in response to receiving the new order of the records within the columns of fields, updating the database query definition to reflect the new order of the records within the columns of fields.

3. The method of claim 1, further comprising:

receiving input via the at least one user interface control element for sorting the rows of database records in the query design surface based on a field contained in the query design surface; and

in response to the sorting the rows of the database records in the query design surface based on the field contained in the query design surface, updating the database query definition to reflect the sorting the rows of the database records in the query design surface.

4. The method of claim 1, further comprising:

receiving input via the at least one user interface control element for grouping the rows of database records in the query design surface; and

in response to the grouping the rows of database records in the query design surface, updating the database query definition to reflect the grouping the rows of database records in of the query design surface.

5. The method of claim 4, further comprising:

receiving input via the at least one user interface control element for aggregating values of database records in the query design surface; and

in response to the aggregating the values of database records in the query design surface, updating the database query definition to reflect the aggregating the values of database records in of the query design surface.

6. The method of claim 1, further comprising:

receiving input via the at least one user interface control element for defining and executing an action query based upon the database query definition, the action query comprising one of a delete query, an update query, an append query, or a make table query.

7. The method of claim 1, further comprising:

receiving a second input via the at least one user interface control element that removes a field displayed in the query design surface, wherein the received second input comprises user input dragging a field displayed in the query design surface from the query design surface; and

generating an updated database query definition based on receiving the second input that modifies the query design surface to reflect the removal of the field from the fields displayed in the query design surface.

8. At least one of an optical disk, a magnetic storage device or a solid state storage device having computer executable instructions stored thereon which, when executed by a computer, cause the computer to:

display a query design surface that displays a view of results of a definition of a database query, the query design surface configured to display a grid displaying rows of database records from at least one database table returned by the database query and columns of fields from the at least one database table returned by the database query;

provide at least one user interface control element for allowing a user to interact with one or more fields of the query design surface, the at least one user interface control element comprising a field list displaying first available fields from a first database table and second available fields from a second database table for adding one or more fields to the view of the results;

receive input via the at least one user interface control element that adds one of the first available fields from the first database table or the second available fields from the second database table from the field list to be displayed in the query design surface, wherein the received input comprises user input dragging the one of the first available fields from the field list onto the query design surface;

modify the results of the database query shown in the query design surface by adding the one of the first available fields from one of the first database table or the second database table from the field list to the one or more fields of the query design surface based on receiving the input via that at least one user interface control element; and

generate an updated database query definition based on receiving the input that modifies the query design surface to reflect the adding of the field from one of first database table or the second database table to the one or more fields of the query design surface.

9. The optical disk, magnetic storage device or solid state storage device of claim 8, wherein each of the columns of fields correspond to a field in the at least one database table.

10. The optical disk, magnetic storage device or solid state storage device of claim 8, wherein each of the rows of database records correspond to a record within the at least one database table.

11. The optical disk, magnetic storage device or solid state storage device of claim 8, wherein the at least one user interface control element being interactive for one of sorting or filtering records within the columns of fields shown in the query design surface.

12. The optical disk, magnetic storage device or solid state storage device of claim 8, wherein the at least one user interface control element further comprises a user interface for one of grouping or aggregating records within the columns of fields shown in the query design surface.

13. The optical disk, magnetic storage device or solid state storage device of claim 8, wherein the at least one user interface control element further comprises a user interface for performing a database join operation between at least one first available field from the first database table and at least one second available field from the second database table,

wherein the database join operation comprises adding the at least one second available field from the second database table from the field list to the query design surface displaying a record from the first database table.

14. The optical disk, magnetic storage device or solid state storage device of claim 8, further causing the computer to:

receive a second input via the at least one user interface control element that removes one of the fields displayed in the query design surface, wherein the received second input comprises user input dragging the one of the fields from the query design surface; and

generate an updated database query definition based on receiving the second input that modifies the query design surface to reflect the removal of the field from the fields displayed in the query design surface.

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

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

providing at least one user interface for interacting with the query design surface to define the results of the database query displayed in the view of results, the at least one user interface comprising a field list displaying first available fields from a first database table and second available fields from a second database table for adding one or more fields to the view of results;

receiving input via the at least one user interface that adds a field to be displayed in the query design from the field list of one of the first available fields from the first database table or the second available fields from the second database, wherein the received input comprises user input dragging the field to be displayed in the query design surface from the field list onto the query design surface; and

generating an updated database query definition capable of returning the results of the database query shown in the view based on receiving the input that modifies the query design surface to reflect the adding of the field from one of the first available fields from the first database table or the second available fields from the second database table to the one or more fields shown in query design surface.

16. The method of claim 15, wherein the at least one user interface further comprises a user interface for grouping or aggregating database records based on modifying the one or more fields shown in the query design surface.

17. The method of claim 16, the method further comprising a database join operation between at least one first available field from the first database table shown in the query design surface and at least one second available field from the second database table,

wherein the database join operation comprises adding the at least one second available field from the second database table from the field list to the query design surface displaying a record from the first database table.

18. The method of claim 15, further comprising:

receiving second input via the at least one user interface that removes a field displayed in the query design surface, wherein the received second input comprises user input dragging the field displayed in the query design surface from the query design surface; and

generating an updated database query definition based on receiving the second input that modifies the query design surface to reflect the removal of the field from fields displayed in the query design surface.