Rails - Read More, Code Less, do Peer Reviews

For some previous projects I rolled some simple code for generating a random string - used to generate a password reset token for example:



Now I've found that this type of functionality has been in Rails for some time - now as ActiveSupport::SecureRandom and previously as Rails::SecretKeyGenerator.

Ok so that is simple code and probably isn't a problem but the lesson here is: read more, code less, do peer reviews...don't waste a lot of time re-inventing the wheel.

Rails ActiveRecord Order by a Function

Of course you can pass functions in the order clause through ActiveRecord finders:



In the above case a Foo has fiscal_year and fiscal_month columns unfortunately stored as VARCHAR(255) containing, for example "2008" and "7". While the sorting will work that query will be able to leverage an index on these columns only if the database used can create indices that include functions. I'm not sure which databases can do this. From a quick look at the SQL Server and MySQL documentation it doesn't appear that either of these databases can do this.

Rails Caching and MySQL GROUP_CONCAT

A recent caching solution that we came up with involved using the hash of the concatenated "children" ids of another resource to identify when the identical and previously stored children payload could be delivered instead of constructing the payload from scratch (which is kind of expensive). Our current solution uses action caching and a custom ActionController::Caching::Fragments::FileStore. In our custom FileStore we override the real_file_path(name) method to use this "children signature" as part of the name. The class looks something like this:



This seems to work well and the next step is likely to look at using the children_sig value for the ETag as well.

One interesting thing that came out of this investigation was that I wondered if I could produce the hash using straight SQL - should be much faster right? Turns out that with MySQL there is a built in aggregate function that concatenates a column and it is called GROUP_CONCAT. To generate that hash in the database with MySQL the following will work:



Pretty cool! I'm not sure which other database products support this as a built in aggregate function but it doesn't appear that SQL Server does.

I found the following recent blog posts talking about other uses for GROUP_CONCAT as well:

http://tempe.st/2009/03/the-thrill-of-a-new-technology-couchdb/
http://www.christianmontoya.com/2007/09/14/mysql-group_concat-this-query-is-insane/
http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html

Display Google Docs Spreadsheet Data on Your Website

The Google Spreadsheet API makes it pretty painless to display spreadsheet data on your own web pages using only client side technologies. You might use this, for example, to display your terrible stock investments on one of your web pages to remind you why you have to go to work everyday.

