codefordenver / codefordenver/Comrad-cloud-data

Initial data seeding script

Open
#1 0 comments 0 reactions 1 assignee Claimed by @seankwilliams View on GitHub
Dominant language
JavaScript
Stars
1
Forks
0
PR merge metrics
No merged PRs in 30d

Description

(copied from section 1 of the doc at https://drive.google.com/file/d/1XdzNaD9XJyMOimvEjzkN1DNjUINdj4p5/view)

We will generate the initial data based on a replication of the MusicBrainz database, filtered down by
only the artists that have plays in the ListenBrainz database.

MusicBrainz queries
To pull artists:
```sql
SELECT id, name FROM musicbrainz.artist
```

To pull albums:
```sql
SELECT artist_credit.name AS artist_name, release_group.name AS album_name,
(
SELECT release.id FROM release
INNER JOIN release_country ON release_country.release = release.id
INNER JOIN release_label ON release.id = release_label.release
INNER JOIN musicbrainz.label ON release_label.label = label.id
WHERE release.release_group = release_group.id
ORDER BY date_year, date_month, date_day
LIMIT 1) AS first_release_id,
(
SELECT label.name FROM musicbrainz.release
INNER JOIN release_country ON release_country.release = release.id
INNER JOIN release_label ON release.id = release_label.release
INNER JOIN musicbrainz.label ON release_label.label = label.id
WHERE release.release_group = release_group.id
ORDER BY date_year, date_month, date_day
LIMIT 1) AS label_name
FROM release_group
INNER JOIN artist_credit ON artist_credit.id = release_group.artist_credit
```
Notes: this only filters the earliest version of each release so that we don’t get any rereleases of albums.
Future improvements could include getting the exact label that released the album in the Comrad
instance’s respective country. Consider filtering this on only albums that have a label.
Various artists will return w/an artist name of “Various Artists”

Query to pull tracks. Recommended to filter this by only the albums/releases from the query above:
```sql
SELECT track.length / 1000 AS duration_in_seconds,
track.position AS track_number,
medium.position AS disk_number,
track.id, track.name,
artist_credit.name AS artists,
artist_count
FROM track
INNER JOIN medium ON medium.id = track.medium
INNER JOIN artist_credit ON artist_credit.id = track.artist_credit
WHERE
medium.release = 6892 --The Wall
--track.id = 7289624 --Believe by Q-Tip, D`Angelo
--track.id = 21432741 --Kill the Lights by Aelx Newell, Nile ROdgers, Jess Glynne
ORDER BY track.medium
```
Additional Steps
Some de-duping may be necessary in tracks, as some tracks appear on multiple albums. Check with
Beatles songs as those have 10k occurrences in the MusicBrainz database.
For KGNU, we will also want to supplement this data with the current data they have in Comrad.
We will want to include the source of all of this data in the database so we can only include Comrad data
in the data dump for KGNU.
The number of plays from the ListenBrainz database and number of plays from KGNU should be
provided for each track, and aggregated for each album and artist from the track data. The number of
plays should be converted into a popularity index that will range from 0-100, with 100 being the most
plays.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.