Software is the only business in which adding extra lanes to the Golden Gate bridge would be called maintenance. -- David Tilbrook

I had to figure this out today to automate some log cleanup:

  1. find all files in the current directory
  2. that are older than 30 days
  3. but not in directories DIR1, DIR2 or DIR3
  4. print the matching file names
  5. and delete them

This is what I came up with:

find . -regextype posix-extended -type f -a -not -regex "^\.\/(DIR1\/|DIR2\/|DIR3\/).*" -a -mtime +30 -exec rm -vf {} \;

I tried doing combinations of -wholepath but it just didn't work. Further, I didn't want to use grep to filter out the directories because there's no corresponding flag in grep to handle the -print0 output from find.

From isql to bcp

I am currently working on migrating a bunch of shell/perl reporting scripts from Solaris to Linux. Clearly, a trivial task since it's all POSIX ... until it's not!

Some of the reports do things which involve dumping data from one database, importing it into a temp table in another database and then doing further processing of the data there. Normally, this would be done using Sybase's bcp utility; you would bcp out from the source database & then bcp in at the target database. However, bcp out works only on full tables or views. You can't provide a select query and get the output of that query in a bcp in compatible format.

The scripts I was working on were relying on some Solaris executable to dump data from a select query in a format that was compatible for import using a bcp in command. Since I didn't have the source code of this binary (typical!), here's how I reproduced its functionality.

First, consider the isql query:

$ isql -U$USER -P$PASSWORD -S$SERVER -I$INTERFACES -o$OUT_FILE << EOF
select * from SUPERHEROES where WEAKNESS like '%Kryptonite%'
EOF

Which results in:

$ cat $OUT_FILE
ID   NAME          ALIAS          POWER                 WEAKNESS
---  -----------  -------------  --------------------  --------------
12   Superman      Kal-El         Flight,X-Ray Vision   Kryptonite
13   Supergirl     Kara Zor-El    Flight,X-Ray Vision   Kryptonite

(2 records selected)

But in order for the output to be importable by bcp in, it has to look like this:

12|Superman|Kal-El|Flight,X-Ray Vision|Kryptonite
13|Supergirl|Kara Zor-El|Flight,X-Ray Vision|Kryptonite

In effect, we need to:

  1. Lose the header rows
  2. Use a better delimiter instead of whitespace
  3. Trim the field values of extra whitespace
  4. Lose the footer rows

For requirements 1 & 2, we can modify the isql statement as follows:

$ isql -b -s"|" -w9999 -U$USER -P$PASSWORD -S$SERVER -I$INTERFACES -o$OUT_FILE << EOF
select * from SUPERHEROES where WEAKNESS like '%Kryptonite%'
EOF

The -b flag removes the header rows and the -s flag asks isql to use the pipe character as the field delimiter in the output. The -w flag is added for good measure: it specifies the width of each output line. The default value of 80 characters is likely to cause each output row to be split over multiple output lines.

The output now looks something like this:

|12 |Superman     |Kal-El       |Flight,X-Ray Vision  |Kryptonite     |
|13 |Supergirl    |Kara Zor-El  |Flight,X-Ray Vision  |Kryptonite     |

(2 records selected)

The footer can be removed by:

$ head -n -2 $OUT_FILE > ${OUT_FILE}.new
$ mv ${OUT_FILE}.new $OUT_FILE

Update (29/Nov/2012) : Just use the set nocount on option in your SQL query which will skip printing of the footer.

And finally, we are left with the task of removing all the extra whitespace around field values. Here's a quick & dirty Python script that does the job:

#!/usr/bin/env python
"""
A simple utility that takes isql generated output
and converts it into a format suitable for import
via 'bcp in'.

The isql output should ideally be generated using
the following flags:
  isql -b -w9999 -s"|"

The delimiter is especially important since this
script assumes pipe ("|") as the field delimiter.

__WARN: The input file will be overwritten!__

Aside: This whole file could probably be replaced
by a one line sed/awk incantation, if I knew how.
"""
import shutil
import sys

