Wednesday, July 27, 2016

Crush Tools convdate

Convert dates on the command line
$ echo '7-25-2015-12:12:12' | convdate --verbose
$ echo '7/25/16' | convdate --verbose -i '%m/%d/%y' -o '%Y-%m-%d'

i is the input format, as per 
o is the output format, as per

Crush Tools subtotal

Given foo.csv:

We want to subtotal columns 2 and 3 (individually) by group in column1:
subtotal --key 1 --sum 2,3 --delim , -i foo.csv


Easy XPath against HTML

UPDATE. Even easier with xidel:

xidel '' --extract '//title'


curl -L | \
  tidy -asxml -numeric -utf8 | \
  sed -e 's/ xmlns.*=".*"//g' | \
  xml select -t -v "//title" -n

  1. Get the HTML content from using curl
  2. Use HTML Tidy to tidy it up, covert it to XHTML, change &entities; to numeric ones, and set the encoding as UTF-8
  3. Use sed to remove the XML namespace, for simpler XPaths
  4. Use XML Starlet to select by XPath

You can output multiple columns like so:
curl -L | \
  tidy -asxml -numeric -utf8 | \
  sed -e 's/ xmlns.*=".*"//g' | \
  xml select -t -v "//title" -o ','-v "//another" -n

Monday, July 25, 2016

Crush Tools dbstream with PostgreSQL

There's a command line library from Google called Crush Tools. It hasn't been updated for a while, but a few commands look useful, like pivot and aggregate.

There's also one called dbstream, which is a perl script to stream to/from a database.

Here's how to run it (on OS X anyways):

Install it (crush-tools, Perl package manager, Perl PostgreSQL driver):
$ brew install crush-tools
$ curl -L | perl - --sudo App::cpanminus
$ sudo cpanm DBD::Pg

And run it:
$ dbstream -de , -ds dbi:Pg:dbname=foo -u alice -p secret -s "select 1,2;"

or insert each record from a file:
$ cut -d, -f1-2 input.csv | dbstream ... -s "insert into tbl values (?,?);"

or look up each record from a file:
$ cut -d, -f1 input.csv | dbstream ... -s "select name from tbl where code=?;"

or update up each record from a file:
$ awk -F, '{print $2","$1}' input.csv | dbstream ... -s "update tbl set name=? where id=?;"

-de is the input & output field separator
-ds is a Perl DBI datasource
-u username
-p password
-s SQL

It's here if it's not in your package manager:

Wednesday, May 25, 2016

Compiling Multicorn on OpenBSD

Install the following:

python 2.7

cd /tmp

git clone git://
cd Multicorn

Change the first line of so can run it with ksh

sed -i 1s/bash/sh/

(I've created a pull request to change this)


gmake && gmake install

Thursday, January 7, 2016

PostgreSQL 9.6 Wish List

First, a huge congratulations and thank you to the PostgreSQL Global Development Group for shipping such great features in 9.5!

I'm teaching myself C (I'm a Java guy) so that I can contribute down the road. 

Here's what I'd love to see in 9.6:

  • Okapi BM-25 as the default relevance measure for text search. SQLite has it, so it should be doable for Postgres. 
  • Overall, make text search a little more Solr-like (n-grams and such)
  • Allow custom, non-C functions (like PLV8) for text search parsing, so I can write my own parser
  • Allow parameters for SET ROLE ? and LISTEN ? so it's a bit safer to call them from a web application
  • Security cookie for SET ROLE, so that user cannot switch roles without permission. See this post for more info.
  • Async LISTEN/NOTIFY in the default JDBC driver.  pgjdbc-ng has it, so it should be doable.

Nice To Have:
  • Kill MD5 forever and use bcrypt or scrypt for password hashing
  • Raise an exception if an identifier is longer than 63 characters, instead of silently truncating it. This was an actual issue with Drupal Commerce
  • Begin SQL:2011 Temporal feature set