Re: Ada DB bindings and APQ

From: Dmitry A. Kazakov (mailbox_at_dmitry-kazakov.de)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 19:26:58 +0100

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

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... This is not passthrough query sql. ... and should be translated into ODBC SQL. ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • RE: ODBC Connection/Query
    ... Is it possible that your ODBC connection timed out in your query? ... check what ODBC Timeout is set to. ...
    (microsoft.public.access.queries)
  • Re: Ada DB bindings and APQ
    ... >>I didn't like the use of handles for database objects. ... > will ensure that the connection will not go away until at least one query ... > things which can be made with a connection. ... That is different to ODBC. ...
    (comp.lang.ada)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... I was able to find the MS Query button and locate my ... in both is ODBC. ... >> be certain that I am using the OLEDB method? ... >from the list of possible connection options. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)