foreach ($collections_rows as $row) {
$groupName = $groups->getByNameByID($row['group_id']);
echo $pdo->log->header("Processing {$groupName}");
//collection
$pdo->queryExec("INSERT IGNORE INTO collections_" . $row['group_id'] . " (subject, fromname, date, xref, totalfiles, group_id, collectionhash, dateadded, filecheck, filesize, releaseid) " . "SELECT subject, fromname, date, xref, totalfiles, group_id, collectionhash, dateadded, filecheck, filesize, releaseid FROM collections WHERE group_id = {$row['group_id']}");
$collections = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM collections where group_id = " . $row['group_id']);
$ncollections = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM collections_" . $row['group_id']);
echo $pdo->log->primary("Group {$groupName}, Collections = {$collections['cnt']} [{$ncollections['cnt']}]");
//binaries
$pdo->queryExec("INSERT IGNORE INTO binaries_{$row['group_id']} (name, filenumber, totalparts, currentparts, binaryhash, partcheck, partsize, collection_id) " . "SELECT name, filenumber, totalparts, currentparts, binaryhash, partcheck, partsize, n.id FROM binaries b " . "INNER JOIN collections c ON b.collection_id = c.id " . "INNER JOIN collections_{$row['group_id']} n ON c.collectionhash = n.collectionhash AND c.group_id = {$row['group_id']}");
$binaries = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM binaries b INNER JOIN collections c ON b.collection_id = c.id where c.group_id = {$row['group_id']}");
$nbinaries = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM binaries_{$row['group_id']}");
echo $pdo->log->primary("Group {$groupName}, Binaries = {$binaries['cnt']} [{$nbinaries['cnt']}]");
//parts
$pdo->queryExec("INSERT IGNORE INTO parts_{$row['group_id']} (messageid, number, partnumber, size, binaryid, collection_id) " . "SELECT messageid, number, partnumber, size, n.id, c.id FROM parts p " . "INNER JOIN binaries b ON p.binaryid = b.id " . "INNER JOIN binaries_{$row['group_id']} n ON b.binaryhash = n.binaryhash " . "INNER JOIN collections_{$row['group_id']} c on c.id = n.collection_id AND c.group_id = {$row['group_id']}");
$parts = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM parts p INNER JOIN binaries b ON p.binaryid = b.id INNER JOIN collections c ON b.collection_id = c.id WHERE c.group_id = {$row['group_id']}");
$nparts = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM parts_{$row['group_id']}");
echo $pdo->log->primary("Group {$groupName}, Parts = {$parts['cnt']} [{$nparts['cnt']}]\n");
$i++;
}
}
if (isset($argv[2]) && $argv[2] == 'truncate') {
echo $pdo->log->info("Truncating collections, binaries and parts tables.");
$pdo->queryExec("TRUNCATE TABLE collections");
$pdo->queryExec("TRUNCATE TABLE binaries");
$pdo->queryExec("TRUNCATE TABLE parts");
}
//set tpg active
$pdo->queryExec("UPDATE settings SET value = 1 WHERE setting = 'tablepergroup'");
echo $pdo->log->header("Processed: {$i} groups and " . number_format($parts_count['cnt']) . " parts in " . $consoleTools->convertTimer(TIME() - $start));
function create_guids($live, $delete = false)
{
$pdo = new Settings();
$consoletools = new ConsoleTools(['ColorCLI' => $pdo->log]);
$timestart = TIME();
$relcount = $deleted = $total = 0;
$relrecs = false;
if ($live == "true") {
$relrecs = $pdo->queryDirect(sprintf("SELECT id, guid FROM releases WHERE nzbstatus = 1 AND nzb_guid IS NULL ORDER BY id DESC"));
} else {
if ($live == "limited") {
$relrecs = $pdo->queryDirect(sprintf("SELECT id, guid FROM releases WHERE nzbstatus = 1 AND nzb_guid IS NULL ORDER BY id DESC LIMIT 10000"));
}
}
if ($relrecs) {
$total = $relrecs->rowCount();
}
if ($total > 0) {
echo $pdo->log->header("Creating nzb_guids for " . number_format($total) . " releases.");
$releases = new Releases(['Settings' => $pdo]);
$nzb = new NZB($pdo);
$releaseImage = new ReleaseImage($pdo);
$reccnt = 0;
if ($relrecs instanceof \Traversable) {
foreach ($relrecs as $relrec) {
$reccnt++;
$nzbpath = $nzb->NZBPath($relrec['guid']);
if ($nzbpath !== false) {
$nzbfile = Utility::unzipGzipFile($nzbpath);
if ($nzbfile) {
$nzbfile = @simplexml_load_string($nzbfile);
}
if (!$nzbfile) {
if (isset($delete) && $delete == 'delete') {
//echo "\n".$nzb->NZBPath($relrec['guid'])." is not a valid xml, deleting release.\n";
$releases->deleteSingle(['g' => $relrec['guid'], 'i' => $relrec['id']], $nzb, $releaseImage);
$deleted++;
}
continue;
}
$binary_names = array();
foreach ($nzbfile->file as $file) {
$binary_names[] = $file["subject"];
}
if (count($binary_names) == 0) {
if (isset($delete) && $delete == 'delete') {
//echo "\n".$nzb->NZBPath($relrec['guid'])." has no binaries, deleting release.\n";
$releases->deleteSingle(['g' => $relrec['guid'], 'i' => $relrec['id']], $nzb, $releaseImage);
$deleted++;
}
continue;
}
asort($binary_names);
foreach ($nzbfile->file as $file) {
if ($file["subject"] == $binary_names[0]) {
$segment = $file->segments->segment;
$nzb_guid = md5($segment);
$pdo->queryExec("UPDATE releases set nzb_guid = " . $pdo->escapestring($nzb_guid) . " WHERE id = " . $relrec["id"]);
$relcount++;
$consoletools->overWritePrimary("Created: [" . $deleted . "] " . $consoletools->percentString($reccnt, $total) . " Time:" . $consoletools->convertTimer(TIME() - $timestart));
break;
}
}
} else {
if (isset($delete) && $delete == 'delete') {
//echo $pdo->log->primary($nzb->NZBPath($relrec['guid']) . " does not have an nzb, deleting.");
$releases->deleteSingle(['g' => $relrec['guid'], 'i' => $relrec['id']], $nzb, $releaseImage);
}
}
}
}
if ($relcount > 0) {
echo "\n";
}
echo $pdo->log->header("Updated " . $relcount . " release(s). This script ran for " . $consoletools->convertTime(TIME() - $timestart));
} else {
echo $pdo->log->info('Query time: ' . $consoletools->convertTime(TIME() - $timestart));
exit($pdo->log->info("No releases are missing the guid."));
}
}
if ($debug) {
echo "\n\nPart Repair insert:\n";
print_r($partrepair);
echo sprintf("\nINSERT INTO missed_parts_%d (numberid, group_id, attempts) VALUES (%s, %s, %s)\n\n", $group['id'], $partrepair['numberid'], $partrepair['group_id'], $partrepair['attempts']);
}
$pdo->queryExec(sprintf('INSERT INTO missed_parts_%d (numberid, group_id, attempts) VALUES (%s, %s, %s);', $group['id'], $partrepair['numberid'], $partrepair['group_id'], $partrepair['attempts']));
$consoletools->overWrite('Part Repairs Completed for ' . $group['name'] . ':' . $consoletools->percentString($pcount, $plen['total']));
$pcount++;
}
}
$pdone += 10000;
}
}
}
$endtime = time();
echo "\nTable population took " . $consoletools->convertTimer($endtime - $starttime) . ".\n";
//Truncate old tables to save space.
if (isset($argv[2]) && $argv[2] == 'delete') {
echo "Truncating old tables...\n";
$pdo->queryDirect('TRUNCATE TABLE collections;');
$pdo->queryDirect('TRUNCATE TABLE binaries;');
$pdo->queryDirect('TRUNCATE TABLE parts');
$pdo->queryDirect('TRUNCATE TABLE missed_parts');
echo "Complete.\n";
}
// Update TPG setting in site-edit.
$pdo->queryExec('UPDATE settings SET value = 1 where setting = \'tablepergroup\';');
$pdo->queryExec('UPDATE tmux SET value = 2 where setting = \'releases\';');
echo "New tables have been created.\nTable Per Group has been set to to \"TRUE\" in site-edit.\nUpdate Releases has been set to Threaded in tmux-edit.\n";
function multi_implode($array, $glue)
{
echo $pdo->log->header("Getting first/last for all your active groups.");
$data = $nntp->getGroups();
if ($nntp->isError($data)) {
exit($pdo->log->error("Failed to getGroups() from nntp server."));
}
echo $pdo->log->header("Inserting new values into short_groups table.");
$pdo->queryExec('TRUNCATE TABLE short_groups');
// Put into an array all active groups
$res = $pdo->query('SELECT name FROM groups WHERE active = 1 OR backfill = 1');
foreach ($data as $newgroup) {
if (myInArray($res, $newgroup['group'], 'name')) {
$pdo->queryInsert(sprintf('INSERT INTO short_groups (name, first_record, last_record, updated) VALUES (%s, %s, %s, NOW())', $pdo->escapeString($newgroup['group']), $pdo->escapeString($newgroup['first']), $pdo->escapeString($newgroup['last'])));
echo $pdo->log->primary('Updated ' . $newgroup['group']);
}
}
echo $pdo->log->header('Running time: ' . $consoleTools->convertTimer(TIME() - $start));
function myInArray($array, $value, $key)
{
//loop through the array
foreach ($array as $val) {
//if $val is an array cal myInArray again with $val as array input
if (is_array($val)) {
if (myInArray($val, $value, $key)) {
return true;
}
} else {
//else check if the given key has $value as value
if ($array[$key] == $value) {
return true;
}
}
