Common DBI Driver Test Suite - Requirements
Hi all. This email aims to start a discussion around clarifying the goals, scope and requirements of a Common DBI Driver Test Suite, including learning from the great work already done on DBI::Test. Once we've kicked these around to the point were there's reasonable agreement, then we'll start a new thread to discuss design/implementation. For this thread though, goals, scope, and requirements are the focus. First, let's spell out some basics... Stakeholders: (who is affected by this work) - driver developers - cpantesters - end users Terminology: * We'll use DBIT as the generic term for this thing, whatever it turns out to look like or be called. Goals: ("how will we know if this project is or is not a success") G1. driver developers improve their compliance with the DBI API and so improve consistency and portability of code. This is what it's all about! G2. driver developers adopt DBIT as a free test suite with good coverage of the DBI API. This is the pay-back _for_ developers. G3. driver developers write and share reusable generic tests (they'll still need to write their own driver-specific tests). This is the pay-back _from_ developers. G4. end users won't be affected by DBIT evolution causing install failures i.e., DBIT runs as 'author testing', *at least for now*. This is our promise to end users not to ruin their day. G5. be usable for the widest range of DBI drivers, including non-SQL. This is a promise to be inclusive. G6. enable testing with proxies like DBD::Gofer. This is a promise to be flexible. G7. enable testing with DBI::PurePerl for pure-perl drivers. This is a promise to support fatpackers and perl6 ;) G8. Provide tools that drivers can use/extend for themselves. I'm thinking specifically here of the creation of test files with combinations of env vars and other settings. E.g., test DBD::CSV with Text::CSV and Text::CSV_XS G9. end users can find the level of DBI API compliance of a driver i.e., by looking at the test configuration for the driver to see what areas of functionality are configured to be skipped. [I thought of adding an extra goal: "other code can determine the limitations/quirks of a driver i.e., by looking at the test configuration for the driver to see how it's configured." but to be generic I think that would require a connected $dbh to determine server version/config. It's worth keeping in mind though in terms of having an object that represents the server that can be queried for info needed by tests. Perhaps that'll end up as extensions to $dbh->get_info().] Scope: (the boundaries and deliverables of the project) S1. the DBIT will be a separate distribution. S3. the DBI won't have a mandatory dependency on DBIT, for now at least, driver developers are the priority. S4. DBIT is not meant to test the underlying database. If a driver implements a database itself then it'll need it's own tests to provide good coverage of that. [Regarding S4: We could enable individual distros to install modules that add DBIT tests that'll be included in DBIT runs in certain cases, e.g., for certain drivers. I want to avoid running lots of SQL tests for drivers where that doesn't make sense. I'm sure we can find a way to do that but still allow sharing of SQL tests for SqlEngine based DBDs.] Requirements: R1. be easy for driver developers to adopt, so existing drivers migrate to using it. R2. be easy for driver developers to extend/contribute to, so driver developers contribute to it. R3. work well with cpantesters, so we get good coverage (perhaps extend Test::Database) So, any thoughts on these goals, scope, and requirements? (Please avoid design and implementation details for now. If you have any concerns that relate to design and implementation that you feel need to be considered now, try to express them in terms of the goals, scope, or requirements that would lead to that design.) Tim.
Re: Common DBI Driver Test Suite - Requirements
On Wed, 25 Sep 2013 17:28:04 +0100, Tim Bunce wrote: > Hi all. > > This email aims to start a discussion around clarifying the goals, scope > and requirements of a Common DBI Driver Test Suite, including learning > from the great work already done on DBI::Test. > > Once we've kicked these around to the point were there's reasonable > agreement, then we'll start a new thread to discuss design/implementation. > For this thread though, goals, scope, and requirements are the focus. > > First, let's spell out some basics... > > Stakeholders: (who is affected by this work) > > - driver developers > - cpantesters > - end users > > Terminology: > > * We'll use DBIT as the generic term for this thing, whatever it > turns out to look like or be called. > > Goals: ("how will we know if this project is or is not a success") > > G1. driver developers improve their compliance with the DBI API > and so improve consistency and portability of code. > This is what it's all about! Yes, my primary goal > G2. driver developers adopt DBIT as a free test suite with good > coverage of the DBI API. This is the pay-back _for_ developers. Yes, also my secondary goal. Going the right way here :) :) > G3. driver developers write and share reusable generic tests > (they'll still need to write their own driver-specific tests). > This is the pay-back _from_ developers. Most likely. To be honest I never thought about it that way > G4. end users won't be affected by DBIT evolution causing install > failures i.e., DBIT runs as 'author testing', *at least for now*. > This is our promise to end users not to ruin their day. 100% agree. for now it is optional > G5. be usable for the widest range of DBI drivers, including non-SQL. > This is a promise to be inclusive. +1 > G6. enable testing with proxies like DBD::Gofer. > This is a promise to be flexible. +1 > G7. enable testing with DBI::PurePerl for pure-perl drivers. > This is a promise to support fatpackers and perl6 ;) +2 > G8. Provide tools that drivers can use/extend for themselves. > I'm thinking specifically here of the creation of test files > with combinations of env vars and other settings. > E.g., test DBD::CSV with Text::CSV and Text::CSV_XS As a goal, this is fine. For DBD::CSV, this falls under G7: It will use Text::CSV_PP (from Text::CSV) under DBI::PurePerl and Text::CSV_XS under DBI (XS) > G9. end users can find the level of DBI API compliance of a driver > i.e., by looking at the test configuration for the driver > to see what areas of functionality are configured to be skipped. I have a hard time parsing that. > [I thought of adding an extra goal: "other code can determine the > limitations/quirks of a driver i.e., by looking at the test > configuration for the driver to see how it's configured." but to be > generic I think that would require a connected $dbh to determine server > version/config. It's worth keeping in mind though in terms of having an > object that represents the server that can be queried for info needed by > tests. Perhaps that'll end up as extensions to $dbh->get_info().] > > > Scope: (the boundaries and deliverables of the project) > > S1. the DBIT will be a separate distribution. +1. It already is, and there has never been the intention to bundle it in any other distribution > S3. the DBI won't have a mandatory dependency on DBIT, > for now at least, driver developers are the priority. +100 > S4. DBIT is not meant to test the underlying database. > If a driver implements a database itself then it'll > need it's own tests to provide good coverage of that. DBIT will offer the interface to test the underlying database. What is important here, as DBIT will offer the possibility to test under different circumstances (like with SQL::Nano or under DBI::Proxy) without having to rewrite all tests over and over again. These test will reside in the test suite of the DBD, not in the test suite of DBIT > [Regarding S4: We could enable individual distros to install modules > that add DBIT tests that'll be included in DBIT runs in certain cases, > e.g., for certain drivers. I want to avoid running lots of SQL tests > for drivers where that doesn't make sense. I'm sure we can find a way to > do that but still allow sharing of SQL tests for SqlEngine based DBDs.] > > > Requirements: > > R1. be easy for driver developers to adopt, > so existing drivers migrate to using it. +1 > R2. be easy for driver developers to extend/contribute to, > so driver developers contribute to it. +1 > R3. work well with cpantesters, > so we get good coverage (perhaps extend Test::Database) +1 > So, any thoughts on these goals, scope, and requirements? > > (Please avoid design and implementation d
Re: Common DBI Driver Test Suite - Requirements
On Wed, Sep 25, 2013 at 07:02:07PM +0200, H.Merijn Brand wrote: > On Wed, 25 Sep 2013 17:28:04 +0100, Tim Bunce > wrote: > > > G9. end users can find the level of DBI API compliance of a driver > > i.e., by looking at the test configuration for the driver > > to see what areas of functionality are configured to be skipped. > > I have a hard time parsing that. Drivers have various limitations, things they don't, or can't, support. It would be unreasonable for them to fail DBIT for those reasons. So there needs to be a way for those drivers to tell DBIT that certain tests should be skipped. And not just drivers. Things like proxies will need to influence what's expected to work. This is a key area. What G9 is trying to express is that that information (which tests to skip) is useful for end users, and probably applications as well. So we should aim to design the system in a way that make that information available in a useful way. Some of that might end up being handled by $dbh->get_info() but there's bound to be some that isn't. > > S4. DBIT is not meant to test the underlying database. > > If a driver implements a database itself then it'll > > need it's own tests to provide good coverage of that. > > DBIT will offer the interface to test the underlying database. What is > important here, as DBIT will offer the possibility to test under > different circumstances (like with SQL::Nano or under DBI::Proxy) > without having to rewrite all tests over and over again. > These test will reside in the test suite of the DBD, not in the test > suite of DBIT Yes. Or, for drivers that want to share tests, those tests could live in modules installed by a distro that the driver declares it's dependant on. Tim.
Re: Common DBI Driver Test Suite - Requirements
Am 25.09.2013 um 19:02 schrieb H.Merijn Brand : > On Wed, 25 Sep 2013 17:28:04 +0100, Tim Bunce > wrote: > >> Hi all. >> [...] > >>G8. Provide tools that drivers can use/extend for themselves. >>I'm thinking specifically here of the creation of test files >>with combinations of env vars and other settings. >>E.g., test DBD::CSV with Text::CSV and Text::CSV_XS > > As a goal, this is fine. For DBD::CSV, this falls under G7: It will use > Text::CSV_PP (from Text::CSV) under DBI::PurePerl and Text::CSV_XS > under DBI (XS) Don't discuss implementation details here. Keep this argument in mind and come back on that later. Then I tell you why I think you have a 3rd case and so need a G8 :D Cheers -- Jens Rehsack pkgsrc, Perl5 rehs...@cpan.org
Re: Common DBI Driver Test Suite - Requirements
Am 25.09.2013 um 19:27 schrieb Tim Bunce : > On Wed, Sep 25, 2013 at 07:02:07PM +0200, H.Merijn Brand wrote: >> On Wed, 25 Sep 2013 17:28:04 +0100, Tim Bunce >> wrote: >> >>>G9. end users can find the level of DBI API compliance of a driver >>>i.e., by looking at the test configuration for the driver >>>to see what areas of functionality are configured to be skipped. >> >> I have a hard time parsing that. > > Drivers have various limitations, things they don't, or can't, support. > It would be unreasonable for them to fail DBIT for those reasons. > So there needs to be a way for those drivers to tell DBIT that > certain tests should be skipped. Riba put that into a R4: It should be possible to configure which test(case) should be run and which not. This should be possible in a manner which avoids asking cpantesters 100 y/n questions ^^ Maybe this belongs to that point (Riba should be able to explain better). For me, a G10 is interesting (handling SQL::Statement as a DBD) G10: Provide tools/infrastructure to combines several test cases in particular order into tests to allow workflows being tested and stress tests by looping can be performed. > And not just drivers. Things like proxies will need to influence > what's expected to work. This is a key area. > > What G9 is trying to express is that that information (which tests to > skip) is useful for end users, and probably applications as well. > So we should aim to design the system in a way that make that > information available in a useful way. > > Some of that might end up being handled by $dbh->get_info() but there's > bound to be some that isn't. > > >>>S4. DBIT is not meant to test the underlying database. >>>If a driver implements a database itself then it'll >>>need it's own tests to provide good coverage of that. >> >> DBIT will offer the interface to test the underlying database. What is >> important here, as DBIT will offer the possibility to test under >> different circumstances (like with SQL::Nano or under DBI::Proxy) >> without having to rewrite all tests over and over again. >> These test will reside in the test suite of the DBD, not in the test >> suite of DBIT > > Yes. Or, for drivers that want to share tests, those tests could live in > modules installed by a distro that the driver declares it's dependant on. I would prefer the "distributed separately" way. It doesn't have to be dozen of independent distributions, there could be a few thematically grouped ones. Like some for ANSI SQL, some for proxy specific ones (like spreading some requests to see how the scaling is?), …) Cheers -- Jens Rehsack pkgsrc, Perl5 rehs...@cpan.org
Re: Common DBI Driver Test Suite - Requirements
On 25/09/13 17:28, Tim Bunce wrote: Hi all. This email aims to start a discussion around clarifying the goals, scope and requirements of a Common DBI Driver Test Suite, including learning from the great work already done on DBI::Test. Once we've kicked these around to the point were there's reasonable agreement, then we'll start a new thread to discuss design/implementation. For this thread though, goals, scope, and requirements are the focus. First, let's spell out some basics... Stakeholders: (who is affected by this work) - driver developers - cpantesters - end users Terminology: * We'll use DBIT as the generic term for this thing, whatever it turns out to look like or be called. Goals: ("how will we know if this project is or is not a success") G1. driver developers improve their compliance with the DBI API and so improve consistency and portability of code. This is what it's all about! G2. driver developers adopt DBIT as a free test suite with good coverage of the DBI API. This is the pay-back _for_ developers. I think one thing many DBD test suites could benefit from is wrappers around many of the DBI methods that wrap that method in tests e.g., when execute is called, did it return undef, a true value (0E0 or a number > 0) or -1 depending on whether it is a select statement or not. If test suites were converted to use those I'm sure we'd find quite a few issues in DBDs but still using existing test code. G3. driver developers write and share reusable generic tests (they'll still need to write their own driver-specific tests). This is the pay-back _from_ developers. G4. end users won't be affected by DBIT evolution causing install failures i.e., DBIT runs as 'author testing', *at least for now*. This is our promise to end users not to ruin their day. G5. be usable for the widest range of DBI drivers, including non-SQL. This is a promise to be inclusive. G6. enable testing with proxies like DBD::Gofer. This is a promise to be flexible. G7. enable testing with DBI::PurePerl for pure-perl drivers. This is a promise to support fatpackers and perl6 ;) G8. Provide tools that drivers can use/extend for themselves. I'm thinking specifically here of the creation of test files with combinations of env vars and other settings. E.g., test DBD::CSV with Text::CSV and Text::CSV_XS G9. end users can find the level of DBI API compliance of a driver i.e., by looking at the test configuration for the driver to see what areas of functionality are configured to be skipped. This in particular is something I'd like to see and expand on. 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: a) does it support named parameters b) does it need a ':' preceding named parameters or not c) can named parameters be used multiple times in the same SQL d) does it use :N for placeholders or ? e) lobs - DBIs' lob read is not documented and so all DBDs do it a different way f) returning cursors from functions or procedures g) output parameters from procedures and functions and when they are available (more_results is another one here) h) DBIx::Class (used by many) and the work it has to do in the background to compensate for differences in DBDs. i) 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. j) can it support unicode k) how do you enable unicode, sqlite_unicode etc l) is last_insert_id usuable - not in some DBDs m) can you safely put comments in SQL - even comments including ? or :param n) does a do create a statement handle, i.e, is do the same as $dbh->prepare->execute o) can you rebind a placeholder with a different type p) does a disconnect commit or rollback q) does type_info/type_info_all return 19 or 20 columns r) does it support DiscardString etc s) does it support setting a type on bind_column . . If I put my mind to it (and looked at my code from years ago when I was involved in writing to multiple DBDs from the same application) I could proably come up with a much longer list - Peter probably could too. I know th
Re: Common DBI Driver Test Suite - Requirements
On Wed, Sep 25, 2013 at 08:36:22PM +0200, Jens Rehsack wrote: > > Am 25.09.2013 um 19:27 schrieb Tim Bunce : > > > On Wed, Sep 25, 2013 at 07:02:07PM +0200, H.Merijn Brand wrote: > >> On Wed, 25 Sep 2013 17:28:04 +0100, Tim Bunce > >> wrote: > >> > >>>G9. end users can find the level of DBI API compliance of a driver > >>>i.e., by looking at the test configuration for the driver > >>>to see what areas of functionality are configured to be skipped. > >> > >> I have a hard time parsing that. > > > > Drivers have various limitations, things they don't, or can't, support. > > It would be unreasonable for them to fail DBIT for those reasons. > > So there needs to be a way for those drivers to tell DBIT that > > certain tests should be skipped. > > Riba put that into a > > R4: It should be possible to configure which test(case) should be > run and which not. This should be possible in a manner which > avoids asking cpantesters 100 y/n questions ^^ > > Maybe this belongs to that point (Riba should be able to explain better). I'd appreciate more details. > For me, a G10 is interesting (handling SQL::Statement as a DBD) > > G10: Provide tools/infrastructure to combines several test cases in > particular order into tests to allow workflows being tested > and stress tests by looping can be performed. Also here. I'm not quite sure what problem this is trying to address. Tim.
Re: Common DBI Driver Test Suite - Requirements
On Thu, Sep 26, 2013 at 05:55:56PM +0100, Martin J. Evans wrote: > On 25/09/13 17:28, Tim Bunce wrote: > > > > G2. driver developers adopt DBIT as a free test suite with good > > coverage of the DBI API. This is the pay-back _for_ developers. > > I think one thing many DBD test suites could benefit from is wrappers > around many of the DBI methods that wrap that method in tests e.g., > when execute is called, did it return undef, a true value (0E0 or a > number > 0) or -1 depending on whether it is a select statement or > not. If test suites were converted to use those I'm sure we'd find > quite a few issues in DBDs but still using existing test code. I don't follow you here Martin. DBIT will implement detailed testing for the return value of execute. > > G9. end users can find the level of DBI API compliance of a driver > > i.e., by looking at the test configuration for the driver > > to see what areas of functionality are configured to be skipped. > > This in particular is something I'd like to see and expand on. > > [...] > 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. 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's also worth noting that we can define our own meanings for certain values passed to get_info. With surprising foresight, sometime around 1998 I think, I arranged for the get_info values 9000 thru to be reserved for the Perl DBI in the ISO standard registry: Registry of Values for the SQL Standard (ANSI X3.135 and ISO/IEC 9075) With especial attention to values related to SQL/CLI ([ANSI/]ISO/IEC 9075-3) I actually reserved value ranges for just about all of the SQL CLI functions. http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31285.html > [...] > > If I put my mind to it (and looked at my code from years ago when I > was involved in writing to multiple DBDs from the same application) I > could proably come up with a much longer list - Peter probably could > too. When the time comes we'll probably need one :) > I know this is not DBIT as such and you might see it as a distraction (if you > do, ignore) but I think it would be worth while. Very. I think it's important. > > R3. work well with cpantesters, > > so we get good coverage (perhaps extend Test::Database) > > As a side note, I as going to add support to Test::Database for > DBD::ODBC because I thought it might get me more smokers actually > running the tests. Then I discovered it needed create database > support, and that was not going to happen with ODBC as each database > has totally different syntax for that and some databases need very > high level permissions to create a database. As I mentioned elsewhere, I suspect we'll want to extend or fork Test::Database to bend it to our needs. > These seem like worthwhile goals. Whether I can be of any help, I > don't know, but I'll at least try and keep up with the discussion and > provide any useful feedback. Thanks Martin. Tim.
Re: Common DBI Driver Test Suite - Requirements
On Thu, 26 Sep 2013 23:29:34 +0100, 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. That was the first incentive for me to start thinking about this years ago. One needs this for scripts that do cross-database work, certainly if database drivers change (to the good) over time, like changing from non-compliant to compliant. -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.19 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
Re: Common DBI Driver Test Suite - Requirements
On 26/09/13 23:29, Tim Bunce wrote:> On Thu, Sep 26, 2013 at 05:55:56PM +0100, Martin J. Evans wrote: On 25/09/13 17:28, Tim Bunce wrote: G2. driver developers adopt DBIT as a free test suite with good coverage of the DBI API. This is the pay-back _for_ developers. I think one thing many DBD test suites could benefit from is wrappers around many of the DBI methods that wrap that method in tests e.g., when execute is called, did it return undef, a true value (0E0 or a number > 0) or -1 depending on whether it is a select statement or not. If test suites were converted to use those I'm sure we'd find quite a few issues in DBDs but still using existing test code. I don't follow you here Martin. DBIT will implement detailed testing for the return value of execute. All I meant was for DBIT to provide method wrappers which incorporate the tests so when DBD writers write their DBD-specific tests they get the method tests for free. G9. end users can find the level of DBI API compliance of a driver i.e., by looking at the test configuration for the driver to see what areas of functionality are configured to be skipped. This in particular is something I'd like to see and expand on. [...] 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. 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 It's also worth noting that we can define our own meanings for certain values passed to get_info. With surprising foresight, sometime around 1998 I think, I arranged for the get_info values 9000 thru to be reserved for the Perl DBI in the ISO standard registry: Registry of Values for the SQL Standard (ANSI X3.135 and ISO/IEC 9075) With especial attention to values related to SQL/CLI ([ANSI/]ISO/IEC 9075-3) I actually reserved value ranges for just about all of the SQL CLI functions. http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31285.html Nice one. [...] If I put my mind to it (and looked at my code from years ago when I was involved in writing to multiple DBDs from the same application) I could proably come up with a much longer list - Peter probably could too. When the time comes we'll probably need one :) I know this is not DBIT as such and you might see it as a distraction (if you do, ignore) but I think it would be worth while. Very. I think it's important. Excellent. I will be happy to help with this as I can. R3. work well with cpantesters, so we get good coverage (perhaps extend Test::Database) As a side note, I as going to add support to Test::Database for DBD::ODBC because I thought it might get me more smokers actually running the tests. Then I discovered it needed create database support, and that was not going to happen with ODBC as each database has totally different syntax for that and some databases need very high level permissions to create a database. As I mentioned elsewhere, I suspect we'll want to extend or fork Test::Database to bend it to our needs. These seem like worthwhile goals. Whether I can be of any help, I don't know, but I'll at least try and keep up with the discussion and provide any useful feedback. Thanks Martin. Tim. Martin
Re: Common DBI Driver Test Suite - Requirements
Am 27.09.2013 um 00:03 schrieb Tim Bunce : > On Wed, Sep 25, 2013 at 08:36:22PM +0200, Jens Rehsack wrote: > >> For me, a G10 is interesting (handling SQL::Statement as a DBD) >> >> G10: Provide tools/infrastructure to combines several test cases in >> particular order into tests to allow workflows being tested >> and stress tests by looping can be performed. > > Also here. I'm not quite sure what problem this is trying to address. This is more or less an idea I had during test development. For a lot of tests, we write some sequences of statements to finally prove one or a few things working fine (at least for DBD::CSV, DBD::DBM and a lot of SQL::Statement tests - but IIRC, this is true for SQLite, too). So for testing whether UPDATE/DELETE works, we create some tables, do some inserts and after that, modify them. For SELECT testing, same. For ChopBlank testing (via select, of course), similar. For JOIN testing, more tables - but finally the same. This goes on … (different edge cases to test, but finally all the same). For SQL::Statement I added a performance check )for being run through the profilers we have) which is assembled from one or more of the above tests. But by hand. If there is a design of combining test cases to a sequence of tests (eg. create tables -> fill data -> select -> update -> delete -> drop), it could help simplifying test cases and allow put loops around sequences to redo some test sequence for 100 times for profiling. The design finally choosen for this goal might be a composite for test cases - but the test case design shall allow doing that. Cheers -- Jens Rehsack rehs...@gmail.com
Re: Common DBI Driver Test Suite - Requirements
On Fri, Sep 27, 2013 at 11:42:10AM +0200, Jens Rehsack wrote: > Am 27.09.2013 um 00:03 schrieb Tim Bunce : > > > On Wed, Sep 25, 2013 at 08:36:22PM +0200, Jens Rehsack wrote: > > > >> For me, a G10 is interesting (handling SQL::Statement as a DBD) > >> > >> G10: Provide tools/infrastructure to combines several test cases in > >> particular order into tests to allow workflows being tested > >> and stress tests by looping can be performed. > > > > Also here. I'm not quite sure what problem this is trying to address. > > > This is more or less an idea I had during test development. For a lot > of tests, we write some sequences of statements to finally prove one > or a few things working fine (at least for DBD::CSV, DBD::DBM and a > lot of SQL::Statement tests - but IIRC, this is true for SQLite, too). > > So for testing whether UPDATE/DELETE works, we create some tables, do > some inserts and after that, modify them. For SELECT testing, same. > For ChopBlank testing (via select, of course), similar. For JOIN testing, > more tables - but finally the same. This goes on … (different edge cases > to test, but finally all the same). > > For SQL::Statement I added a performance check )for being run through > the profilers we have) which is assembled from one or more of the above > tests. But by hand. > > If there is a design of combining test cases to a sequence of tests (eg. > create tables -> fill data -> select -> update -> delete -> drop), it > could help simplifying test cases and allow put loops around sequences > to redo some test sequence for 100 times for profiling. > > The design finally choosen for this goal might be a composite for test > cases - but the test case design shall allow doing that. Test files need to be self-contained, so they can be run in parallel and in randomized order. They'd naturally have a setup-fixture phase, a run-tests phase, and a teardown-fixture phase. Repeating the whole sequence of phases N-times would be easy enough. For some, but not all tests, repeating the run-tests phase N-times between the setup and teardown phases would be possible, and faster. I think this ability would fall-out nicely from defining a suitable API to the code in the test modules. Then it's a simple change to the code that calls the subs/methods in the test modules. Tim. p.s. I'll add parallel running to the requirements - it has significant implications on the design, e.g., unique names for tables.
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.
Common DBI Driver Test Suite - Requirements - take 2
Here's an updated Requirements document, tweaked a little based on the feedback. Goals: ("how will we know if this project is or is not a success") G1. driver developers improve their compliance with the DBI API and so improve consistency and portability of code. This is what it's all about! G2. driver developers adopt DBIT as a free test suite with good coverage of the DBI API. This is the pay-back _for_ developers. G3. driver developers write and share reusable generic tests (they'll still need to write their own driver-specific tests). This is the pay-back _from_ developers. G4. end users aren't affected by DBIT evolution causing install failures i.e., DBIT runs as 'author testing', *at least for now*. This is our promise to end users not to ruin their day. G5. works with the widest range of DBI drivers, including non-SQL and proxy drivers. This is a promise to be inclusive and flexible. G6. enables testing with DBI::PurePerl for pure-perl drivers. This is a promise to support fatpackers and perl6 ;) G7. end users can find the level of DBI API compliance of a driver The test configuration, e.g., what tests to skip, will be data-driven rather than hard-coded logic, and the data will readable via some API. Scope: (the boundaries and deliverables of the project) S1. the DBIT will be a separate distribution. S3. the DBI won't have a mandatory dependency on DBIT, for now at least, driver developers are the priority. S4. DBIT is not meant to test the underlying database. If a driver implements a database itself then it'll need it's own tests to provide good coverage of that. Those tests could use the DBIT infrastructure. Requirements: R1. be easy for driver developers to adopt, so existing drivers migrate to using it. R2. be easy for driver developers to extend/contribute to, so driver developers contribute to it. R3. work well with cpantesters, so we get good coverage (perhaps extend Test::Database) R4. use get_info as the basis for determining the capabilities of the driver and database. We'll extend get_info as needed. R5. allow tests to be run in parallel, e.g. unique table names. R6. provides tools that drivers can use/extend for themselves. I'm thinking specifically here of the creation of test files with combinations of env vars and other settings. E.g., test DBD::CSV with Text::CSV and Text::CSV_XS 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
Re: Common DBI Driver Test Suite - Requirements - take 2
Am 29.09.2013 um 21:02 schrieb Tim Bunce : > Here's an updated Requirements document, tweaked a little based on the > feedback. > > > Goals: ("how will we know if this project is or is not a success") > >G1. driver developers improve their compliance with the DBI API >and so improve consistency and portability of code. >This is what it's all about! > >G2. driver developers adopt DBIT as a free test suite with good >coverage of the DBI API. This is the pay-back _for_ developers. > >G3. driver developers write and share reusable generic tests >(they'll still need to write their own driver-specific tests). >This is the pay-back _from_ developers. > >G4. end users aren't affected by DBIT evolution causing install failures >i.e., DBIT runs as 'author testing', *at least for now*. >This is our promise to end users not to ruin their day. > >G5. works with the widest range of DBI drivers, including non-SQL and >proxy drivers. This is a promise to be inclusive and flexible. > >G6. enables testing with DBI::PurePerl for pure-perl drivers. >This is a promise to support fatpackers and perl6 ;) > >G7. end users can find the level of DBI API compliance of a driver >The test configuration, e.g., what tests to skip, will be data-driven >rather than hard-coded logic, and the data will readable via some API. > > > Scope: (the boundaries and deliverables of the project) > >S1. the DBIT will be a separate distribution. > >S3. the DBI won't have a mandatory dependency on DBIT, >for now at least, driver developers are the priority. > >S4. DBIT is not meant to test the underlying database. >If a driver implements a database itself then it'll >need it's own tests to provide good coverage of that. >Those tests could use the DBIT infrastructure. > I don’t find the mail regarding that, but I remembered that my primary goal is testing the underlying database (I call it SQL::Statement ^^). > Requirements: > >R1. be easy for driver developers to adopt, >so existing drivers migrate to using it. > >R2. be easy for driver developers to extend/contribute to, >so driver developers contribute to it. > >R3. work well with cpantesters, >so we get good coverage (perhaps extend Test::Database) > >R4. use get_info as the basis for determining the capabilities >of the driver and database. We'll extend get_info as needed. > >R5. allow tests to be run in parallel, e.g. unique table names. > >R6. provides tools that drivers can use/extend for themselves. >I'm thinking specifically here of the creation of test files >with combinations of env vars and other settings. >E.g., test DBD::CSV with Text::CSV and Text::CSV_XS > > > Tim. > -- Jens Rehsack rehs...@gmail.com
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.
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 load
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.