Re: Ada DB bindings and APQ

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

Dmitry A. Kazakov wrote:
> After that hot "Windows Ada database support" discussion I have looked at
> APQ by Warren W. Gay. I do believe that it could be used as a starting
> point, because it was developed with generic DB access in mind. Here are
> things I consider important for further development of APQ:
> 
> 1. Dynamic binding to target DB.
> 
> 1.a. A factory which returns Root_Connection_Type'Class depending on the DB
> type parameter. Communication DLL should not be loaded until DB connection
> requested. Otherwise, all DBs have to be installed.

It is not quite true that "all DBs have to be installed". The windows
version of APQ does indeed include a static library component. However,
the remaining part of the code rests in the DLL pieces. For example,
MySQL uses the module c_mysql.c (and in the latest version Sybase
uses c_sybase.c) as an interface between the statically linked APQ
client program and the Windows DLL programs. Under Linux/UNIX, the
shared libraries are used for the Database libraries (libpq for
PostgreSQL for example). The exception is HPUX, where GNAT didn't
support shared libraries anyways (I don't know about the latest
GCC 3.4.X series, but I doubt it).

I'm not against the "factory" idea, but i don't think it will prove
to be very practical. The problem is that every database vendor has
its own unique way of identifying users to the database. PostgreSQL
is perhaps the strangest of them all, and they all overlap, but none
of them agree on the required set of parameters. You can possibly
make them agree in some situations, but this doesn't address existing
databases well.

> 1.b. A factory which determines the DB type upon connection and returns an
> appropriate Root_Connection_Type'Class. (this might be difficult though)

There is connection cloning available, but I see that
no factory form of it is available. I have made the newest
manual available (APQ 2.2) on the website :

    http://home.cogeco.ca/~ve3wwg/apq-2.2.pdf

This is a major cleanup of the APQ manual, and includes
Sybase documentation. I have been dragging my feet on its
release, because my time is short these days.

> 2. Root_Query_Type should know its connection:
> 
>    function Get_Connection (Query : Root_Query_Type)
>       return access Root_Connection_Type'Class; -- Ada 2005?
> 
>    function Get_Connection (Query : Root_Query_Type)
>       return access Root_Connection_Ptr; -- Ada 95

I see where you are going with this, but I'll admit that
I tried to keep the association between these objects
separate. Admitedly, internally, some products will
associate the two anyhow (Sybase does this with its own
C-based objects).

When I started with the first version, I didn't want
coupling between the Connection_Type and the Query_Type,
because one or the other could go out of scope first.
As long as they are kept separately, you avoid this
problem, because to invoke some operations like
"Execute", you must provide them both. Hence you
cannot accidentally have the Connection_Type
destructed for Execute (though the connection
object may be in an unconnected state - but this
is detectable ;-)  This keeps the application
programmer more honest.

> 3. Anyway, Execute should take only one parameter:
> 
>    Execute (Q); -- Query knows its connection

See the above. From a design point of view, I didn't
like this, because the application writer could mistakenly
allow the connection to fall out of scope, and hence
create "undefined behavior" at this point (because of
a hidden connection reference). This form:

       Execute(Q,C);

requires that the designer be aware that C better be
available and "connected"!

> 4. To add a scratch Query to each connection object and make anything
> available for a query for Root_Connection_Type as well.

I'm not sure I follow this point. It sounds like you want
to combine Query_Type and Connection_Type for programmer
convenience. This is a bad idea, IMHO.

Remember that there is a 1 -> many relationship between
the connection and query objects. Most DB client libraries
permit more than one active query on the same connection
(ie. connection sharing).

> 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?

> 6. At least a rudimentary tables management:
> 
>    Get_Tables (Query : in out Root_Query_Type);

This would be nice functionality. I think some care is
required in designing this, so that consistent and uniform
access to this information (or just table names?) is
done. This is not as easy as you might think. Read the
current manual cited above, and lookup how "case policy"
rules are now part of APQ-2.2. You would need to apply
"case policy" to this API also, me thinks.

Additionally, there is the problem as to "what tables"
to return. Informix for example keeps a set of tables
for each "database". Oracle OTOH, could return tables
on a schema basis, which is not necessarily the same
thing. You may need to return more than just a table
name, for this reason.

> 
>    the existing tables can be then fetched.
> 
>    function Table_Exists (Query : Root_Query_Type; Table_Name : String)
>       return Boolean;

Assuming the former API, this should be easy. But again,
for the schema case, you might need to specify table
owner in certain scenarios.

>    Drop_Table (Query : in out Root_Query_Type; Table_Name : String);

Is this one really required? Maybe it does - I mean, the
syntax for dropping a table is pretty uniform across all
databases I have used, but the "case policy" is not however,
for the table name. Perhaps on this basis alone, it is justified.

