Deep Syntax in DBI

Let's look at some code. This uses DBI to talk to MySQL.

    my $out ;

    # This query gets everything out of the database
    my $query_all = '
        SELECT  steps 
            ,   miles 
            ,   datestamp 
            ,   DAYNAME(datestamp) day
            ,   DAYOFWEEK(datestamp) dow
        FROM fitbit_daily 
        ORDER BY datestamp 
        DESC
        ' ;

    # This query used a placeholder to just get better days
    my $query_high = '
        SELECT  steps 
            ,   miles 
            ,   datestamp 
            ,   DAYNAME(datestamp) day
            ,   DAYOFWEEK(datestamp) dow
        FROM fitbit_daily 
        WHERE steps > ?
        ORDER BY datestamp 
        DESC
        ' ;

    # db_hashref() is a wrapper around fetchall_hashref()

    # that looks like this :

    # sub db_hashref {
    #     my $sql = shift ;
    #     my $id = shift ;
    #     my $sth = _execute_query( $sql , @_ ) ;
    #     my $ptr = $sth->fetchall_hashref( $id ) ;
    #     return $ptr ;
    #     }


    $out = db_hashref( $query_all , 'datestamp' ) ;
    say Dumper $out ;
    # Returns all 960 values keyed by datestamp, keyed by datestamp 

    $out = db_hashref( $query_high , 'datestamp' , 18000 ) ;
    say Dumper $out ;
    # Returns the four days where I actually was that active

    $out = db_hashref( $query_all , [ qw{ steps datestamp } ] ) ;
    say Dumper $out ;
    # Returns the a multidimentional hash, with the first key 
    # being the steps and the next one being the datestamp.
    # Perhaps not the most useful, but I'm proving concepts here

The missing syntax is now that will get me the decent step days, in the cool multidimensional hashes that I've grown to love. I might have to take it back a bit to get that behavior. Suggestions?

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.

Petdance says if you have SELECT * in your code, you have a bug

Or, he retweeted it. I think. He knows where to yell at me if I got that wrong.

I don't really agree, except maybe the memory hit when you get tons more data than you expected or need, but the case I'm talking about here is a structured database dump. I'm proud of this query, and I'll expand on it.

        SELECT *
        FROM  sequence_sample
        LEFT JOIN accessions
            ON  sequence_sample.accession_id = accessions.accession_id
        LEFT JOIN requests
            ON  accessions.request_id        = requests.request_id 
        WHERE run_id = ?

A sample is an accession, except that a sample is an accession associated with a run, and that accession can be associated with many runs but only one request. I could see adding several joins to bring in controlled vocabularies, where, for example, the library types come in.

I've worked with SQL for a while, but only now am I integrating the joy of joins. I can thank Coding Horror for his excellent explanation on what the different joins mean, and Learning SQL for the use of ON to specify  the JOIN joins to distinguish it from WHERE which brings in the unique constraints. 

Learned some SQL today

Had to skip out of TEDxPurdueU today.

Which sucked.

There was a problem with my code at work, a problem I really couldn't handle from the front row.

We create runs. Each run has many regions. Each region has one or more accessions. Each accession has a well identifier to tell what part of the region it is in, and barcodes, which are identifiers. Problem is, barcodes associate with accessions, while wells associate with accessions, regions and wells. So, if you are putting one accession into several regions and wells, in a format like this

1,123123,Barcode1,Well1
1,123124,Barcode2,Well2
1,123125,Barcode3,Well3
1,123126,Barcode1,Well4
2,123123,Barcode1,Well4
2,123124,Barcode2,Well3
2,123125,Barcode3,Well2
2,123126,Barcode4,Well1

You now have set the barcodes twice for accessions 123123-123-126. Not good at all.

I first thought "I can check to see if that barcode is set, and skip it if it is". Then I thought, I could add some SQL to ensure it can't write again. That looks  a bit like this:

ALTER TABLE accession_barcode ADD CONSTRAINT accession_barcode UNIQUE ( accession , barcode )

Makes me wonder why I didn't do that in the first place.

How to manage several MySQL accounts

Here is something that I wish I had known a while ago.

So, I had my .my.cnf filled with the information for the login that was mine, which I used for test. Eventually, I started to work with a production database, and was given one login for general CRUD (generally for the web interface, but useful for day-to-day work as well) and one for creating and modifying tables.

I needed to be able to be in multiple ones at a time, which meant I couldn't just copy a config file with all the information about one setup to .my.cnf, then copy another when I needed that.

My initial take on the solution was to make a script and then an alias with all the correct login information (including the password), then call that when I wanted to get into the admin account. This puts the keys to the database into the process table, which is stupid and wrong and dangerous, but I didn't know a better way.

This morning, I thought to ask the DB StackExchange. Of course, I should've searched more thoroughly first. Here is what a sample .my.cnf would look like:

[clienttest]
host        = server.university.edu
user        = test
database    = test
password    = abc123

[clientprod]
host        = server.university.edu
user        = production
database    = production
password    = abc123

[clientadmin]
host        = localhost
user        = admin
database    = production
password    = abc123

[mysql]

prompt='mysql [\u][\d][\h]>'

You then start mysql by typing mysql --defaults-group-suffix=whatever, where the config says [clientwhatever].

That can, of course, be a [client] setting, so just typing mysql gets you into a default space. Adding aliases for easier typing is also useful.

alias test_db="mysql --defaults-group-suffix=test "
alias prod_db="mysql --defaults-group-suffix=prod "
alias admin_db="mysql --defaults-group-suffix=admin "

I think that prompt is too verbose, so mine is cut down to mysql [\u]>, but knowing which account you're in keeps you from accidentally killing something important.

And, of course, in the tradition of other pessimization removers I've blogged, it is good to have an alias to get into your .my.cnf.

alias mycnf="vi ~/.my.cnf"