Using SQL as an every day tool

Every now and then I find myself in the situation where I need to analyze data coming varying sources, be it in the form of files (spreadsheets, CSV, etc.) or data spread across different servers and/or database platforms. The way that I often see people handle this is by loading the data into a spreadsheet (using Excel, LibreOffice or an alternative) and then start filtering and fidgeting away with spreadsheet formulas and functions, creating temporary columns and/or copying and pasting things around until they get their desired result. When you only have to examine a single table of data this is not much of an issue, it may in fact be the best option to start with: its low-barrier and using column filters is pretty intuitive in most spreadsheet software that I’ve used.

When your searching and filtering needs become more complicated and/or involve multiple tables of data that you want to combine in one way or another things tend to get a bit hairy (at least for me) and I personally prefer using SQL instead. In this article I will give an in-depth explanation on how you can do that using a contrived example, if you are experienced with SQL, command line tools and the shell you can probably skim large parts of this article, if most of this is new to you it hopefully contains enough detail for you to follow along.

Continue reading “Using SQL as an every day tool”

Tmux and SSH agent forwarding

When you normally connect to a machine using SSH and start a tmux session SSH agent forwarding (if you have that setup) will work normally. When you however detach your session and re-attach it later you will find that it no longer does, this is because the environment variables in the shell inside the tmux session refers to the same SSH_AUTH_SOCK that it did when you originally connected.

Recent enough versions of tmux have something that can fix this for you, if you execute the command tmux show-environment -s you will see a bunch of shell commands to set a series of environment variables. If you execute those it will update the SSH information in the shell and agent forwarding will work once again, a shortcut to doing this in a single line would be eval "$(tmux show-environment -s)".

If you have your own dotfiles on the destination machine you can make this happen automatically. For bash users there’s the PROMP_COMMAND environment variable (see https://www.johntobin.ie/blog/updating_environment_variables_from_tmux/), for zsh users (like myself) you can do this by specifying a precmd hook.

If you add the following to your .zshrc (or a file you source inside it) you can set this up:

function update_environment_from_tmux() {
  if [ -n "${TMUX}" ]; then
    eval "$(tmux show-environment -s)"
  fi
}

add-zsh-hook precmd update_environment_from_tmux

The above will ensure that the update_environment_from_tmux function is executed before each command that you execute, if you re-attach to a tmux session that is using zsh with the above loaded and execute an SSH related command (git pull, scp, sftp, ssh, etc.) it will first automatically update the SSH environment variables and then execute the command making it work seamlessly.

The update_environment_from_tmux function only evals the tmux show-environment -s command if you are inside a tmux session (which will set the TMUX environment variable).

Hello world!

Here we are, there and back again. After the longest of times I have brought back a WordPress blog for myself to write on about software development. Now time will have to tell if I have anything interesting to write here…