/**
* Optimize a RT index.
* @param string $indexName
*/
public function optimizeRTIndex($indexName)
{
if (!is_null($this->sphinxQL)) {
$this->sphinxQL->queryExec(sprintf('FLUSH RTINDEX %s', $indexName));
$this->sphinxQL->queryExec(sprintf('OPTIMIZE INDEX %s', $indexName));
}
}
function releases_rt()
{
$pdo = new DB();
$rows = $pdo->queryExec('SELECT id, guid, name, searchname, fromname FROM releases');
if ($rows !== false && $rows->rowCount()) {
$sphinx = new SphinxSearch();
$total = $rows->rowCount();
$string = 'REPLACE INTO releases_rt (id, guid, name, searchname, fromname) VALUES ';
$tempString = '';
$i = 0;
echo '[Starting to populate sphinx RT indexes with ' . $total . ' releases.] ';
foreach ($rows as $row) {
$i++;
$tempString .= sprintf('(%d, %s, %s, %s, %s),', $row['id'], $sphinx->sphinxQL->escapeString($row['guid']), $sphinx->sphinxQL->escapeString($row['name']), $sphinx->sphinxQL->escapeString($row['searchname']), $sphinx->sphinxQL->escapeString($row['fromname']));
if ($i === 1000 || $i >= $total) {
$sphinx->sphinxQL->queryExec($string . rtrim($tempString, ','));
$tempString = '';
$total -= $i;
$i = 0;
echo '.';
}
}
echo ' [Done.]' . PHP_EOL;
} else {
echo 'No releases in your DB or an error occurred. This will need to be resolved before you can use the search.' . PHP_EOL;
}
}
/**
* Updates PRE data in the DB.
*
* @access protected
*/
protected function _updatePre()
{
if (empty($this->_curPre['title'])) {
return;
}
$query = 'UPDATE predb SET ';
$query .= !empty($this->_curPre['size']) ? 'size = ' . $this->_pdo->escapeString($this->_curPre['size']) . ', ' : '';
$query .= !empty($this->_curPre['source']) ? 'source = ' . $this->_pdo->escapeString($this->_curPre['source']) . ', ' : '';
$query .= !empty($this->_curPre['files']) ? 'files = ' . $this->_pdo->escapeString($this->_curPre['files']) . ', ' : '';
$query .= !empty($this->_curPre['reason']) ? 'nukereason = ' . $this->_pdo->escapeString($this->_curPre['reason']) . ', ' : '';
$query .= !empty($this->_curPre['reqid']) ? 'requestid = ' . $this->_curPre['reqid'] . ', ' : '';
$query .= !empty($this->_curPre['group_id']) ? 'group_id = ' . $this->_curPre['group_id'] . ', ' : '';
$query .= !empty($this->_curPre['predate']) ? 'predate = ' . $this->_curPre['predate'] . ', ' : '';
$query .= !empty($this->_curPre['nuked']) ? 'nuked = ' . $this->_curPre['nuked'] . ', ' : '';
$query .= !empty($this->_curPre['filename']) ? 'filename = ' . $this->_pdo->escapeString($this->_curPre['filename']) . ', ' : '';
$query .= empty($this->_oldPre['category']) && !empty($this->_curPre['category']) ? 'category = ' . $this->_pdo->escapeString($this->_curPre['category']) . ', ' : '';
if ($query === 'UPDATE predb SET ') {
return;
}
$query .= 'title = ' . $this->_pdo->escapeString($this->_curPre['title']);
$query .= ' WHERE title = ' . $this->_pdo->escapeString($this->_curPre['title']);
$this->_pdo->ping(true);
$this->_pdo->queryExec($query);
$this->_doEcho(false);
}
/**
* Attempts to match PreDB titles to releases.
*
* @param $dateLimit
*/
public function checkPre($dateLimit = false)
{
$this->dateLimit = $dateLimit;
$consoleTools = new ConsoleTools(['ColorCLI' => $this->pdo->log]);
$updated = 0;
$datesql = '';
if ($this->echooutput) {
echo $this->pdo->log->header('Querying DB for release search names not matched with PreDB titles.');
}
if ($this->dateLimit !== false && is_numeric($this->dateLimit)) {
$datesql = sprintf('AND DATEDIFF(NOW(), adddate) <= %d', $this->dateLimit);
}
$res = $this->pdo->queryDirect(sprintf('
SELECT p.id AS preid, r.id AS releaseid
FROM predb p
INNER JOIN releases r ON p.title = r.searchname
WHERE r.preid < 1 %s', $datesql));
if ($res !== false) {
$total = $res->rowCount();
echo $this->pdo->log->primary(number_format($total) . ' releases to match.');
if ($res instanceof \Traversable) {
foreach ($res as $row) {
$this->pdo->queryExec(sprintf('UPDATE releases SET preid = %d WHERE id = %d', $row['preid'], $row['releaseid']));
if ($this->echooutput) {
$consoleTools->overWritePrimary('Matching up preDB titles with release searchnames: ' . $consoleTools->percentString(++$updated, $total));
}
}
if ($this->echooutput) {
echo PHP_EOL;
}
}
if ($this->echooutput) {
echo $this->pdo->log->header('Matched ' . number_format($updated > 0 ? $updated : 0) . ' PreDB titles to release search names.');
}
}
}
function populate_rt($table = '')
{
$pdo = new DB();
$rows = false;
switch ($table) {
case 'releases_rt':
$pdo->queryDirect('SET SESSION group_concat_max_len=8192');
$rows = $pdo->queryExec('SELECT r.id, r.name, r.searchname, r.fromname, IFNULL(GROUP_CONCAT(rf.name SEPARATOR " "),"") filename
FROM releases r LEFT JOIN release_files rf ON(r.id=rf.releaseid) GROUP BY r.id');
$rtvalues = '(id, name, searchname, fromname, filename)';
break;
}
if ($rows !== false && ($total = $rows->rowCount())) {
$sphinx = new SphinxSearch();
$string = sprintf('REPLACE INTO %s %s VALUES ', $table, $rtvalues);
$tempString = '';
$i = 0;
echo '[Starting to populate sphinx RT index ' . $table . ' with ' . $total . ' releases.] ';
foreach ($rows as $row) {
$i++;
switch ($table) {
case 'releases_rt':
$tempString .= sprintf('(%d, %s, %s, %s, %s),', $row['id'], $sphinx->sphinxQL->escapeString($row['name']), $sphinx->sphinxQL->escapeString($row['searchname']), $sphinx->sphinxQL->escapeString($row['fromname']), $sphinx->sphinxQL->escapeString($row['filename']));
break;
}
if ($i === 1000 || $i >= $total) {
$sphinx->sphinxQL->queryExec($string . rtrim($tempString, ','));
$tempString = '';
$total -= $i;
$i = 0;
echo '.';
}
}
echo ' [Done.]' . PHP_EOL;
} else {
echo 'No releases in your DB or an error occurred. This will need to be resolved before you can use the search.' . PHP_EOL;
}
}
} elseif ($argc == 2) {
// Checks that argv[1] exists AND that there are no other arguments, which would be an error.
$socket = preg_replace('#^(?:unix://)?(.*)$#', '$1', $argv[1]);
if (substr($socket, 0, 1) == '/') {
// Make sure the socket path is fully qualified (and using correct separator).
$sphinxConnection = sprintf('unix://%s:', $socket);
}
} else {
exit("Argument 1 must the hostname or IP to the Sphinx searchd server, Argument 2 must be the port to the Sphinx searchd server.\nAlternatively, Argument 1 can be a unix domain socket." . PHP_EOL);
}
$pdo = new DB();
$tableSQL_releases = <<<DDLSQL
CREATE TABLE releases_se
(
\tid BIGINT UNSIGNED NOT NULL,
\tweight INTEGER NOT NULL,
\tquery VARCHAR(1024) NOT NULL,
\tname VARCHAR(255) NOT NULL DEFAULT '',
\tsearchname VARCHAR(255) NOT NULL DEFAULT '',
\tfromname VARCHAR(255) NULL,
\tfilename VARCHAR(1000) NULL,
\tINDEX(query)
) ENGINE=SPHINX CONNECTION="%sreleases_rt"
DDLSQL;
$tables = [];
$tables['releases_se'] = sprintf($tableSQL_releases, $sphinxConnection);
foreach ($tables as $table => $query) {
$pdo->queryExec(sprintf('DROP TABLE IF EXISTS %s', $table));
$pdo->queryExec($query);
}
echo 'All done! If you messed up your sphinx connection info, you can rerun this script.' . PHP_EOL;
/**
* Backfill single group.
*
* @param array $groupArr
* @param int $left
* @param int|string $articles
*
* @return void
*/
public function backfillGroup($groupArr, $left, $articles = '')
{
// Start time for this group.
$startGroup = microtime(true);
$groupName = str_replace('alt.binaries', 'a.b', $groupArr['name']);
// If our local oldest article 0, it means we never ran update_binaries on the group.
if ($groupArr['first_record'] <= 0) {
$dMessage = "You need to run update_binaries on " . $groupName . ". Otherwise the group is dead, you must disable it.";
if ($this->_debug) {
$this->_debugging->log(get_class(), __FUNCTION__, $dMessage, Logger::LOG_ERROR);
}
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->error($dMessage));
}
return;
}
// Select group, here, only once
$data = $this->_nntp->selectGroup($groupArr['name']);
if ($this->_nntp->isError($data)) {
$data = $this->_nntp->dataError($this->_nntp, $groupArr['name']);
if ($this->_nntp->isError($data)) {
return;
}
}
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->primary('Processing ' . $groupName), true);
}
// Check if this is days or post backfill.
$postCheck = $articles === '' ? false : true;
// Get target post based on date or user specified number.
$targetpost = (string) ($postCheck ? round($groupArr['first_record'] - $articles) : $this->_binaries->daytopost($groupArr['backfill_target'], $data));
// Check if target post is smaller than server's oldest, set it to oldest if so.
if ($targetpost < $data['first']) {
$targetpost = $data['first'];
}
// Check if our target post is newer than our oldest post or if our local oldest article is older than the servers oldest.
if ($targetpost >= $groupArr['first_record'] || $groupArr['first_record'] <= $data['first']) {
$dMessage = "We have hit the maximum we can backfill for " . $groupName . ($this->_disableBackfillGroup ? ", disabling backfill on it." : ", skipping it, consider disabling backfill on it.");
if ($this->_debug) {
$this->_debugging->log(get_class(), __FUNCTION__, $dMessage, Logger::LOG_NOTICE);
}
if ($this->_disableBackfillGroup) {
$this->pdo->queryExec(sprintf('
UPDATE groups
SET backfill = 0
WHERE id = %d', $groupArr['id']));
}
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->notice($dMessage), true);
}
return;
}
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->primary('Group ' . $groupName . "'s oldest article is " . number_format($data['first']) . ', newest is ' . number_format($data['last']) . ".\nOur target article is " . number_format($targetpost) . '. Our oldest article is article ' . number_format($groupArr['first_record']) . '.'));
}
// Set first and last, moving the window by max messages.
$last = (string) ($groupArr['first_record'] - 1);
// Set the initial "chunk".
$first = (string) ($last - $this->_binaries->messageBuffer + 1);
// Just in case this is the last chunk we needed.
if ($targetpost > $first) {
$first = $targetpost;
}
$done = false;
while ($done === false) {
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->set256('Yellow') . "\nGetting " . number_format($last - $first + 1) . " articles from " . $groupName . ", " . $left . " group(s) left. (" . number_format($first - $targetpost) . " articles in queue)." . $this->pdo->log->rsetColor(), true);
}
flush();
$lastMsg = $this->_binaries->scan($groupArr, $first, $last, $this->_safePartRepair);
// Get the oldest date.
if (isset($lastMsg['firstArticleDate'])) {
// Try to get it from the oldest pulled article.
$newdate = strtotime($lastMsg['firstArticleDate']);
} else {
// If above failed, try to get it with postdate method.
$newdate = $this->_binaries->postdate($first, $data);
}
$this->pdo->queryExec(sprintf('
UPDATE groups
SET first_record_postdate = %s, first_record = %s, last_updated = NOW()
WHERE id = %d', $this->pdo->from_unixtime($newdate), $this->pdo->escapeString($first), $groupArr['id']));
if ($first == $targetpost) {
$done = true;
} else {
// Keep going: set new last, new first, check for last chunk.
$last = (string) ($first - 1);
$first = (string) ($last - $this->_binaries->messageBuffer + 1);
if ($targetpost > $first) {
$first = $targetpost;
}
}
}
if ($this->_echoCLI) {
$this->pdo->log->doEcho($this->pdo->log->primary(PHP_EOL . 'Group ' . $groupName . ' processed in ' . number_format(microtime(true) - $startGroup, 2) . " seconds."), true);
}
}
exit('ERROR! No groups were found with backfill enabled!' . PHP_EOL);
} else {
exit('ERROR! Group (' . $argv[1] . ') not found!' . PHP_EOL);
}
}
$nntp = new NNTP(['Settings' => $pdo]);
$nntp->doConnect() or exit('Could not connect to Usenet!' . PHP_EOL);
$binaries = new Binaries(['NNTP' => $nntp, 'Settings' => $pdo]);
foreach ($groups as $group) {
$groupNNTP = $nntp->selectGroup($group['name']);
if ($nntp->isError($groupNNTP)) {
echo 'ERROR! Could not fetch information from NNTP for group (' . $group['name'] . ')' . PHP_EOL;
continue;
}
$postDate = $pdo->queryOneRow(sprintf('SELECT UNIX_TIMESTAMP(postdate) AS postdate FROM releases WHERE group_id = %d ORDER BY postdate ASC LIMIT 1', $group['id']));
if ($postDate === false) {
echo 'ERROR! Could not find any existing releases for group (' . $group['name'] . ')' . PHP_EOL;
continue;
}
$articleNumber = (int) $binaries->daytopost(round((time() - $postDate['postdate']) / 86400), $groupNNTP);
if ($group['last_record'] != 0 && $articleNumber >= $group['last_record']) {
echo 'ERROR! Could not determine the article number for this date: (' . $postDate['postdate'] . ') on group (' . $group['name'] . ')' . PHP_EOL;
continue;
}
$articleDate = $binaries->postdate($articleNumber, $groupNNTP);
$pdo->queryExec(sprintf('
UPDATE groups
SET first_record = %d, first_record_postdate = %s
WHERE id = %d', $articleNumber, $pdo->from_unixtime($articleDate), $group['id']));
echo 'SUCCESS! Updated group (' . $group['name'] . ')\'s first article number to (' . $articleNumber . ') dated (' . date('r', $articleDate) . ').' . PHP_EOL . 'The previous first article number was: (' . $group['first_record'] . ')' . (empty($group['first_record_postdate']) ? '.' : ' dated (' . date('r', strtotime($group['first_record_postdate'])) . ').') . PHP_EOL;
}
