jeffreygaydos / music-database-generator Goto Github PK

Fields that likely have 3rd party data available:

• ISRC
• TDB

If there is nothing in the input folder but there is data in the database, the user should at least have the option to run an export-only of the playlists. This is especially useful for when a user edits the paths/other data of songs and wants to regenerate playlist files with that updated data (only works if the MusicDatabaseGenerator can update the tracks in such a way that the trackID is preserved).

Additionally, if the playlist transfer tool finds that it has a trackID which does not exist in the database, delete that part of the playlist and notify the user that it could not find one of the tracks

• consider storing the title or path with the trackid so that the tool can say which song was unable to be found during export

`{"aliases":[{"begin":null,"type-id":"d4dcd0c0-b341-3612-a332-c0ce797b25cf","sort-name":"Orrantia, Omar Dejesu","locale":null,"end":null,"ended":false,"primary":null,"type":"Legal name","name":"Omar Dejesu Orrantia"}],"isnis":["0000000493417373","0000000493716314"],"disambiguation":"","area":{"type":null,"iso-3166-1-codes":["US"],"name":"United States","id":"489ce91b-6658-3307-9877-795b68554c98","disambiguation":"","sort-name":"United States","type-id":null},"country":"US","type-id":"b6e035f4-3ce9-331c-97df-83397230b0df","name":"Sin Estres","begin_area":{"type":null,"name":"Van Nuys","sort-name":"Van Nuys","disambiguation":"","type-id":null,"id":"4dba3eb0-3541-4ac6-bc8e-7c63bf8b4d47"},"end_area":null,"sort-name":"Sin Estres","life-span":{"begin":"1986-11-23","end":null,"ended":false},"end-area":null,"type":"Person","gender":"Male","gender-id":"36d3d30a-839d-3eda-8cb3-29be4384e4a9","ipis":["00836305443","00836305541","00836305639","01171441581"],"id":"5b649725-9874-47e3-a1e3-6a5d6ee323cd","begin-area":{"type":null,"name":"Van Nuys","sort-name":"Van Nuys","disambiguation":"","type-id":null,"id":"4dba3eb0-3541-4ac6-bc8e-7c63bf8b4d47"}} `

Originally posted by @Strezless1 in SinEstres/Strezless-Musick-Productionz-Founder#10 (comment)

The subtables use an Identity column as their key, so during generation that value is not available until the entry is added. Currently, the only other column we have is the name or description of the data in the subtables. If 2 albums or 2 artists have the same name, we would map both to the same data in the subtables. This is undesirable. More data is needed to differentiate between this kind of data specifically in the Artist and Album tables.

Artist - Just needs more specific data about the artist to differentiate them from other artists
Album - Consider adding a reference to the primary artist associated with the album directly in the table

Consider adding a unique constraint to everything other than the identity column in these tables

See related: #5

The MusicLibraryTrack.cs file is getting bloated with specifics on how we translate from the MusicLibraryTrack object to the database itself for each track. It is already split into functions, just split these functions into separate objects like we did with the generation scripts, one for each function.

• IDataSync
• ADataSync
• AlbumSync
• ArtistSync
• PostProcessSync
• etc.

Many tracks surprisingly don't have any artist data associated with them. This is an issue with the file itself lacking data, not our parsers. Use the directory of each file to make a best guess at what the artist data should be, but only after using any data directly on the file

This is important because not everyone wants to (or is able to) use SQL Server on any device. Specifically, Android (and probably Apple) devices do not support using SQL Server locally (obviously), but do allow apps to create & use SQLite databases. This will be essential if we plan to use this to system with my Synthia project.

Groove Music stores duration in milliseconds, and I can't imagine any other music app getting more precise than that. Since we don't store duration in milliseconds, the total duration of a playlist (calculated from individual durations rounded to the nearest second) can vary. This doesn't affect the ability of the Groove Music playlist to work but could make the stat for that playlist or others inaccurate. By innacurate, we are talking up to 0.5 seconds per track in a playlist. This means that a playlist with 100 songs could be off by as much as 50 seconds.

Since we can't be 100% certain what kind of data we will find in mp3 files, we should validate what is found before using EF to insert it. If inserting the data would violate any constraints in the SQL, we should ignore that data and insert some empty/default value.

It would be nice if the user did not have to completely regenerate the music database every time they acquired new music they wanted to add to the database.

Devise a system that records which files the database generator has used.

INSERT: That way when new music is added, it skips all the files it already knows about.
DELETE: Also, every time we re-generate, check to see if the file still exists in that spot and if it does not, delete the track and all related data from the database.
UPDATE: Depending on how we keep track of these things, the user may be able to update some file or whatever we are using to make the generator re-ingest the data for that track.

