Cookie Notice

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

2015/02/05

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?

No comments:

Post a Comment