Schema change release, 2013-05-15

Today we released a schema change update for MusicBrainz. Schema change updates change the format of the underlying MusicBrainz database and allow us to store more information, or model information in a richer/more correct form.

Summary

This release specifically includes some exciting new features:

Areas

A new entity is this release is the “area” entity, which can track countries, subdivisions of countries, cities, and other such location entities (venues, however, will be another entity). While this release primarily only introduces the entity, migrating only our existing list of countries, it’s now also possible to add start and end locations to artists, and to mark works as anthems. Editing of areas, their aliases and annotations, and area-area and area-url relationships, is limited to a new class of “Location Editor”.

ISNI Codes

The International Standard Name Identifier (ISNI) is an ISO standard that identifies public identities of parties. We now have support for storing ISNI codes inside the MusicBrainz database, which will make it easier to cross-reference data in MusicBrainz with other databases.

Multiple Release Events

Releases can now have multiple date and country pairs, whereas previously they could only have one country and one date. This will allow us to more accurately store information about releases that occur in different areas at different dates, but are otherwise the same physical product.

Forthcoming Features

We also began work on the database support for some future MusicBrainz features:

Track MBIDs

All tracks on mediums now have unique identifiers. This will allow people to refer to a specific track in a release in a way that is more resilient to editing than just the track name or position. Currently we have database support for this, but track identifiers are not yet exposed in either the website or the web service.

Dynamic Work Attributes

Dynamic work attributes will let us introduce new attributes to describe works without schema changes

Free Text Relationship Attribute Credits

This feature will let editors specify an alternative name for relationship attributes to specifically exactly which model guitar was used in a recording, rather than the current vague “electric guitar” attribute. Support for this feature is now in the database (in the link_attribute_credit) table, but the UI to do this editing is still to be finished.

Support more formats in the Cover Art Archive

Uploading cover art to the cover art archive will soon support a few other image formats, starting with PNG.

Other Schema Changes

The remaining smaller schema changes are:

  • The wiki transclusion version mapping is now stored in the database, not a flat file.
  • The link_type.short_link_phrase was renamed to link_type.long_link_phrase.
  • The work.artist_credit column was dropped.
  • Collections can now have a description.

Upgrading

If you are currently running a slave database, then you will need to perform a few manual steps to upgrade to the new version:

  1. Take down the web server running MusicBrainz, if you’re running a web server.
  2. Turn off cron jobs if you are automatically updating the database via cron jobs.
  3. Make sure your REPLICATION_TYPE setting is RT_SLAVE
  4. Switch to the new code with git fetch origin followed by git checkout v-2013-05-15
  5. Run carton install --deployment to install any new perl modules.
  6. Run carton exec -Ilib -- ./upgrade.sh from the top of the source directory.
  7. Set DB_SCHEMA_SEQUENCE to 17 in lib/DBDefs.pm
  8. Turn cron jobs back on, if needed.
  9. Restart the MusicBrainz web server, if needed.

Please see the instructions in our more recent blog post instead of these instructions.

Release Notes

This release wouldn’t have possible without help from Alastair Porter, Michael Wiencek, Nicolás Tamargo or the rest of the MusicBrainz team – thank you all for your hard work! As we missed the previous release, there are a few other changes in this release. Here are the full release notes:

Bug

  • [MBS-4703] – Add Medium edit does not correctly display the auto-edit note
  • [MBS-5834] – Users’ votes page mistitled as “edits”
  • [MBS-5851] – Instruments are missing from the JSON webservice responses
  • [MBS-5979] – Automatic redirect to beta clear release editor seeding
  • [MBS-6015] – Edit Artist Credit edits affecting track ACs don’t appear in related release edit histories
  • [MBS-6129] – Relationship editor isn’t correctly parsing attributes
  • [MBS-6149] – Entity merges silently dropping aliases with locales
  • [MBS-6178] – URL page headers are completely inconsistent
  • [MBS-6196] – work/edit_form.tt includes artist credit docs
  • [MBS-6249] – Add Event button broken on add release with cdtoc

