Ada DB bindings and APQ

Ada DB bindings and APQ

[Dmitry A. Kazakov]

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.

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

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

3. Anyway, Execute should take only one parameter:

   Execute (Q); -- Query knows its connection

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

5. Arguably, connection should maintain list of all queries.

6. At least a rudimentary tables management:

   Get_Tables (Query : in out Root_Query_Type);

   the existing tables can be then fetched.

   function Table_Exists (Query : Root_Query_Type; Table_Name : String)
      return Boolean;
   Drop_Table (Query : in out Root_Query_Type; Table_Name : String);

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

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?

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

10. Unicode support (Wide_String etc)

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, ")");

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

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

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

Re: Ada DB bindings and APQ

[Georg Bauhaus]

Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
: On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:
: 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 at all surprised that when I use a binding but not the bound
thing, I'll get an error.

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

I doubt this very much. Indeed, we have a server type application
that does not work at all without a connection pool, because performance
is affected rather drastically when you control connections.
This is at least true for the DB systems we have had a chance to try.

If then you want one physical connection to play the part of a
"reusable" connection that is not in the way of programmers,
then why do you need one at all in the abstraction?
For example, how about ticket based query authentication?

: I do not see much sense in all that levels of types exposed to the user.

As a user, I agree that a DWIM DB abstraction is wonderful.
It just isn't practical when using current database technology :-)

: 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

This starts looking like optimazation techniques used in current
database systems.

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

Aha! So your goal is to have a convenient abstraction for
a specific kind of data storage in small applications?

: Anyway, there should be a uniform way of putting values into requests
: separated from SQL-keywords, and, probably, names of tables, of columns
: etc.

This again sounds like a newly designed Ada-interface based OO data storage
system for Ada programs... Go ahead :)


-- Georg

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Tue, 14 Dec 2004 18:29:16 +0000 (UTC), Georg Bauhaus wrote:

> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
>: On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:
>: 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 at all surprised that when I use a binding but not the bound
> thing, I'll get an error.

Does your OS complain about all devices you could potentially attach via
USB?

>: 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.
> 
> I doubt this very much. Indeed, we have a server type application
> that does not work at all without a connection pool, because performance
> is affected rather drastically when you control connections.

OK, but this does not contradict my point.

> This is at least true for the DB systems we have had a chance to try.
> 
> If then you want one physical connection to play the part of a
> "reusable" connection that is not in the way of programmers,
> then why do you need one at all in the abstraction?
> For example, how about ticket based query authentication?

Sounds as an implementation detail to me, elaborate.

>: I do not see much sense in all that levels of types exposed to the user.
> 
> As a user, I agree that a DWIM DB abstraction is wonderful.
> It just isn't practical when using current database technology :-)

I still do not see any real advantage in separating query and connection.
It would be no problem to have both multiple physical connections and
multiple physical queries without exposing them.

Maybe within the hierarchy: transaction / server, it makes sense to
distinguish both, but separating query /connection makes no sense to me. 

>: 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
> 
> This starts looking like optimazation techniques used in current
> database systems.

So, you agree? (:-))

>: 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.
> 
> Aha! So your goal is to have a convenient abstraction for
> a specific kind of data storage in small applications?

Sure. For a large application, DB will almost certainly be fixed in the
requirements. Forget it. But!

Do it [right] for small things first. Then you will see how quickly it will
eat that large players. PCs vs workstations (or cockroaches vs humans, if
you want (:-)), lessons to learn!

>: Anyway, there should be a uniform way of putting values into requests
>: separated from SQL-keywords, and, probably, names of tables, of columns
>: etc.
> 
> This again sounds like a newly designed Ada-interface based OO data storage
> system for Ada programs...

That's not OO, yet. It is rather typed vs untyped.

> Go ahead :)

Let's see (:-))

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

Re: Ada DB bindings and APQ

[Georg Bauhaus]

Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
: On Tue, 14 Dec 2004 18:29:16 +0000 (UTC), Georg Bauhaus wrote:
: 
:> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
:>: On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:
:>: 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 at all surprised that when I use a binding but not the bound
:> thing, I'll get an error.
: 
: Does your OS complain about all devices you could potentially attach via
: USB?

It is surprising if binding to the operating system fails.
Driver not installed?

 
:>: 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.
:> 
:> I doubt this very much. Indeed, we have a server type application
:> that does not work at all without a connection pool, because performance
:> is affected rather drastically when you control connections.
: 
: OK, but this does not contradict my point.

When you said, "but not so often," and I said, "doubt this very much",
(then gave just one example) I wanted to say that many DBes typically
_are_ queried frequently, in Hz.

But then at the end of your post you write that you envision a distributed
application that stores data in the net. Is this the point from wich
you argue? Quoting Warren, "You seem to be at cross purposes," maybe?

Have you had a chance to study Tanenbaum's et aliorum distributed systems
reasearch (Amoeba, The Globe, ...)?
There should be plenty of information, including a language study
comparing Ada's protected objects and Orca's shared data objects
in distributed systems.

http://www.cs.vu.nl/pub/papers/[orca/]

-- Georg

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Warren" == Warren W Gay VE3WWG <ve3wwg@NoSpam.cogeco.ca> writes:

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

The solution here is to use a notation for identifying the database
that will work for all database.

I have to say I really like the PHP pear URL like syntax, copied from:

<URL:http://pear.php.net/manual/en/package.database.db.intro-dsn.php>

Example 26-1. Connect to database through a socket

mysql://user@unix(/path/to/socket)/pear

Example 26-2. Connect to database on a non standard port

pgsql://user:pass@tcp(localhost:5555)/pear

Example 26-3. Connect to SQLite on a Unix machine using options

sqlite:////full/unix/path/to/file.db?mode=0666

Example 26-4. Connect to SQLite on a Windows machine using options

sqlite:///c:/full/windows/path/to/file.db?mode=0666

Example 26-5. Connect to MySQLi using SSL

mysqli://user:pass@localhost/pear?key=client-key.pem&cert=client-cert.pem

Example 26-6. Connecting to MS Access sometimes requires admin as the user name

odbc(access)://admin@/datasourcename

Example 26-7. Connecting to ODBC with a specific cursor

odbc(access)://admin@/datasourcename?cursor=SQL_CUR_USE_ODBC

pgsql://someuser:apasswd@localhost/thedb

I think this is rather important as it would mean you don't need to
recompile the application in order to use a different database. It
also means the code doesn't have to explicitly support connecting to
every possible database that APQ supports.

Instead you just need *one* config option "database_url" that contains
the URL as per one of the above format.

If you want sample code to pass the URL, it is already written, it
just needs to be ported from PHP to Ada... Not sure how easy this will
be.
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Dmitry" == Dmitry A Kazakov <mailbox@dmitry-kazakov.de> writes:

    Dmitry> That's not OO, yet. It is rather typed vs untyped.

It seems to me it has all the features of an OO system (encapsulation,
abstraction, inheritance, etc). Rather the issue is that some design
decisions have been made that you don't agree with.

While I can see your point of view in issues such as the query type
being independent of the query string, I can also understand the
reasons behind doing it this way, and probably would have done the
same thing myself.

As far I can tell some of your goals of abstraction, while ideal,
solutions may require more thought and research in order to make sure
compatibility with all databases and to ensure your goals really are
met. For instance, my ideal abstraction layer would completely hide
the SQL layer completely but maintain the flexibility of the SQL
layer. This I think would be really difficult to do, and I am not
volunteering... It would mean that it could potentially work with any
database, not just an SQL based database.

Ok, back on planet Earth now, my sane and reasonable priorities for
this project would be:

