How does one represent the result of an SQL query as a Lisp object,
while maintaining as much information as possible? The obvious method
seems to be to have a list of rows, each containing a list of results,
but that makes it awkward to refer to column names. A list of
hash-tables (or even a list of assoc lists) would solve that
particular dilemma, but seems inefficient, since you'd be duplicating
the column details in each result row.
Some APIs, such as the Franz MySQL connect library, prefer to use an
iteration construct something like
(with-db-rows ((<column names>) query)
...)
where the body of the construct is executed for each row, with
variables created for the requested columns, but this sucks when you
want to save the result of the query for later use.
My current solution is to do something like declaring a struct (or
class) at runtime, based on the results of the query, and using that
to access the results. This has overhead on the initial call of a
query on a table, but there aren't too many tables involved. It still
seems slightly hack-ish, though.
There's probably something obvious that I'm missing. I'm fairly new to
Lisp. What do others do?
In article <····························@posting.google.com>,
········@myrealbox.com (Nepheles) wrote:
> How does one represent the result of an SQL query as a Lisp object,
> while maintaining as much information as possible? The obvious method
> seems to be to have a list of rows, each containing a list of results,
> but that makes it awkward to refer to column names. A list of
> hash-tables (or even a list of assoc lists) would solve that
> particular dilemma, but seems inefficient, since you'd be duplicating
> the column details in each result row.
>
> Some APIs, such as the Franz MySQL connect library, prefer to use an
> iteration construct something like
>
> (with-db-rows ((<column names>) query)
> ...)
>
> where the body of the construct is executed for each row, with
> variables created for the requested columns, but this sucks when you
> want to save the result of the query for later use.
>
> My current solution is to do something like declaring a struct (or
> class) at runtime, based on the results of the query, and using that
> to access the results. This has overhead on the initial call of a
> query on a table, but there aren't too many tables involved. It still
> seems slightly hack-ish, though.
>
> There's probably something obvious that I'm missing. I'm fairly new to
> Lisp. What do others do?
I use a little widget I call a DLIST. A DLIST is sort of like an ALIST or
a PLIST except that it keeps all the keys in a single list in the CAR of a
const cell, and all the values in a second list in the CDR of a cons cell,
thusly:
((key1 key2 ... keyn) value1 value2 ... valuen)
The cool part is that the key list can be shared among many DLIST
instances, so the overhead for keeping track of the keys is only a single
CONS cell per instance/row. DASSOC (the DLIST analog of ASSOC and GETF)
is just as efficient as ASSOC and GETF, but DLISTS are much more efficient
in terms of storage when many DLISTS share the same set of keys.
This basic structure also admits a number of optimization. For example,
if the key list is a compile-time constant, then you can replace the value
list with a vector, and compile-time optimize (dassoc key dlist) to (elt
(cdr dlist) (position key (car dlist))), allowing DLIST lookup to be done
in constant time when the key is known at compile-time.
N.B. I don't know if DLISTS are an original invention or not. I strongly
suspect they aren't. They're so simple that it's all but inconceivable
that they were not invented forty years ago. Still, I have never seen
them mentioned in any Lisp text I've ever read. A DLIST is, of course,
nothing more than a STRUCT implemented at the user level.
E.
From: Dmitri Ivanov
Subject: Re: Representation of SQL query results
Date:
Message-ID: <c4mjlt$1t67$2@news.aha.ru>
Hello Nepheles,
"Nepheles" <········@myrealbox.com> wrote:
N> How does one represent the result of an SQL query as a Lisp object,
N> while maintaining as much information as possible? The obvious
N> method seems to be to have a list of rows, each containing a list of
N> results, but that makes it awkward to refer to column names. A list
N> of hash-tables (or even a list of assoc lists) would solve that
N> particular dilemma, but seems inefficient, since you'd be
N> duplicating the column details in each result row.
N> | ...snip...|
In YSQL, a record is typically returned as a list or vector created
automatically. OTOH the following generic provides the user with protocol
for reading into an arbitrary pre-allocated object.
(defgeneric db-fetch-into (cursor object
&key start-column end-column current
&allow-other-keys))
--
Sincerely,
Dmitri Ivanov
lisp.ystok.ru
Nepheles <········@myrealbox.com> wrote:
+---------------
| How does one represent the result of an SQL query as a Lisp object,
| while maintaining as much information as possible? The obvious method
| seems to be to have a list of rows, each containing a list of results,
| but that makes it awkward to refer to column names.
+---------------
Call me simple-minded, perhaps, but I tend to just cons a row of column
names onto the front of the list of result row-lists, and then pass the
resulting single list around, e.g., using Eric Marsden's "pg.lisp":
> (defun simple-query (query)
"SIMPLE-QUERY -- Does PostgreSQL query using a shared,
per-HTTP-request connection.
Success ==> (values results nil) ; A list of rows+1 lists of strings.
Fail ==> (values nil error) ; A SQL-ERROR condition object."
(handler-case
(with-shared-pg-connection (conn *http-request*)
(let* ((result (pg-exec conn query)) ;Single query, no xaction.
;; Pull out just the column names from the attributes.
(cols (mapcar #'car (pg-result result :attributes))))
(values (cons cols (pg-result result :tuples)) nil)))
(error (cc)
(values nil cc))))
SIMPLE-QUERY
> (simple-query "select * from toy limit 4")
(("season" "media" "title" "upd")
("fall" "tape" "My Favorite Thanksgiving" 16)
("xmas" "book" "My Favorite Christmas" 2)
("xmas" "video" "The Grinch who Stole Christmas" 4)
("summer" "book" "Unusual 4ths of July" 17))
>
If you just want to output an HTML table, it's already in a reasonably
convenient form. Or if you need to use the column labels more than once,
just peel them off and re-use them:
> (let* ((results (simple-query "select * from toy limit 4"))
(columns (car results))
(rows (cdr results)))
(loop for row in rows do
(loop for col in columns ; repeats each time
and item in row do
(format t "~&~a:~10t~a~%" col item))
(terpri)))
season: fall
media: tape
title: My Favorite Thanksgiving
upd: 16
season: xmas
media: book
title: My Favorite Christmas
upd: 2
season: xmas
media: video
title: The Grinch who Stole Christmas
upd: 4
season: summer
media: book
title: Unusual 4ths of July
upd: 17
NIL
>
-Rob
-----
Rob Warnock <····@rpw3.org>
627 26th Avenue <URL:http://rpw3.org/>
San Mateo, CA 94403 (650)572-2607