Re: [Chicken-users] sql.egg: incompatible change

[Matthew Welland]

On Saturday 18 August 2007 11:02:07 am Kon Lovett wrote:
> On Aug 18, 2007, at 5:11 AM, Hans Bulfone wrote:
> > hi,
> >
> > i'm planning to make an incompatible change to the sql:select function
> > of the sql.egg.
> >
> > the signature is now:
> >
> > (sql:select what from where #!optional order-by)
> >
> > and i want to change it to:
> >
> > (sql:select what #!key from where group-by having order-by limit)
> >
> > is anyone using the sql.egg and has any objections to this change?

Just a thought: would this work?

(sql:select what from where #!optional optional-modifiers) 

Where optional-modifiers is a list of modifiers such as:

((limit 1)(order foo desc))

> I have the opposite - please switch to keyword arguments.

What would this look like? I'm concerned that nested queries would look a 
bit contrived with keyword arguments. Also, select (and most other sql 
commands) seem too regular to necesitate keywords. I think the sql egg 
approach would lose elegance with keywords.

Then again, I think if positional arguments to sql queries such as 
implemented in the sqlite3 egg were available I'd write my sql in sql and 
not attempt to write them in scheme. 

;; WARNING, WARNING, optional reading below :-)
(set! soapbox-mode #t)

Here is how I see the options:

1. Positional arguments
 ;; Know SQL fairly well? Then this is easy to read and understand (IMHO)

 (sql:exec db "SELECT id,name FROM people WHERE name=?" who)

2. sql egg
 ;; This approach requires learning a new way to write SQL but it is elegant 
 ;; and very "schemish". I have to admit it still takes me two to three 
 ;; times as long to construct even mildly complex queries in sql:select
 ;; versus directly writing the query.

  (sql:exec db (sql:select '(id name) '(foo) `(= name ,who)))

3. string-append
;; This is just plain ugly, error prone and dangerous.

  (sql:exec db 
      (string-append "SELECT id,name FROM people WHERE name='" who "'"))

By the by I suspect that option 2. doesn't protect against SQL injection 
threats to the same degree that option 1. does. Also, I believe that there 
are optimizations that can be done with postional arguments for when a 
query is called multiple times with different arguments. Can the sql egg 
approach ever take advantage of those type of optimizations?

As I've stated else where my wish would be for the MYSQL and POSTGRESQL eggs 
to support positional arguments. Even better would be a unified API for SQL 
queries in Chicken. In the mean time I'm very grateful to have the sql egg 
which relieves me of having to mess with string-append to assemble my 
queries.

Regards,

Matt
--

> Best Wishes,
> Kon
>
> > tnx&bye,
> > hans.
> >
> >
> > _______________________________________________
> > Chicken-users mailing list
> > address@hidden
> > http://lists.nongnu.org/mailman/listinfo/chicken-users
>
> _______________________________________________
> Chicken-users mailing list
> address@hidden
> http://lists.nongnu.org/mailman/listinfo/chicken-users



-- 
http://www.kiatoa.com, fight for a better world.