1. Factory for creating a connection based on a URL (see my previous
   message). I would be happy to start working on this if desired
   (note: I don't claim to be any expert on parsing strings in
   Ada...).

   (note: with 1 completed the next step some people will demand is
   plugins so you can support other database without recompilation.  I
   personally don't think this is worth it at the moment, especially
   as APQ doesn't hide all differences in different databases).

2. Support for different character sets. I do not know what this would
   require, but it is a real problem that different database can have
   different character sets, and making assumptions isn't a good
   idea. Not only that, but in latest versions of mysql different
   tables within the same database can have different character
   sets. At the minimum I guess it should be possible to query what
   character set a table is encoded with (note: I don't know how to do
   this). 16 bit UTF support would be nice, but we have UTF8 in the
   meantime.

3. Support for other SQL based databases. Note I have this as the
   lowest priority...
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Brian May wrote:
>>>>>>"Warren" == Warren W Gay VE3WWG <ve3wwg@NoSpam.cogeco.ca> writes:
>     Warren> I'm not against the "factory" idea, but i don't think it
>     Warren> will prove to be very practical. The problem is that every
>     Warren> database vendor has its own unique way of identifying
>     Warren> users to the database. PostgreSQL is perhaps the strangest
>     Warren> of them all, and they all overlap, but none of them agree
>     Warren> on the required set of parameters. You can possibly make
>     Warren> them agree in some situations, but this doesn't address
>     Warren> existing databases well.
> 
> The solution here is to use a notation for identifying the database
> that will work for all database.
> 
> I have to say I really like the PHP pear URL like syntax, copied from:
> 
> <URL:http://pear.php.net/manual/en/package.database.db.intro-dsn.php>
> 
> Example 26-1. Connect to database through a socket
> 
> mysql://user@unix(/path/to/socket)/pear
> 
> Example 26-2. Connect to database on a non standard port
> 
> pgsql://user:pass@tcp(localhost:5555)/pear

That's not bad actually. That would also let the application
designer put all that nasty config data in one environment
variable (or command argument).

> Example 26-3. Connect to SQLite on a Unix machine using options
> 
> sqlite:////full/unix/path/to/file.db?mode=0666

Not sure why there are so many leading slashes in this case.

> Example 26-4. Connect to SQLite on a Windows machine using options
> 
> sqlite:///c:/full/windows/path/to/file.db?mode=0666
> 
> Example 26-5. Connect to MySQLi using SSL
> 
> mysqli://user:pass@localhost/pear?key=client-key.pem&cert=client-cert.pem
> 
> Example 26-6. Connecting to MS Access sometimes requires admin as the user name
> 
> odbc(access)://admin@/datasourcename
> 
> Example 26-7. Connecting to ODBC with a specific cursor
> 
> odbc(access)://admin@/datasourcename?cursor=SQL_CUR_USE_ODBC
> 
> pgsql://someuser:apasswd@localhost/thedb
> 
> I think this is rather important as it would mean you don't need to
> recompile the application in order to use a different database. It
> also means the code doesn't have to explicitly support connecting to
> every possible database that APQ supports.
> 
> Instead you just need *one* config option "database_url" that contains
> the URL as per one of the above format.
> 
> If you want sample code to pass the URL, it is already written, it
> just needs to be ported from PHP to Ada... Not sure how easy this will
> be.

I like this idea. What it needs on the Linux/*NIX side is to use
dynamically loaded libraries, so that it doesn't insist on all
other shared libraries, even when not used. The shared library
should only be loaded when required.
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Dmitry A. Kazakov wrote:
> 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.

APQ 2.2 does not require you to compile for PostgreSQL, though
earlier versions probably did (I don't remember anymore). I think
that the URL idea that Brian May presented was a good one, and this
should be combined with the idea of dynamically loading shared
libraries upon demand as required (as you suggested).

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

Ah, but I thinks you ask too much ;-)  However, specifying the
database type in URL syntax does become a more practical
possibility.


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

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.

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

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.

When you use a database like Sybase that permits cursor
updates etc., then you definitely will be using 2 Query_Type
objects as a matter of course.

Rolling it all into one object blurs the distinctions, and
requires internal references to hidden objects, linked lists
and reference counting -- all in black box fashion.

I believe that the Ada approach of using objects for what
they do, is both sensible and more readable as it sits now.

Below you mentioned the idea of an environment object.  I was
tempted to implement that in APQ as well. However, I
compromised on this to consolidate the environment into
a combined environment + connection object for simplicity's
sake. The main reason for this was that in most applications,
you will use one connection and thus one environment.
But in purer terms, I would agree that APQ should have
an Environment_Type, that the Connection_Type uses.

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

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

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

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

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

But if the tables are pre-existing, you are not concerned
with how the table's column is declared. The only normal
case for caring about this IMHO is for temp tables, but
that is its own can of worms (see portability section of
the latest manual for why).

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

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

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

That idea is good also. Perhaps someone will contribute that
for us all ;-)
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Ole-Hjalmar Kristensen]

>>>>> "GB" == Georg Bauhaus <sb463ba@l1-hrz.uni-duisburg.de> writes:

    GB> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
    GB> : On Tue, 14 Dec 2004 18:29:16 +0000 (UTC), Georg Bauhaus wrote:
    GB> : 
    GB> :> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
    GB> :>: On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:
    GB> :>: Yep, the first error one gets: lpq not found. libpq.a is not distributed
    GB> :>: with GNAT. This might become a problem in the future.
    GB> :> 
    GB> :> I'm not at all surprised that when I use a binding but not the bound
    GB> :> thing, I'll get an error.
    GB> : 
    GB> : Does your OS complain about all devices you could potentially attach via
    GB> : USB?

    GB> It is surprising if binding to the operating system fails.
    GB> Driver not installed?

 
    GB> :>: Yes, but from user's perspective, at least one query per connection is
    GB> :>: always necessary. In some cases the user will need more, but not so often.
    GB> :> 
    GB> :> I doubt this very much. Indeed, we have a server type application
    GB> :> that does not work at all without a connection pool, because performance
    GB> :> is affected rather drastically when you control connections.
    GB> : 
    GB> : OK, but this does not contradict my point.

    GB> When you said, "but not so often," and I said, "doubt this very much",
    GB> (then gave just one example) I wanted to say that many DBes typically
    GB> _are_ queried frequently, in Hz.

Yes, a large server will typically have a transaction rate of several
thousand transactions per second.

<snip>

    GB> -- Georg

-- 
   C++: The power, elegance and simplicity of a hand grenade.

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Warren" == Warren W Gay VE3WWG <ve3wwg@NoSpam.cogeco.ca> writes:

Hello Warren,

After reading some of this code, I can begin to understand some of the
issues better. Some comments below.

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

Unfortunately, you seem to have lost this benefit already, Query_Type
*is* coupled to Connection_Type (or it looks that way to me):

      Q : Root_Query_Type'Class := New_Query(C1);

begin

...
      Execute_Checked(Q,C2);
...


I get the impression that New_Query(C1) constrains Q so it is only
guaranteed to work with C1.  As such C2 needs to be C1 (or am I
mistaken)?

Worst case, what happens if C1 is a Postgresql connection, but C2 is a
Mysql connection, and the programmer got the two confused?

If this is the case, then one of the parameters is redundant, likely
to cause errors, and should be removed...

As the query string is specific to a connection anyway, I think the
reference in New_Query should stay.

Same goes with the encoding routines.

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

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

This is an issue. Obviously the connection needs to last longer then
the query.

I believe an issue, especially for the case of Execute, is that it may
change the results of the connection, e.g. to error status. You don't
want it to change values that have no obvious connection with the
parameters.

Possible solution (rough):

Split Connection_Type into two types, a Connection_Type and a
Internal_Type.  The user only sees the Connection_Type. Internal_Type
(or whatever you want to call it) is the actual database connection
(similar to the current Connection_Type). Connection_Type and
Query_Type both refer to Internal_Type, and Internal_Type has
reference counting (so it isn't closed while a reference still
exists).

As far as the user is concerned the types work just the same as
before, with one important difference: If an error occurs when calling
Execute(Q), the state of the connection is not effected. Rather the
error information is stored in the Query_Type object instead.

If the user closes the Connection_Type, then it closes the connection,
but doesn't free Internal_Type if Queries are still referring to it
(as known by reference counting). If a query tries to use the
connection after it is closed, then an exception is generated.

Obviously this is rough, and I haven't considered certain issues yet
(most obvious question for me at the moment: would we still need a
child class of Connection_Type for every database? If we don't, then
every connection has to have the same API; need to think about this
more).

I hope some of this makes sense to others, and I hope it makes sense
to me after I wake up tomorrow ;-).


On the matter of coding style, I often notice loops like the following
in APQ code and manual of the form:

      loop
         begin
            Fetch(Q);
         exception
            when No_Tuple =>
               exit;
         end;

         ...

      end loop;

I don't like this, an exception should only be for unexpected events,
but the end of the query is completely expected.

The documentation has an alternative loop structure:

while not End_Of_Query(Q) loop
      Fetch(Q);
      ...
end loop;

This, IMHO, is better as no exceptions occur unless something
unexpected really does occur. It also looks neater and easier to read.
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Georg Bauhaus]

Warren W. Gay VE3WWG <ve3wwg@nospam.cogeco.ca> wrote:
: Brian May wrote:
 
 
:> Example 26-3. Connect to SQLite on a Unix machine using options
:> 
:> sqlite:////full/unix/path/to/file.db?mode=0666
: 
: Not sure why there are so many leading slashes in this case.

I think its because the database name is actually a file name?
The example having 'hostspec:110//usr/db_file.db' may be similar.
Not sure either.

I've started writing a grammer, here is the
part for the first variant of the syntax. (It may have missed
the '//', and others of course.-)

Comments most welcome.  The start rule is named DSN.


Char -> mostly UTF-8 alphanumeric, in particular not on of []{}(),;?*=!@
          (as per Microsoft, but what to do withouth [] on VMS or
          Toronto file system?)

          (Char needs to be escaped in places. This is where the
          parser might become tricky. But we can count brackets,
	  and count implicitly (e.g., ':', '/').)

MSExcl -> '[' | ']' | '{' | '}' | '(' | ')'
MSExcl -> ',' | ';' | '?' | '*' | '=' | '!' | '@'

PWChar -> what characters are allowed in DBes? Can they include MSExcl

DBSpeak -> 'odbc', 'mysql', ...

DB -> 'mysql', 'psql', 'db2', 'sybase', 'solid',  ...

  (Note the ovelap of DBSpeak and DB)


Protocol -> 'tcp', 'p9', ...

Protocol_Opts -> TCP_Opts | P9_Opts | ...  (foreach in Protocol)

Host -> Hostname Opt_Port

Opt_Port -> ':' Port

Hostname -> IP | FQN

Port -> DIGIT DIGIT*    (how many digits max?)

IP -> an IP number, V4 or V6

FQN -> fully qualified host name

DBName -> Char Char*   (is this DB specific?)

UName -> Char Char*
      (is this restrictive? ':' in UName complicates.
      Are there DB systems allowing ':' in UName?)

PWord -> PWChar PWChar*



TCP_Opts -> Host

QSOptions -> '?' a query string as per CGI, for additional DB specific options
	   (note that '?' is excluded from Char)


The two Variants could either be treated separately, or we
could remember whether the variant syntax was used, and then
swith later. But the variant syntax seems to be simpler than
the "standard" one, in particular I see not QSOptions. So just
decide right at the beginning by the presence of '('...')' and
reuse components not at the grammar level, but at implementation
leven (if needed).


                ***   S T A R T   H E R E   ***

                ***        Standard         ***

DSN -> DB First_Colon


First_Colon -> ':' '//'         '/' DBName_Tail
First_Colon -> ':' '//' Server  '/' DBName_Tail
First_Colon -> ':' '//' U       '/' DBName_Tail

	    (There is overlap in Server and U, but as a minimum, we can
	     look ahead for required '@', implying U)

U -> UName Opt_PW '@' Server
Opt_PW -> ':' PWord

Server -> Host
Server -> Protocol '+' Host

       (again, look ahead for '+' before EOS or '/' or '?'.
       Can there be QSOptions without a preceding DBName?
       Check this anyway, a '+' in a QSOptions is not likely the right one)
       (hostnames may be named using names from DB!)


DBName_Tail -> '/' DBName QSOptions




             *** O R   S T A R T   H E R E ***


                ***   Variant syntax   ***


DSN -> DBSpeak '(' DB ')' ...

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Tue, 14 Dec 2004 21:06:56 +0000 (UTC), Georg Bauhaus wrote:

> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
>: On Tue, 14 Dec 2004 18:29:16 +0000 (UTC), Georg Bauhaus wrote:
>: 
>:> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:
>:>: On Tue, 14 Dec 2004 02:25:22 -0500, Warren W. Gay VE3WWG wrote:
>:>: 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 at all surprised that when I use a binding but not the bound
>:> thing, I'll get an error.
>: 
>: Does your OS complain about all devices you could potentially attach via
>: USB?
> 
> It is surprising if binding to the operating system fails.
> Driver not installed?

APQ <-> USB
PosgreSQL <-> Mouse
MySQL <-> USB Stick

>:>: 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.
>:> 
>:> I doubt this very much. Indeed, we have a server type application
>:> that does not work at all without a connection pool, because performance
>:> is affected rather drastically when you control connections.
>: 
>: OK, but this does not contradict my point.
> 
> When you said, "but not so often," and I said, "doubt this very much",
> (then gave just one example) I wanted to say that many DBes typically
> _are_ queried frequently, in Hz.

OK, but how this might disprove that each connection needs at least one
query?

> But then at the end of your post you write that you envision a distributed
> application that stores data in the net. Is this the point from wich
> you argue? Quoting Warren, "You seem to be at cross purposes," maybe?

The point is that separation of queries and connections in API is
superfluous.

> Have you had a chance to study Tanenbaum's et aliorum distributed systems
> reasearch (Amoeba, The Globe, ...)?

I know Andy's works.

> There should be plenty of information, including a language study
> comparing Ada's protected objects and Orca's shared data objects
> in distributed systems.
> 
> http://www.cs.vu.nl/pub/papers/[orca/]

As I said before, that is a very long term perspective. When distributed
systems finally come, they will eliminate the very notion of data base.

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Wed, 15 Dec 2004 10:26:37 +1100, Brian May wrote:

>>>>>> "Dmitry" == Dmitry A Kazakov <mailbox@dmitry-kazakov.de> writes:
> 
>     Dmitry> That's not OO, yet. It is rather typed vs untyped.
> 
> It seems to me it has all the features of an OO system (encapsulation,
> abstraction, inheritance, etc). Rather the issue is that some design
> decisions have been made that you don't agree with.

I didn't mean APQ, otherwise I wouldn't consider it as a starting point. I
meant only the way of constructing queries. Specifically, that there is no
*typed* difference between "CREATE", "1.35" and "INTEGER". Everything is a
string to append. This is untyped => error prone, uncheckable,
unmaintainable.

> While I can see your point of view in issues such as the query type
> being independent of the query string, I can also understand the
> reasons behind doing it this way, and probably would have done the
> same thing myself.

This the next goal: to map the query semantics to a type hierarchy. As the
previous discussion shown, it seems that people are not much ready to
accept it. Let's do it step by step. (:-))
 
> As far I can tell some of your goals of abstraction, while ideal,
> solutions may require more thought and research in order to make sure
> compatibility with all databases and to ensure your goals really are
> met. For instance, my ideal abstraction layer would completely hide
> the SQL layer completely but maintain the flexibility of the SQL
> layer.

Absolutely, but see above.

> This I think would be really difficult to do, and I am not
> volunteering... It would mean that it could potentially work with any
> database, not just an SQL based database.
> 
> Ok, back on planet Earth now, my sane and reasonable priorities for
> this project would be:
> 
> 1. Factory for creating a connection based on a URL (see my previous
>    message). I would be happy to start working on this if desired
>    (note: I don't claim to be any expert on parsing strings in
>    Ada...).

Parsing is not a problem. But advantage is not clear. Basically you need:

- Server name
- DB name
- User name
- Password
- Port

What would change if we pack all that in one string instead of four + one
number?

Real difference is when ODBC provides some DB identification service which
combines Server name + DB name + port in one DSN. But this cannot be done
on the client side. So if you have no MS influence 

>    (note: with 1 completed the next step some people will demand is
>    plugins so you can support other database without recompilation.

I demand it right now! (:-))

>    I  personally don't think this is worth it at the moment, especially
>    as APQ doesn't hide all differences in different databases).

But this is the primary objective in my view.

> 2. Support for different character sets. I do not know what this would
>    require, but it is a real problem that different database can have
>    different character sets, and making assumptions isn't a good
>    idea. Not only that, but in latest versions of mysql different
>    tables within the same database can have different character
>    sets. At the minimum I guess it should be possible to query what
>    character set a table is encoded with (note: I don't know how to do
>    this). 16 bit UTF support would be nice, but we have UTF8 in the
>    meantime.

Fully agree. Main application areas of DBs are localized. So Unicode is a
must.

> 3. Support for other SQL based databases. Note I have this as the
>    lowest priority...

Only within a stable API framework. Otherwise, adding a new DB would lead
to reworking APIs.

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

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

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Dmitry" == Dmitry A Kazakov <mailbox@dmitry-kazakov.de> writes:

    Dmitry> I didn't mean APQ, otherwise I wouldn't consider it as a
    Dmitry> starting point. I meant only the way of constructing
    Dmitry> queries. Specifically, that there is no *typed* difference
    Dmitry> between "CREATE", "1.35" and "INTEGER". Everything is a
    Dmitry> string to append. This is untyped => error prone,
    Dmitry> uncheckable, unmaintainable.

Good point. SQL was never designed to be type safe, and as such it is
the weakest link when combined with a type safe language like Ada. For
example, you can pass SQL a string for an integer type, and as long as
the string happens to be a integer, you will never notice any
problems. Pass it a string that cannot be parsed as an integer, and
the database will complain.

However, I consider this a low priority at the moment, I think some of
the other issues are a higher priority.
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Brian" == Brian May <bam@snoopy.apana.org.au> writes:

    Brian> The documentation has an alternative loop structure:

    Brian> while not End_Of_Query(Q) loop Fetch(Q); ...  end loop;

I realized, despite the documentation, End_Of_Query is not currently
supported on sequential connections, because it is not known if all
tuples have been fetched until you try to fetch the next one past the
end.

This limitation could be overcome if the previous call to the
"execute" or "fetch" called fetch in advance for the next row, and
stored the results in a temporary holding point. You don't miss out on
anything either, as all the rows will eventually have to be fetched
anyway.

This is better, IMHO, then requiring an exception be the terminating
condition for a loop.

An alternative would be to restructure the loop as:

while true loop
      Fetch(Q);
      exit if No_More_Data(Q);

      ...
end loop;

I don't particular like this approach though, although it would work.
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

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?

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

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

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

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

The "code" has been done for months. The *NIX install part
is mostly done, though I am not really happy with it.

The Windows install side is a nightmare. Here's why:

  - gnat 3.14p/3.15p is one choice
  - CYGWIN gcc with Ada support is another
  - PostgreSQL can now be included for CYGWIN
  - or the C client library libpq can be compiled
    independant of CYGWIN

So at this point, what I need to do is to get it up on
SourceForge and let someone who has time and energy
work this all out. I am just too busy to dedicate much
time on this right now (I have become enchanted by another
very fun, new Ada-involved project at the moment).

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

I would suggest you test drive APQ for a while. You might
find that it works rather well the way it is. I am not
saying that it cannot be improved (quite the contrary),
but I think there are more important things to improve
upon without munging all objects into one large massive
one.

>>>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 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. There are a number of other things that
are only checked at runtime when using databases, and I
see this as a nit-pick. Better to use that sledgehammer
on bigger flies!

I can tell you from experience, that I have written a
number of test programs that copy from PostgreSQL
database tables to MySQL, and then to Sybase, all in
the same programs, and I never mixed them up.

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

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

I won't disagree with it for applications like this. "Normal"
is a relative thing I guess, but normally in my book, installs
usually run some SQL scripts to create and initialize databases
for applications. An email application need not be different,
though it can be, as you've suggested.  The reason being is that
these things are typically done once, and then never performed
again (install scripts can be discarded once used).

>>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 ;-)
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Brian May wrote:

>>>>>>"Brian" == Brian May <bam@snoopy.apana.org.au> writes:
> 
> 
>     Brian> The documentation has an alternative loop structure:
> 
>     Brian> while not End_Of_Query(Q) loop Fetch(Q); ...  end loop;
> 
> I realized, despite the documentation, End_Of_Query is not currently
> supported on sequential connections, because it is not known if all
> tuples have been fetched until you try to fetch the next one past the
> end.

Actually for PostgreSQL, this works fine. But for MySQL (at
least for versions that I worked with), it was "MySQL busted"
(this is documented in the APQ 2.2 manual). MySQL's library
would first indicate that it is "not at end", and then when
you went to fetch a row, it would realize "whoopsie, we are
at the end of rows".

Then when I got to Sybase (IIRC), you cannot do this kind of test
at all in sequential mode.

> This limitation could be overcome if the previous call to the
> "execute" or "fetch" called fetch in advance for the next row, and
> stored the results in a temporary holding point. You don't miss out on
> anything either, as all the rows will eventually have to be fetched
> anyway.

Prefetching introduces much ugliness in the Query_Type object,
because you change the state of things. This can be especially
complicated for Sybase, because the Sybase is very fussy
about what happens on the connection and its state(s). Based
upon what I've seen so far, I would _not_ recommend any kind
of prefetching.

> This is better, IMHO, then requiring an exception be the terminating
> condition for a loop.

I'll agree that I am not completely happy with the
way that Fetch raises the exception, but I felt that
was better than the possibility of ignoring a
failure (the C tendancy). That way if you code
a SELECT for example that should only return 1
row, but you get zero rows for some reason, a
Fetch would raise an exception (it cannot be
ignored without going out of your way!)

The other consideration is that normally many
rows are fetched, and there is only one "End".
In this scenario, I felt that the exception was
an acceptable compromise.

> An alternative would be to restructure the loop as:
> 
> while true loop
>       Fetch(Q);
>       exit if No_More_Data(Q);
> 
>       ...
> end loop;
> 
> I don't particular like this approach though, although it would work.

I like the neatness of this approach, but the danger is
that you might do the "..." part on a row that might
not exist. Though I suppose the Query_Type object could
maintain enough state such that it can raise an exception
if any "Values" were fetched from the row that is not there.
Just a quibble: I'd prefer something like "No_Row(Q)" perhaps.

An even neater loop is this, IMHO ;-)

 > loop
 >       Fetch(Q);
 >       exit when No_More_Data(Q);
 >
 >       ...
 > end loop;
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Georg Bauhaus]

Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:

: OK, but how this might disprove that each connection needs at least one
: query?

I was addressing "not so often". If there is only one query per connection
and not many queries per (some existing) connection, then the system does
no longer meet the real time requirements, because establishing
a connection needs quite some time and resources.

: As I said before, that is a very long term perspective. When distributed
: systems finally come, they will eliminate the very notion of data base.

You mean a data flow system? ;-)


-- Georg

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Georg" == Georg Bauhaus <sb463ba@l1-hrz.uni-duisburg.de> writes:
  
    Georg> :> Example 26-3. Connect to SQLite on a Unix machine using options
    Georg> :> 
    Georg> :> sqlite:////full/unix/path/to/file.db?mode=0666
    Georg> : 
    Georg> : Not sure why there are so many leading slashes in this case.

    Georg> I think its because the database name is actually a file name?
    Georg> The example having 'hostspec:110//usr/db_file.db' may be similar.
    Georg> Not sure either.

I would speculate:

sqlite:// + nullhost + / + path

where path="/full/unix/path/to/file.db?mode=0666" maybe with the implication
that you could use:

sqlite:///file.db to refer to the database file in the current directory.

Seems kind of dodgy to me, but you don't want to have to use relative
paths for everything.

For reference, here is the code use for decoding these URLs in
PHP/PEAR. It was copied from /usr/share/php/DB.php on my computer. It
might be possible to rewrite this in Ada.

Also see documentation for rawurldecode at
<URL:http://au2.php.net/manual/en/function.rawurldecode.php>.
     
    // {{{ parseDSN()

    /**
     * Parse a data source name.
     *
     * Additional keys can be added by appending a URI query string to the
     * end of the DSN.
     *
     * The format of the supplied DSN is in its fullest form:
     * <code>
     *  phptype(dbsyntax)://username:password@protocol+hostspec/database?option=8&another=true
     * </code>
     *
     * Most variations are allowed:
     * <code>
     *  phptype://username:password@protocol+hostspec:110//usr/db_file.db?mode=0644
     *  phptype://username:password@hostspec/database_name
     *  phptype://username:password@hostspec
     *  phptype://username@hostspec
     *  phptype://hostspec/database
     *  phptype://hostspec
     *  phptype(dbsyntax)
     *  phptype
     * </code>
     *
     * @param string $dsn Data Source Name to be parsed
     *
     * @return array an associative array with the following keys:
     *  + phptype:  Database backend used in PHP (mysql, odbc etc.)
     *  + dbsyntax: Database used with regards to SQL syntax etc.
     *  + protocol: Communication protocol to use (tcp, unix etc.)
     *  + hostspec: Host specification (hostname[:port])
     *  + database: Database to use on the DBMS server
     *  + username: User name for login
     *  + password: Password for login
     *
     * @author Tomas V.V.Cox <cox@idecnet.com>
     */
    function parseDSN($dsn)
    {
        $parsed = array(
            'phptype'  => false,
            'dbsyntax' => false,
            'username' => false,
            'password' => false,
            'protocol' => false,
            'hostspec' => false,
            'port'     => false,
            'socket'   => false,
            'database' => false,
        );

        if (is_array($dsn)) {
            $dsn = array_merge($parsed, $dsn);
            if (!$dsn['dbsyntax']) {
                $dsn['dbsyntax'] = $dsn['phptype'];
            }
            return $dsn;
        }

        // Find phptype and dbsyntax
        if (($pos = strpos($dsn, '://')) !== false) {
            $str = substr($dsn, 0, $pos);
            $dsn = substr($dsn, $pos + 3);
        } else {
            $str = $dsn;
            $dsn = null;
        }

        // Get phptype and dbsyntax
        // $str => phptype(dbsyntax)
        if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) {
            $parsed['phptype']  = $arr[1];
            $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2];
        } else {
            $parsed['phptype']  = $str;
            $parsed['dbsyntax'] = $str;
        }

        if (!count($dsn)) {
            return $parsed;
        }

        // Get (if found): username and password
        // $dsn => username:password@protocol+hostspec/database
        if (($at = strrpos($dsn,'@')) !== false) {
            $str = substr($dsn, 0, $at);
            $dsn = substr($dsn, $at + 1);
            if (($pos = strpos($str, ':')) !== false) {
                $parsed['username'] = rawurldecode(substr($str, 0, $pos));
                $parsed['password'] = rawurldecode(substr($str, $pos + 1));
            } else {
                $parsed['username'] = rawurldecode($str);
            }
        }

        // Find protocol and hostspec

        // $dsn => proto(proto_opts)/database
        if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) {
            $proto       = $match[1];
            $proto_opts  = $match[2] ? $match[2] : false;
            $dsn         = $match[3];

        // $dsn => protocol+hostspec/database (old format)
        } else {
            if (strpos($dsn, '+') !== false) {
                list($proto, $dsn) = explode('+', $dsn, 2);
            }
            if (strpos($dsn, '/') !== false) {
                list($proto_opts, $dsn) = explode('/', $dsn, 2);
            } else {
                $proto_opts = $dsn;
                $dsn = null;
            }
        }

        // process the different protocol options
        $parsed['protocol'] = (!empty($proto)) ? $proto : 'tcp';
        $proto_opts = rawurldecode($proto_opts);
        if ($parsed['protocol'] == 'tcp') {
            if (strpos($proto_opts, ':') !== false) {
                list($parsed['hostspec'], $parsed['port']) = explode(':', $proto_opts);
            } else {
                $parsed['hostspec'] = $proto_opts;
            }
        } elseif ($parsed['protocol'] == 'unix') {
            $parsed['socket'] = $proto_opts;
        }

        // Get dabase if any
        // $dsn => database
        if ($dsn) {
            // /database
            if (($pos = strpos($dsn, '?')) === false) {
                $parsed['database'] = $dsn;
            // /database?param1=value1&param2=value2
            } else {
                $parsed['database'] = substr($dsn, 0, $pos);
                $dsn = substr($dsn, $pos + 1);
                if (strpos($dsn, '&') !== false) {
                    $opts = explode('&', $dsn);
                } else { // database?param1=value1
                    $opts = array($dsn);
                }
                foreach ($opts as $opt) {
                    list($key, $value) = explode('=', $opt);
                    if (!isset($parsed[$key])) {
                        // don't allow params overwrite
                        $parsed[$key] = rawurldecode($value);
                    }
                }
            }
        }

        return $parsed;
    }

    // }}}

