#!/usr/bin/perl
#
# Name:    friendly_links.pl
# Version: 1.8
# Date:    2003/11/03
# Author:  Greg Zwart
# Purpose: Queries the DEC database for all ripped tracks and creates
#          "friendly" symbolic links to the "unfriendly" tracks.  This
#          does not manipulate the DEC database in any way.
#
# Disclaimer: This script is relatively non-intrusive, but has not been
# thoroughly tested with all DEC configurations.  Use at your own risk.
#
# Revision History: at end of file
#

BEGIN {
	@INC = (@INC,
		"/opt/odin/perl5/lib/site_perl/i386-linux",
		"/opt/odin/perl5/lib/site_perl");
}

use DBI;
use File::Find;
use File::Path;
use File::Basename;
use Getopt::Std;

# Configuration variables
$db = "/content/db";
$links = "/content/exported-music/mappings";
$music = "/content/music";
$std_playlist_file = "_playlist";
$dec_playlist_prefix = "_playlist_";

# Global variables - shouldn't need to be modified
@suffixList = (".mp3",".rm",".ram",".rpm");
$playlistSuffix = ".m3u";

# Get command line options
getopts('suvtdhb') or exit usage();
exit usage() if $opt_h==1;

# Verify proper configuration
abort("Invalid music directory!") if !-d $music;
abort("Invalid mapping directory!") if !-d $links;
abort("Invalid database directory!") if !-d $db;

# Skip the link creation, if the delete-only option is specified
goto DELETIONS if $opt_d==1;

# Query the database for track info and store in an array. Also, copy basic
# info into a hash for use with playlists.
print "Querying database for track and playlist information.\n" if $opt_v==1;
$dbh = DBI->connect("DBI:XBase:$db") or abort("Unable to connect to database");
$sth_track_ary = $dbh->selectall_arrayref("SELECT id, title, file, cd_trk_num, album_id, artist_id, genre_id FROM LibraryTrack ORDER BY album_id, cd_trk_num",{Columns=>{}});
$i=0;
while($id = $sth_track_ary->[$i]->[0]) {
	$track_hash{$id}{'title'} = $sth_track_ary->[$i]->[1];
	$track_hash{$id}{'file'} = $sth_track_ary->[$i]->[2];
	$track_hash{$id}{'cd_trk_num'} = $sth_track_ary->[$i]->[3];
	$track_hash{$id}{'album_id'} = $sth_track_ary->[$i]->[4];
	$track_hash{$id}{'artist_id'} = $sth_track_ary->[$i]->[5];
	$track_hash{$id}{'genre_id'} = $sth_track_ary->[$i]->[6];
	$i++;
} 

# Query the database for album, artist, and genre info and copy to a hash
$sth_album_ary = $dbh->selectall_arrayref("SELECT id, name FROM Album",{Columns=>{}});
$i=0;
while($sth_album_ary->[$i]->[0]) {
	$album_hash{$sth_album_ary->[$i]->[0]}{'name'} = $sth_album_ary->[$i]->[1];
	 $i++;
} 

$sth_artist_ary = $dbh->selectall_arrayref("SELECT id, name FROM Artist",{Columns=>{}});
$i=0;
while($sth_artist_ary->[$i]->[0]) {
	$artist_hash{$sth_artist_ary->[$i]->[0]}{'name'} = $sth_artist_ary->[$i]->[1];
	$i++;
}

$sth_genre_ary = $dbh->selectall_arrayref("SELECT id, genre FROM Genre",{Columns=>{}});
$i=0;
while($sth_genre_ary->[$i]->[0]) {
	$genre_hash{$sth_genre_ary->[$i]->[0]}{'genre'} = $sth_genre_ary->[$i]->[1];
	$i++;
}

# Query the database for playlist items and store in an array and a hash
$sth_items_ary = $dbh->selectall_arrayref("SELECT id, list_id, track_id, order FROM PlayListItem ORDER BY list_id, order",{Columns=>{}});
$i=0;
while($id = $sth_items_ary->[$i]->[0]) {
	$items_hash{$id}{'list_id'} = $sth_items_ary->[$i]->[1];
	$items_hash{$id}{'track_id'} = $sth_items_ary->[$i]->[2];
	$items_hash{$id}{'order'} = $sth_items_ary->[$i]->[3];
	$i++;
}

