I've actually shelved this project for a number of reasons, but I did start and added a UUID to each song in a custom tag that's not visible to MediaMonkey. In C# I used System.Guid.NewGuid() to generate which should almost certainly be unique for each song, but I do a quick check for uniqueness anyway. Then I write that tag to the FLAC.

This is where I stopped, but the next steps would be to create another database with the UUID as a key, and the various fields for each.

While the UUID might be overkill since the SongID should work, I also use the UUID to sync certain fields between 2 MediaMonkey databases, since I listen at home and at work. So if I rate a song at work, or rename it at home, I can sync the two MediaMonkey databases using the song's UUID as a certain way of identifying a song across multiple databases.

I like what Barry suggested he sounds like he has plenty of experience. I was going to suggest something along those lines. I don't have a clue as to what a database trigger would do that a SQL script could't do for this application. I have 15+ yrs of writing triggers. You put as little code in them that you can. Any code that could be executed somewhere else should be there, not in the trigger. You actually think long and hard about how you keep code out of a trigger.

Hello Sir,

Will you make your code available to the public?

Use Trigger in Database

Thanks. Knowing that the SongID doesn't change for optimizations is huge.

I am sure that I will have to perform some integrity checks to handle deletions. It makes sense to check for re-assignment of SongID, too.

Hopefully I can avoid using Custom fields, so that I can use them for other things. I also want to avoid writing more info into the tags of the files themselves.

Thanks!

Scottes wrote:My first guess would be to use SongID, but I'm worried that a specific SongID might get changed after deleting songs and/or cleaning and compacting the database.
Would SongID be a good key field to link the databases? Do SongIDs ever get re-used and/or re-assigned?

It would be easy to generate a unique ID and store it in a Custom field, and link on that, but I'd rather not use up a Custom.

I use SongID to join an external database to MM.

It doesn't get affected by File|MaintainLibrary optimisations.

If you also wanted to be safe against track deletions and possible re-assignment of the key you should probably insert your own key into a custom field.

One thing is that you should look into is Full Text Search, or whatever it is called. The custom fields are probably included, so your keys would be indexed there. Not that it matters probably.

Do you mean you require more Custom fields?

When you change the song Path you may be able to recover through File > Locate Moved/Missing in the other databases. However it may not be fail proof. SongID would be worse though as the different database won't likely match it. There is the duplicate fingerprint (that you can enabled under Tools > Options > Library), but I'm failry sure it's not unique enough (2 of the same songs from different Albums will likely have the same fingerprint).

You could use a Custom field with a unique ID. As custom fields are stored in the files for most file types the value transfers between databases. However you'd still end up having to deal with moved files.

Lowlander wrote:Path would be the unique identifier across different databases,

Do you mean SongPath? That could change if I rename a song and auto-organize.

Perhaps I wasn't clear enough that I want to link the songs together. So a song in MM can be linked to all that song's info I have stored in my database.

Lowlander wrote:however I don't understand why the data would be too much for 1 database.

Said that way, you're right. But that would mean extending the MM database by adding fields and I'd be afraid that this would not be safe for an upgrade. (Is it?)

Path would be the unique identifier across different databases, however I don't understand why the data would be too much for 1 database.

I'm working on a project (C#) to get a lot of info about my songs. I'm going to access numerous databases (MusicBrainz, Echo Nest, Last.fm, etc.) and grab what info I can get (within reason). As an example, I might get genres/styles from all of them for later analysis when looking for similar artists/albums/tracks.

This will be too much data to store in MM, so I will put it in a different database. Yet I want to be able to somehow link the two so that I can show a single form that shows a song's data from all sources simultaneously (MM, MusicBrainz, Last.fm, etc.) So I need a way to link songs in MM to records in my database.

My first guess would be to use SongID, but I'm worried that a specific SongID might get changed after deleting songs and/or cleaning and compacting the database.
Would SongID be a good key field to link the databases? Do SongIDs ever get re-used and/or re-assigned?

It would be easy to generate a unique ID and store it in a Custom field, and link on that, but I'd rather not use up a Custom.

Anyone have any suggestions?