From c187cb0a45231b91018b318cd6494123fa4e221a Mon Sep 17 00:00:00 2001 From: Hugues Hiegel 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 +++++++++++++++++++++++ cleanup.mysql | 14 -------------- remove-badges-variants.mysql | 32 -------------------------------- show-variants.mysql | 23 ----------------------- 6 files changed, 69 insertions(+), 69 deletions(-) create mode 100644 DataBase/cleanup.mysql create mode 100644 DataBase/remove-badges-variants.mysql create mode 100644 DataBase/show-variants.mysql delete mode 100644 cleanup.mysql delete mode 100644 remove-badges-variants.mysql delete mode 100644 show-variants.mysql 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 ; + diff --git a/cleanup.mysql b/cleanup.mysql deleted file mode 100644 index b0ad464..0000000 --- a/cleanup.mysql +++ /dev/null @@ -1,14 +0,0 @@ -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/remove-badges-variants.mysql b/remove-badges-variants.mysql deleted file mode 100644 index 3f29d2a..0000000 --- a/remove-badges-variants.mysql +++ /dev/null @@ -1,32 +0,0 @@ - -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/show-variants.mysql b/show-variants.mysql deleted file mode 100644 index 3eeeeac..0000000 --- a/show-variants.mysql +++ /dev/null @@ -1,23 +0,0 @@ - -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