# Query the database for playlist info and copy into a hash
$sth_playlist_ary = $dbh->selectall_arrayref("SELECT id, name, count FROM PlayList",{Columns=>{}});
$i=0;
while($sth_playlist_ary->[$i]->[0]) {
	$playlist_hash{$sth_playlist_ary->[$i]->[0]}{'name'} = $sth_playlist_ary->[$i]->[1];
	$i++;
}

# Close the database connection 
$dbh->disconnect();

# Loop through all of the tracks
print "Creating track links.\n" if $opt_v==1;
$i=0;
while($id = $sth_track_ary->[$i]->[0]) {
	# Set track info
	$title = $track_hash{$id}{'title'};
	$file = $track_hash{$id}{'file'};
	$track_num = $track_hash{$id}{'cd_trk_num'};
	$album = $album_hash{$track_hash{$id}{'album_id'}}{'name'};
	$artist = $artist_hash{$track_hash{$id}{'artist_id'}}{'name'};
	$genre = $genre_hash{$track_hash{$id}{'genre_id'}}{'album'};

	# Format the content
	$artist    = clean_string($artist);
	$album     = clean_string($album);
	$genre     = clean_string($genre);
	$title     = clean_string($title);
	$track_num = 0 if $track_num !~ /\d+/;
	$track_num = "0" . $track_num if $track_num < 10;

	# Start the title with the track number, if the sort option is specified
	$title = "$track_num" . "_" . "$title" if $opt_s==1;

	# Determine the filepath info
	$file =~ s/^tracks/$music/;
	$suffix = (fileparse($file,@suffixList))[2];
	$link = "$links/$artist/$album/$title$suffix";
	$local_playlist = "$std_playlist_file$playlistSuffix";
	$local_playlist = "_$artist-$album$playlistSuffix" if $opt_u==1;

	# Call a subroutine that creates the symlink
	create_symlink($link,$file,$local_playlist);

	$i++;
}

# Now, loop through the playlists
print "Creating playlist links.\n" if $opt_v==1;
$i=0;
while($id = $sth_items_ary->[$i]->[0]) {
	# Set track info
	$title = $track_hash{$items_hash{$id}{'track_id'}}{'title'};
	$file = $track_hash{$items_hash{$id}{'track_id'}}{'file'};
	$track_num = $items_hash{$id}{'order'};
	$global_playlist = $playlist_hash{$items_hash{$id}{'list_id'}}{'name'};

	# Format the content
	$global_playlist = clean_string($global_playlist);
	$title = clean_string($title);
	$track_num = 0 if $track_num !~ /\d+/;
	$track_num = "0" . $track_num if $track_num < 10;

	# Start the title with the track number, if the sort option is specified
	$title = "$track_num" . "_" . "$title" if $opt_s==1;

	# Determine the filepath info
	$file =~ s/^tracks/$music/;
	$link = "$links/$dec_playlist_prefix$global_playlist/$title.mp3";
	$local_playlist = "$std_playlist_file$playlistSuffix";
	$local_playlist = "_$global_playlist$playlistSuffix" if $opt_u==1;

	# Call a subroutine that creates the symlink
	create_symlink($link,$file,$local_playlist);

	$i++;
}

DELETIONS:
# Finally, things need to stay in sync, so traverse the directory hierarchy
# and remove any links/artists/albums that are no longer in the database.
# This will be accomplished in a two step process:
#  
# Step 1 - Remove symbolic links.  If the -l operator indicates the file is
# a symbolic link, but the -f indicates it is not a file, this means the 
# file that the link references has been deleted, so delete the link...
# this is a hack, but it works.
print "Deleting orphaned links.\n" if $opt_v==1;
finddepth({wanted=>sub{unlink $_ if -l $_ && !-f $_; }},$links) unless $opt_t==1;

# Step 2 - Remove the album/artist directories if they contain no links
finddepth({wanted=>sub{opendir(DIR,$_);@cnt=readdir(DIR)-2;closedir(DIR);rmdir $_ if -d $_ && $cnt==0;}},$links) unless $opt_t==1;

