/**
* Test a single collection regex for a group name.
*
* Requires table per group to be on.
*
* @param string $groupName
* @param string $regex
* @param int $limit
*
* @return array
*/
public function testCollectionRegex($groupName, $regex, $limit)
{
$groups = new Groups(['Settings' => $this->pdo]);
$groupID = $groups->getIDByName($groupName);
if (!$groupID) {
return [];
}
$tableNames = $groups->getCBPTableNames(true, $groupID);
$rows = $this->pdo->query(sprintf('SELECT
b.name, b.totalparts, b.currentparts, HEX(b.binaryhash) AS binaryhash,
c.fromname, c.collectionhash
FROM %s b
INNER JOIN %s c ON c.id = b.collection_id', $tableNames['bname'], $tableNames['cname']));
$data = [];
if ($rows) {
$limit--;
$hashes = [];
foreach ($rows as $row) {
if (preg_match($regex, $row['name'], $matches)) {
ksort($matches);
$string = $string2 = '';
foreach ($matches as $key => $match) {
if (!is_int($key)) {
$string .= $match;
$string2 .= '<br/>' . $key . ': ' . $match;
}
}
$files = 0;
if (preg_match('/[[(\\s](\\d{1,5})(\\/|[\\s_]of[\\s_]|-)(\\d{1,5})[])\\s$:]/i', $row['name'], $fileCount)) {
$files = $fileCount[3];
}
$newCollectionHash = sha1($string . $row['fromname'] . $groupID . $files);
$data['New hash: ' . $newCollectionHash . $string2][$row['binaryhash']] = ['file_name' => $row['name'], 'file_total_parts' => $row['totalparts'], 'file_current_parts' => $row['currentparts'], 'collection_poster' => $row['fromname'], 'old_collection_hash' => $row['collectionhash']];
if ($limit > 0) {
if (count($hashes) > $limit) {
break;
}
$hashes[$newCollectionHash] = '';
}
}
}
}
return $data;
}
/**
* Delete collections (complete/incomplete/old/etc).
*
* @param int|string $groupID (optional)
*
* @void
* @access public
*/
public function deleteCollections($groupID)
{
$startTime = time();
$group = $this->groups->getCBPTableNames($this->tablePerGroup, $groupID);
$deletedCount = 0;
// CBP older than retention.
if ($this->echoCLI) {
echo $this->pdo->log->header("Process Releases -> Delete finished collections." . PHP_EOL) . $this->pdo->log->primary(sprintf('Deleting collections/binaries/parts older than %d hours.', $this->pdo->getSetting('partretentionhours')));
}
$deleted = 0;
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE c, b, p FROM %s c
LEFT JOIN %s b ON (c.id=b.collection_id)
LEFT JOIN %s p ON (b.id=p.binaryid)
WHERE (c.dateadded < NOW() - INTERVAL %d HOUR) %s', $group['cname'], $group['bname'], $group['pname'], $this->pdo->getSetting('partretentionhours'), !empty($groupID) && $this->tablePerGroup === false ? ' AND c.group_id = ' . $groupID : ''));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$firstQuery = $fourthQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' old collections/binaries/parts in ' . ($firstQuery - $startTime) . ' seconds.' . PHP_EOL);
}
// Cleanup orphaned collections, binaries and parts
// this really shouldn't happen, but just incase - so we only run 1/200 of the time
if (mt_rand(0, 200) <= 1) {
// CBP collection orphaned with no binaries or parts.
if ($this->echoCLI) {
echo $this->pdo->log->header("Process Releases -> Remove CBP orphans." . PHP_EOL) . $this->pdo->log->primary('Deleting orphaned collections.');
}
$deleted = 0;
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE c, b, p FROM %s c
LEFT JOIN %s b ON (c.id=b.collection_id)
LEFT JOIN %s p ON (b.id=p.binaryid)
WHERE (b.id IS NULL OR p.binaryid IS NULL) %s', $group['cname'], $group['bname'], $group['pname'], !empty($groupID) && $this->tablePerGroup === false ? ' AND c.group_id = ' . $groupID : ''));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$secondQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' orphaned collections in ' . ($secondQuery - $firstQuery) . ' seconds.' . PHP_EOL);
}
// orphaned binaries - binaries with no parts or binaries with no collection
// Don't delete currently inserting binaries by checking the max id.
if ($this->echoCLI) {
echo $this->pdo->log->primary('Deleting orphaned binaries/parts with no collection.');
}
$deleted = 0;
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE b, p FROM %s b
LEFT JOIN %s p ON(b.id=p.binaryid)
LEFT JOIN %s c ON(b.collection_id=c.id)
WHERE (p.binaryid IS NULL OR c.id IS NULL) AND b.id < %d ', $group['bname'], $group['pname'], $group['cname'], $this->maxQueryFormulator($group['bname'], 20000)));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$thirdQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' binaries with no collections or parts in ' . ($thirdQuery - $secondQuery) . ' seconds.');
}
// orphaned parts - parts with no binary
// Don't delete currently inserting parts by checking the max id.
if ($this->echoCLI) {
echo $this->pdo->log->primary('Deleting orphaned parts with no binaries.');
}
$deleted = 0;
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE p FROM %s p LEFT JOIN %s b ON (p.binaryid=b.id) WHERE b.id IS NULL AND p.binaryid < %d', $group['pname'], $group['bname'], $this->maxQueryFormulator($group['bname'], 20000)));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$fourthQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' parts with no binaries in ' . ($fourthQuery - $thirdQuery) . ' seconds.' . PHP_EOL);
}
}
// done cleaning up Binaries/Parts orphans
if ($this->echoCLI) {
echo $this->pdo->log->primary('Deleting collections that were missed after NZB creation.');
}
$deleted = 0;
// Collections that were missing on NZB creation.
$collections = $this->pdo->queryDirect(sprintf('
SELECT SQL_NO_CACHE c.id
FROM %s c
INNER JOIN releases r ON r.id = c.releaseid
WHERE r.nzbstatus = 1', $group['cname']));
if ($collections instanceof \Traversable) {
foreach ($collections as $collection) {
$deleted++;
$this->pdo->queryExec(sprintf('
DELETE c, b, p
FROM %s c
LEFT JOIN %s b ON(c.id=b.collection_id)
LEFT JOIN %s p ON(b.id=p.binaryid)
WHERE c.id = %d', $group['cname'], $group['bname'], $group['pname'], $collection['id']));
}
$deletedCount += $deleted;
}
if ($this->echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->primary('Finished deleting ' . $deleted . ' collections missed after NZB creation in ' . (time() - $fourthQuery) . ' seconds.' . PHP_EOL . 'Removed ' . number_format($deletedCount) . ' parts/binaries/collection rows in ' . $this->consoleTools->convertTime($fourthQuery - $startTime) . PHP_EOL));
}
}
/**
* Returns unix time for an article number.
*
* @param int $post The article number to get the time from.
* @param array $groupData Usenet group info from NNTP selectGroup method.
*
* @return int Timestamp.
*/
public function postdate($post, array $groupData)
{
// Set table names
$groupID = $this->_groups->getIDByName($groupData['group']);
$group = [];
if ($groupID !== '') {
$group = $this->_groups->getCBPTableNames($this->_tablePerGroup, $groupID);
}
$currentPost = $post;
$attempts = $date = 0;
do {
// Try to get the article date locally first.
if ($groupID !== '') {
// Try to get locally.
$local = $this->_pdo->queryOneRow(sprintf('
SELECT c.date AS date
FROM %s c
INNER JOIN %s b ON(c.id=b.collection_id)
INNER JOIN %s p ON(b.id=p.binaryid)
WHERE p.number = %s
%s LIMIT 1', $group['cname'], $group['bname'], $group['pname'], $currentPost, $this->_tablePerGroup === false ? sprintf('AND c.group_id = %d', $groupID) : ''));
if ($local !== false) {
$date = $local['date'];
break;
}
}
// If we could not find it locally, try usenet.
$header = $this->_nntp->getXOVER($currentPost);
if (!$this->_nntp->isError($header)) {
// Check if the date is set.
if (isset($header[0]['Date']) && strlen($header[0]['Date']) > 0) {
$date = $header[0]['Date'];
break;
}
}
// Try to get a different article number.
if (abs($currentPost - $groupData['first']) > abs($groupData['last'] - $currentPost)) {
$tempPost = round($currentPost / (mt_rand(1005, 1012) / 1000), 0, PHP_ROUND_HALF_UP);
if ($tempPost < $groupData['first']) {
$tempPost = $groupData['first'];
}
} else {
$tempPost = round(mt_rand(1005, 1012) / 1000 * $currentPost, 0, PHP_ROUND_HALF_UP);
if ($tempPost > $groupData['last']) {
$tempPost = $groupData['last'];
}
}
// If we got the same article number as last time, give up.
if ($tempPost === $currentPost) {
break;
}
$currentPost = $tempPost;
if ($this->_debug) {
$this->_colorCLI->doEcho($this->_colorCLI->debug('Postdate retried ' . $attempts . " time(s)."));
}
} while ($attempts++ <= 20);
// If we didn't get a date, set it to now.
if (!$date) {
$date = time();
} else {
$date = strtotime($date);
}
if ($this->_debug) {
$this->_debugging->log(get_class(), __FUNCTION__, 'Article (' . $post . "'s) date is (" . $date . ') (' . $this->daysOld($date) . " days old)", Logger::LOG_INFO);
}
return $date;
}
/**
* Delete collections (complete/incomplete/old/etc).
*
* @param int|string $groupID (optional)
*
* @void
* @access public
*/
public function deleteCollections($groupID)
{
$startTime = time();
$group = $this->groups->getCBPTableNames($this->tablePerGroup, $groupID);
$deletedCount = 0;
if ($this->echoCLI) {
echo $this->pdo->log->header("Process Releases -> Delete finished collections." . PHP_EOL) . $this->pdo->log->primary('Deleting old collections/binaries/parts.');
}
$deleted = 0;
// CBP older than retention.
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE FROM %s WHERE dateadded < (NOW() - INTERVAL %d HOUR) %s', $group['cname'], $this->pdo->getSetting('partretentionhours'), !empty($groupID) && $this->tablePerGroup === false ? ' AND group_id = ' . $groupID : ''));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$firstQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' old collections/binaries/parts in ' . ($firstQuery - $startTime) . ' seconds.' . PHP_EOL . 'Deleting binaries/parts with no collections.');
}
$deleted = 0;
// Binaries/parts that somehow have no collection.
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE %s, %s FROM %s, %s WHERE %s.collection_id = 0 AND %s.id = %s.binaryid', $group['bname'], $group['pname'], $group['bname'], $group['pname'], $group['bname'], $group['bname'], $group['pname']));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$secondQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' binaries/parts with no collections in ' . ($secondQuery - $firstQuery) . ' seconds.' . PHP_EOL . 'Deleting parts with no binaries.');
}
$deleted = 0;
// Parts that somehow have no binaries. Don't delete parts currently inserting, by checking the max ID.
if (mt_rand(0, 100) <= 5) {
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE FROM %s WHERE binaryid NOT IN (SELECT id FROM %s) %s', $group['pname'], $group['bname'], $this->minMaxQueryFormulator($group['pname'], 40000)));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
}
$thirdQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' parts with no binaries in ' . ($thirdQuery - $secondQuery) . ' seconds.' . PHP_EOL . 'Deleting binaries with no collections.');
}
$deleted = 0;
// Binaries that somehow have no collection. Don't delete currently inserting binaries by checking the max id.
$deleteQuery = $this->pdo->queryExec(sprintf('DELETE FROM %s WHERE collection_id NOT IN (SELECT id FROM %s) %s', $group['bname'], $group['cname'], $this->minMaxQueryFormulator($group['bname'], 20000)));
if ($deleteQuery !== false) {
$deleted = $deleteQuery->rowCount();
$deletedCount += $deleted;
}
$fourthQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' binaries with no collections in ' . ($fourthQuery - $thirdQuery) . ' seconds.' . PHP_EOL . 'Deleting collections with no binaries.');
}
$deleted = 0;
// Collections that somehow have no binaries.
$collectionIDs = $this->pdo->queryDirect(sprintf('SELECT SQL_NO_CACHE id FROM %s WHERE id NOT IN (SELECT collection_id FROM %s) %s', $group['cname'], $group['bname'], $this->minMaxQueryFormulator($group['cname'], 10000)));
if ($collectionIDs instanceof \Traversable) {
foreach ($collectionIDs as $collectionID) {
$deleted++;
$this->pdo->queryExec(sprintf('DELETE FROM %s WHERE id = %d', $group['cname'], $collectionID['id']));
}
$deletedCount += $deleted;
}
$fifthQuery = time();
if ($this->echoCLI) {
echo $this->pdo->log->primary('Finished deleting ' . $deleted . ' collections with no binaries in ' . ($fifthQuery - $fourthQuery) . ' seconds.' . PHP_EOL . 'Deleting collections that were missed after NZB creation.');
}
$deleted = 0;
// Collections that were missing on NZB creation.
$collections = $this->pdo->queryDirect(sprintf('
SELECT SQL_NO_CACHE c.id
FROM %s c
INNER JOIN releases r ON r.id = c.releaseid
WHERE r.nzbstatus = 1', $group['cname']));
if ($collections instanceof \Traversable) {
foreach ($collections as $collection) {
$deleted++;
$this->pdo->queryExec(sprintf('
DELETE FROM %s WHERE id = %d', $group['cname'], $collection['id']));
}
$deletedCount += $deleted;
}
if ($this->echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->primary('Finished deleting ' . $deleted . ' collections missed after NZB creation in ' . (time() - $fifthQuery) . ' seconds.' . PHP_EOL . 'Removed ' . number_format($deletedCount) . ' parts/binaries/collection rows in ' . $this->consoleTools->convertTime($fifthQuery - $startTime) . PHP_EOL));
}
}
