From d654b5c03c576bf8962960df9febf3ad37e29ea2 Mon Sep 17 00:00:00 2001
From: lshprung <lshprung@yahoo.com>
Date: Mon, 13 Sep 2021 16:28:16 -0700
Subject: Altered behavior of db_cmd and array_handler

---
 build_db.plx        | 29 +++++++----------------------
 build_playlists.plx |  9 +++++++++
 shared.pl           | 25 +++++++++++++++++++++++--
 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;
-- 
cgit