Re: get_info (was: Common DBI Driver Test Suite - Requirements)
Hey Martin. Thanks for your recent work on this - much food for thought! There's one more aspect of get_info that I'd appreciate your input on: On Sat, Sep 28, 2013 at 05:01:09PM +0100, Tim Bunce wrote: Somewhat independant of that, I'd like to identify a set of get_info items that we recommend all drivers define. That would be a handy thing to have, and to pass on to driver authors, and then use to build tests for those items. What I'm looking for here is mainly get_info items that we're likely to need to be able to write generic tests that are self-configuring to what the driver supports. Could you try to put together such a list? Tim.
Re: get_info (was: Common DBI Driver Test Suite - Requirements)
On 27/01/2014 20:45, Tim Bunce wrote: On Sat, Sep 28, 2013 at 05:24:24PM +0100, Martin J. Evans wrote: I've added it to my TO_DO list. By all means pester me is you hear nothing. Plese consider yourself pestered :) Tim. Back in Sept 2013 (and as part of the DBI Driver Test Suite - Requirements discussion) I said (amongst other things): As someone who has attempted to use DBI to multiple DBD backends (and even ignoring SQL differences) the biggest stumbling block is differences in DBDs either outside of the DBI specification or because DBI allows a driver to not implement something or allows a DBD multiple options. Just ask Peter (ribasushi) how much code is in DBIx::Class to work around driver differences. I've long thought (and I've a feeling proposed - but that might have just been in discussions with Peter over a pint) a capability system beyond what get_info provides. get_info is pretty much ODBC's SQLGetInfo and few drivers beyond DBD::ODBC really support it that well. Just off the top of my head I'm thinking about these sorts of differences: I've been in the position of writing DBD independent code a number of times over the last 10 or so years and it has nearly always resulted in writing a set of module wrappers above the DBD. Five major stumbling blocks have been calling procedures, getting cursors back from procedures, when are output bound parameters available, supported case in table/column names (and whether to quote to maintain case) and last_insert_id but there are many others. Here is a list of things DBDs might not support or do slightly differently with some explanation. This is just a starting list and I'll add to it if people reply and when I get a chance to look at DBIx::Class. 1 does it support named parameters - most do but some don't so I always ended up using ? 2 does it need a ':' preceding named parameters or not - I sent an email about this difference ages ago and I'll dig it out if necessary. DBI does not specify. 3 can named parameters be used multiple times in the same SQL - they certainly cannot in DBD::ODBC but can in DBD::Oracle. 4 does it use :N for placeholders or ? or both 5 can you mix ? and :N - you certainly can't in DBD::ODBC. 6 lobs - DBIs' lob read is not documented and so all DBDs do it a different way. There is a lot more to this than it seems. DBD::Oracle has at least 3 ways of obtaining lobs but the one we repeatedly use is the one which allows us to retrieve the length of a lob first before fetching it (perhaps in parts). We really could do with DBI having a lob read method. 7 returning cursors from functions or procedures - is it supported and how do you do it. This is something we do a lot and it is a PITA across multiple DBDs. 8 output parameters from procedures and functions and when they are available (more_results is another sticky one one here). Again, we had a long discussion about more_results years ago but it became so complex nothing came of it. 9 does it have its own execute_for_fetch - only implemented in 2 DBDs I know of and severely restricted in DBD::ODBC due to ODBC driver differences. You might say why would you care and that seems to be the attitude DBI takes i.e., it gets used if supported or DBI does it. It matters because if you know a DBD does not do it, then you might take a different path to using DBI's implementation. 10 can it support unicode. Obviously a contentious one and one we've visited many times before. 11 how do you enable unicode, sqlite_unicode etc - because of backwards compatibility many DBDs need a specific setting to enable unicode. 12 is last_insert_id usuable - not in some DBDs - certainly not DBD::ODBC. If it can't what do you do? 13 can you safely put comments in SQL - even comments including ? or :param 14 does a do create a statement handle, i.e, is do the same as $dbh-prepare-execute - this mostly only has implications when reporting errors i.e., which handle you get in an error handler. 15 can you rebind a placeholder with a different type 16 does a disconnect commit or rollback 17 does type_info/type_info_all return 19 or 20 columns - ODBC/MS added a new column. Although the type_info in DBI should be immune to this I've seen people having problems with this. 18 does it support DiscardString etc. Probably seems insignificant to most people but if you are reading from a database and producing JSON strings it can matter to you. If you don't have DiscardString you need to add 0 to any number or you JSON numbers end up as N instead of N especially when using JSON::XS. 19 does it support setting a type on bind_column - most don't DBIx::Class (used by many) and the work it has to do in the background to compensate for differences in DBDs must be full of tests which we can examine to get more. If someone from DBIx::Class (Peter/ribasushi?) points me in the right direction I'll look at it. Then there are loads of
Re: get_info (was: Common DBI Driver Test Suite - Requirements)
On Sat, Sep 28, 2013 at 05:24:24PM +0100, Martin J. Evans wrote: I've added it to my TO_DO list. By all means pester me is you hear nothing. Plese consider yourself pestered :) Tim.
get_info (was: Common DBI Driver Test Suite - Requirements)
On Fri, Sep 27, 2013 at 09:12:09AM +0100, Martin J. Evans wrote:
On 26/09/13 23:29, Tim Bunce wrote:
I'm expecting that one of the side-effects of DBIT will be a great
improvement in support for get_info by drivers. That'll be a win for all.
It definitely is a win. I can't even begin to tell you the pain I had
writing DBD neutral code just for ODBC, Oracle, DB2, mysql and
postgres. We persevered for some time but ended up with so much of the
class methods overridden we ended up with effectively 5 different
applications - although it did not look like it from the front. Once
you include the SQL as well - it really is not fun and although I
don't use DBIx::Class myself, it must be quite a piece of code. It
will be so much nicer to do:
if ($h-get_info(parameters_need_colons)) {
do this
} else {
do this
}
than
if ($h-{driver} eq 'Oracle' || $h-{driver} eq 'postres') blah blah
So, Martin, would you be interested in starting a little side-project
to propose an extension to the DBI spec for get_info()?
It's likely that DBIT will feed into that work, but there's clearly a
need beyond DBIT and you've clearly got (painful) experience to draw on :)
I suggest drawing up a list of things you'd like get_info to provide
info about (as you did informally in a recent email) then compare that
list with the those provided by the ANSI/ODBC standard to see which are
already covered.
Somewhat independant of that, I'd like to identify a set of get_info
items that we recommend all drivers define.
Tim.
Re: get_info (was: Common DBI Driver Test Suite - Requirements)
On 28/09/2013 17:01, Tim Bunce wrote:
On Fri, Sep 27, 2013 at 09:12:09AM +0100, Martin J. Evans wrote:
On 26/09/13 23:29, Tim Bunce wrote:
I'm expecting that one of the side-effects of DBIT will be a great
improvement in support for get_info by drivers. That'll be a win for all.
It definitely is a win. I can't even begin to tell you the pain I had
writing DBD neutral code just for ODBC, Oracle, DB2, mysql and
postgres. We persevered for some time but ended up with so much of the
class methods overridden we ended up with effectively 5 different
applications - although it did not look like it from the front. Once
you include the SQL as well - it really is not fun and although I
don't use DBIx::Class myself, it must be quite a piece of code. It
will be so much nicer to do:
if ($h-get_info(parameters_need_colons)) {
do this
} else {
do this
}
than
if ($h-{driver} eq 'Oracle' || $h-{driver} eq 'postres') blah blah
So, Martin, would you be interested in starting a little side-project
to propose an extension to the DBI spec for get_info()?
yes, of course.
It's likely that DBIT will feed into that work, but there's clearly a
need beyond DBIT and you've clearly got (painful) experience to draw on :)
so true.
I suggest drawing up a list of things you'd like get_info to provide
info about (as you did informally in a recent email) then compare that
list with the those provided by the ANSI/ODBC standard to see which are
already covered.
will do.
Somewhat independant of that, I'd like to identify a set of get_info
items that we recommend all drivers define.
Tim.
I've added it to my TO_DO list. By all means pester me is you hear nothing.
Martin
--
Martin J. Evans
Wetherby, UK
