[solved] Mixed artist pictures (3 Viewers)

[ajs]

I'm looking for a SQL command to delete the local source record if an online record exists with the same file name.

Select record in SQL Expert and press minus button on navigation control (above table) ...

With this approach I would loose all Fanart pictures which are not online anymore.
Currently I've over 7,000 Fanart pictures.

Delete missing delete all pictures from DB, when picture present in DB but not present in Fanart folder ...

 

[horned_reaper]

Thanks, but with over 24,000 DB records I can't do this manually. Hence, I'm looking for a SQL command (query) to clean-up.

 

[horned_reaper]

I wrote a PowerShell script to clean up 586 double entries. Now let's see if the issue reoccurs.

 

[horned_reaper]

I partially corrected the descriptions in the configurator. The new version is available in the repository. I can add a full description to read me, or somewhere here in the forum. But I do not know in what form and whether it is necessary.

Two typos:

Exception Options (delete first s)
Don't download Fanarts for Various Artists (add t)

 

[horned_reaper]

I wrote a PowerShell script to clean up 586 double entries. Now let's see if the issue reoccurs.

The issue has been reappeared, with 26 Fanarts so far. Two database records of a Fanart refer to the same picture file.
I already had deleted all duplicate values e. g.
Deleted local source record 10878:

Reappeared:

By the row ID 19607 you see that the online source record remained but the local record was recreated to a new record.
I see the same issue with only local source records as well:

Highly probable the issue is related to case-sensitivity because all duplicates have different spellings:

FullPath

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\music\Jeanette (10729).jpg

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\Jeanette (10729).jpg

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\Him (5886).jpg

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\HIM (5886).jpg

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\music\Jeanette (5).jpg

C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\Jeanette (5).jpg

I did not do a FH full scan or other special things, I just played music.

Could you please make the path check case-insensitive?

 

[ajs]

Could you please make the path check case-insensitive?

Hmmm, its SQLite problem, need to look how make Select case-insensitive ...

 

[ajs]

@horned_reaper
try this select:

SELECT Provider, FullPath FROM Image WHERE Provider = 'Local' AND FullPath COLLATE NOCASE IN (SELECT FullPath FROM Image WHERE Provider <> 'Local')

or

SELECT * FROM Image WHERE Provider = 'Local' AND FullPath COLLATE NOCASE IN (SELECT FullPath FROM Image WHERE Provider <> 'Local')

if return only duplicated pictures for Local provider, then possible delete it like:

DELETE FROM Image WHERE Provider = 'Local' AND FullPath COLLATE NOCASE IN (SELECT FullPath FROM Image WHERE Provider <> 'Local')

 

[horned_reaper]

Thank you, I have a PowerShell script to delete the duplicates as well.
But can you fix this bug in FH?

 

[ajs]

But can you fix this bug in FH?

I add NOCASE property for Column, but how its work i dont know ...

Thank you, I have a PowerShell script to delete the duplicates as well.

If possible, then run this SELECT, if it returns the correct value, I will add it to the database upgrade procedure.

 

[horned_reaper]

Many duplicate items have been found, but with both SELECT queries duplicate items with the same (local) source are not found e. g.:

FullPath	MBID	SourcePath
C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\music\Boney M. (7278).jpg		C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\music\Boney M. (7278).jpg
C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\Boney M. (7278).jpg		C:\ProgramData\Team MediaPortal\MediaPortal\Thumbs\Skin FanArt\Scraper\Music\Boney M. (7278).jpg