User:RobertKaye/Schema Change Release May 2012: Difference between revisions
From MusicBrainz Wiki
Jump to navigationJump to search
Line 37: | Line 37: | ||
); |
); |
||
</pre> |
</pre> |
||
* <del>''TODO'': Does this belong in the <tt>musicbrainz</tt> schema, or do we introduce a <tt>statistics</tt> schema?</del> |
* <del>''TODO'': Does this belong in the <tt>musicbrainz</tt> schema, or do we introduce a <tt>statistics</tt> schema?</del> |
||
** <del> The proper course of action is to introduce the new statistics schema and move all statistics tables there. Lets not create a new schema with some stuff in it, but not all statistics. But, I dont see this as necessary for this release.</del> |
** <del> The proper course of action is to introduce the new statistics schema and move all statistics tables there. Lets not create a new schema with some stuff in it, but not all statistics. But, I dont see this as necessary for this release.</del> |
||
* (discussed on irc, just put this in the musicbrainz schema for now) |
|||
==== http://tickets.musicbrainz.org/browse/MBS-2532 ==== |
==== http://tickets.musicbrainz.org/browse/MBS-2532 ==== |
Revision as of 13:48, 2 April 2012
Tickets under consideration for May 15 2012
Conflict tickets
This ticket is a lot of work and may conflict with a Summer of Code proposal:
I'm unsure how to proceed with this ticket.
Clearly stated tickets
These tickets appear to be cleanly stated and nearly actionable:
http://tickets.musicbrainz.org/browse/MBS-2885
Database changes
CREATE TABLE iswc ( id SERIAL NOT NULL PRIMARY KEY, work INTEGER NOT NULL REFERENCES work (id), iswc CHARACTER(15) CHECK (iswc ~ '^T-?\d{3}.?\d{3}.?\d{3}[-.]?\d$'::text), source SMALLINT, edits_pending INTEGER NOT NULL DEFAULT 0, created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now() ); ALTER TABLE work DROP COLUMN iswc;
http://tickets.musicbrainz.org/browse/MBS-2851
Database changes
CREATE TABLE timeline_event ( event_date DATE PRIMARY KEY, event TEXT NOT NULL, description TEXT NOT NULL, link TEXT NOT NULL );
TODO: Does this belong in the musicbrainz schema, or do we introduce a statistics schema?The proper course of action is to introduce the new statistics schema and move all statistics tables there. Lets not create a new schema with some stuff in it, but not all statistics. But, I dont see this as necessary for this release.
- (discussed on irc, just put this in the musicbrainz schema for now)
http://tickets.musicbrainz.org/browse/MBS-2532
Database changes
CREATE TABLE artist_ipi ( artist INTEGER NOT NULL REFERENCES artist (id), ipi VARCHAR(11) NOT NULL, edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >= 0), created TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE TABLE label_ipi ( label INTEGER NOT NULL REFERENCES label (id), ipi VARCHAR(11) NOT NULL, edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >= 0), created TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); ALTER TABLE artist DROP COLUMN ipi_code; ALTER TABLE label DROP COLUMN ipi_code;
http://tickets.musicbrainz.org/browse/MBS-3788
Database changes
The changes just for artists are:
CREATE TABLE artist_alias_type ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL ); ALTER TABLE artist_alias ADD COLUMN type INTEGER; ALTER TABLE artist_alias ADD COLUMN sort_name INTEGER NOT NULL; ALTER TABLE artist_alias ADD COLUMN begin_date_year SMALLINT; ALTER TABLE artist_alias ADD COLUMN begin_date_month SMALLINT; ALTER TABLE artist_alias ADD COLUMN begin_date_day SMALLINT; ALTER TABLE artist_alias ADD COLUMN end_date_year SMALLINT; ALTER TABLE artist_alias ADD COLUMN end_date_month SMALLINT; ALTER TABLE artist_alias ADD COLUMN end_date_day SMALLINT; ALTER TABLE artist_alias ADD COLUMN primary_for_locale BOOLEAN NOT NULL DEFAULT false; ALTER TABLE artist_alias ADD CONSTRAINT primary_check CHECK (locale IS NULL AND primary_for_locale IS FALSE); DROP INDEX artist_alias_idx_locale_artist; CREATE UNIQUE INDEX artist_alias_idx_primary ON artist_alias (artist, locale) WHERE primary_for_locale = TRUE AND locale IS NOT NULL;
- TODO: Decide on whether we need separate type tables for each entity, or if they can share a type table
http://tickets.musicbrainz.org/browse/MBS-842
Database Changes
ALTER TABLE track ADD COLUMN number VARCHAR(255); UPDATE track SET number = position; ALTER TABLE track ALTER COLUMN number SET NOT NULL;
http://tickets.musicbrainz.org/browse/MBS-3646
Database Changes
CREATE TABLE content_type ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE release_group_content_type ( release_group INTEGER NOT NULL REFERENCES release_group (id), content_type INTEGER NOT NULL REFERENCES content_type (id), PRIMARY KEY (release_group, content_type) );
- TODO: Is content_type really the correct name?
- I'd say content_type should replace the existing release_group_type table, and get that name. --warp
http://tickets.musicbrainz.org/browse/MBS-1799
Database Changes
ALTER TABLE language ADD COLUMN iso_code_3 CHAR(3) NOT NULL; ALTER TABLE language RENAME COLUMN iso_code_2 TO iso_code_1; ALTER TABLE language RENAME COLUMN iso_code_3b TO iso_code_2b; ALTER TABLE language RENAME COLUMN iso_code_3t TO iso_code_2t;
http://tickets.musicbrainz.org/browse/MBS-1798
Database Changes
ALTER TABLE work ADD COLUMN language INTEGER; ALTER TABLE work ADD CONSTRAINT work_fk_language FOREIGN KEY (language) REFERENCES language (id);
http://tickets.musicbrainz.org/browse/MBS-1385
Database Changes
ALTER TABLE link ADD COLUMN ended BOOLEAN NOT NULL DEFAULT FALSE; ALTER TABLE artist ADD COLUMN ended BOOLEAN NOT NULL DEFAULT FALSE; ALTER TABLE label ADD COLUMN ended BOOLEAN NOT NULL DEFAULT FALSE;
http://tickets.musicbrainz.org/browse/MBS-4337
Database Changes
ALTER TABLE editor ADD COLUMN birth_year SMALLINT; ALTER TABLE editor ADD COLUMN gender INTEGER; ALTER TABLE editor ADD COLUMN country INTEGER; ALTER TABLE editor ADD CONSTRAINT editor_fk_gender FOREIGN KEY (gender) REFERENCES gender (id); ALTER TABLE editor ADD CONSTRAINT editor_fk_country FOREIGN KEY (country) REFERENCES country (id); CREATE TYPE FLUENCY AS ENUM ('basic', 'intermediate', 'advanced', 'native'); CREATE TABLE editor_language ( editor INTEGER NOT NULL REFERENCES editor (id), language INTEGER NOT NULL REFERENCES language (id), fluency FLUENCY NOT NULL, PRIMARY KEY (editor, language) ); CREATE INDEX editor_language_idx_language ON editor_language (language);
Next Steps
For each of these tickets, we will require the following two pieces of work:
- Oliver and Warp will review these tickets and sanity check the proposed schema changes. Due date: 29 March
- Specify exactly what the user interface changes will be. Mock ups would be great. Due date: 2 April
- Discuss what to do about the upgrade replication packet. As at least one bug requires updating an entire table, this will produce a very large packet.
Tickets that do not provide the required information by the given due date will be removed from the 2012-05-15 schema change.