[PLUG] lisp pseudo-query engine?

wes plug at the-wes.com
Fri Jul 3 21:02:25 UTC 2009


Here is a response from my lispy friend named Scott. I can forward any
further inquiries to him as well. No promises on if he'll respond further.

-wes

> Here's a weird one, but I'm not afraid to ask:
>
> I'm building an application that queries a database for reports,
> and then lets the user manipulate the data (filter, sort,
> diff with older copies of the same report [AKA "revisions"]).
> The revisions feature has made it necessary to run the SELECT
> only once, and manipulate the data offline (i.e. not re-query
> the same data).  This means I need to either save the initial
> result set in a temp table, or re-implement WHERE and ORDER BY
> in a lispy way that can act on a list of CLOS entity objects.
>
> Now the question: does anyone know of an already existing library
> that translates SELECT statements into lisp FIND and SORT calls?
> Or do I need to roll my own?

So you're looking for LINQ in lisp?  =)

As people continue to ask for this sort of thing and point to
Microsoft's and Mono's implementations, it will be added to cl-sql and
friends, but I don't see it out there yet.

Do you really need full general query support for this locally stored
data?  SQL parsing and all that?  If you only need a handful of queries,
writing them as little functions that invoke FIND and SORT would work.

If you really want to start a big project, you could:

* Add another database backend to cl-sql that queries over in-memory
result sets.

* Get http://sourceforge.net/projects/dotlisp/ (Lisp on the Mono CLR) to
interface with the LINQ interfaces (they're C# only in Mono at the
moment, I think).




> Or am I a fool for not going the temp table route?

This sounds better.  You get to keep all those nice DB properties, like
not having everything disappear if there's a power interruption.  If you
want to pull the data off the master DB server and not trouble it again
with repeated queries while users tweak their reports and not use disk
space on the master to store all the users' temp tables, you can keep
the temp tables on one or more replication slaves.

Depending upon the size of these reports, the bandwidth to your clients,
and the application platform, you could even pull the data to the
client's machine and dump it into a local SQLite for really fast updates
while they play with it.




An even better option, if it doesn't involve too much redesign of a
system already in production, would be to have the original data source
be immutable.  Add an insert_date column.  Perform UPDATEs by inserting
another row with the same primary key but a newer insert_date.  Query
the 'live' data by looking for MAX(insert_date).  Then, reports data
wouldn't need to be copied into temp tables.  You could just note the
date that the report was created, and query the original data looking
for MAX(insert_date) AND insert_date < report_date.

Again, control load with replication servers.  You can even
garbage-collect the master server, removing all records except the
MAX(insert_date) rows, to keep the size of the database from increasing
very much.



More information about the PLUG mailing list