The first thing you need to do is to make a Google Doc Spreadsheet. The second thing you need to do is to "Publish" the spreadsheet. This will make your spreadsheet available for anyone to view and is required if you are going to consume this data with unauthenticated Javascript running in a web page (so you probably don't want to do this with a sheet that contains your world domination secrets). To publish your spreadsheet, click on Share -> Publish as a Web Page -> Publish Now. Depending on the data you may also wish to check "Automatically re-publish when changes are made." For my example you certainly want to do this because the spreadsheet contains stock symbol lookup functions where the numbers will change as your stock investments go lower and lower.

After you have published your spreadsheet you need to get two pieces of information about the spreadsheet - the spreadsheet identifier and the identifier of the individual worksheet within the spreadsheet that you want to snarf the data off of. While it is possible to get at this information by pulling down meta-feeds about your spreadsheet documents it is probably easier if you just navigate to your published spreadsheet and choose View -> Source from your browser. In the source look for a link tag with rel="alternate". This tag will have an href that contains both the spreadsheet and worksheet identifier. An example looks like this:



In that href the "pBYwcZBFkvxZycw-gNxPCIw" is the spreadsheet identifier and the "od6" is the worksheet identifier. With these pieces of information you are now ready to write some code to pull the spreadsheet data back to your web page.

There are two different feeds that you can get to pull that data back to your site. They are identified as "list" and "cells" feeds. This basically breaks down to whether you get the data back as a series of rows or a series of cells. I believe in most cases you are going to want to work with a series of rows so you will use the "list" feed, however, check the API if you believe the "cells" feed may work better for you. The code below shows the feed URL being used to cause a callback to the function processStocks:



I won't go overboard explaining this code here - I think you can figure most of it out. It merely iterates through the returned result set from the spreadsheet, adding each stock value as to an unordered list in the page. It also keeps a running count of the total in a variable called bindex - the Burger Index. The way the cell entries are referenced is a little awkward and corresponds to the column headers - as seen in my example entry.gsx$symbol.$t and entry.gsx$price.$t. Part of this awkwardness comes from the format being a translation of the alternate XML format.

Currently you can see this code in action on my github page as well as list and cells feeds examples here.

SQL - Last Index Of lastIndexOf

Quick flush - lastIndexOf in two varieties of SQL plus using the technique to cut off the last segment in a materialized path:

Greasemonkey Hawaii State Library Lookup

John Udell wrote a Greasemonkey script called LibraryLookup several years ago that I modified to work with the University Of Hawaii's library system. LibraryLookup works in the background when you visit pages on Amazon.com and puts up an indicator if your local library has the book you are browsing.

Later, I attempted to get this script to work with the Hawaii State Public Library System (HSPLS). I quickly found out that the HSPLS was using an ipac system (good! that is on the supported list) but for some reason they have turned off searching by ISBN (are you kidding me!?). I sent someone in their IT staff an email at the time and they responded by saying it was something they would be turning back on soon. A couple years later I checked and still no ISBN search. I sent another email and the response was something about upgrading to a different catalog system. John's script worked by looking for the ISBN, and while I could see ways to get it to work by Title and Author, I never took the time to actually adapt it to do so - until now. My script does an initial search by "Author Keywords" and "Title Keywords" and then looks at the content of the resulting page for an ISBN match. Here is the code:



So to use the code download it to your computer and name it something like hspls.user.js. Then, if you have Greasemonkey installed and open the file with your browser, it should ask you if you want to install the script. After installing the script, whenever you visit a book's page on amazon.com the script will check the HSPLS's site to determine if they have the book. When it finishes this lookup it will append a link to the Amazon page right after the book's title. The link will contain either the text HSPLS(Yes) or HSPLS(No:#). In the case of Yes, an exact ISBN match was found. In the case of No, the ISBN was not found and the # will contain the number of books that were found given the search term that was submitted. The following screen shot shows an example where the lookup failed but indicated 6 possible fuzzy matches:



Ok, that was a bad example! One of the fuzzy matches is the actual book under a different ISBN, oh well. Did I just spend 2 hours of my day off polishing up that script? Yes, I guess I did.

Convert a Repository from Subversion to Git

(Most of this I learned from a blog post by Paul Dowman. This is my personal reminder of how to do this with possibly a couple extra nuggets of information thrown in.)

This article assumes you want to preserve the entirety of the commit history. If you do not want to do that, then you can initialize a new git repository from an old subversion repository in many fewer steps - but I won't be discussing that here.

If you want to keep all commits attributed to the right people the first thing you are going to want to do is to make your authors mapping file (if you don't care about this, you can skip this step and forget about the -A parameter below). This file will be a mapping of svn login names to the full name and email of that committer. The format of that file is simple and as follows:



Name that file something like authors.txt. Now don't worry if you can't remember who all the committers were, you don't have to get this perfectly complete up front. When you issue the following command with the -A authors.txt argument, if a login name that is not in your authors mapping file it encountered the import will stop and tell you which login could not be found. Then, you can just add that author to the file and issue the command again and it will pick up where it left off. Here is the command you need to issue:



Ok so breaking that down the --stdlayout instructs git-svn that you have a typical svn structure by passing the arguments "-t tags -b branches -T trunk". If your structure differs from this, then don't use --stdlayout and adjust accordingly. The --no-metadata argument instructs git-svn to not bring in the svn directories and metadata that allow it to operate as an svn repository. The -A authors.txt file is what allows the import to get the committers correct as discussed above. The final two arguments are the location of your subversion repository and the directory to make your shiny new git repository in locally.

This import command may take a very long time to run as the entire history of commits is constructed.

After the import finishes you probably want to change what were tags in your subversion repository back into tags in this fresh import. To do that cd into your git repository and issue the following command:



This will list all the remote branches in your repository and you will likely see a bunch of them starting with tags/* (depending upon what your svn repository was like of course). For each of those branches that was actually a tag, you want to issue the following two commands (where release-0.2 was the name of my tag):



That first line creates a tag from that remote branch while the second command deletes the remote branch. If you have a bunch of these you will probably want a little script to do this step and I believe that Paul's article mentioned at the top of this post has an example (possibly in the comments).

Now you pretty much have your subversion repository converted to git with the last step being putting this repository out as a public repository you will share with your team. To do that first go to your public git server and set up a new bare repository using git init. The project I was converting when making this blog post is called TACT so for me that looked like this:



The final step is to push your git repository into its publicly hosted space. That is done with two commands. First, add your remote as the origin for your repo, and second, push to the origin. With your current directory being the root of your shiny new git repository that you imported above, here is what that might look like:



That is about it. The very next step you probably want to do is to set up your .gitignore file.