From 843707811834ac764ceb05df10ddfde16e143912 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Thu, 21 Oct 2010 14:08:48 +0200 Subject: [PATCH] A few fixes on upgrade scripts. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- include/newsletter.inc.php | 2 +- modules/profile/groups.inc.php | 2 +- modules/survey/survey.inc.php | 3 ++- upgrade/1.0.1/07_ids_foreign_keys.sql | 3 ++- upgrade/1.0.1/08_profile_foreign_keys.sql | 1 + upgrade/1.0.1/09_remaining_foreign_keys.sql | 1 + upgrade/1.0.1/12_profiles.sql | 2 +- upgrade/1.0.1/update.sh | 5 ++--- 8 files changed, 11 insertions(+), 8 deletions(-) diff --git a/include/newsletter.inc.php b/include/newsletter.inc.php index f914615..d15d64e 100644 --- a/include/newsletter.inc.php +++ b/include/newsletter.inc.php @@ -148,7 +148,7 @@ class NewsLetter extends MassMailer { $user = is_null($uid) ? S::v('uid') : $uid; XDB::execute("REPLACE INTO newsletter_ins (uid,last) - VALUES ({?}, 0)", $user); + VALUES ({?}, NULL)", $user); } protected function subscriptionWhere() diff --git a/modules/profile/groups.inc.php b/modules/profile/groups.inc.php index ab33f21..26bfafd 100644 --- a/modules/profile/groups.inc.php +++ b/modules/profile/groups.inc.php @@ -39,7 +39,7 @@ class ProfileSettingSection implements ProfileSetting XDB::execute("UPDATE profiles SET section = {?} WHERE pid = {?}", - $value, $page->pid()); + ($value == 0) ? null : $value, $page->pid()); } public function getText($value) { diff --git a/modules/survey/survey.inc.php b/modules/survey/survey.inc.php index 299b7fb..cfc2812 100644 --- a/modules/survey/survey.inc.php +++ b/modules/survey/survey.inc.php @@ -439,7 +439,8 @@ class Survey public function vote($uid, $args) { XDB::execute('INSERT INTO survey_votes - SET survey_id = {?}, uid = {?};', $this->id, $uid); // notes the user as having voted + SET survey_id = {?}, uid = {?}', + $this->id, ($uid == 0) ? null : $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]); diff --git a/upgrade/1.0.1/07_ids_foreign_keys.sql b/upgrade/1.0.1/07_ids_foreign_keys.sql index 59d3fb2..2ac1021 100644 --- a/upgrade/1.0.1/07_ids_foreign_keys.sql +++ b/upgrade/1.0.1/07_ids_foreign_keys.sql @@ -6,6 +6,7 @@ UPDATE register_marketing SET sender = NULL WHERE sender = 0; UPDATE register_mstats SET sender = NULL WHERE sender = 0; UPDATE requests SET pid = NULL WHERE pid = 0; UPDATE profile_addresses SET pid = NULL WHERE pid = 0; +UPDATE survey_votes SET uid = NULL WHERE uid = 0; -- Deltes erroneous data. DELETE FROM homonyms WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = homonyms.homonyme_id); @@ -16,6 +17,7 @@ DELETE FROM forum_profiles WHERE NOT EXISTS (SELECT * FROM accounts WHERE accoun DELETE FROM watch WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = watch.uid); DELETE FROM group_event_participants WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = group_event_participants.uid); DELETE FROM group_members WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = group_members.uid); +DELETE FROM axletter_ins WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = axletter_ins.uid); -- Following tables all refer to accounts.uid. ALTER TABLE account_auth_openid ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; @@ -62,7 +64,6 @@ ALTER TABLE watch_promo ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELET -- Following tables all refer to accounts.uid, but they use a different name. ALTER TABLE contacts ADD FOREIGN KEY (contact) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE email_list_moderate ADD FOREIGN KEY (mid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE gapps_accounts ADD FOREIGN KEY (l_userid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE gapps_queue ADD FOREIGN KEY (q_owner_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE gapps_queue ADD FOREIGN KEY (q_recipient_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/upgrade/1.0.1/08_profile_foreign_keys.sql b/upgrade/1.0.1/08_profile_foreign_keys.sql index 5b2cd06..b44ffc2 100644 --- a/upgrade/1.0.1/08_profile_foreign_keys.sql +++ b/upgrade/1.0.1/08_profile_foreign_keys.sql @@ -29,6 +29,7 @@ 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_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, diff --git a/upgrade/1.0.1/09_remaining_foreign_keys.sql b/upgrade/1.0.1/09_remaining_foreign_keys.sql index f30eaf9..0c0b4bc 100644 --- a/upgrade/1.0.1/09_remaining_foreign_keys.sql +++ b/upgrade/1.0.1/09_remaining_foreign_keys.sql @@ -15,6 +15,7 @@ ALTER TABLE group_event_participants MODIFY COLUMN item_id INT(11) UNSIGNED DEFA UPDATE groups SET dom = NULL WHERE dom = 0; UPDATE payments SET asso_id = NULL WHERE asso_id = 0; UPDATE newsletter_art SET cid = NULL WHERE cid = 0; +UPDATE newsletter_ins SET last = NULL WHERE last = 0; -- Deletes things that should have been deleted ealier. DELETE FROM newsletter_art WHERE NOT EXISTS (SELECT * FROM newsletter WHERE newsletter.id = newsletter_art.id); diff --git a/upgrade/1.0.1/12_profiles.sql b/upgrade/1.0.1/12_profiles.sql index 4a792f0..9a33797 100644 --- a/upgrade/1.0.1/12_profiles.sql +++ b/upgrade/1.0.1/12_profiles.sql @@ -7,7 +7,7 @@ CREATE TABLE profile_modifications ( oldText TEXT NOT NULL, newText TEXT NOT NULL, pub ENUM('private', 'ax', 'public') NOT NULL DEFAULT 'private', - PRIMARY KEY(pid, uid, field) + PRIMARY KEY(pid, uid, field), FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB, CHARSET=utf8; diff --git a/upgrade/1.0.1/update.sh b/upgrade/1.0.1/update.sh index 40f1278..d19e994 100755 --- a/upgrade/1.0.1/update.sh +++ b/upgrade/1.0.1/update.sh @@ -3,12 +3,11 @@ . ../inc/pervasive.sh ########################################################### -[ "$DATABASE" != "x5dat" ] || die "Cannot target x5dat" +[ "$DATABASE" != "x4dat" ] || die "Cannot target x4dat" copy_db echo "* switching engines to InnoDB" -(./innodb.sh | while read line; do mysql_exec "$line"; done) || die "ERROR" -echo "OK" +./innodb.sh | mysql_pipe confirm "* Running database upgrade scripts" mysql_run_directory . -- 2.1.4