Cookie Notice

As far as I know, and as far as I remember, nothing in this page does anything with Cookies.

2014/08/04

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