#!/usr/bin/perl
#H# This script performs regular maintenance tasks to keep my database in sync with
#H# my file system.
#H# 
#H# Commands:
#H# info [filename]
#H#         Prints the information about a file from sored in the db
#H# add [filename] [category] [genre] [dir]
#H#         Initially adds a file to the system and the db.
#H#         Details will be derived from file name.
#H#         The dir parameter is optional and is used to override
#H#         the default location of /home/asylum/kmk/media/Music/category/artist/album
#H# rename [old filename] [new filename]
#H#         Renames a file, updates the ID3 tag, and updates the db.
#H# genre [filename] [genre number]
#H#         Changes the id3v2 genre of a file and updates the database
#H# mv [filename] [new path]
#H#         Moves a file to a different directory and updates the database
#H# rm [filename]
#H#         Removes a file from both the database and the filesystem!
#H# replace [old file] [new file]
#H#         Replaces an existing mp3 file with a new one while maintaining all
#H#         original playlist settings.  (not yet implamented)
#H#         Note that if the file names are different the new one will be used.
#H# category [filename] [category] [genre number]
#H#         Moves a file to a different category and updates the tag and db.
#H# +pl [filename] [playlist]
#H#         Adds a file to a playlist
#H#         Also sets +ipod.
#H# -pl [filename] [playlist]
#H#         Removes a file from a playlist
#H#         if [playlist] is "all" then remove from all playlists and from ipod.
#H# verify [filename]
#H#         Verifies the validity of a file based on the md5sum sored in the db
#H# verifydir (recursive)
#H#         Verifies the validity of all files in and under the current dir
#H# md5 [filename]
#H#         Updates the md5sum in the db
#H# index | idx
#H#         Recreates all playlists from the db and re-indexes all symlinks
#H# relink
#H#         Rescans the entire filesystem for symlinks and pictures
#H# +ipod [filename]
#H#         Records that a file is on my iPod (doesn't mean anything yet)
#H# -ipod [filename]
#H#         Records that a file is NOT on my iPod (doesn't mean anything yet)
#H# +myrip [filename]
#H#         Records that a file IS my rip (doesn't mean anything yet)
#H# -myrip [filename]
#H#         Records that a file is NOT my rip (doesn't mean anything yet)
#H# +best [filename]
#H#         Records that a file IS the best version of a song (doesn't mean anything yet)
#H# -best [filename]
#H#         Records that a file is NOT the best version of a song (doesn't mean anything yet)
#H# 
#H# All [filename] params can be replaced with "-current" to use whatever file is playing in
#H# xmms.  Note that this requires xmmsctrl to be installed.

# print out help info if requested
if ($ARGV[0] eq "-h" || $ARGV[0] eq "--help" || $ARGV[0] eq "help" || $ARGV[0] eq "") {
  open (SELF, $0);
  while (<SELF>) {
    if ($_ =~ /^#H# /) {
      $_ =~ s/^#H# //;
      print $_;
    }
  }
  exit;
}

# open the music database and load modules
use Date::Format;
use DBI;
$DB= DBI->connect("DBI:mysql:music:asylum.sanitarium.net" . ";mysql_read_default_file=/home/asylum/kmk/.my.cnf", kmk, $password);

# figure out which command we are running here
$Command=$ARGV[0];
chomp $Command;

# ANSI colors...
$Black=	"\e[0;30m";
$Red=	"\e[0;31m";
$Green=	"\e[0;32m";
$Yellow="\e[0;33m";
$Blue=	"\e[0;34m";
$Purple="\e[0;35m";
$Cyan=	"\e[0;36m";
$White=	"\e[0;37m";

# Put all of the genre names into a hash so I can mess with them later.
%Genres=();
$Genres{"0"}="Blues";
$Genres{"1"}="Classic Rock";
$Genres{"2"}="Country";
$Genres{"3"}="Dance";
$Genres{"4"}="Disco";
$Genres{"5"}="Funk";
$Genres{"6"}="Grunge";
$Genres{"7"}="Hip-Hop";
$Genres{"8"}="Jazz";
$Genres{"9"}="Metal";
$Genres{"10"}="New Age";
$Genres{"11"}="Oldies";
$Genres{"12"}="Other";
$Genres{"13"}="Pop";
$Genres{"14"}="R&B";
$Genres{"15"}="Rap";
$Genres{"16"}="Reggae";
$Genres{"17"}="Rock";
$Genres{"18"}="Techno";
$Genres{"19"}="Industrial";
$Genres{"20"}="Alternative";
$Genres{"21"}="Ska";
$Genres{"22"}="Death Metal";
$Genres{"23"}="Pranks";
$Genres{"24"}="Soundtrack";
$Genres{"25"}="Euro-Techno";
$Genres{"26"}="Ambient";
$Genres{"27"}="Trip-Hop";
$Genres{"28"}="Vocal";
$Genres{"29"}="Jazz+Funk";
$Genres{"30"}="Fusion";
$Genres{"31"}="Trance";
$Genres{"32"}="Classical";
$Genres{"33"}="Instrumental";
$Genres{"34"}="Acid";
$Genres{"35"}="House";
$Genres{"36"}="Game";
$Genres{"37"}="Sound Clip";
$Genres{"38"}="Gospel";
$Genres{"39"}="Noise";
$Genres{"40"}="Alt. Rock";
$Genres{"41"}="Bass";
$Genres{"42"}="Soul";
$Genres{"43"}="Punk";
$Genres{"44"}="Space";
$Genres{"45"}="Meditative";
$Genres{"46"}="Instrum. Pop";
$Genres{"47"}="Instrum. Rock";
$Genres{"48"}="Ethnic";
$Genres{"49"}="Gothic";
$Genres{"50"}="Darkwave";
$Genres{"51"}="Techno-Indust.";
$Genres{"52"}="Electronic";
$Genres{"53"}="Pop-Folk";
$Genres{"54"}="Eurodance";
$Genres{"55"}="Dream";
$Genres{"56"}="Southern Rock";
$Genres{"57"}="Comedy";
$Genres{"58"}="Cult";
$Genres{"59"}="Gangsta";
$Genres{"60"}="Top 40";
$Genres{"61"}="Christian Rap";
$Genres{"62"}="Pop/Funk";
$Genres{"63"}="Jungle";
$Genres{"64"}="Native American";
$Genres{"65"}="Cabaret";
$Genres{"66"}="New Wave";
$Genres{"67"}="Psychadelic";
$Genres{"68"}="Rave";
$Genres{"69"}="Showtunes";
$Genres{"70"}="Trailer";
$Genres{"71"}="Lo-Fi";
$Genres{"72"}="Tribal";
$Genres{"73"}="Acid Punk";
$Genres{"74"}="Acid Jazz";
$Genres{"75"}="Polka";
$Genres{"76"}="Retro";
$Genres{"77"}="Musical";
$Genres{"78"}="Rock & Roll";
$Genres{"79"}="Hard Rock";
$Genres{"80"}="Folk";
$Genres{"81"}="Folk/Rock";
$Genres{"82"}="National Folk";
$Genres{"83"}="Swing";
$Genres{"84"}="Fusion";
$Genres{"85"}="Bebob";
$Genres{"86"}="Latin";
$Genres{"87"}="Revival";
$Genres{"88"}="Celtic";
$Genres{"89"}="Bluegrass";
$Genres{"90"}="Avantgarde";
$Genres{"91"}="Gothic Rock";
$Genres{"92"}="Progress. Rock";
$Genres{"93"}="Psychadel. Rock";
$Genres{"94"}="Symphonic Rock";
$Genres{"95"}="Slow Rock";
$Genres{"96"}="Big Band";
$Genres{"97"}="Chorus";
$Genres{"98"}="Easy Listening";
$Genres{"99"}="Acoustic";
$Genres{"100"}="Humour";
$Genres{"101"}="Speech";
$Genres{"102"}="Chanson";
$Genres{"103"}="Opera";
$Genres{"104"}="Chamber Music";
$Genres{"105"}="Sonata";
$Genres{"106"}="Symphony";
$Genres{"107"}="Booty Bass";
$Genres{"108"}="Primus";
$Genres{"109"}="Porn Groove";
$Genres{"110"}="Satire";
$Genres{"111"}="Slow Jam";
$Genres{"112"}="Club";
$Genres{"113"}="Tango";
$Genres{"114"}="Samba";
$Genres{"115"}="Folklore";
$Genres{"116"}="Ballad";
$Genres{"117"}="Power Ballad";
$Genres{"118"}="Rhythmic Soul";
$Genres{"119"}="Freestyle";
$Genres{"120"}="Duet";
$Genres{"121"}="Punk Rock";
$Genres{"122"}="Drum Solo";
$Genres{"123"}="A Capella";
$Genres{"124"}="Euro-House";
$Genres{"125"}="Dance Hall";
$Genres{"126"}="Goa";
$Genres{"127"}="Drum & Bass";
$Genres{"128"}="Club-House";
$Genres{"129"}="Hardcore";
$Genres{"130"}="Terror";
$Genres{"131"}="Indie";
$Genres{"132"}="BritPop";
$Genres{"133"}="Negerpunk";
$Genres{"134"}="Polsk Punk";
$Genres{"135"}="Beat";
$Genres{"136"}="Christian Gangsta Rap";
$Genres{"137"}="Heavy Metal";
$Genres{"138"}="Black Metal";
$Genres{"139"}="Crossover";
$Genres{"140"}="Contemporary Christian";
$Genres{"141"}="Christian Rock";
$Genres{"142"}="Merengue";
$Genres{"143"}="Salsa";
$Genres{"144"}="Thrash Metal";
$Genres{"145"}="Anime";
$Genres{"146"}="Jpop";
$Genres{"147"}="Synthpop";

if ($Command eq "idx") { $Command="index"; }

