[black_eagle]

I am guessing this is very old code that got left in as potentially useful when @DaveBlake restructured everything to use MBIds

You're guessing wrong then.  The artist table does indeed contain ALL of the artists related to a given song but these are also linked to the roles table.  Executing something like

sql:
SELECT * from songartistsview WHERE songartistview.idSong = 1;

gives me a result of



So, I find all of that information useful. It was Dave that added all the role stuff in the first place. I also doubt that stuff gets duplicated particularly. If you take my example, artist 9 is Angus young, listed as both guitarist and writer. Two different roles with different id's but the same artistid. Therefore, Angus can appear in multiple songs in multiple roles, but has only one entry in the artist table.

To force an update of artist info without clearing the library and re-scanning everything, one would reset the field "lastScraped" in the "artist" table of the "mymusicxx" database. After that it's a simple case of refreshing all artist info. If you attempt to refresh artist info without resetting that field, Kodi will only refresh those artist which haven't been scraped to the library yet.

Nope. Navigate to a list of artists and then Context menu -> query info for all artists will (re)scrape all your artists. Context menu -> change information provider can re-scrape one artist, or if you use a smart playlist to select a group of artists, for just those artists displayed.  If there is local information, you should be given the choice of using that, or refreshing from the internet, depending upon whether your scraper setting is local only or not.  You can also do the same thing for a list of albums, which will refresh the album info.  It's almost as if someone had thought we might want to update stuff eh ?

It would be IMHO, foolish in the extreme to mess around in the music database without first understanding exactly how everything ties together.  At times, this is not necessarily obvious immediately.

[black_eagle]

I see lots of potential for false positives so I am curious how folks want to think about duplicates in the music database.  For me I am think song / album combination but I need to give it much more thought.

What about compilation albums or anthologies that contain tracks on other albums by the same artist?  For instance, I have all the ABBA albums (as originally released), but I also have the box set "The complete Studio Recordings" which obviously includes all of those albums, along with some bonus stuff too.  Is that a duplication or not (the boxset has a different mbid).

Or what about

Code:

SELECT strAlbum from album WHERE idAlbum in (SELECT idAlbum from song WHERE strTitle = "Your Song");
+----------------------------------------------------------------+
| strAlbum                                                       |
+----------------------------------------------------------------+
| Elton John                                                     |
| Greatest Hits                                                  |
| Greatest Hits 1970-2002                                        |
| Greatest Hits 1970-2002                                        |
| Greatest Hits 1970-2002                                        |
| Here and There                                                 |
| Love Songs                                                     |
| One Night Only                                                 |
| The Very Best of Elton John                                    |
| To Be Continued...                                             |
| Tribute To                                                     |
| Live in Australia                                              |
| Phoenix                                                        |
| Now That’s What I Call Music! 97                               |
| Radio 1: Established 1967                                      |
| Revamp: Reimagining the Songs of Elton John & Bernie Taupin    |
| The Love Songs Album                                           |
| Two Rooms: Celebrating the Songs of Elton John & Bernie Taupin |
+----------------------------------------------------------------+

 

Your Song appears on all those albums but I'd not consider it a duplicate.  Even the three "Greatest Hits 1970-2002" aren't actually duplicates in my view as they are different versions.  17+17 tracks, 17+17+4 and 17+17+11.  Determining an actual duplicate would involve comparing the mbids of the possible duplicate items as well as the titles and artists (and possibly the path(s) too) to decide if something actually is a duplicate or not.  But as can be seen from just a couple of simple examples from the top of my head, this isn't at all as straightforward as it might at first appear to be.

And then we have users who for their own reasons don't want to use mbids or have badly tagged files from years ago that have never been re-tagged or fixed but are scanned into their libraries and you have to make sure that whatever solution you eventually come up with (if indeed you decide to) doesn't trash half their library because of the bad tagging etc. 

Just some food for thought.....

[jbinkley60]

Not sure how beneficial cleaning of music database is for your addon.
I remember @DaveBlake telling me the relationships were quite complex between artist-album-songs and the database was better designed and more robust than the video database.
It is not possible to delete anything from the music database from within Kodi. You can't delete albums or songs or artists. Anything that needs deleting, the files need to be moved out of the source and then run a Clean Library.

