photo-stats

commit 327d96f76d6d3fb66ec9f1b4244b32d79a792109
parent 618d12c99b9adfb0429e5be27163715d20280d0c
Author: René Wagner <rwagner@rw-net.de>
Date:   Sun, 16 Aug 2020 09:31:31 +0200

refactor query - split data loading and output

Diffstat:
M
phosta.pl
 | 
46
++++++++++++++++++++++++++--------------------

1 file changed, 26 insertions(+), 20 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -47,11 +47,11 @@ if ( !-e $opt_D ) { create_db($opt_D) or die 'database could not be created'; }
 writeconfig($configfile);
 
 if ( $opt_p ne '' ) { 
-    populate_db($opt_p);
+    populate();
     exit 0;
 }
 
-query_db($opt_s, $opt_g);
+query();
 
 exit 0;
 
@@ -100,15 +100,13 @@ sub create_db
     my $rv = $dbh->do($stmt);
 }
 
-sub populate_db
+sub populate
 {
-    my ($destination_dir) = @_;
-
-    say "Scanning $destination_dir for images...this may take a while...";
+    say "Scanning $opt_p images...this may take a while...";
    
     my $extensions = ' -ext ' . join(' -ext ', split(/\,/, $opt_E)) .' ';
 
-    my $cmd = "exiftool -fast2 -r -m -f -p '\$filepath##\$make##\$model##\$lensmake##\$lens##\$lensmodel##\$focallength##\$focallengthin35mmformat##\$aperture##\$exposuretime##\$shutterspeed##\$iso##\$flash##\$datetimeoriginal' -d \"%Y-%m-%d %H:%M:%S\" " . $extensions . $destination_dir;
+    my $cmd = "exiftool -fast2 -r -m -f -p '\$filepath##\$make##\$model##\$lensmake##\$lens##\$lensmodel##\$focallength##\$focallengthin35mmformat##\$aperture##\$exposuretime##\$shutterspeed##\$iso##\$flash##\$datetimeoriginal' -d \"%Y-%m-%d %H:%M:%S\" " . $extensions . $opt_p;
     if ( $opt_v ) { say "### Exiftool command: $cmd"; }
     my @lines = `$cmd`;
 
@@ -217,29 +215,39 @@ sub get_sql
     return "SELECT $fieldlist count(file) as count FROM photos $wherelist $grouplist $orderlist";
 }
 
-sub query_db
+sub query
 {
-    my ($selected, $grouping) = @_;
-   
-    my $currentlines=0;
-    my $skippedlines=0;
-
     my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1 }) or die $DBI::errstr;
 
     my $total_count = $dbh->selectrow_array("SELECT count(file) from photos");
     say "Querying database $opt_D with $total_count entries...";
     say '';
    
+    if ($opt_v) { say '### SQL Statement: '. get_sql($opt_s, $opt_g); }
+    my $stmt = $dbh->prepare(get_sql($opt_s, $opt_g));
+    $stmt->execute();
+    
+    my $rows = $stmt->fetchall_arrayref;
+    my $headers = $stmt->{NAME};
+    $dbh->disconnect();
+
+    result_to_stdout($headers, $rows);
+}
+
+sub result_to_stdout
+{
+    my ( $headers, $rows ) = @_;
+
+    my $currentlines=0;
+    my $skippedlines=0;
     my $tb = Text::SimpleTable::AutoWidth->new();
     
-    if ($opt_v) { say '### SQL Statement: '. get_sql($selected, $grouping); }
-    my $stmt = $dbh->prepare(get_sql($selected, $grouping));
-    $stmt->execute();
-    while (my @row = $stmt->fetchrow_array)
+    $tb->captions($headers);
+    foreach (@$rows)
     {
         if (!defined($opt_n) || ($currentlines < $opt_n))
         {
-            $tb->row(@row);
+            $tb->row(@$_);
             $currentlines++;
         }
         else
@@ -248,8 +256,6 @@ sub query_db
         }
     }     
     
-    $tb->captions($stmt->{NAME});
-    $dbh->disconnect();
     if (defined($opt_n)) { say "Showing top $currentlines results, skipping $skippedlines." }
     say $tb->draw; 
 }