Building “Sane” Query Interfaces

OSCON 2006 workshop

Building “Sane” Query Interfaces, by Mark Newsome e-mail is newsome@acm.org

Insane query interfaces have no help, no format, no range, and no help on how many results will be returned.

Better — pull-downs with a descriptive default like “select state”. Find min and max date from db. Gives a count for how many items in the pulldown menu. UI automatically reverts to use a popup button when pulldown gets too long.

“Refine” button can be pressed when the form is filled, and it will get a count, so you can “preview” your results. When the “refine” button is pressed, it refines EVERYTHING — ie, the counts for the range for the other items. Very smart! (see the song database demo below, when you enter in a lyrics search and click refine, it repopulates the pulldown menus with only the artists and titles that have the lyrics you requested). Also, if there’s only one choice after refining a pulldown,

Also, once the sort comes back, clicking the header field will sort the data by that field.

This is a great idea, even for things that aren’t at heart a db query — for instance, an interface to a “broadcast e-mail” could instruct a user to select who to send to (whole list, one location, one interest, whatever) , and you can

Framework:

QueryParameterBean
make a query parameter “bean” with getters and setters for parameters. If your form has 20 parameters, just set the ones that the user set. Also, includes methods for determining whether a field is “empty” and if “empty” means 0 or NULL or ”.
Sample classes — getCompanyNameCount(), getCompanyName(), getOrderCount(), getOrders() — they all call the “master query method”.

QueryMethod(selectClause,groupClause,sortClause,parameterBean) — builds dynamic query based on the parameters that were set — if you’ve only set the company name count, you’ll set the select query to count(*) or whatever. Empty parameters don’t participate in the query.

Results are wrapped to free resultSet — into a ResultsWrapper.

How to populate a pull-down on the query form — set parameters from form, call the getFoo() functions, then QueryMethod to build the query, and then call a ResultsFormatter to generate an HTML pulldown (or input field/popup button combo, depending on field size (settable)).

But this is tedious — lots of coding, as opposed to a simple form.
Uses more DB resources for all those extra queries.
But, database are fast….also, you could create the forms dynamically, once a day, instead of dynamically every time the page is loaded.

Other things to do:
Validate form with Javascript to avoid wasteful roundtrips to the server
Use JS popups to assist the user (ie, date selection)
Useful to show cardinality of each item, ie, if there are 6 company names and 50,000 records, you might just want to pick a company name and then hit Refine to see if you have a manageable set. If not, you can go to another field — ie, “browsing by queries”.

