Re: [Shotwell] Where are tags for videos stored?

[Noah Beck <noah b beck gmail com>]

On Wed, Jan 1, 2020 at 10:24 AM Andy Smith <andy strugglers net> wrote:
> I have Shotwell store tags for image files inside the files. I am
> aware that Shotwell does not yet support storing tags inside video
> files. Yet it does still store them somewhere, because I am able to
> add tags to video files and they persist.
>
> So, where does Shotwell store the tags for video files?

Yes they are in the sqlite db file.  Here's a perl routine I use to
extract the tags from the sqlite so I can save them elsewhere; it uses
the PhotoTable, VideoTable, and TagTable from the sqlite db.  The
association of a tag to a photo or video is done with the "hexid" in
the below code, which starts with the string "thumb" for photos and
the string "video-" for videos.

Noah

sub get_photos {
  my ($dbh, $verbose) = @_;

  # exposure_time maps to date/time original field (time of picture's exposure)
  my $select_phototable_sql =
    q{SELECT `id`,`filename`,`rating`,`title`,`exposure_time` } .
    q{FROM `PhotoTable`};
  my $phototable = $dbh->selectall_arrayref($select_phototable_sql);
  print "Found ", scalar(@$phototable), " photos in shotwell
PhotoTable\n" if $verbose;
  $select_phototable_sql =~ s/PhotoTable/VideoTable/;
  my $videotable = $dbh->selectall_arrayref($select_phototable_sql);
  print "Found ", scalar(@$videotable), " videos in shotwell
VideoTable\n" if $verbose;
  my %photos_byhexid = (
    map((sprintf('thumb%016x',  $_->[0]), [ $_->[1], $_->[2], $_->[3],
[], $_->[4] ]), @$phototable),
    map((sprintf('video-%016x', $_->[0]), [ $_->[1], $_->[2], $_->[3],
[], $_->[4] ]), @$videotable)
  );

  my $select_tagtable_sql =
    q{SELECT `name`,`photo_id_list` } .
    q{FROM `TagTable`};
  my $tagtable = $dbh->selectall_arrayref($select_tagtable_sql);
  print "Found ", scalar(@$tagtable), " tags in shotwell TagTable,
distributing to PhotoTable\n" if $verbose;
  foreach my $row (@$tagtable) {
    my $name = $row->[0];
    $name =~ s/^\///; # trim leading / (not sure why it is present in database)
    next unless defined($row->[1]); # sometimes a tag is not
associated with any images
    foreach my $hexid (split(/,/, $row->[1])) {
      die("Error: id $hexid found in TagTable was not found in PhotoTable")
        unless exists($photos_byhexid{$hexid});
      push(@{$photos_byhexid{$hexid}[3]}, $name);
    }
  }
  return([sort({ $a->[0] cmp $b->[0] } values(%photos_byhexid))]);
  # returned ref is an array of 5-element arrays:
  # [0]: filename
  # [1]: rating
  # [2]: title (caption)
  # [3]: tag array
  # [4]: date/time
}