User:RobertKaye/Schema Change Release May 2012
From MusicBrainz Wiki
Jump to navigationJump to search
Tickets under consideration for May 15 2012
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 );
NOTE: This ticket will have the schema change made to the DB, but the actual features will be implemented after the May 15th release.
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; CREATE TABLE label_alias_type ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL ); ALTER TABLE label_alias ADD COLUMN type INTEGER; ALTER TABLE label_alias ADD COLUMN sort_name INTEGER NOT NULL; ALTER TABLE label_alias ADD COLUMN begin_date_year SMALLINT; ALTER TABLE label_alias ADD COLUMN begin_date_month SMALLINT; ALTER TABLE label_alias ADD COLUMN begin_date_day SMALLINT; ALTER TABLE label_alias ADD COLUMN end_date_year SMALLINT; ALTER TABLE label_alias ADD COLUMN end_date_month SMALLINT; ALTER TABLE label_alias ADD COLUMN end_date_day SMALLINT; ALTER TABLE label_alias ADD COLUMN primary_for_locale BOOLEAN NOT NULL DEFAULT false; ALTER TABLE label_alias ADD CONSTRAINT primary_check CHECK (locale IS NULL AND primary_for_locale IS FALSE); DROP INDEX label_alias_idx_locale_label; CREATE UNIQUE INDEX label_alias_idx_primary ON label_alias (label, locale) WHERE primary_for_locale = TRUE AND locale IS NOT NULL; CREATE TABLE work_alias_type ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL ); ALTER TABLE work_alias ADD COLUMN type INTEGER; ALTER TABLE work_alias ADD COLUMN sort_name INTEGER NOT NULL; ALTER TABLE work_alias ADD COLUMN begin_date_year SMALLINT; ALTER TABLE work_alias ADD COLUMN begin_date_month SMALLINT; ALTER TABLE work_alias ADD COLUMN begin_date_day SMALLINT; ALTER TABLE work_alias ADD COLUMN end_date_year SMALLINT; ALTER TABLE work_alias ADD COLUMN end_date_month SMALLINT; ALTER TABLE work_alias ADD COLUMN end_date_day SMALLINT; ALTER TABLE work_alias ADD COLUMN primary_for_locale BOOLEAN NOT NULL DEFAULT false; ALTER TABLE work_alias ADD CONSTRAINT primary_check CHECK (locale IS NULL AND primary_for_locale IS FALSE); DROP INDEX work_alias_idx_locale_work; CREATE UNIQUE INDEX work_alias_idx_primary ON work_alias (work, locale) WHERE primary_for_locale = TRUE AND locale IS NOT NULL;
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;
- The above accurately describes the database changes, but the UPDATE command would run a very long time. We will implement this differently in the schema upgrade scripts.
- Yes, and it will make major changes to a replicated table, so this change requires a lot of careful thought.
http://tickets.musicbrainz.org/browse/MBS-3646
Database Changes
CREATE TABLE release_group_secondary_type ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE release_group_secondary_type_join ( release_group INTEGER NOT NULL REFERENCES release_group (id), content_type INTEGER NOT NULL REFERENCES release_group_secondary_type (id), PRIMARY KEY (release_group, content_type) ); ALTER TABLE release_group_type RENAME to release_group_primary_type
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 pieces of work:
- 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.