From 80ea68bd56df9e85011b4783dbf5aa62fd293795 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Mon, 7 Mar 2011 15:40:48 +0100 Subject: [PATCH] Deletes lost addresses, medals and phones. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- upgrade/1.1.0/12_clean_db.sql | 53 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 53 insertions(+) create mode 100644 upgrade/1.1.0/12_clean_db.sql diff --git a/upgrade/1.1.0/12_clean_db.sql b/upgrade/1.1.0/12_clean_db.sql new file mode 100644 index 0000000..1eecf4f --- /dev/null +++ b/upgrade/1.1.0/12_clean_db.sql @@ -0,0 +1,53 @@ +-- Deletes lost addresses, medals and phones. +DELETE a + FROM profile_addresses AS a + WHERE a.type = 'home' AND NOT EXISTS (SELECT * + FROM profiles AS p + WHERE a.pid = p.pid); +DELETE a + FROM profile_addresses AS a + WHERE a.type = 'job' AND NOT EXISTS (SELECT * + FROM profile_job AS j + WHERE a.pid = j.pid AND a.id = j.id); +DELETE a + FROM profile_addresses AS a + WHERE a.type = 'hq' AND NOT EXISTS (SELECT * + FROM profile_job_enum AS je + WHERE a.jobid = je.id); +DELETE a + FROM profile_addresses AS a + WHERE a.type = 'group' AND NOT EXISTS (SELECT * + FROM groups AS g + WHERE a.groupid = g.id); +DELETE m + FROM profile_medals AS m + WHERE m.gid != 0 AND NOT EXISTS (SELECT * + FROM profile_medal_grade_enum AS mg + WHERE m.mid = mg.mid AND m.gid = mg.gid); +DELETE p + FROM profile_phones AS p + WHERE p.link_type = 'address' AND NOT EXISTS (SELECT * + FROM profile_addresses AS a + WHERE p.pid = a.pid AND p.link_id = a.id); +DELETE p + FROM profile_phones AS p + WHERE p.link_type = 'pro' AND NOT EXISTS (SELECT * + FROM profile_job AS j + WHERE p.pid = j.pid AND p.link_id = j.id); +DELETE p + FROM profile_phones AS p + WHERE p.link_type = 'user' AND NOT EXISTS (SELECT * + FROM profiles AS pf + WHERE p.pid = pf.pid); +DELETE p + FROM profile_phones AS p + WHERE p.link_type = 'hq' AND NOT EXISTS (SELECT * + FROM profile_job_enum AS je + WHERE p.link_id = je.id); +DELETE p + FROM profile_phones AS p + WHERE p.link_type = 'group' AND NOT EXISTS (SELECT * + FROM groups AS g + WHERE p.link_id = g.id); + +-- vim:set syntax=mysql: -- 2.1.4