songproc

A couple of days ago, I wrote a simple Perl script, which really isn't all that special on its own:

collection.pl

#!/usr/bin/perl
use strict;
use POSIX qw(ceil floor);
use DBI;
my $dbname = $ENV{HOME} . "/.kde/share/apps/amarok/collection.db";
my $db = DBI->connect("dbi:SQLite:$dbname") || die "Database $dbname doesn't exist!\n";
my $datas = $db->selectall_arrayref("SELECT artist.name, tags.title, album.name, tags.track, tags.length, year.name FROM tags INNER JOIN artist ON artist.id = tags.artist INNER JOIN album ON album.id = tags.album INNER JOIN year ON year.id = tags.year");
my %albums;
foreach my $data (@{$datas}) {
my ($artist, $title, $album, $track, $dur, $year) = (${$data}[0], ${$data}[1], ${$data}[2], ${$data}[3],${$data}[4], ${$data}[5]);
my $length = $dur / 60;
my $min = floor($length);
my $sec = ceil(($length - $min) * 60);

$length = sprintf("%u:%02u", $min, $sec);
print "$artist\t$title\t$album\t$track\t$length\t$year\n";

}
$db->disconnect;

This script operates on Amarok's collection database for the current user, but only under 'normal-ish' Unix systems. However, the usefulness isn't in this script, but in the following. The 'and' version requires all given conditions to be met, and the 'or' version requires only one match:

songproc-and.awk

BEGIN {
FS=" "
};
(!length(artist) && !length(title) && !length(album)) ||
(length(artist) && length(title) && length(album) && $1 ~ artist && $2 ~ title && $3 ~ album) ||
(length(artist) && length(title) && !length(album) && $1 ~ artist && $2 ~ title) ||
(length(artist) && !length(title) && length(album) && $1 ~ artist && $3 ~ album) ||
(!length(artist) && length(title) && length(album) && $2 ~ title && $3 ~ album) ||
(length(artist) && !length(title) && !length(album) && $1 ~ artist) ||
(!length(artist) && length(title) && !length(album) && $2 ~ title) ||
(!length(artist) && !length(title) && length(album) && $3 ~ album) {
print $_
}

songproc-or.awk

BEGIN {
FS=" "
};
(!length(artist) && !length(title) && !length(album)) || (length(artist) && $1 ~ artist) || (length(title) && $2 ~ title) || (length(album) && $3 ~ album) {
print $_
}

In either case, if no variables are defined it'll return all songs. Here's an example of a query:

% perl collection.pl | awk -f songproc-and.awk artist=中島みゆき album=パラダイス・カフェ | sort -n -t"`tab`" --key=4,4
中島みゆき 旅人のうた パラダイス・カフェ 1 5:03 1996
中島みゆき 伝説 パラダイス・カフェ 2 4:45 1996
中島みゆき 永遠の嘘をついてくれ パラダイス・カフェ 3 5:59 1996
中島みゆき Alone, Please パラダイス・カフェ 4 5:12 1996
中島みゆき それは愛ではない パラダイス・カフェ 5 4:15 1996
中島みゆき なつかない猫 パラダイス・カフェ 6 4:43 1996
中島みゆき Singles Bar パラダイス・カフェ 7 5:55 1996
中島みゆき 青い時代 パラダイス・カフェ 8 3:47 1996
中島みゆき たかが愛 パラダイス・カフェ 9 5:45 1996
中島みゆき 阿檀の木の下で パラダイス・カフェ 10 6:40 1996
中島みゆき パラダイス・カフェ パラダイス・カフェ 11 5:21 1996

This is a listing of all songs by 中島みゆき in the album パラダイス・カフェ, sorted by their track number. If I wanted to reorder the fields, I could just use a simple awk print. Also, I created a simple script that outputs a tab, since it's so painful to do in shells with tab-completion.

#!/bin/sh
echo -e '\t'

Not really worth taking up a whole line, but it's an amazingly useful script.