Improvement

  • [MBS-1346] – New Report: Artists with 0 subscribers
  • [MBS-2229] – Allow multiple release events per release
  • [MBS-3626] – Display license logos in the sidebar
  • [MBS-3669] – Merge dated and undated relationships
  • [MBS-4115] – Cover art archive: Support .png SQL changes
  • [MBS-4294] – Add a “description” field to collections (SQL/UI)
  • [MBS-4756] – Move the wiki transclusion index to the database
  • [MBS-4866] – URL autoselect: ameblo.jp -> has blog at
  • [MBS-4867] – Timeline graph rate-of-change graph should change its vertical scaling depending on where it’s zoomed
  • [MBS-4925] – Add country of birth and country of death to Artist (person)
  • [MBS-5528] – Change short_link_phrase to long_link_phrase
  • [MBS-5772] – Generate relationship documentation (semi-)automatically
  • [MBS-5848] – Instrument credits (SQL)
  • [MBS-6023] – Track MBID UI changes
  • [MBS-6141] – Add discography page URL matching for universal-music.co.jp, lantis.jp, jvcmusic.co.jp, wmg.jp, avexnet.jp and kingrecords.co.jp
  • [MBS-6142] – Prevent Wikipedia links from being added as discography page relationships
  • [MBS-6188] – Remove rating from work merge page
  • [MBS-6189] – Show work languages on ISWC page
  • [MBS-6190] – Artist credit diffs per word, join phrase per char

New Feature

  • [MBS-799] – Location, venue and event support
  • [MBS-1839] – Track MBID SQL changes
  • [MBS-2417] – Support multiple countries/regions on a single release
  • [MBS-3296] – Add dynamic attributes
  • [MBS-3985] – Support multiple artist countries
  • [MBS-5272] – Create daily and weekly “rollup” replication packets
  • [MBS-5302] – Store International Standard Name Identifier (ISNI, ISO 27729) for artists and labels
  • [MBS-5861] – Dynamic work attributes (SQL)

Task

  • [MBS-5314] – Drop the work.artist_credit column
  • [MBS-6133] – Ensure JPEG uploads still work through CAA
  • [MBS-6167] – Add iTunes links to the sidebar
  • [MBS-6170] – Add Bandcamp links to the sidebar

Sub-task

  • [MBS-4217] – Spotify relationship under the External links section
  • [MBS-5809] – SQL changes for MBS-4294: Add a “description” field to collections
  • [MBS-5917] – transclusion to DB: SQL
  • [MBS-5918] – transclusion to DB: UI
  • [MBS-5919] – locations: SQL
  • [MBS-5920] – locations: UI
  • [MBS-5929] – Schema changes to store relationship type guidelines and example usages in the database
  • [MBS-5930] – Generate documentation automatically/allow managing guidelines in DB
  • [MBS-5933] – Schema changes to support multiple (date, country) pairs on releases
  • [MBS-6187] – UI changes to support multiple country/date pairs on releases

Schema change release tomorrow 15 May, 1300UTC

Things have been quiet at MusicBrainz in the past few weeks, but don’t let this fool you! We’ve been working hard on getting the schema change release put together. We’re on schedule for releasing this tomorrow at 13:00 UTC, 15 May, 2013.

We’re going to start the release process at 1300UTC, but the site may not go down just yet then. We’ll get started once we have all of our ducks in a row — to get more updates from us before we start, please follow @musicbrainz on Twitter or join us in IRC at #musicbrainz on irc.freenode.net.

Thanks, and wish us luck for a smooth schema change tomorrow!

Official schema change notification for 15 May, 2013

