/** * 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);