[nrdo-list] RFC: "orderby" syntax

[Stuart Ballard]

As you may know, nrdo currently treats the "orderby" clause of a get
as a single string which is included pretty much unmodified (except
for parameter substitution) in the SQL query. For example:

orderby [my_field desc, tbl.some_other_field, SomeSQLFunction(a_field)];

It turns out that there are quite a few situations where nrdo would
like to know more information about the orderby clause than simply an
SQL string that it can't parse. For example, in some situations the
fields in the orderby clause could be indexed. Also, orderby can't be
supported at all by the Mock Object support described in my recent
blog post (http://sab39.dev.netreach.com/12/?vobId=278) if it's only
an SQL string.

I've figured out most of what the syntax should be like but there's
one situation I can't decide the best way to handle.

The basic syntax, in keeping with the normal structure of a dfn file
and consistent with "fields" and "params", would be:

orderby {my_field desc; tbl.some_other_field};

(For backward compatibility, the old syntax will still be supported
but will be translated internally so that even old code gets most of
the benefits of the new features).

The problematic situation is how to sort on an arbitrary SQL
expression like SomeSQLFunction above. The naive solution would be:

orderby {my_field desc; tbl.some_other_field; [SomeSQLFunction(a_field)]};

This works, but it makes it hard to give good error messages. If you
accidentally typed my_feild instead of my_field, it would be nice if
nrdo could spot that there's no such field and report an error.
However, as far as nrdo is concerned "my_feild" and
"SomeSQLFunction(a_field)" are both just strings (the square brackets
are just for quoting and aren't included at all in the parsed object
model), and it can't tell that one of them was supposed to be a field
and the other was SQL that it shouldn't be trying to parse. There are
a lot of places where nrdo today fails to report errors it ought to be
able to detect, and I want to be removing those, not adding new ones!

A possible alternative would be:

orderby {my_field desc; tbl.some_other_field; sql [SomeSQLFunction(a_field)]};

Here we use a new "sql" keyword to identify clauses that shouldn't be
parsed. (The keyword might also be something else, like "raw"). What
worries me about this approach is that it seems rather un-obvious that
you have to put a new keyword in there. It might be possible though to
make it a little more obvious, by giving a hint in the error message
that you get if you forget to do it.

Anyone have any feedback? How common might it be to be trying to order
by things that aren't fields? Is it acceptable to require people to
manually tag those things as "raw" or "sql"? And if so, what's the
best keyword to use?

Thanks,
Stuart.
--
http://sab39.dev.netreach.com/