[Rets-dev] RETS2 Workgroup Meeting Tuesday 3/14 2PM EST - RQL Query Language

[Dave Dribin]

On Mar 14, 2006, at 4:59 PM, Dave Scott wrote:
> In any case, your response misses my main point:  If the RETS query  
> language is going to move in the direction of SQL,  the move should  
> be made in a way that allows us to take full advantage of existing  
> SQL-based technology (not just the databases themselves, but  
> libraries, utilities, etc.)  I understand the need to subset SQL,  
> but extending SQL should be avoided if possible.  In the case of  
> the "ALL IN" operator, it is possible; there are SQL equivalents.

I agree extending SQL should be avoided if possible, and I almost  
didn't include "ALL IN" for exactly this reason.  But I wanted RQL to  
be a 1:1 translation from DMQL, and I couldn't think of the SQL  
equivalent of a lookup-and.  If there's a ANSI SQL syntax for this  
kind of query, then yeah, we should be using that instead of "ALL  
IN".  Lemme know what it is, and I'll update the BNF.

But as far as I can tell, SQL doesn't have the concept of lookups,  
and in particular lookup-multi.  lookup-and is really only meaningful  
for a lookup-multi field.  Take the (EXT=+WTRFRONT,DOCK) example.  In  
English this means I'm looking for a property that has exterior  
features of waterfront and dock.  For this to make any sense, EXT has  
to be a lookup-multi, since the field essentially has more than one  
value.  In SQL, a column can only have 1 value, so there's not direct  
SQL translation.

One other solution is to not support lookup-and queries at all.  I  
don't know how widely this kind of query is used by clients, but if  
it's not very common, we could just get rid of it.  Handling lookup- 
and on the server side is a bit of a pain, anyway.  If lookup-multi's  
are implemented as a VARCHAR, then some fancy pattern matching is  
needed.  If they are implemented in separate tables, then some fancy  
subquery/join is needed.  Either way, it's a bit hairy.

-Dave