Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Testing and database date functions

by relax99 (Monk)
on Dec 09, 2004 at 16:27 UTC ( [id://413612]=perlquestion: print w/replies, xml ) Need Help??

relax99 has asked for the wisdom of the Perl Monks concerning the following question:

Hello!

I am writing a test script for a Perl program that does database operations. The problem is that some of these operations depend on database's current date and dayofweek functions and I can not vary results from these.

The program reads two values from a table (for the current date and the next date) and then makes updates to a row in another table for the current date based on the data in these two rows. My initial solution was to create a test script that uses Test::Simple to test the actual result against the required result based on a set of predefined inputs and outputs.

Set 1: M T W T F S S Inputs: 1, 1, 0, 1, 0, 0, 0 Outputs: 1, 1, 0, 0, 0, 0, 0 Set 2: M T W T F S S Inputs: 1, 1, 1, 1, 1, 0, 0 Outputs: 1, 1, 1, 1, 1, 0, 0 Set 3: etc...

For instance, if the current day of week is Monday then the inputs are 1, 1 and the output should be 1. If the current day of week is Thursday then the inputs are 1, 0 (from set 1) and the output should be 0.

But, like I said, the Perl program uses sql with current date function in it, so I can't get the program to accept the inputs for arbitrary days of week.

How would you go about testing this program? Is it even possible? I could run the test every day for 7 days and that would test all combinations of inputs, but that seems to undermine the whole concept of testing where you should be able to run all of your tests and see if they succeed at any point in time, not over a period of so many days.

Please ask me for more details if something in the description of the problem is unclear to you.

Thanks!

Replies are listed 'Best First'.
Re: Testing and database date functions
by pernod (Chaplain) on Dec 09, 2004 at 16:42 UTC

    Take a look at Test::MockObject. What you are trying to test is the script, not the database, therefore you can fake the database (and its date and dayofweek functions) using MockObjects.

    Do a super search on Test::MockObject to see some other uses of mocking. It is a very powerful technique that allows you to concentrate on the main job of your code, and it allows you to keep testdata and tests in the same place.

    Good luck!

    pernod
    --
    Mischief. Mayhem. Soap.

      Even better, check out DBD::Mock which is meant to transparently replace your database connection allows you to specify resultsets. You could seed the connection with any results you want no matter what day it is.

      Chris
      M-x auto-bs-mode

Re: Testing and database date functions
by dragonchild (Archbishop) on Dec 09, 2004 at 18:14 UTC
    I vote for DBD::Mock - I've had good results with it.

    Alternately, the fact that testing your script isn't easy may be a sign that you should consider reworking your design. :-)

    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.

      May or may not be a sign. :) The script is dead simple... I am faily sure I got the design right. I'd still be curious to see what you think about it, but I can't post it here since there are some company/business specific details and once I remove them there isn't much of the script left.

      And I naturally came across DBD::Mock once I started looking at Test::MockObject... I think DBD::Mock could be a solution to this and many other testing needs. Thank you!

Re: Testing and database date functions
by Tuppence (Pilgrim) on Dec 09, 2004 at 19:27 UTC

    In order to be able to fully test this kind of code, my company uses a set_test_datetime function that sets up the database to think it is that day, and also sets the time in the libraries.

    In this way, we can test for any date and any time, no matter when it is right now.

      That would work great - exactly what I need. I thought about it, but the company I work at right now is ***fairly new*** to automated testing of its production Perl code. That may not find understanding with a lot of people, so I have to do my testing without effecting other people's ways of doing things.
Re: Testing and database date functions
by hmerrill (Friar) on Dec 09, 2004 at 18:38 UTC
      But, like I said, the Perl program uses sql with current date function in it, so I can't get the program to accept the inputs for arbitrary days of week.
    I don't really know what you mean by this. What database is this for? What "current date function" are you referring to? Most database have some type of "day of the week" function - you supply the date and it tells you what the day of the week is.

    For example, Oracle's current date function is SYSDATE. If my perl script did something like:

    my $dbh = DBI->connect(blah blah) or die "Can't connect: $DBI::errstr"; my $sth = $dbh->prepare("SELECT SYSDATE FROM DUAL") or die "Can't prepare: $DBI::errstr";
    why couldn't you change the perl script to get the day of the week from Oracle - something like this:
    my $sth = $dbh->prepare(qq{ SELECT TO_CHAR(SYSDATE, 'DAY') AS DAY FROM DUAL }) or die "Can't prepare: $DBI::errstr";
    In Oracle sqlplus, doing "SELECT TO_CHAR(SYSDATE, 'DAY') AS DAY_OF_THE_WEEK FROM DUAL" produces this:
    SQL> SELECT TO_CHAR(SYSDATE, 'DAY') AS DAY 2 FROM DUAL; DAY -------- THURSDAY
    Isn't that what you're looking for?

    HTH.

      No, sorry, the code is already written. I just want to test it now. What I am trying to do is to verify that the code will run the tests correctly for every day of a week. And I don't want to make any changes to production sql while doing that.

      I was referring to a "generic" current_date() database function... kind of like pseudocode... The actual function's name could be sysdate(), now(), 'current date'... most of this stuff is database-specific as you rightly noticed.

        Ok then. I don't know of an existing module that does that, so I'm guessing you'll have to roll your own. Shouldn't be that tough - you can use the database handle function get_info. Here are the get_info numbers that can be used as parameters:
        Type Name Example A Example B ---- -------------------------- ------------ ---------------- 17 SQL_DBMS_NAME 'ACCESS' 'Oracle' 18 SQL_DBMS_VER '03.50.0000' '08.01.0721 ...' 29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"' 41 SQL_CATALOG_NAME_SEPARATOR '.' '@' 114 SQL_CATALOG_LOCATION 1 2
        so you could use
        my $dbms_name = $dbh->get_info(17);
        your new function could take in the $dbh handle, and call get_info(17) to get the dbms name, and once you know the name, you can construct the database specific SQL that will get you the day of the week for that database.

        HTH.

Re: Testing and database date functions
by sgifford (Prior) on Dec 09, 2004 at 20:35 UTC

    To solve a similar problem, I wrote code to allow an environment variable to override the current date and time. The Perl code would use the real current time if the environment variable was unset, or would use the one from the environment if it was set. This did require some changes to the Perl code, but worked pretty well, and I can test how the code would work on different dates by changing the environment variable.

    Another possibility worth investigating is abusing time zones. Some systems will let you set a timezone offset of something like "72 hours" which will make the date appear forward or backwared a 3 days. If your database has some notion of per-connection timezones, a trick like that could work.

Re: Testing and database date functions
by iburrell (Chaplain) on Dec 09, 2004 at 21:29 UTC
    What we do at work is use a custom function instead of current_date(). The custom function, which is implemented in PL/SQL for Oracle and PL/pgSQL for Postgres, looks in a temporary table for a test date or uses the real current date. This allows us to set the "current date" that our SQL code sees.
Re: Testing and database date functions
by RiotTown (Scribe) on Dec 09, 2004 at 22:19 UTC
    Not sure if it is a viable option or not, but you could always just change the date on the database server. I've done that in the past for applications that make heavy use of SYSDATE (or their related friends) functions. Althought this doesn't work well if you are testing against a production db...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://413612]
Approved by Arunbear
Front-paged by Roy Johnson
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2024-03-28 13:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found