From 8768e5aff0816b1d207c04ef800c12ec62be9496 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 7 Sep 2010 13:14:38 +0200 Subject: [PATCH] Add foreign keys for profile tables. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- include/geocoding.inc.php | 4 +- include/validations/entreprises.inc.php | 6 +-- modules/profile/addresses.inc.php | 8 --- modules/profile/general.inc.php | 3 +- modules/profile/jobs.inc.php | 4 ++ upgrade/1.0.1/08_profile_foreign_keys.sql | 90 +++++++++++++++++++++++++++++++ 6 files changed, 102 insertions(+), 13 deletions(-) create mode 100644 upgrade/1.0.1/08_profile_foreign_keys.sql diff --git a/include/geocoding.inc.php b/include/geocoding.inc.php index 620ae56..da809d7 100644 --- a/include/geocoding.inc.php +++ b/include/geocoding.inc.php @@ -41,7 +41,7 @@ abstract class Geocoder { 'locality' => 'geoloc_localities', ); - if (isset($address[$area . 'Name']) && isset($databases[$area])) { + if (isset($address[$area . 'Name']) && isset($databases[$area]) && !empty($address[$area . 'Name'])) { $res = XDB::query("SELECT id FROM " . $databases[$area] . " WHERE name = {?}", @@ -54,6 +54,8 @@ abstract class Geocoder { } else { $address[$area . 'Id'] = $res->fetchOneCell(); } + } else { + $address[$area . 'Id'] = null; } } diff --git a/include/validations/entreprises.inc.php b/include/validations/entreprises.inc.php index 63dea2f..cdb0cc1 100644 --- a/include/validations/entreprises.inc.php +++ b/include/validations/entreprises.inc.php @@ -30,9 +30,9 @@ class EntrReq extends ProfileValidate public $acronym; public $url; public $email; - public $holdingid; - public $NAF_code; - public $AX_code; + public $holdingid = null; + public $NAF_code = null;; + public $AX_code = null; public $tel; public $fax; diff --git a/modules/profile/addresses.inc.php b/modules/profile/addresses.inc.php index 6fe6f75..0c6629a 100644 --- a/modules/profile/addresses.inc.php +++ b/modules/profile/addresses.inc.php @@ -95,14 +95,6 @@ class ProfileSettingAddress extends ProfileSettingGeocoding Geocoder::getAreaId($address, "subAdministrativeArea"); Geocoder::getAreaId($address, "locality"); - // Cleanup foreign keys - $foreign_keys = array('localityId', 'subAdministrativeAreaId', 'administrativeAreaId', 'countryId'); - foreach ($foreign_keys as $key) { - if ($address[$key] == '') { - $address[$key] = null; - } - } - XDB::execute("INSERT INTO profile_addresses (pid, type, id, flags, accuracy, text, postalText, postalCode, localityId, subAdministrativeAreaId, administrativeAreaId, diff --git a/modules/profile/general.inc.php b/modules/profile/general.inc.php index ea4e05a..38cbd07 100644 --- a/modules/profile/general.inc.php +++ b/modules/profile/general.inc.php @@ -302,11 +302,12 @@ class ProfileSettingEdu implements ProfileSetting $page->pid()); foreach ($value as $eduid=>&$edu) { if ($edu['eduid'] != '') { + $fieldId = ($edu['fieldid'] == 0) ? null : $edu['fieldid']; XDB::execute("INSERT INTO profile_education SET id = {?}, pid = {?}, eduid = {?}, degreeid = {?}, fieldid = {?}, grad_year = {?}, program = {?}", $eduid, $page->pid(), $edu['eduid'], $edu['degreeid'], - $edu['fieldid'], $edu['grad_year'], $edu['program']); + $fieldId, $edu['grad_year'], $edu['program']); } } } diff --git a/modules/profile/jobs.inc.php b/modules/profile/jobs.inc.php index 30cc3f4..a77bfa3 100644 --- a/modules/profile/jobs.inc.php +++ b/modules/profile/jobs.inc.php @@ -230,6 +230,10 @@ class ProfileSettingJob extends ProfileSettingGeocoding } array_splice($value, $key, 1); } + foreach (array('sectorid', 'subsectorid', 'subsubsectorid') as $key) { + if ($job[$key] == 0) { + $job[$key] = null; + } } foreach ($value as $key => &$job) { $ls = true; diff --git a/upgrade/1.0.1/08_profile_foreign_keys.sql b/upgrade/1.0.1/08_profile_foreign_keys.sql new file mode 100644 index 0000000..86a3972 --- /dev/null +++ b/upgrade/1.0.1/08_profile_foreign_keys.sql @@ -0,0 +1,90 @@ +-- Modify tables that are refered to. +ALTER TABLE profile_education_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; +ALTER TABLE profile_education_degree_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; +ALTER TABLE profile_education_field_enum MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; +ALTER TABLE profile_section_enum MODIFY COLUMN id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0; + +-- Following tables all needs a sligth change in their definition to match their foreign correspondance. +ALTER TABLE profile_addresses MODIFY COLUMN localityId BIGINT(20) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_addresses MODIFY COLUMN subAdministrativeAreaId INT(11) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_addresses MODIFY COLUMN administrativeAreaId INT(11) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_binets MODIFY COLUMN binet_id TINYINT(3) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_education MODIFY COLUMN eduid INT(11) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_education MODIFY COLUMN degreeid INT(11) UNSIGNED DEFAULT NULL; +ALTER TABLE profile_education MODIFY COLUMN fieldid INT(11) UNSIGNED DEFAULT NULL; +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; + +-- Rename columns that should be called uid. +-- ALTER TABLE group_events CHANGE COLUMN organisateur_uid uid INT(11) UNSIGNED DEFAULT NULL; + +-- Prepares table having ids set to 0. +DELETE FROM profile_mentor_country WHERE country = 'YU' OR country = '00'; +UPDATE profile_addresses SET localityId = NULL WHERE localityId = 0; +UPDATE profile_addresses SET subAdministrativeAreaId = NULL WHERE subAdministrativeAreaId = 0; +UPDATE profile_addresses SET administrativeAreaId = NULL WHERE administrativeAreaId = 0; +UPDATE profile_addresses SET countryId = NULL WHERE countryId = ''; +UPDATE profile_education SET eduid = NULL WHERE eduid = 0; +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; + +-- Adds missing data in foreign tables. +INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, country, + countryFR, capital, nationalityFR, licensePlate, belongsTo, phonePrefix) + VALUES ('GF', 'GUF', 254, 'EU', 'French Guiana', 'Guyane française', 'Cayenne', NULL, 'FR', 'FR', 594), + ('GP', 'GLP', 312, 'EU', 'Guadeloupe', 'Guadeloupe', 'Basse-Terre', NULL, 'FR', 'FR', 590), + ('MQ', 'MTQ', 474, 'EU', 'Martinique', 'Martinique', 'Fort-de-France', NULL, 'FR', 'FR', 596), + ('NC', 'NCL', 540, 'EU', 'New Caledonia', 'Nouvelle-Calédonie', 'Nouméa', NULL, 'FR', 'FR', 687), + ('PF', 'PYF', 258, 'EU', 'French Polynesia', 'Polynésie française', 'Papeete', NULL, 'FR', 'FR', 689), + ('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. +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; +ALTER TABLE profile_addresses ADD FOREIGN KEY (countryId) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_binets ADD FOREIGN KEY (binet_id) REFERENCES profile_binet_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_corps ADD FOREIGN KEY (original_corpsid) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_corps ADD FOREIGN KEY (current_corpsid) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_corps ADD FOREIGN KEY (rankid) REFERENCES profile_corps_rank_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_education ADD FOREIGN KEY (eduid) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +-- +ALTER TABLE profile_education ADD FOREIGN KEY (degreeid) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_education ADD FOREIGN KEY (fieldid) REFERENCES profile_education_field_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profile_education_enum ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; +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; +ALTER TABLE profiles ADD FOREIGN KEY (section) REFERENCES profile_section_enum (id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profiles ADD FOREIGN KEY (nationality1) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profiles ADD FOREIGN KEY (nationality2) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE profiles ADD FOREIGN KEY (nationality3) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; + +-- vim:set syntax=mysql: -- 2.1.4