From 7f376ae05ebd655d5161ed87aedb1edc6b01c08c Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Wed, 1 Sep 2010 18:44:56 +0200 Subject: [PATCH] Uniformizes pid and uid fields in database (Closes #1207). MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- classes/user.php | 6 +-- include/validations/surveys.inc.php | 11 ++--- modules/survey/survey.inc.php | 6 +-- modules/xnetevents.php | 28 ++++++------ templates/xnetevents/edit.tpl | 2 +- templates/xnetevents/index.tpl | 2 +- upgrade/1.0.1/00_job.sql | 3 +- upgrade/1.0.1/06_ids.sql | 90 +++++++++++++++++++++++++++++++++++++ 8 files changed, 114 insertions(+), 34 deletions(-) create mode 100644 upgrade/1.0.1/06_ids.sql diff --git a/classes/user.php b/classes/user.php index b69c6d2..6c78ab4 100644 --- a/classes/user.php +++ b/classes/user.php @@ -732,16 +732,12 @@ class User extends PlUser } // Updates user in following tables. - foreach (array('group_announces', 'payment_transactions', 'log_sessions') as $table) { + foreach (array('group_announces', 'payment_transactions', 'log_sessions', 'group_events') as $table) { XDB::execute('UPDATE ' . $table . ' SET uid = {?} WHERE uid = {?}', $newuser->id(), $this->id()); } - XDB::execute('UPDATE group_events - SET organisateur_uid = {?} - WHERE organisateur_uid = {?}', - $newuser->id(), $this->id()); // Merges user in following tables, ie updates when possible, then deletes remaining occurences of the old user. foreach (array('group_announces_read', 'group_event_participants', 'group_member_sub_requests', 'group_members') as $table) { diff --git a/include/validations/surveys.inc.php b/include/validations/surveys.inc.php index bf03ad9..8eca5d6 100644 --- a/include/validations/surveys.inc.php +++ b/include/validations/surveys.inc.php @@ -91,14 +91,9 @@ class SurveyReq extends Validate public function commit() { - $sql = 'INSERT INTO surveys - SET questions={?}, - title={?}, - description={?}, - author_id={?}, - end={?}, - mode={?}, - promos={?}'; + $sql = 'INSERT INTO surveys + SET questions = {?}, title = {?}, description = {?}, + uid = {?}, end = {?},mode = {?}, promos = {?}'; return XDB::execute($sql, serialize($this->questions), $this->title, $this->description, $this->user->id(), $this->end, $this->mode, $this->promos); } diff --git a/modules/survey/survey.inc.php b/modules/survey/survey.inc.php index 0c0c645..670075e 100644 --- a/modules/survey/survey.inc.php +++ b/modules/survey/survey.inc.php @@ -192,7 +192,7 @@ class Survey $nbf = count($line); $users = array(); if ($this->isMode(self::MODE_XIDENT)) { // if the mode is non anonymous - $users = User::getBulkUsersWithUIDs(XDB::fetchAllAssoc('vid', 'SELECT v.id AS vid, v.user_id + $users = User::getBulkUsersWithUIDs(XDB::fetchAllAssoc('vid', 'SELECT v.id AS vid, v.uid FROM survey_votes AS v WHERE v.survey_id = {?} ORDER BY vid ASC', @@ -435,7 +435,7 @@ class Survey public function vote($uid, $args) { XDB::execute('INSERT INTO survey_votes - SET survey_id = {?}, user_id = {?};', $this->id, $uid); // notes the user as having voted + SET survey_id = {?}, uid = {?};', $this->id, $uid); // notes the user as having voted $vid = XDB::insertId(); for ($i = 0; $i < count($this->questions); $i++) { $ans = $this->questions[$i]->checkAnswer($args[$i]); @@ -454,7 +454,7 @@ class Survey { $res = XDB::query('SELECT id FROM survey_votes - WHERE survey_id = {?} AND user_id = {?};', $this->id, $uid); // checks whether the user has already voted + WHERE survey_id = {?} AND uid = {?};', $this->id, $uid); // checks whether the user has already voted return ($res->numRows() != 0); } // }}} diff --git a/modules/xnetevents.php b/modules/xnetevents.php index d5091c9..9db873e 100644 --- a/modules/xnetevents.php +++ b/modules/xnetevents.php @@ -394,12 +394,12 @@ class XnetEventsModule extends PLModule ); $trivial = array('intitule', 'descriptif', 'noinvite', - 'show_participants', 'accept_nonmembre', 'organisateur_uid'); + 'show_participants', 'accept_nonmembre', 'uid'); foreach ($trivial as $k) { $evt[$k] = Post::v($k); } if (!$eid) { - $evt['organisateur_uid'] = S::v('uid'); + $evt['uid'] = S::v('uid'); } if (Post::v('deadline')) { @@ -411,17 +411,17 @@ class XnetEventsModule extends PLModule } // Store the modifications in the database - XDB::execute('REPLACE INTO group_events - SET eid={?}, asso_id={?}, organisateur_uid={?}, intitule={?}, - paiement_id = {?}, descriptif = {?}, debut = {?}, - fin = {?}, show_participants = {?}, short_name = {?}, - deadline_inscription = {?}, noinvite = {?}, - accept_nonmembre = {?}', - $evt['eid'], $evt['asso_id'], $evt['organisateur_uid'], - $evt['intitule'], $evt['paiement_id'], $evt['descriptif'], - $evt['debut'], $evt['fin'], $evt['show_participants'], - $evt['short_name'], $evt['deadline_inscription'], - $evt['noinvite'], $evt['accept_nonmembre']); + XDB::execute('REPLACE INTO group_events + SET eid = {?}, asso_id = {?}, uid = {?}, intitule = {?}, + paiement_id = {?}, descriptif = {?}, debut = {?}, + fin = {?}, show_participants = {?}, short_name = {?}, + deadline_inscription = {?}, noinvite = {?}, + accept_nonmembre = {?}', + $evt['eid'], $evt['asso_id'], $evt['uid'], + $evt['intitule'], $evt['paiement_id'], $evt['descriptif'], + $evt['debut'], $evt['fin'], $evt['show_participants'], + $evt['short_name'], $evt['deadline_inscription'], + $evt['noinvite'], $evt['accept_nonmembre']); // if new event, get its id if (!$eid) { @@ -487,7 +487,7 @@ class XnetEventsModule extends PLModule // when modifying an old event retreive the old datas if ($eid) { $res = XDB::query( - "SELECT eid, intitule, descriptif, debut, fin, organisateur_uid, + "SELECT eid, intitule, descriptif, debut, fin, uid, show_participants, paiement_id, short_name, deadline_inscription, noinvite, accept_nonmembre FROM group_events diff --git a/templates/xnetevents/edit.tpl b/templates/xnetevents/edit.tpl index 4e2f4cd..6816e40 100644 --- a/templates/xnetevents/edit.tpl +++ b/templates/xnetevents/edit.tpl @@ -251,7 +251,7 @@ Ton inscription à [METS LE NOM DE L'ÉVÉNEMENT ICI] a bien été enregistrée
- {if $evt.eid}{/if} + {if $evt.eid}{/if}   diff --git a/templates/xnetevents/index.tpl b/templates/xnetevents/index.tpl index 3db8f45..9447847 100644 --- a/templates/xnetevents/index.tpl +++ b/templates/xnetevents/index.tpl @@ -97,7 +97,7 @@ Annonceur : - {profile user=$e.organisateur_uid promo=true groupperms=false} + {profile user=$e.uid promo=true groupperms=false} {if $is_admin || $e.show_participants || ($e.deadline_inscription && $e.inscr_open)} diff --git a/upgrade/1.0.1/00_job.sql b/upgrade/1.0.1/00_job.sql index 79c721b..fa7b4ec 100644 --- a/upgrade/1.0.1/00_job.sql +++ b/upgrade/1.0.1/00_job.sql @@ -37,7 +37,7 @@ CREATE TABLE `profile_job_term` ( `computed` enum('original','computed') NOT NULL DEFAULT 'original' COMMENT 'terms can be added by user or computed from entreprise', PRIMARY KEY (`pid`, `jid`, `jtid`), INDEX `jtid` (`jtid`), - FOREIGN KEY (`pid`, `jid`) REFERENCES `profile_job` (`pid`, `id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`jid`) REFERENCES `profile_job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms for jobs in profiles'; @@ -46,7 +46,6 @@ CREATE TABLE `profile_mentor_term` ( `jtid` int unsigned NOT NULL COMMENT 'term id', PRIMARY KEY (`pid`, `jtid`), INDEX `jtid` (`jtid`), - FOREIGN KEY (`pid`) REFERENCES `profiles` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms for mentorship in profiles'; diff --git a/upgrade/1.0.1/06_ids.sql b/upgrade/1.0.1/06_ids.sql new file mode 100644 index 0000000..dad95d3 --- /dev/null +++ b/upgrade/1.0.1/06_ids.sql @@ -0,0 +1,90 @@ +-- accounts and profiles are the tables where uid and pid are defined through an auto increment. +ALTER TABLE accounts MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; +ALTER TABLE profiles MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; + +-- Following tables all refer to accounts.uid. +ALTER TABLE account_auth_openid MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE account_lost_passwords MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE account_profiles MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE aliases MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE announce_read MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE announces MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE axletter_ins MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE axletter_rights MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE carvas MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE contacts MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE email_list_moderate MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE email_options MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE email_send_save MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE email_watch MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE emails MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE forum_innd MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE forum_profiles MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE forum_subs MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE group_announces MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE group_announces_read MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE group_event_participants MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE group_member_sub_requests MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE group_members MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE homonyms MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE ip_watch MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE log_last_sessions MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE log_sessions MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE newsletter_ins MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE payment_transactions MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_modifications MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_marketing MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_mstats MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_pending MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_subs MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE reminder MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE requests MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE requests_hidden MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE watch MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE watch_nonins MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE watch_promo MODIFY COLUMN uid INT(11) UNSIGNED NOT NULL DEFAULT 0; + +-- Following tables all refer to accounts.uid, but they use a different name. +ALTER TABLE contacts MODIFY COLUMN contact INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE email_list_moderate MODIFY COLUMN mid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE gapps_accounts MODIFY COLUMN l_userid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE gapps_queue MODIFY COLUMN q_owner_id INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE gapps_queue MODIFY COLUMN q_recipient_id INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE gapps_nicknames MODIFY COLUMN l_userid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE homonyms MODIFY COLUMN homonyme_id INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE log_sessions MODIFY COLUMN suid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_marketing MODIFY COLUMN sender INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE register_mstats MODIFY COLUMN sender INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE watch_nonins MODIFY COLUMN ni_id INT(11) UNSIGNED NOT NULL DEFAULT 0; + +-- Rename columns that should be called uid. +ALTER TABLE group_events CHANGE COLUMN organisateur_uid uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE survey_votes CHANGE COLUMN user_id uid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE surveys CHANGE COLUMN author_id uid INT(11) UNSIGNED NOT NULL DEFAULT 0; + +-- Following tables all refer to profiles.pid. +ALTER TABLE account_profiles MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE requests MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE search_name MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE watch_profile MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_addresses MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_binets MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_corps MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_display MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_education MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_job MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_job_term MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_langskills MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_medals MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_mentor MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_mentor_country MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_mentor_sector MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_mentor_term MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_modifications MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_name MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_networking MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_phones MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_photos MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; +ALTER TABLE profile_skills MODIFY COLUMN pid INT(11) UNSIGNED NOT NULL DEFAULT 0; + +-- vim:set syntax=mysql: -- 2.1.4