diff options
| -rw-r--r-- | DataBase/COLORS | 8 | ||||
| -rw-r--r-- | DataBase/STYLES | 34 | ||||
| -rw-r--r-- | DataBase/TYPES | 11 | ||||
| -rw-r--r-- | DataBase/cleanup.mysql | 30 | ||||
| -rw-r--r-- | DataBase/color.plot | 8 | ||||
| -rw-r--r-- | DataBase/remove-badges-variants.mysql | 32 | ||||
| -rw-r--r-- | DataBase/show-variants.mysql | 17 | ||||
| -rw-r--r-- | DataBase/style.plot | 35 | ||||
| -rw-r--r-- | DataBase/type.plot | 96 |
9 files changed, 28 insertions, 243 deletions
diff --git a/DataBase/COLORS b/DataBase/COLORS deleted file mode 100644 index ef75989..0000000 --- a/DataBase/COLORS +++ /dev/null @@ -1,8 +0,0 @@ -Black -Red -Green -Yellow -Blue -LightBlue -Gray -White diff --git a/DataBase/STYLES b/DataBase/STYLES deleted file mode 100644 index bb7b864..0000000 --- a/DataBase/STYLES +++ /dev/null @@ -1,34 +0,0 @@ -Astonished -Broken -Curly -DirtyEgo -DisgustingBehaviour -DownCome -Elegant -FaceLift -Flamy -Funny -Girlie -Grindy -Guilty -Horrorful -HorsePuke -Letters -MaxRhodes -MemoryLapses -MisProject -Modern -Morpheus -Nails -Nasty -Pastelaria -Porcelain -PrintError -Rochester -Romantic -Ruritania -Screamy -Selfish -Shortcut -Simple -TypeO diff --git a/DataBase/TYPES b/DataBase/TYPES deleted file mode 100644 index e73ff39..0000000 --- a/DataBase/TYPES +++ /dev/null @@ -1,11 +0,0 @@ -TracksPerDay -TracksPerWeek -TracksPerMonth -TotalTracks -AlbumsPerDay -AlbumsPerWeek -AlbumsPerMonth -TotalAlbums -Since -Trueness -Trueness diff --git a/DataBase/cleanup.mysql b/DataBase/cleanup.mysql index b0ad464..aa51af5 100644 --- a/DataBase/cleanup.mysql +++ b/DataBase/cleanup.mysql @@ -1,14 +1,26 @@ 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) ; +/** + * 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.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 ) + ) ; -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 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 ; -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/color.plot b/DataBase/color.plot deleted file mode 100644 index 296bc49..0000000 --- a/DataBase/color.plot +++ /dev/null @@ -1,8 +0,0 @@ -1 "Black" 90477 -2 "Red" 21473 -3 "Blue" 6572 -4 "Green" 4925 -5 "LightBlue" 4811 -6 "Gray" 1445 -7 "Yellow" 1187 -8 "White" 187 diff --git a/DataBase/remove-badges-variants.mysql b/DataBase/remove-badges-variants.mysql deleted file mode 100644 index 3f29d2a..0000000 --- a/DataBase/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/DataBase/show-variants.mysql b/DataBase/show-variants.mysql index 3eeeeac..8909494 100644 --- a/DataBase/show-variants.mysql +++ b/DataBase/show-variants.mysql @@ -8,16 +8,13 @@ USE lastfm * 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 ) ; +CREATE TEMPORARY TABLE variants (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 ) +SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, variants.count + FROM badges, variants + WHERE badges.username = variants.username + AND ( (badges.type = variants.type AND variants.count > 1) + OR ( (badges.type LIKE concat(variants.type,"%") OR variants.type LIKE concat(badges.type,"%")) + AND badges.type != variants.type ) ) ; -DROP TABLE tmp ; - diff --git a/DataBase/style.plot b/DataBase/style.plot deleted file mode 100644 index 81831dc..0000000 --- a/DataBase/style.plot +++ /dev/null @@ -1,35 +0,0 @@ -1 "TypeO" 37938 -2 "Modern" 12504 -3 "Letters" 11102 -4 "Morpheus" 10547 -5 "Horrorful" 9771 -6 "Grindy" 9146 -7 "Simple" 8242 -8 "Screamy" 6994 -9 "Romantic" 6626 -10 "Elegant" 4137 -11 "Ruritania" 3472 -12 "Funny" 2936 -13 "Girlie" 2630 -14 "FaceLift" 2036 -15 "Curly" 1532 -16 "Flamy" 1059 -17 "Astonished" 147 -18 "Broken" 81 -19 "DirtyEgo" 44 -20 "PrintError" 15 -21 "HorsePuke" 15 -22 "DisgustingBehaviour" 15 -23 "Porcelain" 12 -24 "DownCome" 11 -25 "MisProject" 11 -26 "Pastelaria" 10 -27 "Nasty" 8 -28 "Nails" 8 -29 "Guilty" 7 -30 "" 7 -31 "Shortcut" 4 -32 "Selfish" 3 -33 "MaxRhodes" 3 -34 "Rochester" 2 -35 "MemoryLapses" 2 diff --git a/DataBase/type.plot b/DataBase/type.plot deleted file mode 100644 index 47ffb26..0000000 --- a/DataBase/type.plot +++ /dev/null @@ -1,96 +0,0 @@ -1 "TracksPerDay" 16559 -2 "Trueness" 16204 -3 "Since" 11123 -4 "TotalAlbums" 10409 -5 "TracksPerWeek" 10249 -6 "TotalTracks" 8558 -7 "TracksPerMonth" 8155 -8 "AlbumsPerDay" 6759 -9 "PerDay" 6221 -10 "AlbumsPerWeek" 5075 -11 "PerWeek" 5028 -12 "AlbumsPerMonth" 4885 -13 "PerMonth" 4091 -14 "Total2" 3886 -15 "Total" 3687 -16 "PerDay2" 2532 -17 "Trueness2" 2198 -18 "PerWeek2" 2191 -19 "PerMonth2" 1948 -20 "Since2" 1231 -21 "5" -22 "TracksPerYear" 3 -23 "Monthly" 2 -24 "NotYourDoll" 2 -25 "Trueness1" 2 -26 "InsaneGenius" 2 -27 "Daily" 2 -28 "Bashenko" 2 -29 "OLYCOM" 1 -30 "engelmeine" 1 -31 "Totalsongs" 1 -32 "Utworow" 1 -33 "Noodle-CAT" 1 -34 "creamy-r0ck" 1 -35 "TracksserYear" 1 -36 "Wrig" 1 -37 "TracksProTag" 1 -38 "Mastodon118" 1 -39 "buscemigiuseppe" 1 -40 "unoriginal42" 1 -41 "Daily" tracks -42 "jiajuNz" 1 -43 "benni89" 1 -44 "TracksDay" 1 -45 "tiigrikucu" 1 -46 "pocobelle" 1 -47 "IanAR" 1 -48 "Placebo" 1 -49 "Since1" 1 -50 "ssdlpf" 1 -51 "NeverSmile" 1 -52 "Deinvadda" 1 -53 "SSS" 1 -54 "TracksPeray" 1 -55 "razzamataz007" 1 -56 "laykanfighter" 1 -57 "dannyshaw123" 1 -58 "WeeklyTracks" 1 -59 "Pusch" 1 -60 "koji7" 1 -61 "beelzbubba" 1 -62 "TracksWeek" 1 -63 "Opooz" 1 -64 "gugusse" 1 -65 "--UncleSam--" 1 -66 "PerWeek1" 1 -67 "Denkmalvor" 1 -68 "xxprettyinmetal" 1 -69 "Muschelsucher" 1 -70 "C4lyps0" 1 -71 "wnnrssn" 1 -72 "Zzz" 1 -73 "Makeyo" 1 -74 "Blasphemia" 1 -75 "Треков" 1 -76 "treeny" 1 -77 "sc0ttmst" 1 -78 "-f-r-e-a-k-" 1 -79 "Morpheus" 1 -80 "the_jelly_fish" 1 -81 "PeopleEveryday" 1 -82 "haanka" 1 -83 "untrueness" 1 -84 "candle94" 1 -85 "Slayer_Soul" 1 -86 "nemoflow" 1 -87 "decadent_sluts" 1 -88 "Horrorful" 1 -89 "TC14" 1 -90 "rainstormiee" 1 -91 "Lady_Croft_90" 1 -92 "CtG" 1 -93 "TracksAlDÃa" 1 -94 "panopticon6" 1 -95 "Hell_RoB" 1 -96 "Total1" 1 |