• This could also be achieved by deleting and re-adding the tracks as well, which may be simpler from a development and user perspective

Keep in mind that the intent of this is to speed up generation for when a user needs to add or delete only a few songs to the database.

Certain tracks, in my case the Portal 1 & 2 soundtracks, repeatedly get ingested during each run even though no changes are found with them. Investigate why this is happening and fix it. I would guess it has to do with not setting the update timestamp properly.

Allow ISRCs with in the format: AA-AAA-11-11111. They are still valid because they have only 12 meaningful characters, but the extra - characters make the field 15 characters long. Increase the field length to match on Main.

Although, we should just handle this, remove the -s and shove it into the 12 characters.

In general, try setting this up on a "fresh" machine, where SQL and SQL Server may not already be set up, and document how to do that. Additionally, document how to change the proper configs to get a user's data to run. The following is a list of suggestions:

• Utilize a config file to allow for absolute paths to music
• Create a more advanced loading system so you can see progress and estimated wait time of the generator
• Add log output for more specific details on what is happening with each file and why/if certain fields are missing (if those fields are some of the more important ones that really should be on every MP3 file)
  • Of course, have the output of the console direct users to this more verbose logging when necessary

Last updated:	2021-07-11 14:19 PDT

Originally posted by @StrezlessMusick in StrezlessMusick/Musician-on-the-rise#3 (comment)

Once you get past around 1,000 tracks, processing becomes extremely slow (~5 seconds per track). The first couple of inserts are quick, but subsequent inserts are much slower. Determine if this is due to something in C# or SQL and attempt to improve performance.

Some tables have references to IDs from other tables. Add foreign key constraints to ensure the IDs in those tables actually match with IDs found in the table they intend to reference

Consider adding unique constraints to tables with auto-incrementing keys to all columns except for their keys

• i.e. The data in the table should be unique not just because of the auto-increment, but because the "payload" data is actually different
• UNIQUE constraints should only be used on "primary" tables. That is, tables that have real data in them, not just tables that map between other tables.

There are a couple of fields and tables that I have marked as subject to change. Before we close out this project, let's make sure those fields are deleted or implemented.

Average Decibels

• Not sure how useful this will be
• Min/Max Decibels might also be useful
• Reading out decibel values is 1 - no longer metadata, but content, and 2 - extremely time consuming. Opting to replace this with the following:
• Comfortable listening volume column that is custom modified by the user (as needed) might be the best option

Linked Tracks

• The bit flag on Main is maybe a bit unnecessary, but depending on how large the LinkedTracks table gets, it could save us on performance
• Rename the table to be PlayBeforeTrackID and PlayAfterTrackID
• Yes you can make a playlist out of this
  • Come to think of it, we should just implement this as a playlist
  • Also, you didn't put ordering in the playlists at all lol

Playlist

• Needs playlist track rank
• Can be utilized for "linked tracks"
  • LinkedTrackPlaylistID is present if the tracks are linked. It is a reference to the PlaylistTracksTable, which can then tell us which order the tracks should be played in.
  • This will be especially efficient for tracks that are linked to a lot of other tracks since we can literally treat it like a playlist

Rating

• Get it off the file for the tracks
  • Hmm actually ratings are a Windows-specific datapoint that is not attached to the files themselves
• This will be a dummy variable then...

See te-custom-mods for inspiration

Creating a build does not allow the user to change the connection string from the App.config file if the user has multiple local databases or wishes to use something other than the default settings for the SQL Server Express setup. See if there is a way to change the connection string in code using the appsettings.json file.

Most local music playing apps on mobile and desktop have ways to export music playlists:

Groove Music:

• .zpl files located in Music\Playlists\Playlists from Groove Music (these files are essentially XML files)

Samsung Music:

• .m3u files located directly in the Music folder of the phone (only after exporting them via the app)

Further support for music apps will be considered later, we'll start with these 2 for now.

Document how to import/export playlists on the readme

Moods are not a very generalized feature that most music apps would end up using. Additionally, Moods are simply unordered playlists, and should be implemented using that table in the "front-end" music app that uses this database if desired. This also simplifies CRUD operations

The tool should not run into duplicate key errors, even if the delete existing Playlist data configuration is set to false. Determine why the duplicate key error is occurring and either fix the bug or notify the user of their error

https://strezless-musick-productionz.business.site/

https://github.com/StrezlessMusick

Originally posted by @StrezlessMusick in StrezlessMusick/Musician-on-the-rise#3 (comment)