Remove tag association with faces

[Simon Frei]

Hi Mick,

We are hijacking an unrelated bug with this, it is probably more
appropriate to continue this discussion here.
I am currently working on a feature to remove face identities from the
database and thus also marking the tag as not a people tag. You can
either wait for that or try to fix your problem in the database.
I never ventured into the database code, but I think the sql line
responsible for the people association of a tag is
    INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);
where the property value is "person". The existence of this property
marks the tag as a people tag.

Cheers,
Simon

On 30/12/16 16:01, Mick Sulley wrote:
> https://bugs.kde.org/show_bug.cgi?id=360155
>
> --- Comment #12 from Mick Sulley <[hidden email]> ---
> I have just gone through all of the non-people tags on the left, none of them
> link to any pictures, so I guess it is a problem with the database.  Do you
> know which table identifies a tag as a face tag?
>

[Mick Sulley]

Hi Simon,

Thanks for the info.  I have done some database stuff in the past, so I
will have a poke around and see what I can do.

Mick


On 30/12/16 15:23, Simon Frei wrote:

> Hi Mick,
>
> We are hijacking an unrelated bug with this, it is probably more
> appropriate to continue this discussion here.
> I am currently working on a feature to remove face identities from the
> database and thus also marking the tag as not a people tag. You can
> either wait for that or try to fix your problem in the database.
> I never ventured into the database code, but I think the sql line
> responsible for the people association of a tag is
>      INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);
> where the property value is "person". The existence of this property
> marks the tag as a people tag.
>
> Cheers,
> Simon
>
> On 30/12/16 16:01, Mick Sulley wrote:
>> https://bugs.kde.org/show_bug.cgi?id=360155
>>
>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>> I have just gone through all of the non-people tags on the left, none of them
>> link to any pictures, so I guess it is a problem with the database.  Do you
>> know which table identifies a tag as a face tag?
>>
>

[Mick Sulley]

OK I have half fixed it.  Here is what I did in case anyone is interested -

Create backup of the database, (just copy digikam4.db)
Open a SQLite Manager in Firefox
Browse the 'tags' table and note the id of the tag with name 'People',
mine was 22
in the 'Execute SQL' tab run

select * from tagproperties
where property = 'person'
and tagid not in
(select t.id from tags t, tagstree tt
where tt.pid = 22
and tt.id = t.id)

This should list all of the tags that are listed under People in the
left tab but are not really people
If that list looks like the ones you want to remove run

delete  from tagproperties
where property = 'person'
and tagid not in
(select t.id from tags t, tagstree tt
where tt.pid = 22
and tt.id = t.id)

I now only see real people tags on the left side, however, when I try to
tag someone I still get non person tags listed in the suggestions.  Does
this mean that there is some other rouge data in my database?

How does DK generate the list of suggestions?

Mick


On 30/12/16 15:33, Mick Sulley wrote:

> Hi Simon,
>
> Thanks for the info.  I have done some database stuff in the past, so
> I will have a poke around and see what I can do.
>
> Mick
>
>
> On 30/12/16 15:23, Simon Frei wrote:
>> Hi Mick,
>>
>> We are hijacking an unrelated bug with this, it is probably more
>> appropriate to continue this discussion here.
>> I am currently working on a feature to remove face identities from the
>> database and thus also marking the tag as not a people tag. You can
>> either wait for that or try to fix your problem in the database.
>> I never ventured into the database code, but I think the sql line
>> responsible for the people association of a tag is
>>      INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);
>> where the property value is "person". The existence of this property
>> marks the tag as a people tag.
>>
>> Cheers,
>> Simon
>>
>> On 30/12/16 16:01, Mick Sulley wrote:
>>> https://bugs.kde.org/show_bug.cgi?id=360155
>>>
>>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>>> I have just gone through all of the non-people tags on the left,
>>> none of them
>>> link to any pictures, so I guess it is a problem with the database.  
>>> Do you
>>> know which table identifies a tag as a face tag?
>>>
>>
>

[Simon Frei]

You also need to remove the persons from the identity table of the face
database. However there is more information in the face database
associated with that identity. I do not know if these will automatically
be cleaned up when digikam is next run or if it will become messy. Just
keep backing up before trying it out.


