summaryrefslogtreecommitdiff
path: root/build_playlists.plx
blob: ac8735d4ad9611f83b253a2fe3042b58f74ac1ee (plain)
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
#!/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

# 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 add support for overwriting playlists
# TODO make it so files are sorted ahead of time (may require functions from sqlite API)
# 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
# TODO support custom table name
# TODO support custom database path
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_file; # Output file, based on output_pattern

	@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];

			# remove illegal filename characters, replace them with underscore
			if (!($tags_of_interest[$j] eq "PATH")){
				$tag_hash{$tags_of_interest[$j]} =~ s/[\/<>:"\\|?*]/_/g;
			}
		}

		## DEBUG TODO remove me
		#for my $i (keys %tag_hash){
		#	for my $j ($tag_hash{$i}){
		#		print "$j\n";
		#	}
		#}
		#die;

		# TODO break up by semicolon (signifying array of tag values)
		# Determine output_file
		$output_file = $output_pattern;
		$output_file =~ s/[{]([^}]*)[}]/$tag_hash{$1}/g;

		# Open the file for writing
		open FH, ">> $output_file" or die $!;
		# DEBUG
		print "Opened $output_file\n";
		build_m3u("$output_file", *FH, $tag_hash{PATH});
		close FH;
	}
}

# Disconnect from sqlite database
$dbh->disconnect();