Having Problems Munging Data in R

#!/group/bioinfo/apps/apps/R-3.1.2/bin/Rscript

# a blog post in code-and-comment form

# Between having some problems with our VMs and wanting 
# to learn Log::Log4perl. I wrote a program that took 
# the load average -- at first at the hour, via 
# crontab -- and stored the value. And, if the load 
# average was > 20, it would send me an alert

# It used to be a problem. It is no longer. Now I 
# just want to learn how to munge data in R

# read in file
logfile = read.table('~/.uptime.log')

# The logfile looks like this:
#
#   2017/01/01 00:02:01 genomics-test : 0.36 0.09 0.03
#   2017/01/01 00:02:02 genomics : 0.04 0.03 0.04
#   2017/01/01 00:02:02 genomics-db : 0.12 0.05 0.01
#   2017/01/01 00:02:04 genomics-apps : 1.87 1.24 0.79
#   2017/01/01 01:02:02 genomics-db : 0.24 0.14 0.05
#   2017/01/01 01:02:02 genomics-test : 0.53 0.14 0.04
#   2017/01/01 01:02:03 genomics : 0.13 0.09 0.08
#   2017/01/01 01:02:04 genomics-apps : 1.66 1.82 1.58
#   2017/01/01 02:02:01 genomics-test : 0.15 0.03 0.01
#   ...

# set column names
colnames(logfile)=c('date','time','host','colon','load','x','y')

# now:
#
#   date       time     host         colon load x y
#   2017/01/01 00:02:01 genomics-test : 0.36 0.09 0.03
#   2017/01/01 00:02:02 genomics : 0.04 0.03 0.04

logfile$datetime <- paste( as.character(logfile$date) , as.character(logfile$time) )
# datetime == 'YYYY/MM/DD HH:MM:SS'
logfile$datetime <- sub('......$','',logfile$datetime)
# datetime == 'YYYY/MM/DD HH'
logfile$datetime <- sub('/','',logfile$datetime)
# datetime == 'YYYYMM/DD HH'
logfile$datetime <- sub('/','',logfile$datetime)
# datetime == 'YYYYMMDD HH'
logfile$datetime <- sub(' ','',logfile$datetime)
# datetime == 'YYYYMMDDHH'

# for every datetime in logfile. I love clean data

# removes several columns we no longer need

logfile$time    <- NULL
logfile$date    <- NULL
logfile$colon   <- NULL
logfile$x       <- NULL
logfile$y       <- NULL

# logfile now looks like this:
#
#   datetime  host             load
#   2017010100 genomics-test    0.36 
#   2017010100 genomics         0.04 
#   2017010100 genomics-db      0.12 
#   2017010100 genomics-apps    1.87 
#   2017010101 genomics-db      0.24 
#   2017010101 genomics-test    0.53 
#   2017010101 genomics         0.13 
#   2017010101 genomics-apps    1.66 
#   2017010102 genomics-test    0.15 
#   ...

# and we can get the X and Y for a big huge replacement table
hosts <- unique(logfile$host[order(logfile$host)])
dates <- unique(logfile$datetime)

# because what we want is something closer to this
#
#   datetime        genomics    genomics-apps   genomics-db     genomics-test
#   2017010100      0.04        1.87            0.12            0.36
#   2017010101      0.13        1.66            0.15            0.53
#   ...

# let's try to put it into a dataframe

uptime.data <- data.frame()
uptime.data$datetime <- vector() ;
for ( h in hosts ) {
    uptime.data[h] <- vector()
    } 

# and here, we have a data frame that looks like 
#
#   datetime        genomics    genomics-apps   genomics-db     genomics-test
#
# as I understand it, you can only append to a data frame by merging.
# I need to create a data frame that looks like
#
#   datetime        genomics    genomics-apps   genomics-db     genomics-test
#   2017010100      0.04        1.87            0.12            0.36
#
# and then merge that. Then do the same with 
#
#   datetime        genomics    genomics-apps   genomics-db     genomics-test
#   2017010101      0.13        1.66            0.15            0.53
#
# and so on.
#
# I don't know how to do that. 
#
# I *think* the way is make a one-vector data frame:
#
#   datetime        
#   2017010101      
#
# and add the vectors one at a time.

for ( d in dates ) {

    # we don't and the whole log here. we just want 
    # this hour's data
    # 
    #   datetime  host             load
    #   2017010100 genomics-test    0.36 
    #   2017010100 genomics         0.04 
    #   2017010100 genomics-db      0.12 
    #   2017010100 genomics-apps    1.87 
    log <- subset(logfile, datetime==d)

    print(d)

    for ( h in hosts ) {
        # and we can narrow it down further
        # 
        #   datetime  host             load
        #   2017010100 genomics         0.04 
        hostv <- subset(log,host==h)
        load = hostv$load 
        # problem is, due to fun LDAP issues, sometimes 
        # the logging doesn't happen
        if ( 0 == length(load) ) { load <- -1 }
        print(paste(h, load ))
    }

    # and here's where I'm hung. I can get all the pieces 
    # I want, even -1 for missing values, but I can't seem  
    # to put it together into a one-row data frame
    # to append to uptime.data. 

    #   [1] "2017010100"
    #   [1] "genomics 0.04"
    #   [1] "genomics-apps 1.87"
    #   [1] "genomics-db 0.12"
    #   [1] "genomics-test 0.36"
    #   [1] "2017010101"
    #   [1] "genomics 0.13"
    #   [1] "genomics-apps 1.66"
    #   [1] "genomics-db 0.24"
    #   [1] "genomics-test 0.53"
    #   [1] "2017010102"
    #   [1] "genomics 0.36"
    #   [1] "genomics-apps 0.71"
    #   [1] "genomics-db 0.08"
    #   [1] "genomics-test 0.15"

}