The best person to explain multi-path and to advise whether adding music cleaning is of any use would be @black_eagle

I'll start with the analyzer code and we can tweak it until we get rid of most false positives.  It may take a couple of options for some folks who have better or worse libraries.  I don't see too big of an issue with songs or artists.  I can compare them against all other tables they may appear in and if thee are any matches then they aren't orphaned.   The cleaner code just takes the output of records from the analyzer and deletes the identified records.  I'll need to spend a bit of time looking at the triggers too, which often do some cleanup of their own.  I think we'll know pretty quick on whether the analyzer is able to find anything or not without developing all table analysis.  I'll start with a couple of simpler ones first and see what happens.


Thanks,

Jeff

[jbinkley60]

My wish list for the music database cleaning includes some hacks/manipulation. The problem with the music database is - while it is pretty robust - that once everything is scanned into the library, small changes to the underlying file (system) are not picked up by Kodi. For example, at one time I had to move my artist information folder to an Unraid server and was unable to maintain the exact path. This resulted in all art not being picked up properly afterwards and the display of artist info became weird. Also, there is no easy way to update artist biographies in batches, i.e. when new albums are released, the artists dies or a group is disbanded.

Both cases can be adressed by database manipulation. To force an update of artist info without clearing the library and re-scanning everything, one would reset the field "lastScraped" in the "artist" table of the "mymusicxx" database. After that it's a simple case of refreshing all artist info. If you attempt to refresh artist info without resetting that field, Kodi will only refresh those artist which haven't been scraped to the library yet.

This type of stuff likely won't float to the top of the list.  It veers into recraping and metadata cleanup vs. identification and deletion.  

The process of refreshing artist information will usually also pick up any missing art again, with the notable exception of the artist thumbnail, in my experience. I haven't found a good way to remedy this yet, as all art is stored in a dedicated table "art" and then concatenated into the "artist" table in the field "strImage". If there was way to correct the db by checking against the correct path to the artist info folder that would be awesome.

This one I can look closer at.  There may be a way to identify missing things like certain artwork.  

 

Lastly, the artist table has an incredible amound of entries, not all of which are useful (at the moment, IMHO). Not only are album and song artists imported but also all entries from the tags COMPOSER and PERFORMER (or their counterparts in other tag schemes). I am guessing this is very old code that got left in as potentially useful when @DaveBlake restructured everything to use MBIds. As those entries are not necessarily connected to MBIds they will result in possible duplicates and the necessity of manual intervention during the scraping of artist information. I would think some code refactoring is needed here to search composers and performers's MBIds. KSCleaner could help here in a similar fashion to the Video Duplicates functionality and search artists without MBIds and juxtapose them with entries with the same name that have an MBId, maybe even offering to fill that missing info (I'm dreaming here but hey, can you blame a guy...).

I'll likely start with artists, art, song_artist, sources and paths which have no other associated entries in any table in the music database.  These are classic types of orphans.  They we can look at some level of data integrity checking, including missing data fields.  That could be handled via an addon setting.  I may be developing this at the same time I write the Mezzmo Kodi addon music database sync capability, since so much of the code is similar.  Together they will give me pretty good insight into certain aspects of the database schema and relationships. 

Stay tuned,

Jeff

[jbinkley60]

Your Song appears on all those albums but I'd not consider it a duplicate.  Even the three "Greatest Hits 1970-2002" aren't actually duplicates in my view as they are different versions.  17+17 tracks, 17+17+4 and 17+17+11.  Determining an actual duplicate would involve comparing the mbids of the possible duplicate items as well as the titles and artists (and possibly the path(s) too) to decide if something actually is a duplicate or not.  But as can be seen from just a couple of simple examples from the top of my head, this isn't at all as straightforward as it might at first appear to be.

And then we have users who for their own reasons don't want to use mbids or have badly tagged files from years ago that have never been re-tagged or fixed but are scanned into their libraries and you have to make sure that whatever solution you eventually come up with (if indeed you decide to) doesn't trash half their library because of the bad tagging etc. 

Just some food for thought.....

