Added database blacklisting
+#!/usr/bin/perl
+use strict;
+
+use DBI;
+use Digest::MD5;
+use Audio::TagLib;
+
+my $uthome = "$ENV{HOME}/.utunes";
+
+my $dbh = DBI->connect("dbi:SQLite:dbname=$uthome/tunes.db","","");
+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,
+ samplerate integer,
+ channels 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`);
+sub fullpath {
+ my $path = shift;
+ return $path if m'^/';
+ return "$pwd/$path";
+}
+
+sub db_add {
+ my $file = fullpath(shift);
+ 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;
+ }
+
+ my $f = Audio::TagLib::FileRef->new($file);
+ return unless $f->file();
+
+ my (%strings, %numeric);
+ if ($f->tag()) {
+ %strings = (
+ title => $f->tag()->title(),
+ artist => $f->tag()->artist(),
+ album => $f->tag()->album(),
+ comment => $f->tag()->comment(),
+ genre => $f->tag()->genre(),
+ );
+
+ %numeric = (
+ year => $f->tag()->year(),
+ track => $f->tag()->track(),
+ );
+ }
+
+ %numeric = (%numeric,
+ length => $f->audioProperties()->length(),
+ bitrate => $f->audioProperties()->bitrate(),
+ samplerate => $f->audioProperties()->sampleRate(),
+ channels => $f->audioProperties()->channels(),
+ );
+
+ 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}->stripWhiteSpace()->toCString(1);
+ 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);
+}
+
+sub db_rm {
+ my $file = fullpath(shift);
+ my $hash = Digest::MD5::md5_hex($file);
+ $dbh->do(qq{DELETE FROM tunes WHERE hash=\"$hash\"});
+}
+
+sub db_info {
+ my $file = fullpath(shift);
+ print "$file\n";
+ my $hash = Digest::MD5::md5_hex($file);
+ my $r = $dbh->selectrow_hashref(qq{SELECT * FROM tunes WHERE hash="$hash"});
+ 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 "\tsamplerate\t", $r->{'samplerate'} / 1000, "kHz\n";
+ print "\tchannels\t", $r->{'channels'}, "\n";
+}
+
+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(qq{SELECT hash,filename FROM tunes});
+ $sth->execute;
+ my $r;
+ while ($r = $sth->fetchrow_hashref()) {
+ if (! -e $r->{filename}) {
+ print "Purging $r->{filename}\n";
+ my $hash = $r->{hash};
+ $dbh->do(qq{DELETE FROM tunes WHERE hash="$hash"});
+ }
+ }
+} elsif ($ARGV[0] eq 'info') {
+ shift;
+ if (@ARGV) {
+ foreach (@ARGV) {
+ db_info($_);
+ }
+ } else {
+ while (<STDIN>) {
+ chomp;
+ db_info($_);
+ }
+ }
+} elsif ($ARGV[0] eq 'random') {
+ my $n_files;
+ if ($ARGV[1]) {
+ $n_files = $ARGV[1];
+ } else {
+ ($n_files) = $dbh->selectrow_array(qq{SELECT count(*) FROM tunes});
+ }
+
+ # This could take a very large amount of memory...
+ my @files = @{$dbh->selectcol_arrayref(qq{SELECT filename FROM tunes})};
+
+ for my $i (1..$n_files) {
+ my $n = int(rand(@files));
+ print scalar splice(@files, $n, 1), "\n";
+ last unless @files;
+ }
+} else {
+ my @where;
+ foreach (@ARGV) {
+ if (/^(artist|title|album|genre|comment)(==|=)(.*?)$/) {
+ 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};
+ }
+ } else {
+ s/"/""/g;
+ push @where, qq{(artist LIKE "\%$_\%" OR title LIKE "\%$_\%" OR album LIKE "\%$_\%" OR comment LIKE "\%$_\%" OR filename LIKE "\%$_\%")};
+ }
+ }
+ my $query = "SELECT filename FROM tunes WHERE " . join(' AND ', @where);
+ #print $query,"\n";
+ my $sth = $dbh->prepare($query);
+ $sth->execute;
+ while (my $file = ($sth->fetchrow_array)[0]) {
+ print "$file\n";
+ }
+}
+
+$dbh->disconnect();
-#!/usr/bin/perl
-use strict;
-
-use DBI;
-use Digest::MD5;
-use Audio::TagLib;
-
-my $uthome = "$ENV{HOME}/.utunes";
-
-my $dbh = DBI->connect("dbi:SQLite:dbname=$uthome/tunes.db","","");
-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,
- samplerate integer,
- channels 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`);
-sub fullpath {
- my $path = shift;
- return $path if m'^/';
- return "$pwd/$path";
-}
-
-sub db_add {
- my $file = fullpath(shift);
- 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;
- }
-
- my $f = Audio::TagLib::FileRef->new($file);
- return unless $f->file();
-
- my (%strings, %numeric);
- if ($f->tag()) {
- %strings = (
- title => $f->tag()->title(),
- artist => $f->tag()->artist(),
- album => $f->tag()->album(),
- comment => $f->tag()->comment(),
- genre => $f->tag()->genre(),
- );
-
- %numeric = (
- year => $f->tag()->year(),
- track => $f->tag()->track(),
- );
- }
-
- %numeric = (%numeric,
- length => $f->audioProperties()->length(),
- bitrate => $f->audioProperties()->bitrate(),
- samplerate => $f->audioProperties()->sampleRate(),
- channels => $f->audioProperties()->channels(),
- );
-
- 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}->stripWhiteSpace()->toCString(1);
- 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);
-}
-
-sub db_rm {
- my $file = fullpath(shift);
- my $hash = Digest::MD5::md5_hex($file);
- $dbh->do(qq{DELETE FROM tunes WHERE hash=\"$hash\"});
-}
-
-sub db_info {
- my $file = fullpath(shift);
- print "$file\n";
- my $hash = Digest::MD5::md5_hex($file);
- my $r = $dbh->selectrow_hashref(qq{SELECT * FROM tunes WHERE hash="$hash"});
- 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 "\tsamplerate\t", $r->{'samplerate'} / 1000, "kHz\n";
- print "\tchannels\t", $r->{'channels'}, "\n";
-}
-
-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(qq{SELECT hash,filename FROM tunes});
- $sth->execute;
- my $r;
- while ($r = $sth->fetchrow_hashref()) {
- if (! -e $r->{filename}) {
- print "Purging $r->{filename}\n";
- my $hash = $r->{hash};
- $dbh->do(qq{DELETE FROM tunes WHERE hash="$hash"});
- }
- }
-} elsif ($ARGV[0] eq 'info') {
- shift;
- if (@ARGV) {
- foreach (@ARGV) {
- db_info($_);
- }
- } else {
- while (<STDIN>) {
- chomp;
- db_info($_);
- }
- }
-} elsif ($ARGV[0] eq 'random') {
- my $n_files;
- if ($ARGV[1]) {
- $n_files = $ARGV[1];
- } else {
- ($n_files) = $dbh->selectrow_array(qq{SELECT count(*) FROM tunes});
- }
-
- # This could take a very large amount of memory...
- my @files = @{$dbh->selectcol_arrayref(qq{SELECT filename FROM tunes})};
-
- for my $i (1..$n_files) {
- my $n = int(rand(@files));
- print scalar splice(@files, $n, 1), "\n";
- last unless @files;
- }
-} else {
- my @where;
- foreach (@ARGV) {
- if (/^(artist|title|album|genre|comment)(==|=)(.*?)$/) {
- 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};
- }
- } else {
- s/"/""/g;
- push @where, qq{(artist LIKE "\%$_\%" OR title LIKE "\%$_\%" OR album LIKE "\%$_\%" OR comment LIKE "\%$_\%" OR filename LIKE "\%$_\%")};
- }
- }
- my $query = "SELECT filename FROM tunes WHERE " . join(' AND ', @where);
- #print $query,"\n";
- my $sth = $dbh->prepare($query);
- $sth->execute;
- while (my $file = ($sth->fetchrow_array)[0]) {
- print "$file\n";
- }
-}
-
-$dbh->disconnect();