Our recent work on Sutoiku Activities outlined the need for query variables. Following some internal discussions, we also realized that we should add support for query parameters as well. Both apply to the values of query filters (account.country equals "France"), but they address two different sets of requirements.

Query Parameters
Query parameters allow the value of a query filter to be set by the user when a query is executed. To define a query parameter from the query builder, simply check the “Query parameter” checkbox in a given query filter. When doing so, the placeholder for the query value goes from “Enter value” to “Enter default value”, the latter being optional.

When a query parameter is defined, a corresponding input field is displayed underneath the query name in the query selector box. When the query is selected, default filter values are automatically populated, and the user can set the values of any query parameter, then click on the “Run” button to refresh the Object View.

Query Variables
Query variables allow the value of a query filter to be dynamically set when a query is executed. To use a query variable as value of a query filter, simply add the dollar sign ($) as prefix to a variable’s name. The variable must be within the query engine’s scope in order to be properly evaluated at runtime.

Our user interface is made of two main components: an object view that displays multiple records of an object, and a record view that displays one record at a time. The object view itself is supposed to be powered by the query builder, which is responsible for defining which records of the object should be displayed, and in what fashion.

Today, François put together a very crude proof-of-concept for the object view, and it’s quite impressive. It does not look like much, but the underlying architecture is unusual. What makes it challenging is that our query builder is quite sophisticated and capable of generating very complex SQL queries from an easy-to-use wizard. As a result, we need to be able to execute such queries to lookup the records to be displayed by the object view, and while this is a trivial matter when we’re using our server in the back-end, it’s quite a different story when we just have the user interface running in standalone mode (without any server).

Well, as it turns out, the framework we’re using for our user interface is capable of processing SQL queries locally, with almost everything we need at the exception of summaries for hierarchical grouping. So, we decided over lunch to take advantage of this feature, and shortly thereafter we had our proof-of-concept up and running. A ton of work remains, but we’re getting dangerously close to having a real object view.

More screenshots coming soon…

The project for our first customer consists in developing a SQL query builder using a specific user interface technology (the same used for our own UI). We gave a second demo today, and they really liked it. Our latest version includes the following:

• Support for multiple tables (as data sources)
• Column selection and ordering
• Logical filtering (unlimited number of filters and boolean expressions)
• Joins (unlimited number of join levels)
• Hierarchical sorting (unlimited number of sorting levels)
• Hierarchical grouping (unlimited number of grouping levels)
• Statistical summaries (unlimited number of summaries)

The user interface is fully graphical, built using a combination of tabs and previous/next buttons so that you can navigate through it quickly and easily. Of all the user interfaces I have seen for query building, it’s by far the cleanest and simplest. And it’s also one of the most powerful. Down the road, we might also add the following:

• Hierarchical joins (to make it easier to build deep joins)
• Query parameters (set by users when queries are executed)
• Query variables (dynamically looked-up from online services)

This project is quite interesting, but what gets us really excited is that we preserved the rights to embed the entire codebase into our own product as well, which means that advanced reporting will be built right into it, including charting and pivot tables (yes, real pivot tables!).

From a release standpoint, we’re planning to have it integrated into our product immediately after Release 0.2, which means sometime in late July if everything goes as planned.