Yeah, duplicate detection is going to be problematic, hence my questions.  The duplication function is just an identifier and doesn't do deletions so I am not worried about it trashing a library but ti could throw out a lot of false positives, as you indicate.  I'll likely hold off on duplicate detection until the simpler orphan record analysis is working.   In the end there may not be a clean way of identifying certain duplicates except by the user selecting what they choose to be a dupe.

I appreciate the feedback,


Jeff

[HeresJohnny]

Nope. Navigate to a list of artists and then Context menu -> query info for all artists will (re)scrape all your artists.

Thank you for your insights. I don't claim to have your understanding of the inner workings, I'm just doing goal oriented workaround stuff. The above functionality was the whole reason I wrote my little treaty. Contrary to what you're saying, when I execute "query info for all artists" on a fully scraped library it does exactly... nothing.

It would be IMHO, foolish in the extreme to mess around in the music database without first understanding exactly how everything ties together.  At times, this is not necessarily obvious immediately.

If you make something foolproof someone else will make a better fool... I can only say that the current state doesn't fit my needs and I have to execute the steps described by me before to get the desired results.

Anyway, I think we're just throwing around ideas at this point so any and all input is welcome and of course doubly so that of the devs.

[jbinkley60]

I've continued to look at the music database and the current versions have done a pretty good job with delete triggers, which should reduce the number of potential orphans but as I look more, I do see more linked fields than I originally found.  They will lend themselves for better orphan detection (maybe from earlier versions of the database, scraper issues etc..).  There are definitely some differences between the music and video database schema, including streamdetails and files information being built into the song table (minus codec information and a few other minor things), no resume field except for auidiobooks (even though resume pointers wok with music listtiems and the Kodi player) and no tag table etc..  