We’re nearly done implementing the SQL portions of our tickets for the upcoming schema change on 15 May, 2013. We’ve settled on the following tickets that we plan to release:

  • MBS-5861: Dynamic work attributes
  • MBS-3978: Support more than one barcode on same release
  • MBS-4756: Move the wiki transclusion index to the database
  • MBS-799: Location, venue and event support
  • MBS-3985: Support multiple artist countries
  • MBS-4925: Add country of birth and country of death to Artist (person)
  • MBS-4115: Cover art archive: Support .png SQL changes
  • MBS-1839: Track MBID SQL changes
  • MBS-5809: Add a “description” field to collections
  • MBS-5314: Drop the work.artist_credit column
  • MBS-5302: Store International Standard Name Identifier (ISNI, ISO 27729) for artists and labels
  • MBS-5528: Change short_link_phrase to long_link_phrase
  • MBS-2229: Allow multiple release events per release
  • MBS-2417: Support multiple countries/regions on a single release
  • MBS-5772: Generate relationship documentation (semi-)automatically
  • MBS-5848: Instrument credits

Each of the tickets above will give you a complete idea of how we plan to change our schema on May 15th. Questions? Post a question in the comments and we will answer it.

Finally, we are going to require that Postgres 9.1 will be the minimum version of Postgres going forward. I’ve spoken to many people about this and it seems that a large percentage of people are already using Postgres 9.1, so this should not be a major change.

Thanks!

Preparing for the May 15th schema change release

It it time for us to start the process towards the next schema change release. Starting today and for the next two weeks, we’re going to seek people to be the champion (sponsor) of a ticket. If you feel strongly about a schema change ticket getting taken care of, you should consider championing this ticket. Once you’ve decided to do adopt a ticket, you should assign the ticket to yourself.

Then, over the next two weeks it will be up to you to do the following:

  1. Drive consensus around the core concept of the ticket. If you go through the process of working up a ticket, but no one agrees with what you’re proposing, you’ve wasted your time. Make sure that you get buy in from others in the community. For instance, if Nikki doesn’t like it, chances are its not going to fly. 🙂
  2. Each schema change feature requires two tickets: 1) An SQL ticket that implements the actual changes to the database and defines the queries used to fetch the data. 2) A UI change ticket that implements the UI portions of the schema change ticket.
  3. Ensure that the ticket clearly states what needs to be done to implement the ticket. The ticket should essentially become or link to a requirements document. This requirements document should explain what the new feature should do. It should not explain how it should be done — we should leave the how to our developers who are going to implement the feature.
  4. Provide as much supporting documentation as you can. Mock-ups for UIs are deeply appreciated (even if they delve into the how realm of things) and very useful for meaningfully discussing these tickets.
  5. Have the ticket reviewed by a developer for clarity and completeness, then address any issues said developer may raise.

On 15 February, we’re going to look at the list of tickets that people have taken on and choose the ones that are clear enough to move forward. If you’ve done all the work outlined above, the chances are good that your ticket will be chosen to move forward. If your ticket is chosen to move forward, there will be more questions that the developers will raise — hopefully those can be tackled in the space of a week. After that we will take all of the well defined tickets and schedule them for implementation. All the other tickets that are not clear to implement will be rejected and will have to make another pass though this process in the autumn.

If you’re still interested, here is the list of schema change tickets that should be considered for this.

We’re going to follow the this schedule:

  • 1 Feb: Schema change ticket selection starts
  • 15 Feb: Select schema change tickets for implementation, start making tickets fully actionable
  • 1 March: Tickets must be fully actionable. Tickets that are not actionable will be dropped from the 15 May release.
  • 15 March: SQL tickets must be fully implemented.
  • 1 May: UI tickets must be fully implemented, start final ticket testing phase
  • 15 May: Release day

All of these dates have been added to our new community calendar.

Updating MusicBrainz slave instances for 2012-10-15

If you have a replicated instance of MusicBrainz, please follow these instructions to get your server running on the new schema:

  1. Take down the web server running MusicBrainz, if you’re running a web server.
  2. Turn off cron jobs if you are automatically updating the database via cron jobs.
  3. Make sure your REPLICATION_TYPE setting is RT_SLAVE
  4. Switch to the new code with git fetch origin followed by git checkout v-2012-10-15-schema-change
  5. Run carton install --deployment to install any new perl modules.
  6. Run carton exec -- ./upgrade.sh from the top of the source directory.
  7. Set DB_SCHEMA_SEQUENCE to 16 in lib/DBDefs.pm
  8. Turn cron jobs back on, if needed.
  9. Restart the MusicBrainz web server, if needed.

