#!/usr/bin/perl						       
#
# Register.pl
# 	Script to troll music available
#	on computer and register it with
#	the mysql database.
#
# IMPORTANT : C
#	For this script to work, you must
#	organize your music in a the following
#	Hierarchical structure. 
#	/BandName/AlbumName/TrackNumber.SongName.mp3	
#	At some point I'm gonna use id3 tags for
#	organization, but not right now. 
#
#	
#	Copyright 2003:
#	Sam Heller - Sheller@Marlboro.edu
#	Do Whatever the hell you want with
# 	this, just make sure you mention who
#	I am...
############################################

############################################
# Use DBD::mysql, create the database, and #
# connect to it.                           #
############################################
use DBD::mysql;
$dbh = DBI->connect("DBI:mysql:music", "MYSQLUSER","MYSQLPASS") or die "Can't Connect to MySQL DB : $!\n";         

###################################################
# Name the Update Files and open them for writing #
###################################################
$BandFile  = "/tmp/Band.Update";
$AlbumFile = "/tmp/Album.Update";
$SongFile  = "/tmp/Song.Update";
open BANDFILE, ">$BandFile" or die "Couldn't open $BandFile for write : $!\n";
open ALBUMFILE, ">$AlbumFile" or die "Couldn't open $AlbumFile for write : $!\n";
open SONGFILE, ">$SongFile" or die "Couldn't open $SongFile for write : $!\n";

#########################################################
# We need to assign unique ID's to all of the elements, #
# so we need to create counters for all of them.        #
#########################################################
$BandCount = 1 ;
$AlbumCount = 1 ;
$SongCount = 1 ;

#########################################
# $RootDir is where the music is stored #
#########################################
$RootDir = "MUSICDIR";
$dir = $RootDir;

##############################################
# Open the Base Directory and get a reading. #
##############################################
opendir DIR, $dir
        or die "Can't open $dir : $!";
@listing = readdir DIR;
splice (@listing, 0, 1);
splice (@listing, 0, 1);

######################
# Get the Band Names #
######################
foreach (@listing) 
	{
	$group = $_;
	$currentdir = "$RootDir$group\/";
	opendir BAND, $currentdir or die "Cant open $currentdir : $!";
	
	print BANDFILE "$_ ~ ";		# Put Band into $BandFile
	print BANDFILE "$BandCount  ";	# Put BandID into $BandFile
	print BANDFILE "\n";		# Seperation Marker

#######################
# Get the Album names #
#######################
	@albumlist = readdir BAND;	
   	splice (@albumlist, 0, 1);
        splice (@albumlist, 0, 1);
	foreach (@albumlist) 	
		{
		print ALBUMFILE "$_ ~ ";	    	# Put Album Name Into ALBUMFILE
		print ALBUMFILE "$group ~ ";		# Put Band Name Into ALBUMFILE
		print ALBUMFILE "$AlbumCount ~ ";    	# Put AlbumID Into ALBUMFILE
		print ALBUMFILE "$BandCount  ";    	# Put BandID Into ALBUMFILE
		print ALBUMFILE "\n";		    	# Seperation Marker

#####################################
# Now get the Individual Song Names #
#####################################

		$album = $_;		
		$currentdir = "$RootDir$group\/$album\/";
		opendir ALBUMDIR, $currentdir;
		@tracklist = readdir ALBUMDIR;
               	splice (@tracklist, 0, 1); 
               	splice (@tracklist, 0, 1);
		$path =  "//$_";
		$Track = "01";
		foreach (@tracklist)
			{
			print SONGFILE "$_ ~ ";			# Put Song Name into $SongFile
			print SONGFILE "$currentdir ~ ";	# Put Song Path Number into $SongFile
			print SONGFILE "$Track ~ ";      	# Put Track Number into $SongFile		
			print SONGFILE "$SongCount ~ "; 	# Put SongID into $SongFile  
			print SONGFILE "$BandCount ~ "; 	# Put BandID into $SongFile			
			print SONGFILE "$AlbumCount";		# Put AlbumID into $SongFile
			print SONGFILE "\n";	 		# Seperation Marker
			$SongCount++;		 		# Autoincrement SongID 
			$Track++;				# AutoIncrement Track Number		
			}
		$AlbumCount++;					# Autoincrement AlbumID
		}
	$BandCount++;						# Autoincrement BandID
	}
close BANDFILE;
close ALBUMFILE;
close SONGFILE;

#########################################
# First we drop all the Existing Tables #
#########################################

$dbh->do("DROP TABLE Band ") or die "Couldn't Drop Band Table : $!\n";
$dbh->do("DROP TABLE Album") or die "Couldn't Drop Album Table : $!\n";
$dbh->do("DROP TABLE Song ") or die "Couldn't Drop Song Table : $!\n"; 

#################################
# Now We Re-Create those Tables #
#################################

$dbh->do("CREATE TABLE Band  	(
				BandName VARCHAR(80), 
				BandID CHAR(5)
				)")
				or die "Couldn't Create Band Table : $!\n";

$dbh->do("CREATE TABLE Album 	(
				AlbumName VARCHAR(80), 
				BandName VARCHAR(80),
				AlbumID CHAR(5), 
				BandID CHAR(5)
				)")
				or die "Couldn't Create Album Table : $!\n";

$dbh->do("CREATE TABLE Song  	(
				SongName VARCHAR(80), 
				SongPath VARCHAR(240),
				TrackNumber CHAR(2),
				TrackID CHAR(8),
				BandID CHAR(5),
				AlbumID CHAR(5)
				)")
				or die "Couldn't Create Song Table : $!\n";


###################################################
# Now We Load The Data From The Update Files.     #
# This could probably be done in a sleeker manner #
# without any external files, but this is easier  #
# for now.					  #
###################################################

$dbh->do("LOAD DATA INFILE \'$BandFile\' INTO TABLE Band FIELDS TERMINATED BY ' ~ ' LINES TERMINATED BY '\n'; ")
	or die "Couldn't Load Data from $BandFile into Table : $!\n";

$dbh->do("LOAD DATA INFILE \'$AlbumFile\' INTO TABLE Album FIELDS TERMINATED BY ' ~ ' LINES TERMINATED BY '\n'")
	or die "Couldn't Load Data From $AlbumFile into Table : $!\n";

$dbh->do("LOAD DATA INFILE \'$SongFile\' INTO TABLE Song FIELDS TERMINATED BY ' ~ ' LINES TERMINATED BY '\n'")
	or die "Couldn't Load DATA From $SongFile into Table : $!\n";

####################################################
# Add Indexing to Allow an Eventual Search Engine. #
####################################################

$dbh->do("ALTER TABLE Band ADD FULLTEXT (BandName)");
$dbh->do("ALTER TABLE Album ADD FULLTEXT (AlbumName)");
$dbh->do("ALTER TABLE Song ADD FULLTEXT (SongName)");

###############################################
# Delete the Update Files, close our          #
# connection to the database, and we're done! #
###############################################

unlink $BandFile;
unlink $AlbumFile;
unlink $Songfile;
$dbh->disconnect;