ut-garbage-collect

#! /usr/bin/env php
<?php

/**
 * 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 = Gazelle\DB::DB();

// clean artists
$db->prepared_query("
    DELETE aha
    FROM artists_group ag
    INNER JOIN artists_alias aa ON (ag.PrimaryAlias = aa.AliasID)
    INNER JOIN artist_has_attr aha ON (aha.artist_id = ag.ArtistID)
    WHERE aa.Name like 'phpunit.%';
");
$db->prepared_query("
    DELETE ag, aa
    FROM artists_group ag
    INNER JOIN artists_alias aa ON (ag.PrimaryAlias = aa.AliasID)
    INNER JOIN artists_alias aa USING (ArtistID)
    WHERE aa.Name like 'phpunit.%';
");
$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 collages
$db->prepared_query("
    SELECT ID
    FROM collages
    WHERE Name regexp '^(phpunit collage (?:ajax|comment|report) )'
");

$collageMan = new Gazelle\Manager\Collage;
foreach ($db->collect(0, false) 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 Gazelle\Manager\Request;
foreach ($db->collect(0, false) 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 '
");

$forMan = new Gazelle\Manager\Forum;
foreach ($db->collect(0, false) 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 g.ID as gid, t.ID as tid
    FROM torrents_group g
    LEFT JOIN torrents t on (t.GroupID = g.ID)
    WHERE g.Name regexp '^(?:phpunit (?:category new|collfree|notify|reaper|request|seedbox|torman) [a-z]{6}|php split title [a-z]{10}|Some [a-z]{8} songs)$'
");
$groupList   = $db->collect('gid', false);
$torrentList = $db->collect('tid', false);

$torMan = new Gazelle\Manager\Torrent;
foreach ($torrentList as $torrentId) {
    $torrent = $torMan->findById((int)$torrentId);
    if ($torrent) {
        echo "torrent $torrentId\n";
        $torrent->remove(null, 'garbage collection', -1);
    }
}
$tgMan = new Gazelle\Manager\TGroup;
foreach ($groupList as $tgroupId) {
    $tgroup = $tgMan->findById($tgroupId);
    if ($tgroup) {
        echo "tgroup $torrentId ({$tgroup->name()})\n";
        $tgroup->remove(new Gazelle\User(1));
    }
}

// clean collages

$db->prepared_query("
    SELECT ID
    FROM collages c
    WHERE c.Name regexp '^phpunit freeleech '
");
$collMan = new Gazelle\Manager\Collage;

foreach ($db->collect(0, false) as $collageId) {
    $collage = $collMan->findById($collageId);
    echo "collage {$collage->id()} ({$collage->name()})\n";
    $collage->hardRemove();
}

// and finally, clean users

(new Gazelle\DB)->relaxConstraints(true);

$db->prepared_query("
    SELECT um.ID
    FROM users_main um
    WHERE um.Username regexp '^(?:(?:admin|collage|create|donor|download|inbox\.(?:recv|send)|forum|invitee?|mod|tgroup\.[au]|u(?:[1-3]|pload|ser(?:hist)?))\.[a-z]{6}|tag\.[a-z]{8}|(?:admin|art(?:2|ist|y)|backlog|blog|collfree|comment|enc\.med|feat|invite|mod|new\.grp|re(?:aper|clab|g[1-5]|lease|portg?|q)|sbox|tag(?:no|2(?:no|yes))|text|tgcat|token|torman|trk(?:down|free)|um[123]|uploader|user|year|xva)\.[a-z]{10}|notf(?:\.(new|enc))?\.[^.]+)$'
");
$userMan = new Gazelle\Manager\User;

foreach ($db->collect(0, false) 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) {}
    }
}

$db->prepared_query("
    DELETE uf
    FROM users_freeleeches uf
    LEFT JOIN users_main um ON (um.ID = uf.UserID)
    WHERE um.ID IS NULL
");