-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Brian May]

>>>>> "Warren" == Warren W Gay VE3WWG <ve3wwg@NoSpam.cogeco.ca> writes:

    Warren> I like the neatness of this approach, but the danger is
    Warren> that you might do the "..." part on a row that might
    Warren> not exist. Though I suppose the Query_Type object could
    Warren> maintain enough state such that it can raise an exception
    Warren> if any "Values" were fetched from the row that is not there.
    Warren> Just a quibble: I'd prefer something like "No_Row(Q)" perhaps.

    Warren> An even neater loop is this, IMHO ;-)

    >> loop
    >> Fetch(Q);
    >> exit when No_More_Data(Q);
    >> 
    >> ...
    >> end loop;

How about

Valid_Row : Boolean;

loop
        Fetch(Q, Valid_Row);
        exit when not Valid_Row;
        ...
end loop;

Where Valid_Row is an out variable from Fetch.

This would avoid breaking any existing software, and make it obvious
that Valid_Row has to be checked.

I suspect this should work regardless of what the database is.


Other questions that might be significant, especially to some of my
proposed changes.

1. What happens if an unexpected exception occurs within the above
   loop, and it prevents continuing the loop. In fact, the code that
   handles the exception may not realize it occurred in middle of the
   fetch operation. Is there any way of recovering the database
   connection?