On 30/12/16 23:08, Mick Sulley wrote:

> OK I have half fixed it.  Here is what I did in case anyone is
> interested -
>
> Create backup of the database, (just copy digikam4.db)
> Open a SQLite Manager in Firefox
> Browse the 'tags' table and note the id of the tag with name 'People',
> mine was 22
> in the 'Execute SQL' tab run
>
> select * from tagproperties
> where property = 'person'
> and tagid not in
> (select t.id from tags t, tagstree tt
> where tt.pid = 22
> and tt.id = t.id)
>
> This should list all of the tags that are listed under People in the
> left tab but are not really people
> If that list looks like the ones you want to remove run
>
> delete  from tagproperties
> where property = 'person'
> and tagid not in
> (select t.id from tags t, tagstree tt
> where tt.pid = 22
> and tt.id = t.id)
>
> I now only see real people tags on the left side, however, when I try
> to tag someone I still get non person tags listed in the suggestions.
> Does this mean that there is some other rouge data in my database?
>
> How does DK generate the list of suggestions?
>
> Mick
>
>
> On 30/12/16 15:33, Mick Sulley wrote:
>> Hi Simon,
>>
>> Thanks for the info.  I have done some database stuff in the past, so
>> I will have a poke around and see what I can do.
>>
>> Mick
>>
>>
>> On 30/12/16 15:23, Simon Frei wrote:
>>> Hi Mick,
>>>
>>> We are hijacking an unrelated bug with this, it is probably more
>>> appropriate to continue this discussion here.
>>> I am currently working on a feature to remove face identities from the
>>> database and thus also marking the tag as not a people tag. You can
>>> either wait for that or try to fix your problem in the database.
>>> I never ventured into the database code, but I think the sql line
>>> responsible for the people association of a tag is
>>>      INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?,
>>> ?);
>>> where the property value is "person". The existence of this property
>>> marks the tag as a people tag.
>>>
>>> Cheers,
>>> Simon
>>>
>>> On 30/12/16 16:01, Mick Sulley wrote:
>>>> https://bugs.kde.org/show_bug.cgi?id=360155
>>>>
>>>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>>>> I have just gone through all of the non-people tags on the left,
>>>> none of them
>>>> link to any pictures, so I guess it is a problem with the
>>>> database.  Do you
>>>> know which table identifies a tag as a face tag?
>>>>
>>>
>>
>

[Mick Sulley]

Sorry, which database do you mean?  In my album directory I have
digikam4.db, recognition.db, thumbnails-digikam.db and
thumbnails-digikam4.db (which is empty)



On 31/12/16 14:09, Simon Frei wrote:

> You also need to remove the persons from the identity table of the face
> database. However there is more information in the face database
> associated with that identity. I do not know if these will automatically
> be cleaned up when digikam is next run or if it will become messy. Just
> keep backing up before trying it out.
>
>
> On 30/12/16 23:08, Mick Sulley wrote:
>> OK I have half fixed it.  Here is what I did in case anyone is
>> interested -
>>
>> Create backup of the database, (just copy digikam4.db)
>> Open a SQLite Manager in Firefox
>> Browse the 'tags' table and note the id of the tag with name 'People',
>> mine was 22
>> in the 'Execute SQL' tab run
>>
>> select * from tagproperties
>> where property = 'person'
>> and tagid not in
>> (select t.id from tags t, tagstree tt
>> where tt.pid = 22
>> and tt.id = t.id)
>>
>> This should list all of the tags that are listed under People in the
>> left tab but are not really people
>> If that list looks like the ones you want to remove run
>>
>> delete  from tagproperties
>> where property = 'person'
>> and tagid not in
>> (select t.id from tags t, tagstree tt
>> where tt.pid = 22
>> and tt.id = t.id)
>>
>> I now only see real people tags on the left side, however, when I try
>> to tag someone I still get non person tags listed in the suggestions.
>> Does this mean that there is some other rouge data in my database?
>>
>> How does DK generate the list of suggestions?
>>
>> Mick
>>
>>
>> On 30/12/16 15:33, Mick Sulley wrote:
>>> Hi Simon,
>>>
>>> Thanks for the info.  I have done some database stuff in the past, so
>>> I will have a poke around and see what I can do.
>>>
>>> Mick
>>>
>>>
>>> On 30/12/16 15:23, Simon Frei wrote:
>>>> Hi Mick,
>>>>
>>>> We are hijacking an unrelated bug with this, it is probably more
>>>> appropriate to continue this discussion here.
>>>> I am currently working on a feature to remove face identities from the
>>>> database and thus also marking the tag as not a people tag. You can
>>>> either wait for that or try to fix your problem in the database.
>>>> I never ventured into the database code, but I think the sql line
>>>> responsible for the people association of a tag is
>>>>       INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?,
>>>> ?);
>>>> where the property value is "person". The existence of this property
>>>> marks the tag as a people tag.
>>>>
>>>> Cheers,
>>>> Simon
>>>>
>>>> On 30/12/16 16:01, Mick Sulley wrote:
>>>>> https://bugs.kde.org/show_bug.cgi?id=360155
>>>>>
>>>>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>>>>> I have just gone through all of the non-people tags on the left,
>>>>> none of them
>>>>> link to any pictures, so I guess it is a problem with the
>>>>> database.  Do you
>>>>> know which table identifies a tag as a face tag?
>>>>>