I am curious on the lack of a tag table (and tag_link) and how folks manage dynamic playlists.  I ask because with Mezzmo, that I use, there is almost 100% parity between video and music media.  So keywords (Mezzmo's version of tags), which I use heavily for managing multiple lists of favorites, smart playlists and such are the same between video and music.  I realize Kodi supports traditional m3u and more playlists but those are often static.  How do folks get around not having tags in the music database ?  Is it through ID3/4 tagging of files ?  I am so accustomed to having the feature I am not sure how I would operate without tags.  I feel that I must be missing something.

Thanks,

Jeff

[jbinkley60]

The yellow text colour is much, much better. Thanks.
Just for other users, even though Yellow is shown as the font colour in the settings page, you need to scroll through the colours and back to yellow for the setting to be saved.

I found the issue with the default color setting (Blue) not working, which was causing this behavior.  I had an extra character in the Blue setting.  This will be fixed in v1.0.8 when I release it soon.


Thanks for catching it,


Jeff

[jbinkley60]

I have posted a new release version 1.0.8 of Kodi Selective Cleaner.  This version adds support for Kodi 22 and allows selecting the colors for records which will be cleaned   I appreciate all who provided feature suggestions and helped test this release.


Here's the complete list of highlights since 1.0.7

• Added support for Kodi 22 Piers interim development builds
• Added the ability to select the color of records flagged for cleaning notification


I've also added some content to the Wiki for the  Analyze / Clean All and the Duplicate Video Media Analysis features, including a table which highlights the duplicate media detection algorithms.  I plan to begin work on v1.0.9 in mid-December.  Watch here for questions and ideas for that release.


Thanks,

Jeff

[rafi1]

I have posted a new release version 1.0.8 of Kodi Selective Cleaner.  This version adds support for Kodi 22 and allows selecting the colors for records which will be cleaned   I appreciate all who provided feature suggestions and helped test this release.

Thanks, so, no capitalization related duplicates cleaning as of yet?

[jbinkley60]

Thanks, so, no capitalization related duplicates cleaning as of yet?

My apologies, I forgot about that item.  I'll move it to the top of the list for 1.0.9.  I will need your help to test.   I'll start with this post item #2 but am sure I will need some assistance since I don't use the native Kodi tools, don't see the issue in my setup and don't run a MySQL central DB.  I am pretty sure I can craft a manual solution.  An automated one could be more problematic and may require an additional option to enable / disable similar to strict file checking.


Thanks,

Jeff

[rafi1]

>>I'll start with this post item #2
>1. Duplicate paths with different case matching when running Kodi under Windows (i.e. smb://MyPath and smb://mypath)
>2. Duplicate paths due to Windows drive letter mapping in paths pointing the same place  (i.e. d:\MyVideos   and \\myserver\d\MyVideos)

I think it's best to start with #1 as it is more often sen and common to both smb and none-smb: i.e.:  smb://myservername[or IP]/D/MyPath smb://myservername[or IP]/d/mypath  OR D:\MyPath D:\mypath D:/mypath D:/mypath etc.

BTW in  #1 -  smb://... will always be followed by server IP or name as part of the path.

[jbinkley60]

>>I'll start with this post item #2
>1. Duplicate paths with different case matching when running Kodi under Windows (i.e. smb://MyPath and smb://mypath)
>2. Duplicate paths due to Windows drive letter mapping in paths pointing the same place  (i.e. d:\MyVideos   and \\myserver\d\MyVideos)

I think it's best to start with #1 as it is more often sen and common to both smb and none-smb: i.e.:  smb://myservername[or IP]/D/MyPath smb://myservername[or IP]/d/mypath  OR D:\MyPath D:\mypath D:/mypath D:/mypath etc.

BTW in  #1 -  smb://... will always be followed by server IP or name as part of the path.

Ok.  We can start with #1.  Do you have any instances of this today in your setup ?  If so, can you run the duplicate analyzer and send me the CSV file via a private message ?  I'd like to see exactly what it looks like across the various tables in your database.


Thanks,

Jeff

[rafi1]

Sure, I have the issue mostly visible on my AntroidTV streamer, but also on Win10 (mostly similar smb: & local dups)
https://drive.google.com/file/d/1p7J6d0B...sp=sharing

[jbinkley60]

Sure, I have the issue mostly visible on my AntroidTV streamer, but also on Win10 (mostly similar smb: & local dups)
https://drive.google.com/file/d/1p7J6d0B...sp=sharing

I've started looking at what you provided and I feel compelled to say that I am so glad that I don't have to deal with this level of complexity and issues with my setup.  The Mezzmo Kodi addon keeps this from happening, including having path tables with 870 entries etc..).  Having gotten that out of my system, I have a few questions.

1.  Since I don't use the Kodi scrapers and such, can you tell me if Kodi creates an entry in the source.xml file for each path table entry or just for the main SMB / local mapping ?  I believe it's just the mapping.  I ask because I have no plans to have KSCleaner clean up XMl files at this time.  I don't think this is an issue but it would be ncie if I could get a copy of your sources.xml file.
2.  Is there a reason why you haven't yet let KSCleaner cleanup the orphans in your database ?  It makes it a bit difficult to sort out orphans from dupes, with this level of database integrity issues.  In some cases the dupes could be orphans   For example you have 433 orphaned TV Episodes in your database and many more with data integrity issues, like due to past scraper problems. 
3.  I am trying to determine exactly how the duplicate path issue is occurring.  I suspect your database is running without case sensitivity but you are using a case sensitive client (i.e Linux or Android) to add content to your library.).  Can you help me better understand the cause ?  This will help me best determine a solution.
4.  I saw this type of situation where there are duplicate path entries with different scraper configurations.  I will defer to the Kodi experts on the suggested best way to handle this type of situation.  

From the file table I see a duplicate like this:

 idFile                                      idPath                                                        strFilename
8081                                       2402                     Once.Upon.a.Time.in.Hollywood.2019.WEB-DL.IVI.1080p-SOFCJ.mkv
8862                                       5377                     Once.Upon.a.Time.in.Hollywood.2019.WEB-DL.IVI.1080p-SOFCJ.mkv

And when I look at the path table I see this:

idPath                                        strPathstr                                                                                                ContentstrScraper
2402                smb://192.168.0.4/E/downloads/Movies/moviesmetadata.                                   themoviedb.org
5377                smb://192.168.0.4/E/Downloads/Movies/moviesmetadata.                                  themoviedb.org.python

Sorry for all the questions but we'll need to get to this level of detail to properly fix things without breaking other things.


Thanks,

Jeff