Interesting! I wish I'd known about DBD::Mock sooner. I'm (painfully) creating bogus data and then deleting it in my existing test suites. This seems like a more elegant way of doing it.

When you mention API testing, are you implying Class::DBI? All of my classes are descended from Class::DBI - I've added no functionality to it.

Thanks!
MrCromeDome

When you mention API testing, are you implying Class::DBI? All of my classes are descended from Class::DBI - I've added no functionality to it.

API testing means that you should test how your class might be used. If a user calls the view_table method, it will spit out the data @blah. Even if Class::DBI does all the dirty work, you still want to be sure that your return values comes out as you expect. In a way this is black box testing since you are not examing the internals of Class::DBI (where the work is being done), but only checking that given the proper input, your methods return the proper output.

And then of course, you can use DBD::Mock to check that the right SQL statement was produced, and all that good stuff too. In a way this is also testing that Class::DBI is doing it's job correctly, although maybe more grey box testing.

-stvn

I wish I'd known about DBD::Mock sooner. I'm (painfully) creating bogus data and then deleting it in my existing test suites.

I do not wish to take anything away from the fine post by dragonchild, because it is good advice. DBD::Mock is a lightweight way to test your API, as he said. But, as he also said, "That should be a good start."

In other words, it is not the whole solution. You should still be testing the database itself, in some capacity. You want to make sure the connection works, the database is returning the right type of data, the table structure works as expected, etc. So once you get a nice suite of API tests with DBD::Mock, you are still probably going to end up creating bogus data and deleting it, in order to further test your application's database interaction.

Please do not think I'm saying DBD::Mock is pointless, though. It allows you (and your test suite) to narrow down where a problem lies. For example, if you are getting incorrect data from the live db test, but correct data from the DBD::Mock test, then you know there might be something wrong with the database. This type of troubleshooting information is extremely useful.

Well, you obviously are doing something more than just Class::DBI is doing, otherwise you wouldn't have subclassed it!

Maybe you could test that the My::DBI::Foo class does or doesn't have a bar() method or a 'baz' column. Maybe you need to test your collections to make sure the right thing comes back? has_a()/has_many()/might_have() all need tested to make sure you're returning the right class.

These tests are going to be very simple and should run very quickly. But, they provide some sort of safety net for you to program against. Remember - most safety nets are built with twine. :-)

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

There are a few ways you can do this. Most of them involve having a reference database with known data.

One way to restore the known state is to begin a transaction at the beginning of the test and roll it back at the end of the test. Any changes you made during the test are undone.

The problem with this approach is that the rollback semantics in most databases break this; begin and commit are nested properly but a rollback rolls back the entire transaction, not just the most deeply nested one.

In one project with no legacy code we could use transaction savepoints to "fake" proper rollbacks of only the innermost nesting level. And that made it possible to use a transaction for restoring the database state in all unit tests. (The 1100+ unit tests was a success factor for that project btw). This was for Sybase T-SQL, but the savepoint stuff works for Oracle as well.

One more generic way of providing a known state is to make a snapshot of the database before running the tests and restore it just before each test.

If you can run SQLite this is just a matter of copying the, often very small, database file.

If it's any other database, see if you can't script something with your database's data loader utility.

During the actual testing, I find Test::DatabaseRow very useful. One thing to be wary of is that you may have two different database connections to the database, and depending on the isolation level and transaction state, one database connection may not see changes made using the other database connection. And depending on the locking scheme and isolation level, the two connections may lock each other.

/J

The tranasction savepoints sound interesting . While never having investigated them, I'm imagining that SQL Server (the DBMS I'm stuck with) must have some similar functionality. My original suite of tests was very brute force - add rows that I need for my tests, run the tests, manually delete those rows. On the plus side, I at least verified that the delete functionality in Class::DBI worked as intended ;)

Up to this point, I've always verified my database tests using Class::DBI, but I think I like using yet another tool to verify my test data. Looks like Test::DatabaseRow will fit that bill nicely. Thanks for the recommendation!

MrCromeDome

I can imagine that Sybase and MS SQL Server have identical transaction semantics.

This only works if you can do it consistently, but here is how to nest transactions and limit the rollback scope so that it matches that of e.g. a procedure:

    begin tran
    save tran the_procedure_name
    
    --modify stuff

    if ... error ... begin
        rollback tran the_procedure_name
        commit tran
        return 1
    end
 
    --success   
 
    commit tran
    
    return 0
[download]

You start with beginning a transaction and immediately saving the transaction to a named savepoint. I've asked around and this doesn't seem to incur very much overhead.

If everything works okay, just commit once to end the outermost transaction.

If anything goes wrong, first rollback to the named savepoint, then commit the transaction (to make sure it's nested properly).

/J

While not as good as probably using DBD::Mock, I usually test my Class::DBI based modules through a series of tests using DBD::SQLite since it doesn't rely on MySQL/Postgres/MSSQL installs. Of course, the minute I need db specific SQL, I'm screwed. :-)

Usually, I'll use_ok/isa_ok the modules, load sql schema/data into a db file using DBI, override the Class::DBI::connection/set_db to point to that file, then test all the interactions and results.

Now granted, I'm testing just as much SQLite as I am my module, but the same it true at a certain level for DBD::Mock as well I guess.

Now granted, I'm testing just as much SQLite as I am my module, but the same it true at a certain level for DBD::Mock as well I guess.

Actually, it's not. DBD::Mock doesn't actually do anything. In fact, it doesn't even look at the SQL you gave it other than to verify you gave it something. What it does is say "The first statement executed will get resultset A and the second will get resultset B". That's it.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

That is true, and I'm not disagreeing. But at a certain level, I'm still testing DBI, its use of DBD drivers and the DBD::Mock code. That was what I meant.

If I'm going to depend on DBI+DBD+Mock to be in working order, I might as well go for the gusto and use a DBD driver that does something with the statements against a raw DB file I control during testing. That's just my opinion.

My subclasses are autogenerated from a SQLFairy scheme objects (sometime soon it might make it the CPAN), so I have two tests per class:

• schema test
• functionality test

The schema tests make sure that important fields are there, and relationships work, and exist (->meta_info), proper subclassing is done, and so forth. Since all of this is auto generated, I write these before I write the schema, and then go through the schema slowly passing the tests.

The functionality tests take the objects, and create them throught the CDBI interface, and work with them. These tests are not concerned about how the cdbi objects even persist. They would work if a compatible interface was based on Storable, for example. They test the functionality as defined by the subclass.

Since I have funky stuff going on (Class::Delegation AUTOLOADing accessors to a Storable'd has in a BLOB, for example), these tests are pretty comprehensive.

What i'm trying to point out is that this layer, the database persistence, has two concerns - correct definition of the persistence semantics, and correct functionality of the persisting objects (perhaps including the fact that they properly persist). Neither of these concerns have to do with the database itself. That's for Class::DBI to test, and indeed it does. If you feel you can help it test more comprehensively, then you should submit these tests to Class::DBI.