http://qs321.pair.com?node_id=879208

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

Normally when I test an application that makes use of times, I turn to Test::MockTime which works wonderfully for setting Perls notions of the current time to arbitrary values. In this app, however it doesn't work because the database has the following fields:

created   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

[download]

and...

CREATE TRIGGER IF NOT EXISTS update_modification_time 
        AFTER UPDATE OF title, body ON items
        FOR EACH ROW BEGIN
            UPDATE items SET modified = DATETIME('NOW') WHERE id = new
+.id;
        END;

[download]

Modules like Test::MockTime work by intercepting Perls time related functions but here the time is being set by the SQLite library (calling down to libc functions no doubt) and so this approach does not work. I can think of two possible alternatives:

There are C preload libraries like faketime which intercept C library time functions. Can something like that be used from within a Perl script?

DBD::SQlite lets you use sqlite_create_function. Could this be used to override SQLites time related functions? If so which ones?

Comments welcome on whether either of these ideas are feasable or if there are better solutions.

--
જલધર

Replies are listed 'Best First'.
Re: Mocking time with SQLite
by Khen1950fx (Canon) on Dec 26, 2010 at 12:56 UTC
    I tried DateTime::Format::SQLite. It'll parse a string into a format that SQLite understands. Note that SQLite uses UTC, so Test::MockTime may not be compatible with SQLite. Following the example from the SYNOPSIS: 
    #!/usr/bin/perl

use strict;
use warnings;
use DateTime::Format::SQLite;

my $dt = DateTime::Format::SQLite->parse_datetime( '2010-12-26 04:41:2
+0' );
print DateTime::Format::SQLite->format_datetime($dt), "\n";

    [download]
[reply]
[d/l]

      Sorry, I'm confused as to how DateTime::Format::SQlite (Which I am using btw just not in this test script) could help here. It converts SQLite date/time values to and from DateTime objects. My problem is the "wrong" value is in the database in the first place. I need to change SQLites notion of the current time.

      --
      જલધર

[reply]
Re: Mocking time with SQLite
by oko1 (Deacon) on Dec 26, 2010 at 14:54 UTC

    Based on the 'LD_PRELOAD' example shown at the 'faketime' page, it certainly appears to be a plausible solution. I can't see how you'd use it from within a Perl script, but launching the script with 

    LD_PRELOAD=/usr/local/lib/libfaketime.so.1 FAKETIME="-15d" ./my_sqlite_script

    should do it. (Thanks for the link, BTW; I'm actually going to download it and try it myself - it would solve a couple of minor testing situations for me. :) 


    -- 
Education is not the filling of a pail, but the lighting of a fire.
 -- W. B. Yeats
[reply]

      I thought maybe it could be used via some kind of XS wrapper or something. It's a moot point anyway as my alternative approach works now. This is much better as it will be portable to any platform whereas I suspect the preload library is Unix specific only.

      --
      જલધર

[reply]
Re: Mocking time with SQLite
by jaldhar (Vicar) on Dec 26, 2010 at 14:51 UTC

    Here's an update on how I'm progressing with this. I took the second approach from my initial post and added the following code to my script. 

    #This is a function to override SQLites DATETIME('NOW').

sub my_fake_time {

    # gmtime() normally returns the current time but it is overriden b
+y Test::MockTime to give a fake time.
    # Actually I am using the version of gmtime() from Time::gmtime as
+ it has a nicer interface.  As this too overrides 
    # Perls core gmtime() function it must be use'd after Test::MockTi
+me so all the overrides occur in the right order.
    my $t = gmtime;

    # return the fake date in the same format as SQLite returns.
    return sprintf '%04d-%02d-%02d %02d:%02d:%02d', $t->year + 1900,
        $t->mon + 1, $t->mday, $t->hour, $t->min, $t->sec;

};

...

# This works.  DATETIME('NOW') in my SQL now returns a fake time.  

$dbh->sqlite_create_function(
    'DATETIME', 1, \&my_fake_time
);

# However CURRENT TIMESTAMP still doesn't work even though from what I
+'ve read it is implemented in terms of DATETIME('NOW') 
#
# I tried this but it doesn't work.

$dbh->sqlite_create_function(
    'CURRENT TIMESTAMP', 1, \&my_fake_time
);

# Any other ideas?

    [download]

    --
    જલધર

[reply]
[d/l]

      Yet another update. It's CURRENT_TIMESTAMP (I left out the underscore.) Now this works:

      dbh->sqlite_create_function(
     'CURRENT_TIMESTAMP', 0, \&my_fake_time
 );

      [download]

      --
      જલધર

[reply]
[d/l]

Back to Seekers of Perl Wisdom