This upgrade requires quite a bit of disk-space to execute; your slave may run into trouble if there is less than 10Gb of disk space free. If you’re on a disk space constrained machine, you may want to consider re-importing the data rather than upgrading in place. The next data dump should be available in about 14-16 hours from now.

Schema change release 2012-10-15

We just finished the latest schema change release. Please stay tuned for instructions on how to upgrade your MusicBrainz slave instances.

This release features work from Nikki, Navap, Bitmap, Freso and the MusicBrainz developers. Thanks for your work everyone! The following changes were included in this release:

Bug

  • [MBS-240] – Attached files not downloading properly in transcluded page
  • [MBS-3610] – Ratings : 502 Bad Gateway
  • [MBS-3853] – Annotation history page allows the same version to be selected for both Old and New
  • [MBS-3963] – Edit search "artist in my subscription" should hide the artist input field
  • [MBS-4033] – Offered and installed search plugin name mismatch
  • [MBS-4206] – Whitespace isn’t removed from the last join phrase
  • [MBS-4220] – Artists shown to last 0 days if begin year = end year
  • [MBS-4268] – "{age} ago" does not work for all languages
  • [MBS-4747] – Wrong diff or wrong edit allowed for an URL
  • [MBS-4812] – Empty "Remove release label" edit was entered
  • [MBS-4929] – Some fields on user’s "Open edits" page are duplicating while merging works
  • [MBS-4978] – Release merges with strategy "Merge mediums and recordings" don’t show up on the affected recordings’ open edits list.
  • [MBS-5047] – Changing secondary RG types is an autoedit for all users
  • [MBS-5051] – Empty artists with pending edits still get the « will be removed automatically » warning
  • [MBS-5090] – Some entities are marked as having pending edits when they don’t
  • [MBS-5177] – Editors can edit after removing their email address
  • [MBS-5253] – Table release_group_secondary_type_join misses a created field
  • [MBS-5264] – Track Parser results in edits changing durations on tracks in mediums with discids in some cases
  • [MBS-5306] – Internal server error when searching for edits by artist country and relationship type
  • [MBS-5321] – Some relationship editor tests are failing.
  • [MBS-5329] – Relationship editor breaks when using translations
  • [MBS-5330] – Medium formats untranslated on release pages and in release editor
  • [MBS-5331] – Edit note label is cut off when translated
  • [MBS-5332] – Last updated timestamp is missing on some details pages
  • [MBS-5333] – cover art types translate incorrectly
  • [MBS-5336] – Relationships get double-translated
  • [MBS-5342] – .pot files are generated with the ‘perl-format’ po flag when they are not being passed to sprintf
  • [MBS-5344] – Edits display page, for an add cover art edit, for a removed release, has invalid link
  • [MBS-5346] – TuneCore blocking blocks valid ISRCs
  • [MBS-5357] – Translations creeping into edit info
  • [MBS-5368] – Changing the link type in the relationship editor causes the dialog to jump
  • [MBS-5372] – AR editor: paste should detect entity type
  • [MBS-5377] – MediaWiki upgrade broke WikiDoc redirects
  • [MBS-5382] – AR editor: "part of" ARs are displayed backwards
  • [MBS-5384] – Relationship editor: track artists only shown when AC has different number of artists
  • [MBS-5385] – Relationship editor shouldn’t merge relationships with different dates
  • [MBS-5390] – Relationship editor isn’t loading related works for some recordings
  • [MBS-5391] – Relationship editor doesn’t support the "ended" checkbox
  • [MBS-5392] – Relationship editor should display dates consistent with the rest of the site
  • [MBS-5396] – relationship editor changes to MB.utility.keys cause it to die on null
  • [MBS-5406] – "Subscriptions" editor tab shows 3 empty <li> inside <ul> between « jesus2099 is subscribed to: » and p.pageselector
  • [MBS-5412] – Some statistics tabs have partially-untranslated headers
  • [MBS-5413] – e.g. "Artist-Artist" and "Artist-Artist relationships" not translating correctly on /statistics/relationships
  • [MBS-5414] – Javascript tests no longer run in older versions of Opera
  • [MBS-5415] – "Types:" (under cover art) should often be in singular
  • [MBS-5417] – WikiDocs: Prevent access to File and Image namespace
  • [MBS-5419] – Chrome frame wasn’t removed completely/properly
  • [MBS-5425] – Some ModBot edit notes end up stored translated

