Re: Ada DB bindings and APQ

["Warren W. Gay VE3WWG" <ve3wwg@NoSpam.cogeco.ca>]

Dmitry A. Kazakov wrote:

> On Wed, 15 Dec 2004 21:53:11 -0500, Warren W. Gay VE3WWG wrote:
>>Dmitry A. Kazakov wrote:
>>>On Tue, 14 Dec 2004 23:05:17 -0500, Warren W. Gay VE3WWG wrote:
>>>>Dmitry A. Kazakov wrote:
>>>>>Connection could be just a handle to some dynamic-scope connection object
>>>>>with reference counting. This will solve both the problem of above and
>>>>>still allow cloning connections.
>>>>
>>>>I didn't like the use of handles for database objects. I purposely
>>>>stuck to using controlled objects, so that they can go out of
>>>>scope gracefully, be finalized (perhaps committed or rolled back)
>>>>and then destructed. Handles, like open Ada File_Types, never
>>>>get that maintenance.
>>>
>>>Let's make them derived from Ada.Finalization.Controlled. 
>>
>>What's the point of that? Why not use the actual object? It
>>seems like unnecessary indirection and complexity - what
>>problem are you trying to solve with handles?
> 
> Dynamic scopes. If user creates these objects on the stack then [s]he is
> responsible for doing it in a proper order. The only way Ada might help
> here is to use access discriminants, then Ada accessibility rules will do
> the work.

Well, I think it is normal to do a lot of things in the "proper
order" ;-)

If at the main program level, you connect to the database with
the Connection_Type (which is the normal practice), and then
create Query_Type object(s) upon demand within the deeper reaches
of your application, there is plenty of natural "separation".
Both object types are already derived from Ada.Finalization.Controlled,
BTW.

>>>if Root_Connection_Type were limited controlled, derived from
>>>Object.Entity, then I could create handles just by instantiating
>>>Object.Handle. Root_Query_Type can hold a handle to its connection. This
>>>will ensure that the connection will not go away until at least one query
>>>is alive.
>>
>>But why? The objects themselves solve the problem already.
> 
> How? Either you have one more level of idirection, then you could remove
> it.

OK, reading this again, I see you are focused on the Connection_Type
going away prematurely. Yes, there is a small risk in the present design.
It doesn't happen to me, but I suppose others could get tripped up
on this issue.

Yes, you _could_ introduce smart pointers to solve that issue, and
someone is welcome to make that change if they feel so inclined.

>>Here is food for thought ;-) -- if Sybase and other databases implement
>>their interface with 2-3 different classes of objects, why is the
>>APQ design so wrong to do something similar?
> 
> Sybase interface is a low-level one. If there were an interface to some
> RISC processor, then probably it would have objects for registers, cache
> levels etc. It would be very exciting, but annoying for an Ada programmer.

I don't see how a RISC processor interface applies to the discussion
at hand. The database client "interface" from an object perspective
boils down to about 4 generalized objects:

  1. Environment (userid, passwords, ports, address, hostname etc.)
  2. The connection (methods like connect, disconnect, am I connected)
  3. The query
  4. Blobs

If you look at all of these components, they all have their own
pieces of data, states and methods. In OO design you try to
subdivide the problem in sensible ways.

You actually make the API more difficult, if you roll everything
into one object (I was tempted to call it a blob). Because if I
am defining an application, then I'd probably only define one
environment object for each database used (unless I was logging
in with multiple userids). Normally, I'd only use one database
connection, but if I were to use more (maybe for performance
in a server), then they would all share the same environment
(the parameters are in common). If instead each connection (as
I have it now) holds the environment, they I have to do extra
fussing to make sure that I clone all of that environment for
the new connection. The more I think about this, the more I
wished I had made an outer Environment_Type object, separate
from Connection_Type.

Now with Brian's URI idea, it might be possible to come up with
a common Environment_Type for all databases ever supported by
APQ. By doing this, you avoid having to reinvent, recode all
that support for every database introduced into APQ. Instead,
the Sybase, MySQL and PostgreSQL Connect calls can include
both the standard Environment_Type and Connection_Type objects
(ignoring smart pointer handles as you have suggested).

