Merge remote branch 'origin/xorg/1.0.2/master' into xorg/master

[platal.git] / upgrade / 1.0.1 / 08_profile_foreign_keys.sql

diff --git a/upgrade/1.0.1/08_profile_foreign_keys.sql b/upgrade/1.0.1/08_profile_foreign_keys.sql
index b0ce65f..b44ffc2 100644 (file)
--- a/upgrade/1.0.1/08_profile_foreign_keys.sql
+++ b/upgrade/1.0.1/08_profile_foreign_keys.sql
@@ -16,9 +16,6 @@ ALTER TABLE profile_education_enum MODIFY COLUMN country CHAR(2) DEFAULT 'FR';
 ALTER TABLE profile_education_degree MODIFY COLUMN eduid INT(11) UNSIGNED DEFAULT 0;
 ALTER TABLE profile_education_degree MODIFY COLUMN degreeid INT(11) UNSIGNED DEFAULT 0;
 ALTER TABLE profile_job MODIFY COLUMN jobid INT(6) UNSIGNED DEFAULT NULL;
-ALTER TABLE profile_job MODIFY COLUMN sectorid TINYINT(2) UNSIGNED DEFAULT NULL;
-ALTER TABLE profile_job MODIFY COLUMN subsectorid SMALLINT(3) UNSIGNED DEFAULT NULL;
-ALTER TABLE profile_job MODIFY COLUMN subsubsectorid SMALLINT(3) UNSIGNED DEFAULT NULL;
 
 -- Prepares table having ids set to 0.
 DELETE FROM profile_mentor_country WHERE country = 'YU' OR country = '00';
@@ -31,10 +28,8 @@ UPDATE profile_education SET degreeid = NULL WHERE degreeid = 0;
 UPDATE profile_education SET fieldid = NULL WHERE fieldid = 0;
 UPDATE profile_education_enum SET country = NULL WHERE country = '';
 UPDATE profile_job SET jobid = NULL WHERE jobid = 0;
-UPDATE profile_job SET sectorid = NULL WHERE sectorid = 0;
-UPDATE profile_job SET subsectorid = NULL WHERE subsectorid = 0;
-UPDATE profile_job SET subsubsectorid = NULL WHERE subsubsectorid = 0;
 UPDATE profile_job_enum SET holdingid = NULL WHERE holdingid = 0;
+UPDATE profiles SET section = NULL WHERE section = 0;
 
 -- Adds missing data in foreign tables.
 INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, country,
@@ -47,7 +42,7 @@ INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, wor
              ('PR', 'PRI', 630, 'NA', 'Puerto Rico', 'Porto Rico', 'San Juan', NULL, 'USA', 'US', 1787),
              ('MP', 'MNP', 580, 'NA', 'Northern Mariana Islands', 'Îles Mariannes du Nord', ' Saipan', NULL, 'USA', 'US', 1670);
 
--- Following tables all refer to profiles.pid.
+-- Finaly we add the foreign keys.
 ALTER TABLE profile_addresses ADD FOREIGN KEY (localityId) REFERENCES geoloc_localities (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_addresses ADD FOREIGN KEY (subAdministrativeAreaId) REFERENCES geoloc_subadministrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_addresses ADD FOREIGN KEY (administrativeAreaId) REFERENCES geoloc_administrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
@@ -63,18 +58,10 @@ ALTER TABLE profile_education_enum ADD FOREIGN KEY (country) REFERENCES geoloc_c
 ALTER TABLE profile_education_degree ADD FOREIGN KEY (eduid) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_education_degree ADD FOREIGN KEY (degreeid) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_job ADD FOREIGN KEY (jobid) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job ADD FOREIGN KEY (sectorid) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job ADD FOREIGN KEY (subsectorid) REFERENCES profile_job_subsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job ADD FOREIGN KEY (subsubsectorid) REFERENCES profile_job_subsubsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job_alternates ADD FOREIGN KEY (subsubsectorid) REFERENCES profile_job_subsubsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_job_enum ADD FOREIGN KEY (holdingid) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job_subsector_enum ADD FOREIGN KEY (sectorid) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job_subsubsector_enum ADD FOREIGN KEY (sectorid) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_job_subsubsector_enum ADD FOREIGN KEY (subsectorid) REFERENCES profile_job_subsector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_langskills ADD FOREIGN KEY (lid) REFERENCES profile_langskill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_medal_grade_enum ADD FOREIGN KEY (mid) REFERENCES profile_medal_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_mentor_country ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE profile_mentor_sector ADD FOREIGN KEY (sectorid) REFERENCES profile_job_sector_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_skills ADD FOREIGN KEY (cid) REFERENCES profile_skill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_name ADD FOREIGN KEY (typeid) REFERENCES profile_name_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE profile_networking ADD FOREIGN KEY (nwid) REFERENCES profile_networking_enum (nwid) ON DELETE CASCADE ON UPDATE CASCADE;