A database gotcha - null doesn't equal anything

I recently spent a long time debugging a problem where a database query was returning fewer rows than I expected. The cause was a well-known fact, but one that's easy to forget about: a "not equals" comparison to NULL is always false. An example makes this clear...

Create a table with a couple of columns:
CREATE TABLE test
(
  id integer NOT NULL,
  name character varying(10),
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

Insert a few rows into the table; one of the rows includes a NULL value:
INSERT INTO test VALUES (1, 'abc');
INSERT INTO test VALUES (2, 'def');
INSERT INTO test VALUES (3, null);

Suppose we'd like to find all the rows where the name isn't 'abc'. It seems like this query would do it:
SELECT * FROM test WHERE name <> 'abc'

But that doesn't work. The query returns only the first row. This query solves the problem:
SELECT * FROM test WHERE COALESCE(name, '') <> 'abc'

I executed the above example in PostgreSQL 9.2, but the same principle applies to many databases.

Listing all column names in a PostgreSQL table

My PostgreSQL database contains a table with more than 100 columns, and I wanted to list the name and data type of each column, in a format that I could copy and paste. I was using Windows 7, but a similar technique should work on Linux. Here's one way to do it:

• Open a DOS command window.
• Change to PostgreSQL's bin directory. On my computer, the command is cd C:\Program Files\PostgreSQL\9.2\bin.
• Make sure the directory C:\temp exists.
• Execute this command: psql -U <your PostgreSQL user name> -d <your database name> -c "\d <your table name>" > c:\temp\out.txt
• This will create the text file C:\temp\out.txt with a list of field names and data types.

Setting up Python and Bottle on Ubuntu 12.04

Here are the steps I followed to get Python and the Bottle web framework working on an Amazon Web Services (AWS) Ubuntu server:

• Log in to AWS and create a new instance. Select the AMI Ubuntu Server 12.04.1 LTS, 64-bit. If you wish to minimize costs, set the instance type to T1 Micro. Accept AWS's default settings for the instance.
• Configure the instance's security group to, at a minimum, allow access from your own IP address to ports 22, 80 and 5901.
• Use an SSH client to connect to the instance, logging in as the user ubuntu.
• This AMI comes with Python pre-installed. You can verify this by entering the command python. The response should indicate that Python 2.7.3 is running. Enter quit() to exit Python.
• Install Bottle: sudo apt-get install python-bottle.
• Install a VNC server so you'll be able to run a web browser on your Ubuntu server. See my blog post, VNC server for headless Ubuntu 12.04 and VNC client for Windows 7, for details.
• Install a web browser: sudo apt-get install firefox.
• Create a directory for your Python files. I called mine python_test: mkdir python_test.
• Change to that directory: cd python_test.
• Create a Bottle "hello world" application. I found an example here and modified it slightly. Use your favoirte text editor to create a file named hello.py as follows:

 from bottle import route, run

@route('/hello')

def hello():

    return "Hello World!"

run(host='localhost', port=8080)                                   

• Execute this Python script: python hello.py.
• Use a VNC client to connect to your server. In the VNC client, open Firefox. Visit http://localhost:8080/hello. You should see "Hello World!" in your browser.
• In your SSH client, press Ctrl+C to stop the Bottle server.

VNC server for headless Ubuntu 12.04 and VNC client for Windows 7 - success!

Objective: To set up a GUI client on Windows 7 to manage my Ubuntu server.

The server is a headless Ubuntu 12.04 box on Amazon Web Services. The client machine is my Windows 7 laptop.

I found lots of advice about GUI interfaces for Ubuntu, but none of it worked in my situation. I encountered problems such as:

• It was often suggested to use xRDP as the server and the built in Windows Remote Desktop as the client. That would be great if it worked, but I just couldn't get it to work. Once in a while, I could log in, but most of the time I got password-related errors.
• A lot of the advice was for old  versions of Ubuntu.
• The advice assumed a GUI was already installed on the server, that is, it was Ubuntu desktop rather than a headless Ubuntu server.

I finally found a solution that worked: vnc4server as the server and TightVNC Viewer as the client. Many thanks to Coddswallop's post on vnc4server setup.

Setting up vnc4server on the Ubuntu server

• Use an SSH client to connect to the server.
• Enter sudo apt-get update.
• Enter sudo apt-get install gnome-core gnome-session-fallback.
• Enter sudo apt-get install vnc4server.
• Enter vncserver.
• You'll be prompted to make up a password. Enter whatever you like, and make a note of it. You'll need this password when using the VNC client to connect.
• Re-enter the same password when prompted.
• Enter vncserver -kill :1.
• Enter cp .vnc/xstartup .vnc/xstartup.bak.
• Use your favorite text editor (I used VIM) to modify the file .vnc/xstartup, so that it looks like this:

#!/bin/sh

# Uncomment the following two lines for normal desktop:

unset SESSION_MANAGER

#exec /etc/X11/xinit/xinitrc

gnome-session --session=gnome-classic &

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup

[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources

xsetroot -solid grey

vncconfig -iconic &

#x-terminal-emulator -geometry 80×4+10+10 -ls -title ?$VNCDESKTOP Desktop? &

#x-window-manager &

• Enter vncserver -geometry 1200x650. You can change 1200x650 to whatever width and height suits your client machine's display, and you can add an optional depth parameter to change the color depth.
• Enable the necessary port in your firewall. In my case, this was port 5901. In the AWS Control Panel, I edited the applicable Security Group, creating an inbound rule for port 5901 for my laptop's IP address.
• Note: After rebooting the server, you'll need to reissue the command vncserver -geometry 1200x650. If you wish, you could set up your server to execute this command automatically on startup.

Setting up TightVNC Viewer on the Windows 7 client

• I visited http://www.tightvnc.com/download.php and downloaded the Installer for Windows (64-bit).
• I ran the resulting tightvnc-2.6.4-setup-32bit.msi.
• Windows asked whether I wanted to give the installer permission to run, and I said yes.
• During the installation, I was asked whether to allow the installer to create a Windows firewall rule. I said no.
• I selected custom installation and installed only the client, omitting the server component.
• From the Start Menu, I launched TightVNC Viewer.
• In the Remote Host field, I entered my server's public IP address followed by port :5901. Example: 12.34.56.78:5901. Then I clicked Connect.
• When prompted for a password, I entered the password I made up when installing the server.
• A GUI interface to my server opened. Yay!

Rails - opening a new browser tab or window in the foreground

In a Rails website, suppose you'd like to open a new window (or new tab, depending on the user's browser settings) when the user clicks a link. And suppose you'd like to make sure the new tab or window comes to the foreground. And suppose you'd like to specify the URL the "Rails way," using link_to rather than hard-coding an HTML a tag. Here's how...

This example assumes "merchant" is an instance of a Rails model class with RESTful routes. It creates a "Shop now" link that opens the merchant's page in a new tab or window and gives it the focus. Of course, this relies on JavaScript's being enabled in the user's browser.

<%= link_to "Shop now", merchant, onclick: "var w = window.open(this.href); w.focus(); return false;" %>

Setting up Ubuntu with Rails, Git, Heroku, Capybara and more

I've been following Michael Hartl's excellent Ruby on Rails tutorial. One nice thing about is it that it not only explains Rails, but guides the reader through setting up an entire development ecosystem with GitHub for source control, Heroku for deployment, RSpec and Capybara for testing, and Bootstrap for stylesheets.

I'm using an Amazon Web Services Ubuntu instance. Hartl provides instructions for setting up all the required software on a variety of platforms, including Linux. For the most part, his instructions worked for me, but I found that a few tweaks were needed. This article documents how I set up my AWS Ubuntu box.

Prerequisites:

• An AWS account with a keypair
• A GitHub account
• A Heroku account

Launch the AWS EC2 instance

• Log in to your AWS account.
• On the EC2 Dashboard, click Launch Instance
• Click Classic Wizard. Step through the wizard, accepting the default, except:
• Choose the AMI Ubuntu Server 12.04.1 LTS (64-bit).
• I set the Instance Type to T1 Micro. You can use any Instance Type you wish. T1 Micro is a bit underpowered, but qualifies for AWS's free tier.
• Select your existing keypair.
• Set the Security Group to quick-start-1. Or use any Security Group you wish. Configure the Security Group to provide inbound access on TCP 22, 80 and 3000 for any IP addresses that will access the server.
• After you complete the wizard, you may want to give your instance a name. I called mine Ubuntu Ruby 3.
• Connect to your instance for the first time:
• Make sure your browser supports Java.
• Right-click the instance in the EC2 Dashboard and select Connect.
• Change the user name to ubuntu.
• For the private key path, browse to your keypair file (e.g. mine is located at C:\Users\ssaporta\Desktop\stevekeypair.pem).
• Check the Save key location box.
• Click Launch SSH Client. Respond affirmatively to any security dialogs that may appear, so that the Java SSH client can run.
• When prompted Do you want to add this host? click Yes.

Install Git, RVM, Ruby and Rails

Section 1.2.2 of the tutorial  provides instructions for Git, RVM, Ruby and Rails installation, but I had some trouble with them on Ubuntu. Based on this Sudobits Blog entry, I found the steps below worked better:

• Launch the SSH client to connect to your instance.
• Enter sudo apt-get update.
• Enter sudo apt-get install git.
• Enter curl -L get.rvm.io | bash -s stable.
• Enter source ~/.rvm/scripts/rvm.
• Enter sudo /usr/bin/apt-get install build-essential openssl libreadline6 libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt-dev autoconf libc6-dev ncurses-dev automake libtool bison subversion pkg-config.
• Enter sudo apt-add-repository ppa:chris-lea/node.js.
• Enter sudo apt-get update.
• Enter sudo apt-get install nodejs.
• Enter rvm install 1.9.3. On a T1 Micro instance, this could take half an hour or so.
• Enter rvm use 1.9.3 --default.
• Enter gem install rails. This, too, could take a while on a T1 Micro instance.

Create and publish your first Rails app

This section walks through the creation of a simple Rails app, but my real point in including it is to show the steps for setting up pushes to GitHub and Heroku.

• Launch the SSH client to connect to your instance.
• Create the file ~/.gemrc with these contents:

install: --no-rdoc --no-ri

update: --no-rdoc --no-ri

• Create a folder and create a Rails project in it:
• Enter mkdir rails_projects.
• Enter cd rails_projects.
• Enter rails new first_app.
• Enter cd rails_projects/first_app.
• Set up Git and GitHub, and perform your first commit:
• Enter git config --global user.name "Your Name". Replace Your Name with your actual name.
• Enter git config --global user.email you@example.com. replace you@example.com with your actual email address.
• Enter git init.
• If desired, edit .gitignore as Hartl suggests
• Enter git add . (yes, that's a dot at the end).
• Enter git commit -m “Initial commit”.
• Enter cd ~/.ssh.
• Enter ssh-keygen -t rsa -C "you@example.com". Replace you@example.com with your actual email address. Press Enter three times to use the default filename and a blank passphrase.
• Enter cat ~/.ssh/id_rsa.pub. Select the output and press Ctrl+Ins to copy it to the clipboard.
• In a web browser on your own computer, log in to your GitHub account and create a new repository:
• Set the repository name to first_app.
• Set the description to The first app for the Ruby on Rails tutorial.
• Select Public.
• Do not check the box to initialize this repository with a README.
• In GitHub, click the Account Settings icon.
• Click SSH Keys.
• Click Add SSH Key. Enter a title (e.g. ubuntu). Paste the key from the clipboard. Enter your password when prompted.
• Return to your SSH client and enter cd ~/rails_projects/first_app.
• Enter git remote add origin git@github.com:username/first_app.git. Replace username with your actual GitHub username.
• Enter git push -u origin master. When prompted, enter yes.
• Deploy your Rails app to Heroku:
• Enter wget -qO- https://toolbelt.heroku.com/install-ubuntu.sh | sh.
• Enter heroku login. Enter your Heroku username and password when prompted.
• Enter heroku keys:add ~/.ssh/id_rsa.pub.
• Enter git push heroku master.
• Note the URL displayed in the output. You can view your app on Heroku at this URL.

At this point, you should be all set to develop Rails apps, test them with RSpec and Capybara, push them to GitHub, and deploy them to Heroku.

An excellent tutorial for node.js noobs

I just completed Manuel Kiessling's Node Beginner Book. Great tutorial! In two hours, I had a working node.js app and a decent appreciation of what node.js is all about. Furthermore, I got everything working on Windows 7 without any trouble. To install node.js on Windows 7, I just went to http://nodejs.org/ and clicked the INSTALL button.