On Not YET Contributing to an Open Source Project

Not the module in question. 

I had an idea, and the shortest path between here and a working implementation is through CPAN, so I found a module and tried to install it.

No go. Failed tests.

So I find the GitHub repo and make an issue.

That doesn't get me any closer to a working implementation of my idea, nor does it involve me coding or running code. We can't have that, so I took the next step, which was forking, cloning and branching the code, then playing with it to find out what's going on and why.

I won't tell you which repo; that's really besides the point. I've dealt with Perl enough to know that, when the module was last updated, everything was tested and everything worked. There is therefore no condemnation for those who are in CPAN.

Simply speaking, at some point, the API the module is interacting with changed how it works, returning an image instead of JSON that would contain the URL of said image. Not an unreasonable thing to do, I think. That saves you a step, and for my purposes, I'd never have need to get either the image or the location of the image. I feel free to believe that force installing the module would get me
an acceptable outcome.

But if I know a thing is broken and I know how to fix it, and I don't fix it, I'm simply leaving the world in a broken state. That's hardly a responsible response.

The question becomes "Just how do I fix this?", and I see three choices:

• FOLLOW THE TEST! The test wants an object which is converted from the JSON the API returns. I could easily skip the API call and just return http://example.com/{whatever}/image/. This will even be a valid URL, but when the API changes again, which it will, the URL will be inaccurate. This seems brittle to me.
• CHANGE THE TEST! The API wants to pass back an image. I can pass that on through and rewrite the test. But the module isn't brand new, which means that someone out there is using the failing function to get the URL of the image and this change will brake that existing code. It's probably already broken; this module won't install, as established, but this is a significant change in the API, which shouldn't be made without consideration. 
• DROP IT ALL! Remove the function that causes the problem. Remove the tests. Leave only a stub saying "This functionality has been removed". In some ways, this is the coward's way out, but it would be the simplest thing. Easier to remove functionality than to add it.

After I thought about it, I was leaning toward an All-Three approach: Making three branches, implementing each idea, then submitting three parallel pull requests, leaving it to the maintainer to decide the proper choice.

Yeah, that's not a smart plan. 2/3 of that work is going to be not used, by definition. After consultation with my advisors (read, anyone who would listen on Twitter, IRC or Hangouts), I decided that asking before coding anything, so I added details to my (admittedly short and incomplete, submitted before understanding) issue to say "I see three alternatives; what do you want me to do?"

But that is the proper way to do it. It isn't like I went "I want a feature, and here it is." I just want a working module to do the thing I'm thinking of (which might not be as cool as I thought it was), and the things I could do to make it work again could bug the people actually using it. The community of users.

As always, this is a point where xkcd understands and explains all.

So, I have been thinking about this, asking about this and writing about this, rather than implementing the initial idea, or even fixing the problem.

Which frustrates me to no end.

My Reason to hate Python might be GONE!

Let me show you some code.

#!/usr/bin/env python

mylist = [0,1,2,3]

for n in mylist:
 for  m in mylist:
  print m,n
    print m,n
 print n

Looks pretty normal, right? Just a loop, right? Just a loop within a loop.

Yes it is, but if you look closer, you'll notice two spaces in front of the second print statement.

This is exactly what happened to me the first time I tried Python, about 15 years ago. It was code that showed open machines in ENAD 302, and I ran it on an NT machine I had installed ActiveState Python on. I no longer have that job, thus no longer have that machine and that version of Python. I no longer can find the code, and the computer lab in ENAD 302 is gone.

As is ENAD.

All I have is the memory of having pages of error reports that didn't tell me that the problem was that, halfway into a 200-or-so line Python program. This has lead me to set expandtab or the equivalent for every editor I've used since. Burn me once, shame on you, but burn me twice...

I admit that disliked Python before that, but then it was more "Perl does this already, so why do I have to learn how to do the exact same thing in another dynamic language? What do I gain?" rather than "This language takes as a core feature a means to create undetectable errors."

But no. My hatred of Python stopped coming from a logical place. "Creates undetectable errors" is a logical argument, one that is no longer true, but I got taken to a place of negative emotion, like someone who was bitten by a dog as a child and now is overcome with fear or hate when one comes up now.

(I tried it a few times since, and each time, my experience said "this is an objectively stupid way of doing things", until I bumped into things like Sikuli or my FitBit code where there was either no other way or this was the easiest way to get to "working".)