2. With Mysql what happens if I do this:

declare
        Q1 : Root_Query_Type'Class := New_Query(C);
        Q2 : Root_Query_Type'Class := New_Query(C);
begin
        Prepare(Q1, ...);
        Prepare(Q2, ...)

        Set_Fetch_Mode(Q1, Sequential_Fetch);
        Set_Fetch_Mode(Q2, Sequential_Fetch);

        Execute(Q1, C);
        Execute(Q2, C);

        loop
                Fetch(Q1);
                Fetch(Q2);
                ...
        end loop;
end;

Will this work, trigger an error, or will it kill the Mysql connection
as requests are intermixed?
-- 
Brian May <bam@snoopy.apana.org.au>

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Brian May wrote:
>>>>>>"Warren" == Warren W Gay VE3WWG <ve3wwg@NoSpam.cogeco.ca> writes:
> 
> 
>     Warren> I like the neatness of this approach, but the danger is
>     Warren> that you might do the "..." part on a row that might
>     Warren> not exist. Though I suppose the Query_Type object could
>     Warren> maintain enough state such that it can raise an exception
>     Warren> if any "Values" were fetched from the row that is not there.
>     Warren> Just a quibble: I'd prefer something like "No_Row(Q)" perhaps.
> 
>     Warren> An even neater loop is this, IMHO ;-)
> 
>     >> loop
>     >> Fetch(Q);
>     >> exit when No_More_Data(Q);
>     >> 
>     >> ...
>     >> end loop;
> 
> How about
> 
> Valid_Row : Boolean;
> 
> loop
>         Fetch(Q, Valid_Row);
>         exit when not Valid_Row;
>         ...
> end loop;
> 
> Where Valid_Row is an out variable from Fetch.
> 
> This would avoid breaking any existing software, and make it obvious
> that Valid_Row has to be checked.

