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;
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 ALTER TABLE profile_job
MODIFY COLUMN sectorid
TINYINT(2) UNSIGNED
DEFAULT NULL;
20 ALTER TABLE profile_job
MODIFY COLUMN subsectorid
SMALLINT(3) UNSIGNED
DEFAULT NULL;
21 ALTER TABLE profile_job
MODIFY COLUMN subsubsectorid
SMALLINT(3) UNSIGNED
DEFAULT NULL;
23 -- Rename columns that should be called uid.
24 -- ALTER TABLE group_events CHANGE COLUMN organisateur_uid uid INT(11) UNSIGNED DEFAULT NULL;
26 -- Prepares table having ids set to 0.
27 DELETE FROM profile_mentor_country
WHERE country
= 'YU' OR country
= '00';
28 UPDATE profile_addresses
SET localityId
= NULL WHERE localityId
= 0;
29 UPDATE profile_addresses
SET subAdministrativeAreaId
= NULL WHERE subAdministrativeAreaId
= 0;
30 UPDATE profile_addresses
SET administrativeAreaId
= NULL WHERE administrativeAreaId
= 0;
31 UPDATE profile_addresses
SET countryId
= NULL WHERE countryId
= '';
32 UPDATE profile_education
SET eduid
= NULL WHERE eduid
= 0;
33 UPDATE profile_education
SET degreeid
= NULL WHERE degreeid
= 0;
34 UPDATE profile_education
SET fieldid
= NULL WHERE fieldid
= 0;
35 UPDATE profile_education_enum
SET country
= NULL WHERE country
= '';
36 UPDATE profile_job
SET jobid
= NULL WHERE jobid
= 0;
37 UPDATE profile_job
SET sectorid
= NULL WHERE sectorid
= 0;
38 UPDATE profile_job
SET subsectorid
= NULL WHERE subsectorid
= 0;
39 UPDATE profile_job
SET subsubsectorid
= NULL WHERE subsubsectorid
= 0;
40 UPDATE profile_job_enum
SET holdingid
= NULL WHERE holdingid
= 0;
42 -- Adds missing data in foreign tables.
43 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
, country
,
44 countryFR
, capital
, nationalityFR
, licensePlate
, belongsTo
, phonePrefix
)
45 VALUES ('GF', 'GUF', 254, 'EU', 'French Guiana', 'Guyane française', 'Cayenne', NULL, 'FR', 'FR', 594),
46 ('GP', 'GLP', 312, 'EU', 'Guadeloupe', 'Guadeloupe', 'Basse-Terre', NULL, 'FR', 'FR', 590),
47 ('MQ', 'MTQ', 474, 'EU', 'Martinique', 'Martinique', 'Fort-de-France', NULL, 'FR', 'FR', 596),
48 ('NC', 'NCL', 540, 'EU', 'New Caledonia', 'Nouvelle-Calédonie', 'Nouméa', NULL, 'FR', 'FR', 687),
49 ('PF', 'PYF', 258, 'EU', 'French Polynesia', 'Polynésie française', 'Papeete', NULL, 'FR', 'FR', 689),
50 ('PR', 'PRI', 630, 'NA', 'Puerto Rico', 'Porto Rico', 'San Juan', NULL, 'USA', 'US', 1787),
51 ('MP', 'MNP', 580, 'NA', 'Northern Mariana Islands', 'Îles Mariannes du Nord', ' Saipan', NULL, 'USA', 'US', 1670);
53 -- Following tables all refer to profiles.pid.
54 ALTER TABLE profile_addresses
ADD FOREIGN KEY (localityId
) REFERENCES geoloc_localities (id) ON DELETE CASCADE ON UPDATE CASCADE;
55 ALTER TABLE profile_addresses
ADD FOREIGN KEY (subAdministrativeAreaId
) REFERENCES geoloc_subadministrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
56 ALTER TABLE profile_addresses
ADD FOREIGN KEY (administrativeAreaId
) REFERENCES geoloc_administrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
57 ALTER TABLE profile_addresses
ADD FOREIGN KEY (countryId
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
58 ALTER TABLE profile_binets
ADD FOREIGN KEY (binet_id
) REFERENCES profile_binet_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
59 ALTER TABLE profile_corps
ADD FOREIGN KEY (original_corpsid
) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
60 ALTER TABLE profile_corps
ADD FOREIGN KEY (current_corpsid
) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
61 ALTER TABLE profile_corps
ADD FOREIGN KEY (rankid
) REFERENCES profile_corps_rank_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
62 ALTER TABLE profile_education
ADD FOREIGN KEY (eduid
) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
63 ALTER TABLE profile_education
ADD FOREIGN KEY (degreeid
) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
64 ALTER TABLE profile_education
ADD FOREIGN KEY (fieldid
) REFERENCES profile_education_field_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
65 ALTER TABLE profile_education_enum
ADD FOREIGN KEY (country
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
66 ALTER TABLE profile_education_degree
ADD FOREIGN KEY (eduid
) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
67 ALTER TABLE profile_education_degree
ADD FOREIGN KEY (degreeid
) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
68 ALTER TABLE profile_job
ADD FOREIGN KEY (jobid
) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
69 ALTER TABLE profile_job
ADD FOREIGN KEY (sectorid
) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
70 ALTER TABLE profile_job
ADD FOREIGN KEY (subsectorid
) REFERENCES profile_job_subsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
71 ALTER TABLE profile_job
ADD FOREIGN KEY (subsubsectorid
) REFERENCES profile_job_subsubsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
72 ALTER TABLE profile_job_alternates
ADD FOREIGN KEY (subsubsectorid
) REFERENCES profile_job_subsubsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
73 ALTER TABLE profile_job_enum
ADD FOREIGN KEY (holdingid
) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
74 ALTER TABLE profile_job_subsector_enum
ADD FOREIGN KEY (sectorid
) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
75 ALTER TABLE profile_job_subsubsector_enum
ADD FOREIGN KEY (sectorid
) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
76 ALTER TABLE profile_job_subsubsector_enum
ADD FOREIGN KEY (subsectorid
) REFERENCES profile_job_subsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
77 ALTER TABLE profile_langskills
ADD FOREIGN KEY (lid
) REFERENCES profile_langskill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
78 ALTER TABLE profile_medal_grade_enum
ADD FOREIGN KEY (mid
) REFERENCES profile_medal_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
79 ALTER TABLE profile_mentor_country
ADD FOREIGN KEY (country
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
80 ALTER TABLE profile_mentor_sector
ADD FOREIGN KEY (sectorid
) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
81 ALTER TABLE profile_skills
ADD FOREIGN KEY (cid
) REFERENCES profile_skill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
82 ALTER TABLE profile_name
ADD FOREIGN KEY (typeid
) REFERENCES profile_name_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
83 ALTER TABLE profile_networking
ADD FOREIGN KEY (nwid
) REFERENCES profile_networking_enum (nwid
) ON DELETE CASCADE ON UPDATE CASCADE;
84 ALTER TABLE profiles
ADD FOREIGN KEY (section) REFERENCES profile_section_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
85 ALTER TABLE profiles
ADD FOREIGN KEY (nationality1
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
86 ALTER TABLE profiles
ADD FOREIGN KEY (nationality2
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
87 ALTER TABLE profiles
ADD FOREIGN KEY (nationality3
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
89 -- vim:set syntax=mysql: