Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Mocking time with SQLite

by jaldhar (Vicar)
on Dec 26, 2010 at 12:10 UTC ( #879208=perlquestion: print w/replies, xml ) Need Help??

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:



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; END;

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 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/ 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

      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.


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";

      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.


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?


      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 );


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://879208]
Approved by planetscape
Front-paged by Old_Gray_Bear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (2)
As of 2023-02-09 02:09 GMT
Find Nodes?
    Voting Booth?
    I prefer not to run the latest version of Perl because:

    Results (44 votes). Check out past polls.