From c187cb0a45231b91018b318cd6494123fa4e221a Mon Sep 17 00:00:00 2001
From: Hugues Hiegel <hugues@hiegel.fr>
Date: Mon, 10 May 2010 17:36:26 +0200
Subject: [DataBase] little cleanup

---
 DataBase/cleanup.mysql                | 14 ++++++++++++++
 DataBase/remove-badges-variants.mysql | 32 ++++++++++++++++++++++++++++++++
 DataBase/show-variants.mysql          | 23 +++++++++++++++++++++++
 3 files changed, 69 insertions(+)
 create mode 100644 DataBase/cleanup.mysql
 create mode 100644 DataBase/remove-badges-variants.mysql
 create mode 100644 DataBase/show-variants.mysql

(limited to 'DataBase')

diff --git a/DataBase/cleanup.mysql b/DataBase/cleanup.mysql
new file mode 100644
index 0000000..b0ad464
--- /dev/null
+++ b/DataBase/cleanup.mysql
@@ -0,0 +1,14 @@
+USE lastfm
+
+CREATE TEMPORARY TABLE users_having_a_badge (SELECT username, 0 AS has_a_badge FROM users) ;
+CREATE TEMPORARY TABLE badges_having_a_user (SELECT username, 0 AS has_a_user FROM badges) ;
+
+UPDATE users_having_a_badge, badges SET has_a_badge = 1 WHERE users_having_a_badge.username = badges.username ;
+UPDATE badges_having_a_user, users SET has_a_user = 1 WHERE badges_having_a_user.username = users.username ;
+
+DELETE users.* FROM users, users_having_a_badge where has_a_badge = 0 AND users.username = users_having_a_badge.username ;
+SELECT badges.png FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ;
+DELETE badges.* FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ;
+
+DROP TABLE users_having_a_badge;
+DROP TABLE badges_having_a_user;
diff --git a/DataBase/remove-badges-variants.mysql b/DataBase/remove-badges-variants.mysql
new file mode 100644
index 0000000..3f29d2a
--- /dev/null
+++ b/DataBase/remove-badges-variants.mysql
@@ -0,0 +1,32 @@
+
+USE lastfm
+
+
+/**
+ * Deletes all entries in badges
+ * for each unique username + type
+ * where more than one style or color exists
+ */
+
+DROP TABLE IF EXISTS tmp ;
+CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
+
+SELECT badges.png
+	FROM badges, tmp
+	WHERE badges.username = tmp.username
+	AND ( (badges.type = tmp.type AND tmp.count > 1)
+	   OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%"))
+		 AND badges.type != tmp.type )
+	   ) ;
+
+DELETE badges.*
+	FROM badges, tmp
+	WHERE badges.username = tmp.username
+	AND ( (badges.type = tmp.type AND tmp.count > 1)
+	   OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%"))
+		 AND badges.type != tmp.type )
+	   ) ;
+
+
+DROP TABLE tmp ;
+
diff --git a/DataBase/show-variants.mysql b/DataBase/show-variants.mysql
new file mode 100644
index 0000000..3eeeeac
--- /dev/null
+++ b/DataBase/show-variants.mysql
@@ -0,0 +1,23 @@
+
+USE lastfm
+
+
+/**
+ * Deletes all entries in badges
+ * for each unique username + type
+ * where more than one style or color exists
+ */
+
+DROP TABLE IF EXISTS tmp ;
+CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
+
+SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, tmp.count
+	FROM badges, tmp
+	WHERE badges.username = tmp.username
+	AND ( (badges.type = tmp.type AND tmp.count > 1)
+	   OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%"))
+		 AND badges.type != tmp.type )
+	   ) ;
+
+DROP TABLE tmp ;
+
-- 
cgit v1.2.3