-- Classical Music Database DDL (MySQL)
-- This DDL creates a database for mapping classical music, including
-- composers, works, artists, performances, text, instrumentation etc.
-- It is originally an exercise and attempts to be complete, that is,
-- a relevant and accurate description of the classical music world.


-- Global matter
-- ----------------------------------------------------------------------------

-- ISO language codes
-- e.g. DE (german), EN-AU (australian english), ANG (old english)
CREATE TABLE IF NOT EXISTS languages (
    language     CHAR(6), -- ISO 639 - 2 letter (3 if needed, plus opt. hyphen country)
    language_en  VARCHAR(50), -- Name in english (including age variants)
    language_native VARCHAR(50) CHARACTER SET utf8, -- Name in native language
    PRIMARY KEY (language)
) TYPE = InnoDB;

-- ISO 3166-2 country codes
-- Outdated countries (eg Prussia) cannot be represented (did ISO forget about them?)
CREATE TABLE IF NOT EXISTS countries (
    country     CHAR(2) NOT NULL, -- ISO 2 character representation of country
    country_en  VARCHAR(80) NOT NULL default '', -- English translation of country
    iso3        CHAR(3), -- ISO 3 character representation of country
    numcode     SMALLINT(6), -- numerical representation of country -- ?
    PRIMARY KEY (country)
) TYPE = InnoDB;


-- Artists
-- ---------------------------------------------------------------------------

-- Artists
-- Any person or group that contributes or performs a work
CREATE TABLE IF NOT EXISTS artists (
   artist_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
   primary_role    ENUM('composer','performer','poet'), -- more to come
   recognition     ENUM('well-known','known','obscure') DEFAULT 'obscure', -- 3 is a good number
   information     TEXT,
   -- What sort of information should we have?
   -- Biography? style analysis? Wikipedia link?
   PRIMARY KEY (artist_id)
) TYPE = InnoDB;

-- Group types
-- Orchestra, Choir etc etc
CREATE TABLE IF NOT EXISTS group_types (
  group_type       VARCHAR(50) NOT NULL,
  PRIMARY KEY (group_type)
) TYPE = InnoDB;

