* Ada DB bindings and APQ @ 2004-12-12 17:42 Dmitry A. Kazakov 2004-12-14 7:25 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-12 17:42 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-12 17:42 Ada DB bindings and APQ Dmitry A. Kazakov @ 2004-12-14 7:25 ` Warren W. Gay VE3WWG 2004-12-14 17:37 ` Dmitry A. Kazakov ` (2 more replies) 0 siblings, 3 replies; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-14 7:25 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 7:25 ` Warren W. Gay VE3WWG @ 2004-12-14 17:37 ` Dmitry A. Kazakov 2004-12-14 18:29 ` Georg Bauhaus 2004-12-15 4:05 ` Warren W. Gay VE3WWG 2004-12-14 22:40 ` Brian May 2004-12-15 10:48 ` Brian May 2 siblings, 2 replies; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-14 17:37 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 17:37 ` Dmitry A. Kazakov @ 2004-12-14 18:29 ` Georg Bauhaus 2004-12-14 19:45 ` Dmitry A. Kazakov 2004-12-15 4:05 ` Warren W. Gay VE3WWG 1 sibling, 1 reply; 33+ messages in thread From: Georg Bauhaus @ 2004-12-14 18:29 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 18:29 ` Georg Bauhaus @ 2004-12-14 19:45 ` Dmitry A. Kazakov 2004-12-14 21:06 ` Georg Bauhaus 2004-12-14 23:26 ` Brian May 0 siblings, 2 replies; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-14 19:45 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 19:45 ` Dmitry A. Kazakov @ 2004-12-14 21:06 ` Georg Bauhaus 2004-12-15 8:19 ` Ole-Hjalmar Kristensen 2004-12-15 17:20 ` Dmitry A. Kazakov 2004-12-14 23:26 ` Brian May 1 sibling, 2 replies; 33+ messages in thread From: Georg Bauhaus @ 2004-12-14 21:06 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 21:06 ` Georg Bauhaus @ 2004-12-15 8:19 ` Ole-Hjalmar Kristensen 2004-12-15 17:20 ` Dmitry A. Kazakov 1 sibling, 0 replies; 33+ messages in thread From: Ole-Hjalmar Kristensen @ 2004-12-15 8:19 UTC (permalink / raw) >>>>> "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. ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 21:06 ` Georg Bauhaus 2004-12-15 8:19 ` Ole-Hjalmar Kristensen @ 2004-12-15 17:20 ` Dmitry A. Kazakov 2004-12-16 13:28 ` Georg Bauhaus 1 sibling, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-15 17:20 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 17:20 ` Dmitry A. Kazakov @ 2004-12-16 13:28 ` Georg Bauhaus 2004-12-17 13:23 ` Dmitry A. Kazakov 0 siblings, 1 reply; 33+ messages in thread From: Georg Bauhaus @ 2004-12-16 13:28 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-16 13:28 ` Georg Bauhaus @ 2004-12-17 13:23 ` Dmitry A. Kazakov 0 siblings, 0 replies; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-17 13:23 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 19:45 ` Dmitry A. Kazakov 2004-12-14 21:06 ` Georg Bauhaus @ 2004-12-14 23:26 ` Brian May 2004-12-15 17:43 ` Dmitry A. Kazakov 1 sibling, 1 reply; 33+ messages in thread From: Brian May @ 2004-12-14 23:26 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 23:26 ` Brian May @ 2004-12-15 17:43 ` Dmitry A. Kazakov 2004-12-15 21:54 ` Brian May 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-15 17:43 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 17:43 ` Dmitry A. Kazakov @ 2004-12-15 21:54 ` Brian May 0 siblings, 0 replies; 33+ messages in thread From: Brian May @ 2004-12-15 21:54 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 17:37 ` Dmitry A. Kazakov 2004-12-14 18:29 ` Georg Bauhaus @ 2004-12-15 4:05 ` Warren W. Gay VE3WWG 2004-12-15 18:26 ` Dmitry A. Kazakov 1 sibling, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-15 4:05 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 4:05 ` Warren W. Gay VE3WWG @ 2004-12-15 18:26 ` Dmitry A. Kazakov 2004-12-16 2:53 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-15 18:26 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 18:26 ` Dmitry A. Kazakov @ 2004-12-16 2:53 ` Warren W. Gay VE3WWG 2004-12-18 16:43 ` Dmitry A. Kazakov 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-16 2:53 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-16 2:53 ` Warren W. Gay VE3WWG @ 2004-12-18 16:43 ` Dmitry A. Kazakov 2004-12-18 20:36 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-18 16:43 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-18 16:43 ` Dmitry A. Kazakov @ 2004-12-18 20:36 ` Warren W. Gay VE3WWG 2004-12-18 22:21 ` Dmitry A. Kazakov 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-18 20:36 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-18 20:36 ` Warren W. Gay VE3WWG @ 2004-12-18 22:21 ` Dmitry A. Kazakov 2004-12-19 0:53 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-18 22:21 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-18 22:21 ` Dmitry A. Kazakov @ 2004-12-19 0:53 ` Warren W. Gay VE3WWG 2004-12-19 12:21 ` Dmitry A. Kazakov 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-19 0:53 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-19 0:53 ` Warren W. Gay VE3WWG @ 2004-12-19 12:21 ` Dmitry A. Kazakov 2004-12-20 5:33 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-19 12:21 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-19 12:21 ` Dmitry A. Kazakov @ 2004-12-20 5:33 ` Warren W. Gay VE3WWG 2004-12-20 20:01 ` Dmitry A. Kazakov 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-20 5:33 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-20 5:33 ` Warren W. Gay VE3WWG @ 2004-12-20 20:01 ` Dmitry A. Kazakov 2004-12-20 20:54 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Dmitry A. Kazakov @ 2004-12-20 20:01 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-20 20:01 ` Dmitry A. Kazakov @ 2004-12-20 20:54 ` Warren W. Gay VE3WWG 0 siblings, 0 replies; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-20 20:54 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 7:25 ` Warren W. Gay VE3WWG 2004-12-14 17:37 ` Dmitry A. Kazakov @ 2004-12-14 22:40 ` Brian May 2004-12-15 3:23 ` Warren W. Gay VE3WWG 2004-12-15 10:48 ` Brian May 2 siblings, 1 reply; 33+ messages in thread From: Brian May @ 2004-12-14 22:40 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 22:40 ` Brian May @ 2004-12-15 3:23 ` Warren W. Gay VE3WWG 2004-12-15 15:01 ` Georg Bauhaus 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-15 3:23 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 3:23 ` Warren W. Gay VE3WWG @ 2004-12-15 15:01 ` Georg Bauhaus 2004-12-17 4:31 ` Brian May 0 siblings, 1 reply; 33+ messages in thread From: Georg Bauhaus @ 2004-12-15 15:01 UTC (permalink / raw) 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 ')' ... ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 15:01 ` Georg Bauhaus @ 2004-12-17 4:31 ` Brian May 0 siblings, 0 replies; 33+ messages in thread From: Brian May @ 2004-12-17 4:31 UTC (permalink / raw) >>>>> "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¶m2=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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-14 7:25 ` Warren W. Gay VE3WWG 2004-12-14 17:37 ` Dmitry A. Kazakov 2004-12-14 22:40 ` Brian May @ 2004-12-15 10:48 ` Brian May 2004-12-16 1:40 ` Brian May 2 siblings, 1 reply; 33+ messages in thread From: Brian May @ 2004-12-15 10:48 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-15 10:48 ` Brian May @ 2004-12-16 1:40 ` Brian May 2004-12-16 3:10 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Brian May @ 2004-12-16 1:40 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-16 1:40 ` Brian May @ 2004-12-16 3:10 ` Warren W. Gay VE3WWG 2004-12-17 4:55 ` Brian May 0 siblings, 1 reply; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-16 3:10 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-16 3:10 ` Warren W. Gay VE3WWG @ 2004-12-17 4:55 ` Brian May 2004-12-17 11:13 ` Warren W. Gay VE3WWG 0 siblings, 1 reply; 33+ messages in thread From: Brian May @ 2004-12-17 4:55 UTC (permalink / raw) >>>>> "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> ^ permalink raw reply [flat|nested] 33+ messages in thread
* Re: Ada DB bindings and APQ 2004-12-17 4:55 ` Brian May @ 2004-12-17 11:13 ` Warren W. Gay VE3WWG 0 siblings, 0 replies; 33+ messages in thread From: Warren W. Gay VE3WWG @ 2004-12-17 11:13 UTC (permalink / raw) 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 ^ permalink raw reply [flat|nested] 33+ messages in thread
end of thread, other threads:[~2004-12-20 20:54 UTC | newest] Thread overview: 33+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2004-12-12 17:42 Ada DB bindings and APQ Dmitry A. Kazakov 2004-12-14 7:25 ` Warren W. Gay VE3WWG 2004-12-14 17:37 ` Dmitry A. Kazakov 2004-12-14 18:29 ` Georg Bauhaus 2004-12-14 19:45 ` Dmitry A. Kazakov 2004-12-14 21:06 ` Georg Bauhaus 2004-12-15 8:19 ` Ole-Hjalmar Kristensen 2004-12-15 17:20 ` Dmitry A. Kazakov 2004-12-16 13:28 ` Georg Bauhaus 2004-12-17 13:23 ` Dmitry A. Kazakov 2004-12-14 23:26 ` Brian May 2004-12-15 17:43 ` Dmitry A. Kazakov 2004-12-15 21:54 ` Brian May 2004-12-15 4:05 ` Warren W. Gay VE3WWG 2004-12-15 18:26 ` Dmitry A. Kazakov 2004-12-16 2:53 ` Warren W. Gay VE3WWG 2004-12-18 16:43 ` Dmitry A. Kazakov 2004-12-18 20:36 ` Warren W. Gay VE3WWG 2004-12-18 22:21 ` Dmitry A. Kazakov 2004-12-19 0:53 ` Warren W. Gay VE3WWG 2004-12-19 12:21 ` Dmitry A. Kazakov 2004-12-20 5:33 ` Warren W. Gay VE3WWG 2004-12-20 20:01 ` Dmitry A. Kazakov 2004-12-20 20:54 ` Warren W. Gay VE3WWG 2004-12-14 22:40 ` Brian May 2004-12-15 3:23 ` Warren W. Gay VE3WWG 2004-12-15 15:01 ` Georg Bauhaus 2004-12-17 4:31 ` Brian May 2004-12-15 10:48 ` Brian May 2004-12-16 1:40 ` Brian May 2004-12-16 3:10 ` Warren W. Gay VE3WWG 2004-12-17 4:55 ` Brian May 2004-12-17 11:13 ` Warren W. Gay VE3WWG
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox