/udb.pl
use strict;
use DBI;
use Digest::MD5;
use Music::Tag;
use MP3::Info;
use File::Find;
use Encode;
$MP3::Info::try_harder = 1;
my $uthome = "$ENV{HOME}/.utunes";
my $dbh = DBI->connect("dbi:SQLite:dbname=$uthome/tunes.db","","")
or die "Could not open utunes DB";
$dbh->{} = 1;
my $rv = $dbh->do("SELECT count(*) FROM tunes LIMIT 1");
if (!$rv) {
print "Creating database schema...\n";
$dbh->do(qq{CREATE TABLE tunes (
hash varchar(32) primary key,
filename blob,
artist varchar(255),
title varchar(255),
album varchar(255),
genre varchar(255),
comment varchar(255),
track integer,
year integer,
length integer,
bitrate integer
)});
}
if (!@ARGV) {
print "Please specify a command or a query.\n";
exit 1;
}
my $pwd; # Our working directory won't change
chomp ($pwd = `pwd`);
{
my $path = shift;
return $path if $path =~ m'^/';
$path =~ s'^\./'';
return "$pwd/$path";
}
my @blacklist;
open BLACKLIST, "$uthome/blacklist";
while (<BLACKLIST>) {
chomp;
push @blacklist, qr/^$_$/;
}
close BLACKLIST;
{
my $file = shift;
foreach my $i (@blacklist) {
return 1 if $file =~ $i;
}
}
my %size_exp = (k => 10, m => 20, g => 30, t => 40);
{
my $q = shift;
$q =~ /([kmgb])b?$/i;
my $s = lc $1;
if (exists $size_exp{$s}) {
$q <<= $size_exp{$s};
}
return $q;
}
{
my $time = shift;
my @t = reverse split(/:/, $time);
warn "More than three segments in time value: $time"
if @t > 3;
my $v = shift @t;
my $m = 60;
while (@t) {
$v += (shift @t) * $m;
$m *= 60;
}
return $v;
}
{
my $file = fullpath(shift);
return unless -f $file;
return if blacklisted($file);
if (-l $file and !-e $file) {
print "$file seems to be a broken link.\n";
return;
}
# Note that we don't hash the file *data*, that's slow. We just hash
# the full path to get a unique id that's manageably short.
my $hash = Digest::MD5::md5_hex($file);
my $r = $dbh->selectrow_arrayref(qq{SELECT count(*) FROM tunes WHERE hash="$hash"});
if ($r->[0] == 1) {
return 1;
}
my $f = Music::Tag->new($file);
next unless @{$f->{}};
$f->get_tag();
my (%strings, %numeric);
%strings = (
title => $f->title(),
artist => $f->artist(),
album => $f->album(),
comment => $f->comment(),
genre => $f->genre(),
);
%numeric = (
year => $f->year(),
track => $f->track(),
length => $f->secs(),
bitrate => $f->bitrate(),
);
$f->close();
my (@keys, @values);
push @keys, 'hash';
push @values, qq/"$hash"/;
push @keys, 'filename';
$file =~ s/"/""/g;
push @values, qq/"$file"/;
foreach my $k (keys %strings) {
if (defined($strings{$k})) {
my $str = $strings{$k};
next unless $str;
push @keys, $k;
$str =~ s/"/""/g;
push @values, qq/"$str"/;
}
}
foreach my $k (keys %numeric) {
if ($numeric{$k}) { # Zero values are useless, too.
push @keys, $k;
$numeric{$k} += 0.0; # Make sure it's numeric
push @values, qq/$numeric{$k}/;
}
}
my $statement = "INSERT INTO tunes (" . join(',',@keys) . ") values (" . join(',',@values) . ")";
#print $statement,"\n";
$dbh->do($statement);
return 1;
}
{
my $file = fullpath(shift);
my $hash = Digest::MD5::md5_hex($file);
$dbh->do(qq{DELETE FROM tunes WHERE hash=\"$hash\"});
}
{
my $file = fullpath(shift);
my $hash = Digest::MD5::md5_hex($file);
my $r = $dbh->selectrow_hashref(qq{SELECT * FROM tunes WHERE hash="$hash"});
if ($r) {
print "$file\n";
} else {
print "$file not in db\n";
return;
}
foreach my $k ('artist','title','album','genre','track','year','comment') {
print "\t$k\t\t$r->{$k}\n";
}
my @time;
unshift @time, sprintf("%02d", $r->{'length'} % 60);
unshift @time, sprintf("%02d", int($r->{'length'} / 60) % 60);
unshift @time, int($r->{'length'} / 3600);
for (0..1) {
shift @time if $time[0] == 0;
}
print "\tlength\t\t", join(':',@time),"\n";
print "\tbitrate\t\t", $r->{'bitrate'}, "kbit\n";
print "\tsize\t\t", -s $file, "\n";
}
{
my (@where, $order, $ltype, $limit);
$order = 'ORDER BY artist,album,year,track';
foreach (@_) {
if (/^(artist|title|album|genre|comment|filename)(==|=)(.*?)$/) {
my ($k, $op, $v) = ($1, $2, $3);
$v =~ s/"/""/g;
if ($op eq '==') {
push @where, qq{$k LIKE "$v"};
} else {
push @where, qq{$k LIKE "\%$v\%"};
}
} elsif (/^(track|year|length|bitrate|samplerate|channels)(>|<|==|=)(.*)$/) {
my ($k, $op, $v) = ($1, $2, int($3));
if ($op eq '=' or $op eq '==') {
push @where, qq{$k=$v};
} elsif ($op eq '>' or $op eq '<') {
push @where, qq{$k$op$v};
}
} elsif ($_ eq 'random') {
$order = 'ORDER BY random()';
} elsif (/^limit==?(.*)$/) {
my $v = $1;
if ($v =~ /^\d+$/) {
$order .= " LIMIT $v";
} elsif ($v =~ /:/) {
$ltype = 'time';
$limit = time_expand($v);
} elsif ($v =~ /[bkmgt]$/) {
$ltype = 'size';
$limit = size_expand($v);
} else {
print "Unknown type of limit: $v\n";
exit 1;
}
} else {
s/"/""/g;
push @where, qq{(artist LIKE "\%$_\%" OR title LIKE "\%$_\%" OR album LIKE "\%$_\%" OR comment LIKE "\%$_\%" OR filename LIKE "\%$_\%")};
}
}
my $query = "SELECT filename,length FROM tunes ";
if (@where) {
$query .= "WHERE " . join(' AND ', @where) . ' ';
}
$query .= $order;
#print $query,"\n";
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute;
# /me kicks lisp in the teeth
if (not defined $ltype) {
return sub {
return ($sth->fetchrow_array)[0];
}
} elsif ($ltype eq 'time') {
my $length = 0;
return sub {
if ($length < $limit) {
my $fi = $sth->fetchrow_hashref;
$length += $fi->{};
return $fi->{};
}
}
} elsif ($ltype eq 'size') {
my $size = 0;
return sub {
if ($size < $limit) {
my $file = ($sth->fetchrow_array)[0];
$size += -s $file;
return $file;
}
}
}
}
if ($ARGV[0] eq 'add') {
shift;
$dbh->begin_work;
if (@ARGV) {
foreach (@ARGV) {
db_add($_);
}
} else {
while (<STDIN>) {
chomp;
db_add($_);
}
}
$dbh->commit;
} elsif ($ARGV[0] eq 'del' or $ARGV[0] eq 'rm') {
shift;
$dbh->begin_work;
if (@ARGV) {
foreach (@ARGV) {
db_rm($_);
}
} else {
while (<STDIN>) {
chomp;
db_rm($_);
}
}
$dbh->commit;
} elsif ($ARGV[0] eq 'clean') {
my $sth = $dbh->prepare('SELECT hash, filename FROM tunes');
$sth->execute;
my $r;
while ($r = $sth->fetchrow_hashref()) {
if (blacklisted($r->{}) ||
! (-e $r->{} || -e decode('utf8', $r->{}))) {
print "Purging $r->{filename}\n";
my $hash = $r->{};
$dbh->do('DELETE FROM tunes WHERE hash=?',
undef, $hash);
}
}
} elsif ($ARGV[0] eq 'info') {
shift;
if (@ARGV) {
foreach (@ARGV) {
db_info($_);
}
} else {
while (<STDIN>) {
chomp;
db_info($_);
}
}
} elsif ($ARGV[0] eq 'scan') {
shift;
unless (@ARGV) {
print "No directories specified\n";
exit 1;
}
for my $dir (@ARGV) {
unless (-d $dir) {
print "$dir is not a directory\n";
exit 1;
}
}
find(sub {
return unless -f;
db_add($File::Find::name) or print "Could not add $File::Find::name\n";
}, @ARGV);
} elsif ($ARGV[0] eq 'import') {
my $db = $ARGV[1];
unless ($db) {
print "You must specify a tunes db to import";
exit 1;
}
my $insert = $dbh->prepare("INSERT INTO tunes (hash,filename,artist,title,album,genre,comment,track,year,length,bitrate) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
my $update = $dbh->prepare("UPDATE tunes SET filename=?, artist=?, title=?, album=?, genre=?, comment=?, track=?, year=?, length=?, bitrate=? WHERE hash=?");
my $import_dbh = DBI->connect("dbi:SQLite:dbname=$db","","");
my $import_sth = $import_dbh->prepare("select hash,filename,artist,title,album,genre,comment,track,year,length,bitrate from tunes");
$import_sth->execute;
$dbh->begin_work;
while (my @r = $import_sth->fetchrow_array) {
my ($c) = $dbh->selectrow_array("SELECT count(*) FROM tunes WHERE hash=?", undef, $r[0]);
#print "$c ",join('|', @r), "\n\n";
if ($c) {
$update->execute(@r[1..$#r], $r[0]);
print "updated $r[1]\n" || die "failed to update";
} else {
$insert->execute(@r) || die "failed to insert";
print "inserted $r[1]\n";
}
}
$import_sth->finish;
$import_dbh->disconnect;
$dbh->commit;
} else {
my $results = db_select(@ARGV);
my $file;
print "$file\n" while $file = $results->();
}
$dbh->disconnect();