summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorlshprung <lshprung@yahoo.com>2021-09-13 16:28:16 -0700
committerlshprung <lshprung@yahoo.com>2021-09-13 16:28:16 -0700
commitd654b5c03c576bf8962960df9febf3ad37e29ea2 (patch)
tree05725f26906ff0f2b2d6b703ee280ce38e593ac1
parent232a02548e314b8cb69d31fd98fe2395ca5520cb (diff)
Altered behavior of db_cmd and array_handler
-rwxr-xr-xbuild_db.plx29
-rwxr-xr-xbuild_playlists.plx9
-rw-r--r--shared.pl25
3 files changed, 39 insertions, 24 deletions
diff --git a/build_db.plx b/build_db.plx
index 7d2cf04..141da03 100755
--- a/build_db.plx
+++ b/build_db.plx
@@ -28,29 +28,10 @@ our %options = (
);
my %data; #Hold info from Audio::Scan
+my @db_output; #Hold array containing output from a sql statement
my $statement; #Hold statements for sqlite
-# Handle digging into non-scalar tags
-# @_[0] -> array tag
-sub array_handler {
- my $output = "";
-
- for my $i (@_){
- # If another array, recursively handle
- if (ref($i) eq 'ARRAY'){
- $output = $output . array_handler(@$i);
- }
-
- # If scalar, append to output normally
- elsif (!ref($i)){
- $output = $output . "$i;";
- }
- }
-
- return $output;
-}
-
# Wrapper to handle calls to Audio::Scan->scan(); returns tags hash
# @_[0] -> file to scan
sub audio_scan {
@@ -234,10 +215,12 @@ if (!$options{append}){
}
# If appending, add columns where necessary
+# FIXME add check to avoid duplicate rows with the same PATH
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)){
+ @db_output = array_handler(db_cmd($dbh, $statement));
+ if (!$db_output[0]){
$statement = "ALTER TABLE $table_name ADD COLUMN \"$i\";";
db_cmd($dbh, $statement);
}
@@ -268,7 +251,9 @@ for my $file (@file_list){
# If tag is an array, encode the array into semicolon-separated string
if (ref($data{$i}) eq 'ARRAY'){
- $statement = $statement . array_handler($data{$i});
+ for my $j (array_handler($data{$i})){
+ $statement = $statement . "$j;";
+ }
$statement =~ s/[;]+$//g;
$statement = $statement . "\",";
}
diff --git a/build_playlists.plx b/build_playlists.plx
index dd8ec9d..1534fbe 100755
--- a/build_playlists.plx
+++ b/build_playlists.plx
@@ -85,6 +85,7 @@ for (my $i = 0; $i <= $#ARGV; $i++){
}
}
+# Connect to database file
my $dbh = DBI->connect("DBI:SQLite:dbname=$dbname", "", "", { RaiseError => 1}) or die $DBI::errstr;
# DEBUG
print "Opened database successfully\n";
@@ -94,3 +95,11 @@ $statement = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='$t
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}){
+ my @db_output = array_handler(db_cmd($dbh, $statement_arg, "SQL_STATEMENT returned successfully"));
+ for my $i (@db_output){
+ print "$i\n";
+ }
+}
diff --git a/shared.pl b/shared.pl
index ea2fdfd..16993ab 100644
--- a/shared.pl
+++ b/shared.pl
@@ -1,4 +1,25 @@
-# File to hold shared variables and subroutines
+# File to hold shared subroutines
+
+
+# Handle digging into non-scalar tags and other deep arrays
+# @_[0] -> array tag/deep array
+sub array_handler {
+ my @output;
+
+ for my $i (@_){
+ # If another array, recursively handle
+ if (ref($i) eq 'ARRAY'){
+ push(@output, array_handler(@$i));
+ }
+
+ # If scalar, append to output normally
+ elsif (!ref($i)){
+ push(@output, "$i");
+ }
+ }
+
+ return @output;
+}
# Wrapper to handle sqlite commands, return an array of returned lines from sqlite output
@@ -18,7 +39,7 @@ sub db_cmd {
}
# Build output array
- return($sth->fetchrow_array);
+ return($sth->fetchall_arrayref);
}
1;