| 1 | -- Modify tables that are refered to. |
| 2 | ALTER TABLE profile_education_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
| 3 | ALTER TABLE profile_education_degree_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
| 4 | ALTER TABLE profile_education_field_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
| 5 | ALTER TABLE profile_section_enum MODIFY COLUMN id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0; |
| 6 | |
| 7 | -- Following tables all needs a sligth change in their definition to match their foreign correspondance. |
| 8 | ALTER TABLE profile_addresses MODIFY COLUMN localityId BIGINT(20) UNSIGNED DEFAULT NULL; |
| 9 | ALTER TABLE profile_addresses MODIFY COLUMN subAdministrativeAreaId INT(11) UNSIGNED DEFAULT NULL; |
| 10 | ALTER TABLE profile_addresses MODIFY COLUMN administrativeAreaId INT(11) UNSIGNED DEFAULT NULL; |
| 11 | ALTER TABLE profile_binets MODIFY COLUMN binet_id TINYINT(3) UNSIGNED DEFAULT NULL; |
| 12 | ALTER TABLE profile_education MODIFY COLUMN eduid INT(11) UNSIGNED DEFAULT NULL; |
| 13 | ALTER TABLE profile_education MODIFY COLUMN degreeid INT(11) UNSIGNED DEFAULT NULL; |
| 14 | ALTER TABLE profile_education MODIFY COLUMN fieldid INT(11) UNSIGNED DEFAULT NULL; |
| 15 | ALTER TABLE profile_education_enum MODIFY COLUMN country CHAR(2) DEFAULT 'FR'; |
| 16 | ALTER TABLE profile_education_degree MODIFY COLUMN eduid INT(11) UNSIGNED DEFAULT 0; |
| 17 | ALTER TABLE profile_education_degree MODIFY COLUMN degreeid INT(11) UNSIGNED DEFAULT 0; |
| 18 | ALTER TABLE profile_job MODIFY COLUMN jobid INT(6) UNSIGNED DEFAULT NULL; |
| 19 | |
| 20 | -- Prepares table having ids set to 0. |
| 21 | DELETE FROM profile_mentor_country WHERE country = 'YU' OR country = '00'; |
| 22 | UPDATE profile_addresses SET localityId = NULL WHERE localityId = 0; |
| 23 | UPDATE profile_addresses SET subAdministrativeAreaId = NULL WHERE subAdministrativeAreaId = 0; |
| 24 | UPDATE profile_addresses SET administrativeAreaId = NULL WHERE administrativeAreaId = 0; |
| 25 | UPDATE profile_addresses SET countryId = NULL WHERE countryId = ''; |
| 26 | UPDATE profile_education SET eduid = NULL WHERE eduid = 0; |
| 27 | UPDATE profile_education SET degreeid = NULL WHERE degreeid = 0; |
| 28 | UPDATE profile_education SET fieldid = NULL WHERE fieldid = 0; |
| 29 | UPDATE profile_education_enum SET country = NULL WHERE country = ''; |
| 30 | UPDATE profile_job SET jobid = NULL WHERE jobid = 0; |
| 31 | UPDATE profile_job_enum SET holdingid = NULL WHERE holdingid = 0; |
| 32 | |
| 33 | -- Adds missing data in foreign tables. |
| 34 | INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, country, |
| 35 | countryFR, capital, nationalityFR, licensePlate, belongsTo, phonePrefix) |
| 36 | VALUES ('GF', 'GUF', 254, 'EU', 'French Guiana', 'Guyane française', 'Cayenne', NULL, 'FR', 'FR', 594), |
| 37 | ('GP', 'GLP', 312, 'EU', 'Guadeloupe', 'Guadeloupe', 'Basse-Terre', NULL, 'FR', 'FR', 590), |
| 38 | ('MQ', 'MTQ', 474, 'EU', 'Martinique', 'Martinique', 'Fort-de-France', NULL, 'FR', 'FR', 596), |
| 39 | ('NC', 'NCL', 540, 'EU', 'New Caledonia', 'Nouvelle-Calédonie', 'Nouméa', NULL, 'FR', 'FR', 687), |
| 40 | ('PF', 'PYF', 258, 'EU', 'French Polynesia', 'Polynésie française', 'Papeete', NULL, 'FR', 'FR', 689), |
| 41 | ('PR', 'PRI', 630, 'NA', 'Puerto Rico', 'Porto Rico', 'San Juan', NULL, 'USA', 'US', 1787), |
| 42 | ('MP', 'MNP', 580, 'NA', 'Northern Mariana Islands', 'Îles Mariannes du Nord', ' Saipan', NULL, 'USA', 'US', 1670); |
| 43 | |
| 44 | -- Finaly we add the foreign keys. |
| 45 | ALTER TABLE profile_addresses ADD FOREIGN KEY (localityId) REFERENCES geoloc_localities (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 46 | ALTER TABLE profile_addresses ADD FOREIGN KEY (subAdministrativeAreaId) REFERENCES geoloc_subadministrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 47 | ALTER TABLE profile_addresses ADD FOREIGN KEY (administrativeAreaId) REFERENCES geoloc_administrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 48 | ALTER TABLE profile_addresses ADD FOREIGN KEY (countryId) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 49 | ALTER TABLE profile_binets ADD FOREIGN KEY (binet_id) REFERENCES profile_binet_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 50 | ALTER TABLE profile_corps ADD FOREIGN KEY (original_corpsid) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 51 | ALTER TABLE profile_corps ADD FOREIGN KEY (current_corpsid) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 52 | ALTER TABLE profile_corps ADD FOREIGN KEY (rankid) REFERENCES profile_corps_rank_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 53 | ALTER TABLE profile_education ADD FOREIGN KEY (eduid) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 54 | ALTER TABLE profile_education ADD FOREIGN KEY (degreeid) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 55 | ALTER TABLE profile_education ADD FOREIGN KEY (fieldid) REFERENCES profile_education_field_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 56 | ALTER TABLE profile_education_enum ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 57 | ALTER TABLE profile_education_degree ADD FOREIGN KEY (eduid) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 58 | ALTER TABLE profile_education_degree ADD FOREIGN KEY (degreeid) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 59 | ALTER TABLE profile_job ADD FOREIGN KEY (jobid) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 60 | ALTER TABLE profile_job_enum ADD FOREIGN KEY (holdingid) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 61 | ALTER TABLE profile_langskills ADD FOREIGN KEY (lid) REFERENCES profile_langskill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 62 | ALTER TABLE profile_medal_grade_enum ADD FOREIGN KEY (mid) REFERENCES profile_medal_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 63 | ALTER TABLE profile_mentor_country ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 64 | ALTER TABLE profile_skills ADD FOREIGN KEY (cid) REFERENCES profile_skill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 65 | ALTER TABLE profile_name ADD FOREIGN KEY (typeid) REFERENCES profile_name_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 66 | ALTER TABLE profile_networking ADD FOREIGN KEY (nwid) REFERENCES profile_networking_enum (nwid) ON DELETE CASCADE ON UPDATE CASCADE; |
| 67 | ALTER TABLE profiles ADD FOREIGN KEY (section) REFERENCES profile_section_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 68 | ALTER TABLE profiles ADD FOREIGN KEY (nationality1) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 69 | ALTER TABLE profiles ADD FOREIGN KEY (nationality2) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 70 | ALTER TABLE profiles ADD FOREIGN KEY (nationality3) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 71 | |
| 72 | -- vim:set syntax=mysql: |