[Simon Frei]

Recognition.db is the "face database".


On 31/12/16 15:31, Mick Sulley wrote:

> Sorry, which database do you mean?  In my album directory I have
> digikam4.db, recognition.db, thumbnails-digikam.db and
> thumbnails-digikam4.db (which is empty)
>
>
>
> On 31/12/16 14:09, Simon Frei wrote:
>> You also need to remove the persons from the identity table of the face
>> database. However there is more information in the face database
>> associated with that identity. I do not know if these will automatically
>> be cleaned up when digikam is next run or if it will become messy. Just
>> keep backing up before trying it out.
>>
>>
>> On 30/12/16 23:08, Mick Sulley wrote:
>>> OK I have half fixed it.  Here is what I did in case anyone is
>>> interested -
>>>
>>> Create backup of the database, (just copy digikam4.db)
>>> Open a SQLite Manager in Firefox
>>> Browse the 'tags' table and note the id of the tag with name 'People',
>>> mine was 22
>>> in the 'Execute SQL' tab run
>>>
>>> select * from tagproperties
>>> where property = 'person'
>>> and tagid not in
>>> (select t.id from tags t, tagstree tt
>>> where tt.pid = 22
>>> and tt.id = t.id)
>>>
>>> This should list all of the tags that are listed under People in the
>>> left tab but are not really people
>>> If that list looks like the ones you want to remove run
>>>
>>> delete  from tagproperties
>>> where property = 'person'
>>> and tagid not in
>>> (select t.id from tags t, tagstree tt
>>> where tt.pid = 22
>>> and tt.id = t.id)
>>>
>>> I now only see real people tags on the left side, however, when I try
>>> to tag someone I still get non person tags listed in the suggestions.
>>> Does this mean that there is some other rouge data in my database?
>>>
>>> How does DK generate the list of suggestions?
>>>
>>> Mick
>>>
>>>
>>> On 30/12/16 15:33, Mick Sulley wrote:
>>>> Hi Simon,
>>>>
>>>> Thanks for the info.  I have done some database stuff in the past, so
>>>> I will have a poke around and see what I can do.
>>>>
>>>> Mick
>>>>
>>>>
>>>> On 30/12/16 15:23, Simon Frei wrote:
>>>>> Hi Mick,
>>>>>
>>>>> We are hijacking an unrelated bug with this, it is probably more
>>>>> appropriate to continue this discussion here.
>>>>> I am currently working on a feature to remove face identities from
>>>>> the
>>>>> database and thus also marking the tag as not a people tag. You can
>>>>> either wait for that or try to fix your problem in the database.
>>>>> I never ventured into the database code, but I think the sql line
>>>>> responsible for the people association of a tag is
>>>>>       INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?,
>>>>> ?);
>>>>> where the property value is "person". The existence of this property
>>>>> marks the tag as a people tag.
>>>>>
>>>>> Cheers,
>>>>> Simon
>>>>>
>>>>> On 30/12/16 16:01, Mick Sulley wrote:
>>>>>> https://bugs.kde.org/show_bug.cgi?id=360155
>>>>>>
>>>>>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>>>>>> I have just gone through all of the non-people tags on the left,
>>>>>> none of them
>>>>>> link to any pictures, so I guess it is a problem with the
>>>>>> database.  Do you
>>>>>> know which table identifies a tag as a face tag?
>>>>>>
>

