Commit | Line | Data |
---|---|---|
8768e5af SJ |
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; | |
8768e5af | 19 | |
8768e5af SJ |
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; | |
8768e5af SJ |
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 | ||
c550efca | 44 | -- Finaly we add the foreign keys. |
8768e5af SJ |
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; | |
8768e5af SJ |
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; | |
8768e5af | 60 | ALTER TABLE profile_job_enum ADD FOREIGN KEY (holdingid) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; |
8768e5af SJ |
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; | |
8768e5af SJ |
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: |