Oh man, music data structures are a rabbithole
I added a new feature to my website that automatically shows my current played and historically scrobbled music. I built it from the ground up meaning that I built my own scrobbler (which right now just sniffs my deskto…
以下正文同步自 Hacker News 最新，版权归原站所有，已转换为易读排版。
I added a new feature to my website that automatically shows my current played and historically scrobbled music. I built it from the ground up meaning that I built my own scrobbler (which right now just sniffs my desktop Apple Music and pulls recent Youtube Music) and made a DB and API to store and present this.
When I was drawing out the data structures behind my tables, I started off pretty simple, I was like:
Aerodynamic — Daft Punk
Superhuman — NCT 127
The One Time In Houston — Wale
The Trooper — Iron Maiden
I Won't Back Down — Tom Petty
Song
Artist str
Name str
AlbumName str
Duration float
StartedAt str
ScrobbledAt str
NormalizedAlbumName str
Id str
No Song records
AlbumCover
image64 str
NormalizedAlbumName str
No AlbumCover records
← →
Which for scrobbled data I think that was good enough, I added also a album cover table so I can get some nice pics alongside the song. The autoscrobbler on desktop can rip the local images of Apple Music, so I decided to just get a base64 24x24 picture and store that as a new table and just do a join to pull it so we don’t need to resave a bunch of album art over and over again.
This means that for when I scrobble from Youtube or Apple Music, I would want to build a system that can automatically flatten the songs and albums so the least amount of data is duplicated.
Originally I tried to just simplify this situation by using UPC as a unique identifier, but the Youtube API does not return that, nor do I think most songs uploaded on youtube even if it's official actually have a UPC.
Instead we need to look at the song and album's intrinsic attributes to derive uniquness when we do lookups on our DB.
Now that I had my v1 done, I wanted to make this even more efficient, so I made Song and Scrobble two separate entities
On My Way to Nordstroms (feat. Stockz) — Guapdad 4000
Homecoming — Green Day
Dirty Harry — Gorillaz
American Psycho — grouptherapy.
Song
Name str
NormalizedAlbumName str
AlbumName str
Artist str
Duration float
SongId str
StartedAt str
ScrobbledAt str
Id str
No Song records
Scrobble
ScrobbledAt str
StartedAt str
SongId str
No Scrobble records
AlbumCover
image64 str
NormalizedAlbumName str
No AlbumCover records
← →
Then I was like, let's make Album and Artist their own entity as well
MAGFEST — femtanyl
Cave In — Owl City
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
Song
Name str
Duration float
SongId str
AlbumId str
ArtistId str
NormalizedAlbumName str
AlbumName str
Artist str
No Song records
Scrobble
ScrobbledAt str
StartedAt str
SongId str
No Scrobble records
AlbumCover
AlbumId str
image64 str
NormalizedAlbumName str
No AlbumCover records
← →
But now we have multiple artists, and we want to do a join, we would need to set up a new relation SongArtist since those are many-to-many connections.
High — Freddie Gibbs, Madlib & Danny Brown
Fire Away — Madeon & Slayyyter
Perfect Pinterest Garden (Ninajirachi & underscores Remix) — Porter Robinson, Ninajirachi & underscores
Nice to meet you — PinkPantheress & Central Cee
Opposite Day — FIG & Yoshi T.
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
Song
Name str
Duration float
SongId str
AlbumId str
ArtistId str
No Song records
Scrobble
ScrobbledAt str
StartedAt str
SongId str
No Scrobble records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
For posterity let's also set up ArtistAlbum so we can assocaite multiple artists credited an album’s ownership
letstalkaboutit — Aminé, KAYTRANADA & Freddie Gibbs
Downgrade — Guapdad 4000 & !llmind
Otis — Kanye West & JAY-Z feat. Otis Redding
A$AP Ferg — NAV & Metro Boomin feat. Lil Uzi Vert
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
Song
Name str
Duration float
SongId str
AlbumId str
No Song records
Scrobble
ScrobbledAt str
StartedAt str
SongId str
No Scrobble records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
Oh, also there's explicit versus non-explicit song releases, lets add a is_explicit as well.
LOYALTY. — Kendrick Lamar feat. Rihanna
LOYALTY. — Kendrick Lamar feat. Rihanna
Song
↓ Show existing fields ↑ Collapse existing fields
Name str
Duration float
SongId str
AlbumId str
is_explicit bool
No Song records
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
Scrobble
ScrobbledAt str
StartedAt str
SongId str
No Scrobble records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
But hey, what if the song we want is on multiple albums? How do we define it then?
Bohemian Rhapsody — Queen
Bohemian Rhapsody — Queen
Bohemian Rhapsody — Queen
Bohemian Rhapsody — Queen
AlbumSong
AlbumSongId str
SongId str
AlbumId str
No AlbumSong records
Scrobble
↓ Show existing fields ↑ Collapse existing fields
StartedAt str
ScrobbledAt str
AlbumSongId str
ScrobbleId str
SongId str
No Scrobble records
Song
is_explicit bool
Name str
Duration float
SongId str
AlbumId str
No Song records
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
But what if there’s multiple versions of the same song…on the same album...
I guess we can treat them each as individual songs, but we still need to create some uniqueness here. Apple Music gives us a SongReleaseDate, let's use that, we can probably pull the same from YouTube's upload date, though TBH, this is most likley not going to work given Youtube Music's APIs.
Bodys — Car Seat Headrest
Bodys — Car Seat Headrest
Song
↓ Show existing fields ↑ Collapse existing fields
is_explicit bool
SongId str
Name str
Duration float
SongReleaseDate str
No Song records
AlbumSong
AlbumSongId str
SongId str
AlbumId str
No AlbumSong records
Scrobble
↓ Show existing fields ↑ Collapse existing fields
ScrobbleId str
StartedAt str
ScrobbledAt str
AlbumSongId str
No Scrobble records
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
Album
AlbumName str
AlbumId str
No Album records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
What if there’s a rerelease of an album then? Guess I need to do that as well...turns out though, most albums rereleased do acutally change their name, besides Fearless...thanks Taylor.
But the metadata actually claims it's release date was the original date, I guess the track count is different, lets just add that as a field.
Love Story — Taylor Swift
Love Story — Taylor Swift
Album
↓ Show existing fields ↑ Collapse existing fields
AlbumId str
AlbumName str
TrackCount int
No Album records
Song
↓ Show existing fields ↑ Collapse existing fields
is_explicit bool
SongId str
Name str
Duration float
SongReleaseDate str
No Song records
AlbumSong
AlbumSongId str
SongId str
AlbumId str
No AlbumSong records
Scrobble
↓ Show existing fields ↑ Collapse existing fields
ScrobbleId str
StartedAt str
ScrobbledAt str
AlbumSongId str
No Scrobble records
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
Now what if an artist released the same song, with the metadata claiming the same name and release date….seriously....
Role Model by Eminem has two versions, but in the Apple Music metadata it retains the same release date....
I guess we can check uniqueness with also songDuration
Role Model — Eminem
Role Model — Eminem
Song
↓ Show existing fields ↑ Collapse existing fields
is_explicit bool
SongId str
Name str
SongReleaseDate str
Duration float
No Song records
Album
↓ Show existing fields ↑ Collapse existing fields
AlbumId str
AlbumName str
TrackCount int
No Album records
AlbumSong
AlbumSongId str
SongId str
AlbumId str
No AlbumSong records
Scrobble
↓ Show existing fields ↑ Collapse existing fields
ScrobbleId str
StartedAt str
ScrobbledAt str
AlbumSongId str
No Scrobble records
ArtistAlbum
ArtistId str
AlbumId str
No ArtistAlbum records
SongArtist
SongId str
ArtistId str
No SongArtist records
Artist
ArtistId str
ArtistName str
No Artist records
AlbumCover
AlbumId str
image64 str
No AlbumCover records
← →
You know, why is it that Role Model has two different durations and two versions that are both explicit?
After some digging (listening) even though the longer one is labeled as explicit, it's mostly censored, so probably the music distortion adds 5s. I am not sure why Apple labels it as explicit though, maybe because of how violent it is as a song? Maybe it was a mistake on their end ¯(ツ)/¯
But anyway, what if all of those above are true, and also the two versions are the exact...same...duration...thanks Taylor...
ME! — Taylor Swift feat. Brendon Urie
ME! — Taylor Swift feat. Brendon Urie
Taylor Swift Removes “Sp
正文由 探How 从来源站点 RSS 同步，内容未经改写；遇到排版缺失或需要图片、视频时请以原文为准。