-- Groups: Those arists that are groups
CREATE TABLE IF NOT EXISTS groups (
   artist_id      INT UNSIGNED NOT NULL, -- must have an artist id
   name           VARCHAR(100) CHARACTER SET utf8, -- name of group
   shortname      VARCHAR(20) CHARACTER SET utf8, -- quick display of name
   established    DATE,
   nationality    CHAR(6),
   city  VARCHAR(50) CHARACTER SET utf8, -- city where the group is based
   group_type     VARCHAR(50),
   PRIMARY KEY (artist_id),
   FOREIGN KEY (artist_id)
     REFERENCES artists (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
   INDEX (group_type),
   FOREIGN KEY (group_type)
     REFERENCES group_types (group_type)
     ON UPDATE CASCADE
     ON DELETE CASCADE
) TYPE = InnoDB;

-- People: Those artists that are people
-- Although there is a 'additional_name' entity, primary names are stored here.
-- Every person really needs to have a name, and most people will only have one.
-- There is no need to complicate things for the sake of a few Russian composers
-- and a couple of others that have taken aliases. 
-- For category explanation see the 'additional_names' table.

CREATE TABLE IF NOT EXISTS persons (
   artist_id       INT UNSIGNED NOT NULL, -- must have an artist id
   nationality     CHAR(2), -- ISO 3166 Country code
   date_of_birth   DATE,
   date_of_death   DATE,
   dates_caveat    SET('circa', 'floruit'), -- c. and fl.
   gender          ENUM('male','female'),
   firstname       VARCHAR(50) CHARACTER SET utf8,
   surname         VARCHAR(50) CHARACTER SET utf8,
   name_language   CHAR(6), -- Language of the name (eg. Händel is German)
   name_category   ENUM('original','alias','translation') DEFAULT 'original',
   INDEX (surname), -- why not? There is a view that searches by surname
   PRIMARY KEY (artist_id),
   FOREIGN KEY (artist_id)
     REFERENCES artists (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
   INDEX (nationality),
   FOREIGN KEY (nationality)
     REFERENCES countries (country)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
  INDEX (name_language),
  FOREIGN KEY (name_language)
     REFERENCES languages (language)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) TYPE = InnoDB;

-- Additional Names (Stored in UTF8, similar to title)
-- Display
  -- 'primary'     NOT AVAILABLE: this is represented in the 'persons' table. Displayed all the time.
  -- 'secondary'   is displayed in the lists after 'primary', (in brackets, useful for aliases
  -- 'rarely'      only when full details are required (generally translations)
  -- 'never'       never actually display. Use this to link misspelt searches.
  --  NULL         implies 'rarely'
-- Category
  -- 'original'    The given name in the person's native language
  -- 'alias'       Any other name that the person might be known by
  -- 'translation' obvious, also includes various spellings (e.g. Russian->English)
CREATE TABLE IF NOT EXISTS additional_names (
  artist_id        INT UNSIGNED NOT NULL,
  firstname        VARCHAR(50) CHARACTER SET utf8,
  surname          VARCHAR(50) CHARACTER SET utf8,
  name_language    CHAR(6),
  name_display     ENUM('secondary','rarely','never') DEFAULT 'rarely', -- Primary is stored in 'persons' table
  name_category    ENUM('original','alias','translation') DEFAULT 'original',
  PRIMARY KEY (artist_id,firstname,surname),
  FOREIGN KEY (artist_id)
     REFERENCES persons (artist_id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  INDEX (name_language),
  FOREIGN KEY (name_language)
     REFERENCES languages (language)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) TYPE = InnoDB;

-- Extra information about artists that are contactable
-- Obviously this is a one to many, contact details are attached
-- to the artist one by one
CREATE TABLE IF NOT EXISTS contact_details (
   contact_detail_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   artist_id          INT UNSIGNED NOT NULL,
   contact_detail     VARCHAR(100) NOT NULL,
   contact_type       ENUM('phone number','mobile phone','email','address','postal address','website') NOT NULL,
   PRIMARY KEY (contact_detail_id),
   FOREIGN KEY (artist_id)
     REFERENCES artists (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE
) TYPE = InnoDB;

-- Relating persons to groups
-- 'leave_date': 
  -- NULL leave_date means person has not left. 
  -- If leave date is unknown, it should be set to: 0000-00-00
-- 'prinicpal': 
  -- true where the person plays a major role in the group
  -- i.e. a conductor, director, principal, scholar, accompanist etc
CREATE TABLE IF NOT EXISTS persons_groups (
   position_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   person_id    INT UNSIGNED NOT NULL,
   group_id     INT UNSIGNED NOT NULL,
   role         VARCHAR(20), -- Conductor, Violin etc (no multiple roles as yet)
   principal    BOOL, -- see above
   join_date    DATE DEFAULT '0000-00-00', 
   leave_date   DATE DEFAULT '0000-00-00', 
   PRIMARY KEY (position_id),
   INDEX (group_id),
   FOREIGN KEY (person_id)
     REFERENCES persons (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
   FOREIGN KEY (group_id)
     REFERENCES groups (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE
) TYPE = InnoDB;


-- Instruments
-- ---------------------------------------------------------------------------
-- Families of instruments
CREATE TABLE IF NOT EXISTS families (
   family     VARCHAR(50) NOT NULL, -- Family name: Saxophone, Woodwind, Voice
   PRIMARY KEY (family)
) TYPE = InnoDB;

-- The instruments
-- Ranges are simple: they could be a whole lot more comprehensive.
-- They might vary according to skill level and some may sound better
-- than others (i.e. the weaker notes). Maybe in the future
CREATE TABLE IF NOT EXISTS instruments (
   instrument_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name            VARCHAR(50), -- The english name for defaults
   -- abbreviation    VARCHAR(10), -- Standard abbreviation for the instrument
   transpose_key   SMALLINT DEFAULT 0, -- Number of semitones from concert pitch (negative allowed)
   -- Lowest note in concert pitch (professional standard) (not language neutral)
   range_bottom    ENUM("C0","C#0","D0","D#0","E0","F0","F#0","G0","G#0","A0","A#0","B0",
                        "C1","C#1","D1","D#1","E1","F1","F#1","G1","G#1","A1","A#1","B1",
                        "C2","C#2","D2","D#2","E2","F2","F#2","G2","G#2","A2","A#2","B2",
                        "C3","C#3","D3","D#3","E3","F3","F#3","G3","G#3","A3","A#3","B3",
                        "C4","C#4","D4","D#4","E4","F4","F#4","G4","G#4","A4","A#4","B4",
                        "C5","C#5","D5","D#5","E5","F5","F#5","G5","G#5","A5","A#5","B5",
                        "C6","C#6","D6","D#6","E6","F6","F#6","G6","G#6","A6","A#6","B6",
                        "C7","C#7","D7","D#7","E7","F7","F#7","G7","G#7","A7","A#7","B7",
                        "C8","C#8","D8","D#8","E8","F8","F#8","G8","G#8","A8","A#8","B8",
                        "C9","C#9","D9","D#9","E9","F9","F#9","G9","G#9","A9","A#9","B9"),
   -- Highest note in concert pitch (professional standard) (not language neutral)
   range_top       ENUM("C0","C#0","D0","D#0","E0","F0","F#0","G0","G#0","A0","A#0","B0",
                        "C1","C#1","D1","D#1","E1","F1","F#1","G1","G#1","A1","A#1","B1",
                        "C2","C#2","D2","D#2","E2","F2","F#2","G2","G#2","A2","A#2","B2",
                        "C3","C#3","D3","D#3","E3","F3","F#3","G3","G#3","A3","A#3","B3",
                        "C4","C#4","D4","D#4","E4","F4","F#4","G4","G#4","A4","A#4","B4",
                        "C5","C#5","D5","D#5","E5","F5","F#5","G5","G#5","A5","A#5","B5",
                        "C6","C#6","D6","D#6","E6","F6","F#6","G6","G#6","A6","A#6","B6",
                        "C7","C#7","D7","D#7","E7","F7","F#7","G7","G#7","A7","A#7","B7",
                        "C8","C#8","D8","D#8","E8","F8","F#8","G8","G#8","A8","A#8","B8",
                        "C9","C#9","D9","D#9","E9","F9","F#9","G9","G#9","A9","A#9","B9"),
   PRIMARY KEY (instrument_id)
) TYPE = InnoDB;

-- Linking instruments to their families
CREATE TABLE IF NOT EXISTS families_instruments (
   instrument_id   INT UNSIGNED NOT NULL,
   family       VARCHAR(50) NOT NULL,
   PRIMARY KEY (instrument_id, family),
   INDEX (family),
   FOREIGN KEY (instrument_id)
     REFERENCES instruments(instrument_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
   FOREIGN KEY (family)
     REFERENCES families(family)
     ON UPDATE CASCADE
     ON DELETE CASCADE
) TYPE = InnoDB;

-- Linking instruments to people
CREATE TABLE IF NOT EXISTS plays_instrument (
   artist_id       INT UNSIGNED NOT NULL,
   instrument_id   INT UNSIGNED NOT NULL,
   PRIMARY KEY (artist_id, instrument_id),
   INDEX (instrument_id),
   FOREIGN KEY (artist_id)
     REFERENCES persons (artist_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
   FOREIGN KEY (instrument_id)
     REFERENCES instruments(instrument_id)
     ON UPDATE CASCADE
     ON DELETE CASCADE
) TYPE = InnoDB;


-- Pictures
-- ---------------------------------------------------------------------------

-- Pictures of various things:
-- people, groups, instruments, locations, publications, occasion artwork, work
CREATE TABLE IF NOT EXISTS pictures (
   picture_id   INT UNSIGNED AUTO_INCREMENT NOT NULL,
   filename     VARCHAR(100), -- Filename
   tn_filename  VARCHAR(100),          -- Thumbnail filename
   caption      VARCHAR(200),          -- All photos need captions
   subject_date DATE,               -- date of photo/image
   PRIMARY KEY (picture_id)
) TYPE = InnoDB;

-- Linking pictures to artists
CREATE TABLE IF NOT EXISTS artist_picture (
   artist_id    INT UNSIGNED NOT NULL,
   picture_id   INT UNSIGNED NOT NULL,
   PRIMARY KEY (artist_id, picture_id),
   INDEX (picture_id),
   FOREIGN KEY (artist_id)
      REFERENCES artists (artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (picture_id)
      REFERENCES pictures (picture_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Linking pictures to instruments
CREATE TABLE IF NOT EXISTS instrument_picture (
   instrument_id    INT UNSIGNED NOT NULL,
   picture_id   INT UNSIGNED NOT NULL,
   PRIMARY KEY (instrument_id, picture_id),
   INDEX (picture_id),
   FOREIGN KEY (instrument_id)
      REFERENCES instruments (instrument_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (picture_id)
      REFERENCES pictures (picture_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;


-- Works
-- ---------------------------------------------------------------------------

-- For religious music
CREATE TABLE IF NOT EXISTS occasions (
   occasion_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   occasion    VARCHAR(50),
   PRIMARY KEY (occasion_id)
) TYPE = InnoDB;

CREATE TABLE IF NOT EXISTS churches (
   church_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   church     VARCHAR(50),
   PRIMARY KEY(church_id)
) TYPE = InnoDB;

CREATE TABLE IF NOT EXISTS text_types (
   text_type_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   text_type   VARCHAR(50),
   PRIMARY KEY (text_type_id)
) TYPE = InnoDB;

CREATE TABLE IF NOT EXISTS sacred_texts (
   text_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
   reference     VARCHAR(40),  -- e.g. "Matthew 12:14"
   language      CHAR(6), -- language of the text
   text_type_id  INT UNSIGNED, -- e.g. "psalm", "requiem", "mass"
   written_for_occasion   INT UNSIGNED, -- Particular feast of event
   suitable_for_occasion  INT UNSIGNED, -- TODO: This may not be necessary
   text       TEXT,
   PRIMARY KEY (text_id),
   FOREIGN KEY (language)              REFERENCES languages(language),
   FOREIGN KEY (text_type_id)             REFERENCES text_types(text_type_id),
   FOREIGN KEY (written_for_occasion)  REFERENCES occasions(occasion_id),
   FOREIGN KEY (suitable_for_occasion) REFERENCES occasions(occasion_id)
) TYPE = InnoDB;

-- which texts are suitable for which churches
CREATE TABLE IF NOT EXISTS churches_suitable_texts (
   text_id      INT UNSIGNED NOT NULL,
   church_id    INT UNSIGNED NOT NULL,
   PRIMARY KEY (church_id,text_id),
   FOREIGN KEY (church_id)             REFERENCES churches(church_id),
   FOREIGN KEY (text_id)               REFERENCES sacred_texts(text_id)
) TYPE = InnoDB;

-- To ensure uniformity and flexibility
CREATE TABLE IF NOT EXISTS instrumentations (
   short      VARCHAR(30), -- short custom reference: eg SATB
   detailed   TEXT, -- Detailed description: eg "4 part voice (Soprano, Alto, Tenor, Bass)"
   PRIMARY KEY (short)
) TYPE = InnoDB;

-- Why not?
-- See wikipedia article 'Category:Musical forms'
CREATE TABLE IF NOT EXISTS forms (
   form       VARCHAR(50),
   detailed   TEXT, -- A detailed description of the form
   wikilink   VARCHAR(80), -- The name of the relevant wikipedia page (link can be derrived)
   PRIMARY KEY (form)
) TYPE = InnoDB;

-- Auto import of instruments for an instrumentation. Searching for an
-- instrument shound bring up all the works with this instrumentation.
CREATE TABLE IF NOT EXISTS instrumentations_instruments (
   instrumentation  VARCHAR(50), -- short name of the instrumentation
   instrument       INT UNSIGNED NOT NULL, -- instrument
   PRIMARY KEY (instrumentation, instrument),
   FOREIGN KEY (instrumentation)
      REFERENCES instrumentations(short)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (instrument),
   FOREIGN KEY (instrument)
      REFERENCES instruments(instrument_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- The works
CREATE TABLE IF NOT EXISTS works (
   work_id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
   opus         VARCHAR(20),
   information  TEXT,
   central_key  ENUM("Ab","A","A#","Bb","B","B#",
                     "Cb","C","C#","Db","D","D#",
                     "Eb","E","E#","Fb","F","F#",
                     "Gb","G","G#", "atonal"), -- (not language neutral)
   central_mode ENUM("major","minor"), -- null is also available
   form         VARCHAR(50), -- sonata, etude, nocturne etc 
   instrumentation VARCHAR(50),
   date         DATE,
   PRIMARY KEY (work_id),
   INDEX (instrumentation),
   FOREIGN KEY (instrumentation)
      REFERENCES instrumentations(short)
      ON UPDATE CASCADE,
   INDEX (form),
   FOREIGN KEY (form)
      REFERENCES forms(form)
      ON UPDATE CASCADE
) TYPE = InnoDB;


-- Title (Stored in UTF8)
-- Display
  -- 'primary'     is the one that is displayed in lists etc
  -- 'secondary'   is displayed in the lists after 'primary', (in brackets)
  -- 'rarely'      only when full details are required
  -- 'never'       never actually display. Use this to link misspelt searches.
  --  NULL         implies 'rarely'
-- Category
  -- 'original'    The title given by the composer
  -- 'additional'  Common titles (eg Moonlight, Lodron)
  -- 'translation' obvious
CREATE TABLE IF NOT EXISTS work_titles (
   work_id      INT UNSIGNED NOT NULL, -- Which work this is for
   title        VARCHAR(100) CHARACTER SET utf8, -- The text of the title
   language     CHAR(6), -- Original language: ISO 639 Language code
   display      ENUM('primary','secondary','rarely','never') DEFAULT 'primary', -- see above
   category     ENUM('original','additional','translation') DEFAULT 'original', -- see above
   PRIMARY KEY (work_id, title),
   INDEX (language),
   FOREIGN KEY (language)
      REFERENCES languages (language)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (work_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Individual movements or parts (weak entity)
CREATE TABLE IF NOT EXISTS movements (
   work_id          INT UNSIGNED NOT NULL,
   movement_number  INT UNSIGNED, -- can be null
   duration         TIME,
   direction        VARCHAR(100), -- 'Allegro vivace' etc
   PRIMARY KEY (work_id, movement_number),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

 -- Movement Titles:
 -- Mostly just the direction may suffice, but also include a title
 -- here if you can (Sometimes the intro lyrics).
 -- Note that lyrics also go in 'text' table, but this one 
 -- is searchable, and rightly so ('Nessun dorma' etc)
 -- Include common translations: "Wer ist Sylvia?" "Who is Sylvia?"
CREATE TABLE IF NOT EXISTS movement_titles (
   work_id          INT UNSIGNED NOT NULL,
   movement_number  INT UNSIGNED,
   movement_title   VARCHAR(100) CHARACTER SET utf8, 
   language         CHAR(6),
   display          ENUM('primary','secondary','rarely','never') DEFAULT 'primary', -- see work_titles
   category         ENUM('original','additional','translation') DEFAULT 'original', -- see work_titles
   PRIMARY KEY (work_id,movement_number,movement_title),
   FOREIGN KEY (work_id,movement_number)
      REFERENCES movements(work_id,movement_number)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (language),
   FOREIGN KEY (language)
      REFERENCES languages(language)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Linking composers etc to works
-- Only one per work, per composer, but multiple contributions are allowed
CREATE TABLE IF NOT EXISTS create_work (
   work_id      INT UNSIGNED NOT NULL,
   artist_id    INT UNSIGNED NOT NULL,
   -- Allow multiple values
   -- access using: WHERE contribution LIKE '%Composer%'
   --           or: WHERE FIND_IN_SET('Composer',contribution) > 0
   contribution SET("composer", "arranger", "librettist",
                    "lyricist", "poet",     "author"),
   PRIMARY KEY (work_id, artist_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (artist_id),
   FOREIGN KEY (artist_id)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Linking works/movements to instruments
CREATE TABLE IF NOT EXISTS works_instruments (
   work_id         INT UNSIGNED NOT NULL,
   movement_number INT UNSIGNED,  -- null / zero for all movements
   instrument_id   INT UNSIGNED NOT NULL,
   role            ENUM('feature','solo'), -- Role of the instrument 
   PRIMARY KEY (work_id, movement_number, instrument_id),
   FOREIGN KEY (work_id, movement_number)
      REFERENCES movements(work_id, movement_number)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (instrument_id),
   FOREIGN KEY (instrument_id)
      REFERENCES instruments(instrument_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- MySQL allows 'text' as an unquoted idenifier
-- This supposedly references a weak entity
CREATE TABLE IF NOT EXISTS texts (
   work_id         INT UNSIGNED NOT NULL,
   movement_number INT UNSIGNED,
   text_source     VARCHAR(100), -- from a larger work eg Psalm 321 etc
   author          INT UNSIGNED, -- artist id
   language        CHAR(6),  -- language of the given text (ISO 639)
   original_lang   BOOL DEFAULT TRUE,      -- is this a translation?
   text            TEXT,
   PRIMARY KEY (work_id, movement_number, language),
   FOREIGN KEY (work_id, movement_number)
      REFERENCES movements(work_id, movement_number)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (language),
   FOREIGN KEY (language)
      REFERENCES languages(language)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (author),
   FOREIGN KEY (author)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- dates are rough - works really define periods, not dates
CREATE TABLE IF NOT EXISTS periods (
   period_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   period     VARCHAR(50), -- name of the period
   start_date DATE, -- rough start date
   end_date   DATE, -- rough end date
   PRIMARY KEY (period_id)
) TYPE = InnoDB;

-- Linking works and periods: 'of a' period
CREATE TABLE IF NOT EXISTS works_periods (
   work_id    INT UNSIGNED NOT NULL,
   period_id  INT UNSIGNED NOT NULL,
   PRIMARY KEY (work_id, period_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (period_id)
      REFERENCES periods(period_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Roughly linking composers to periods
CREATE TABLE IF NOT EXISTS artists_periods (
   artist_id   INT UNSIGNED NOT NULL,
   period_id   INT UNSIGNED NOT NULL,
   PRIMARY KEY (artist_id, period_id),
   FOREIGN KEY (artist_id)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (period_id)
      REFERENCES periods(period_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- User defined organisation
-- type can be genre, style, difficulty, etc
CREATE TABLE IF NOT EXISTS collections (
   collection VARCHAR(50) UNIQUE NOT NULL, -- name of the collection
   type       VARCHAR(50), -- grouping collections of a type, may be null
   -- type       ENUM('Genre','Style'), -- reduces flexibility
   PRIMARY KEY (collection)
) TYPE = InnoDB;

-- Linking works and collections
CREATE TABLE IF NOT EXISTS works_collections (
   work_id    INT UNSIGNED NOT NULL,
   collection VARCHAR(50), -- name of the collection
   PRIMARY KEY (collection, work_id),
   INDEX (work_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (collection)
      REFERENCES collections(collection)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;


-- Performances
-- ---------------------------------------------------------------------------
-- Performances
-- I would really like to add a user's impression of a performance (good/bad etc)
-- Perhaps this is best in another table, linked to a user or summed
CREATE TABLE IF NOT EXISTS performances (
   performance_id    INT UNSIGNED AUTO_INCREMENT NOT NULL,
   date_time         DATETIME, -- date and time of performance
   venue             VARCHAR(100), -- venue (should city/country be included?)
   occasion          VARCHAR(100), -- occasion (if any)
   notes             TEXT, -- any relevant information
   PRIMARY KEY (performance_id)
) TYPE = InnoDB;

-- Linking artists to performances
CREATE TABLE IF NOT EXISTS performs (
   performance_id    INT UNSIGNED NOT NULL,
   artist_id         INT UNSIGNED NOT NULL,
   instrument_id     INT UNSIGNED, -- if any
   role              VARCHAR(50), -- soloist, conductor, performer, guest artist etc
   PRIMARY KEY (performance_id, artist_id),
   INDEX (artist_id),
   INDEX (instrument_id),
   FOREIGN KEY (performance_id)
      REFERENCES performances(performance_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (artist_id)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   FOREIGN KEY (instrument_id)
      REFERENCES instruments(instrument_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Linking works to performances
CREATE TABLE IF NOT EXISTS works_performances (
   performance_id    INT UNSIGNED NOT NULL,
   work_id           INT UNSIGNED NOT NULL,
   PRIMARY KEY (performance_id, work_id),
   FOREIGN KEY (performance_id)
      REFERENCES performances(performance_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (work_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- File formats
CREATE TABLE IF NOT EXISTS file_formats (
   format           VARCHAR(20) NOT NULL, -- shortname for reference: ogg mp3 flac etc
   full_name        VARCHAR(50),  -- Ogg Vorbis etc
   extension        VARCHAR(10),  -- common filename extension mp3, ogg, flac wav
   website          VARCHAR(100), -- more information
   lossless         BOOL DEFAULT FALSE,      -- is this a lossless format?
   patented         BOOL DEFAULT TRUE,      -- is this an open format?
   notes            TEXT,      -- compatability with various OSs etc
   PRIMARY KEY (format)
) TYPE = InnoDB;


-- Recordings: different formats of the same recording allowed
CREATE TABLE IF NOT EXISTS recordings (
   recording_id     INT UNSIGNED AUTO_INCREMENT NOT NULL,
   format           VARCHAR(20),  -- ogg, mp3 (shudder), flac etc
   performance_id   INT UNSIGNED,
   work_id          INT UNSIGNED,   -- References movement
   movement_number  INT UNSIGNED,   -- References movement (can be null)
   filename         VARCHAR(200), -- Relative to local tree, not a system (please!)
   quality          ENUM('Bad','Good','Professional'),  -- recording quality
   bitrate          INT UNSIGNED,   -- Best average bit-rate of recording.
                               -- Very relevant for classical music on high end systems
   recording_source VARCHAR(20),  -- CD, live digital, record, radio, internet radio (shudder)
   duration         TIME,      -- Duration of recording
   PRIMARY KEY (recording_id, format),
   INDEX (performance_id),
   FOREIGN KEY (performance_id)
      REFERENCES performances(performance_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (format),
   FOREIGN KEY (format)
      REFERENCES file_formats(format)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (work_id, movement_number),
   FOREIGN KEY (work_id, movement_number)
      REFERENCES movements(work_id, movement_number)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Albums: Note that no actual attempt is made to link to one particular work/artist etc
CREATE TABLE IF NOT EXISTS albums (
   album_id     INT UNSIGNED AUTO_INCREMENT NOT NULL, -- because titles aren't unique
   title        VARCHAR(100), -- The title on the cover
   keywords     VARCHAR(100), -- Other references to people or groups may appear on the cover
   year         DATE,     -- released
   label        VARCHAR(50), -- could be, but isn't, a separate entity
   catalog_id   VARCHAR(50), -- the id assigned by the label
   location     VARCHAR(50),
   barcode      BIGINT UNSIGNED,   -- I assume barcodes are always very large integers
   PRIMARY KEY (album_id)
) TYPE = InnoDB;

-- Album (cover) images (weak relationship)
CREATE TABLE IF NOT EXISTS album_images (
   album_id     INT UNSIGNED NOT NULL,
   image_id     INT UNSIGNED NOT NULL,
   cover        BOOL DEFAULT TRUE,       -- is it a cover image
   number       INT UNSIGNED DEFAULT NULL, -- artificial, only if needed: for multiple images (rare)
   PRIMARY KEY (album_id, image_id, number)
) TYPE = InnoDB;

-- Album notes
-- Text/introduction/Analysis of the works that may be included as album notes
-- No mention of format (i.e. HTML, Docbook, XML, Latex whatever). Assume HTML for now.
-- One per album, per language
CREATE TABLE IF NOT EXISTS album_notes (
   album_id     INT UNSIGNED NOT NULL,
   language     CHAR(6), -- ISO 639
   text         TEXT, -- The text
   author       INT UNSIGNED, -- Artist ID, not important, only if they're already in the database please
   PRIMARY KEY (album_id, language),
   FOREIGN KEY (album_id)
      REFERENCES albums(album_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (language),
   FOREIGN KEY (language)
      REFERENCES languages(language)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (author),
   FOREIGN KEY (author)
      REFERENCES persons(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Album tracks
-- Links recordings to albums (deliberately many to many for compilation albums)
CREATE TABLE IF NOT EXISTS album_tracks (
   album_id     INT UNSIGNED NOT NULL,
   recording_id INT UNSIGNED,
   track_number SMALLINT UNSIGNED,
   PRIMARY KEY (album_id, recording_id),
   FOREIGN KEY (album_id)
      REFERENCES albums(album_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (recording_id),
   FOREIGN KEY (recording_id)
      REFERENCES recordings(recording_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;


-- Publication information
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS supplier (
   name      VARCHAR(50),
   address   VARCHAR(200), -- No need to be atomic
   mail_address   VARCHAR(200), -- NULL is same as normal address
   phone_number VARCHAR(15),
   email     VARCHAR(100),
   contact_name  VARCHAR(50), -- no need to be atomic
   PRIMARY KEY (name)
) TYPE = InnoDB;

-- It might be a problem if you don't know the ISMN
CREATE TABLE IF NOT EXISTS publications (
   ISMN       VARCHAR(15) NOT NULL, -- Standard number, M plus 10 digits. Couple extra for future extensions
   work_id    INT UNSIGNED NOT NULL,
   edition    VARCHAR(50), -- edition notes
   publisher  VARCHAR(50), -- another entity?
   supplier   VARCHAR(50),
   price      DECIMAL(4,2), -- Up to $9999.99. Currency? no need as yet.
   PRIMARY KEY (ISMN),
   INDEX (work_id),
   FOREIGN KEY (work_id)
      REFERENCES works(work_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (supplier),
   FOREIGN KEY (supplier)
      REFERENCES supplier(name)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- Copies
-- copy_id is bigint, so it can be a barcode if they are used
CREATE TABLE IF NOT EXISTS copies (
   copy_id     BIGINT aUTO_INCREMENT NOT NULL,
   publication VARCHAR(15) NOT NULL,  -- ISMN
   owner       INT UNSIGNED,  -- artist_id, who owns it.
   possessor   INT UNSIGNED,  -- artist_id, who has it. NULL means owner has it
   part        VARCHAR(50), -- part description
   instrument  INT UNSIGNED,  -- instrument id, if applicable
   number      INT UNSIGNED,  -- weak identifying number, if applicable
   PRIMARY KEY (copy_id),
   INDEX (publication),
   FOREIGN KEY (publication)
      REFERENCES publications(ISMN)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (owner),
   FOREIGN KEY (owner)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (possessor),
   FOREIGN KEY (possessor)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (instrument),
   FOREIGN KEY (instrument)
      REFERENCES instruments(instrument_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;

-- A wishlist for a particular publication
CREATE TABLE IF NOT EXISTS wants_publication (
   publication    VARCHAR(15), -- ISMN
   artist         INT UNSIGNED, -- artist_id, Who wants it.
   quantity       SMALLINT UNSIGNED,
   priority       ENUM('Low','Medium','High'), -- Out of 3?
   purchase_date  DATE, -- When it should be bought
   PRIMARY KEY (publication, artist),
   FOREIGN KEY (publication)
      REFERENCES publications(ISMN)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   INDEX (artist),
   FOREIGN KEY (artist)
      REFERENCES artists(artist_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE = InnoDB;