More Fun With SQL

I've mentioned this bit of code recently:

        INSERT INTO accession_analysis (
            accession_id ,  analysis_id ,
            reference_id ,  status ,
            status_text  ,  extra_parameters
            )
        SELECT
            accession_id ,
            ? , ? , ? , ? , ?, ?
        FROM accessions
        WHERE request_id = ?
        ORDER BY accession_id

Well, it's slightly different. Before we were just holding the current state ( waiting, working, success, failure ) and now we're holding text information, too, and you don't just want the current state, you want to be able to look back. So, in addition to accession_analysis , we're adding accession_analysis_status, which will have, so far, a unique id, an id for the AA it connects to, and then the the status information.

And the run now. I have to add a run number to the accession_analysis schema. I can do that, but that's not germane right now.

What is germane is how to store the status into accession_analysis_status at about the same time as it goes into accession_analysis, getting the id from the accession_analysis table. It would be far easier if I was using an iterative approach, but then I'm blasting the DB with many connections instead of just one.

An approach would be to find all the accession_analysis elements without a matching accession_analysis_status, and then inserting them into accession_analysis_status. Something like

INSERT INTO accession_analysis_status ( 
    aa_id , status , status_text 
    ) 
SELECT id , status ,status_text 
FROM accession_analysis 
WHERE there's no aa_id corresponding to the id in accession_analysis

But clearly, I don't know how to express this as SQL yet.

Command Line Comments

Just read Life on the Command Line, wherein the author explains how he does everything everything everything on the command line these days

I'm largely sympathetic. Honestly. And I'm close. Look at my work Linux box and you'll see a good half-dozen terminal windows open. I'm a programmer whose two primary programming styles are command-line/batch/crontab and web programming, so I am much more comfortable with those styles and their explicit order than the GUI developers.

But I have sort of a guilty secret.

I spend most of my programming time using KomodoEdit.

I have a fairly custom .vimrc that allows me to do cool things in vi, but beyond crontab editing and other things that look for $EDITOR, I only use vi when I want to do lots of specific and repetitive find-replace stuff or need to do small changes, or when I'm using SSH to connect to a system and not using SSHFS to mount it. My heavy lifting for editors is done via KomodoEdit.

And that isn't it. Not by a long shot.

I have a significant amount of music. I used to use Rhythmbox as my media player because it allowed me to use and thus alias a command-line interface. On that system, I would have rhythmbox-client --play ,rhythmbox-client --pause , rhythmbox-client --previous and rhythmbox-client --next aliased to play , pause , prev and next .

I stopped for a few reasons. I now have a Windows machine that I use for testing web dev in different browsers, and, when I play my media, I play via Windows Media Player, in part because playing and queueing media that I have but didn't have in my media library made Rhythmbox unhappy. But, between Amazom, Google, Rdio, Pandora and Spotify (which I love), I hardly listen to that huge library anymore, because either I have much of that library up already or I'm streaming stuff I want but don't have. And those tools don't give me a command-line option.

It gets to what tool is powerful enough to allow me to do what I need to. With music, I need it on and going, and paused on occasion.

Don't get me wrong. I'm with him at least in part about mail sorting. Before web tools and Thunderbird, my preferred mail client was PINE, but there's functional reasons that I can't remember anymore that I moved the one remaining command-line mail account from the nmap-based sorting from mh/nmh to procmail. IIRC, I liked the syntax for mh better but I found that SpamAssassin worked better with procmail, and I desperately needed SpamAssassin. The sorting choices I get from both Gmail and Zimbra are both clunkier than a nice mh or procmail script. But I spend little of my time in email, so that shouldn't make much of a difference.

Anyway, interesting read.

Fun with SQL

If you want to put one thing into a database, that's easy.

 INSERT INTO table_1 (field_1 , field_2 , field_3 ) VALUES ( 4 , 1 , 2 ) ; 

I've been putting lots of things into table_1 that are in table_2, where there are n instances of table_1 in each table_2. This lead me to something like this.

 
my $sql ;
$sql = << 'SQL'
SELECT id FROM table_2 WHERE field_3 = ?
SQL
my $ptr = db_arrayref( $sql , $param->{ field_3 } ) ;

$sql = << 'SQL'
INSERT INTO table_1 ( 
    field_1 , field_2 , field_3 
    ) 
VALUES (
    ? , ? , ?
    )
SQL

map {
   my @vals ;
   push @vals , $param->{ a } ;
   push @vals , $param->{ b } ;
   push @vals , $_ ;
    }  @$ptr ;

That's OK for what it is, but what that ends up meaning is lots of small SQL commands sent to the server. SQL handles the commands well, but the opening and closing of network connections is just sort of sucky, so, that's not optimal. So, if you can just send one command, that's what you want. And in this case, the command is this:

 
INSERT INTO table_1 ( field_1 , field_2 , field_3 ) 
    SELECT 
    1 ,
    2 ,
    field_3 FROM table_2 WHERE field_3 = ?
    ; 

Isn't SQL wonderful?

Failure to Plan is Planning to Fail