I like that idea.

> Other questions that might be significant, especially to some of my
> proposed changes.
> 
> 1. What happens if an unexpected exception occurs within the above
>    loop, and it prevents continuing the loop. In fact, the code that
>    handles the exception may not realize it occurred in middle of the
>    fetch operation. Is there any way of recovering the database
>    connection?

An unexpected error would raise SQL_Error (or somesuch). So
if you expect to recover from it, you must obviously be willing
to catch the exception at some level.

> 2. With Mysql what happens if I do this:
> 
> declare
>         Q1 : Root_Query_Type'Class := New_Query(C);
>         Q2 : Root_Query_Type'Class := New_Query(C);
> begin
>         Prepare(Q1, ...);
>         Prepare(Q2, ...)
> 
>         Set_Fetch_Mode(Q1, Sequential_Fetch);
>         Set_Fetch_Mode(Q2, Sequential_Fetch);
> 
>         Execute(Q1, C);
>         Execute(Q2, C);
> 
>         loop
>                 Fetch(Q1);
>                 Fetch(Q2);
>                 ...
>         end loop;
> end;
> 
> Will this work, trigger an error, or will it kill the Mysql connection
> as requests are intermixed?

I haven't tried this, but I know that PostgreSQL is supposed
to support this, and I believe it will. Whether MySQL and Sybase
can, I am not sure.

In MySQL's case, what I believe happens is that any Q2 rows that
are skipped over to fetch rows for Q1, will just be loaded
into the C client memory for later use. But I have not taken
the time to test this.
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Thu, 16 Dec 2004 13:28:39 +0000 (UTC), Georg Bauhaus wrote:

> Dmitry A. Kazakov <mailbox@dmitry-kazakov.de> wrote:

>: As I said before, that is a very long term perspective. When distributed
>: systems finally come, they will eliminate the very notion of data base.
> 
> You mean a data flow system? ;-)

I don't believe much in them. How would you verify one?

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

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.

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

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

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

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

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

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

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Sat, 18 Dec 2004 15:36:15 -0500, Warren W. Gay VE3WWG wrote:

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

Then you should have no problems with having handles to them. Note that
this by no means would prevent users from using underlying objects directly
if they want.

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

You can get all that from a connection object

>   2. The connection (methods like connect, disconnect, am I connected)

Why should I connect, disconnect etc. From user's perspective, when I
create an object it should be usable, immediately. Introducing Connect, you
add complexity and various sources of errors. I can understand where
Connect comes from: Ada has no proper constructors with parameters for
limited objects. This is the reason for handles (they are non-limited). But
your objects are not limited, so there is no reason for creating
half-backed objects:

   type Root_Connection_Type (<>) is abstract ...;
      -- Always properly constructed
   function Connect (...) return Root_Connection_Type is abstract;
      -- The only way to create it is this constructor
private
   type Root_Connection_Type is abstract ...;

>   3. The query

Because Connection has no methods except for a construction function, it
can be merged with query

>   4. Blobs

Yes. This is a different thing. One will probably need a common base for
all types on Ada side with can be stored in DB. Blobs is just on of them.
Presumably there also should be DB_Integer, DB_String, DB_Bounded_String
etc.

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

Oh, that's no problem in my view:

   type Database_Type is abstract ...

   function Connect (...)
      return Database_Type is abstract ;
      -- Creates new connection (and new query)

   function Shared_Session (DB : Database_Type)
      return Database_Type is abstract;
      -- Creates new query, connection is shared

   function New_Session (DB : Database_Type)
      return Database_Type is abstract;
      -- Creates new connection and new query for it

> I regret not having
> separated the environment from the connection, because
> they both serve different purposes and state also.

No reason for regrets, I found it rather refreshing after ugly ODBC! (:-))

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

