/**
* Gets the size of the table from the information schema & determines if threshold is met.
* @param [object] $dbc - the mysqli db connection object.
* @param [string] $dbName - the name of the database where the table we want to check resides.
* @param [string] $tableName - the table we want to check the size of.
* @param [int] $threshold - The number of MB we use to determine to truncate the table.
*/
function getTableSize($dbc, $dbName, $tableName, $threshold)
{
$sql = "SELECT table_name AS 'Table', \n ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB' \n FROM information_schema.TABLES \n WHERE table_schema = '{$dbName}'\n AND table_name = '{$tableName}'";
if ($result = $dbc->query($sql)) {
$row = $result->fetch_assoc();
if ($row['Size_MB'] >= $threshold) {
truncateTable($dbc, $tableName);
sendEmail(true, $row['Size_MB'], $tableName);
} else {
sendEmail(false, $row['Size_MB'], $tableName);
}
}
}
}
function truncateTable($pdo, $tableName, $runQueries)
{
if (!runQuery($pdo, "TRUNCATE TABLE " . $tableName, $runQueries)) {
exit("Unable to truncate: " . $tableName . "\nConversion Stopped....\n");
}
}
$nn_schema = $argv[1];
$nZEDB_schema = $argv[2];
$runQueries = $argv[3] == "true";
// This converts from the newznab schema to the nZEDb schema.
echo "Resetting Collections/Binaries/Parts/PartRepair\n";
truncateTable($pdo, $nZEDB_schema . ".collections", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".binaries", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".parts", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".missed_parts", $runQueries);
echo "Converting from newznab to nZEDb... This will take a while...\n\n";
convertTable($pdo, $nZEDB_schema, "anidb", "INSERT INTO " . $nZEDB_schema . ".anidb (airdates, anidbid, categories, characters, creators, description, enddate, episodetitles, epnos, picture, rating, related, startdate, title, type, unixtime) " . "SELECT airdates, anidbID, categories, characters, creators, description, enddate, episodetitles, epnos, picture, rating, related, startdate, title, type, UNIX_TIMESTAMP(createddate) FROM " . $nn_schema . ".anidb", $runQueries);
/* we no longer have this table, so this has to be changed to match current practise.
convertTable($pdo,
$nZEDB_schema,
"animetitles",
"INSERT INTO " . $nZEDB_schema . ".animetitles (anidbid, title, unixtime) " .
"SELECT anidbID, title, UNIX_TIMESTAMP(createddate) FROM " . $nn_schema .
".animetitles",
$runQueries);
*/
echo "Skipping binaries table: Different in nZEDb\n";
convertTable($pdo, $nZEDB_schema, "binaryblacklist", "INSERT INTO " . $nZEDB_schema . ".binaryblacklist (id, groupname, regex, msgcol, optype, status, description) " . "SELECT ID, groupname, regex, msgcol, optype, status, description FROM " . $nn_schema . ".binaryblacklist", $runQueries);
// You lose genreID and dewey.
convertTable($pdo, $nZEDB_schema, "bookinfo", "INSERT INTO " . $nZEDB_schema . ".bookinfo (id, title , author, asin, isbn, ean, url, publisher, publishdate, pages, overview, cover, createddate, updateddate) " . "SELECT ID, title , author, asin, isbn, ean, url, publisher, publishdate, pages, review, cover, createddate, updateddate FROM " . $nn_schema . ".bookinfo GROUP BY asin", $runQueries);
<?php
require __DIR__ . '/conf.php';
require __DIR__ . '/includes/functions.php';
require __DIR__ . '/spreadsheet-reader/php-excel-reader/excel_reader2.php';
require __DIR__ . '/spreadsheet-reader/SpreadsheetReader.php';
$fileData = getLatestFile(PRODUCTS_FILE_PATH);
$fileName = $fileData['name'];
if (fileUnread($fileName)) {
ini_set('memory_limit', -1);
truncateTable(TABLE_PRODUCTS);
$ProductsReader = new SpreadsheetReader(PRODUCTS_FILE_PATH . '/' . $fileName);
$recordsRead = 0;
foreach ($ProductsReader as $key => $field) {
if (!empty($field[1])) {
$arr = array();
$arr['name'] = $mysqli->real_escape_string($field[1]);
$arr['description'] = $mysqli->real_escape_string($field[2]);
$arr['vendor'] = $mysqli->real_escape_string($field[3]);
$arr['type'] = $mysqli->real_escape_string($field[4]);
$arr['tags'] = $mysqli->real_escape_string($field[5]);
$arr['image'] = $field[22];
$arr['meta_description'] = $mysqli->real_escape_string($field[29]);
$arr['compare_price'] = $field[21];
$arr['weight'] = $field[13];
$arr['quantity'] = sumQuantity($field);
$arr['sku'] = $field[7];
$fields = implode(',', array_keys($arr));
$values = implode("','", array_values($arr));
$query = "INSERT INTO products (" . $fields . ") VALUES ('" . $values . "')";
$result = $mysqli->query($query);
}
// Check argument count.
if (!isset($argv[1]) || !isset($argv[2]) || !isset($argv[3])) {
passthru("clear");
echo "Usage: newznab_schema nZEDB_schema true/false\n" . "example: php convert_from_newznab.php newznab nzedb true\n\n" . "newznab_schema: Schema where your newznab install is located. The database name. The current user in your config.php file must have access to this schema\n" . "nZEDB_schema: Schema where you want the newznab data converted too. The database name. The schema must be populated and will be wiped clean except the sites and categories tables\n" . "true/false: false = Show the queries but do not run. true means you understand the risks and want to convert the data (Your old data will not be touched\n\n" . "NOTE: This is experimental and there is a possibility that this will not work correctly. Please let us know if it doesn't work correctly, but we are not responsible for any lost data.\n" . " You will have to start any backfilling and processing over again since we use a different mechanism for processing releases\n\n";
exit(1);
}
$nn_schema = $argv[1];
$nZEDB_schema = $argv[2];
$runQueries = $argv[3] == "true";
// This converts from the schema newznab to the schema nZEDb.
echo "Resetting Collections/Binaries/Parts/PartRepair\n";
truncateTable($pdo, $nZEDB_schema . ".collections", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".binaries", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".parts", $runQueries);
truncateTable($pdo, $nZEDB_schema . ".partrepair", $runQueries);
echo "Converting from newznab to nZEDb.... This will take a while....\n\n";
convertTable($pdo, $nZEDB_schema, "anidb", "INSERT INTO " . $nZEDB_schema . ".anidb (airdates, anidbid, categories, characters, creators, description, enddate, episodetitles, epnos, picture, rating, related, startdate, title, type, unixtime) " . "SELECT airdates, anidbID, categories, characters, creators, description, enddate, episodetitles, epnos, picture, rating, related, startdate, title, type, UNIX_TIMESTAMP(createddate) FROM " . $nn_schema . ".anidb", $runQueries);
convertTable($pdo, $nZEDB_schema, "animetitles", "INSERT INTO " . $nZEDB_schema . ".animetitles (anidbid, title, unixtime) " . "SELECT anidbID, title, UNIX_TIMESTAMP(createddate) FROM " . $nn_schema . ".animetitles", $runQueries);
echo "Skipping binaries table: Different in nZEDb\n";
convertTable($pdo, $nZEDB_schema, "binaryblacklist", "INSERT INTO " . $nZEDB_schema . ".binaryblacklist (id, groupname, regex, msgcol, optype, status, description) " . "SELECT ID, groupname, regex, msgcol, optype, status, description FROM " . $nn_schema . ".binaryblacklist", $runQueries);
// You lose genreID and dewey.
convertTable($pdo, $nZEDB_schema, "bookinfo", "INSERT INTO " . $nZEDB_schema . ".bookinfo (id, title , author, asin, isbn, ean, url, publisher, publishdate, pages, overview, cover, createddate, updateddate) " . "SELECT ID, title , author, asin, isbn, ean, url, publisher, publishdate, pages, review, cover, createddate, updateddate FROM " . $nn_schema . ".bookinfo GROUP BY asin", $runQueries);
echo "Skipping category table: using nZEDb's version\n";
convertTable($pdo, $nZEDB_schema, "consoleinfo", "INSERT INTO " . $nZEDB_schema . ".consoleinfo (asin, cover, createddate, esrb, genreid, id, platform, publisher, releasedate, review, salesrank, title, updateddate, url) " . "SELECT asin, cover, createddate, esrb, genreID, ID, platform, publisher, releasedate, review, salesrank, title, updateddate, url FROM " . $nn_schema . ".consoleinfo GROUP BY asin", $runQueries);
convertTable($pdo, $nZEDB_schema, "content", "INSERT INTO " . $nZEDB_schema . ".content (id, title, url, body, metadescription, metakeywords, contenttype, showinmenu, status, ordinal, role) " . "SELECT id, title, url, body, metadescription, metakeywords, contenttype, showinmenu, status, ordinal, role FROM " . $nn_schema . ".content", $runQueries);
echo "episodeinfo = tvrageepisodes in nZEDb\n";
// Convert episodeinfo to tvrageepisodes - You loose (tvdbID, imdbID, director, gueststars, overview, rating, writer, epabsolute)
convertTable($pdo, $nZEDB_schema, "tvrageepisodes", "INSERT INTO " . $nZEDB_schema . ".tvrageepisodes (id, rageid, showtitle, airdate, link, fullep, eptitle) " . "SELECT MIN(ID), rageID, showtitle, MIN(airdate), link, fullep, eptitle FROM " . $nn_schema . ".episodeinfo where rageID <> 0 GROUP BY rageID, fullep", $runQueries);
convertTable($pdo, $nZEDB_schema, "forumpost", "INSERT INTO " . $nZEDB_schema . ".forumpost (forumid, parentid, user_id, subject, message, locked, sticky, replies, createddate, updateddate) " . "SELECT forumID, parentID, userID, subject, message, locked, sticky, replies, createddate, updateddate FROM " . $nn_schema . ".forumpost", $runQueries);
convertTable($pdo, $nZEDB_schema, "genres", "INSERT INTO " . $nZEDB_schema . ".genres (title, type) " . "SELECT title, type FROM " . $nn_schema . ".genres", $runQueries);
