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; } }