User:RobertKaye/Schema Change Release May 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:

• http://tickets.musicbrainz.org/browse/MBS-799

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,
    work           INTEGER,
    iswc           CHARACTER(15),
    source         SMALLINT,
    edits_pending  INTEGER,
    created        TIMESTAMP WITH TIME ZONE
);
ALTER TABLE work DROP COLUMN iswc;

• TODO: Clarify constraints, clarify primary key

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.

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?

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.