phosta (18314B)
1 #!/usr/bin/perl 2 # Copyright(c) René Wagner 2019-2023 3 # https://src.clttr.info/rwa/photo-stats 4 # published under BSD 3-Clause License 5 6 use Modern::Perl '2019'; 7 no warnings 'experimental'; 8 use POSIX 'lround'; 9 use Text::SimpleTable::AutoWidth; 10 use Term::ANSIColor; 11 use Scalar::Util 'looks_like_number'; 12 use DBI; 13 use Cwd; 14 use Getopt::Std; 15 use File::Spec::Functions; 16 use File::HomeDir; 17 use Data::Dumper; 18 19 $Getopt::Std::STANDARD_HELP_VERSION = 'true'; 20 21 my $VERSION = '1.3.1'; 22 my $PROGRAM = 'phosta'; 23 24 my $configfile = catfile(File::HomeDir->my_home, '.phosta.conf'); 25 my $aliasfile = catfile(File::HomeDir->my_home, '.phosta.alias'); 26 27 # read commandline switches 28 our $opt_E='jpg,jpeg'; 29 our $opt_D= catfile(File::HomeDir->my_home, '.phosta.db'); 30 our $opt_p=''; 31 our $opt_t=undef; 32 our $opt_c=0; 33 our $opt_s=undef; 34 our $opt_g=undef; 35 our $opt_v=0; 36 our $opt_n=undef; 37 our $opt_o='count'; 38 our $opt_r=0; 39 our $opt_f=undef; 40 our $opt_e=0; 41 our $opt_W=80; 42 our $opt_T='auto'; 43 our $opt_h=undef; 44 45 getconfig(); 46 47 getopts('hvcrp:n:g:s:t:D:E:o:f:eW:T:') or die "Invalid parameters provided! See 'phosta --help' for more details."; 48 validate() or die "Invalid parameters provided! See 'phosta --help' for more details."; 49 my $dsn = "DBI:SQLite:dbname=$opt_D"; 50 51 if ($opt_h) { 52 main::HELP_MESSAGE(); 53 exit 0; 54 } 55 56 if ($opt_c) { unlink $opt_D; } 57 if ( !-e $opt_D ) { create_db($opt_D) or die 'database could not be created'; } 58 59 writeconfig(); 60 61 if ($opt_p ne '') { 62 populate(); 63 exit 0; 64 } 65 66 query(); 67 68 exit 0; 69 70 sub validate 71 { 72 my @group_params = ('year', 'month', 'week', 'dayofmonth', 'dayofweek', 'hour'); 73 if (defined($opt_g)) { 74 foreach (split (/,/, $opt_g)) { 75 my $val = $_; 76 grep /$val/, @group_params or return 0; 77 } 78 } 79 my @select_params = qw(file maker model lensmaker lens aperture exposuretime iso focallength focallength35mm); 80 if (defined($opt_s)) { 81 foreach (split (/,/, $opt_s)) { 82 my $val = $_; 83 grep /$val/, @select_params or return 0; 84 } 85 } 86 if (defined($opt_f)) { 87 foreach (split (/,/, $opt_f)) { 88 my @filter = split(/=/, $_); 89 grep /$filter[0]/, @select_params or return 0; 90 } 91 } 92 93 my @order_params = (@group_params, @select_params, 'count'); 94 if (defined($opt_o)) { 95 foreach (split (/,/, $opt_o)) { 96 my $val = $_; 97 grep /$val/, @order_params or return 0; 98 } 99 } 100 (!defined($opt_n) || (looks_like_number($opt_n) && $opt_n > 0)) or return 0; 101 (looks_like_number($opt_W) && $opt_W >= 50) or return 0; 102 103 !defined($opt_t) || $opt_t =~ /^([0-9]{8}){0,1}\-([0-9]{8}){0,1}$/ or return 0; 104 105 $opt_E =~ /^([a-z]{2,4}){1,}(\,[a-z]{2,4}){0,}$/ or return 0; 106 107 !defined($opt_f) || $opt_f =~ /^(,{0,1}[\w\-]{2,}(!){0,1}=[\w\-\s]{1,}){1,}$/ or return 0; 108 109 my @table_params = qw(on off auto); 110 grep /$opt_T/, @table_params or return 0; 111 return 1; 112 } 113 114 sub getconfig 115 { 116 if ( -f $configfile ) { do $configfile; } 117 } 118 119 sub writeconfig 120 { 121 open(my $filehandle, '>', $configfile) or die "Could not open file '$configfile': $!"; 122 say $filehandle '$opt_D="'. $opt_D .'";'; 123 say $filehandle '$opt_E="'. $opt_E .'";'; 124 if ($opt_W != -1) { say $filehandle '$opt_W="'. $opt_W .'";'; } 125 say $filehandle '$opt_T="'. $opt_T .'";'; 126 close $filehandle; 127 } 128 129 sub get_aliases 130 { 131 my @aliases; 132 if ( -f $aliasfile ) { 133 open(FH, '<', $aliasfile) or die "Could not read file '$aliasfile': $!"; 134 135 while(<FH>) { 136 chomp $_; 137 my @line = split /\t{1,}/, $_; 138 push @aliases, \@line; 139 } 140 141 close(FH); 142 } 143 144 if ( $opt_v ) { 145 say '### Aliases found'; 146 foreach (@aliases) { say join(' -> ', @$_) } 147 } 148 149 return @aliases; 150 } 151 152 sub trim { my $s = shift; $s =~ s/^\s+|\s+$//g; return $s }; 153 154 sub create_db 155 { 156 my ($dbfile) = @_; 157 my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1 }) or die $DBI::errstr; 158 159 my $stmt = 'CREATE TABLE photos (file TEXT PRIMARY KEY, maker TEXT, model TEXT, lensmaker TEXT, lens TEXT, focallength INTEGER, focallength35mm INTEGER, aperture DECIMAL, exposuretime TEXT, iso INTEGER, flash TEXT, datetimeoriginal DATETIME);'; 160 !$opt_v or say "### DB Statement: $stmt"; 161 my $rv = $dbh->do($stmt); 162 $dbh->disconnect(); 163 } 164 165 sub populate 166 { 167 say "Scanning $opt_p images...this may take a while..."; 168 169 my $extensions = ' -ext ' . join(' -ext ', split(/\,/, $opt_E)) .' '; 170 my @aliases = get_aliases(); 171 172 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\""; 173 !$opt_v or say "### Exiftool command: $cmd"; 174 my @lines = `$cmd`; 175 176 my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1, AutoCommit => 0 }) or die $DBI::errstr; 177 my $errorcount = 0; 178 foreach (@lines) { 179 chomp $_; 180 my @line = split(/#/, $_); 181 182 map { s/^\s+|\s+$//g; } @line; 183 my ($file, $maker, $model, $lensmaker, $lens, $lensmodel, $fl, $fl35, $apert, $exposuretime, $ss, $iso, $flash, $datetimeoriginal) = @line; 184 my @forbidden_content = ('Unknown', 'N/A', '', '-'); 185 if (!defined($maker) || grep /$maker/, @forbidden_content) { $maker = '-'}; 186 if (!defined($model) || grep /$model/, @forbidden_content) { $model = '-'}; 187 if (!defined($lensmaker) || grep /$lensmaker/, @forbidden_content) { $lensmaker = '-' }; 188 if (!defined($lens) || grep /$lens/, @forbidden_content) { $lens = $lensmodel }; 189 if (!defined($lens) || grep /$lens/, @forbidden_content) { $lens = '-' }; 190 $apert = ($apert ne '-') ? sprintf("%.1f", $apert) : $apert; 191 $fl = ($fl ne '-') ? sprintf("%.1f", $fl) : $fl; 192 $fl35 = ($fl35 ne '-') ? POSIX::lround($fl35) : $fl35; 193 $exposuretime = (looks_like_number($exposuretime) && $exposuretime < 1 && $exposuretime != 0) ? "1/". POSIX::lround(1/$exposuretime) : $exposuretime; 194 $datetimeoriginal =~ /\d{4}\-\d{2}\-\d{2}\s\d{2}:\d{2}:\d{2}$/ or $datetimeoriginal = '-'; 195 196 foreach (@aliases) 197 { 198 $maker =~ s/@$_[0]/@$_[1]/; 199 $model =~ s/@$_[0]/@$_[1]/; 200 $lensmaker =~ s/@$_[0]/@$_[1]/; 201 $lens =~ s/@$_[0]/@$_[1]/; 202 } 203 204 my $stmt = "INSERT OR REPLACE INTO photos (file, maker, model, lensmaker, lens, focallength, focallength35mm, aperture, exposuretime, iso, flash, datetimeoriginal) 205 VALUES ('$file', '$maker', '$model', '$lensmaker', '$lens', '$fl', '$fl35', '$apert', '$exposuretime', '$iso', '$flash', '$datetimeoriginal')"; 206 my $rv = $dbh->do($stmt) or $errorcount++; 207 } 208 $dbh->commit(); 209 $dbh->disconnect(); 210 say sprintf('%5d', $errorcount). ' image files skipped due to errors'; 211 say "Updated database $opt_D."; 212 } 213 214 sub get_timerange 215 { 216 my ($from, $to) = split(/\-/, $opt_t); 217 218 $from = $from ne '' ? substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) : undef; 219 $to = $to ne '' ? substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) : undef; 220 221 return ($from, $to); 222 } 223 224 sub get_filters 225 { 226 if (defined($opt_f)) { 227 my @returnarray; 228 my (@filters) = split /\,/, $opt_f; 229 foreach (@filters) { 230 my @line = split /(!{0,1}=)/, $_; 231 push @returnarray, \@line; 232 } 233 return @returnarray; 234 } 235 return; 236 } 237 238 sub get_sql 239 { 240 my $fieldlist = ''; 241 my @grouparray; 242 my $orderlist = ''; 243 my @wherearray; 244 if (defined($opt_g)) { 245 foreach (split (/,/, $opt_g)) { 246 for ($_) { 247 if ('month') { 248 $fieldlist = $fieldlist . "IFNULL(strftime('%m', datetimeoriginal), '-') as month,"; 249 push @grouparray, "strftime('%m', datetimeoriginal)"; 250 } 251 elsif ('week') { 252 $fieldlist = $fieldlist . "IFNULL(strftime('%W', datetimeoriginal), '-') as week,"; 253 push @grouparray, "strftime('%W', datetimeoriginal)"; 254 } 255 elsif ('year') { 256 $fieldlist = $fieldlist . "IFNULL(strftime('%Y', datetimeoriginal), '-') as year,"; 257 push @grouparray, "strftime('%Y', datetimeoriginal)"; 258 } 259 elsif ('hour') { 260 $fieldlist = $fieldlist . "IFNULL(strftime('%H', datetimeoriginal), '-') as hour,"; 261 push @grouparray, "strftime('%H', datetimeoriginal)"; 262 } 263 elsif ('dayofmonth') { 264 $fieldlist = $fieldlist . "IFNULL(strftime('%d', datetimeoriginal), '-') as dayofmonth,"; 265 push @grouparray, "strftime('%d', datetimeoriginal)"; 266 } 267 elsif ('dayofweek') { 268 $fieldlist = $fieldlist . "substr('SunMonTueWedThuFriSat-', 1+ 3 * IFNULL(strftime('%w', datetimeoriginal), 7), 3) as dayofweek,"; 269 push @grouparray, "strftime('%w', datetimeoriginal)"; 270 } 271 } 272 } 273 } 274 if (defined($opt_s)) { 275 $fieldlist = $fieldlist . $opt_s. ', '; 276 push @grouparray, $opt_s; 277 } 278 279 if (defined($opt_o)) { 280 my $sorter = $opt_r ? ' ASC' : ' DESC'; 281 my @order = split(/\,/, $opt_o); 282 $orderlist = ' ORDER BY '. join("$sorter, ", @order) .$sorter; 283 } 284 285 if (defined($opt_t)) { 286 my ($from, $to) = get_timerange(); 287 !defined($from) or push @wherearray, "datetimeoriginal >= '$from'"; 288 !defined($to) or push @wherearray, "datetimeoriginal <= '$to'"; 289 } 290 291 292 foreach (get_filters()) { 293 push @wherearray, @{$_}[0] . (@{$_}[1] =~ /!/ ? ' NOT' : '') . " LIKE '%@{$_}[2]%'"; 294 } 295 296 my $wherelist = ''; 297 my $grouplist = ''; 298 if (scalar @grouparray) { $grouplist = 'GROUP BY '. join(', ', @grouparray); } 299 if (scalar @wherearray) { $wherelist = 'WHERE ' . join(' AND ', @wherearray); } 300 301 return "SELECT $fieldlist count(file) as count FROM photos $wherelist $grouplist $orderlist"; 302 } 303 304 sub query 305 { 306 my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1 }) or die $DBI::errstr; 307 308 my $total_count = $dbh->selectrow_array("SELECT count(file) from photos"); 309 310 say "Querying database $opt_D with $total_count entries..."; 311 say ''; 312 $total_count > 0 or return; 313 my $sql = get_sql(); 314 !$opt_v or say '### SQL Statement: '. $sql; 315 my $stmt = $dbh->prepare($sql); 316 $stmt->execute(); 317 318 my $rows = $stmt->fetchall_arrayref; 319 my $headers = $stmt->{NAME}; 320 $dbh->disconnect(); 321 322 push @$headers, 'percent'; 323 my $sum = 0; 324 foreach (@$rows) { 325 $sum += @$_[$#$_]; 326 } 327 foreach (@$rows) { 328 my $perc = $sum > 0 ? sprintf("%.1f", (@$_[$#$_] / $sum)* 100). '%' : ''; 329 push @$_, $perc; 330 } 331 332 my ($limited_rows, $skippedlines) = limit_results($rows); 333 334 print_filterinfo(scalar @$limited_rows, $skippedlines, $sum); 335 if ($opt_T eq 'off' || (@$headers == 3 and $opt_T eq 'auto') ) { 336 result_to_graph($headers, $limited_rows, $sum); 337 } else { 338 result_to_table($headers, $limited_rows, $sum); 339 } 340 } 341 342 sub limit_results 343 { 344 my ( $rows ) = @_; 345 my $skippedlines = 0; 346 my $currentlines = 0; 347 my @limited_rows; 348 349 foreach (@$rows) { 350 if (!defined($opt_n) || ($currentlines < $opt_n)) { 351 if ($opt_e) { 352 my $skip = 1; 353 for (my $i=0; $i < (scalar @$_ - 2); $i++) { 354 @$_[$i] eq '-' or $skip = 0; 355 } 356 if ($skip) { 357 $skippedlines++; 358 next; 359 } 360 } 361 push @limited_rows, $_; 362 $currentlines++; 363 } else { 364 $skippedlines++; 365 } 366 } 367 return (\@limited_rows, $skippedlines); 368 } 369 370 sub print_filterinfo 371 { 372 my ($currentlines, $skippedlines, $sum) = @_; 373 if (defined($opt_f) || defined($opt_t) || defined($opt_g)) { 374 say 'Applied filters:'; 375 if ($opt_t) { 376 my ($from, $to) = get_timerange(); 377 if (defined($from) && defined($to)) { say ' - images taken between '. color('bold'). $from .color('reset') .' and '. color('bold'). $to . color('reset'); } 378 if (defined($from) && !defined($to)) { say ' - images taken since '. color('bold'). $from . color('reset'); } 379 if (defined($to) && !defined($from)) { say ' - images taken till '. color('bold'). $to . color('reset'); } 380 } 381 foreach (get_filters()) { 382 say ' - ' .color('italic') . @{$_}[0] .color('reset') . (@{$_}[1] =~ /!/ ? ' NOT' : '') .' LIKE '.color('bold') .@{$_}[2]. color('reset'); 383 } 384 if ($opt_g) { 385 foreach (split(/\,/, $opt_g)) { 386 say ' - group by '.color('italic'). $_ .color('reset'); 387 } 388 } 389 say ''; 390 } 391 392 say 'A total of '. color('bold'). "$sum images". color('reset') .' matched your criteria.'; 393 if (defined($opt_o) || defined($opt_r)) { 394 say 'Sorted in '.color('bold'). ($opt_r ? 'ascending' : 'descending') .color('reset').' order by '. color('italic') . join(color('reset').', '.color('italic'), split(/\,/, $opt_o)). color('reset').'.'; 395 } 396 397 if (defined($opt_n) || $opt_e) { 398 say 'Showing '. color('bold'). "$currentlines". color('reset'). ' results, skipping '. color('italic') . "$skippedlines results".color('reset') .' due to empty fields.'; 399 } 400 say ''; 401 } 402 403 sub result_to_graph 404 { 405 my ( $headers, $rows, $sum ) = @_; 406 407 my $barwidth = POSIX::lround(($opt_W - 17) * 0.7); 408 my $titlewidth = POSIX::lround(($opt_W - 17) * 0.3); 409 my $chartformat = '%'.$titlewidth.'.'.$titlewidth.'s |%-'.$barwidth.'s| %5s (%s)'; 410 411 my $countcolumn = @$headers - 2; 412 my $percentcolumn = @$headers - 1; 413 414 foreach (@$rows) { 415 # join columns for display before the chart 416 if ( @$headers >= 4) { 417 # TODO: give remaining columns more room if first columns are shorter 418 # TODO: remove trailing whitespace 419 my $titlecolumns = @$headers - 2; 420 my $columnwidth = POSIX::lround($titlewidth / $titlecolumns); 421 my $title = ''; 422 for (my $i = 0; $i < $titlecolumns; $i++) { 423 $title = $title . ((length(@$_[$i]) > $columnwidth) ? substr(@$_[$i], 0, $columnwidth-1).'~ ' : @$_[$i] . ' '); 424 } 425 @$_[0] = $title; 426 } 427 say sprintf($chartformat, @$_[0], "*"x(50/$sum*@$_[$countcolumn]), @$_[$percentcolumn], @$_[$countcolumn]); 428 } 429 } 430 431 sub result_to_table 432 { 433 my ( $headers, $rows, $sum, $skippedlines ) = @_; 434 435 my $tb = Text::SimpleTable::AutoWidth->new('max_width' => $opt_W); 436 437 $tb->captions($headers); 438 foreach (@$rows) { 439 $tb->row(@$_); 440 } 441 442 if (scalar @$rows) { say $tb->draw; } 443 } 444 445 sub main::VERSION_MESSAGE() 446 { 447 say $PROGRAM . ' - version ' . $VERSION; 448 say 'https://git.sr.ht/~rwa/photo-stats'; 449 say 'Copyright(c) 2019-2023 René Wagner - published under BSD 3 - Clause License'; 450 } 451 452 sub main::HELP_MESSAGE 453 { 454 say ''; 455 say 'EXIF data extraction for media tools and stats querying for your terminal.'; 456 say ''; 457 say 'usage: phosta [options]'; 458 say ''; 459 say 'Options with upper-case letters are automatically saved in the user config and can be omitted if no change is required.'; 460 say ''; 461 say 'generic options:'; 462 say ' -D <file> : path and name of the db file to use, defaults to ~/photo_stats.db'; 463 say ' -c : clear the database'; 464 say ' -v : be verbose - print some debug output'; 465 say ' --help : show this help'; 466 say ''; 467 say 'data gathering:'; 468 say ' -p <folder> : populate database with the EXIF data of the media files in the specified folder'; 469 say ' -E <ext> : list of comma separated extensions used for scanning image files, defaults to jpg,jpeg'; 470 say ' only media files which match (case-insensitive!) the given extensions are added to the database'; 471 say ''; 472 say 'stats querying:'; 473 say ' -g <period> : group by a time period, defaults to total (which means no grouping by period)'; 474 say ' allowed values: year, month, week, dayofmonth, dayofweek, hour'; 475 say ' -s <fields> : specify the information you want to select, defaults to none (just show number of images)'; 476 say ' allowed values: file, maker, model, lensmaker, lens, aperture, exposuretime, iso, focallength, focallength35mm'; 477 say ' multiple fields should be listed comma-separated without whitespaces'; 478 say ' -t <range> : only take images into account which have been taken in the given timerange'; 479 say ' <range> must be specified as \'YYYYMMDD-YYYYMMDD\', you can omit the first or the later value'; 480 say ' -f <expr> : filter images based on the given expression, multiple expression can be given comma-separated'; 481 say ' this works as a case-insensitive search, multiple expressions need to match all (AND logic)'; 482 say ' use "field=value" for CONTAINS and "field!=value" for NOT CONTAINS'; 483 say ' allowed fields: file, maker, model, lensmaker, lens, aperture, exposuretime, iso, focallength, focallength35mm'; 484 say ' -n <number> : limit the resultset to <number> of lines'; 485 say ' -o <fields> : sort your output by the given fields (sequence matters!) in descending order'; 486 say ' allowed values: any comma separated combination of the values of -g and -s param and \'count\''; 487 say ' -r : sort in reverse (ascending) order, default is descending order'; 488 say ' -e : skip lines with empty selected or grouping fields'; 489 say ' -W <number> : define max char width of output, default: 80, a minimum of 50 is required for meaningful output'; 490 say ' -1 deletes the current setting'; 491 say ' -T <value> : show results as always as a table (on) or not (off) or auto (chart when single field is selected), default is auto'; 492 say ' allowed values: auto, on, off'; 493 say ''; 494 say 'For further information see the man pages phosta(1), phosta-alias(5) and phosta-examples(7).'; 495 }