[Mick Sulley]

I just stopped DK, deleted recognition.db and restarted.  I still get
all (or at least many) tags listed in the suggestions box, not just people.

I notice that the old recognition.db was 1.1mb but the new one is only
10.2kb, not sure why.


On 31/12/16 16:12, Simon Frei wrote:

> Recognition.db is the "face database".
>
>
> On 31/12/16 15:31, Mick Sulley wrote:
>> Sorry, which database do you mean?  In my album directory I have
>> digikam4.db, recognition.db, thumbnails-digikam.db and
>> thumbnails-digikam4.db (which is empty)
>>
>>
>>
>> On 31/12/16 14:09, Simon Frei wrote:
>>> You also need to remove the persons from the identity table of the face
>>> database. However there is more information in the face database
>>> associated with that identity. I do not know if these will automatically
>>> be cleaned up when digikam is next run or if it will become messy. Just
>>> keep backing up before trying it out.
>>>
>>>
>>> On 30/12/16 23:08, Mick Sulley wrote:
>>>> OK I have half fixed it.  Here is what I did in case anyone is
>>>> interested -
>>>>
>>>> Create backup of the database, (just copy digikam4.db)
>>>> Open a SQLite Manager in Firefox
>>>> Browse the 'tags' table and note the id of the tag with name 'People',
>>>> mine was 22
>>>> in the 'Execute SQL' tab run
>>>>
>>>> select * from tagproperties
>>>> where property = 'person'
>>>> and tagid not in
>>>> (select t.id from tags t, tagstree tt
>>>> where tt.pid = 22
>>>> and tt.id = t.id)
>>>>
>>>> This should list all of the tags that are listed under People in the
>>>> left tab but are not really people
>>>> If that list looks like the ones you want to remove run
>>>>
>>>> delete  from tagproperties
>>>> where property = 'person'
>>>> and tagid not in
>>>> (select t.id from tags t, tagstree tt
>>>> where tt.pid = 22
>>>> and tt.id = t.id)
>>>>
>>>> I now only see real people tags on the left side, however, when I try
>>>> to tag someone I still get non person tags listed in the suggestions.
>>>> Does this mean that there is some other rouge data in my database?
>>>>
>>>> How does DK generate the list of suggestions?
>>>>
>>>> Mick
>>>>
>>>>
>>>> On 30/12/16 15:33, Mick Sulley wrote:
>>>>> Hi Simon,
>>>>>
>>>>> Thanks for the info.  I have done some database stuff in the past, so
>>>>> I will have a poke around and see what I can do.
>>>>>
>>>>> Mick
>>>>>
>>>>>
>>>>> On 30/12/16 15:23, Simon Frei wrote:
>>>>>> Hi Mick,
>>>>>>
>>>>>> We are hijacking an unrelated bug with this, it is probably more
>>>>>> appropriate to continue this discussion here.
>>>>>> I am currently working on a feature to remove face identities from
>>>>>> the
>>>>>> database and thus also marking the tag as not a people tag. You can
>>>>>> either wait for that or try to fix your problem in the database.
>>>>>> I never ventured into the database code, but I think the sql line
>>>>>> responsible for the people association of a tag is
>>>>>>        INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?,
>>>>>> ?);
>>>>>> where the property value is "person". The existence of this property
>>>>>> marks the tag as a people tag.
>>>>>>
>>>>>> Cheers,
>>>>>> Simon
>>>>>>
>>>>>> On 30/12/16 16:01, Mick Sulley wrote:
>>>>>>> https://bugs.kde.org/show_bug.cgi?id=360155
>>>>>>>
>>>>>>> --- Comment #12 from Mick Sulley <[hidden email]> ---
>>>>>>> I have just gone through all of the non-people tags on the left,
>>>>>>> none of them
>>>>>>> link to any pictures, so I guess it is a problem with the
>>>>>>> database.  Do you
>>>>>>> know which table identifies a tag as a face tag?
>>>>>>>