No matter, even if the DB exists, there is still only one valid way to
encode, say, floating point number or string literal. It does not help to
be able to send a garbage only to get SQL_Error in return.

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

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Dmitry A. Kazakov wrote:
> On Sat, 18 Dec 2004 15:36:15 -0500, Warren W. Gay VE3WWG wrote:
>>Dmitry A. Kazakov wrote:
>>>On Wed, 15 Dec 2004 21:53:11 -0500, Warren W. Gay VE3WWG wrote:
>>>>Dmitry A. Kazakov wrote:
...
>>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.)
> 
> You can get all that from a connection object

Again, they have different data items, states and methods. Again,
you have a 1 to many relationship between environment and
connections!

>>  2. The connection (methods like connect, disconnect, am I connected)
> 
> Why should I connect, disconnect etc. From user's perspective, when I
> create an object it should be usable, immediately. 

 From a user's perspective, you want "control". You are espousing
a Microsoft approach where you take control away from the caller,
and instead tie connection with construction. This is not very
flexible.

> Introducing Connect, you
> add complexity and various sources of errors. I can understand where
> Connect comes from: Ada has no proper constructors with parameters for
> limited objects. This is the reason for handles (they are non-limited). But
> your objects are not limited, so there is no reason for creating
> half-backed objects:

Look, it has nothing to do with Ada or "half baked" objects ("baked"
is what I think you meant). It is all about control. If we took your
view, you would never allow a Rewind operation on a file. You would
have to destruct it and recreate it.

An object goes through states, sometimes many of them. I see nothing
half baked about being connected or not. What IS half baked about it
is that it combines Environment & connection! By putting them in
one object, I multiply the number of states and methods for a
combined object.

>    type Root_Connection_Type (<>) is abstract ...;
>       -- Always properly constructed
>    function Connect (...) return Root_Connection_Type is abstract;
>       -- The only way to create it is this constructor

This is one approach, but there are times when it is much better
(efficiency is one way it can be "better") to manage it by state,
rather than tie it to object construction. For example, in a
web server you may want to manage a pool of connections. Yes,
you can manage it by creation/destruction, but this is more
expensive than connecting/disconnecting a database.

>>  3. The query
> 
> Because Connection has no methods except for a construction function, it
> can be merged with query

There is a 1 to many relationship between connections and queries!

>>  4. Blobs
> 
> Yes. This is a different thing. One will probably need a common base for
> all types on Ada side with can be stored in DB. Blobs is just on of them.
> Presumably there also should be DB_Integer, DB_String, DB_Bounded_String
> etc.

Naw. I prefer to use Ada streams for Blob I/O. That way I don't
have to force the user to use types that I dream up.

>>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.
> 
> Oh, that's no problem in my view:
> 
>    type Database_Type is abstract ...
> 
>    function Connect (...)
>       return Database_Type is abstract ;
>       -- Creates new connection (and new query)
> 
>    function Shared_Session (DB : Database_Type)
>       return Database_Type is abstract;
>       -- Creates new query, connection is shared
> 
>    function New_Session (DB : Database_Type)
>       return Database_Type is abstract;
>       -- Creates new connection and new query for it

I'm sorry, but yuk.

>>I regret not having
>>separated the environment from the connection, because
>>they both serve different purposes and state also.
> 
> No reason for regrets, I found it rather refreshing after ugly ODBC! (:-))

:)

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

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Sat, 18 Dec 2004 19:53:51 -0500, Warren W. Gay VE3WWG wrote:

> Dmitry A. Kazakov wrote:
>> On Sat, 18 Dec 2004 15:36:15 -0500, Warren W. Gay VE3WWG wrote:
>>>Dmitry A. Kazakov wrote:
>>>>On Wed, 15 Dec 2004 21:53:11 -0500, Warren W. Gay VE3WWG wrote:
>>>>>Dmitry A. Kazakov wrote:
> ...
>>>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.)
>> 
>> You can get all that from a connection object
> 
> Again, they have different data items, states and methods. Again,
> you have a 1 to many relationship between environment and
> connections!

These connections are between implementation objects. There is no reason to
expose these details to the end user.

>>>  2. The connection (methods like connect, disconnect, am I connected)
>> 
>> Why should I connect, disconnect etc. From user's perspective, when I
>> create an object it should be usable, immediately. 
> 
>  From a user's perspective, you want "control". You are espousing
> a Microsoft approach where you take control away from the caller,
> and instead tie connection with construction. This is not very
> flexible.

It is not their approach. At least in MFC. Either because of C++ object
construction model deficiency, or just in order to keep users in awe, but
the result is that anything has Init().

As a user I don't want to control anything. I just want to use it. Only
when it first works, and there are problems with how it works, then maybe,
but unlikely, I would attune it a bit.

The major goal of any bindings is to maintain complexity.

>> Introducing Connect, you
>> add complexity and various sources of errors. I can understand where
>> Connect comes from: Ada has no proper constructors with parameters for
>> limited objects. This is the reason for handles (they are non-limited). But
>> your objects are not limited, so there is no reason for creating
>> half-backed objects:
> 
> Look, it has nothing to do with Ada or "half baked" objects ("baked"
> is what I think you meant). It is all about control. If we took your
> view, you would never allow a Rewind operation on a file.

Exactly, because not every device would allow it. It would be a bad design
to allow rewind that throws Use_Error. A proper design would be to derive a
class of files which can be rewound from the base class of files.

> You would have to destruct it and recreate it.

Yes, because the file was a socket!

> An object goes through states, sometimes many of them. I see nothing
> half baked about being connected or not.

Do not forget separation of implementation and interface. There are logical
and physical states, there are interface and implementation objects. They
need *not* to be same. You are trying to throw everything in one cauldron.

> What IS half baked about it
> is that it combines Environment & connection! By putting them in
> one object, I multiply the number of states and methods for a
> combined object.

These states are of no user interest. I do not need an unconnected
connection. Really, it is like inedible food.

>>    type Root_Connection_Type (<>) is abstract ...;
>>       -- Always properly constructed
>>    function Connect (...) return Root_Connection_Type is abstract;
>>       -- The only way to create it is this constructor
> 
> This is one approach, but there are times when it is much better
> (efficiency is one way it can be "better") to manage it by state,
> rather than tie it to object construction. For example, in a
> web server you may want to manage a pool of connections. Yes,
> you can manage it by creation/destruction, but this is more
> expensive than connecting/disconnecting a database.

If you compare the time required to connect with the time required to
initialize an interface object you will find that the latter is negligible. 

If you mean a physical communication object, then again it is not user's
responsibility. Normally, it is OS which maintains a pool file descriptors.
Because they are OS's resources. It would be wrong to require from an Ada
application to interfere there.

Let that be an issue, then most probably it will highly depend on the
target DB type. So the user will have no opportunity to influence it
anyway. It is the responsibility of bindings to internally provide pools of
objects expensive to create.

>>>  3. The query
>> 
>> Because Connection has no methods except for a construction function, it
>> can be merged with query
> 
> There is a 1 to many relationship between connections and queries!

In the interface it is 1 to 1. Internally it might be any, why should I
care?

>>>  4. Blobs
>> 
>> Yes. This is a different thing. One will probably need a common base for
>> all types on Ada side with can be stored in DB. Blobs is just on of them.
>> Presumably there also should be DB_Integer, DB_String, DB_Bounded_String
>> etc.
> 
> Naw. I prefer to use Ada streams for Blob I/O.

Alas, but they are not portable.

> That way I don't have to force the user to use types that I dream up.

[ Ada 2005 will finally have multiple inheritance restricted to interfaces.
That will solve the problem without ugly generics. ]

Look, you describe the interface of a DB type and user have to implement
it. Note that it is not you, who forces the user, but a very real thing,
the DB. There *must* be a mapping between DB and user types. Your "dreamed
up" types just embody that mapping.

User-defined type conversions might do it very easy and elegant, but that
is another story.

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

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Dmitry A. Kazakov wrote:
...
>>>> 1. Environment (userid, passwords, ports, address, hostname etc.)
>>>
>>>You can get all that from a connection object
>>
>>Again, they have different data items, states and methods. Again,
>>you have a 1 to many relationship between environment and
>>connections!
> 
> These connections are between implementation objects. There is no reason to
> expose these details to the end user.

There is, but I give up. You obviously don't like dividing
up a problem into reasonable objects. You'd rather have
it all in one large mass, which I cannot agree with. Even
M$ wouldn't do this for MFC, as you've used for example,
later on.

We're just going to disagree on this point, and I see no
reason to flog this dead horse any longer. ;-)

>>>> 2. The connection (methods like connect, disconnect, am I connected)
>>>
>>>Why should I connect, disconnect etc. From user's perspective, when I
>>>create an object it should be usable, immediately. 
>>
>> From a user's perspective, you want "control". You are espousing
>>a Microsoft approach where you take control away from the caller,
>>and instead tie connection with construction. This is not very
>>flexible.
> 
> It is not their approach. At least in MFC. Either because of C++ object
> construction model deficiency, or just in order to keep users in awe, but
> the result is that anything has Init().

Actually, MFC is one of the things that M$ has done fairly well
in my books. I was referring more to the general M$ philosophy
that Windoze uses (taking away control). Their API sometimes
does this as well IMO, but I am too lazy to cite a specific
example.

> As a user I don't want to control anything. I just want to use it. 

Heh, heh, what's the difference? If I _use_ a car, I _am_
controlling it. If I _use_ a can opener, I sure hope I am
in control of it! If we can't agree on that, then let's
just disagree once again.

> Only
> when it first works, and there are problems with how it works, then maybe,
> but unlikely, I would attune it a bit.
> 
> The major goal of any bindings is to maintain complexity.

Correction: _a_ major goal is... but it is by no means the only
one. It is only one of several important goals, many of them
in conflict requiring a balanced compromise.

>>>Introducing Connect, you
>>>add complexity and various sources of errors. I can understand where
>>>Connect comes from: Ada has no proper constructors with parameters for
>>>limited objects. This is the reason for handles (they are non-limited). But
>>>your objects are not limited, so there is no reason for creating
>>>half-backed objects:
>>
>>Look, it has nothing to do with Ada or "half baked" objects ("baked"
>>is what I think you meant). It is all about control. If we took your
>>view, you would never allow a Rewind operation on a file.
> 
> Exactly, because not every device would allow it. It would be a bad design
> to allow rewind that throws Use_Error. A proper design would be to derive a
> class of files which can be rewound from the base class of files.

So Ada's Rewind operation is faulty in your view? C's fseek()
is faulty in your view? C'mon now. Really.

We're all big boys here, and can figure out when Rewind is
proper and when its not. I support Ada and "its ways", but
let's not expect a "rubber room" in which we can never do
something harmful. If that ever happens, then I think it is
probably safe to say that we've wound up at the Asylum.

>>You would have to destruct it and recreate it.
> 
> Yes, because the file was a socket!

Whether it is a socket or a named pipe, anonymous pipe or microkernel
message queue, doesn't matter to the user. That is a physical
detail. This detail however, does sometimes poke through to the
user, for example, when specifying say the URI for connecting
to the database, but otherwise how the connection is made is
imaterial (the "how" does put requirements on how the connection
is specified, but otherwise the details are not important).

But back to the orignal point, why this means that I have
to do a construct/destruct just to connect or not?
Here's another reason why this requirement is not
a good thing (TM):

Since (at present) the Connection_Type object includes the
connection environment as well, every time you construct the
object new, you also have to reconfigure/specify all of that
connection information. Now if you add parsing of the URI
(instead), it starts to add some significant overhead
(admittedly not a lot, compared to the time required to
establish a connection - but still this is wasted work being
repeated each time). From a design perspective, this
is bad, because you unnecessarily repeat processing what
need only be done once (many applications only ever deal
with one database/schema). This is in fact one argument in
favour of the environment object, but let's not flog that
horse..

>>An object goes through states, sometimes many of them. I see nothing
>>half baked about being connected or not.
> 
> Do not forget separation of implementation and interface. There are logical
> and physical states, there are interface and implementation objects. They
> need *not* to be same. You are trying to throw everything in one cauldron.

No, I disagree. The user need not understand all of the
specifics whether they are physical, or Sybase or MySQL
specific API issues (APQ hides this ugliness). What the
APQ API _does_ support is the simple idea that the user's
object is "connected" or "not connected". Where is the
cauldron there?

If there is any soup, it _is_ in the way that APQ currently
includes "environment", when in fact it actually belongs
in its own user object (but again, I digress).

>>What IS half baked about it
>>is that it combines Environment & connection! By putting them in
>>one object, I multiply the number of states and methods for a
>>combined object.
> 
> These states are of no user interest. I do not need an unconnected
> connection. Really, it is like inedible food.

If I am using the Booch components (for example), what you
are suggesting is that I should not have a Map object, if it
is empty ("inedible food").  After all, according to your
logic, what can you do with an empty Map object?
You would only have a Map object created, when it
contains at least one object. But writing code to work this
way is more work, and pointless without a special requirement
to do so.

There is nothing wrong with objects having defined states. Just
because an object is empty or disconnected, is not an argument
by itself against the existence of the object as you are
insisting it is. There might be other requirements that make
this a good idea, but this argument by itself does not decide
the case IMHO.

> If you compare the time required to connect with the time required to
> initialize an interface object you will find that the latter is negligible. 

Time and overhead is not the only factor. At the point where
you want to connect to the database, you might not have
access to the very parameters that you need to supply (this
often happens in GUI callbacks).

An unconnected object can preconfigure all of the parameters
necessary for a connect operation to be done at some other
point in time. By your linking of these two "actions", you
now force the user to drag around all of this extra baggage,
in order to affect a successful "connect". In effect, you've
made the API more difficult for some users.

> If you mean a physical communication object, then again it is not user's
> responsibility. Normally, it is OS which maintains a pool file descriptors.
> Because they are OS's resources. It would be wrong to require from an Ada
> application to interfere there.
> 
> Let that be an issue, then most probably it will highly depend on the
> target DB type. So the user will have no opportunity to influence it
> anyway. It is the responsibility of bindings to internally provide pools of
> objects expensive to create.

Connections are usually expensive. But APQ doesn't try to mitigate
that, since the control over _when_ and the _frequency_ of the
connecting operations are under the control of the calling program
(as it should be). Users of APQ don't want me deciding this for
them.

>>>> 3. The query
>>>
>>>Because Connection has no methods except for a construction function, it
>>>can be merged with query
>>
>>There is a 1 to many relationship between connections and queries!
> 
> In the interface it is 1 to 1. Internally it might be any, why should I
> care?

Not everything is a hammer. In life we work with many objects
as users of objects. OO programming borrows from some of our
life experiences.

>>>> 4. Blobs
>>>
>>>Yes. This is a different thing. One will probably need a common base for
>>>all types on Ada side with can be stored in DB. Blobs is just on of them.
>>>Presumably there also should be DB_Integer, DB_String, DB_Bounded_String
>>>etc.
>>
>>Naw. I prefer to use Ada streams for Blob I/O.
> 
> Alas, but they are not portable.

This is indeed a weakness. However, it can be _portable_ if the
user uses it that way (for example if he chooses only to write
streams of bytes or Stream_Elements). But I'll agree, this has
its limitations.

>>That way I don't have to force the user to use types that I dream up.
> 
> [ Ada 2005 will finally have multiple inheritance restricted to interfaces.
> That will solve the problem without ugly generics. ]

I don't see how MI is going to fix this problem, but no matter.

> Look, you describe the interface of a DB type and user have to implement
> it. Note that it is not you, who forces the user, but a very real thing,
> the DB. There *must* be a mapping between DB and user types. Your "dreamed
> up" types just embody that mapping.

You've missed the point. In Ada I have the choice between
using something like Integer or a type that I define:

   type My_Int is range 0..5;

Your approach is to force the user to derive their type from
something that APQ provides (or something standard Ada
provides). For example, APQ could say that if you write an
integer out to a blob that you have to use APQ.Blob_Integer or
some such.

   type My_Int is new APQ.Blob_Integer range 0..5; -- eg

But if I were the programmer, I would not like
this much, because I'd want to use My_Int instead (maybe it
is defined by a 3rd party package).

> User-defined type conversions might do it very easy and elegant, but that
> is another story.

I hate unnecessary use of "conversions". They are a great source
of error. This is why APQ goes out of its way to provide generic
procedures so that programs don't have to be written with
conversions all over the place. It reminds me too much of casting
C types all over the place -- it strips the compiler of much of
its type safety.
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg

Re: Ada DB bindings and APQ

[Dmitry A. Kazakov]

On Mon, 20 Dec 2004 00:33:31 -0500, Warren W. Gay VE3WWG wrote:

> Dmitry A. Kazakov wrote:

>>>>Introducing Connect, you
>>>>add complexity and various sources of errors. I can understand where
>>>>Connect comes from: Ada has no proper constructors with parameters for
>>>>limited objects. This is the reason for handles (they are non-limited). But
>>>>your objects are not limited, so there is no reason for creating
>>>>half-backed objects:
>>>
>>>Look, it has nothing to do with Ada or "half baked" objects ("baked"
>>>is what I think you meant). It is all about control. If we took your
>>>view, you would never allow a Rewind operation on a file.
>> 
>> Exactly, because not every device would allow it. It would be a bad design
>> to allow rewind that throws Use_Error. A proper design would be to derive a
>> class of files which can be rewound from the base class of files.
> 
> So Ada's Rewind operation is faulty in your view?

Ada 83 didn't have that elaborated type inheritance of Ada 95. I'm almost
sure that a possibility to have several File_Types was considered that
time.

> C's fseek() is faulty in your view? C'mon now. Really.

Yes, in my view In_File, Out_File should be not modes but [sub]types of
File_Type. After all we have that for in/out/inout parameters: Where is any
difference? Logically, there is no one.

BTW, according to your point of view, shouldn't File_Type and
File_Operation be two independent types? Open is called on File_Type.
Read/Write are on File_Operation. Disagree? (:-))

> We're all big boys here, and can figure out when Rewind is
> proper and when its not. I support Ada and "its ways", but
> let's not expect a "rubber room" in which we can never do
> something harmful. If that ever happens, then I think it is
> probably safe to say that we've wound up at the Asylum.

The Ada's way is to allow harmful things only when prohibiting them would
be too expensive. I see nothing expensive in eliminating a type. (:-))

>>>You would have to destruct it and recreate it.
>> 
>> Yes, because the file was a socket!
> 
> Whether it is a socket or a named pipe, anonymous pipe or microkernel
> message queue, doesn't matter to the user. That is a physical
> detail.

What matters is the logical view. The same physical thing may support
different views. In OO terms: the same thing may implement many interfaces.
But it is a very bad idea to implement interfaces partially. Again, in OO
terms it is called LSP violation. If we know for sure that we cannot rewind
a file, it is awful to say, well, we can, but the result is an error.

> This detail however, does sometimes poke through to the
> user, for example, when specifying say the URI for connecting
> to the database, but otherwise how the connection is made is
> imaterial (the "how" does put requirements on how the connection
> is specified, but otherwise the details are not important).
> 
> But back to the orignal point, why this means that I have
> to do a construct/destruct just to connect or not?

Because the object is unusable when not connected.

> Here's another reason why this requirement is not
> a good thing (TM):
> 
> Since (at present) the Connection_Type object includes the
> connection environment as well, every time you construct the
> object new, you also have to reconfigure/specify all of that
> connection information. Now if you add parsing of the URI
> (instead), it starts to add some significant overhead
> (admittedly not a lot, compared to the time required to
> establish a connection - but still this is wasted work being
> repeated each time). From a design perspective, this
> is bad, because you unnecessarily repeat processing what
> need only be done once (many applications only ever deal
> with one database/schema). This is in fact one argument in
> favour of the environment object, but let's not flog that
> horse..

You are talking about *re*connecting. It is another thing. Provide a method
for that, if this option is essential.

>>>An object goes through states, sometimes many of them. I see nothing
>>>half baked about being connected or not.
>> 
>> Do not forget separation of implementation and interface. There are logical
>> and physical states, there are interface and implementation objects. They
>> need *not* to be same. You are trying to throw everything in one cauldron.
> 
> No, I disagree. The user need not understand all of the
> specifics whether they are physical, or Sybase or MySQL
> specific API issues (APQ hides this ugliness). What the
> APQ API _does_ support is the simple idea that the user's
> object is "connected" or "not connected". Where is the
> cauldron there?

Because "connection" and "query" are implementation details. The only real
thing is a DB. Consider a DB which does not use server/client model.
Consider a DB fully integrated into OS, with access authentication based on
application rights. You might be always connected to it.

>>>What IS half baked about it
>>>is that it combines Environment & connection! By putting them in
>>>one object, I multiply the number of states and methods for a
>>>combined object.
>> 
>> These states are of no user interest. I do not need an unconnected
>> connection. Really, it is like inedible food.
> 
> If I am using the Booch components (for example), what you
> are suggesting is that I should not have a Map object, if it
> is empty ("inedible food").  After all, according to your
> logic, what can you do with an empty Map object?
> You would only have a Map object created, when it
> contains at least one object. But writing code to work this
> way is more work, and pointless without a special requirement
> to do so.

This is a good example. Thank you.

The decision is not based on object states, which are internal and of no
interest. [ BTW the best possible design is stateless objects (i.e. pure
undistinguishable values, a la functional programming) ]

The decision is based on the public interface: which methods are
applicable. If we consider maps, then we will see that everything what can
be done with a non-empty map can be also done with an empty one.
Mathematically: an empty pair-set is a set. In OO terms: LSP holds. This is
the reason WHY no empty maps are needed as a separate [sub]type.

Now consider one particular method:

   function Size (X : Map) return Natural;

Should we change Natural to Positive above, for whatever reason, THEN Size
will cease to be a method of Map and *only* then we will have to have
General_Map with no Size defined and its specialization Non_Empty_Map with
Size.
 
> There is nothing wrong with objects having defined states. Just
> because an object is empty or disconnected, is not an argument
> by itself against the existence of the object as you are
> insisting it is. There might be other requirements that make
> this a good idea, but this argument by itself does not decide
> the case IMHO.

See above. The most important argument *for* having unusable objects are 

1. A language deficiency to provide constructors with parameters.

2. Questionable design of containers, which is closely related to 1.
Usually, if you want to have a container of objects you have to provide a
kind of Null_Object to initialize unused slots. That Null_Object could be
that unconnected one. There might be better ways than though.

>> If you compare the time required to connect with the time required to
>> initialize an interface object you will find that the latter is negligible. 
> 
> Time and overhead is not the only factor. At the point where
> you want to connect to the database, you might not have
> access to the very parameters that you need to supply (this
> often happens in GUI callbacks).

It is irrelevant. If you have to pass construction parameters then you have
to do it. There is no reason for storing them into the object.

> An unconnected object can preconfigure all of the parameters
> necessary for a connect operation to be done at some other
> point in time. By your linking of these two "actions", you
> now force the user to drag around all of this extra baggage,
> in order to affect a successful "connect". In effect, you've
> made the API more difficult for some users.

Quite the opposite. You have packed connection parameters where they do not
belong to. It is like to pack file name, path, OS version, time stamp etc
into File_Type!

> OO programming borrows from some of our life experiences.

Philosophy! He, he. BTW, as for that infamous OO-religion: God programmed
the world in the OO way. Well, I am an atheist!

>>>That way I don't have to force the user to use types that I dream up.
>> 
>> [ Ada 2005 will finally have multiple inheritance restricted to interfaces.
>> That will solve the problem without ugly generics. ]
> 
> I don't see how MI is going to fix this problem, but no matter.
> 
>> Look, you describe the interface of a DB type and user have to implement
>> it. Note that it is not you, who forces the user, but a very real thing,
>> the DB. There *must* be a mapping between DB and user types. Your "dreamed
>> up" types just embody that mapping.
> 
> You've missed the point. In Ada I have the choice between
> using something like Integer or a type that I define:
> 
>    type My_Int is range 0..5;
> 
> Your approach is to force the user to derive their type from
> something that APQ provides (or something standard Ada
> provides).

Not to derive, but to implement.

> For example, APQ could say that if you write an
> integer out to a blob that you have to use APQ.Blob_Integer or
> some such.
> 
>    type My_Int is new APQ.Blob_Integer range 0..5; -- eg
> 
> But if I were the programmer, I would not like
> this much, because I'd want to use My_Int instead (maybe it
> is defined by a 3rd party package).

But you cannot use My_Int because, for example, the DB might support no
safe conversion to/from it. Again, DB is the real thing here! Remember my
classification? It is B-bindings. So My_Int is imaginary. If you want to
fix My_Int, then that should be A-bindings. Though it would be difficult to
do, because Ada does not support classes for all types. We could imagine in
Ada 2100 (:-)), something like dispatching user-defined attributes. Then
our children could write:

declare
   I : My_Int := ...
begin
   Append (Q, I'APQ_Value);

BTW this requires multiple dispatch, because Append dispatches on Q, and so
determines the expected type of I'APQ_Value, which in turn dispatches on
both the result (the type expected by DB) and the type of I (the type
provided by the user).

>> User-defined type conversions might do it very easy and elegant, but that
>> is another story.
> 
> I hate unnecessary use of "conversions". They are a great source
> of error.

It is a myth caused by automatic conversions of PL/1 and C. There is
nothing wrong with conversions. After all when you derive one type from
another you implicitly define conversions. Inheritance and conversions are
equivalent.

> This is why APQ goes out of its way to provide generic
> procedures so that programs don't have to be written with
> conversions all over the place. It reminds me too much of casting
> C types all over the place -- it strips the compiler of much of
> its type safety.

Right, the conversions have to be applied as necessary. Explicit
conversions are as bad as ones automatically generated at
compiler's/language designer's will. The compiler should invent nothing,
but being told it should be intelligent enough to understand it at one
dash. If I say in some scope that Unbounded_String is convertible to String
(=Unbounded_String is an in-subtype of String), then within this scope
everywhere a String is expected a value of Unbounded_String should be
acceptable as well. It is stone-safe.

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

Re: Ada DB bindings and APQ

[Warren W. Gay VE3WWG]

Dmitry A. Kazakov wrote:
...big snip...

There is enough there for another year of debating, but
I have neither the time nor the energy.  AFAICS, you
actually argued against yourself on some points, but this
has become much too circular for my taste.

We're just going to have to disagree on many points. ;-)
-- 
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg