pattern matching - How can a big table be treated as a database?

I often work with big tables that I want to treat kind of like a database. Here's an example table.

theTable = {
 {"id","color","size","flavor"},
 {1,"blue",5,"cherry"},
 {2,"green",5,"piquant"},
 {3,"blue",20,"peppermint"}
}

In a database I would ask

SELECT * FROM `theTable` WHERE `color` = 'blue' AND `size` > 10

and get effectively

{{3, "blue", 20, "peppermint"}}

in response. In Mathematica, I need to determine the "column number" for color and size, then use Cases with And to do the same thing.

Cases[theTable[[2 ;;]], a_ /; And[a[[2]] == "blue", a[[3]] > 10]]

This is operationally much clumsier than the database way. Unfortunately setting up a database for every such table is too much extra work, particularly since I want the data to end up in mma anyway.

How can this approach be improved? Specifically, how can I more easily use the column names directly, instead of their part numbers? And how can I avoid the ugly a_/;f[a] pattern?

Answer

A way of getting around the a_/;test[a] syntax is to write out the tests in string form, and use replace to insert the values. For this to work you need to build rules from your table. Here is a simple implementation:

 SetAttributes[queryCriteria, HoldAll]
 queryCriteria[theTable_, query_] := Function[{entry}, 
 Unevaluated[query] /. (Rule @@@ Transpose[{theTable[[1]], entry}]), HoldAll]

 Select[theTable, queryCriteria[theTable, "color" == "blue" && "size" > 10]]

Personally I would prefer not having to give theTable as an argument to the query function constructor, since conceptually you shouldn't need a table to define a query, however it's needed during the construction because you have the field names listed in the first row. A way to nicely work around this is to consider a query an indpependent entitiy, which doesn't use the table until it's used in Select. This can be defined by setting an Upvalue pattern for Select, to resemble your included example, I use where as a name for the query:

 SetAttributes[where, HoldAll]
 Select[table_, where[query_]] ^:= Select[table, queryCriteria[table, query]]

So that the query can be written:

 Select[theTable, where["color" == "blue" && "size" > 10]]

This is all just ways of doing a similar thing with different syntax however. I would expect that performance issues become more important with big Databases.