summaryrefslogtreecommitdiff
path: root/build_db.plx
diff options
context:
space:
mode:
Diffstat (limited to 'build_db.plx')
-rwxr-xr-xbuild_db.plx260
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();