Accept * wildcards, but convert to % for the db.
Convert text fields to upper or lower case for comparison (or use a case-insensitive search, which MySQL has — just don’t use BINARY).
Use a text box for “too many” values for a pulldown
Also, use a trigger on insert to update values in a lookup table (ie, # of users, when a user is added, just increment a count). Or write a function that regularly makes this table by doing a count(*), but not every time.

Paged format for results — run the query, but only select the row ids (or primary key with MySQL, since not every row has an externally visible ID). Cache the ids, and retrieve the row ids for each page. (with MySQL you could use limits, but you have to be careful about running the same query at different times — if a new row is inserted, then your limit is slightly off)

Alternatively you could use cursors for paged formats, but that way is better.

Benefits:
You can drill down large datasets without leaving the form. Improves efficiency and reduces frustration.

The db does more work, but that’s its job, and it makes the overall user time lower (ie, db load is higher, but it means the db does work instead of the user).

http://www.capitolvelo.com/newsome/music.php has a technique like this for a song database, written in php. The code for that page.

Future work — automatically generate refinable and customizable query interfaces. Goals: SQL and code-free for users, users can specify the query graphically (using an ER diagram or layout of db), and tool generates the experience.

Now here’s where MySQL could really come in handy. One of my HUGE pet peeves is I’ll create a database and then end up wanting to make a bunch of forms for it — search, edit, insert new, etc. You could write code to go through all the tables in one or more dbs (or in all dbs), do a PROCEDURE ANALYSE(), and create forms based on the info in there (ie, if ENUM is recommended, use a pull-down menu, if char or varchar is recommended, use a text box, etc).

http://www.capitolvelo.com/newsome/index.html shows his writings.

(he’s done this in Java and PHP. He uses prepared statements in Java, which eliminates worries about quotes in data. If you’re building an SQL string, you have to worry about that).

OSCON 2006 workshop

Building “Sane” Query Interfaces, by Mark Newsome e-mail is newsome@acm.org

Insane query interfaces have no help, no format, no range, and no help on how many results will be returned.

Better — pull-downs with a descriptive default like “select state”. Find min and max date from db. Gives a count for how many items in the pulldown menu. UI automatically reverts to use a popup button when pulldown gets too long.

“Refine” button can be pressed when the form is filled, and it will get a count, so you can “preview” your results. When the “refine” button is pressed, it refines EVERYTHING — ie, the counts for the range for the other items. Very smart! (see the song database demo below, when you enter in a lyrics search and click refine, it repopulates the pulldown menus with only the artists and titles that have the lyrics you requested). Also, if there’s only one choice after refining a pulldown,

Also, once the sort comes back, clicking the header field will sort the data by that field.

This is a great idea, even for things that aren’t at heart a db query — for instance, an interface to a “broadcast e-mail” could instruct a user to select who to send to (whole list, one location, one interest, whatever) , and you can

Framework:

QueryParameterBean
make a query parameter “bean” with getters and setters for parameters. If your form has 20 parameters, just set the ones that the user set. Also, includes methods for determining whether a field is “empty” and if “empty” means 0 or NULL or ”.
Sample classes — getCompanyNameCount(), getCompanyName(), getOrderCount(), getOrders() — they all call the “master query method”.

QueryMethod(selectClause,groupClause,sortClause,parameterBean) — builds dynamic query based on the parameters that were set — if you’ve only set the company name count, you’ll set the select query to count(*) or whatever. Empty parameters don’t participate in the query.

Results are wrapped to free resultSet — into a ResultsWrapper.

How to populate a pull-down on the query form — set parameters from form, call the getFoo() functions, then QueryMethod to build the query, and then call a ResultsFormatter to generate an HTML pulldown (or input field/popup button combo, depending on field size (settable)).

But this is tedious — lots of coding, as opposed to a simple form.
Uses more DB resources for all those extra queries.
But, database are fast….also, you could create the forms dynamically, once a day, instead of dynamically every time the page is loaded.

Other things to do:
Validate form with Javascript to avoid wasteful roundtrips to the server
Use JS popups to assist the user (ie, date selection)
Useful to show cardinality of each item, ie, if there are 6 company names and 50,000 records, you might just want to pick a company name and then hit Refine to see if you have a manageable set. If not, you can go to another field — ie, “browsing by queries”.

Accept * wildcards, but convert to % for the db.
Convert text fields to upper or lower case for comparison (or use a case-insensitive search, which MySQL has — just don’t use BINARY).
Use a text box for “too many” values for a pulldown
Also, use a trigger on insert to update values in a lookup table (ie, # of users, when a user is added, just increment a count). Or write a function that regularly makes this table by doing a count(*), but not every time.

Paged format for results — run the query, but only select the row ids (or primary key with MySQL, since not every row has an externally visible ID). Cache the ids, and retrieve the row ids for each page. (with MySQL you could use limits, but you have to be careful about running the same query at different times — if a new row is inserted, then your limit is slightly off)

Alternatively you could use cursors for paged formats, but that way is better.

Benefits:
You can drill down large datasets without leaving the form. Improves efficiency and reduces frustration.

The db does more work, but that’s its job, and it makes the overall user time lower (ie, db load is higher, but it means the db does work instead of the user).

http://www.capitolvelo.com/newsome/music.php has a technique like this for a song database, written in php. The code for that page.

Future work — automatically generate refinable and customizable query interfaces. Goals: SQL and code-free for users, users can specify the query graphically (using an ER diagram or layout of db), and tool generates the experience.

Now here’s where MySQL could really come in handy. One of my HUGE pet peeves is I’ll create a database and then end up wanting to make a bunch of forms for it — search, edit, insert new, etc. You could write code to go through all the tables in one or more dbs (or in all dbs), do a PROCEDURE ANALYSE(), and create forms based on the info in there (ie, if ENUM is recommended, use a pull-down menu, if char or varchar is recommended, use a text box, etc).

http://www.capitolvelo.com/newsome/index.html shows his writings.

(he’s done this in Java and PHP. He uses prepared statements in Java, which eliminates worries about quotes in data. If you’re building an SQL string, you have to worry about that).

Comments are closed.