Re: Ada DB bindings and APQ

["Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de>]

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. BTW, I have an
implementation in simple components:

http://www.dmitry-kazakov.de/ada/components.htm

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.

>> Yes, but from user's perspective, at least one query per connection is
>> always necessary. In some cases the user will need more, but not so often.
>> So the idea is that he just creates a connection and then does with it
>> anything he would do with a query. Further many "connection-level" things
>> require a query, if they return result sets, like "SHOW TABLES".
> 
> The thing is that you normally only create and connect to
> the database once in the whole application (at least in the
> normal case). You pass around the connection in the user
> data in GUI programs or any other way you like in non-GUI
> programs.
> 
> You then create, use and finalize Query_Type objects as
> required, occaisionally specifying a connection when
> required. This is really not that onerous to code. The
> code reads better, because the programmer has a clear
> concept of a connection and a query. By rolling it all
> up into one object that does "all", the programmer must
> visualize the components that are participating behind
> the scenes.

Just call it Database_Type! (:-))

>> BTW, one could get rid of query type altogether if there would be two
>> different connection-cloning. One that returns a handle to a new
>> "connection" (physically just new query) for the same underlying connection
>> object, another that makes a new connection.
>> 
>> I do not see much sense in all that levels of types exposed to the user. In
>> ODBC it is even one more: environment > connection > statement. It is
>> rather implementation details to me. (Everything is a file (:-))
> 
> ODBC is not the only one. Sybase implements an object that
> also fills this role. I believe that you want to keep the
> objects well separated by function, and I'll admit that I
> compromised principle by combining environment & connection.
> But I think OO design principles are such that you don't
> want to roll everything into one massive object.

Right, if the functionality differs. But so far there are little or no
things which can be made with a connection. That is different to ODBC. Also
ODBC has prepared statements. And that all just does ODBC too complex to
use.

> Now having said that, if you really insist on a one-object
> approach, you might want to test drive the idea by
> rolling your own "interface package" if you will, that
> does just that using the APQ provided types. But I believe
> that you'll quickly arrive at the unhappy conclusion that
> it just gets very messy, and results in a more complicated
> to understand object.

I already did it for ODBC (it is presently under test.) Of course it is
rather a specialized package to provide object persistence. But basically
it has only one type: Persistent_Storage_Handle, which is a handle to
Persistent_Storage_Object. I am considering to do the same with APQ. When
do you plan to ship v 2.2, BTW?

> I prefer to have 2 simple objects
> instead of one complicated to understand object. The
> code is much easier to read and understand this way.
> It also separates the finalization of the query from the
> finalization of the connection.

I had in mind:

declare
   DB : DB_Handle := Connect (Engine_MySQL, "localhost", "testdb",...);
begin
   Prepare (DB, "SELECT ...);
   Execute (DB);
   Fetch (DB);
   ...
end;

>>>>5. Arguably, connection should maintain list of all queries.
>>>
>>>You seem to be at cross purposes. First you want connection
>>>and query rolled into one, and then the connection should
>>>maintain a list of queries?
>> 
>> Internally of course. This could be necessary for "light-weight" connection
>> cloning.
>> 
>> Handle --> Query_Object --> Connection_Object
>> 
>> 1. Handle copy (+1 reference count of Query_Object)
>> 2. Light-weight copy: creates new query (+1 reference count of connection)
>> 3. Heavy-weight copy: creates a new query and a new connection
> 
> When designing an API, I always try to look at the way
> it is going to be used and why it must exist. I shy away
> from unnecessary API, unless it is frequently needed (to
> save or simplify code).
> 
> So coming back to this, I have to ask, what is the
> problem that this is intended to solve?

You will get rid of parallel hierarchy of types:

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 finalize
connection before a query that depends on it etc. You will have problems
with aliasing and side effects.

>>>>6. At least a rudimentary tables management:
>>>>
>>>>   Get_Tables (Query : in out Root_Query_Type);
> ...
>> Basically because of PostgreSQL, which lacks SQL query for that.
> 
> I think you can derive it from some "system tables".

PostgreSQL ODBC driver should know...

>> In that case you need to enumerate tables, table columns, their types.
>> Which BTW would require even more complex stuff: to determine Ada data type
>> most fitting to the column type. My case is much simpler. The application
>> starts, it checks if DB exists, if not, it creates that empty. I believe,
>> this is about 80% of needs for small applications.
> 
> These kinds of operations are important for SQL-helper
> tools, but are not the normal kinds of operations for
> normal applications, which tend to be more static.

But somebody should create that tables! It is quite normal that application
initializes tables upon first start. Consider an e-mail program. It should
store it address book into a DB of user choice. How to implement it in a DB
independent way?
 
>> See, this is why they should not be separated (at least, as long as Ada
>> does not support parallel type hierarchies.)
>> 
>> Anyway, there should be a uniform way of putting values into requests
>> separated from SQL-keywords, and, probably, names of tables, of columns
>> etc.
> 
> 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.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de