if ($Command ne "verifydir" && $Command ne "index" && $Command ne "relink") {
  # Might as well figure all this crap out before even bothering to figure out what
  # command we are processing since almost all will need it.
  # The verifydir and index commands don't use any params so there is nothing to
  # do for them until the command specific stuff.

  # The file name is almost always the first param (after the command) so suck it in now
  # and replace it later if needed.
  $FileNameParam=$ARGV[1];

  # allow -current to probe xmms for the currently playing file and use that instead.
  if ($FileNameParam eq "-current") {
    #$FileNameParam=`xmmsctrl print %F`;
    $FileNameParam=`audtool current-song-filename | sed -e 's/^file...//' -e 's/%20/ /g'`;
    chomp $FileNameParam;
  }

  # find the real full path to the file name so I can use relative paths on the CLI
  $FullPath=`realpath "$FileNameParam"`;
  chomp $FullPath;
  # Get the full real path of the directory that the file is in.
  $Path=`dirname "$FullPath"`;
  chomp $Path;
  # Get just the file name
  @PathParts = split (/\//,$FullPath);
  foreach $PathPart (@PathParts) {
    $FullFileName=$PathPart;
  }
  # get the format of the file
  @FileNameParts = split (/\./,$FullFileName);
  foreach $FileNamePart (@FileNameParts) {
    $FileFormat=$FileNamePart;
  }

  # chop off the file extension...
  $FileName=$FullFileName;
  $FileName =~ s/\.$FileFormat$//;

  if ($Command ne "add" && $Command ne "tag") {
    # Might as well grab this here to unless the command isn't going to need it.
    # This pulls the current info on the file out of the db so it will be on hand later.
    # The add and tag commands assume the file isn't in the db yet.
    $ID="N";
    $SafePath=$Path;
    $SafeFileName=$FileName;
    $SafePath =~ s/'/\\'/g;
    $SafeFileName =~ s/'/\\'/g;
    $QueryString="select * from tunes where Path='$SafePath' and FileName='$SafeFileName'";
    $QueryHandle=$DB->prepare($QueryString);
    $QueryHandle->execute;
    while (@Data=$QueryHandle->fetchrow_array) {
      ($ID,$Path,$FileName,$Category,$Genre,$Artist,$Title,$Album,$TrackNum,$Length,$DateAdded,$IsOnIPod,$PlayListsList,$MyRip,$Best,$MP3Bitrate,$MP3Size,$MP3md5sum,$FLACBitrate,$FLACSize,$FLACmd5sum,$AC3Bitrate,$AC3Size,$AC3md5sum,$OGGBitrate,$OGGSize,$OGGmd5sum,$AACBitrate,$AACSize,$AACmd5sum)=@Data;
    }
  }
}




if ($Command eq "info") {
  # code for info command
  # sanity check to make sure the file actually existed in the query above.
  if ($ID eq "N") { die "File not found in database!\n"; }

  #print "$White";
  print "ID:		$ID\n";
  print "Path:		$Path\n";
  print "FileName:	$FileName\n";
  print "Category:	$Category\n";
  print "Genre:		$Genre ($Genres{$Genre})\n";
  print "Artist:		$Artist\n";
  print "Title:		$Title\n";
  print "Album:		$Album\n";
  print "Track Number:	$TrackNum\n";
  print "Length:		$Length\n";
  print "Date Added:	$DateAdded\n";
  print "Playlists:	$PlayListsList\n";
  print "My Rip:		$MyRip\n";
  print "iPod:		$IsOnIPod\n";
  print "Best:		$Best\n";
  print "MP3Bitrate:	$MP3Bitrate\n";
  print "MP3Size:	$MP3Size\n";
  print "MP3md5sum:	$MP3md5sum\n";
  print "FLACBitrate:	$FLACBitrate\n";
  print "FLACSize:	$FLACSize\n";
  print "FLACmd5sum:	$FLACmd5sum\n";
  print "AC3Bitrate:	$AC3Bitrate\n";
  print "AC3Size:	$AC3Size\n";
  print "AC3md5sum:	$AC3md5sum\n";
  print "OGGBitrate:	$OGGBitrate\n";
  print "OGGSize:	$OGGSize\n";
  print "OGGmd5sum:	$OGGmd5sum\n";
  print "AACBitrate:	$AACBitrate\n";
  print "AACSize:	$AACSize\n";
  print "AACmd5sum:	$AACmd5sum\n";





} elsif ($Command eq "add") {
  # code for add command.
  $NewFileName=$ARGV[1];
  $Category=$ARGV[2];
  $Genre=$ARGV[3];
  $NewPath=$ARGV[4];
  # quick check for params before we start touching things.
  if ($Category eq "" || $Genre eq "") { die "Incorect number of params for add command.\n"; }
  # make sure that the file exists.  Then get info about it.
  open (FH, "$Path/$FileName.$FileFormat") || die "Can't open file.";
  close(FH);
  # Escape the single quotes in the filename so it will be safe for MySQL
  $EscapedFileName=$FileName;
  $EscapedFileName =~ s/'/\\'/g;
  # chop up the filename into its component parts (with escapes)
  ($EscapedArtist,$EscapedAlbum,$TrackNum,$EscapedTitle)=split (/ - /,$EscapedFileName,4);
  ## quick check to see if there is already a file that is simillar.  Not fatal if so.
  #$QueryString="select FileName from tunes where Artist='$EscapedArtist' and Title like '\%$EscapedTitle%'";
  #$QueryHandle=$DB->prepare($QueryString);
  #$QueryHandle->execute;
  #while (@Data=$QueryHandle->fetchrow_array) {
  #  $FileName=$Data[0];
  #  print "$Yellow";
  #  print "WARNING: $FileName is simillar. $Green\n";
  #}
  # chop up the file name without escapes
  ($Artist,$Album,$TrackNum,$Title)=split (/ - /,$FileName,4);
  # Construct the new directory name where the file will be stored.
  if ($NewPath eq "") {
    $NewPath="/home/asylum/kmk/media/Music/$Category/$Artist/$Album";
  }
  $EscapedPath=$NewPath;
  $EscapedPath =~ s/'/\\'/g;

  # Find out if this song already exists...
  $ExistingID=0;
  $MP3Bitrate=0;
  $AC3Bitrate=0;
  $OGGBitrate=0;
  $AACBitrate=0;
  $FLACBitrate=0;
  $MP3Size=0;
  $FLACSize=0;
  $AC3Size=0;
  $OGGSize=0;
  $AACSize=0;
  $MP3md5sum="NULL";
  $FLACmd5sum="NULL";
  $AC3md5sum="NULL";
  $OGGmd5sum="NULL";
  $AACmd5sum="NULL";
  $QueryString="select ID,Length,MP3Bitrate,MP3Size,MP3md5sum,FLACSize,FLACmd5sum,AC3Size,AC3md5sum,OGGSize,OGGmd5sum,AACSize,AACmd5sum,Path,Genre,Artist,Title,Album,TrackNum from tunes where FileName='$EscapedFileName'";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($ExistingID,$Length,$MP3Bitrate,$MP3Size,$MP3md5sum,$FLACSize,$FLACmd5sum,$AC3Size,$AC3md5sum,$OGGSize,$OGGmd5sum,$AACSize,$AACmd5sum,$NewPath,$Genre,$Artist,$Title,$Album,$TrackNum)=@Data;
  }
  if ($ExistingID == 0) {
    if ($FileFormat ne "mp3") {
      die "ERROR: You must start with an mp3 file.\n";
    }
  } else {
    if ($FileFormat eq "mp3") {
      die "ERROR: You can't just replace the mp3 file.\n";
    }
  }
  if ($MP3md5sum eq "") { $MP3md5sum="NULL"; }
  if ($FLACmd5sum eq "") { $FLACmd5sum="NULL"; }
  if ($AC3md5sum eq "") { $AC3md5sum="NULL"; }
  if ($OGGmd5sum eq "") { $OGGmd5sum="NULL"; }
  if ($AACmd5sum eq "") { $AACmd5sum="NULL"; }

  if ($FileFormat eq "mp3") {
    # use mp3info to grab the bitrate and play time.
    open (MP3INFO, qq[mp3info -F -x -r a "$FileName.mp3" 2>&1 |]);
    while (<MP3INFO>) {
      $Line=$_;
      chomp $Line;
      if ($Line =~ /^Audio:/) {
        ($Junk,$MP3Bitrate,$Junk) = split (/ +/,$Line,3);
      }
      if ($Line =~ /^Length/) {
        ($Junk,$LengthTxt) = split (/ +/,$Line,2);
        $Length= "00:" . "$LengthTxt";
      }
    }
    close (MP3INFO);
    # Strip off old id3v1 and id3v2 tags so I can create brand new ones
    system (qq[id3v2 -D "$FileName.mp3"]);
    # Setup perms and ownership
    system (qq[chmod 640 "$FileName.mp3"]);
    # Add the new id3v2 tag to the mp3 file (first time I have actually touched the file)
    $ID3V2Params=qq[-2 -a "$Artist" -A "$Album" -t "$Title" -T $TrackNum -g $Genre];
    system (qq[id3v2 $ID3V2Params "$FileName.mp3"]);
    system (qq[chgrp music "$FileName.mp3"]);
    # Get the new file size and md5sum since we have added an id3v2 tag
    open (FH, "$FileName.mp3") || die "Can't open file.";
    ($MP3Size,$Ctime)=(stat(FH))[7,10];
    close(FH);
    open (TH, qq[md5sum "$FileName.mp3" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($MP3md5sum,$junk)=split (/ /,$MD5SumText,2);
    # Create the new location just in case it isn't there yet.
    system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
    # Actually move the file into its new home.
    system (qq[mv -v "$FileName.mp3" "$NewPath/"]);
  } elsif ($FileFormat eq "flac") {
    open (FH, "$FileName.flac") || die "Can't open file.";
    close (FH);
    # id tag the flac file...
    system (qq[metaflac --remove-tag=ARTIST --remove-tag=ALBUM --remove-tag=TRACKNUMBER --remove-tag=TITLE --remove-tag=GENRE --set-tag="ARTIST=$Artist" --set-tag="ALBUM=$Album" --set-tag="TRACKNUMBER=$TrackNum" --set-tag="TITLE=$Title" --set-tag="GENRE=$Genres{$Genre}" "$FileName.flac"]);
    open (FH, "$FileName.flac") || die "Can't open file.";
    ($FLACSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    # Setup perms and ownership
    system (qq[chgrp music "$FileName.flac"]);
    system (qq[chmod 640 "$FileName.flac"]);
    open (TH, qq[md5sum "$FileName.flac" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($FLACmd5sum,$junk)=split (/ /,$MD5SumText,2);
    ($Hours,$Minutes,$Seconds)=split(/:/,$Length,3);
    $Minutes=$Minutes+($Hours*60);
    $Seconds=$Seconds+($Minutes*60);
    $Bits=$FLACSize*8;
    $FLACBitrate=($Bits/$Seconds)/1000;
    system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
    system (qq[mv -v "$FileName.flac" "$NewPath/"]);
  } elsif ($FileFormat eq "ac3") {
    open (FH, "$FileName.ac3") || die "Can't open file.";
    ($AC3Size,$Ctime)=(stat(FH))[7,10];
    close(FH);
    # Setup perms and ownership
    system (qq[chgrp music "$FileName.ac3"]);
    system (qq[chmod 640 "$FileName.ac3"]);
    open (TH, qq[md5sum "$FileName.ac3" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($AC3md5sum,$junk)=split (/ /,$MD5SumText,2);
    system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
    system (qq[mv -v "$FileName.ac3" "$NewPath/"]);
    system (qq[ln "$NewPath/$FileName.ac3" "$NewPath/$FileName.ac3.avi"]);
  } elsif ($FileFormat eq "ogg") {
    open (FH, "$FileName.ogg") || die "Can't open file.";
    close (FH);
    # id tag the flac file...
    system (qq[vorbiscomment -w "$FileName.ogg" -t "ARTIST=$Artist" -t "ALBUM=$Album" -t "TRACKNUMBER=$TrackNum" -t "TITLE=$Title" -t "GENRE=$Genres{$Genre}"]);
    open (FH, "$FileName.ogg") || die "Can't open file.";
    ($OGGSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    # Setup perms and ownership
    system (qq[chgrp music "$FileName.ogg"]);
    system (qq[chmod 640 "$FileName.ogg"]);
    open (TH, qq[md5sum "$FileName.ogg" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($OGGmd5sum,$junk)=split (/ /,$MD5SumText,2);
    system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
    system (qq[mv -v "$FileName.ogg" "$NewPath/"]);
  } elsif ($FileFormat eq "aac") {
    open (FH, "$FileName.aac") || die "Can't open file.";
    close (FH);
    $ID3V2Params=qq[-a "$Artist" -A "$Album" -t "$Title" -T $TrackNum -g $Genre];
    system (qq[mid3v2 $ID3V2Params "$FileName.aac"]);
    open (FH, "$FileName.aac") || die "Can't open file.";
    ($AACSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    # Setup perms and ownership
    system (qq[chgrp music "$FileName.aac"]);
    system (qq[chmod 640 "$FileName.aac"]);
    open (TH, qq[md5sum "$FileName.aac" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($AACmd5sum,$junk)=split (/ /,$MD5SumText,2);
    system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
    system (qq[mv -v "$FileName.aac" "$NewPath/"]);
  }

  # add single quotes to the md5sum vars so they don't have to be quoted in the SQL command.
  if ($MP3md5sum ne "NULL") { $MP3md5sum = sprintf("'%s'", $MP3md5sum); }
  if ($FLACmd5sum ne "NULL") { $FLACmd5sum = sprintf("'%s'", $FLACmd5sum); }
  if ($AC3md5sum ne "NULL") { $AC3md5sum = sprintf("'%s'", $AC3md5sum); }
  if ($OGGmd5sum ne "NULL") { $OGGmd5sum = sprintf("'%s'", $OGGmd5sum); }
  if ($AACmd5sum ne "NULL") { $AACmd5sum = sprintf("'%s'", $AACmd5sum); }

  if ($ExistingID == 0) {
    if ($FileFormat eq "mp3") {
      $InsertString="insert into tunes values (null,'$EscapedPath','$EscapedFileName','$Category','$Genre','$EscapedArtist','$EscapedTitle','$EscapedAlbum','$TrackNum','$Length',now(),'unknown','','unknown','Y','$MP3Bitrate','$MP3Size',$MP3md5sum,'$FLACBitrate','$FLACSize',$FLACmd5sum,'$AC3Bitrate','$AC3Size',$AC3md5sum,'$OGGBitrate','$OGGSize',$OGGmd5sum,$AACBitrate,$AACSize,$AACmd5sum);";
      $InsertHandle=$DB->prepare($InsertString);
      $InsertHandle->execute;
    } else {
      die "ERROR: You must start with an mp3 file.\n";
    }
  } else {
    $UpdateString="update tunes set FLACSize='$FLACSize', FLACBitrate='$FLACBitrate', FLACmd5sum=$FLACmd5sum, AC3Size='$AC3Size', AC3md5sum=$AC3md5sum, OGGSize='$OGGSize', OGGmd5sum=$OGGmd5sum, AACSize='$AACSize', AACmd5sum=$AACmd5sum where ID=$ExistingID";
    $UpdateHandle=$DB->prepare($UpdateString);
    $UpdateHandle->execute;
  }





} elsif ($Command eq "rename") {
  # rename code here.
  # quick sanity check...
  if ($ID eq "N") { die "File not found in database!\n"; }
  $NewFileName=$ARGV[2];
  $NewFileBaseName=$NewFileName;
  $NewFileBaseName =~ s/.mp3//;
  # Rename the file first
  system (qq[mv -v "$FileName.mp3" "$NewFileName"]);
  if ($FLACSize > 0) { system (qq[mv -v "$FileName.flac" "$NewFileBaseName.flac"]); }
  if ($OGGSize > 0) { system (qq[mv -v "$FileName.ogg" "$NewFileBaseName.ogg"]); }
  if ($AACSize > 0) { system (qq[mv -v "$FileName.aac" "$NewFileBaseName.aac"]); }
  if ($AC3Size > 0) { system (qq[mv -v "$FileName.ac3" "$NewFileBaseName.ac3"]); }
  if ($AC3Size > 0) { system (qq[mv -v "$FileName.ac3.avi" "$NewFileBaseName.ac3.avi"]); }
  # Strip off the id3v2 tag since it is going to be recreated
  system (qq[id3v2 -D "$NewFileName"]);
  # chop up the new file name
  ($Artist,$Album,$TrackNum,$Title)=split (/ - /,$NewFileName,4);
  $Title =~ s/\.mp3//;
  # re-create the id3v2 tag
  system (qq[id3v2 -2 -a "$Artist" -A "$Album" -t "$Title" -T $TrackNum -g $Genre "$NewFileName"]);
  system (qq[chgrp music "$NewFileName"]);
  # make things safe for MySQL
  $EscapedNewFileName=$NewFileName;
  $EscapedNewFileName =~ s/'/\\'/g;
  ($EscapedArtist,$EscapedAlbum,$TrackNum,$EscapedTitle)=split (/ - /,$EscapedNewFileName,4);
  $EscapedNewFileName =~ s/\.mp3//;
  $EscapedTitle =~ s/\.mp3//;
  # update the db entry.
  $UpdateString="update tunes set FileName='$EscapedNewFileName', Artist='$EscapedArtist', Album='$EscapedAlbum', TrackNum='$TrackNum', Title='$EscapedTitle' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;
  # update the md5sum since I have modified the file.
  system (qq[mp3db md5 "$NewFileName"]);





} elsif ($Command eq "genre") {
  # genre changing code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $Genre=$ARGV[2];
  chomp $Genre;
  # change the genre in the id3v2 tag on the file.
  system (qq[id3v2 -2 -g $Genre "$FileName.mp3"]);
  system (qq[chgrp music "$FileName.mp3"]);
  # update the db
  $UpdateString="update tunes set Genre='$Genre' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;
  # update the md5sum since I have touched the file
  system (qq[mp3db md5 "$Path/$FileName.mp3"]);




} elsif ($Command eq "category") {
  # category changing code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $Category=$ARGV[2];
  $Genre=$ARGV[3];
  # First we change the genre by recalling this program since changing categories
  # always includes a genre change
  system (qq[mp3db genre "$FileNameParam" $Genre]);
  # now we recall ourself to mv the file since the category is really just a filesystem location.
  system (qq[mp3db mv "$FileName" "/home/asylum/kmk/media/Music/$Category/$Artist/$Album"]);
  # update the db
  $UpdateString="update tunes set Category='$Category' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "mv") {
  # file moving code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $NewPath=$ARGV[2];
  # get the full path for the new path
  $RealNewPath=`realpath "$NewPath"`;
  chomp $RealNewPath;
  $NewPath=$RealNewPath;
  # create the new path just in case it isn't there yet
  system (qq[mkdir -p "$NewPath" > /dev/null 2>&1]);
    system (qq[chgrp music "$NewPath" > /dev/null 2>&1]);
  # actually move the file
  system (qq[mv -v "$FileName.*" "$NewPath/"]);
  # make things safe for mysql
  $NewPath =~ s/'/\\'/g;
  # update the database
  $UpdateString="update tunes set Path='$NewPath' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "rm") {
  # file deleting code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  print "$Red";
  print "Deleting ID# $ID $Green\n";
  # actually delete the file
  system (qq[rm -v "$FileName.mp3"]);
  # delete the entry from the db
  $DeleteString="delete from tunes where ID=$ID";
  $DeleteHandle=$DB->prepare($DeleteString);
  $DeleteHandle->execute;




} elsif ($Command eq "+pl") {
  # file +pl code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $NewPlayList=$ARGV[2];
  print "Was in: $PlayListsList\n";
  # chop up the old list of play lists into an array
  @PlayLists=split (/ /,$PlayListsList);
  # add the new play list to the array
  push (@PlayLists,$NewPlayList);
  # initialize a new string var
  $NewPlayListsList="";
  foreach $PlayList (@PlayLists) {
    # add each play list in the array to the string var
    $NewPlayListsList="$NewPlayListsList $PlayList";
  }
  # clean up any extra crap in the string var
  $NewPlayListsList =~ s/^ //;
  $NewPlayListsList =~ s/ $//;
  print "Now in: $NewPlayListsList\n";
  # put the new string var into the db instead of the old list
  $UpdateString="update tunes set PlayLists='$NewPlayListsList' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;
  # set +ipod since all playlists get put there anyways
  system (qq[mp3db +ipod "$Path/$FileName.mp3"]);



} elsif ($Command eq "-pl") {
  # file -pl code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $RemovePlayList=$ARGV[2];
  print "Was in: $PlayListsList\n";
  # chop up the old list of play lists into an array
  @PlayLists=split (/ /,$PlayListsList);
  # initialize a new string var
  $NewPlayListsList="";
  if ($RemovePlayList ne "all") {
    foreach $PlayList (@PlayLists) {
      # put each play list into the string var unless it is the one we are ditching
      if ($PlayList ne $RemovePlayList) {
        $NewPlayListsList="$NewPlayListsList $PlayList";
      }
    }
  } else {
    system (qq[mp3db -ipod "$Path/$FileName.mp3"]);
  }
  # clean up any extra crap in the string var
  $NewPlayListsList =~ s/^ //;
  $NewPlayListsList =~ s/ $//;
  print "Now in: $NewPlayListsList\n";
  # put the new string var into the db instead of the old list
  $UpdateString="update tunes set PlayLists='$NewPlayListsList' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "+ipod") {
  # file +ipod code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set IsOnIPod='Y' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "-ipod") {
  # file -ipod code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set IsOnIPod='N' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "+myrip") {
  # file +myrip code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set MyRip='Y' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "-myrip") {
  # file +myrip code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set MyRip='N' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "verify") {
  # file verify code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }

  # check md5sum of mp3 file
  if ($MP3md5sum ne "") {
    print "$White$FileName.mp3:";
    open (FH, "$Path/$FileName.mp3") || die "$Red Can't open file. $White";
    ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    if ($CurrentFileSize != $MP3Size) {
      print "$Red FAILED (wrong size)\n";
    } else {
      open (TH, qq[md5sum "$Path/$FileName.mp3" |]);
      $MD5SumText=<TH>;
      close (TH);
      chomp $MD5SumText;
      ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
      if ($CurrentMD5Sum ne $MP3md5sum) {
        print "$Red FAILED (md5)\n";
      } else {
        print "$Green OK\n";
      }
    }
  }
  # check md5sum of flac file
  if ($FLACmd5sum ne "") {
    print "$White$FileName.flac:";
    open (FH, "$Path/$FileName.flac") || die "$Red Can't open file. $White";
    ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    if ($CurrentFileSize != $FLACSize) {
      print "$Red FAILED (wrong size)\n";
    } else {
      open (TH, qq[md5sum "$Path/$FileName.flac" |]);
      $MD5SumText=<TH>;
      close (TH);
      chomp $MD5SumText;
      ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
      if ($CurrentMD5Sum ne $FLACmd5sum) {
        print "$Red FAILED (md5)\n";
      } else {
        print "$Green OK\n";
      }
    }
  }
  # check md5sum of ac3/a52 file
  if ($AC3md5sum ne "") {
    print "$White$FileName.ac3:";
    open (FH, "$Path/$FileName.ac3") || die "$Red Can't open file. $White";
    ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    if ($CurrentFileSize != $AC3Size) {
      print "$Red FAILED (wrong size)\n";
    } else {
      open (TH, qq[md5sum "$Path/$FileName.ac3" |]);
      $MD5SumText=<TH>;
      close (TH);
      chomp $MD5SumText;
      ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
      if ($CurrentMD5Sum ne $AC3md5sum) {
        print "$Red FAILED (md5)\n";
      } else {
        print "$Green OK\n";
      }
    }
  }
  # check md5sum of ogg file
  if ($OGGmd5sum ne "") {
    print "$White$FileName.ogg:";
    open (FH, "$Path/$FileName.ogg") || die "$Red Can't open file. $White";
    ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    if ($CurrentFileSize != $OGGSize) {
      print "$Red FAILED (wrong size)\n";
    } else {
      open (TH, qq[md5sum "$Path/$FileName.ogg" |]);
      $MD5SumText=<TH>;
      close (TH);
      chomp $MD5SumText;
      ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
      if ($CurrentMD5Sum ne $OGGmd5sum) {
        print "$Red FAILED (md5)\n";
      } else {
        print "$Green OK\n";
      }
    }
  }
  # check md5sum of aac file
  if ($AACmd5sum ne "") {
    print "$White$FileName.aac";
    open (FH, "$Path/$FileName.aac") || die "$Red Can't open file. $White";
    ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
    close(FH);
    if ($CurrentFileSize != $AACSize) {
      print "$Red FAILED (wrong size)\n";
    } else {
      open (TH, qq[md5sum "$Path/$FileName.aac" |]);
      $MD5SumText=<TH>;
      close (TH);
      chomp $MD5SumText;
      ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
      if ($CurrentMD5Sum ne $AACmd5sum) {
        print "$Red FAILED (md5)\n";
      } else {
        print "$Green OK\n";
      }
    }
  }



} elsif ($Command eq "verifydir") {
  # file verifydir code here...
  # there are NO params for this command.
  # get the current dir
  $Dir=`pwd`;
  chomp $Dir;
  $SafeDir=$Dir;
  $SafeDir =~ s/'/\\'/g;
  # Find all files in the current dir and the dirs under it (from the db)
  $QueryString="select Path,FileName,MP3md5sum,MP3Size,FLACmd5sum,FLACSize,AC3md5sum,AC3Size,OGGmd5sum,OGGSize,AACmd5sum,AACSize from tunes where Path like '$SafeDir%' order by Path,FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  $ProblemCount=0;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3md5sum,$MP3Size,$FLACmd5sum,$FLACSize,$AC3md5sum,$AC3Size,$OGGmd5sum,$OGGSize,$AACmd5sum,$AACSize)=@Data;
    $FN="$Path/$FileName";
    $FN =~ s/$Dir\///;

    if ($MP3md5sum ne "") {
      print "$White$FN.mp3:";
      if (! -e "$FN.mp3") {
        print "$Red MISSING!\n";
        $ProblemCount++;
      } else {
        open (FH, "$FN.mp3");
        ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
        close(FH);
        if ($CurrentFileSize != $MP3Size) {
          print "$Red FAILED (wrong size)\n";
          $ProblemCount++;
        } else {
          open (TH, qq[md5sum "$FN.mp3" |]);
          $MD5SumText=<TH>;
          close (TH);
          chomp $MD5SumText;
          ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
          if ($CurrentMD5Sum ne $MP3md5sum) {
            print "$Red FAILED (md5)\n";
            $ProblemCount++;
          } else {
            print "$Green OK\n";
          }
        }
      }
    }
    if ($FLACmd5sum ne "") {
      print "$White$FN.flac:";
      if (! -e "$FN.flac") {
        print "$Red MISSING!\n";
        $ProblemCount++;
      } else {
        open (FH, "$FN.flac");
        ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
        close(FH);
        if ($CurrentFileSize != $FLACSize) {
          print "$Red FAILED (wrong size)\n";
          $ProblemCount++;
        } else {
          open (TH, qq[md5sum "$FN.flac" |]);
          $MD5SumText=<TH>;
          close (TH);
          chomp $MD5SumText;
          ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
          if ($CurrentMD5Sum ne $FLACmd5sum) {
            print "$Red FAILED (md5)\n";
            $ProblemCount++;
          } else {
            print "$Green OK\n";
          }
        }
      }
    }
    if ($AC3md5sum ne "") {
      print "$White$FN.ac3:";
      if (! -e "$FN.ac3") {
        print "$Red MISSING!\n";
        $ProblemCount++;
      } else {
        open (FH, "$FN.ac3");
        ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
        close(FH);
        if ($CurrentFileSize != $AC3Size) {
          print "$Red FAILED (wrong size)\n";
          $ProblemCount++;
        } else {
          open (TH, qq[md5sum "$FN.ac3" |]);
          $MD5SumText=<TH>;
          close (TH);
          chomp $MD5SumText;
          ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
          if ($CurrentMD5Sum ne $AC3md5sum) {
            print "$Red FAILED (md5)\n";
            $ProblemCount++;
          } else {
            print "$Green OK\n";
          }
        }
      }
    }
    if ($OGGmd5sum ne "") {
      print "$White$FN.ogg:";
      if (! -e "$FN.ogg") {
        print "$Red MISSING!\n";
        $ProblemCount++;
      } else {
        open (FH, "$FN.ogg");
        ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
        close(FH);
        if ($CurrentFileSize != $OGGSize) {
          print "$Red FAILED (wrong size)\n";
          $ProblemCount++;
        } else {
          open (TH, qq[md5sum "$FN.ogg" |]);
          $MD5SumText=<TH>;
          close (TH);
          chomp $MD5SumText;
          ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
          if ($CurrentMD5Sum ne $OGGmd5sum) {
            print "$Red FAILED (md5)\n";
            $ProblemCount++;
          } else {
            print "$Green OK\n";
          }
        }
      }
    }
    if ($AACmd5sum ne "") {
      print "$White$FN.aac";
      if (! -e "$FN.aac") {
        print "$Red MISSING!\n";
        $ProblemCount++;
      } else {
        open (FH, "$FN.aac");
        ($CurrentFileSize,$Ctime)=(stat(FH))[7,10];
        close(FH);
        if ($CurrentFileSize != $AACSize) {
          print "$Red FAILED (wrong size)\n";
          $ProblemCount++;
        } else {
          open (TH, qq[md5sum "$FN.aac" |]);
          $MD5SumText=<TH>;
          close (TH);
          chomp $MD5SumText;
          ($CurrentMD5Sum,$junk)=split (/ /,$MD5SumText,2);
          if ($CurrentMD5Sum ne $AACmd5sum) {
            print "$Red FAILED (md5)\n";
            $ProblemCount++;
          } else {
            print "$Green OK\n";
          }
        }
      }
    }
  }
  if ($ProblemCount == 0) {
    print "$Green";
    print "No problems detected.\n";
  } else {
    print "$Yellow";
    print "WARNING: $ProblemCount problems detected!$White\n";
  }




} elsif ($Command eq "md5") {
  # file md5 code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  $MP3md5sum="";
  $FLACmd5sum="";
  $AC3md5sum="";
  $OGGmd5sum="";
  $AACmd5sum="";
  $MP3Size=0;
  $FLACSize=0;
  $AC3Size=0;
  $OGGSize=0;
  $AACSize=0;
  if ( -e "$FileName.mp3") {
    open (FH, "$FileName.mp3") || die "Can't open file.";
    ($MP3Size)=(stat(FH))[7];
    close(FH);
    # grab the current md5sum of the file
    open (TH, qq[md5sum "$FileName.mp3" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($MP3md5sum,$junk)=split (/ /,$MD5SumText,2);
  }
  if ( -e "$FileName.flac") {
    open (FH, "$FileName.flac") || die "Can't open file.";
    ($FLACSize)=(stat(FH))[7];
    close(FH);
    # grab the current md5sum of the file
    open (TH, qq[md5sum "$FileName.flac" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($FLACmd5sum,$junk)=split (/ /,$MD5SumText,2);
  }
  if ( -e "$FileName.ac3") {
    open (FH, "$FileName.ac3") || die "Can't open file.";
    ($AC3Size)=(stat(FH))[7];
    close(FH);
    # grab the current md5sum of the file
    open (TH, qq[md5sum "$FileName.ac3" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($AC3md5sum,$junk)=split (/ /,$MD5SumText,2);
  }
  if ( -e "$FileName.ogg") {
    open (FH, "$FileName.ogg") || die "Can't open file.";
    ($OGGSize)=(stat(FH))[7];
    close(FH);
    # grab the current md5sum of the file
    open (TH, qq[md5sum "$FileName.ogg" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($OGGmd5sum,$junk)=split (/ /,$MD5SumText,2);
  }
  if ( -e "$FileName.aac") {
    open (FH, "$FileName.aac") || die "Can't open file.";
    ($AACSize)=(stat(FH))[7];
    close(FH);
    # grab the current md5sum of the file
    open (TH, qq[md5sum "$FileName.aac" |]);
    $MD5SumText=<TH>;
    close (TH);
    chomp $MD5SumText;
    ($AACmd5sum,$junk)=split (/ /,$MD5SumText,2);
  }
  # update the db
  if ($MP3md5sum ne "") { $MP3md5sum = sprintf("'%s'", $MP3md5sum); } else { $MP3md5sum="NULL"; }
  if ($FLACmd5sum ne "") { $FLACmd5sum = sprintf("'%s'", $FLACmd5sum); } else { $FLACmd5sum="NULL"; }
  if ($AC3md5sum ne "") { $AC3md5sum = sprintf("'%s'", $AC3md5sum); } else { $AC3md5sum="NULL"; }
  if ($OGGmd5sum ne "") { $OGGmd5sum = sprintf("'%s'", $OGGmd5sum); } else { $OGGmd5sum="NULL"; }
  if ($AACmd5sum ne "") { $AACmd5sum = sprintf("'%s'", $AACmd5sum); } else { $AACmd5sum="NULL"; }
  $UpdateString="update tunes set MP3Size='$MP3Size', MP3md5sum=$MP3md5sum, FLACSize='$FLACSize', FLACmd5sum=$FLACmd5sum, AC3Size='$AC3Size', AC3md5sum=$AC3md5sum, OGGSize='$OGGSize', OGGmd5sum=$OGGmd5sum,AACSize='$AACSize', AACmd5sum=$AACmd5sum where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "+best") {
  # file +best code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set Best='Y' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "-best") {
  # file -best code here...
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  # just change the db for now.
  $UpdateString="update tunes set Best='N' where ID=$ID";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "index") {
  print "Rebuilding all playlists from the database...\n";
  # this makes it easier to make URI compliant URLs
  use CGI qw/escape unescape/;
  # open a crapload of files that will become playlists and other things
  # most are to be used with my play script
  open (MD5, ">/home/asylum/kmk/media/Music/all.md5");
  open (ALLFILES, ">/home/asylum/kmk/media/Music/playlists/allfiles.m3u");
  open (ALLMP3, ">/home/asylum/kmk/media/Music/playlists/allmp3.m3u");
  #open (UALLMP3, ">/home/asylum/kmk/media/Music/playlists/mm/allmusic.m3u");
  open (ALLFLAC, ">/home/asylum/kmk/media/Music/playlists/allflac.m3u");
  open (ALLAC3, ">/home/asylum/kmk/media/Music/playlists/allac3.m3u");
  open (ALLOGG, ">/home/asylum/kmk/media/Music/playlists/allogg.m3u");
  open (ALLAAC, ">/home/asylum/kmk/media/Music/playlists/allaac.m3u");
  open (W_ALL, ">/home/asylum/kmk/media/Music/playlists/w_all.m3u");
  open (EIGHTIES, ">/home/asylum/kmk/media/Music/playlists/80s.m3u");
  open (ALTERNATIVE, ">/home/asylum/kmk/media/Music/playlists/alternative.m3u");
  open (CLASSIC, ">/home/asylum/kmk/media/Music/playlists/classic.m3u");
  open (FUNNY, ">/home/asylum/kmk/media/Music/playlists/funny.m3u");
  open (METAL, ">/home/asylum/kmk/media/Music/playlists/metal.m3u");
  open (MOVIES, ">/home/asylum/kmk/media/Music/playlists/movies.m3u");
  open (OLDIES, ">/home/asylum/kmk/media/Music/playlists/oldies.m3u");
  open (ROCK, ">/home/asylum/kmk/media/Music/playlists/rock.m3u");
  open (TECHNO, ">/home/asylum/kmk/media/Music/playlists/techno.m3u");
  open (PL80S, ">/home/asylum/kmk/media/Music/playlists/80.m3u");
  open (IPL80S, ">/home/asylum/kmk/.ipod/playlists/80s");
  open (UPL80S, ">/home/asylum/kmk/media/Music/playlists/mm/80s.m3u");
  open (OPL80S, ">/home/asylum/kmk/media/Music/.opod/Playlists/80s.m3u");
  open (CHICK_METAL, ">/home/asylum/kmk/media/Music/playlists/chick_metal.m3u");
  open (ICHICK_METAL, ">/home/asylum/kmk/.ipod/playlists/Chick Metal");
  open (UCHICK_METAL, ">/home/asylum/kmk/media/Music/playlists/mm/Chick Metal.m3u");
  open (OCHICK_METAL, ">/home/asylum/kmk/media/Music/.opod/Playlists/Chick Metal.m3u");
  open (GAME_MUSIC, ">/home/asylum/kmk/media/Music/playlists/game_music.m3u");
  open (IGAME_MUSIC, ">/home/asylum/kmk/.ipod/playlists/Game Music");
  open (UGAME_MUSIC, ">/home/asylum/kmk/media/Music/playlists/mm/Game Music.m3u");
  #open (OGAME_MUSIC, ">/home/asylum/kmk/media/Music/.opod/Playlists/Game Music.m3u");
  open (GOOD_RIFFS, ">/home/asylum/kmk/media/Music/playlists/good_riffs.m3u");
  open (IGOOD_RIFFS, ">/home/asylum/kmk/.ipod/playlists/Good Riffs");
  open (UGOOD_RIFFS, ">/home/asylum/kmk/media/Music/playlists/mm/Good Riffs.m3u");
  open (OGOOD_RIFFS, ">/home/asylum/kmk/media/Music/.opod/Playlists/Good Riffs.m3u");
  open (ATHEIST, ">/home/asylum/kmk/media/Music/playlists/atheist.m3u");
  open (IATHEIST, ">/home/asylum/kmk/.ipod/playlists/Atheist");
  open (UATHEIST, ">/home/asylum/kmk/media/Music/playlists/mm/Atheist.m3u");
  open (OATHEIST, ">/home/asylum/kmk/media/Music/.opod/Playlists/Atheist.m3u");
  open (GTA, ">/home/asylum/kmk/media/Music/playlists/gta.m3u");
  open (IGTA, ">/home/asylum/kmk/.ipod/playlists/gta");
  open (UGTA, ">/home/asylum/kmk/media/Music/playlists/mm/gta.m3u");
  open (HEAVY_METAL, ">/home/asylum/kmk/media/Music/playlists/heavy_metal.m3u");
  open (IHEAVY_METAL, ">/home/asylum/kmk/.ipod/playlists/Heavy Metal");
  open (UHEAVY_METAL, ">/home/asylum/kmk/media/Music/playlists/mm/Heavy Metal.m3u");
  open (OHEAVY_METAL, ">/home/asylum/kmk/media/Music/.opod/Playlists/Heavy Metal.m3u");
  open (DEATH_METAL, ">/home/asylum/kmk/media/Music/playlists/death_metal.m3u");
  open (IDEATH_METAL, ">/home/asylum/kmk/.ipod/playlists/Death Metal");
  open (UDEATH_METAL, ">/home/asylum/kmk/media/Music/playlists/mm/Death Metal.m3u");
  open (ODEATH_METAL, ">/home/asylum/kmk/media/Music/.opod/Playlists/Death Metal.m3u");
  open (HARDCORE_TECHNO, ">/home/asylum/kmk/media/Music/playlists/hardcore_techno.m3u");
  open (IHARDCORE_TECHNO, ">/home/asylum/kmk/.ipod/playlists/Hardcore Techno");
  open (UHARDCORE_TECHNO, ">/home/asylum/kmk/media/Music/playlists/mm/Hardcore Techno.m3u");
  open (OHARDCORE_TECHNO, ">/home/asylum/kmk/media/Music/.opod/Playlists/Hardcore Techno.m3u");
  open (PATRIOTIC, ">/home/asylum/kmk/media/Music/playlists/patriotic.m3u");
  open (IPATRIOTIC, ">/home/asylum/kmk/.ipod/playlists/Patriotic");
  open (UPATRIOTIC, ">/home/asylum/kmk/media/Music/playlists/mm/Patriotic.m3u");
  open (OPATRIOTIC, ">/home/asylum/kmk/media/Music/.opod/Playlists/Patriotic.m3u");
  open (POWER_METAL, ">/home/asylum/kmk/media/Music/playlists/power_metal.m3u");
  open (IPOWER_METAL, ">/home/asylum/kmk/.ipod/playlists/Power Metal");
  open (UPOWER_METAL, ">/home/asylum/kmk/media/Music/playlists/mm/Power Metal.m3u");
  open (OPOWER_METAL, ">/home/asylum/kmk/media/Music/.opod/Playlists/Power Metal.m3u");
  open (OPENBSD, ">/home/asylum/kmk/media/Music/playlists/openbsd.m3u");
  open (IOPENBSD, ">/home/asylum/kmk/.ipod/playlists/OpenBSD");
  open (UOPENBSD, ">/home/asylum/kmk/media/Music/playlists/mm/OpenBSD.m3u");
  open (OOPENBSD, ">/home/asylum/kmk/media/Music/.opod/Playlists/OpenBSD.m3u");
  open (IE, ">/home/asylum/kmk/media/Music/playlists/ie.m3u");
  open (XMAS, ">/home/asylum/kmk/media/Music/playlists/xmas.m3u");
  open (IXMAS, ">/home/asylum/kmk/.ipod/playlists/Xmas");
  open (UXMAS, ">/home/asylum/kmk/media/Music/playlists/mm/Xmas.m3u");
  #open (OXMAS, ">/home/asylum/kmk/media/Music/.opod/Playlists/Xmas.m3u");
  open (ALLTRACKER, ">/home/asylum/kmk/media/Music/playlists/alltracker.m3u");
  open (TODAY, ">/home/asylum/kmk/media/Music/playlists/today.m3u");
  open (RECENT7, ">/home/asylum/kmk/media/Music/playlists/recent7.m3u");
  open (RECENT30, ">/home/asylum/kmk/media/Music/playlists/recent30.m3u");
  open (RECENT60, ">/home/asylum/kmk/media/Music/playlists/recent60.m3u");
  open (RECENT90, ">/home/asylum/kmk/media/Music/playlists/recent90.m3u");
  open (LONG, ">/home/asylum/kmk/media/Music/playlists/long.m3u");
  open (ITODAY, ">/home/asylum/kmk/.ipod/playlists/today");
  open (IRECENT7, ">/home/asylum/kmk/.ipod/playlists/recent7");
  open (IRECENT30, ">/home/asylum/kmk/.ipod/playlists/recent30");
  open (IRECENT60, ">/home/asylum/kmk/.ipod/playlists/recent60");
  open (IRECENT90, ">/home/asylum/kmk/.ipod/playlists/recent90");
  open (UTODAY, ">/home/asylum/kmk/media/Music/playlists/mm/today.m3u");
  open (URECENT7, ">/home/asylum/kmk/media/Music/playlists/mm/recent7.m3u");
  open (URECENT30, ">/home/asylum/kmk/media/Music/playlists/mm/recent30.m3u");
  open (URECENT60, ">/home/asylum/kmk/media/Music/playlists/mm/recent60.m3u");
  open (URECENT90, ">/home/asylum/kmk/media/Music/playlists/mm/recent90.m3u");
  open (ULONG, ">/home/asylum/kmk/media/Music/playlists/mm/Long.m3u");
  open (OLONG, ">/home/asylum/kmk/media/Music/.opod/Playlists/Long Songs.m3u");
  open (UNLISTED, ">/home/asylum/kmk/media/Music/playlists/unlisted.m3u");
  open (LISTED, ">/home/asylum/kmk/media/Music/playlists/listed.m3u");
  open (ILISTED, ">/home/asylum/kmk/.ipod/playlists/Listed");
  open (ULISTED, ">/home/asylum/kmk/media/Music/playlists/mm/Listed.m3u");
  open (OLISTED, ">/home/asylum/kmk/media/Music/.opod/Playlists/Listed.m3u");
  open (HARDCORE_ROCK, ">/home/asylum/kmk/media/Music/playlists/hardcore_rock.m3u");
  open (IHARDCORE_ROCK, ">/home/asylum/kmk/.ipod/playlists/Hardcore Rock");
  open (UHARDCORE_ROCK, ">/home/asylum/kmk/media/Music/playlists/mm/Hardcore Rock.m3u");
  open (OHARDCORE_ROCK, ">/home/asylum/kmk/media/Music/.opod/Playlists/Hardcore Rock.m3u");
  open (ALTERNATIVE_ROCK, ">/home/asylum/kmk/media/Music/playlists/alternative_rock.m3u");
  open (IALTERNATIVE_ROCK, ">/home/asylum/kmk/.ipod/playlists/Alternative Rock");
  open (UALTERNATIVE_ROCK, ">/home/asylum/kmk/media/Music/playlists/mm/Alternative Rock.m3u");
  open (OALTERNATIVE_ROCK, ">/home/asylum/kmk/media/Music/.opod/Playlists/Alternative Rock.m3u");
  open (IPOD, ">/home/asylum/kmk/media/Music/playlists/iPod.m3u");
  open (UIPOD, ">/home/asylum/kmk/media/Music/playlists/mm/iPod.m3u.m3u");
  open (IPOD_NO, ">/home/asylum/kmk/media/Music/playlists/iPod_no.m3u");
  open (IIPOD_NO, ">/home/asylum/kmk/media/Music/playlists/gtkpod/iPod_no.m3u");
  #open (UIPOD_NO, ">/home/asylum/kmk/media/Music/playlists/mm/iPod_no.m3u");
  open (IPOD_UNKNOWN, ">/home/asylum/kmk/media/Music/playlists/iPod_unknown.m3u");
  open (IIPOD_UNKNOWN, ">/home/asylum/kmk/media/Music/playlists/gtkpod/iPod_unknown.m3u");
  open (UIPOD_UNKNOWN, ">/home/asylum/kmk/media/Music/playlists/mm/iPod_unknown.m3u");

  # suck in all the data from the db
  #$QueryString="select * from tunes order by Artist,Album,TrackNum,Title,FileName";
  $QueryString="select * from tunes order by Path,FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($ID,$Path,$FileName,$Category,$Genre,$Artist,$Title,$Album,$TrackNum,$Length,$DateAdded,$IsOnIPod,$PlayListsList,$MyRip,$Best,$MP3Bitrate,$MP3Size,$MP3md5sum,$FLACBitrate,$FLACSize,$FLACmd5sum,$AC3Bitrate,$AC3Size,$AC3md5sum,$OGGBitrate,$OGGSize,$OGGmd5sum,$AACBitrate,$AACSize,$AACmd5sum)=@Data;
    # Get a real UNIX path for the ipod as it will always be an mp3 file
    $IFN="$Path/$FileName.mp3";
    $UFN="$Path/$FileName.mp3";
    if ($TrackNum < 10) { $TrackNum="0$TrackNum"; }
    $OFN="../Music/$Artist/$Album/$TrackNum - $Title.mp3";
    # Get a real UNIX path for the best available format in order of preference
    if ($MP3Size >= 1) {
      $FN="$Path/$FileName.mp3";
      print (ALLMP3 "$FN\n");
      $RFN=$FN;
      $RFN =~ s/\/home\/asylum\/kmk\/media\/Music\///;
      print (MD5 "$MP3md5sum *$RFN\n");
    }
    if ($AACSize >= 1) {
      $FN="$Path/$FileName.aac";
      #$UFN="$Path/$FileName.aac";
      $OFN =~ s/\.mp3/.aac/;
      print (ALLAAC "$FN\n");
      $RFN=$FN;
      $RFN =~ s/\/home\/asylum\/kmk\/media\/Music\///;
      print (MD5 "$AACmd5sum *$RFN\n");
    }
    if ($OGGSize >= 1) {
      $FN="$Path/$FileName.ogg";
      $UFN="$Path/$FileName.ogg";
      $OFN =~ s/\.mp3/.ogg/;
      $OFN =~ s/\.aac/.ogg/;
      print (ALLOGG "$FN\n");
      $RFN=$FN;
      $RFN =~ s/\/home\/asylum\/kmk\/media\/Music\///;
      print (MD5 "$OGGmd5sum *$RFN\n");
    }
    if ($FLACSize >= 1) {
      $FN="$Path/$FileName.flac";
      $OFN =~ s/\.mp3/.flac/;
      $OFN =~ s/\.ogg/.flac/;
      $OFN =~ s/\.aac/.flac/;
      print (ALLFLAC "$FN\n");
      $RFN=$FN;
      $RFN =~ s/\/home\/asylum\/kmk\/media\/Music\///;
      print (MD5 "$FLACmd5sum *$RFN\n");
    }
    if ($AC3Size >= 1) {
      $AC3FN="$Path/$FileName.ac3";
      print (ALLAC3 "$AC3FN\n");
      $RFN=$AC3FN;
      $RFN =~ s/\/home\/asylum\/kmk\/media\/Music\///;
      print (MD5 "$AC3md5sum *$RFN\n");
    }
    #$UFN=$IFN;
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (ALLFILES "$FN\n");
    #print (UALLMP3 "$UFN\n");
    # W_All is a playlist that has windows compliant paths.  Assuming that my tunes
    # are mounted on W: via samba
    $W_All="$FN";
    $W_All =~ s/\/home\/asylum\/kmk\/media\/Music\//W:\\/;
    $W_All =~ s/\//\\/g;
    print (W_ALL "$W_All\n");

    # make the category based playlists
    if ($Category eq "80s") { print (EIGHTIES "$FN\n"); }
    if ($Category eq "alternative") { print (ALTERNATIVE "$FN\n"); }
    if ($Category eq "classic") { print (CLASSIC "$FN\n"); }
    if ($Category eq "funny") { print (FUNNY "$FN\n"); }
    if ($Category eq "metal") { print (METAL "$FN\n"); }
    if ($Category eq "movies_TV_games") { print (MOVIES "$FN\n"); }
    if ($Category eq "oldies") { print (OLDIES "$FN\n"); }
    if ($Category eq "rock") { print (ROCK "$FN\n"); }
    if ($Category eq "techno_and_dance") { print (TECHNO "$FN\n"); }
    # make the actual playlists
    if ($PlayListsList =~ /80s/) {
      print (PL80S "$FN\n");
      print (IPL80S "$IFN\n");
      print (UPL80S "$UFN\n");
      print (OPL80S "$OFN\n");
    }
    if ($PlayListsList =~ /chick_metal/) {
      print (CHICK_METAL "$FN\n");
      print (ICHICK_METAL "$IFN\n");
      print (UCHICK_METAL "$UFN\n");
      print (OCHICK_METAL "$OFN\n");
    }
    if ($PlayListsList =~ /game_music/) {
      print (GAME_MUSIC "$FN\n");
      print (IGAME_MUSIC "$IFN\n");
      print (UGAME_MUSIC "$UFN\n");
      #print (OGAME_MUSIC "$OFN\n");
    }
    if ($PlayListsList =~ /good_riffs/) {
      print (GOOD_RIFFS "$FN\n");
      print (IGOOD_RIFFS "$IFN\n");
      print (UGOOD_RIFFS "$UFN\n");
      print (OGOOD_RIFFS "$OFN\n");
    }
    if ($PlayListsList =~ /atheist/) {
      print (ATHEIST "$FN\n");
      print (IATHEIST "$IFN\n");
      print (UATHEIST "$UFN\n");
      print (OATHEIST "$OFN\n");
    }
    if ($PlayListsList =~ /GTA/) {
      print (GTA "$FN\n");
      print (IGTA "$IFN\n");
      print (UGTA "$UFN\n");
    }
    if ($PlayListsList =~ /heavy_metal/) {
      print (HEAVY_METAL "$FN\n");
      print (IHEAVY_METAL "$IFN\n");
      print (UHEAVY_METAL "$UFN\n");
      print (OHEAVY_METAL "$OFN\n");
    }
    if ($PlayListsList =~ /death_metal/) {
      print (DEATH_METAL "$FN\n");
      print (IDEATH_METAL "$IFN\n");
      print (UDEATH_METAL "$UFN\n");
      print (ODEATH_METAL "$OFN\n");
    }
    if ($PlayListsList =~ /hardcore_techno/) {
      print (HARDCORE_TECHNO "$FN\n");
      print (IHARDCORE_TECHNO "$IFN\n");
      print (UHARDCORE_TECHNO "$UFN\n");
      print (OHARDCORE_TECHNO "$OFN\n");
    }
    if ($PlayListsList =~ /hardcore_rock/) {
      print (HARDCORE_ROCK "$FN\n");
      print (IHARDCORE_ROCK "$IFN\n");
      print (UHARDCORE_ROCK "$UFN\n");
      print (OHARDCORE_ROCK "$OFN\n");
    }
    if ($PlayListsList =~ /alternative_rock/) {
      print (ALTERNATIVE_ROCK "$FN\n");
      print (IALTERNATIVE_ROCK "$IFN\n");
      print (UALTERNATIVE_ROCK "$UFN\n");
      print (OALTERNATIVE_ROCK "$OFN\n");
    }
    if ($PlayListsList =~ /patriotic/) {
      print (PATRIOTIC "$FN\n");
      print (IPATRIOTIC "$IFN\n");
      print (UPATRIOTIC "$UFN\n");
      print (OPATRIOTIC "$OFN\n");
    }
    if ($PlayListsList =~ /power_metal/) {
      print (POWER_METAL "$FN\n");
      print (IPOWER_METAL "$IFN\n");
      print (UPOWER_METAL "$UFN\n");
      print (OPOWER_METAL "$OFN\n");
    }
    if ($PlayListsList =~ /openbsd/) {
      print (OPENBSD "$FN\n");
      print (IOPENBSD "$IFN\n");
      print (UOPENBSD "$UFN\n");
      print (OOPENBSD "$OFN\n");
    }
    if ($PlayListsList =~ /xmas/) {
      print (XMAS "$FN\n");
      print (IXMAS "$IFN\n");
      print (UXMAS "$UFN\n");
      #print (OXMAS "$OFN\n");
    }
    if ($PlayListsList =~ /ie/) {
      print (IE "$FN\n");
    }
    if ($PlayListsList eq "") {
      print (UNLISTED "$FN\n");
    }
    if ($PlayListsList ne "") { 
      if ($PlayListsList ne "xmas") {
        if ($PlayListsList ne "ie") {
          if ($PlayListsList ne "game_music") {
            if ($PlayListsList ne "openbsd") {
              if ($PlayListsList ne "patriotic") {
                print (LISTED "$FN\n");
                print (ILISTED "$IFN\n");
                print (ULISTED "$UFN\n");
                print (OLISTED "$OFN\n");
	      }
	    }
	  }
        }
      }
    }
    # iPod playlists
    if ($IsOnIPod eq "Y") { print (IPOD "$IFN\n"); print (UIPOD "$UFN\n"); }
    if ($IsOnIPod eq "N") { print (IPOD_NO "$FN\n"); }
    if ($IsOnIPod eq "N") { print (IIPOD_NO "$IFN\n"); }
    #if ($IsOnIPod eq "N") { print (UIPOD_NO "$UFN\n"); }
    if ($IsOnIPod eq "unknown") { print (IPOD_UNKNOWN "$FN\n"); }
    if ($IsOnIPod eq "unknown") { print (IIPOD_UNKNOWN "$IFN\n"); }
    if ($IsOnIPod eq "unknown") { print (UIPOD_UNKNOWN "$UFN\n"); }
  }
  # make my playlist of all long songs
  $QueryString="select Path,FileName,IsOnIPod,MP3Size,FLACSize,AC3Size,OGGSize,AACSize,Artist,Album,TrackNum,Title from tunes where Length >= '00:10:00' order by Length DESC, FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$IsOnIPod,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize,$Artist,$Album,$TrackNum,$Title)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($TrackNum < 10) { $TrackNum="0$TrackNum"; }
    #$OFN="../Music/$Artist/$Album/$TrackNum - $Title.mp3";
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (LONG "$FN\n");
    if ($IsOnIPod eq "Y") {
      print (ILONG "$IFN\n");
      print (OLONG "$OFN\n");
      print (ULONG "$UFN\n");
    }
  }
  $QueryString="select FileName from trackerz where Length >= '00:10:00' order by Length DESC, FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FileName=$Data[0];
    $FN="/home/asylum/kmk/media/Music/tracker/$FileName";
    print (LONG "$FN\n");
  }

  # finding recently added mp3 files...
  $QueryString="select Path,FileName,MP3Size,FLACSize,AC3Size,OGGSize,AACSize from tunes where DateAdded >= DATE_SUB(now(), interval 2 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (TODAY "$FN\n");
    print (ITODAY "$IFN\n");
    print (UTODAY "$UFN\n");
  }
  $QueryString="select Path,FileName,MP3Size,FLACSize,AC3Size,OGGSize,AACSize from tunes where DateAdded >= DATE_SUB(now(), interval 7 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    #if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (RECENT7 "$FN\n");
    print (IRECENT7 "$IFN\n");
    print (URECENT7 "$UFN\n");
  }
  $QueryString="select Path,FileName,MP3Size,FLACSize,AC3Size,OGGSize,AACSize from tunes where DateAdded >= DATE_SUB(now(), interval 30 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    #if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (RECENT30 "$FN\n");
    print (IRECENT30 "$IFN\n");
    print (URECENT30 "$UFN\n");
  }
  $QueryString="select Path,FileName,MP3Size,FLACSize,AC3Size,OGGSize,AACSize from tunes where DateAdded >= DATE_SUB(now(), interval 60 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    #if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (RECENT60 "$FN\n");
    print (IRECENT60 "$IFN\n");
    print (URECENT60 "$UFN\n");
  }
  $QueryString="select Path,FileName,MP3Size,FLACSize,AC3Size,OGGSize,AACSize from tunes where DateAdded >= DATE_SUB(now(), interval 90 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    ($Path,$FileName,$MP3Size,$FLACSize,$AC3Size,$OGGSize,$AACSize)=@Data;
    $IFN="$Path/$FileName.mp3";
    $UFN=$IFN;
    if ($MP3Size >= 1) { $FN="$Path/$FileName.mp3"; }
    #if ($AACSize >= 1) { $FN="$Path/$FileName.aac"; $UFN =~ s/\.mp3/.aac/; }
    if ($OGGSize >= 1) { $FN="$Path/$FileName.ogg"; $UFN =~ s/\.mp3/.ogg/; $UFN =~ s/\.aac/.ogg/; }
    if ($FLACSize >= 1) { $FN="$Path/$FileName.flac"; }
    if ($AC3Size >= 1) { $FN="$Path/$FileName.ac3.avi"; }
    $UFN =~ s/\/home\/asylum\/kmk\/media\/Music\//U:\\media\\Music\\/;
    $UFN =~ s/\//\\/g;
    print (RECENT90 "$FN\n");
    print (IRECENT90 "$IFN\n");
    print (URECENT90 "$UFN\n");
  }
  # finding recently added tracker files...
  $QueryString="select FileName from trackerz where DateAdded >= DATE_SUB(now(), interval 2 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="/home/asylum/kmk/media/Music/tracker/$Data[0]";
    print (TODAY "$FN\n");
  }
  $QueryString="select FileName from trackerz where DateAdded >= DATE_SUB(now(), interval 7 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="/home/asylum/kmk/media/Music/tracker/$Data[0]";
    print (RECENT7 "$FN\n");
  }
  $QueryString="select FileName from trackerz where DateAdded >= DATE_SUB(now(), interval 30 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="/home/asylum/kmk/media/Music/tracker/$Data[0]";
    print (RECENT30 "$FN\n");
  }
  $QueryString="select FileName from trackerz where DateAdded >= DATE_SUB(now(), interval 60 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="/home/asylum/kmk/media/Music/tracker/$Data[0]";
    print (RECENT60 "$FN\n");
  }
  $QueryString="select FileName from trackerz where DateAdded >= DATE_SUB(now(), interval 90 day) order by DateAdded,ID";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="/home/asylum/kmk/media/Music/tracker/$Data[0]";
    print (RECENT90 "$FN\n");
  }
  # add in tracker files...
  $QueryString="select FileName from trackerz order by FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FileName=$Data[0];
    $FileName="/home/asylum/kmk/media/Music/tracker/$FileName";
    print (ALLFILES "$FileName\n");
    print (ALLTRACKER "$FileName\n");
    $FileName =~ s/^\/home\/asylum\/kmk\/media\/Music\///;
  }
  # done with all these files.
  close (ALLFILES);
  close (ALLMP3);
  close (ALLFLAC);
  close (ALLAC3);
  close (ALLOGG);
  close (ALLAAC);
  close (W_ALL);
  close (EIGHTIES);
  close (ALTERNATIVE);
  close (CLASSIC);
  close (FUNNY);
  close (METAL);
  close (MOVIES);
  close (OLDIES);
  close (ROCK);
  close (TECHNO);
  close (PL80S);
  close (IPL80S);
  close (OPL80S);
  close (CHICK_METAL);
  close (ICHICK_METAL);
  close (OCHICK_METAL);
  close (GAME_MUSIC);
  close (IGAME_MUSIC);
  #close (OGAME_MUSIC);
  close (GOOD_RIFFS);
  close (IGOOD_RIFFS);
  close (OGOOD_RIFFS);
  close (GTA);
  close (IGTA);
  close (HEAVY_METAL);
  close (IHEAVY_METAL);
  close (OHEAVY_METAL);
  close (DEATH_METAL);
  close (IDEATH_METAL);
  close (ODEATH_METAL);
  close (HARDCORE_TECHNO);
  close (IHARDCORE_TECHNO);
  close (OHARDCORE_TECHNO);
  close (PATRIOTIC);
  close (IPATRIOTIC);
  close (OPATRIOTIC);
  close (POWER_METAL);
  close (IPOWER_METAL);
  close (OPOWER_METAL);
  close (OPENBSD);
  close (IOPENBSD);
  close (OOPENBSD);
  close (IE);
  close (XMAS);
  close (IXMAS);
  #close (OXMAS);
  close (ALLTRACKER);
  close (TODAY);
  close (RECENT7);
  close (RECENT30);
  close (RECENT60);
  close (RECENT90);
  close (LONG);
  close (ITODAY);
  close (IRECENT7);
  close (IRECENT30);
  close (IRECENT60);
  close (IRECENT90);
  close (ILONG);
  close (OLONG);
  close (UNLISTED);
  close (LISTED);
  close (ILISTED);
  close (OLISTED);
  close (HARDCORE_ROCK);
  close (IHARDCORE_ROCK);
  close (OHARDCORE_ROCK);
  close (ALTERNATIVE_ROCK);
  close (IALTERNATIVE_ROCK);
  close (OALTERNATIVE_ROCK);
  close (IPOD);
  close (IPOD_NO);
  close (IIPOD_NO);
  close (IPOD_UNKNOWN);
  close (IIPOD_UNKNOWN);
  close (MD5);

  # make my cute little html index with a different sort order
  open (HTML, ">/home/asylum/kmk/media/Music/allfiles.html");
  # quick header for the html file
  print (HTML "<HTML><HEAD><TITLE>Kevin Korb's mp3 files</TITLE></HEAD><BODY><UL>\n");
  $QueryString="select Path,FileName,MP3Size,FLACSize,OGGSize,AACSize,AC3Size from tunes order by Path,FileName";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $FN="$Data[0]/$Data[1]";
    $MP3Size=$Data[2];
    $FLACSize=$Data[3];
    $OGGSize=$Data[4];
    $AACSize=$Data[5];
    $AC3Size=$Data[6];
    $AllFilesTxt="$FN";
    $AllFilesTxt =~ s/^\/home\/asylum\/kmk\/media\/Music\///;
    # this time we are doing http URI encoding instead of mysql
    $EncodedFileName = escape($AllFilesTxt);
    # escape escapes too much
    $EncodedFileName =~ s/%2F/\//g;
    #print (HTML qq[<LI><A href="$EncodedFileName">$AllFilesTxt</A></LI>\n]);
    print (HTML qq[<LI>$AllFilesTxt]);
    if ($FLACSize != 0) { print (HTML qq{ <A href="$EncodedFileName.flac">[flac]</A>}); }
    if ($AC3Size != 0) { print (HTML qq{ <A href="$EncodedFileName.ac3">[ac3]</A>}); }
    if ($AACSize != 0) { print (HTML qq{ <A href="$EncodedFileName.aac">[aac]</A>}); }
    if ($OGGSize != 0) { print (HTML qq{ <A href="$EncodedFileName.ogg">[ogg]</A>}); }
    if ($MP3Size != 0) { print (HTML qq{ <A href="$EncodedFileName.mp3">[mp3]</A>}); }
    print (HTML "</LI>\n");
  }
  # html footer
  print (HTML "</UL></BODY></HTML>\n");
  close (HTML);

  # Make a directory for use in gaming
  # I am using randomly numbered file names since for some reason nobody thought to include a shuffler in GTA.
  system ("/bin/rm -rf /home/asylum/kmk/media/Music/.gta");
  system ("mkdir /home/asylum/kmk/media/Music/.gta");
  $Loop=1;
  $QueryString="select Path,FileName from tunes where PlayLists like '%GTA%' order by rand()";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $Count=sprintf ("%08d.mp3",$Loop);
    $FullFileName="$Data[0]/$Data[1].mp3";
    system (qq[ln -s "$FullFileName" /home/asylum/kmk/media/Music/.gta/$Count]);
    $Loop++;
  }
  # Make a directory for use in gaming
  # I am using randomly numbered file names since for some reason nobody thought to include a shuffler in GTA.
  system ("/bin/rm -rf /home/asylum/kmk/media/Music/.listed");
  system ("mkdir /home/asylum/kmk/media/Music/.listed");
  $Loop=1;
  $QueryString="select Path,FileName from tunes where PlayLists not like '' and PlayLists not like 'xmas' and PlayLists not like 'ie' and PlayLists not like 'openbsd' order by rand()";
  $QueryHandle=$DB->prepare($QueryString);
  $QueryHandle->execute;
  while (@Data=$QueryHandle->fetchrow_array) {
    $Count=sprintf ("%08d.mp3",$Loop);
    $FullFileName="$Data[0]/$Data[1].mp3";
    system (qq[ln -s "$FullFileName" /home/asylum/kmk/media/Music/.listed/$Count]);
    $Loop++;
  }




} elsif ($Command eq "replace") {
  die ("Replace doesn't work yet.\n");
  #Replacement code here
  $ReplacementFileNameParam=$ARGV[2];
  # quick sanity check
  if ($ID eq "N") { die "File not found in database!\n"; }
  if ($FileType ne "mp3") { die "Replace only works with the mp3 file|\n"; }
  open (FH, "$Path/$FileName.mp3") || die "Can't open old file.";
  close(FH);
  open (FH, "$ReplacementFileNameParam") || die "Can't open new file.";
  close(FH);
  # remove the old file...
  system (qq[mp3db rm "$Path/$FileName.mp3"]);
  # Add the new file with the old file's settings
  system (qq[mp3db add "$ReplacementFileNameParam" $Category $Genre "$Path"]);
  # get the md5sum of the new file to use as a db key...
  open (TH, qq[md5sum "$Path/$ReplacementFileNameParam" |]);
  $MD5SumText=<TH>;
  close (TH);
  chomp $MD5SumText;
  ($MD5Sum,$junk)=split (/ /,$MD5SumText,2);
  # update the other fields in the table to match the old file...
  $UpdateString="update tunes set IsOnIPod='$IsOnIPod', PlayLists='$PlayListsList', MyRip='$MyRip', Best='$Best' where md5sum='$MD5Sum'";
  $UpdateHandle=$DB->prepare($UpdateString);
  $UpdateHandle->execute;




} elsif ($Command eq "relink") {
  print "Clearing out the pics dir...\n";
  # these are just symlinks
  # I symlink all my pics into a common dir so I can hit them with thumbnail viewers
  system ("rm /home/asylum/kmk/media/Music/pics/*.jpg /home/asylum/kmk/media/Music/pics/*.png");
  print "Finding all symlinks...\n";
  open (LINKS, ">/home/asylum/kmk/media/Music/links.txt");
  # hunt down all symlinks
  open (LINKSFOUND, "find /home/asylum/kmk/media/Music -name .gta -prune -o -type l -print |");
  while (<LINKSFOUND>) {
    $LinkFile=$_;
    chomp $LinkFile;
    # find out what they are pointing to
    $LinkText=`file "$LinkFile"`;
    chomp $LinkText;
    # reformat the output a bit
    $LinkText =~ s/: symbolic link to / >>> /g;
    # throw out warnings if broken links are found
    if ($LinkText =~ /broken symbolic link/) { print "$LinkText\n"; }
    # this is just a text file of all my symlinks.  It isn't really used for anything.
    print (LINKS "$LinkText\n");
  }
  close (LINKSFOUND);
  close (LINKS);
  print "Symlinking all pics...\n";
  # finding all pics except the ones in the thumbnail dir
  open (PICLIST, qq[find /home/asylum/kmk/media/Music -path /home/asylum/kmk/media/Music/pics/.xvpics -prune -or -path /home/asylum/kmk/media/Music/new -prune -or -type f -name "*.png" -print -or -type f -name "*.jpg" -print |]);
  while (<PICLIST>) {
    $PicFile=$_;
    chomp $PicFile;
    # making symlinks of all found pics
    system (qq[ln -s "$PicFile" /home/asylum/kmk/media/Music/pics/]);
  }
  close (PICLIST);




} else {
  die "Invalid command specified!";
}