November 2008 Archives

I experimented with audio recording of first article draft in last few days.

Recording on EeePC with Audacity went very well (allthough I was recording to slow SD card) after I remembered to to decrease quality to preserve disk space.

I was using microphone on cheap headphones for recording because it doesn't record typing on keyboard and laptop noise, as built-in mics do.

By default, Audacity records in stereo, and I recorded in stereo allthough there is just one microphone on headphones because left channel had higher gain (and thus, noise) than right. Other than that, channels appeared identical, so I decided to throw away noisy right channel and turn left channel to mono.

In the end, I exported from Audacity project to mp3 directly on EeePC because source files where too big for transfer over wifi :-)

After I transfered mp3 files to ThinkPad I was presented with a list of mp3 files to listen (5 parts, about 3 hour of material) and I knew I have to do something.

Quick.

So after a first 4 parts or so I ended up with setup like this:

Small shell script and vim config you can do wonders:

#!/bin/sh

# moc(p) support for audio transcribing
#
# 2008-11-20 Dobrica Pavlinusic <dpavlin@rot13.org>

cmd="--toggle-pause"

if echo "x$1x" | grep '^x-*[0-9][0-9]*x$' > /dev/null ; then
cmd="--seek $1"
elif [ ! -z "$1" ] ; then
mocp --info | grep "^$1: " | sed 's/^.*: //'
exit
fi

mocp $cmd
mocp --info |\
egrep '^(State|File|Title|CurrentTime|TotalTime)' |\
egrep '^(State|File|Title|CurrentTime|TotalTime)' | sed 's/^[^:]*: *//' |\
osd_cat -p bottom -A center -s 3 -f neep-24 -O 2 -d 1 -l 5

exit


And insert following in ~/.vimrc:


" play-pause
noremap <esc>[33~ :silent execute "! play-toggle.sh &"<cr>:redraw!<cr>
" seek 5 sec backward
noremap <esc>[34~ :silent execute "! play-toggle.sh -5 &"<cr>:redraw!<cr>
" CurrentTime from mocp --info
noremap <esc>[29~ :r !play-toggle.sh CurrentTime<cr>


Wouldn't it be nice to have a CGI script which would convert bunch of SQL queries into XLS file on-the-fly? And while we are at it, let's have multiple reports, each in it's own directory?

In a sense, it's simple REST API to SQL files on disk which produce Excel files. I first wrote something like this back in 2002, but until now, I didn't have subversion repository for it or announced it to the world.

Each file in current directory which ends in *.sql will be converted to Excel sheet. If you want to have specific order, you can prefix filenames with numbers which will be striped when creating sheet names.

Comments in sql files (lines beginning with --) will be placed in first line in bold.

To specify database on which SQL query is executed \c database syntax is supported.

You can also run script from command line, and it will produce sql_reports.xls file.

If run within directory, it will use files in it to produce file.

When called as CGI, directory name can be appended to name of script to produce report for any sub-directory within directory where sql2xls.cgi is installed.

INSTALLATION

Only required file is this script sql2xls.cgi

If your server is configured to execute .cgi files, you can drop this script anywhere, but you can also add something like

   ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi

in Apache's virtual host configuration to get nice URLs

To configure default database, user, password and other settings create config.pl file in same directory in which sql2xls.cgi is with something like this:

  $dsn      = 'DBI:mysql:dbname=';
  $database = 'database';
  $user     = 'user';
  $passwd   = 'password';
  $path     = 'sql_reports.xls';

  $db_encoding     = 'utf-8';
  $xls_date_format = 'dd.mm.yyyy';
  $debug = 1;

SECURITY

There is none. Use apache auth modules if you need it.

So, you want to checkout source code on some server and at the same time have ability to commit or diff against local copy on your laptop? Seems like an easy task, but it does involve some unexpected steps (for me at least), so here is a quick how to...

Create ssh tunnel from target host back to your laptop (called llin in output). Edit .ssh/config and add something like:

Host server-dev.rot13.org server-dev
	Hostname server-dev.rot13.org
	RemoteForward 8022 127.0.0.1:22

You will notice that I added short name so I can type just ssh server-dev because I'm lazy.

When you login to server-dev you might think that something like svn checkout is everything that is left. However, that doesn't really work:

dpavlin@server-dev:~$ svn ls svn+ssh://localhost:8022/home/dpavlin/private/svn/SQL2XLS
ssh: localhost:8022: Name or service not known
svn: Connection closed unexpectedly

It seems that subversion doesn't like port number within hostname! So, let's make .ssh/config there also:

Host llin-svn
	Hostname localhost
	Port 8022

Let's try it out:

dpavlin@server-dev:~$ svn ls svn+ssh://llin-svn/home/dpavlin/private/svn/SQL2XLS
The authenticity of host 'localhost (127.0.0.1)' can't be established.
RSA key fingerprint is 00:11:22:33:44:55:66:77:88:99:aa:bb:cc:dd:ee:ff.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'localhost' (RSA) to the list of known hosts.
Password or swipe finger: 

Much better, but is asks us for password every time. We don't really like that, so we'll create ssh keys to get around this:

dpavlin@server-dev:~$ ssh-keygen -f .ssh/llin-svn
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in .ssh/llin-svn.
Your public key has been saved in .ssh/llin-svn.pub.
The key fingerprint is:
aa:bb:cc:dd:ee:ff:00:11:22:33:44:55:66:77:88:99 dpavlin@server-dev.rot13.org

Now we will insert generated .ssh/llin-svn.pub into .ssh/authorized_keys on laptop but allow only svnserver to be started:

command="svnserve -t" ssh-rsa AAA...rest of key...AAA== dpavlin@server-dev.rot13.org

If you want normal ssh login back to your laptop, you might leave out command="svnserve -t", but this makes me feel better. On the other hand, tunnel will be open only when we are logged into server-dev, but I usually prefer more security if possible. If you don't want to commit back to laptop, you might add -R flag to make repository read-only.

But wait, there is more! We need to tell ssh on server-dev that we are using newly generated key, so our final .ssh/config looks like this:

Host llin-svn
	Hostname localhost
	Port 8022
	IdentityFile ~/.ssh/llin-svn

We can test it now to make sure that subversion doesn't ask for password by simply checking out source code:

dpavlin@server-dev:~$ svn co svn+ssh://llin-svn/home/dpavlin/private/svn/SQL2XLS
Checked out revision 0.

Happy hacking!