I have a database table full of requests. When that table is filled, we also put together a wiki page for that request, because the the data we generate for each request can be fairly free-form. This means that we effectively divorce the state of the DB for a given request and the state of the wiki.

There are certain fields that are on the wiki page and not the database. Specifically, contact information for the person making the request. It made sense to me, at the time. I think it was based upon the data separation issue, or that we actually hold that in a profile, too, but I can't remember right now.

Now, my boss wants to be able to regenerate the wiki page for quick-and-easy refresh. Regenerate the DB stuff into a new wiki page and cut-and-paste the non-DB stuff from the old page to the new page. Easy-peasy. Except, I don't store all the data in the database.

So, now, I'm looking at adding columns to the table to handle said contact information and spidering the contact info off the wiki pages. If I had just put it into the database in the first place...

More Javascript Didacticisms: Namespaces

For the draft page in the Blogger dashboard, I'm seeing sixteen javascript libraries and five extensions running in Chrome. That's a lot. Think about that next time you type var scratch = "something" .

If you want to use scratch and so does the person who wrote the popup library, your code might not work. I learned this when I was writing test code to properly create objects for jQuery's post(). I was sharing a variable that contained the URL for the JSON I was grabbing, and when I had Before commented out, After worked perfectly, but otherwise, it all crashed.

The solution? Objects.

var my_ns = {
    foo : x' ,
    bar : 14 ,
    blee : function () {
        ...
        }
    }

$(function() {
    my_ns.blee()
    } )

Thank you, Stack Overflow!

I have to thank Stack Overflow again.

I code everything from SQL to Javascript, and that means that I don't really get to become expert in everything. I do alright, but there's occasionally things I just don't get, and I don't really have a community of developers around me to ask.

My most recent problem relates to hashes in Javascript connecting to JSON. You can make arrays behave like hashes in Javascript:

var hash = new Array()

hash['foo'] = 1

alert( hash.foo )

My problem was with post, the jQuery method to do AJAX with large data sets. This didn't work.

$.post( url , hash , function() { ... } , 'json' )

Because hash is an abused array, not an object. Before I figured it out, today, thanks to SO, I make a long string that looked like { 'foo' : '1' , 'bar' : '2' } and ran eval on it. Bad bad bad bad bad. I now know that it should work like this:

var hash = {}

hash['foo'] = 1


$.post( url , hash , function() { ... } , 'json' )

Thanks again, StackOverflow!

What to do with massive amounts of computrons, plus quitting in front of people

I am in the bioinformatics space. Irony is, there's no way I could've paid to attend OSCON. 

Dialup Slowdown

This is the dialup handshake, slowed down 700x.

I used to have a 2400 baud (call it ~2Kb/m) and I'm now getting 5MB/s, which is more than a 700x speedup.

My .perltidyrc

Especially when working with others, it is important to be able to format your code. Perl Best Practices convinced me to start using PerlTidy, and this is my current .perltidyrc.

# my .perltidyrc config file
#   http://perldoc.perl.org/perlstyle.html

  --backup-and-modify-in-place
  #--cuddled-else
  --continuation-indentation=4
  --indent-columns=4
  --maximum-line-length=78
  #--line-up-parentheses
  --opening-brace-always-on-right
  --noopening-sub-brace-on-new-line #  -nsbl        # opening sub braces on right
  --indent-closing-brace  # thought this was -ibc
  --indent-block-comments #  -icb         # indent comment blocs
  --vertical-tightness=0
  --vertical-tightness-closing=0
  --closing-token-indentation=3
  --stack-opening-tokens
  #--stack-closing-tokens
  --space-terminal-semicolon
  --space-for-semicolon

  --brace-tightness=0
  --paren-tightness=0
  --block-brace-tightness=0
  --square-bracket-tightness=0
  --no-outdent-long-quotes

I Keep Telling People!

This guy on Reddit points out that, in 1996, a simple statement of many of our regular lives would be taken as science fiction.

Mary pulled out her pocket computer and scanned the datastream. It established contact with satellites screaming overhead, triangulated her position, and indicated there was an available car just a few blocks away; she swiped her finger across the glass screen to reserve it. A few minutes later, she spotted the little green hatchback and tapped her bag against the door to unlock it. "Bummer," she said as she glanced at her realtime traffic monitor. "Accident on the Bay Bridge. I'll have to take the San Mateo. Computer, directions to Oakland airport. Fastest route." Meanwhile, she pulled up Kevin's flight on the viewscreen. The plane icon was blipping over the Sierra Nevadas and arrival would be in half an hour. She wrote him a quick message: "Running late. Be there soon. See if you can get a pic of the mountains for our virtual photospace."

This is not a new thought. I worked in a used bookstore during my first sojourn in academia, around the summer of 1991, five years before the point in question. At that time, I was big into cyberpunk science fiction. Bruce Sterling and William Gibson. I was putting things away, cleaning up the place, when I saw a book centered upon a computer hacker. It wasn't in the science fiction section. It wasn't even in the true crime section like Cliff Stoll's The Cuckoo's Egg. It was in mystery. That was the point where science fiction stopped being about futurism for me.