From: Warren Young
Date: November 20 2008 5:08pm
Subject: Re: SSQLs - doing a SQL where clause query without specifying the
 columns
List-Archive: http://lists.mysql.com/plusplus/8184
Message-Id: <49259981.1030402@etr-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jay Rajput wrote:
> 
> What I have in my mind is something like this.
> 
> stock item;
> item.id = "5";
> item.getById();

We've discussed such a thing on the list before.  The main difficulties 
are in making it truly useful and safe.

The function doesn't belong on the SSQLS itself.  The right way to do it 
is to parallel the existing Query::insert(), replace() and update() 
mechanisms.  So, what you're asking for is Query::select().

The WHERE clause for the SELECT is easy to build, conceptually.  It 
should use the COMPCOUNT columns.  I don't see that there is an easy way 
to get this list from an SSQLS with the current design, but we do have 
SSQLS v2 in the pipeline.

The resulting syntax is very clean, something like this:

	// SELECT FROM stock WHERE id = 5
	stock result = query.select(stock(5));

Obviously, the assumption here is that there is only one record that 
matches the passed exemplar SSQLS.  That should be a valid assumption, 
due to the other ways the COMPCOUNT field set is used.

SSQLS v2 should also support getting multiple rows:

	// SELECT FROM stock WHERE sdate = '2008-02-18'
	stock where;
	where.setSdate(Date("2008-02-18"));
	vector<stock> result = query.select_some(where);

That is, if you use accessor methods to set the fields, it knows which 
are set and which are still uninitialized, so it can use only the 
initialized ones.

Query::delete() should also exist, but because it's so dangerous, making 
it Do the Right Thing is critical.  For instance, we can't have it 
creating code that amounts to "DELETE FROM mytable", due to a degenerate 
WHERE clause that matches everything.

> I do know that there is nothing like cols()

Again, something we can add for SSQLS v2.

> I was thinking if there is
> some function like that which can give me the column names for the table.

See examples/fieldinf.cpp.

> And then I should be expecting all the members in the item structure to be
> populated with the values. 

That's taken care of as of MySQL++ v3.0.  You can store any Row into an 
SSQLS, and it will do a best-fit effort on it.  Fields that exist in the 
Row but not in the SSQLS get lost, and fields in the SSQLS with no 
corresponding columns in the Row are left uninitialized.  In MySQL++ 
v2.x and previous, the SSQLS definition and the Row had to overlap more 
or less exactly, at least up to the number of SSQLS fields.