Anyway, I don't have the energy to convince you that OO
design usually subdivides problems into more than one object.
Yes, you can subdivide too much, or as you seem to be proposing,
you can subdivide too little. I prefer a more balanced
approach, and if you still disagree, then let's let it
rest there ;-)

>> I really can't
>>see why all the fuss over one object vs two from the user's
>>perspective. Not everything is a hammer (read File_Type),
>>and there is room for multi-object designs in modelling
>>interfaces.
> 
> There should be a purpose in separating. So far I see none.

There is:
  - data
  - states
  - purposes
  - actions

are all "different". This is why they began as separate
objects. As I pointed out before, I regret not having
separated the environment from the connection, because
they both serve different purposes and state also.

>>>Root_Connection_Type <- MySQL.Connection_Type
>>>     |                                      |
>>>Root_Query_Type <- MySQL.Query_Type
>>>
>>>Ada is unable to provide any consistency here. You can mistakenly use
>>>PostgreSQL.Query_Type with MySQL.Connection_Type. 
>>
>>You can compile it, but you'll get an exception when
>>you try to use Execute with a mismatched paramter. Don't
>>forget that the connection gets "converted" to the
>>MySQL.Connection_Type before it can be used, and of
>>course this will fail if it is a PostgreSQL.Connection_Type.
>>
>>So "Ada does provide consistency here", but not at
>>compile time.
> 
> It is your implementation which does checks, not Ada. 

Partially true, but not completely.  In Execute(), where
you must supply both a Query_Type and Connection_Type,
code like this will immediately raise an exception if
the connection provided is not the right derived type:

    if not Is_Connected(Connection_Type(Connection)) then
       Raise_Exception(Not_Connected'Identity,
       ...

The exception gets raised when I do the conversion:

   Connection_Type(Connection)

which is present in the if statement. Ada does that, not
I. I would otherwise be glad to take the credit ;-)

The Connection_Type required in the MySQL Execute code,
will insist at that point that the object be precisely:

   APQ.MySQL.Client.Connection_Type

 > Then the result is
> that you have replaced one exception with another. The ultimate design
> goal, in my view, should be that only exception related to failed DB
> operations might propagate. We should separate soup and flies.

I understand the point you are making, and it is good one,
but go back and understand the code. The type mismatched _IS_
satisfied by an Ada check here.

>>>You can finalize
>>>connection before a query that depends on it etc. 
>>
>>That is a risk, but its generally pretty obvious at
>>runtime when it happens! But if you structure your
>>code normally, the Connection_Type is declared
>>at the outermost scope and again, this tends not
>>to be a problem.
> 
> That reminds me argumentation of C proponents. (:-))

A very low thing to say my friend 8-0

>>>>Doing table names in a portable way is problematic. Some databases
>>>>are not case sensitive, but they are case sensitive when they come
>>>>to TABLE names. Others can be configured to force lowercase table
>>>>names (MySQL IIRC), still others are always case sensitive (Sybase).
>>>>
>>>>This is why "case policy" was introduced into APQ 2.2. This is also
>>>>why it is necessary to distinguish between certain SQL building
>>>>APIs and other others like Append_Quoted.
>>>>
>>>>Believe me, if there was a unified way to do it all, I would have
>>>>welcomed it. But the reality is a bit more complicated than that ;-)
>>>
>>>The method that places a value into a query should simply dispatch on the
>>>query type. The implementation is then DB specific and will encode the
>>>value as appropriate for the DB.
>>
>>Heh heh, it all sounds very easy ;-)
> 
> It is easy. MySQL implementation does know how to properly encode values.
> But if you think that it is not easy, then it is even more so unfair to
> push that to poor users! (:-))

I think you missed the point: Its not hard to make the mechanism work,
but it is harder to please everyone, and in all of its variations. One of the
things about libraries and bindings, is that they should not be dictating
policy. They should simply be there to let the users accomplish their
goals, but they shouldn't dictate the choices made (on the database side
for example). The user of APQ may not have a choice in data types being
used, since the database may prexist, be owned by someone else, etc. etc.

Satisfying everyone is the hard part of a binding ;-)
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg