photo-stats

commit 1fe3ae7e36fba77d471eb6e3b523ef1d9e7905c5
parent 09cd1ab4ea7adcb19f620c64bc2ad11484cb72e6
Author: René Wagner <rwagner@rw-net.de>
Date:   Thu, 13 Aug 2020 21:57:23 +0200

implement basic grouping by time range

remove config file usage, its not needed at the moment

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

1 file changed, 64 insertions(+), 24 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -3,6 +3,7 @@
 # https://git.sr.ht/~rwa/photo-stats
 # published under BSD 3-Clause License - https://git.sr.ht/~rwa/photo-stats/tree/master/LICENSE
 
+use v5.32;
 use warnings;
 use strict;
 use feature qw(say);
@@ -12,7 +13,6 @@ use Cwd;
 use DBI;
 use File::Basename;
 use File::Spec::Functions;
-use File::HomeDir;
 use Getopt::Std;
 
 my $driver   = "SQLite"; 
@@ -23,38 +23,38 @@ my $password = "";
 
 $Getopt::Std::STANDARD_HELP_VERSION = 'true';
 
-my $VERSION = '0.2';
+my $VERSION = '0.3';
 my $PROGRAM = 'Photo Stats';
 
-my $configfile = catfile(File::HomeDir->my_home, '.photo-stats.conf');
-
 # read commandline switches
 our $opt_p='';
 our $opt_c=0;
+our $opt_q=0;
+our $opt_s='';
+our $opt_g='';
 
-getconfig($configfile);
-
-getopts('cp:') or die "Invalid parameters provided! See 'phosta.pl --help' for more details.";
-#if ( $opt_s ) { writeconfig($configfile); }
+getopts('cqp:g:s:') or die "Invalid parameters provided! See 'phosta.pl --help' for more details.";
 
 if ( $opt_p ne '' ) { 
-    get_stats($opt_p);
+    populate_db($opt_p, $opt_c);
+}
+if ( $opt_q ) {
+    query_db($opt_s, $opt_g)
 }
 
 exit 0;
 
-sub get_stats
+sub populate_db
 {
-    my ($destination_dir) = @_;
+    my ($destination_dir, $clean) = @_;
 
-    say "Scanning $destination_dir for files...";
+    say "Scanning $destination_dir for images...";
 
     my $cmd = "exiftool -fast2 -r -m -f -p '\$filepath##\$make##\$model##\$lens##\$lensmodel##\$focallength##\$focallengthin35mmformat##\$aperture##\$exposuretime##\$iso##\$flash##\$datetimeoriginal' -d \"%Y-%m-%d %H:%M:%S\" -ext jpg " . $destination_dir;
     my @lines = qx($cmd);
 
-    my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
-  or die $DBI::errstr;
-    if ( $opt_c ) { $dbh->do('DELETE FROM photos'); }
+    my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
+    if ( $clean ) { $dbh->do('DELETE FROM photos'); }
     my $errorcount = 0;
     my $emptycount = 0;
     foreach (@lines)
@@ -77,18 +77,56 @@ sub get_stats
     say sprintf('%5d', $errorcount). ' image files skipped due to errors';
 }
 
-sub getconfig
+sub get_sql
 {
-	my ( $config_filename ) = @_;
-	if ( -f $config_filename ) { do $config_filename; }
+    my ($selected, $grouping) = @_;
+
+    my $grouping_field = '';
+    my $grouping_group = '';
+    given ($grouping) 
+    {
+        when ('week')
+        {
+            $grouping_field = "strftime('%Y/%m', datetimeoriginal) as month,";
+            $grouping_group = "GROUP BY strftime('%Y/%m', datetimeoriginal)";
+        }
+        when ('month')
+        {
+            $grouping_field = "strftime('%Y/%W', datetimeoriginal) as week_of_year,";
+            $grouping_group = "GROUP BY strftime('%Y/%W', datetimeoriginal)";
+        }
+        when ('year')
+        {
+            $grouping_field = "strftime('%Y', datetimeoriginal) as year,";
+            $grouping_group = "GROUP BY strftime('%Y', datetimeoriginal)";
+        }
+        default
+        {
+        }
+    }
+
+    return qq/SELECT $grouping_field count(file) as photo_count FROM photos $grouping_group ORDER BY 1 DESC/;
 }
 
-sub writeconfig
+sub query_db
 {
-	my ( $config_filename ) = @_;
-	open(my $filehandle, '>', $config_filename) or die "Could not open file '$config_filename': $!";
-    #say $filehandle '$opt_f="'. $opt_f .'";';
-	close $filehandle;
+    my ($selected, $grouping) = @_;
+
+    my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
+
+    my $total_count = $dbh->selectrow_array("SELECT count(file) from photos");
+    say "Querying local database with $total_count entries...";
+    say '';
+    
+    my $stmt = $dbh->prepare(get_sql($selected, $grouping));
+    my @row;
+    $stmt->execute();
+    while (@row = $stmt->fetchrow_array)
+    {
+        print join(", ", @row), "\n";
+    }     
+
+    $dbh->disconnect();
 }
 
 sub main::VERSION_MESSAGE()
@@ -109,5 +147,7 @@ sub main::HELP_MESSAGE
 	say 'options:';
     say '   -p <folder> : populate database from the files in the specified folder';
     say '   -c          : clear the database before populating with data from the folder';
-	say '   --help      : show this help';
+	say '   -g          : group by time range: year, month, week of year or total (no time range grouping), defaults to total';
+    say '   -s          : specify the information you want to select: focallength, iso, aperture, exposuretime, maker/model or photos, defaults to photos';
+    say '   --help      : show this help';
 }