PostgreSQL Column-to-Row Transposition

I recently had a need to generate a geolocation history of user activity. The result set needed to be a linear history of users' activities, with each row consisting of a user identifier, activity type, location, and timestamp. Unfortunately our database schema stored the location and timestamp of three different types of activities across 3 separate pairs of columns in the same table. To accomplish the required output, I needed to transpose the columns into rows. To accomplish this, I was able to make use of PostgreSQL's array constructor syntax and unnest array function.

From a table with the following columns:

• user_id
• activity1_location
• activity1_timestamp
• activity2_location
• activity3_timestamp
• activity3_location
• activity3_timestamp

I issued the following query:

SELECT user_id, 
unnest(ARRAY['activity1', 
             'activity2',
             'activity3']) as "activity type",

unnest(ARRAY[activity1_location, 
             activity2_location, 
             activity3_location]) as "location",

unnest(ARRAY[activity1_timestamp, 
             activity2_timestamp, 
             activity3_timestamp]) as "timestamp";

The unnest function generates multiple rows, one row per element of the specified array. This produces a result such as:

user_id | activity_type | location    | timestamp

--------+---------------+-------------+--------------------

      1 | activity1     | address1    | 2012-03-13 00:00:00

      1 | activity2     | address2    | 2012-03-13 00:00:01

      1 | activity3     | address3    | 2012-03-13 00:00:02

      1 | activity1     | address4    | 2012-03-13 00:01:00

      1 | activity2     | address5    | 2012-03-13 00:01:01

      1 | activity3     | address6    | 2012-03-13 00:01:02

      2 | activity1     | address7    | 2012-03-14 00:00:00

      2 | activity2     | address8    | 2012-03-14 00:00:01

      2 | activity3     | address9    | 2012-03-14 00:00:02

...

I learned about these PostgrSQL array functions from this highly recommended slide presentation, "Postgres: The Bits You Haven't Found".

Quicker Code Reviews

This year I resolve to spend less time doing code reviews by only reviewing the test code. This should work, right?

Android "External Storage" Poorly Named

As a user of an Android device, I've always been a little confused about the various types of data storage that are available, and where exactly my apps are storing their (well really, my) data. There's "internal storage", which I've always assumed is akin to the internal hard drive on a normal computer: durable, persistent storage. And then there's "external storage", which I've assumed meant a removable SD card. So I always assumed that if I were to take the memory card out of my phone and toss the phone in the water, I would at least maintain in my possession all of the data that I've explicitly moved to (or configured apps to automatically save to) "external storage". Well, it turns out that's not quite the case. What Android docs call "external" storage is really just a "non-private" data space. And a removable SD card may or may not be where this non-private storage resides, as it can actually be a partition of the internal storage! Come on Google! Why call this "external storage" at all? It's "non-private" or "public" storage. Please rename it.

Here are the official docs:

Using the External Storage
Every Android-compatible device supports a shared "external storage" that you can use to save files. This can be a removable storage media (such as an SD card) or an internal (non-removable) storage. Files saved to the external storage are world-readable and can be modified by the user when they enable USB mass storage to transfer files on a computer.

It's possible that a device using a partition of the internal storage for the external storage may also offer an SD card slot. In this case, the SD card is not part of the external storage and your app cannot access it (the extra storage is intended only for user-provided media that the system scans).

Subversion Directory Tree Conflicts

Came across this animation on a blog while looking for some answers on how to properly resolve a Subversion tree conflict on a directory. This about describes how I feel at the moment, after having already spent a large part of the day working with merging source code branches. In fact, I often feel like this, when I can't find proper documentation for the software I'm using.

For what it's worth, the "answer" I was looking for was found in the last paragraph here, which tells me that Subversion will be of no help in resolving my particular problem. Joy!

Other tree conflicts

There are other cases which are labelled as tree conflicts simply because the conflict involves a folder rather than a file. For example if you add a folder with the same name to both trunk and branch and then try to merge you will get a tree conflict. If you want to keep the folder from the merge target, just mark the conflict as resolved. If you want to use the one in the merge source then you need to SVN delete the one in the target first and run the merge again. If you need anything more complicated then you have to resolve manually.             

Scala "for" iteration with indexes

In Scala, to iterate through a collection of items while keeping an index, Seq.zipWithIndex:


for (e <- items.zipWithIndex) {
  println(e._1 + " at index " + e._2)
}


(I find this especially useful when writing Scala code that calls into Java library setter methods that are index-based.)

Getting Started is the Hardest Part

Too often, when I'm trying to get started on a small, personal software project, I'm stymied by the time it takes to get the development environment and project infrastructure setup. With a full-time job as a developer, an addiction to cycling, and the responsibilities associated with being the parent of a two-year child, it's hard to find the mental energy and time to work on even a small software idea.  So when I do have an hour of mental energy available, the last thing I want to spend it on is project setup and configuration task. Maven archetypes to the rescue! Archetypes allow you to setup your project nearly instantly, and if you have appropriate Maven support in your IDE, you'll be ready to code within second (okay, minutes). If--and this is a big if--you can find an appropriately up-to-date archetype that provides the exact stack of technologies upon which your project will rely. So far, I don't seem to have such luck (can any one tell me where I can find well designed sampling of Scala-based Maven archetypes?) So instead of trying to start off with someone else's half-baked archetype each time I need to start a project, I've decided to take the time create my own archetype(s) that I can reuse and evolve for my own needs. The following Maven reference page was all I needed to figure out how to generated my own custom archetypes: http://maven.apache.org/archetype/maven-archetype-plugin/advanced-usage.html.

Find Most Recently Modified File

To find the most recently modified file in the current directory tree:

find . -type f -printf '%T@\t%t\t%p\n' | sort -nr | head -n 1 | cut -f 2,3