Testing with sqlite

It can be daunting to start thinking about how you are going to test
database interaction for your app. Especially if you have never tested
database interaction. I have received several suggestions as to which
modules to use, however I have found the best success with just
creating a small sqlite db in memory,filling it with test data and
passing the db handle.

For example:

my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:","","") or die DBI::errstr;

Then you can just start inserting your test case data and then set up the tests to pass or fail accordingly.

That's not to say that that I think this is the best way to go about
things. It's just that the database testing I have needed to do so far
hasn't been complex enough for me to have to go and find a more
dynamic way to perform the tests.

Now I have run into situations at thewhere sqlite doesn't fit the
bill. This usually entails when database specific functions are
used. For example, if the Oracle "TO_DATE" function is passed to a sqlite database, then it's going to fail.

So sure, sqlite testing is great in most cases, however if you have a
database team who writes your queries for you, or you tend to want to
do a lot of database specific operations I would suggest testing with
the same database you are using in production.

Working with DateTime.pm

Lately I have been using the DateTime module for handling dates. While it cuts down on some of the laborious aspects of dealing with time intervals and date formatting there are some methods that can be confusing.


1) delta always returns positive. I have been advised to use delta to get the differences between days, however it always returns a positive number. I imagine there is a good reason for this and it would be great if someone could tell me why it is useful when you have alternatives such as subtract().


2) now() always comes back in UTC Unless you read the docs it is easy to assume that you are going to get now() in your own time zone.


Given that daylight savings time observed in different ways in different countries, converting time-zones is confusing. The correct way to subtract dates with datetime according to the docs: Always do your date math in UTC. Then you can convert the dates to whatever timezone is appropriate when its time to display the results.


It looks to me like DateTime makes time manipulation simple enough without glossing over important details. Given it is tricky when the default is utc but one can easily come to an understanding of the module's behavior if they test appropriately.

For Docs Go to the CPAN