From cb46deaa4d9056e5ea2a86d224c8878c76c70768 Mon Sep 17 00:00:00 2001 From: olivier Date: Thu, 23 Apr 2009 17:44:10 +0000 Subject: [PATCH] docref sql: setup foreign key constraints cascading, fix enumerations uniqueness git-svn-id: http://svn.parisson.org/svn/crem@84 3bf09e05-f825-4182-b9bc-eedd7160adf0 --- trunk/docref/crem.sql | 113 ++++++++++++++++++++++++++---------------- 1 file changed, 70 insertions(+), 43 deletions(-) diff --git a/trunk/docref/crem.sql b/trunk/docref/crem.sql index 06b0d5b..0539d02 100644 --- a/trunk/docref/crem.sql +++ b/trunk/docref/crem.sql @@ -49,52 +49,52 @@ CREATE TABLE physical_formats ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE publishing_status ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE acquisition_modes ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE metadata_authors ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE metadata_writers ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE legal_rights ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE recording_contexts ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE ad_conversions ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE vernacular_styles ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE generic_styles ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; -- @@ -103,7 +103,7 @@ CREATE TABLE generic_styles ( CREATE TABLE publishers ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - value VARCHAR(250) NOT NULL + value VARCHAR(250) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE publisher_collections ( @@ -111,7 +111,7 @@ CREATE TABLE publisher_collections ( publisher_id INTEGER NOT NULL, value VARCHAR(250) NOT NULL, - FOREIGN KEY(publisher_id) REFERENCES publishers (id) + FOREIGN KEY(publisher_id) REFERENCES publishers (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- @@ -130,8 +130,8 @@ CREATE TABLE locations ( current_name VARCHAR(150), -- recursif is_authoritative BOOLEAN NOT NULL DEFAULT 0, - FOREIGN KEY(current_name) REFERENCES locations (name), - FOREIGN KEY(complete_type_id) REFERENCES location_types (id) + FOREIGN KEY(current_name) REFERENCES locations (name) ON DELETE SET NULL ON UPDATE CASCADE, + FOREIGN KEY(complete_type_id) REFERENCES location_types (id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE location_aliases ( @@ -140,7 +140,8 @@ CREATE TABLE location_aliases ( is_authoritative BOOLEAN NOT NULL DEFAULT 0, PRIMARY KEY(location_name, alias), - FOREIGN KEY(location_name) REFERENCES locations (name) + FOREIGN KEY(location_name) REFERENCES locations (name) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; CREATE TABLE location_relations ( @@ -148,8 +149,10 @@ CREATE TABLE location_relations ( parent_location_name VARCHAR(150), PRIMARY KEY(location_name, parent_location_name), - FOREIGN KEY(location_name) REFERENCES locations (name), + FOREIGN KEY(location_name) REFERENCES locations (name) + ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(parent_location_name) REFERENCES locations (name) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; -- @@ -165,7 +168,7 @@ CREATE TABLE ethnic_group_aliases ( ethnic_group_id INTEGER NOT NULL, name VARCHAR(250) NOT NULL, - FOREIGN KEY(ethnic_group_id) REFERENCES ethnic_groups (id) + FOREIGN KEY(ethnic_group_id) REFERENCES ethnic_groups (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- @@ -211,16 +214,26 @@ CREATE TABLE media_collections ( ad_conversion_id INTEGER, public_access ENUM('none', 'metadata', 'full') NOT NULL DEFAULT 'metadata', - FOREIGN KEY(ad_conversion_id) REFERENCES ad_conversions (id), - FOREIGN KEY(publisher_collection_id) REFERENCES publisher_collections (id), - FOREIGN KEY(recording_context_id) REFERENCES recording_contexts (id), - FOREIGN KEY(publisher_id) REFERENCES publishers (id), - FOREIGN KEY(metadata_author_id) REFERENCES metadata_authors (id), - FOREIGN KEY(physical_format_id) REFERENCES physical_formats (id), - FOREIGN KEY(metadata_writer_id) REFERENCES metadata_writers (id), - FOREIGN KEY(legal_rights_id) REFERENCES legal_rights (id), - FOREIGN KEY(acquisition_mode_id) REFERENCES acquisition_modes (id), + FOREIGN KEY(ad_conversion_id) REFERENCES ad_conversions (id) + ON DELETE SET NULL, + FOREIGN KEY(publisher_collection_id) REFERENCES publisher_collections (id) + ON DELETE SET NULL, + FOREIGN KEY(recording_context_id) REFERENCES recording_contexts (id) + ON DELETE SET NULL, + FOREIGN KEY(publisher_id) REFERENCES publishers (id) + ON DELETE SET NULL, + FOREIGN KEY(metadata_author_id) REFERENCES metadata_authors (id) + ON DELETE SET NULL, + FOREIGN KEY(physical_format_id) REFERENCES physical_formats (id) + ON DELETE SET NULL, + FOREIGN KEY(metadata_writer_id) REFERENCES metadata_writers (id) + ON DELETE SET NULL, + FOREIGN KEY(legal_rights_id) REFERENCES legal_rights (id) + ON DELETE SET NULL, + FOREIGN KEY(acquisition_mode_id) REFERENCES acquisition_modes (id) + ON DELETE SET NULL, FOREIGN KEY(publishing_status_id) REFERENCES publishing_status (id) + ON DELETE SET NULL ) ENGINE=InnoDB; -- @@ -254,13 +267,20 @@ CREATE TABLE media_items ( filename VARCHAR(250) NOT NULL DEFAULT '', public_access ENUM('none', 'metadata', 'full') NOT NULL DEFAULT 'metadata', - FOREIGN KEY(location_name) REFERENCES locations (name), - FOREIGN KEY(ethnic_group_id) REFERENCES ethnic_groups (id), - FOREIGN KEY(collection_id) REFERENCES media_collections (id), - FOREIGN KEY(vernacular_style_id) REFERENCES vernacular_styles (id), - FOREIGN KEY(location_name) REFERENCES locations (name), - FOREIGN KEY(copied_from_item_id) REFERENCES media_items (id), + FOREIGN KEY(collection_id) REFERENCES media_collections (id) + ON DELETE CASCADE, + FOREIGN KEY(location_name) REFERENCES locations (name) + ON DELETE SET NULL, + FOREIGN KEY(ethnic_group_id) REFERENCES ethnic_groups (id) + ON DELETE SET NULL, + FOREIGN KEY(vernacular_style_id) REFERENCES vernacular_styles (id) + ON DELETE SET NULL, + FOREIGN KEY(location_name) REFERENCES locations (name) + ON DELETE SET NULL, + FOREIGN KEY(copied_from_item_id) REFERENCES media_items (id) + ON DELETE SET NULL, FOREIGN KEY(generic_style_id) REFERENCES generic_styles (id) + ON DELETE SET NULL ) ENGINE=InnoDB; -- @@ -274,7 +294,7 @@ CREATE TABLE media_parts ( start FLOAT NOT NULL, end FLOAT NOT NULL, - FOREIGN KEY(item_id) REFERENCES media_items (id) + FOREIGN KEY(item_id) REFERENCES media_items (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- @@ -290,8 +310,10 @@ CREATE TABLE instrument_relations ( instrument_id INTEGER NOT NULL, parent_instrument_id INTEGER NOT NULL, - FOREIGN KEY(instrument_id) REFERENCES instruments (id), + FOREIGN KEY(instrument_id) REFERENCES instruments (id) + ON DELETE CASCADE, FOREIGN KEY(parent_instrument_id) REFERENCES instruments (id) + ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE instrument_aliases ( @@ -303,20 +325,25 @@ CREATE TABLE instrument_alias_relations ( alias_id INTEGER NOT NULL, instrument_id INTEGER NOT NULL, - FOREIGN KEY(alias_id) REFERENCES instrument_aliases (id), + FOREIGN KEY(alias_id) REFERENCES instrument_aliases (id) + ON DELETE CASCADE, FOREIGN KEY(instrument_id) REFERENCES instruments (id) + ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE media_item_performances ( media_item_id INTEGER NOT NULL, - instrument_id INTEGER NOT NULL, + instrument_id INTEGER, alias_id INTEGER, instruments_num VARCHAR(250) NOT NULL DEFAULT '', musicians VARCHAR(250) NOT NULL DEFAULT '', - FOREIGN KEY(media_item_id) REFERENCES media_items (id), - FOREIGN KEY(instrument_id) REFERENCES instruments (id), + FOREIGN KEY(media_item_id) REFERENCES media_items (id) + ON DELETE CASCADE, + FOREIGN KEY(instrument_id) REFERENCES instruments (id) + ON DELETE SET NULL, FOREIGN KEY(alias_id) REFERENCES instrument_aliases (id) + ON DELETE SET NULL ) ENGINE=InnoDB; -- @@ -333,8 +360,8 @@ CREATE TABLE media_item_keywords ( keyword_id INTEGER NOT NULL, PRIMARY KEY(item_id, keyword_id), - FOREIGN KEY(item_id) REFERENCES media_items (id), - FOREIGN KEY(keyword_id) REFERENCES context_keywords (id) + FOREIGN KEY(item_id) REFERENCES media_items (id) ON DELETE CASCADE, + FOREIGN KEY(keyword_id) REFERENCES context_keywords (id) ON DELETE CASCADE ) ENGINE=InnoDB; -- @@ -359,7 +386,7 @@ CREATE TABLE playlists ( owner_username VARCHAR(250) NOT NULL, name VARCHAR(250) NOT NULL, - FOREIGN KEY(owner_username) REFERENCES users (username) + FOREIGN KEY(owner_username) REFERENCES users (username) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE playlist_resources ( @@ -367,7 +394,7 @@ CREATE TABLE playlist_resources ( resource_type ENUM('item', 'collection') NOT NULL, resource_id INTEGER NOT NULL, - FOREIGN KEY(playlist_id) REFERENCES playlists (id) + FOREIGN KEY(playlist_id) REFERENCES playlists (id) ON DELETE CASCADE ) ENGINE=InnoDB; @@ -383,6 +410,6 @@ CREATE TABLE revisions ( time DATETIME NOT NULL, username VARCHAR(64) NOT NULL, - FOREIGN KEY(username) REFERENCES users (username) + FOREIGN KEY(username) REFERENCES users (username) ) ENGINE=InnoDB; -- 2.39.5