Support:Database Cleanup: Difference between revisions
Jump to navigation
Jump to search
Content deleted Content added
imported>Hendrik Brummermann No edit summary |
imported>Hendrik Brummermann |
||
| (74 intermediate revisions by 2 users not shown) | |||
| Line 11: | Line 11: | ||
SELECT null FROM rpobject WHERE characters.object_id=rpobject.object_id |
SELECT null FROM rpobject WHERE characters.object_id=rpobject.object_id |
||
) ORDER BY timedate; |
) ORDER BY timedate; |
||
</source> |
|||
Consider: |
|||
<source lang="sql"> |
|||
SELECT * FROM characters |
|||
LEFT JOIN rpobject ON characters.object_id=rpobject.object_id |
|||
WHERE rpobject.object_id IS NULL |
|||
ORDER BY timedate; |
|||
</source> |
</source> |
||
| Line 19: | Line 26: | ||
SELECT null FROM characters WHERE player_id=account.id |
SELECT null FROM characters WHERE player_id=account.id |
||
) AND status='active'; |
) AND status='active'; |
||
</source> |
|||
Consider: |
|||
<source lang="sql"> |
|||
SELECT account.* FROM account LEFT JOIN characters |
|||
ON player_id=account.id |
|||
where account.STATUS='active' and player_id is null; |
|||
</source> |
</source> |
||
== Metrics for discontinuted accounts == |
== Metrics for discontinuted accounts == |
||
* Must have no entries in gameEvents (2008-06-30, 2009-08-17), itemlog (2008-03-24), accountban |
* Must have no entries in gameEvents (2008-06-30, 2009-08-17), itemlog (2008-03-24), kills (2010-01-08), accountban |
||
* Remember to clean additional tables, character_stats, accountLink, ... |
* Remember to clean additional tables, character_stats, accountLink, ... |
||
* Criteria |
* Criteria |
||
| Line 30: | Line 43: | ||
** no valuable items |
** no valuable items |
||
** <del>No other characters on that account that are not candidates</del> Multi character support is younger than 2008-06-30. |
** <del>No other characters on that account that are not candidates</del> Multi character support is younger than 2008-06-30. |
||
=== Cleanup Script === |
|||
<source lang="sql"> |
|||
INSERT INTO del2 SELECT charname FROM del WHERE items='0 leather_armor, 0 club'; |
|||
INSERT INTO del2 SELECT charname FROM del WHERE items='0 club, 0 leather_armor'; |
|||
INSERT INTO del2 SELECT charname FROM del WHERE items='0 leather armor, 0 club'; |
|||
INSERT INTO del2 SELECT charname FROM del WHERE items='0 club, 0 leather armor'; |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents WHERE charname=source); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2008_08_21 WHERE charname=source); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2009_02_19 WHERE charname=source); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2009_08_17 WHERE charname=source); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM itemlog WHERE charname = source); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killed = charname AND killed_type='P'); |
|||
DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killer = charname AND killer_type='P'); |
|||
CREATE TABLE del3 (charname VARCHAR(50), PRIMARY KEY(charname)); |
|||
-- check |
|||
INSERT INTO del3 |
|||
SELECT c3.charname FROM characters As c3, |
|||
(SELECT account.id As account_id FROM account, del2, characters WHERE account.username=del2.charname AND account.id=player_id |
|||
AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname) |
|||
GROUP BY username |
|||
HAVING count(*) > 1) As x |
|||
WHERE x.account_id = c3.player_id; |
|||
INSERT INTO del3 |
|||
SELECT c3.charname FROM characters As c3, |
|||
(SELECT account.id As account_id, count(*) |
|||
FROM account, del2, characters As c1, characters As c2 |
|||
WHERE account.id=c1.player_id AND c1.charname=del2.charname AND account.id=c2.player_id |
|||
AND NOT EXISTS ( |
|||
SELECT null FROM del2 As t WHERE t.charname = c2.charname) |
|||
GROUP BY account_id |
|||
HAVING count(*) > 1) As x |
|||
WHERE x.account_id = c3.player_id; |
|||
DELETE FROM del2 USING del2, del3 WHERE del2.charname = del3.charname; |
|||
DROP TABLE del3; |
|||
</source> |
|||
=== Delete === |
|||
<source lang="sql"> |
|||
DELETE FROM reached_achievement USING reached_achievement, del2 WHERE reached_achievement.charname = del2.charname; |
|||
DELETE FROM postman USING postman, del2 WHERE source = del2.charname; |
|||
DELETE FROM postman USING postman, del2 WHERE target = del2.charname; |
|||
DELETE FROM buddy USING buddy, del2 WHERE buddy.charname = del2.charname; |
|||
DELETE FROM buddy USING buddy, del2 WHERE buddy.buddy = del2.charname; |
|||
DELETE FROM character_stats USING character_stats, del2 WHERE name = del2.charname; |
|||
UPDATE halloffame, del2 SET halloffame.charname = concat('del.2012-05-06.', halloffame.charname) WHERE del2.charname=halloffame.charname; |
|||
UPDATE halloffame_archive_alltimes, del2 SET halloffame_archive_alltimes.charname = concat('del.2012-05-06.', halloffame_archive_alltimes.charname) WHERE del2.charname=halloffame_archive_alltimes.charname; |
|||
UPDATE halloffame_archive_recent, del2 SET halloffame_archive_recent.charname = concat('del.2012-05-06.', halloffame_archive_recent.charname) WHERE del2.charname=halloffame_archive_recent.charname; |
|||
UPDATE halloffame_archive, del2 SET halloffame_archive.charname = concat('del.2012-05-06.', halloffame_archive.charname) WHERE del2.charname=halloffame_archive.charname; |
|||
UPDATE cid, del2 SET cid.charname = concat('del.2012-05-06.', cid.charname) WHERE del2.charname=cid.charname; |
|||
-- TODO account |
|||
UPDATE characters, del2 SET characters.status='deleted' AND characters.charname = concat('del.2012-05-06.', characters.charname) WHERE del2.charname=characters.charname; |
|||
</source> |
|||