1 files changed, 260 insertions, 0 deletions
diff --git a/build_db.plx b/build_db.plx
new file mode 100755
index 0000000..40e5363
--- /dev/null
+++ b/build_db.plx
@@ -0,0 +1,260 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use utf8;
+use DBI;
+use Audio::Scan;
+use File::HomeDir;
+
+
+# Keep track of columns that need to be created in the database
+our %columns;
+
+# Variables to be set by user
+our $music_dir = File::HomeDir->my_home . "/Music/";
+our $dbname;
+our $table_name = "LIBRARY";
+our %extensions = (
+	flac => '1',
+	mp3 => '1',
+	ogg => '1'
+);
+
+# Keep track of options that have been set
+our %options = (
+	append => 0,
+	output => 0
+);
+
+my %data;      #Hold info from Audio::Scan
+my $statement; #Hold statements for sqlite
+
+
+# Wrapper to handle calls to Audio::Scan->scan(); returns tags hash
+# 	@_[0] -> file to scan
+sub audio_scan {
+	my $data = Audio::Scan->scan("$_[0]");
+	$data = $data->{tags};
+	return %$data;
+}
+
+# Wrapper to handle sqlite commands
+# 	@_[0]            -> database handle
+# 	@_[1]            -> command/statement
+# 	@_[2] (optional) -> output statement
+sub db_cmd {
+	my $rv = $_[0]->do($_[1]);
+	if ($rv < 0){
+		die $DBI::errstr;
+	}
+
+	if (defined $_[2]){
+		print "$_[2]\n";
+	}
+
+	return $rv;
+}
+
+
+# Scan a directory recursively, return an array of files (optionally, matching a certain file extension or extensions)
+# 	@_[0] -> $music_dir
+# 	@_[1] -> hash of file extensions to scan for
+sub get_files {
+	my @file_list;
+	my @file_split;
+
+	# Remove extra /'s from the end of $_[0]
+	my $dir_path = $_[0];
+	my $extensions = $_[1];
+
+	opendir my $dh, "$dir_path" or die "$!";
+	while (my $file = readdir($dh)) {
+		# Skip . and .. directories
+		if ($file eq "." or $file eq ".."){
+			next;
+		}
+
+		if (-d "$dir_path/$file"){
+			push(@file_list, get_files("$dir_path/$file", %extensions));
+		}
+
+		elsif (-f "$dir_path/$file" and -r "$dir_path/$file"){
+			# Check that the extension matches
+			@file_split = split /\./, "$dir_path/$file";
+			if (defined $extensions{"$file_split[-1]"} and $extensions{"$file_split[-1]"} == 1){
+				push(@file_list, "$dir_path/$file");
+			}
+		}
+	}
+
+	closedir $dh;
+	return @file_list;
+}
+
+# Print a help message
+sub print_help {
+	print
+"Usage:
+  $0 [OPTION]... [DIRECTORY]
+
+Generate a database for audio files in DIRECTORY (by default ~/Music).
+
+Options:
+  -a, --append			append to database file, instead of overwriting it
+  -h, --help			display this help and exit
+  -o, --output FILE		specify output file for database (default is library.db at the root of DIRECTORY)
+  -t, --table-name TABLE	specify table name in database file (default is LIBRARY)
+";
+}
+
+# Test scan for Audio::Scan module
+sub scan_test {
+	my $data = Audio::Scan->scan("/home/louie/Music/Bjork/Debut/01 Human Behaviour.flac");
+	$data = $data->{tags};
+	for (keys %$data){
+		print "$_ -> $data->{$_}\n";
+	}
+}
+
+
+# parse flags and arguments
+for (my $i = 0; $i <= $#ARGV; $i++){
+	if ($ARGV[$i] =~ /-a|--append/){
+		$options{append} = 1;
+	}
+
+	elsif ($ARGV[$i] =~ /-h|--help/){
+		print_help();
+		exit;
+	}
+
+	elsif ($ARGV[$i] =~ /-o|--output/){
+		$i++;
+		$dbname = "$ARGV[$i]";
+		$options{output} = 1;
+	}
+
+	elsif ($ARGV[$i] =~ /-t|--table-name/){
+		$i++;
+		$table_name = $ARGV[$i];
+	}
+
+	elsif ($ARGV[$i] =~ /^[^-]/){
+		$music_dir = "$ARGV[$i]";
+		last;
+	}
+}
+
+# Remove trailing '/' from $music_dir and handle if $dbname was not set by the user
+$music_dir =~ s/\/+$//g;
+if (!$options{output}){
+	$dbname = $music_dir . "/library.db";
+}
+
+# Test to ensure $music_dir is a valid directory
+if (! -d $music_dir){
+	die "Error: \"$music_dir\" is not a directory\n";
+}
+
+# Get a list of files in $music_dir
+print "Looking through files in $music_dir\n";
+my @file_list = get_files($music_dir, %extensions);
+# DEBUG
+for my $i (sort @file_list){
+	print "$i\n";
+}
+
+# Get tags for each file
+#for my $file (sort @file_list){
+#	$data = Audio::Scan->scan("$file");
+#	$data = $data->{tags};
+#	for my $i (keys %$data){
+#		print "$i -> $data->{$i}\n";
+#	}
+#}
+
+# Append tags to %columns
+for my $file (@file_list){
+	#$data = Audio::Scan->scan("$file");
+	#$data = $data->{tags};
+	%data = audio_scan("$file");
+	for my $i (keys %data){
+		$columns{$i} = '1';
+	}
+}
+# DEBUG
+for my $i (keys %columns){
+	print "$i\n";
+}
+
+# Connect to sqlite database created in the base of $music_dir
+my $dbh = DBI->connect("DBI:SQLite:dbname=$dbname", "", "", { RaiseError => 1}) or die $DBI::errstr;
+print "Opened database successfully\n";
+
+# Overwrite $table_name if it exists (TODO alert user to overwrite)
+# TODO fix rows with non-ascii characters. These rows do not have any metadata filled in
+# If --append flag was passed, skip this step
+if (!$options{append}){
+	$statement = "DROP TABLE if EXISTS $table_name";
+	db_cmd($dbh, $statement, "Overwriting table \"$table_name\"");
+
+	# Create table $table_name in the database with columns from %columns
+	# Need to create additional columns for ID and PATH
+	$statement = "CREATE TABLE $table_name 
+	(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+	PATH TEXT NOT NULL";
+	for my $i (sort(keys %columns)){
+		$statement = $statement . ",
+		\"$i\" TEXT";
+	}
+	$statement = $statement . ");";
+	db_cmd($dbh, $statement, "Created table successfully");
+}
+
+# If appending, add columns where necessary
+else {
+	for my $i (sort(keys %columns)){
+		$statement = "SELECT COUNT(*) AS CNTREC FROM pragma_table_info('$table_name') WHERE name=\"$i\";";
+		if (db_cmd($dbh, $statement) > 0){
+			$statement = "ALTER TABLE $table_name ADD COLUMN \"$i\";";
+			db_cmd($dbh, $statement);
+		}
+	}
+}
+
+# Add each file from @file_list to the table
+$statement = "INSERT INTO $table_name(PATH)
+VALUES";
+for my $file (@file_list){
+	$statement = $statement . "(\"$file\"),";
+}
+$statement =~ s/[,]$/;/g;
+db_cmd($dbh, $statement);
+
+# Set each file's tags in the table
+# TODO handle array tags (such as genre)
+for my $file (@file_list){
+	$statement = "UPDATE $table_name
+	SET ";
+
+	%data = audio_scan("$file");
+
+	# Loop to add all the columns for $statement
+	for my $i (sort(keys %data)){
+		next if $i eq "MCDI"; #FIXME MCDI field creates issues
+		$data{$i} =~ s/\"/\'\'/g;
+		$statement = $statement . "\"$i\" = \"$data{$i}\",";
+	}
+	$statement =~ s/[,]$/\n/g;
+
+	# Specify this insertion is for $file only
+	$statement = $statement . "WHERE PATH = \"$file\";";
+
+	#FIXME MCDI tag is binary. This should be considered and handled in a secondary file
+	db_cmd($dbh, $statement, "Updated tags for $file");
+}
+
+ 
+# Disconnect from sqlite database
+$dbh->disconnect();