Improvement

  • [MBS-1226] – Address verification email should include the client’s IP address
  • [MBS-1530] – More space/sections on the add disc id page
  • [MBS-1789] – No way to compare discid track length against tracklist length
  • [MBS-2449] – Search page should say when it was last updated
  • [MBS-2492] – Don’t show quality levels in edit documentation where they don’t apply
  • [MBS-2543] – Make the dash at the end of /cdtoc/ and /cdstub/ URLs optional
  • [MBS-2650] – After adding a relationship, go to the overview tab
  • [MBS-2717] – User’s ratings page should be paginated
  • [MBS-3606] – Matched CD TOC page should have a link to the disc id
  • [MBS-3791] – Drop Description from URLs
  • [MBS-4104] – Show release merges under all track/recording artists
  • [MBS-4405] – Report: Artists that may be persons shoudn’t show obvious groups
  • [MBS-4690] – Include links to all image sizes
  • [MBS-5099] – Make release/work language clickable
  • [MBS-5236] – Add stats for work types
  • [MBS-5279] – Try to avoid duplicating links in the sidebar
  • [MBS-5285] – Add NOT NULL constraints to disambiguation & join phrase column
  • [MBS-5290] – CAA replication
  • [MBS-5350] – Add a way to check # of open edits and edits entered in the last day
  • [MBS-5352] – /doc/Edit_Types/38 and similar should link to the documentation.
  • [MBS-5362] – Add cleanup and autoselect for iTunes links
  • [MBS-5364] – Ask the user to confirm navigation away from the relationship editor (mainly for backspace)
  • [MBS-5365] – In the relationship editor, pressing enter while focused in the entity lookup should add the relationship
  • [MBS-5366] – Make it easier to reuse artists in the relationship editor
  • [MBS-5371] – Don’t show ModBot as a limited user
  • [MBS-5375] – Remove whitespace above doctype declaration
  • [MBS-5376] – Add artist credit description
  • [MBS-5404] – Improve the relationship editor’s multiselect plugin

New Feature

  • [MBS-5320] – All data dumps should be signed by MusicBrainz
  • [MBS-5324] – Relationship editor should allow using auto-editor privileges
  • [MBS-5363] – Allow adding/editing release group relationships in the relationship editor

Task

  • [MBS-5182] – Add final controlled_for_whitespace checks
  • [MBS-5289] – Finish/merge log statistic features
  • [MBS-5291] – Move existing timeline stats into its own schema
  • [MBS-5316] – Remove PGOPTIONS/search_path from ./admin/psql
  • [MBS-5354] – Remove drastically out-of-sequence cover art images
  • [MBS-5355] – Remove depedency on Function::Parameters
  • [MBS-5374] – Commit MB logo into repo

The Git commit SHA for this release is 5bd71d211dceb25a7a0d545a14cb3273e3e89f19, tag is v-2012-10-15-schema-change.

Schema change release: Oct 15th

On October 15th, we’re going to update our schema once again. This time we’re only making minor changes and some cleanup from the last schema change. To find out exactly what will be changing, please take a look at our milestone for this release.

At this point we’ve frozen the list of tickets we’re going to adress for October 15th. We’re not going to accept any more schema change tickets for this release.

Please let us know if you have any questions!

Post schema change fix for importing clean data

Yesterday we found a bug that prevents the import of a post schema change update data set. We’ve pushed out a fix for this and tagged it with:

v-2012-05-15-import-fix

If you’re planning on importing a new data set, make sure to check out this tag, rather than the tag mentioned in this entry.