Re: [boost] SQL client library ?

Subject: Re: [boost] SQL client library ?
From: Allan Wind (allan_wind_at_[hidden])
Date: 2009-09-16 17:50:57

On 2009-09-16T20:12:13, Jean-Louis Leroy wrote:
> Like this :
> 	
> 	select((t.id)).from(t).where(t.name.like("pattern"))
> 	
> 	select((t1.id, t2.id)).from((t1, t2, p))
> 	.where((t1.income + t2.income).between(20, 40)
> 		&& p.husband == t1.id && p.wife == t2.id)

I recently played with most of the perl modules to generate SQL, 
and none really worked well for some semi-fancy reporting 
queries.

On one hand I wanted a data structure so I could build a query 
dynamically: add column X to the select and group by expression, 
then do it for Y.  Here is a collection of conditions for the 
where clause which should be 'AND'ed together etc.

However if you build your entire query you are forced into using
this new API even if most of features add no value, or when it 
adds overhead to something you can easily just type out:

"select count(*) from tbl"
"select (select max(value) from tbl1 where tbl1.id = tbl2.id) from tbl2 where ... group by tbl2.name"

It would be great if you could start with a string of SQL, have 
the library parse it and provide features to refine expressions.  
Let the library optionally bind and escape values.  Provide 
control over how the output should be normalized (case of 
keywords, sub query indent, field separators etc), and allow the 
SQL to be dumped.  mysql, for instance, caches the exact string 
of the query.

The SQL generation library should be separate from whatever talks 
to the database.  You may not need to talk to the database, or 
there might be lots of magic with database handlers (load 
balancing, pooling, keep-alive etc).

I hope it helps.

/Allan
-- 
Allan Wind
Life Integrity, LLC
<http://lifeintegrity.com>