# sub: creates the symlink
sub create_symlink($$$) {
	(my $src, my $dest, my $playlist) = @_;
	print "called create_symlink($src, $dest, $playlist)\n" if $opt_b==1;

	# Parse the file/path info
	(my $link, my $path, my $suffix) = fileparse($src,@suffixList);

	# Create the artist/album hierarchy, if it doesn't already exist
	if(length($path) > 0 && !-d $path) {
		print "making $path\n" if $opt_b==1;
		mkpath $path unless $opt_t==1;
	}

	# Create the symbolic link. Use of 'eval' is to bypass this command on
	# unsupported systems.
	if(!-f $src) {
		print "linking $src to $dest\n" if $opt_b==1;
		eval{symlink($dest,$src),1} unless $opt_t==1;

		# Append the track name to the playlist.  It was reported that the
		# AudioTron is not compatible with this format... you must end each
		# line of the playlist with "\x0\r\n" instead of just "\n".
		# Unfortunately, doing so breaks Apache::MP3 playlist functionality.
		# If a solution is found that is compatible with both scenarios, it
		# will be implmented, but in the mean time, "standard" compatibility
		# will be with Apache::MP3.
		open LIST,">>$path/$playlist";
		print LIST "./$link$suffix\n";
		close LIST;
	}
}

# sub: translates string into valid Samba/Windows characterset
sub clean_string($) {
	(my $string) = @_;
	$string =~ s/: /--/g;
	$string =~ tr/\"\:\/\\\.\?\xd4\xdf/\'\-\-\-/d;
	$string =~ tr/\xe2\xe4\xe0\xe5\xe9\xea\xeb\xe8\xef\xee\xec\xc4\xc5\xc9\xf4\xf6\xf2\xfc\xfb\xf9\xff\xd6\xdc\xe1\xed\xf3\xfa\xf1\xd1/aaaaeeeeiiiAAEooouuuyOUaiounN/;
	$string =~ s/\xe6/ae/g;
	$string =~ s/\xc6/AE/g;
	return $string;
}

# sub: displays usage information
sub usage() {
print<<_EOF_;
usage: $0 [-svtdh]
    -s  sort tracks by track number
    -u  creates unique playlist names, using the album name
    -v  verbose output.
    -t  test-mode.  Does not create links.
    -d  delete-only.  Only deletes out-dated links.
    -h  displays usage information.
_EOF_
}

# sub: wrapper for 'die'
sub abort($) {
	print "@_\n";
	die;
}



############################################################################
# Revision History
#
#  v1.8 - 2003/11/03
#  - improved language support by reformatting strings (e.g. replacing
#    accented characters) - changes provided by Jonathan S. Ott
#
#  v1.7 - 2003/04/11
#  - fixed bug that caused existing tracks to be repeatedly added to
#    playlists each time the script was executed
#  - removed special characters from playlist (added in v1.6 for AudioTron
#    compatibility) which broke Apache::MP3 compatibility
#
#  v1.6 - 2003/03/20
#  - added support for DEC playlists
#  - modified the verbose output (-v option) to be more user-friendly
#  - moved symlink creation to a subroutine
#  - modified some file parsing, so script now requires File::Basename and
#    File::Path perl modules, both of which are installed on DEC by default
#  - fixed bug where script would incorrectly created .mp3 symlink to Real
#    media files.  This is fixed with the new file parsing, so the correct
#    file extensions are used.
#  - added special characters to playlist files to be compatible with
#    AudioTron
#
#  v1.5 - 2003/03/08
#  - fixed bug when using the -s (sort) option
#
#  v1.4 - 2003/03/02
#  - after gaining a little more experience with DBH, discovered it is much
#    more efficient to query database and pull values into Perl data
#    structures all at once.  For large collections, this may improve the
#    efficiency of friendly_links.pl up to 100x!!!
#
#  v1.3 - 2003/01/15
#  - added -s and -u options to, respectively, allow tracks to be sorted and
#    playlists to be uniquely named
#
#  v1.2 - 2003/01/10
#  - modified sql query so tracks are ordered by album, then track number.
#    results in script building links for a full album at a time.
#
#  v1.1 - 2002/11/04
#  - modified to access database directly (eliminated a lot of module deps)
#  - added support for album playlist file creation
#  - added check for '/' characters in artist/album/track info
#
#  v1.0 - 2002/10/29
#  - initial version
#