mirror of
https://github.com/OPSnet/Gazelle.git
synced 2026-01-16 18:04:34 -05:00
299 lines
8.7 KiB
PHP
Executable File
299 lines
8.7 KiB
PHP
Executable File
#! /usr/bin/env php
|
|
<?php
|
|
|
|
namespace Gazelle;
|
|
|
|
/**
|
|
* Sometimes, unit tests crash and burn and leave garbage behind in the database.
|
|
* This program will clean out all known test entries. Any remaining entries are
|
|
* either unknown to the unit tests, or the queries here do not identify them.
|
|
*/
|
|
|
|
require_once(__DIR__ . '/../lib/bootstrap.php');
|
|
|
|
$db = DB::DB();
|
|
|
|
$db->prepared_query("SHOW TABLES LIKE 'phpunit_%'");
|
|
$tableList = $db->collect(0);
|
|
foreach($tableList as $t) {
|
|
$db->prepared_query("DROP TABLE $t");
|
|
}
|
|
|
|
// clean artists
|
|
|
|
$db->prepared_query("SET foreign_key_checks = 0");
|
|
$db->prepared_query("
|
|
DELETE aa, ag
|
|
FROM artists_alias aa
|
|
INNER JOIN artists_group ag on (
|
|
ag.ArtistID = aa.ArtistID
|
|
AND ag.PrimaryAlias = aa.AliasID
|
|
)
|
|
WHERE aa.name REGEXP '^(phpunit (?:req|artist merge) |(?:qqqq|qqqb|qqbb|qbbb|bbbb)\.phpunit\.)[a-z]{6}$'
|
|
");
|
|
$db->prepared_query("
|
|
DELETE aa, ag
|
|
FROM artists_alias aa
|
|
INNER JOIN artists_group ag on (
|
|
ag.ArtistID = aa.ArtistID
|
|
AND ag.PrimaryAlias = aa.AliasID
|
|
)
|
|
WHERE aa.name REGEXP '^(phpunit\.|Tag Girl |Notify Man )'
|
|
");
|
|
$db->prepared_query("
|
|
DELETE FROM artists_alias
|
|
WHERE Name REGEXP '^(?:phpunit\.|new artist |Tracker Girl )';
|
|
");
|
|
|
|
$db->prepared_query("SET foreign_key_checks = 1");
|
|
|
|
$db->prepared_query("
|
|
DELETE FROM artists_alias aa
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM artists_group ag WHERE ag.artistid = aa.artistid
|
|
)
|
|
");
|
|
$db->prepared_query("
|
|
DELETE FROM bookmarks_artists ba WHERE NOT EXISTS(
|
|
SELECT 1 FROM artists_group ag WHERE ag.ArtistID = ba.ArtistID
|
|
)
|
|
");
|
|
$db->prepared_query("
|
|
CREATE TEMPORARY TABLE orphan_similar (SimilarID int not null primary key)
|
|
");
|
|
$db->prepared_query("
|
|
INSERT INTO orphan_similar
|
|
SELECT DISTINCT sim.similarid
|
|
FROM artists_similar sim
|
|
LEFT JOIN artists_group ag USING (artistid)
|
|
WHERE ag.artistid IS NULL;
|
|
");
|
|
$db->prepared_query("
|
|
DELETE ss
|
|
FROM artists_similar_scores ss
|
|
INNER JOIN orphan_similar os USING (SimilarID)
|
|
");
|
|
|
|
// clean reports
|
|
$db->prepared_query("
|
|
DELETE FROM reports
|
|
WHERE Type = 'request'
|
|
AND Reason = 'phpunit report'
|
|
");
|
|
$db->prepared_query("
|
|
DELETE r
|
|
FROM reports r
|
|
LEFT JOIN users_main um ON (um.ID = r.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
|
|
// clean wiki
|
|
$db->prepared_query("
|
|
DELETE FROM wiki_aliases WHERE Alias REGEXP '^(phpunittitle|wikieditreadable)'
|
|
");
|
|
$db->prepared_query("
|
|
DELETE FROM wiki_revisions WHERE Title LIKE 'phpunit title %';
|
|
");
|
|
$db->prepared_query("
|
|
DELETE FROM wiki_articles WHERE Title REGEXP '^(wiki edit readable|phpunit title) '
|
|
");
|
|
|
|
$userMan = new Manager\User();
|
|
$collageMan = new Manager\Collage();
|
|
$collageMan->requestContext()->setViewer(
|
|
$userMan->findById(
|
|
(int)$db->scalar('
|
|
SELECT um.id
|
|
FROM users_main um
|
|
INNER JOIN permissions p on (p.ID = um.PermissionID)
|
|
ORDER BY p.level DESC
|
|
LIMIT 1
|
|
')
|
|
)
|
|
);
|
|
|
|
// clean collages
|
|
$db->prepared_query("
|
|
SELECT ID
|
|
FROM collages
|
|
WHERE Name regexp '^(?:phpunit (?:collage (?:ajax|artist|comment|contrib|personal|report)|merge artist|search report) )'
|
|
");
|
|
foreach ($db->collect(0) as $collageId) {
|
|
$collage = $collageMan->findById($collageId);
|
|
echo "collage {$collage->id()} ({$collage->name()})\n";
|
|
$collage->hardRemove();
|
|
}
|
|
|
|
// clean requests
|
|
$db->prepared_query("
|
|
SELECT ID
|
|
FROM requests
|
|
WHERE Title regexp '^(?:The [a-z]{6} Test Sessions|phpunit request(?: report)?)$'
|
|
");
|
|
|
|
$reqMan = new Manager\Request();
|
|
foreach ($db->collect(0) as $requestId) {
|
|
$request = $reqMan->findById($requestId);
|
|
echo "request {$request->id()} ({$request->title()})\n";
|
|
$request->remove();
|
|
}
|
|
|
|
// clean forums
|
|
$db->prepared_query("
|
|
SELECT ID
|
|
FROM forums
|
|
WHERE Name regexp '^(phpunit |forum .{6}$)'
|
|
");
|
|
|
|
$forMan = new Manager\Forum();
|
|
foreach ($db->collect(0) as $forumId) {
|
|
$forum = $forMan->findById($forumId);
|
|
echo "forum {$forum->id()} ({$forum->name()})\n";
|
|
$db->prepared_query("
|
|
DELETE fp, ft
|
|
FROM forums_posts fp
|
|
INNER JOIN forums_topics ft ON (ft.ID = fp.TopicID)
|
|
WHERE ft.ForumID = ?
|
|
", $forum->id()
|
|
);
|
|
$forum->remove();
|
|
}
|
|
$forMan->flushToc();
|
|
|
|
// clean torrents and torrent groups
|
|
$db->prepared_query("
|
|
SELECT tg.ID as gid, t.ID as tid
|
|
FROM torrents_group tg
|
|
LEFT JOIN torrents t on (t.GroupID = tg.ID)
|
|
WHERE tg.Name regexp '^(?:phpunit (?:category new|collfree|live in|notify|reaper|request|seedbox|tgvote|torman|torrent) [a-z]{6}|(?:php(?: split title|unit artist (?:autocomp|merge[12]))|tracker) [a-z]{10}|Some (?:(?:boppy|live|more) )?[a-z]{8} (?:contest )?songs)$'
|
|
");
|
|
$groupList = $db->collect('gid');
|
|
$torrentList = $db->collect('tid');
|
|
|
|
$torMan = new Manager\Torrent();
|
|
foreach ($torrentList as $torrentId) {
|
|
$torrent = $torMan->findById((int)$torrentId);
|
|
if ($torrent) {
|
|
echo "torrent $torrentId\n";
|
|
try {
|
|
$torrent->removeTorrent(null, 'garbage collection', -1);
|
|
} catch (\Exception) {
|
|
}
|
|
}
|
|
}
|
|
|
|
$tgMan = new Manager\TGroup();
|
|
foreach ($groupList as $tgroupId) {
|
|
$tgroup = $tgMan->findById($tgroupId);
|
|
if ($tgroup instanceof TGroup) {
|
|
try {
|
|
echo "tgroup $torrentId ({$tgroup?->name()})\n";
|
|
$db->prepared_query("
|
|
DELETE FROM bookmarks_torrents bt WHERE GroupID = ?
|
|
", $tgroupId
|
|
);
|
|
$tgroup->remove(new User(1));
|
|
} catch (\Error|\Exception) {
|
|
}
|
|
}
|
|
}
|
|
|
|
// and finally, clean users
|
|
|
|
(new DB())->relaxConstraints(true);
|
|
|
|
$db->prepared_query("
|
|
SELECT um.ID
|
|
FROM users_main um
|
|
WHERE um.Username regexp '^(?:(?:(?:admin|base|bb|bo(?:nus(?:g|r|stat))?|collage|contest|create|donor|download|error|inbox\.(?:recv|send)|forum|friend[1-3]|invitee?|logchecker|mod|priv[12]|sitelog|stats|tag|text|tgroup\.[anu]|trk(?:fl)?|twig|u(?:[1-3]|pload|ser(?:hist)?)|wiki)\.[a-z]{6}|tag\.[a-z]{8}|(?:admin|art(?:2|ist|y)|backlog|blog|collfree|comment|dnu|dupip|email[1-3]?|enc\.med|feat|fls|geodist|invite|ipv4|merge|mfa|mod|nav|new(?:\.grp|s)|ord|phpunit|re(?:aper|clab|g[1-5]|lease|portg?|q)|sbox|searchrep|spec|stats|tag(?:no|2(?:no|yes))?|text|tg(?:cat|vote|man)|token|torman|torrent|trk(?:down|fl|free)?|uat|ul[0-3]|um[1-4]|uploader|user|year|xva)\.[a-z]{10}|notf(?:\.(new|enc))?\.[^.]+)|spm_(?:fls|mod|sysop|user)_[a-z]{10})$'
|
|
");
|
|
foreach ($db->collect(0) as $userId) {
|
|
$user = $userMan->findById($userId);
|
|
$db->prepared_query("DELETE FROM forums_topics WHERE LastPostAuthorID = ?", $userId);
|
|
$db->prepared_query("DELETE FROM locked_accounts WHERE UserID = ?", $userId);
|
|
$db->prepared_query("DELETE FROM users_stats_daily WHERE UserID = ?", $userId);
|
|
$db->prepared_query("DELETE FROM users_stats_monthly WHERE UserID = ?", $userId);
|
|
$db->prepared_query("DELETE FROM users_stats_yearly WHERE UserID = ?", $userId);
|
|
if ($user) {
|
|
try {
|
|
echo "user {$user->label()}\n";
|
|
$user->remove();
|
|
} catch (\Error $e) {
|
|
echo " fail {$e->getMessage()}\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM bookmarks_artists u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM bookmarks_collages u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM bookmarks_requests u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM bookmarks_torrents u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_freeleeches u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_history_ips u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM user_last_access_delta u
|
|
LEFT JOIN users_main um ON (um.ID = u.user_id)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_leech_stats u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_stats_daily u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_stats_monthly u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_stats_yearly u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|
|
$db->prepared_query("
|
|
DELETE u
|
|
FROM users_info u
|
|
LEFT JOIN users_main um ON (um.ID = u.UserID)
|
|
WHERE um.ID IS NULL
|
|
");
|