Recording Release date in Database dump

MusicBrainz Development
,
1

I am trying to identify the release date of a recording from the database dump. I see a materialized view recording_first_release_date in the schema that links recording id and release date but db dump in the ftp doesn’t have an export of that. Is there a way we can extract the recording id and release date? Thanks.

1 Like
2

Hi @muthumusic,

recording_first_release_date is derived from other tables, so we don’t provide dumps for it, but do provide a script to build it.

If you’re using musicbrainz-docker, you can follow the instructions here: GitHub - metabrainz/musicbrainz-docker: Docker Compose project for the MusicBrainz Server with replication, search, and development setup (but replace all at the end of the command with recording_first_release_date, to just build that table).

If you only have the database and don’t have any MusicBrainz Server installation, you can still build the table by connecting to the database with psql and executing the following statement:

INSERT INTO recording_first_release_date SELECT * FROM get_recording_first_release_date_rows(‘TRUE’);

Note that the info in recording_first_release_date is derived from release events, so its accuracy depends on what release events have been entered into the database (and recordings being merged/associated with releases correctly).

Hope that helps.

4 Likes
3

Thank you for the response. I only have the database dump files and not the entire database. Is there a way i can join the existing table files to retrieve the release date of a recording? I went through github logs and i noticed this SQL. Is that the right SQL to get the release dates associated with all recordings? If yes, i can get the first release date from it.

SELECT track.recording,
       release.id AS release,
       date_year AS year,
       date_month AS month,
       date_day AS day
  FROM track
  JOIN medium ON (medium.id = track.medium)
  JOIN release ON (release.id = medium.release)
  LEFT JOIN (
      SELECT release, date_year, date_month, date_day
        FROM release_country
       UNION
      SELECT release, date_year, date_month, date_day
        FROM release_unknown_country
  ) release_dates
    ON release_dates.release = release.id;
1 Like
4

That query will give you all release dates for all recordings, not just the earliest ones. You’ll have to sort them. Here’s what the server uses if the recording_first_release_date table is empty:

SELECT DISTINCT ON (track.recording) track.recording,
    rd.date_year AS year,
    rd.date_month AS month,
    rd.date_day AS day
FROM track
JOIN medium ON medium.id = track.medium
LEFT JOIN (
    SELECT release, date_year, date_month, date_day FROM release_country
    UNION ALL
    SELECT release, date_year, date_month, date_day FROM release_unknown_country
) rd ON rd.release = medium.release
WHERE track.recording = ?
ORDER BY track.recording,
    rd.date_year NULLS LAST,
    rd.date_month NULLS LAST,
    rd.date_day NULLS LAST;

Replace WHERE track.recording = ? with whatever condition you’re using.

3 Likes
5

Thank you so much, that really helps.

2 Likes