> 7. Close/Open_DB_Trace should be primitives of Root_Connection_Type. Same
> for Is_Trace.

I am not so sure about this one. You see, if you do that
you loose the option to choose which Query_Type objects
(and thus query streams) that you want to trace. If you
enable/disable at the Connection level, you enable/disable
for all queries -- not necessarily what you want (trace
information can be large in volume, and this often requires
selectivity).

> 8. Generic types support:
> 
>    function <Type_Name>_DB_Type (Connection : Root_Connection_Type)
>       return String;
> 
> yields the native DB name for the given type. For example:
> 
>    Prepare (Q, "CREATE TABLE customer_name ");
>    Append (String_DB_Type (My_Connection));
>       -- How varying strings are named there?

This is a "no win" situation IMHO. The problem is that you
often have "choices". You can't please everyone with this
approach, though I suppose it might be "configurable".
I'll leave this to someone else's imagination and research ;-)

> 9. For row identifiers there should be three of them:
> 
> -  Row_ID_DB_Type should return kind of "INTEGER NOT NULL AUTO_INCREMENT
> PRIMARY KEY" for MySQL, "SERIAL" for PostgreSQL etc.
> -  Row_Ref_DB_Type would return "INTEGER" to reference a row.
> -  Row_New_ID that would return the literal to be used in INSERT, like
> "NewID()", "NULL" etc

I can see where you are going with this, but I can't see
a lot of value in doing this. Usually tables are declared
ahead of time, outside of the application (temp tables are
the exception - depending upon database vendor [Oracle does
things differently again]). Where you do declare them on
the fly, you have the opportunity to use proper primary
keys etc. (providing you have one of course). Perhaps
your application needs are different.

Check the portability chapter for problems regarding
temp tables, if you are curious.

> 10. Unicode support (Wide_String etc)

Yes, I'll admit that I left this out on purpose. It wasn't important
to me, but for others this may be another matter.

> 11. Presently values are placed using Append or Append_Quoted. It is IMO
> error prone. Proposal: to add dispatching Value, which will encode values
> according to the DB:
> 
>    Prepare (Q, "INSERT ... VALUES (");
>    Value (Q, Customer, ","); -- Quote if necessary
>    Value (Q, Clock, ",");    -- Use appropriate time literal
>    Value (Q, Data, ")");

Actually, for some database vendors, quotes are _automatically_
provided for data values that need them (bit, date/time etc.).
In some cases dates must be quoted, and in others not. Booleans
are fun also, since some take TRUE/FALSE, others T/F, and still
others 0/1, and seems to me IIRC, even '0'b, and '1'b.

The Append_Quoted call has in mind a different purpose, and its
a subtle one: Append_Quoted is particularly important for character
strings, where special characters may be found within it. The most
obvious character that creates problems is the quote character itself!

So if you need to build a query like:

   INSERT INTO CUSTOMER ( NAME, ... )
                 VALUES ( 'Fred's Emporium', ... );

you will experience a nice tidy SQL syntax error. For some database
vendors (like Sybase, IIRC), you want:

   INSERT INTO CUSTOMER ( NAME, ... )
                 VALUES ( 'Fred''s Emporium', ... );

for others, like PostgreSQL, you want:

   INSERT INTO CUSTOMER ( NAME, ... )
                 VALUES ( 'Fred\'s Emporium', ... );

The Append_Quoted call is designed to relieve you from having to
figure out which characters to quote, and how to quote it.

For this call to work, it _must_ have both the Query_Type object
and the Connection_Type object. While this isn't strictly true
of all database vendors, APQ now requires it because most require
this, so that it can properly escape the string according to the
locale chosen. The locale is usually configured at the connection
level.

The newest unreleased version of APQ (2.2) also maintains what
is called a "case policy". Obviously data values that are
encoded with Append_Quoted automatically preserve the case
of the text, regardless if the case policy is to force upper or
lower case of SQL text. All non-quoted text is subject to the
case policy, unless the case policy is to leave the case as
is. The case policy was necessary to bridge the gap between
databases that are case sensitive on the table names and those
that are not (See APQ 2.2 manual for more information).

The Future of APQ:
------------------

What I plan to do in the near future, is to make APQ available
somewhere accessible to all. I haven't given it much thought
yet, but I was considering sourceforge. I want to make it
available for others to contribute to it, because it needs
more work and time than I have available. Quite frankly I
personally don't have much interest in an M$ SQL Server
branch, yet I recognize this would be useful to others. Obviously,
other ports like Oracle would also be useful.

Thank-you for looking at the documentation and sharing your
thoughts on the design.

I welcome any other thoughts and suggestions on improvements.
If anyone has any interest in participating in future releases
and upgrades of APQ, perhaps you would like to voice your
vote for the best place to host it.

Thanks, Warren.
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg