Ada DB bindings and APQ

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

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