DELIM = "|"
NL = "\n"

if len(sys.argv) != 2:
    print >> sys.stderr, """Usage: 
    isql2bcp.py <input_file>"""
    sys.exit(1)

infile = sys.argv[1]
outfile = infile + ".new"

in_fp = open(infile,'r')
out_fp = open(outfile,'w')

for line in in_fp:
    # Strip each line of leading & trailing DELIM
    # as well as trailing newline
    line = line.strip(DELIM+NL)
    tokens = line.split(DELIM)
    # Remove the extra whitespace surrounding each token
    tokens =  [t.strip() for t in tokens]
    line = DELIM.join(tokens)
    out_fp.write(line + NL)

in_fp.close()
out_fp.close()

shutil.move(outfile, infile)

The script can then be invoked as:

$ isql2bcp.py $OUT_FILE

Resulting in output that can be imported using bcp as:

$ bcp $TEMPDB..$TABLE in $OUT_FILE -c -t"|" -I$INTERFACES -S$SERVER -U$USER -P$PASSWORD

That's it. I hope this will be useful to someone!

Lens Hack

I recently got a couple of D-Link 942L IP cameras. Unfortunately, these units have a very narrow field of view which meant that they didn't really cover the area we wanted to monitor. So I looked around and found a site called dealextreme.com selling stick-on lenses. Bought two, a 13mm wide-angle lens and a much wider fish-eye.

W-67 wide-angle lens  FE-12 fish-eye lens

Here's the fish-eye out of the package:

fish-eye unpacked

The small metal rings that you see in the photo above have glue on one side and are meant to be stuck permanently around your camera's lens. The fish-eye lens has a magnetic ring around its base allowing you to quickly attach/detach the lens to your camera, thanks to the pasted-on metallic ring.

Although these lenses are marketed as mobile phone camera add-ons, they fit perfectly on the D-Link cameras!

At roughly 10$ per lens, I couldn't be happier with this purchase. They've made the D-Link cameras useful!

Here are the links if you want to get these lenses:

Raspberry Pi

I got my Raspberry Pi (photos) about a month ago and got time to set it up a couple of weeks back. At the moment, it is running Raspbmc RC4 which is a distribution of XBMC tailored just for this device.

Raspberry Pi

The Pi has been playing most of my video & audio collection without issues over LAN. Video is smooth with hardly any stuttering or frame-drops. Audio over HDMI works just fine playing full DTS or surround sound via my Sony. Raspmbc also supports HDMI-CEC pretty well so I can control it using my TV's remote control.

XBMC on Raspberry Pi

Here's what doesn't work at the moment:

  • Fast forward & rewind for videos doesn't work. I've to either jump or direct seek, which work without issues.
  • Haven't found a way to activate the XBMC Context Menu using the TV remote
  • Music & TV show libraries show up empty on the Android phone remote.
  • There's a noticeable delay (about 2-3 seconds) switching between normal interface & full screen video playback.

If you notice, these are all XBMC issues. The Pi itself is great and for the price, it is a noiseless, heatless computing powerhouse!

More photos on G+

Update (02/09/2012) The delay switching from normal UI to full screen video playback went away by disabling the Adjust display refresh rate to match video option in XBMC's Video Playback settings screen. And using the following custom remote mapping file, I can now access the Context Menu by pressing the TV remote's yellow color key labelled "C":

<!-- /home/pi/.xbmc/userdata/keymaps/remote.xml -->
<!-- Configuration file for driving CEClib remote  -->
<keymap>
  <global>
    <remote>
      <green>Info</green>
      <yellow>ContextMenu</yellow>
      <blue>XBMC.ActivateWindow(Favourites)</blue>
    </remote>
  </global>
  <FullscreenVideo>
    <remote>
      <select>OSD</select>
      <green>Info</green>
      <yellow>CodecInfo</yellow>
      <blue>NextSubtitle</blue>
    </remote>
  </FullscreenVideo>
</keymap>