Re: Ada DB bindings and APQ

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

On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:

> 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).

Yep, the first error one gets: lpq not found. libpq.a is not distributed
with GNAT. This might become a problem in the future.

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

I think that: User_Name : String; Password : String; + maybe additional
String for vendor-specific "modes" should cover all possibilities. Quite
similar to Ada.Text_IO.Open, it is caller's responsibility to provide
User_Name in the required format.

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

OK, thanks.

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

I meant a quite wild thing: you do not know the DB type, only the
host+DB+user+password. Then you connect (conventional ports are scanned,
severs detected etc (:-))

>> 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).

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.

>> 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).

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".

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 (:-))

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

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

At least names. Querying column types would be much more complex.

> This is not as easy as you might think.

Basically because of PostgreSQL, which lacks SQL query for that.

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

The user specified upon connection?

>> 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).

I depends on what is the primary object the user works with. But in any
case it should be a primitive operation either of Root_Connection_Type or
of Root_Query_Type.

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

The above is the scenario when you are working with some pre-existing DB.
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.

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

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.

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

I think you could provide APQ.ODBC which would then cover anything "not yet
ready". Though OID would be a hammer with ODBC.

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