summaryrefslogtreecommitdiff
path: root/DataBase/cleanup.mysql
blob: 0fb94e98b8a19995e2600b02259d68653b773749 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
USE lastfm

/**
 * Deletes all entries in badges
 * for each unique username + type
 * where more than one style or color exists
 */
CREATE TEMPORARY TABLE duplicates (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
DELETE badges.*
	FROM badges, duplicates
	WHERE badges.username = duplicates.username
	AND badges.hits = 1
	AND ( (badges.type = duplicates.type AND duplicates.count > 1)
	   OR ( (badges.type LIKE concat(duplicates.type,"%") OR duplicates.type LIKE concat(badges.type,"%"))
		 AND badges.type != duplicates.type )
	   ) ;

/**
 * Delete orphaned users and orphaned badges
 */
CREATE TEMPORARY TABLE users_without_badge (SELECT users.username FROM users LEFT JOIN badges ON users.username = badges.username WHERE badges.username IS NULL) ;
CREATE TEMPORARY TABLE badges_without_user (SELECT badges.username FROM badges LEFT JOIN users ON badges.username = users.username WHERE users.username IS NULL) ;

DELETE users.* FROM users, users_without_badge where users.username = users_without_badge.username ;
DELETE badges.* FROM badges, badges_without_user where badges.username = badges_without_user.username ;