1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
|
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::HomeDir;
require "./shared.pl";
# Variables to be set by the user
our $dbname = File::HomeDir->my_home . "/Music/library.db";
our $table_name = "LIBRARY";
our @tags_of_interest = ("PATH"); # Record TAG arguments (PATH will always be of interest)
our $output_pattern; # Record OUTPUT_PATTERN argument
our $statement_arg; # Record SQL_STATEMENT argument
our $separator = ';'; # Symbol set to separate multiple values per file tag (TODO add flags related to this)
# Keep track of options that have been set
our %options = (
quiet => 0,
sql => 0
);
my @db_output; #Hold array containing output from a sql statement
my $statement; #Hold statements for sqlite
# TODO quit with help message if no arguments set
# TODO add support for overwriting playlists
# Write to an m3u file to create a playlist
# @_[0] -> m3u file path
# @_[1] -> m3u file handle
# @_[2] -> array of audio file paths
sub build_m3u {
my $filename = shift;
my $filehandle = shift;
# Create m3u header if the file is new
if ((-s $filename) == 0){
print $filehandle "#EXTM3U\n\n";
}
# TODO add support for EXTINF metadata (track runtime, Display name)
for my $line (@_){
print $filehandle "$line\n";
# DEBUG
if (!$options{quiet}){
print "Added $line\n";
}
}
}
# Print a help message
sub print_help {
print
"Usage:
$0 [OPTION]... TAG [OUTPUT_PATTERN]
$0 [OPTION]... --sql SQL_STATEMENT [OUTPUT]
Generate m3u playlist(s) for audio files in a database (by default ~/Music/library.db).
Playlists can be generated based on columns (TAGs) in the database or based on a SQL statement output.
Multiple tags can be specified. They must be comma-separated.
If tags are specified, an output pattern can also be specified (see Examples).
Options:
-i, --input FILE specify path for database file to use to generate playlists (default is \$HOME/Music/library.db)
-h, --help display this help and exit
-q, --quiet quiet (no output)
--sql SQL_STATEMENT generate a single playlist based on output of some SQL statement
-t, --table-name TABLE specify table name in database file (default is LIBRARY)
Examples:
$0 ALBUM,ALBUMARTIST \"/home/john/Music/playlists/{ALBUMARTIST}-{ALBUM}.m3u\" Generate a playlist for every combination of ALBUM and ALBUMARTIST in the database, with the output file pattern ALBUMARTIST-ALBUM.m3u
$0 --sql \"SELECT PATH FROM LIBRARY WHERE ARTIST='Steely Dan';\" steely_dan.m3u Generate a playlist based on the output of this SQL statement
$0 --sql \"ARTIST='Steely Dan';\" steely_dan.m3u If an incomplete SQL statement is received, the \"SELECT PATH FROM {table_name} WHERE \" part of the SQL statement is assumed to be implied
";
}
# parse flags and arguments
for (my $i = 0; $i <= $#ARGV; $i++){
if ($ARGV[$i] =~ /-i|--input/){
$i++;
$dbname = "$ARGV[$i]";
}
elsif ($ARGV[$i] =~ /-h|--help/){
print_help();
exit;
}
elsif ($ARGV[$i] =~ /-q|--quiet/){
$options{quiet} = 1;
}
elsif ($ARGV[$i] =~ /--sql/){
$i++;
$statement_arg = "$ARGV[$i]";
$options{sql} = 1;
}
elsif ($ARGV[$i] =~ /-t|--table-name/){
$i++;
$table_name = "$ARGV[$i]";
}
elsif ($ARGV[$i] =~ /^[^-]/){
# This arg should contain the list of tags
if (!$options{sql} and scalar(@tags_of_interest) <= 1){
push(@tags_of_interest, split(',', "$ARGV[$i]"));
}
# This arg should contain the output_pattern
else {
$output_pattern = "$ARGV[$i]";
last;
}
}
}
# Quit if dbname does not exist
if (! -r $dbname){
die "Error: database $dbname is not readable or does not exist"
}
# Connect to database file
my $dbh = DBI->connect("DBI:SQLite:dbname=$dbname", "", "", { RaiseError => 1}) or die $DBI::errstr;
# DEBUG
if (!$options{quiet}){
print "Opened database successfully\n";
}
# Check that table exists
$statement = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='$table_name';";
if (!db_cmd($dbh, $statement)){
die "Error: table \"$table_name\" does not exist in $dbname";
}
# If sql mode is turned on, build a playlist based on a query
if ($options{sql}){
# If query does not start with 'SELECT', assume it is implied
if (!($statement_arg =~ /^SELECT/i)){
$statement_arg = "SELECT PATH FROM $table_name WHERE " . $statement_arg;
}
@db_output = flatten_array(db_cmd($dbh, $statement_arg, "SQL_STATEMENT returned successfully"));
# TODO add switch for appending
# TODO alert user to overwrite
open FH, "> $output_pattern" or die $!;
# DEBUG
if (!$options{quiet}){
print "Opened $output_pattern\n";
}
build_m3u("$output_pattern", *FH, @db_output);
close FH;
}
# Go through every entry to build multiple playlists
else {
my %tag_hash; # Track tag values for each file
my @output_files; # Output files, based on output_pattern
my @value_arr; # Hold values after splitting by SEP to append to tag_hash
@db_output = flatten_array(db_cmd($dbh, "SELECT count(*) FROM $table_name;"));
my $row_count = $db_output[0];
# Go through each row by ID
for my $i (1..$row_count){
# Get output for the PATH, plus each tag of interest; store it in tag_hash
$statement = join(',', @tags_of_interest);
@db_output = flatten_array(db_cmd($dbh, "SELECT $statement FROM $table_name WHERE ID=$i;"));
for my $j (0..scalar(@db_output)-1){
$tag_hash{$tags_of_interest[$j]} = $db_output[$j];
if (!($tags_of_interest[$j] eq "PATH")){
# remove illegal filename characters, replace them with underscore
$tag_hash{$tags_of_interest[$j]} =~ s/[\/<>:"\\|?*]/_/g;
# Separate out arrays
@value_arr = split(';', $tag_hash{$tags_of_interest[$j]});
@tag_hash{$tags_of_interest[$j]} = ();
push(@{$tag_hash{$tags_of_interest[$j]}}, @value_arr);
}
}
# TODO determine array of output files (consider making this a separate subroutine)
#$output_file = $output_pattern;
#$output_file =~ s/[{]([^}]*)[}]/$tag_hash{$1}/g;
# TODO Remove duplicates from @output_file
# TODO break up by semicolon (signifying array of tag values)
#for my $j (keys %tag_hash){
# for my $k (split(';', $tag_hash{$j})){
# print("$j -> $k\n");
# }
#}
next;
# Determine output_file
# Open the file for writing
#open FH, ">> $output_file" or die $!;
# DEBUG
if (!$options{quiet}){
#print "Opened $output_file\n";
}
#build_m3u("$output_file", *FH, $tag_hash{PATH});
close FH;
}
}
# Disconnect from sqlite database
$dbh->disconnect();
|
