[elephant-devel] Querying Advice

[Robert L. Read]

This requires a philosophical response.  In general, I think it will be
way easier than
you image, once you have been pointed in the right direction.  Take my
advice with 
a grain of salt.

First of all, ask yourself, what is the size of your dataset?  Can you
fit it all into memory?
If so, you have the full power of lisp at your command in dealing with
the querying.  You
will not have to write any macros to do this.  You might find the DCM
package, in the "contrib"
directory, a useful package, although it does not address querying; it
is more of a cache handling
issue.  (DCM has only been tested under SBCL, as far as I know.)

Under SBCL, when it comes to sorting you have "sort" and "stable-sort";
I think these are build in.
I'll eat a candle if you don't find them to blazingly fast (although the
predicates that you pass them
might take some time.)

I think really the only good way to answer this question in a deeper way
is to provide some
example code.  I do exactly what you are talking about in my application
(http://konsenti.com),
(although I use DCM), so I ought to be able to produce an example
program relatively quickly.
You'll have to figure out how to map the GUI into those requests
yourself, however.

I'll try to post an example by Monday.



On Sun, 2006-11-12 at 10:28 -0500, Daniel Salama wrote:

> Hi all,
> 
> Coming from a Rails/MySQL world, we are having some difficulty
> trying  
> to comprehend/model/implement querying the database using
> dynamically  
> generated criteria.
> 
> For example, in the life we are trying to leave, we present the user  
> with advanced search screen, where they can select anywhere between
> 0  
> and 20 different search fields. When the user submits the screen, we  
> dynamically construct the WHERE SQL statement based on whatever  
> fields and information the user entered.
> 
> Obviously, I could envision this being the easy part, however, any  
> pointers would really help here. Then we rely on the SQL-engine to  
> perform the proper computations in order to return to us the
> matching  
> resultset.
> 
> In addition, the user is then able to dynamically sort the results
> to  
> their heart's desire. We simply dynamically generate the ORDER BY  
> clause as well.
> 
> As a learning task, we are trying to migrate some of this  
> functionality to Elephant using BDB. After reading the HTML-ized  
> tutorial and looking at some of the tests in the package, we still  
> have a hard time understanding how to go about implementing
> something  
> like this. From previous posts, we can use a cursor to jump into a  
> subset of the data we need to get (e.g. based on some indexed or  
> secondary indexed value in the search criteria). However, after
> that,  
> I guess we would have to sequentially navigate thru the  results in  
> order to "manually" select each record based on all the other  
> possible search arguments. I suppose, in a way, this can be done  
> relatively painless by using macros (but we first would like to do  
> the manual expansion to learn how it would look like). Any ideas or  
> suggestions would certainly be appreciated here.
> 
> Then we have the issue of the sorting. I suppose it falls into a  
> similar situation: once we get the matching resultset from the  
> previous step, we would have to perform some "efficient" sort  
> algorithm on the data set dynamically based on the user's sorting  
> desire. I also suppose we could create a macro for this as well.
> 
> In a way, and after reading many posts out there, we are basically  
> wanting to bypass all the RDBMS machinery and working directly with  
> the data. After all, I guess many if not all of these RDBMS systems  
> are simply a SQL-domain language interface performing all these  
> computations and abstracting the direct access to the raw data,
> which  
> is probably even using BDB in the backend. So, I suppose we have to  
> learn to be able to roll-up our sleeves and deal with manipulating  
> the data in a more direct way.
> 
> Again, any ideas or suggestions here would be really appreciated.
> 
> Thanks,
> Daniel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20061112/f9b7cd36/attachment.html>