-
-
Save JamesBondsky/89d182bb266b29bb8a7d631614210e27 to your computer and use it in GitHub Desktop.
[Очистка таблиц] Очистка больших таблиц в битриксе #utils#bitrix#cron
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| define('STOP_STATISTICS', true); | |
| define('BX_SECURITY_SHOW_MESSAGE', true); | |
| define("NO_KEEP_STATISTIC", true); | |
| define("NO_AGENT_STATISTIC", true); | |
| define("NOT_CHECK_PERMISSIONS", true); | |
| $_SERVER['DOCUMENT_ROOT'] = '/home/bitrix/www'; | |
| require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_before.php'); | |
| use Bitrix\Main\Application; | |
| use Bitrix\Main\Localization\Loc; | |
| class TableCleaner | |
| { | |
| #log path | |
| const logPath = '/cleardblog_mail.log'; | |
| #path where file with dates | |
| const dataPath = '/cleardblog.json'; | |
| #PID что бы убедиться что процесс работает только один | |
| const pidPath = '/cleardblog.pid'; | |
| #Регулирует шаг (limit x 10 = 50 * 1000 * 10) | |
| const limit = 40 * 1000; | |
| const tableName = 'b_mail_message'; | |
| const MODIFIED_FIELD = 'DATE_INSERT'; | |
| static array $arDates; | |
| static function log(string $strText, int $mode = FILE_APPEND): bool | |
| { | |
| $strText = '[' . ConvertTimeStamp(false, 'FULL') . '] ' . $strText . PHP_EOL; | |
| return file_put_contents($_SERVER['DOCUMENT_ROOT'] . self::logPath, $strText, $mode); | |
| } | |
| static function getCount($strDate) | |
| { | |
| $connection = \Bitrix\Main\Application::getConnection(); | |
| $strSql = 'SELECT count(ID) as qty FROM ' . self::tableName . ' WHERE ' . self::MODIFIED_FIELD . ' < "' . $strDate . '"'; | |
| return (int)$connection->queryScalar($strSql); | |
| } | |
| static function getDate() | |
| { | |
| $arData = self::getDates(); | |
| if ($arData['DATES'][0] && strlen($arData['DATES'][0]) > 0) { | |
| return ConvertDateTime($arData['DATES'][0], "YYYY-MM-DD 00:00:00", "ru"); | |
| } | |
| return false; | |
| } | |
| static function getDates() | |
| { | |
| if (empty(self::$arDates)) { | |
| if ($_REQUEST['MAKE'] == 'Y' && strlen($_REQUEST['DATE']) > 0) { | |
| $strDate = trim($_REQUEST['DATE']); | |
| self::makeDates($strDate); | |
| self::removePID(); | |
| die(); | |
| } else if (file_exists($_SERVER['DOCUMENT_ROOT'] . self::dataPath)) { | |
| $arDates = file_get_contents($_SERVER['DOCUMENT_ROOT'] . self::dataPath); | |
| self::$arDates = json_decode($arDates, true); | |
| return self::$arDates; | |
| } else { | |
| throw new \Exception('DATA_FILE_MISSING you can use ?MAKE=Y&DATE=2020-01-01 to make one'); | |
| } | |
| } | |
| return self::$arDates; | |
| } | |
| static function checkPID() | |
| { | |
| if (file_exists($_SERVER['DOCUMENT_ROOT'] . self::pidPath)) { | |
| CMain::FinalActions(); | |
| die(); | |
| } | |
| touch($_SERVER['DOCUMENT_ROOT'] . self::pidPath); | |
| } | |
| static function removePID() | |
| { | |
| if (file_exists($_SERVER['DOCUMENT_ROOT'] . self::pidPath)) { | |
| unlink($_SERVER['DOCUMENT_ROOT'] . self::pidPath); | |
| return true; | |
| } | |
| return false; | |
| } | |
| static function unsetDate($strNewDate) | |
| { | |
| $arDates = self::getDates(); | |
| $arNewDates = []; | |
| foreach ($arDates['DATES'] as $strDate) { | |
| if (strtotime($strDate) == strtotime($strNewDate)) { | |
| continue; | |
| } | |
| $arNewDates[] = $strDate; | |
| } | |
| $arDates['DATES'] = $arNewDates; | |
| file_put_contents($_SERVER['DOCUMENT_ROOT'] . self::dataPath, json_encode($arDates)); | |
| self::log('DATE: ' . $strNewDate . ' UNSETTED'); | |
| } | |
| static function makeDates(string $strDateFrom, int $step = 5): bool | |
| { | |
| $obDateStart = new \DateTime(Date('Y-m-d H:i:s', strtotime($strDateFrom))); | |
| $strDateEnd = time() - (60 * 60 * 24 * 31 * 12); | |
| $obDateEnd = new \DateTime(Date('Y-m-d H:i:s', $strDateEnd)); | |
| $interval = DateInterval::createFromDateString($step . ' days'); | |
| $obInterval = new DatePeriod($obDateStart, $interval, $obDateEnd); | |
| $arData = []; | |
| foreach ($obInterval as $key => $obDate) { | |
| $arData['DATES'][] = $obDate->format('d.m.Y 00:00:00'); | |
| } | |
| file_put_contents($_SERVER['DOCUMENT_ROOT'] . self::dataPath, json_encode($arData)); | |
| self::removePID(); | |
| throw new \Exception( | |
| 'FILE_CREATED FROM: ' . $strDateFrom . ' step ' . $step . ' - 2 months ' . $_SERVER['DOCUMENT_ROOT'] . self::dataPath | |
| ); | |
| } | |
| static function start() | |
| { | |
| $id = uniqid(); | |
| self::checkPID(); | |
| Bitrix\Main\Diag\Debug::startTimeLabel("tableCLeaner"); | |
| $connection = \Bitrix\Main\Application::getConnection(); | |
| $limit = self::limit; | |
| if (!$strDate = self::getDate()) { | |
| throw new \Exception('ALL_IS_OVER_NO_DATES'); | |
| } | |
| self::log('STARTED FOR DATE < ' . $strDate . ' qty=' . ($limit * 10) . ' ID=' . $id); | |
| $iLeft = self::getCount($strDate); | |
| self::log(number_format($iLeft)); | |
| if ($iLeft > 0) { | |
| $strSql = 'DELETE FROM ' . self::tableName . ' WHERE ' . self::MODIFIED_FIELD . ' < "' . $strDate . '" LIMIT ' . $limit; | |
| $iMax = 10; | |
| if ($iLeft <= ($limit * 10)) { | |
| $iMax = ceil($iLeft / $limit); | |
| self::log('iMax=' . $iMax); | |
| } | |
| for ($i = 0; $i < $iMax; $i++) { | |
| // "DELETE FROM b_bp_tracking WHERE MODIFIED < "2019-01-01 00:00:00" LIMIT 50000" | |
| $obRes = $connection->query($strSql); | |
| sleep(1); | |
| } | |
| $iLeft = self::getCount($strDate); | |
| self::log('->' . number_format($iLeft)); | |
| } | |
| if ($iLeft <= 0) { | |
| self::unsetDate($strDate); | |
| } | |
| Bitrix\Main\Diag\Debug::endTimeLabel("tableCLeaner"); | |
| $arLabels = Bitrix\Main\Diag\Debug::getTimeLabels(); | |
| self::log('duration: ' . number_format($arLabels["tableCLeaner"]['time'], 2) . 's'); | |
| self::log('END ID=' . $id); | |
| self::removePID(); | |
| } | |
| } | |
| try { | |
| TableCleaner::start(); | |
| } catch (\Exception $e) { | |
| TableCleaner::log($e->getMessage()); | |
| } | |
| CMain::FinalActions(); | |
| die(); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [main] | |
| # сколько дней хранить лог загрузчика писем | |
| b_mail_message_days = 360 | |
| # сколько дней хранить логи БП | |
| b_bp_tracking = 60 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| define('STOP_STATISTICS', true); | |
| define('BX_SECURITY_SHOW_MESSAGE', true); | |
| define("NO_KEEP_STATISTIC", true); | |
| define("NO_AGENT_STATISTIC", true); | |
| define("NOT_CHECK_PERMISSIONS", true); | |
| $_SERVER['DOCUMENT_ROOT'] = '/home/bitrix/www'; | |
| require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_before.php'); | |
| use Bitrix\Main\Application; | |
| use Bitrix\Main\Localization\Loc; | |
| class TableCleaner | |
| { | |
| #log path | |
| const logPath = '/bitrix/php_interface/include/dbcleaner/dbcleaner.log'; | |
| #PID что бы убедиться что процесс работает только один | |
| const pidPath = '/bitrix/php_interface/include/dbcleaner/cleardb.pid'; | |
| const iniPath = '/bitrix/php_interface/include/dbcleaner/dbcleanersettings.ini'; | |
| const limit = 500000; | |
| static array $arDates; | |
| static function log(string $strText, int $mode = FILE_APPEND): bool | |
| { | |
| $strText = '[' . ConvertTimeStamp(false, 'FULL') . '] ' . $strText . PHP_EOL; | |
| return file_put_contents($_SERVER['DOCUMENT_ROOT'] . self::logPath, $strText, $mode); | |
| } | |
| static function checkPID() | |
| { | |
| if (file_exists($_SERVER['DOCUMENT_ROOT'] . self::pidPath)) { | |
| CMain::FinalActions(); | |
| die(); | |
| } | |
| touch($_SERVER['DOCUMENT_ROOT'] . self::pidPath); | |
| } | |
| static function removePID() | |
| { | |
| if (file_exists($_SERVER['DOCUMENT_ROOT'] . self::pidPath)) { | |
| unlink($_SERVER['DOCUMENT_ROOT'] . self::pidPath); | |
| return true; | |
| } | |
| return false; | |
| } | |
| static function getSettings() | |
| { | |
| $arRequiredSettingsKeys = ['b_mail_message_days', 'b_bp_tracking']; | |
| $arSettings = parse_ini_file($_SERVER['DOCUMENT_ROOT'] . self::iniPath); | |
| if (!$arSettings) { | |
| throw new \Exception('CANT_LOAD_INIT'); | |
| } | |
| foreach ($arRequiredSettingsKeys as $key) { | |
| if (!array_key_exists($key, $arSettings) || $arSettings[$key] <= 0) { | |
| throw new \Exception("REQUIRED_VALUE_[{$key}]_INVALID"); | |
| } | |
| } | |
| return $arSettings; | |
| } | |
| static function parseDays(int $days = 60): int | |
| { | |
| if (!$days) throw new \Exception('INCORRENT_DAYS'); | |
| return intval(60 * 60 * 24 * $days); | |
| } | |
| static function clearTrackingTable(int $days): bool | |
| { | |
| $obConnection = \Bitrix\Main\Application::getConnection(); | |
| $iDateFrom = time() - self::parseDays($days); | |
| $obDateFrom = new \DateTime(Date('Y-m-d H:i:s', $iDateFrom)); | |
| $strSql = ' | |
| DELETE FROM b_bp_tracking | |
| WHERE MODIFIED < "' . $obDateFrom->format('Y-m-d 00:00:00') | |
| . '" LIMIT ' | |
| . self::limit; | |
| self::log(trim(preg_replace('/\s\s+/', ' ', $strSql)) . ' -> ' . $days . ' days'); | |
| if ($obConnection->query($strSql)) { | |
| return true; | |
| } | |
| throw new \Exception(__METHOD__ . '_SQL_ERROR_' . trim($strSql)); | |
| } | |
| static function clearMailMessageTable(int $days): bool | |
| { | |
| \CModule::IncludeModule('mail'); | |
| $obConnection = \Bitrix\Main\Application::getConnection(); | |
| $iDateFrom = time() - self::parseDays($days); | |
| $obDateFrom = new \DateTime(Date('Y-m-d H:i:s', $iDateFrom)); | |
| $strSql = 'SELECT ID FROM b_mail_message WHERE DATE_INSERT < "' . $obDateFrom->format('Y-m-d 00:00:00') . '"'; | |
| $dbRes = $obConnection->query($strSql); | |
| self::log($strSql . | |
| ' -> qty: ' . $dbRes->getSelectedRowsCount() . | |
| ' -> ' . $days . ' days' | |
| ); | |
| if ($dbRes->getSelectedRowsCount()) { | |
| while ($arRes = $dbRes->fetch()) { | |
| \CMailMessage::Delete($arRes['ID']); | |
| } | |
| } | |
| return true; | |
| } | |
| static function start() | |
| { | |
| $id = uniqid(); | |
| self::checkPID(); | |
| Bitrix\Main\Diag\Debug::startTimeLabel("tableCLeaner"); | |
| $arSettings = self::getSettings(); | |
| self::clearTrackingTable($arSettings['b_bp_tracking']); | |
| self::clearMailMessageTable($arSettings['b_mail_message_days']); | |
| Bitrix\Main\Diag\Debug::endTimeLabel("tableCLeaner"); | |
| $arLabels = Bitrix\Main\Diag\Debug::getTimeLabels(); | |
| self::log('duration: ' . number_format($arLabels["tableCLeaner"]['time'], 2) . 's'); | |
| self::removePID(); | |
| } | |
| } | |
| try { | |
| set_time_limit(0); | |
| TableCleaner::start(); | |
| } catch (\Exception $e) { | |
| TableCleaner::log($e->getMessage()); | |
| } | |
| CMain::FinalActions(); | |
| die(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment