commit 11fbae0e8848fb6f6b9e7b1dae4d1e07f87901a8
parent 04a37e3c4e0752bccc903f1b598270439e33a53b
Author: René Wagner <rwagner@rw-net.de>
Date: Tue, 25 Aug 2020 21:25:03 +0200
add filtering by value
Diffstat:
| M | README.md | | | 14 | +++++++++++--- |
| M | phosta.pl | | | 57 | +++++++++++++++++++++++++++++++++++++++------------------ |
2 files changed, 50 insertions(+), 21 deletions(-)
diff --git a/README.md b/README.md
@@ -52,6 +52,7 @@ Additionally the following perl modules need to be installed:
- hour
- filtering of data by
- time range of photos shot
+ - values in fields in "CONTAINS" and "NOT CONTAINS" mode
- custom sorting of output by arbitrary fields
- output results in a table view to your terminal
@@ -102,13 +103,17 @@ data gathering:
This option is saved to the user conf
stats querying:
- -g : group by time range, defaults to total (which means no grouping by time range)
+ -g <period> : group by a time period, defaults to total (which means no grouping by period)
allowed values: year, month, week, hour
-s <fields> : specify the information you want to select, defaults to none (just show number of images)
allowed values: maker, model, lensmake, lens, aperture, exposuretime, iso, focallength, focallength35mm
multiple fields should be listed comma-separated
-t <range> : only take images into account which have been taken in the given timerange
<range> must be specified as 'YYYYMMDD-YYYYMMDD', you can omit one value
+ -f <expr> : filter images based on the given expression, multiple expression can be given comma-separated
+ this works as a case-insensitive CONTAINS search, multiple expressions need to match all (AND logic)
+ use "field=value" for CONTAINS and "field!=value" for NOT CONTAINS, value must not include whitespaces
+ allowed fields: file, maker, model, lensmake, lens, aperture, exposuretime, iso, focallength, focallength35mm
-n <number> : limit the resultset to <number> of lines
-o <fields> : sort your output by the given fields (sequence matters!) in descending order
allowed values: any comma separated combination of the values of -t and -s param and 'count'
@@ -119,10 +124,13 @@ examples:
load EXIF data of files with the extensions jpg, jpeg and tiff in folder ~/Pictures to the database located in ~/Documents/stats.db
phosta -s model -g month -t 20190101-20121231 -o month,count
- show number of pictures taken with a specific camera body in 2019 grouped by month, sorted by newest month first
+ show number of pictures taken in with a specific camera body in 2019 grouped by month, sorted by newest month first
phosta -n 10 -s lens -o count
- show top 10 lenses used the most over all pictures taken
+ show top 10 lenses used the most
+
+ phosta -s model -f maker!=ricoh
+ show camera models where the maker field does not contain the term "ricoh"
```
## FAQ
diff --git a/phosta.pl b/phosta.pl
@@ -17,7 +17,7 @@ use File::HomeDir;
$Getopt::Std::STANDARD_HELP_VERSION = 'true';
-my $VERSION = '0.8';
+my $VERSION = '0.9';
my $PROGRAM = 'Photo Stats';
my $configfile = catfile(File::HomeDir->my_home, '.phosta.conf');
@@ -34,10 +34,11 @@ our $opt_v=0;
our $opt_n=undef;
our $opt_o='count';
our $opt_r=0;
+our $opt_f=undef;
getconfig($configfile);
-getopts('vcrp:n:g:s:t:D:E:o:') or die "Invalid parameters provided! See 'phosta --help' for more details.";
+getopts('vcrp:n:g:s:t:D:E:o:f:') or die "Invalid parameters provided! See 'phosta --help' for more details.";
validate() or die "Invalid parameters provided! See 'phosta --help' for more details.";
my $dsn = "DBI:SQLite:dbname=$opt_D";
@@ -83,6 +84,7 @@ sub validate
$opt_E =~ /^([a-z]{2,4}){1,}(\,[a-z]{2,4}){0,}$/ or return 0;
+ !defined($opt_f) || $opt_f =~ /^[\w]{2,}(!){0,1}=[\w]{1,}(,[\w]{2,}(!){0,1}=[\w]{1,}){0,}$/ or return 0;
return 1;
}
@@ -139,7 +141,7 @@ sub populate
$apert = ($apert ne '-') ? sprintf("%.1f", $apert) : $apert;
$fl = ($fl ne '-') ? sprintf("%.1f", $fl) : $fl;
$fl35 = ($fl35 ne '-') ? POSIX::lround($fl35) : $fl35;
- $exposuretime = (looks_like_number($exposuretime) && $exposuretime < 1) ? "1/". POSIX::lround(1/$exposuretime) : $exposuretime;
+ $exposuretime = (looks_like_number($exposuretime) && $exposuretime < 1 && $exposuretime != 0) ? "1/". POSIX::lround(1/$exposuretime) : $exposuretime;
if ($datetimeoriginal eq '0000:00:00 00:00:00') { $datetimeoriginal = '-'; }
my $stmt = "INSERT OR REPLACE INTO photos (file, maker, model, lensmake, lens, focallength, focallength35mm, aperture, exposuretime, iso, flash, datetimeoriginal)
@@ -154,40 +156,40 @@ sub populate
sub get_sql
{
- my ($selected, $grouping, $order, $timerange) = @_;
+ my ($selected, $grouping, $order, $timerange, $filter) = @_;
my $fieldlist = '';
- my $grouplist = '';
+ my @grouparray;
my $orderlist = '';
- my $wherelist = '';
+ my @wherearray;
given ($grouping)
{
when ('month')
{
$fieldlist = "IFNULL(strftime('%Y/%m', datetimeoriginal), '-') as month,";
- $grouplist = "strftime('%Y/%m', datetimeoriginal)";
+ push @grouparray, "strftime('%Y/%m', datetimeoriginal)";
}
when ('week')
{
$fieldlist = "IFNULL(strftime('%Y/%W', datetimeoriginal), '-') as week,";
- $grouplist = "strftime('%Y/%W', datetimeoriginal)";
+ push @grouparray, "strftime('%Y/%W', datetimeoriginal)";
}
when ('year')
{
$fieldlist = "IFNULL(strftime('%Y', datetimeoriginal), '-') as year,";
- $grouplist = "strftime('%Y', datetimeoriginal)";
+ push @grouparray, "strftime('%Y', datetimeoriginal)";
}
when ('hour')
{
$fieldlist = "IFNULL(strftime('%H', datetimeoriginal), '-') as hour,";
- $grouplist = "strftime('%H', datetimeoriginal)";
+ push @grouparray, "strftime('%H', datetimeoriginal)";
}
}
if (defined($selected))
{
$fieldlist = $fieldlist . $selected . ', ';
- $grouplist .= ($grouplist ne '' ? ',' : '') . $selected;
+ push @grouparray, $selected;
}
if (defined($order))
@@ -202,16 +204,28 @@ sub get_sql
my ($from, $to) = split(/\-/, $timerange);
if ($from ne '')
{
- $wherelist = 'datetimeoriginal >= \''. substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) .'\'';
+ push @wherearray, 'datetimeoriginal >= \''. substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) .'\'';
}
if ($to ne '')
{
- $wherelist .= ($wherelist ne '' ? ' AND ' : '') . 'datetimeoriginal <= \''. substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) .'\'';
+ push @wherearray, 'datetimeoriginal <= \''. substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) .'\'';
}
}
- if ($grouplist ne '') { $grouplist = 'GROUP BY '. $grouplist; }
- if ($wherelist ne '') { $wherelist = 'WHERE ' . $wherelist; }
+ if (defined($filter))
+ {
+ my (@filters) = split /\,/, $filter;
+ foreach (@filters)
+ {
+ my ( $sub1, $sub2, $sub3 ) = split /(!){0,1}=/, $_;
+ push @wherearray, $sub1 . ($_ =~ /!/ ? ' NOT ' : '') . " LIKE '%$sub3%'";
+ }
+ }
+
+ my $wherelist = '';
+ my $grouplist = '';
+ if (scalar @grouparray) { $grouplist = 'GROUP BY '. join(', ', @grouparray); }
+ if (scalar @wherearray) { $wherelist = 'WHERE ' . join(' AND ', @wherearray); }
return "SELECT $fieldlist count(file) as count FROM photos $wherelist $grouplist $orderlist";
}
@@ -225,9 +239,9 @@ sub query
say "Querying database $opt_D with $total_count entries...";
say '';
$total_count > 0 or return;
-
- if ($opt_v) { say '### SQL Statement: '. get_sql($opt_s, $opt_g, $opt_o, $opt_t); }
- my $stmt = $dbh->prepare(get_sql($opt_s, $opt_g, $opt_o, $opt_t));
+ my $sql = get_sql($opt_s, $opt_g, $opt_o, $opt_t, $opt_f);
+ if ($opt_v) { say '### SQL Statement: '. $sql; }
+ my $stmt = $dbh->prepare($sql);
$stmt->execute();
my $rows = $stmt->fetchall_arrayref;
@@ -317,6 +331,10 @@ sub main::HELP_MESSAGE
say ' multiple fields should be listed comma-separated';
say ' -t <range> : only take images into account which have been taken in the given timerange';
say ' <range> must be specified as \'YYYYMMDD-YYYYMMDD\', you can omit one value';
+ say ' -f <expr> : filter images based on the given expression, multiple expression can be given comma-separated';
+ say ' this works as a case-insensitive CONTAINS search, multiple expressions need to match all (AND logic)';
+ say ' use "field=value" for CONTAINS and "field!=value" for NOT CONTAINS, value must not include whitespaces';
+ say ' allowed fields: file, maker, model, lensmake, lens, aperture, exposuretime, iso, focallength, focallength35mm';
say ' -n <number> : limit the resultset to <number> of lines';
say ' -o <fields> : sort your output by the given fields (sequence matters!) in descending order';
say ' allowed values: any comma separated combination of the values of -t and -s param and \'count\'';
@@ -331,4 +349,7 @@ sub main::HELP_MESSAGE
say '';
say ' phosta -n 10 -s lens -o count';
say ' show top 10 lenses used the most';
+ say '';
+ say ' phosta -s model -f maker!=ricoh';
+ say ' show camera models where the maker field does not contain the term "ricoh"';
}
