Re: Finding mislaid tags

[Jack <ostroffjh sbcglobal net>]

On 2009.08.28 08:24, Frank de Lange wrote:
> Dougie Nisbet wrote:
> > I'm usually pretty good at keeping my tag hierarchy neat and tidy.  
> > However sometimes I forget where I've put one or accidentally get it  
> > in the wrong place. For example, I've lost 'Merseyside'. It's not  
> > under my 'Places' tag so I've probably accidentally buried it in  
> > another tag tree. I can
> >    /Merseyside
> > and find all the photos. I just can't find the tag.
>
> While the interface does not offer a search option you can make use  
> of the fact that f-spot uses an sqlite database. Those can be queried  
> by the sqlite command line tool. Use something like this to get a  
> list of all your tags and their parent tags:
>
> sqlite3 .config/f-spot/photos.db "select label.name,  
> parents.name,label.is_category from tags as label,tags as parents  
> where parents.id=label.category_id union select  
> tags.name,'ROOT',tags.is_category from tags where  
> tags.category_id=0"|sort|less
>
> (one line of course, and this assumes that your f-spot database lives  
> in  ~/.config/f-spot/photos.db, and that your sqlite command line  
> tool goes by the name sqlite3 as it does on Debian and derived  
> distros.)
>
> This produces a list of tags like the following:
> ...
> Autumn|Seasons|1
> Bellini|Paard|1
> Birthday_Hanna|Events|1
> Borreliosis|Diseases|1
> Buildings|Objects|1
> CanonIXUS70-Rob|Sources|1
> CanonPSA460-Frank|Sources|1
> CanonPSA530-Wil|Sources|1
> CanonPSG9-Teodor|Sources|1
> Clarissa|People|1
> Clouds, skies and meteorological phenomenae|Objects|1
> Coco|Paard|1
> Objects|ROOT|1
> Hidden|ROOT|0
> ...
>
> Use grep to find your tag:
[details trimmed]

Below is a very rudimentary perl script that uses the above sql, but  
prints all tags, one per line, each with the full path of tags above  
it.  There is no error checking.  It requires the perl DBI and  
DBD::SQLite modules.  It makes the assumption listed above about the  
location of the database, but it should be obvious where to change it  
if necessary.

#/usr/bin/perl -w
use strict;
use DBI;
my $db="$ENV{'HOME'}/.config/f-spot/photos.db";
my $sql="select label.name, parents.name, label.is_category
  from tags as label, tags as parents
 where parents.id=label.category_id
union select tags.name, 'ROOT', tags.is_category
  from tags where tags.category_id=0";
my %parent;
my $dbh = DBI->connect("dbi:SQLite:dbname=$db","")
    or die "Error connecting to database: $!\n";

my @rows = @{$dbh->selectall_arrayref($sql)};

foreach my $row (@rows) {
    my ($tag, $parent, $iscat) = @$row;
    $parent{$tag} = $parent;
}

foreach my $tag (sort(keys %parent)) {
    print "$tag";
    while ($parent{$tag} ne 'ROOT') {
	$tag = $parent{$tag};
	print " -> $tag";
    }
    print "\n";
}