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

Perl Monks, I am using timestamp with DBI::MySQL for the first time. I need to add entries to MySQL using hostname and timestamp. Before I add an entry, I need to check if the entry's timestamp is higher than the entries in MySQL. What is the best way to do this? Should I select all the entries by hostname and compare the timestamps in Perl or can I do the compare in MySQL and use the rows returned to determine if it is newer? Thanks in advance!

Replies are listed 'Best First'.
Re: Selecting most recent MySQL timestamp
by moritz (Cardinal) on Aug 11, 2009 at 07:10 UTC
    You can just compare it to the max of all the timestamps, in sql.
[reply]
Re: Selecting most recent MySQL timestamp
by JavaFan (Canon) on Aug 11, 2009 at 09:46 UTC
    You could add a trigger on the table which rolls back any insert of a row with a timestamp that doesn't exceed the maximum timestamp already in the table. Doing it with a trigger will prevent any application regardless of the language it's written in to insert "forbidden" timestamps. It also prevents race conditions.
[reply]
Re: Selecting most recent MySQL timestamp
by roboticus (Chancellor) on Aug 11, 2009 at 11:35 UTC
    Update: After re-reading the thread, it appears that I misunderstood the OPs requirements--I was allowing a timestamp to be updated that may be less than the maximum one in the table. The code should be more like:

    $ST=$DBH->prepare(<<EOSQL);
if not exists (select * from tbl where timestamp > ?)
   if exists (select * from tbl where hostname=?)
      update tbl set timestamp=? where hostname=?
   else
      insert tbl (hostname, timestamp) values (?, ?)
EOSQL

$ST->execute($timestamp, $hostname, $timestamp, $hostname, $hostname, 
+$timestamp);

    [download]
    ewhitt:

    Let SQL do some of the work for you:

    $ST=$DBH->prepare(<<EOSQL);
if not exists (select * from tbl where hostname=?)
   insert tbl (hostname, timestamp) values (?, ?)
else
   if not exists (select * from tbl where hostname=? and timestamp>?)
      update tbl set timestamp=? where hostname=?
EOSQL

$ST->execute($hostname, $hostname, $timestamp, $hostname, $timestamp,
             $timestamp, $hostname);

    [download]

    Note: I would actually use a stored procedure so I wouldn't have to repeat the $hostname and $timestamp variables in an odd order. But I'm not a MySQL user, so I thought I'd do it with basic SQL.

    This way, there's only one transaction with the database for each update: You just give the database a hostname and timestamp with a couple of decisions, and it'll do the rest.

    Usual disclaimers apply: Untested code, et cetera.

    ...roboticus
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom