From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00 autolearn=ham autolearn_force=no version=3.4.4 X-Google-Thread: 103376,ee0dc912649d50d4 X-Google-Attributes: gid103376,public X-Google-Language: ENGLISH,ASCII-7-bit Path: g2news1.google.com!news4.google.com!news.glorb.com!meganewsservers.com!feeder2.on.meganewsservers.com!feed.cgocable.net!read1.cgocable.net.POSTED!53ab2750!not-for-mail From: "Warren W. Gay VE3WWG" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.lang.ada Subject: Re: Ada DB bindings and APQ References: <1km3c584awura$.y7djkir1ozya$.dlg@40tude.net> In-Reply-To: <1km3c584awura$.y7djkir1ozya$.dlg@40tude.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Message-ID: Date: Tue, 14 Dec 2004 02:25:22 -0500 NNTP-Posting-Host: 24.150.168.167 X-Complaints-To: abuse@cogeco.ca X-Trace: read1.cgocable.net 1103009125 24.150.168.167 (Tue, 14 Dec 2004 02:25:25 EST) NNTP-Posting-Date: Tue, 14 Dec 2004 02:25:25 EST Organization: Cogeco Cable Xref: g2news1.google.com comp.lang.ada:6932 Date: 2004-12-14T02:25:22-05:00 List-Id: 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 _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