Then I find someone online who says "tabs are better than spaces". For outputting formatted text, I do agree, but in code, that leads to invisible bugs. So, when someone is wrong online, you correct them.

But then, I wrote the above code, expecting the same errors and received none.

(In this process, I learned some interesting things about Sublime Text, like the way you can set color_scheme and draw_white_space and translate_tabs_to_spaces at a language-specific level, which I did to allow me to see the white space when writing the above code. Sublime Text is neat.)

I've been saying this for a while, but I think this is the last thing I needed to find out before I lived it: the Python that's here today is not the Python that bit me 15 years ago, and I should get over my hangups and "pet the dog". 

Death of a Project

Years ago, I learned some R. When I was doing so, I had decided to move from just being a vi man to trying something a little more modern, so I was using ActiveState's KomodoEdit.

A problem was that KomodoEdit had syntax highlighting for many languages, but not R. So, I did some digging, found some code that did what I wanted that someone else abandoned, adapted it some and made it work. Then I made it a GitHub repo called RLangSyntax. I had an itch, I scratched it, I made the backscratcher available to others and I went on with my life, eventually moving to SublimeText.

Until ActiveState released Komodo(IDE|Edit) 9, and I started getting issues in my repo. Those issues were solved by incrementing maxVersion from 8 to 9, and then by remembering and documenting the build process and adding a release download of the resulting xpi. And, because I've gone on to other languages and editors, I left it.

Until ActiveState released KomodoIDE X for 10. I figured that, as with 9, I'd eventually get issues, and so I decided to jump ahead of it. I installed KomodoIDE, tried to install RLangSyntax to see what the errors were. I contacted the support team and asked them a few questions, like "can I just assume Komodo will keep the same engine for syntax highlighting, so I can set maxVersion as 20 or something and let it go?"

Here I wish to say that, for both the 8->9 and 9->X conversions, the Komodo support team was helpful, friendly and intense. A bit more intense than I like, but being helpful and friendly leads me to forgive a lot. I use Sublime Text and vi for my editing needs, but I certainly feel that Komodo could work for me. I like how KomodoIDE takes it's cue from Sublime Text and Atom.

But I make the minimum changes to get it to install and start waiting for the automated checking of their version of Package Manager will take place, and I get this comment:

"Hey guys, I do want to point out that as of Komodo 9.3 we have built-in support for R lang syntax."

Ahem.

I'm happy that happened. I really am. R is really not a thing I touch anymore. I look at ggplot2 and think "Hey, I could make really pretty plots with that", but I don't have reason to make new plots or change old plots right now. And, I'm very happy with my Sublime Text environment. This is an important change for both R and KomodoIDE, and I'm happy.

But, of all the toys I released on GitHub, this is the one that had the most interaction, which implies the most use. One measure of value as a programmer is the number of users your code has, and the change, while of use to those who code in R with KomodoIDE, makes me a programmer of lesser import. But, really, not so much, because beyond packaging and iterating maxVersion, I didn't add much.

So, that repo's documentation now reads: "You probably don't want to use this project. So long, and thanks for the fish."

a DRY KISS

I've been working on a tool. I discussed a lot of it yesterday.

I had a model to get the information based on PI, and I wanted to get to what I considered the interesting bit, so it was only after the performance went from suck to SUCK that I dove back, which is what I did yesterday. Starting with the project instead of the PI made the whole thing easier. But now I'm stuck.

The only differences between these queries are on lines 14, 19 and 20. Which gets to the problem. I know that I don't need half of what I get in lines 1-11, but when I pull stuff out, I now have two places to pull it.

I have a great 90-line majesty of a query that includes six left joins, and I append different endings depending on whether I want to get everything, or a segment defined by A or B or something. I could probably tighten it up so I have SELECT FROM, the different blocks, then AND AND ORDER BY. But there we're adding complexity, and we're butting Don't Repeat Yourself (DRY) against Keep It Simple, Stupid (KISS).

I'm happy to keep it as-is, as a pair of multi-line variables inside my program. I think I'd rather have the two like this than gin up a way to give me both, so KISS over DRY, in part because I cannot imagine a third way I'd want to access this data, so we hit You Ain't Gonna Need It (YAGNI).

But if there's strong reasons why I should make the change, rather than package it up and put it in cron, feel free to tell me.

This is only kinda like thinking in algorithms

I have a program. It has two parts: get the data and use the data.

"Get the data" involves several queries to the database to gather the data, then I munge it into the form I need. Specifically, it's about people who generate samples of DNA data (called "primary investigator" or PI for those not involved in research), a little about the samples themselves, and those that the data are shared with.

"Use the data" involves seeing how closely the reality of the file system ACLs is aligned with the ideal as expressed by the database.

I expected that I'd spend 5% of my time, at worst, in "get the data" and 95% of my time in "use the data". So much so, I found a way to parallelize that part so I could do it n projects at a time.

In reality, it's running 50-50.

It might have something to do with the lag I've added, trying to throw in debugging code. That might've made it worse.

It might have something to do with database access. For this, I think we take a step back.

We have several database tables, and while each one rarely changes, they might. So, instead of having queries all over the place, we write dump_that_table() or the like. That way, instead of digging all over the code base for SELECT * FROM that_table (which, in and of itself, is a bug waiting to happen) (also), you go to one function and get it from one place.

So, I have get_all_pi_ids() and get_pi(), which could not be pulled into a single function until I rewrote the DB handling code, which now allows me to make [ 1 : { id:i, name:'Aaron A. Aaronson", ... }, ... ] to put it in JSON terms. Right now, though, this means I make 1 + 475 database calls to get that list.

Then I get all that PI's share info. This is done in two forms: when a PI shares a project and when a PI shares everything. I start with get_own_projects() and get_other_pi_projects(), which get both cases (a project is owned by PI and a project is shared with PI). That makes it 1 + ( 3 * 475) database calls.

I think I'll stop now, because the amount of shame I feel is still (barely) surmountable, and I'm now trying to look at the solutions.

A solution is to start with the projects themselves. Many projects are on an old system and we cannot do this mess with, and there's a nice boolean where we can say AND project.is_old_system = 0 and just ignore them. Each project has an owner, and so, if we add PI to the query, we lose having to get it special. Come to think of it, if we make each PI share with herself, we say goodbye to special cases altogether.

I'm suspecting that we cannot meaningfully handle both the "share all" and the "share one" parts in one query. I'm beginning to want to add joins to MongoDB or something, which might just be possible, but my data is in MySQL. Anyway, if we get this down to 2 queries instead of the nearly 1500, that should fix a lot of the issues with DB access lag.

As, of course, will making sure the script keeps DB handles alive, which I think I did with my first interface but removed due to a forgotten bug.

So, the first step in fixing this mess is to make better "get this" interfaces, which will allow me to get it all with as few steps as possible.


(As an aside, I'll say I wish Blogger had a "code" button along with the "B" "I" and "U" buttons.)

Logging, Plotting and Shoshin: A Developer's Journey

I heard about Log::Log4perl and decided that this would be a good thing to learn and to integrate into the lab's workflow.

We were having problems with our VMs and it was suggested I start logging performance metrics, so when we go to our support people, we have something more than "this sucks" and "everything's slow".

So, I had a reason and I had an excuse, so I started logging. But logs without graphs are boring. I mean, some logs can tell you "here, right here, is the line number where you are an idiot", but this log is just performance metrics, so if you don't get a graph, you're not seeing it.

That tells a story. That tells us that there was something goofy going on with genomics-test (worse, I can tell you, because we had nothing going on with genomics-test, because the software we want to test is not working yet. There was a kernel bug and a few other things that had fixed the other VMs, but not that one, so our admin took it down and started from scratch.

Look at that graph. Do you see the downtime? No?

That's the problem. This shows the last 100 records for each VM, but for hours where is no record, there should be -1 or a discontinuity or something.

I generally use R as a plotting library, because all the preformatting is something I know how to do in Perl, my language of choice, but I've been trying to do more and more in R, and I'm hitting the limits of my knowledge. My code, including commented-out bad trails, follows.

My thought was, hey, we have all the dates in the column final$datetime, and I can make a unique array of them. My next step would be to go through each entry for dates, and, if there was no genomicstest$datetime that equalled that date, I would throw in a null or a -1 or something. That's what the ifelse() stuff is all about.

But, I found, this removes the association between the datetime and the load average, and the plots I was getting were not the above with gaps, as it should be, but one where I'm still getting high loads today.

Clearly, I am looking at R as an experienced Perl programmer. You can write FORTRAN in any language, they say, but you cannot write Perl in R. The disjunct between a Perl coder codes Perl and an R coder codes R is significant. As a Perl person, I want to create a system that's repeatable and packaged, to get the data I know is there and put it into the form I want it to be. The lineage of Perl is from shell tools like sed and awk, but it has aspirations toward being a systems programming language.

R users are about the opposite, I think. R is usable as a scripting language but the general case is as an interactive environment. Like a data shell. You start munging and plotting data in order to discover what it tells you. In this case, I can tell you that I was expecting a general high (but not terribly high; we have had load averages into the hundreds from these VMs), and that the periodicity of the load came as a complete surprise to me.

(There are other other differences, of course. Perl thinks of everything as a scalar, meaning either a string or a number, or an array of scalars, or a special fast array of scalars. R thinks of everything as data structures and vectors and such. Things I need to integrate into my head, but not things I wish to blog on right now.)

The difference between making a tool to give you expected results and using a tool to identify unexpected aspects is the difference, I believe, between a computer programmer and a data scientist. And I'm finding the latter is more where I want to be.

So, I want to try to learn R as she is spoke, to allow myself to think in it's terms. Certainly there's a simple transform that gives me what I want, but I do not know it yet. As I do it, I will have to let go of my feelings of mastery of Perl and allow myself to become a beginner in R.

But seriously, if anyone has the answer, I'll take that, too.

I Need To Write This Up To Understand It, or The Epic Battle between Wat and Derp!

I've signed up for Neil Bowers' Pull Request Challenge, as I've blogged about before.

This month, I'm working with System::Command. It's neat, a step up from system calls, and as I understand it, integral to handling Git programmically, within Git::Repository. If I wasn't elbow-deep, I might try to use it as part of my Net::Globus project.

But the challenge isn't to use a module, it's to commit changes to a project. I wrote book, and he suggested writing a test to catch a bug that shows up in Windows. There's lots of "what even?" that's been going along with this, the great majority has a lot to do with the difference between coding for your self and your lab and coding for a community. I mean, I really do not get what's going on here, and you know that old joke? "Doctor, it hurts when I do this!" "Don't do that, then!" I keep thinking "If this is breaking your code, do something else!"

But, that sort of thinking doesn't get your code merged. So, I'm proceeding, my mind becoming the ring for an epic cage match between Wat and Derp.

One of the points of Derp (thanks #win32 on irc.perl.org) is that warnings are not exceptions. Since the error as given has a distressingly consistent ability to lock up Powershell session, I hadn't thought of that. It's lead me to think about cutting back to the least amount of code you can include to get a behavior you want to test for. In this case, it isn't just giving the wrong result, it's kicking up a lot of errors. Or, rather, warnings.

Among the questions on the $64,000 Pyramid are:

• "Why does Git Bash shell show the IPC::Run::Win32Pump errors, after the next prompt shows up, while Powershell doesn't?" 
• "Is that significant?"
• "What, then, is killing the great Powershells of my laptop?"
• "How do you trap that?" 
• "Isn't that more a test issue or a program issue?"
• "How, then, do you turn this mess into a meaningful test?"

That last one is what I'll be sleeping on tonight. On the one hand, turn this into a .t file and any warning, any at all, would be enough to trigger the test, as it's demonstrated that no errors occur in Linux. On the other, a test that says "Yeah, this kicks up errors" seems less-than-helpful, so specifying the GEN7 and GEN11 seems like the kind of thing that could break on another system.

I suppose I could write both a general and specific error.

Any comments, either supportive ("Yeah, that's exactly the kind of test you need!"), inquisitive ("Can I use BEGIN blocks in my other work?") or dismissive ("_That_ is a stupid way to test this out!") will be read and learned from. 

Meanwhile, I wrote this after my bedtime, so good night.

Fixing an old logic issue

I am not especially proud of the code below.

It does it's job. Give it a request and a number of accessions and the names you want them to go by, and it changes them in the database.

Except...

Accessions are defined as zero-padded six-digit numbers, so instead of 99999, you'd have 099999. If you're strict, everything's fine.

But user's are not always strict. Sometimes they just put in 99999, expecting it to just work.

Oh, if only it were that easy.

I have requests here for the purpose of ensuring that for request 09999, you can only change accessions associated with that request. This is what lines 27-29 are for, to get the set of accessions that are entered by the user and one of the given request's accessions.

Yes, requests are defined as zero-padded five-digit numbers.

If I don't zero-pad the accessions, I get nothing in @accessions.

But if I do zero pad, I get no library name from $param->{ $acc }.

There is a fix for it. I could go back to the source and ensure that this sees no un-padded numbers. I could run through the $param hashref again, But clearly, this is something I should've built in at first.

"Well, That Was Strange": Hunting Gremlins in SQL and Perl

The query base is 90 lines.

Depending on what it's used for, one specific entry or the whole lot, it has different endings, but the main body is 90 lines. There are 20 left joins in it.

It is an ugly thing.

So ugly, in fact, that am loath to include it here.

So ugly that I felt it necessary to comment and explain my use of joins.

This is where the trouble started.

I noticed it when I was running tests, getting the following error.

Clearly, it needed a bind variable, but something along the line blocked it.

I had this problem on Friday morning on our newer server, then it stopped. Honestly, it was such a fire-fighting day that I lost track of what was happening with it.

Then the module was put on the old server and the problem rose up again.

Whether that makes me Shatner or Lithgow
remains an exercise for the reader.

I said "my code has gremlins" and went home at 5pm.

When I got back to the lab this morning, I made different test scripts, each identical except for the hashbang. I set one for system Perl, which is 5.10, one for the one we hardcode into most of our web and cron uses, which is 5.16, and the one we have as env perl, currently 5.20.

The cooler solution would've been to have several versions of Perl installed with Perlbrew, then running perlbrew exec perl myprogram.pl instead, but I don't have Perlbrew installed on that system.

The error occurs with 5.10. It does not with 5.16 or 5.20.

And when I run it against a version without the comments in the query, it works everywhere.

I don't have any clue if the issue is with Perl 5.10 or with the version of DBI currently installed with 5.10, and I don't expect to. The old system is a Sun machine that was off support before I was hired in, and the admin for it reminds us each time we talk to him that it's only a matter of time before it falls and can no longer get up. I haven't worked off that machine for around two years, and this query's move to the old server is part of the move of certain services to the new machine.

And, as everything is fine with Perls 5.16 or higher, I must regard this as a solved problem except with legacy installs.

I know that MySQL accepts # as the comment character, but Sublime Text prefers to make -- mean SQL comments, so when I commented the query, I used the double-dash, and our solution is to remove the comments when deploying to the old server. It's a temporary solution, to be sure, but deploying to the old server is only temporary, too.

It's a sad and strange situation where the solution is to uncomment code, but here, that seems to be it.

Update: Matt S. Trout pushed me to check into the DBD::mysql versions, to see which versions corresponded to the error. The offending 5.10 perl used DBD::mysql v. 4.013, and looking at the DBD::mysql change log, I see bug #30033: Fixed handling of comments to allow comments that contain characters that might otherwise cause placeholder detection to not work properly. Matt suggests adding "use DBD::mysql 4.014;", which is more than reasonable.

01010100 01101000 01100001 01110100 00100111 01110011 00100000 01110111 01100101 01101001 01110010 01100100 00101110 00101110 00101110

Slight language warning.

The PaleoFuture blog on Gizmodo had a review of Tomorrowland from a Robot from the Future today, and of course it's in binary. I saw that shortly after coming in, and I thought "Hey, that'd be an interesting exercise to use to prime my programming pump, get my head in the right mindset."

So, I wrote code to decode it. I read perldoc -f pack and thought "Nope", so I found Data::Translate, which I now know is a very simple wrapper.

Once I wrote that, I decided the thing to do was to write b2a.pl and a2p.pl, because why not? But I found that this

a2b.pl Bite my shiny metal ass

gave me this

01000010 01101001 01110100 01100101 00100000 01101101 01111001 00100000 01110011 01101000 01101001 01101110 01111001 00100000 01101101 01100101 01110100 01100001 01101100 00100000 01100001 01110011 01110011

but this

b2a.pl 01000010 01101001 01110100 01100101 00100000 01101101 01111001 00100000 01110011 01101000 01101001 01101110 01111001 00100000 01101101 01100101 01110100 01100001 01101100 00100000 01100001 01110011 01110011

gave me this

Bitemyshinymetalass

So, what went wrong? Where did the spaces go?

This will take some time with me trying to grok pack, which is so unrelated to my daily responsibilities that I installed Data::Translate simply so I wouldn't have to learn a thing, but it seems that, if you pass pack the space character in binary (00100000), it returns an empty string. This is a somewhat strange thing, but once you understand the behavior, you can react to it.


(The catchphrase for Bender seemed the most appropriate text to use.)

Being Open, Being Wrong, Being Corrected, Being Smarter: Response about MongoDB

Just heard a podcast from Developer Tea where Ben Lesh of Netflix explain how he likes it when he makes mistakes, because then he learns, and we should always be learning. (I listened to it when driving, so I didn't write down the quote, and I'm not sure if it's in part 1 or part 2 of the interview.)

Yesterday, I posted on issues I had with MongoDB and Perl. I had done what I had wanted before, I was sure, but my code no longer reflected it, and Shub-Internet was not responding with the answers I desired. If you don't recall:

# This worked to get one value:
my $find_one = $collection->find_one( { date => $today }) ;

# but if I wanted the whole thing, this didn't work:
my $find = $collection->find ;

As it turns out, there's a specific piece of information I did not find, but David Golden of MongoDB set me straight.

# but if I wanted the whole thing, this didn't work:
my $find = $collection->find ;
my @all = $find->all ;
# and in the case I'd want:
my %all =
    map { $_->{ date } => $_ } #map, grep and sort are all-powerful
    $find->all ;
say Dumper \%all ;

This is great. Wonderful, even. I had a failure in understanding, I was open about it, I was corrected, and now I am a better programmer for it.

I do sorta wish I had given Stack Overflow a deeper search first, though....

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.

Name Things Like You, or "Do you ever feel like an idiot?"

I was preparing a talk on developing against the FitBit API yesterday, which lead me to look at the FitBit website, and I found something called "Alertness Checker", which I didn't recognize. The others, like TicTrac and FaceBook and IFTTT, I did, but not "Alertness Checker".

So I revoked access.

And I found that "Alertness Tracker" is the name I put for my "app", by which I mean the connected series of programs I collect as fitbit_tools, which is the topic of the talk I'll be giving in a week and a half.

Of course, I felt like an idiot, once I realized the FitBit tools I had built my life around had stopped working. The key one is called fitbit.today.py, and it checks once an hour to see if I've made more than 100 steps in that hour, and chides me if I haven't. It's lots of small things, but when you realize you broke lots of small things, you feel like an idiot.

The first thing is, anybody could name a thing "Alertness Tracker". The specific thing that caused me to develop fitbit_tools is a point where I wore my FitBit Ultra every day for several weeks without noticing I hadn't charged it recently and it was a dead dead thing. Once I had enough to tell me if it hasn't synced in a few days, I knew enough to have it tell me if the battery level was anything but high. I wrote it in Python because I couldn't find any code (besides the Net::Twitter module) that really handled oAuth in Perl, but I did find the code in Python. After that, I decided that I'd like to know more about my trends than the FitBit dashboard can tell me, then put it out on GitHub, but since I had the App registered with FitBit, I stayed with it.

This is the main thing I want to hit. The secondary point is "Don't wallop things without checking", but the main point is that you have a style, and you should go with that style. Stay within the conventions of your project and team, of course, but you should be able to recognize yours as yours. In the lab, Rick and I are both Perl programmers, but our styles are very different, so we can open up libraries and look at subroutines and say "Hey, I don't know when I wrote that, or why I wrote that, or what stupid thing I was thinking when I wrote that, but I certainly know that I wrote that."

I recall, I faked the registration using FitBit's tools rather than writing a "get client tokens" oAuth script. I think I put that in the "I really should do that" category and forgot it there. Now, as I'm going to talk through it, and as I just re-experienced the issue, I have explained it in the README and have received a "round tuit", and so will have to write something up.

You Had One Job, MongoDBx::Class Documentation

So, after yesterday, I started back with the command line interface to MongoDB. I found I can do what I want with it, which of course is creating, retrieving, updating and deleting records. So, I try working with Perl, my go-to language, and I install MongoDBx::Class. The following code, especially the $db-&gtlinsert() is closely adapted from the CPAN page.

#!/usr/bin/perl

use feature qw{ say state } ;
use strict ;
use warnings ;
use MongoDBx::Class ;

my $dbx = MongoDBx::Class->new( namespace => 'MyApp::Model::DB' ) ;
my $conn = $dbx->connect(host => 'localhost', port => 27017 , safe => 1 );
my $db = $conn->get_database( 'test' ) ;

my @movies ;
push @movies , {
    title => 'Blade Runner' ,
    rating => 'R' ,
    releaseYear => '1982' ,
    hasCreditCookie => 1
    } ;
push @movies , {
    title => 'Thor' ,
    rating => 'PG-13' ,
    releaseYear => '2011' ,
    hasCreditCookie => 1
    } ;

for my $movie ( @movies ) {
    say qq{$movie->{ title } ($movie->{ releaseYear })};
    $db->insert($movie) ; # How the documentation says it should go
    }

You Had ONE Job, Mongoose.js Documentation

Source

I'm trying to learn Node.js, MongoDB and Mongoose.js as an ORM connecting the two. I don't know what I'm doing yet, going through the early example code. I come to this as a reasonably experienced hand with Perl and MySQL. The first thing people write is a "Hello World" application, where you give it an input, commonly your name, and it gives you an output. In data, the four actions you want are Create, Read, Update and Delete, often used as CRUD. When trying out a data-storage system, you want to be able to Hello World your data.

The following is code adapted from the front page of Mongoosejs.com and the first link to documentation.

#!/usr/bin/js

/*
 * test_mongo.js
 *  adapted from mongoosejs.com
 *  
 * Getting up to speed with MongoDB, Mongoose as an ORM, ORMs in general,
 * and Node.js. 
*/

// Testing direct from http://mongoosejs.com/

// initiallizing connection to MongoDB 
var mongoose = require('mongoose');
mongoose.connect('mongodb://localhost/cats');
var db = mongoose.connection;
db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', function callback () {
    console.log( "Yay" );
    });

// Schema - what comes from DB
var kittySchema = mongoose.Schema({ name: String }) ; 
kittySchema.methods.speak = function () {
    var greeting = this.name
        ? "Meow name is " + this.name
        : "I don't have a name" ;
    console.log(greeting);
    } ;
kittySchema.methods.save = function ( err , cat ) {
    console.log( cat ) ;
    if ( err ) {
        console.log(err) ;
        }
    cat.speak() ;
    } ;

// Model - what we work with
var Kitten = mongoose.model('Kitten', kittySchema) ;
Kitten.find(function (err, kittens) {
    if (err) // TODO handle err
    console.log(kittens)
    })

// Objects - instances of models
var silence = new Kitten({ name: 'Silence' }) ;
var morris = new Kitten({ name: 'Morris' }) ;
var fluffy = new Kitten({ name: 'Fluffy' });
var blank  = new Kitten({ });

console.log( '------------------------' ) ;
silence.speak() ;
morris.speak() ;
fluffy.speak() ;
blank.speak() ;
console.log( '------------------------' ) ;
silence.save() ;
morris.save() ;
fluffy.save() ;
blank.save() ;
console.log( '------------------------' ) ;

process.exit(0) ;

save() doesn't seem to save, or do anything, really. speak() works, but why shouldn't it? The reason I'm doing this is so that Mongoose can stand between me and MongoDB. Mongoose has one job. The documentation has one job: to show people me how to do just that.

One Job!

Coming to Terms with Javascript's Not-Quite-Procedural Nature

I'm creating a web application. I've written HTML code on the server before and have decided templates are best, because whatever language, writing code that generates HTML is always worse than making a template that has holes you can fill with your data. This is why I now love Perl's Template Toolkit.

Funny thing is, making the code client-side doesn't make it much better. For now, I'm using Mustache.

There are many things you can easily do in JavasScript, but multi-line variables are not pretty in JavaScript, so it's far better to have external templates and import them. This is my current code to import a list of templates, which so far are div_request.m, div_sample.m and div_final.m.

var accordion_request = {} ;
accordion_request.m_path    = '/dev/dave' ;
accordion_request.templates = {} ;
accordion_request.template_fetch = []
accordion_request.template_fetch.push( 'div_request' ) ;
accordion_request.template_fetch.push( 'div_sample' ) ;
accordion_request.template_fetch.push( 'div_final' ) ;

accordion_request.get_templates = function ( ) {
    for ( var t in accordion_request.template_fetch ) {
        var template = accordion_request.template_fetch[t] ;
        var name = [ template , 'm' ].join( '.' )
        var url = [ accordion_request.m_path , name ].join('/') ;
        console.log( template ) ;
        console.log( name ) ;
        console.log( url ) ;
        $.get( url , function ( data ) {
            console.log( ': ' + url ) ;
            accordion_request.templates[template] = data ;
            }  ) ;
        }
    }

Do you see the problem?

JavaScript does not block. So, we convert div_request to div_request.m to /dev/dave/div_request.m, then we set the jQuery $.get() off to get that url, then we convert div_sample to div_sample.m to /dev/dave/div_sample.m and set that off to $.get(), then jump back to div_final.

By the time $.get() starts working, url is "/dev/dave/div_final.m", so we get a console log that looks like:

div_sample
div_sample.m
/dev/dave/div_sample.m
div_request
div_request.m
/dev/dave/div_request.m
div_final
div_final.m
/dev/dave/div_final.m
: /dev/dave/div_final.m
: /dev/dave/div_final.m
: /dev/dave/div_final.m

I now believe I need to write a function that takes url and gets and writes it, so that it has it's own scoped url rather than using the one in get_templates(). We will see.

Why Yahoo is Sad for me

In a perfect world, I would be able to wrap up my tools in a little bundle. I can't. We've made a decision to have one lib directory and put everything in it. So, we have our blessed jQuery, jQuery-UI, Mustache.js, etc. And, we don't use every library in every web app. So, this means for one JS-heavy page, I'm calling those three, jQuery TableSorter, HighCharts and a theme, plus my library which calls that.

Well, really, I can and should pull out the HighCharts libs, as we no longer plot on that page. Which makes things even worse. I call seven (now 5) libraries to do this work which ySlow considers excessive. But because I don't always use jQuery-UI and Mustache, I can't just make and minify a std_lib.min.js with everything put together.

This is where I get a B.

Even worse, I don't run everything through GZIP. (F.) I also don't use a CDN. I am using SSL now, and it seems that SSL and CDNs are mutually exclusive, as everything that doesn't come from your https server is a possible security issue. Even then, almost everybody who is going to use this data is going to be in the same lab. Still, F.

And we're not going to get into how I'm not going to play with the Apache config to get Expires headers (F) and Entity Tags (F).

And we have our Lab Notebook, the place where we store all our work, in OddMuse. And OddMuse remembers who you are with a Cookie. Not having written OddMuse, I can't change that. Not having a good alternate choice, I can't change to another Wiki. So, there's one cookie. (D)

I know that Yahoo's problems are not my problems. Honestly, while I got a grade of 77 (C) on the page as a whole, I know it's one of my better websites, because it handles only a small chunk of data at a time. And really, I'd rather work with my unminified JS so if problems arrive, the JS console will give me a sane reading of where the problem lies.

So, I'll take that Charlie and proceed to the next problem.

Bug in Perl? Or am I just doing it wrong?

Three modules: MyTools::Foo, MyTools::Bar and MyTools::Blee. Foo primarily exports foo(), which returns "foo". Bar, similarly, exports bar() which returns "bar". Same with Blee.

Foo also exports foo_bar(), which concatenates the output of foo() and bar(), and foo_blee(), which does the same with foo() and blee(). Bar has bar_foo() and bar_blee(), and Blee has blee_foo() and blee_bar().

Here, the library holding MyTools is identified using the PERL5LIB environment variable. I do this to model a problem shared by a suite of modules that have spaghetti-like tendencies to interconnect with each other. Previously, I had used use lib '/path/to/lib' to do this purpose, but the need to start using git and the like has pushed me toward using a means to identifying library paths without hardcoding them.

I haven't developed the test case such that, using use lib, it works, but my production uses lib all over the place, and I only noticed the problem when I copied it to a dev directory and pulled those lines.

The problem I'm seeing is that Perl starts to look for foo() in Bar or Blee, when foo() is in Foo. Clearly, It'd be better if Foo didn't include Bar and Blee while Bar included Foo and Blee, etc. It would be nice if I had separated the code better in the first place, but seeing that the code base where I started finding this problem is 19 modules with over 8,000 lines and is used across several systems by someone else, so every change has the potential to break my lab's workflow, I can't really disentangle it right now.

So, it strikes me that Perl is wrong, too. (I fully admit that my code is an unruly hairball. I'm starting to pay that technical debt right now.) I'm somewhat loathe to tag this as an error in Perl and start filing bug reports until someone with more direct experience looks at this and says either "That's odd" or "Dave, you're a dumbass." I would certainly accept either answer.

So, am I wrong?