Deep Syntax in DBI

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

view plainprint?

1. my $out ;  
2.   
3. # This query gets everything out of the database  
4. my $query_all = '  
5.     SELECT  steps   
6.         ,   miles   
7.         ,   datestamp   
8.         ,   DAYNAME(datestamp) day  
9.         ,   DAYOFWEEK(datestamp) dow  
10.     FROM fitbit_daily   
11.     ORDER BY datestamp   
12.     DESC  
13.     ' ;  
14.   
15. # This query used a placeholder to just get better days  
16. my $query_high = '  
17.     SELECT  steps   
18.         ,   miles   
19.         ,   datestamp   
20.         ,   DAYNAME(datestamp) day  
21.         ,   DAYOFWEEK(datestamp) dow  
22.     FROM fitbit_daily   
23.     WHERE steps > ?  
24.     ORDER BY datestamp   
25.     DESC  
26.     ' ;  
27.   
28. # db_hashref() is a wrapper around fetchall_hashref()  

    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()

view plainprint?

1. # that looks like this :  
2.   
3. # sub db_hashref {  
4. #     my $sql = shift ;  
5. #     my $id = shift ;  
6. #     my $sth = _execute_query( $sql , @_ ) ;  
7. #     my $ptr = $sth->fetchall_hashref( $id ) ;  
8. #     return $ptr ;  
9. #     }  
10.   
11.   
12. $out = db_hashref( $query_all , 'datestamp' ) ;  
13. say Dumper $out ;  
14. # Returns all 960 values keyed by datestamp, keyed by datestamp   
15.   
16. $out = db_hashref( $query_high , 'datestamp' , 18000 ) ;  
17. say Dumper $out ;  
18. # Returns the four days where I actually was that active  
19.   
20. $out = db_hashref( $query_all , [ qw{ steps datestamp } ] ) ;  
21. say Dumper $out ;  
22. # Returns the a multidimentional hash, with the first key   
23. # being the steps and the next one being the datestamp.  
24. # Perhaps not the most useful, but I'm proving concepts here  

    # 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?