I get why I was wrong. Re: Database WTF

In a line, date_time NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Specifically, ON UPDATE CURRENT_TIMESTAMP. I did not want that. I wanted to default to current timestamp, but I didn't want it to change. Well, in one case, I think. Not here.

 D'oh!

A Database WTF

Database table exists, and has three columns worth mentioning:

• run_id - the key to the table
• date_time - timestamp created on row creation
• ack_error - a boolean set to determine if errors are silenced.

This is my test code. It includes some functions I import.

• db_do - Given SQL statements, does them. Used for INSERT, UPDATE, REPLACE, DELETE.
• db_arrayref - Given SQL statements, does then and returns the result. Used for SELECT.
• confirm_error_run - Given a run_id, gives current state of ack_error.
• ack_run_error - Given a run_id, sets ack_error to true.
• clear_run_error - Given a run_id, sets ack_error to false. 

view plainprint?

1. sub check_date {  
2.     my $run_id     = shift ;  
3.     my $check_date = q{  
4.     SELECT date_time date  
5.     FROM sequence_run  
6.     WHERE run_id = ?  
7.     } ;  
8.     my $date = db_arrayref( $check_date, $run_id ) ;  
9.     return $date->[ 0 ]->[ 0 ] ;  
10.     }  
11.   
12. my $SQL = 'UPDATE sequence_run SET ack_error = ? where run_id = ? ' ;  
13.   
14. $DB::Database = 'genomicsdb' ;  
15. my $run_id = 318 ;  
16. my $date ;  
17. my $error ;  
18.   
19. say $run_id ;  
20.   
21. $date = check_date( $run_id ) ;  say $date ;  
22. $error = confirm_error_run( $run_id ) ; say $error ;  
23.   
24. my $do = db_do( $SQL , '1' , $run_id ) ;  
25. say $do ;  
26.   
27. say '-' x 40 ;  
28. $date = check_date( $run_id ) ;  say $date ;  
29. $error  = confirm_error_run( $run_id ) ; say $error ;  
30.   
31. ack_run_error( $run_id ) ;  
32.   
33. say '-' x 40 ;  
34. $date = check_date( $run_id ) ;  say $date ;  
35. $error  = confirm_error_run( $run_id ) ; say $error ;  
36.   
37. clear_run_error( $run_id ) ;  
38.   
39. say '-' x 40 ;  
40. $date = check_date( $run_id ) ;  say $date ;  
41. $error  = confirm_error_run( $run_id ) ; say $error ;  

    sub check_date {
        my $run_id     = shift ;
        my $check_date = q{
        SELECT date_time date
        FROM sequence_run
        WHERE run_id = ?
        } ;
        my $date = db_arrayref( $check_date, $run_id ) ;
        return $date->[ 0 ]->[ 0 ] ;
        }

    my $SQL = 'UPDATE sequence_run SET ack_error = ? where run_id = ? ' ;

    $DB::Database = 'genomicsdb' ;
    my $run_id = 318 ;
    my $date ;
    my $error ;

    say $run_id ;

    $date = check_date( $run_id ) ;  say $date ;
    $error = confirm_error_run( $run_id ) ; say $error ;

    my $do = db_do( $SQL , '1' , $run_id ) ;
    say $do ;

    say '-' x 40 ;
    $date = check_date( $run_id ) ;  say $date ;
    $error  = confirm_error_run( $run_id ) ; say $error ;

    ack_run_error( $run_id ) ;

    say '-' x 40 ;
    $date = check_date( $run_id ) ;  say $date ;
    $error  = confirm_error_run( $run_id ) ; say $error ;

    clear_run_error( $run_id ) ;

    say '-' x 40 ;
    $date = check_date( $run_id ) ;  say $date ;
    $error  = confirm_error_run( $run_id ) ; say $error ;

Changes to ack_error should be immaterial to date_time, right? Yet...

djacoby@genomics 12:00:44 ~ $ ./test.pl 
318
2014-07-31 14:36:43
1
1
----------------------------------------
2014-07-31 14:36:43
1
----------------------------------------
2014-07-31 14:36:43
1
----------------------------------------
2014-08-04 12:22:09
0

I just don't get it. ack_run_error() and clear_run_error() are essentially like the db_do( $SQL , ... ), and it's somewhat nondeterministic whether my db_do() and ack_run_error() reset the time. Confusing.