Kevin's Programming Journal


PostgreSQL Puzzle

3/13/2015

Here’s a little PostgreSQL puzzle.

Why does the following result in ERROR: failed to find conversion function from unknown to text on PostgreSQL 9.3?

SELECT CASE WHEN x='foo' THEN 1 ELSE 2 END
FROM (
    SELECT 'foo' AS x
) AS subquery

But this works perfectly fine?

SELECT CASE WHEN x='foo' THEN 1 ELSE 2 END
FROM (
    SELECT CASE WHEN 1=1 THEN 'foo' ELSE 'bar' END AS x
) AS subquery

The only difference is the subquery.

In the first snippet, PostgreSQL complains that the type of column x in subquery is unknown. I find this strange, considering that the query uses a string literal and it is obviously text.

In the second snippet, the string literal has been replaced with a CASE and the problem goes away. It’s fascinating that PostgreSQL is able to correctly determine the type of a computed column, in this case, but not the type of a column defined with a string literal.

Adding a CAST to the x column resolves the issue.

SELECT CASE WHEN CAST(x AS text)='foo' THEN 1 ELSE 2 END
FROM (
    SELECT 'foo' AS x
) AS subquery

Git amend arbitrary commits

2/17/2015

Using Git on a daily basis, and rebasing commit history nearly as much, led me to ask the question: Why doesn’t git --amend work with arbitrary commits?

For example, imagine being able to stage some changes, and then squash them with git-amend HEAD~1 or git-amend 279923e.

I imagined that this ability wouldn’t be much different from a fixup commit that was immediately squashed into the target commit. With that in mind, I created a shell script to do just this.

#!/bin/bash
set -e

# Boilerplate template reference:
# https://github.com/oxyc/bash-boilerplate/blob/master/script.sh

sha1=""
version="0.1"

usage() {
    echo -n "$(basename $0) [OPTION]... [SHA1]

Commit index then immediately squash into target SHA1.

Options:
  -h, --help        Display this help and exit
      --version     Output version information and exit
"
}

# Print help if no arguments were passed.
[[ $# -eq 0 ]] && set -- "--help"

# Read the options and set stuff
while [[ $1 = -?* ]]; do
  case $1 in
    -h|--help) usage >&2; exit 0 ;;
    --version) echo "$version"; exit 0 ;;
    *) echo "Invalid option: $1" >&2; exit 1 ;;
  esac
  shift
done

sha1=$1
shift

set -x
git commit --fixup $sha1
git rebase -p -i --autosquash $sha1~2

The biggest drawback is that you can’t stage more than 1 fixup at a time since it rebases history. However, I would argue that similar to git --amend, this utility is designed for a narrow use-case and shouldn’t be expected to handle anything more complicated.

What I learned is that rebasing is complicated. There’s a reason that git --amend doesn’t work like this out of the box, and the reason is probably that this command is only marginally useful for minor changes, and useless for non-trivial fixups.

While this command was not as useful as I expected it to be, I still use it at least once or twice per week, and plan to continue experimenting with it and hopefully come up with some better ideas on how to improve its usefulness.

GitHub API authentication using OAuth

2/5/2015

For those interested in obtaining access to private repository information using the Github API, here is a sample application built with Flask, which demonstrates how to perform authorization via OAuth.

OTM Dashboard

The bulk of the work takes place in main.py#login.

@app.route("/login")
def login():
    if 'access_token' in session:
        return redirect(url_for('index'))
    elif 'code' in request.args:
        # Exchange auth code for access token.
        params = {
            'client_id': app.config['CLIENT_ID'],
            'client_secret': app.config['CLIENT_SECRET'],
            'code': request.args.get('code')
        }
        url = 'https://github.com/login/oauth/access_token'
        res = requests.post(url, data=params)
        qs = urlparse.parse_qs(res.text)
        session['access_token'] = qs['access_token'][0]
        return redirect(url_for('index'))
    else:
        # Request auth code.
        params = {
            'client_id': app.config['CLIENT_ID'],
            'redirect_uri': app.config['SITE_URL'] + '/login',
            'scope': 'repo'
        }
        qs = '&'.join(a + '=' + b for a, b in params.iteritems())
        url = 'https://github.com/login/oauth/authorize?' + qs
        return redirect(url)

Workman layout

2/1/2015

I’ve been using the Workman keyboard layout for about 4 weeks now, and other than Ubuntu randomly changing keyboard layouts, it’s been a positive experience.

Workman keyboard layout

As my touch typing speed increased, I was surprised to find that my fingers would occasionally revert to Dvorak, which I have not typed for years. Although I think that Dvorak is a great layout, I don’t find it particularly attuned to programming and general keyboard shortcuts compared to the Workman layout. Try to copy & paste on a Dvorak keyboard and you’ll see what I mean.

I enjoy using the Workman layout and I plan to keep using it indefinitely.

My typing speed is presently around 40-50 WPM.