public function testReplaceTable() { $this->assertInstanceOf('CM_Db_Exception', $this->catchException(function () { CM_Db_Db::replaceTable('test', 'test_new'); })); CM_Db_Db::exec('CREATE TABLE `test_new` (`id` INT(10) UNSIGNED NOT NULL)'); CM_Db_Db::insert('test_new', ['id' => 123]); CM_Db_Db::replaceTable('test', 'test_new'); $this->assertSame([['id' => '123']], CM_Db_Db::select('test', '*')->fetchAll()); $this->assertSame(false, CM_Db_Db::existsTable('test_new')); }
protected function _upgradeIpBlocks() { if ($this->_withoutIpBlocks) { return; } $this->_streamOutput->writeln('Updating IP blocks database…'); $ipBlocksReader = $this->_getIpBlocksReader(); CM_Db_Db::exec('DROP TABLE IF EXISTS `cm_model_location_ip_new`'); CM_Db_Db::exec('CREATE TABLE `cm_model_location_ip_new` LIKE `cm_model_location_ip`'); $infoListWarning = array(); $stream = $ipBlocksReader['stream']; $count = $ipBlocksReader['lineCount']; $item = 0; $batch = []; while (false !== ($row = fgetcsv($stream))) { if ($item >= 2 && count($row) >= 3) { // Skip copyright, column names and empty lines list($ipStart, $ipEnd, $maxMind) = $row; $ipStart = (int) $ipStart; $ipEnd = (int) $ipEnd; $maxMind = (int) $maxMind; $level = null; $id = null; if (isset($this->_zipCodeIdListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_ZIP; $id = $this->_zipCodeIdListByMaxMind[$maxMind]; } elseif (isset($this->_cityIdList[$maxMind])) { $level = CM_Model_Location::LEVEL_CITY; $id = $this->_cityIdList[$maxMind]; } elseif (isset($this->_regionIdListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_STATE; $id = $this->_regionIdListByMaxMind[$maxMind]; } elseif (isset($this->_countryCodeListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_COUNTRY; $countryCode = $this->_countryCodeListByMaxMind[$maxMind]; if (isset($this->_countryIdList[$countryCode])) { $id = $this->_countryIdList[$countryCode]; } } if ($level && $id) { $batch[] = [$id, $level, $ipStart, $ipEnd]; if (1000 === count($batch)) { CM_Db_Db::insert('cm_model_location_ip_new', ['id', 'level', 'ipStart', 'ipEnd'], $batch); $batch = []; } } else { $infoListWarning['Ignoring unknown locations'][] = $maxMind; } } $this->_printProgressCounter(++$item, $count); } if (!empty($batch)) { CM_Db_Db::insert('cm_model_location_ip_new', ['id', 'level', 'ipStart', 'ipEnd'], $batch); } unset($batch); unset($this->_countryIdList); unset($this->_countryCodeListByMaxMind); unset($this->_regionIdListByMaxMind); unset($this->_cityIdList); unset($this->_zipCodeIdListByMaxMind); $this->_printInfoList($infoListWarning, '!'); $this->_streamOutput->writeln('Checking overlapping of IP blocks…'); $result = CM_Db_Db::select('cm_model_location_ip_new', ['ipStart', 'ipEnd'], null, 'ipEnd ASC'); $ipStartPrevious = $ipEndPrevious = 0; $count = $result->getAffectedRows(); $item = 0; while (false !== ($row = $result->fetch())) { list($ipStart, $ipEnd) = array_values($row); if ($ipStart <= $ipEndPrevious) { $infoListWarning['Overlapping IP blocks'][] = "{$ipStartPrevious}-{$ipEndPrevious} and {$ipStart}-{$ipEnd}"; } $ipStartPrevious = $ipStart; $ipEndPrevious = $ipEnd; $this->_printProgressCounter(++$item, $count); } CM_Db_Db::replaceTable('cm_model_location_ip', 'cm_model_location_ip_new'); $this->_printInfoList($infoListWarning, '!'); }
public static function createAggregation() { CM_Db_Db::exec('DROP TABLE IF EXISTS `cm_tmp_location_new`'); CM_Db_Db::exec('CREATE TABLE `cm_tmp_location_new` LIKE `cm_tmp_location`'); CM_Db_Db::exec('INSERT INTO `cm_tmp_location_new` (`level`,`id`,`1Id`,`2Id`,`3Id`,`4Id`,`name`, `abbreviation`, `nameFull`, `lat`,`lon`) SELECT 1, `1`.`id`, `1`.`id`, NULL, NULL, NULL, `1`.`name`, `1`.`abbreviation`, CONCAT_WS(" ", `1`.`name`, `1`.`abbreviation`), NULL, NULL FROM `cm_model_location_country` AS `1` UNION SELECT 2, `2`.`id`, `1`.`id`, `2`.`id`, NULL, NULL, `2`.`name`, `2`.`abbreviation`, CONCAT_WS(" ", `2`.name, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), NULL, NULL FROM `cm_model_location_state` AS `2` LEFT JOIN `cm_model_location_country` AS `1` ON(`2`.`countryId`=`1`.`id`) UNION SELECT 3, `3`.`id`, `1`.`id`, `2`.`id`, `3`.`id`, NULL, `3`.`name`, NULL, CONCAT_WS(" ", `3`.`name`, `2`.`name`, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), `3`.`lat`, `3`.`lon` FROM `cm_model_location_city` AS `3` LEFT JOIN `cm_model_location_state` AS `2` ON(`3`.`stateId`=`2`.`id`) LEFT JOIN `cm_model_location_country` AS `1` ON(`3`.`countryId`=`1`.`id`) UNION SELECT 4, `4`.`id`, `1`.`id`, `2`.`id`, `3`.`id`, `4`.`id`, `4`.`name`, NULL, CONCAT_WS(" ", `4`.`name`, `3`.`name`, `2`.`name`, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), `4`.`lat`, `4`.`lon` FROM `cm_model_location_zip` AS `4` LEFT JOIN `cm_model_location_city` AS `3` ON(`4`.`cityId`=`3`.`id`) LEFT JOIN `cm_model_location_state` AS `2` ON(`3`.`stateId`=`2`.`id`) LEFT JOIN `cm_model_location_country` AS `1` ON(`3`.`countryId`=`1`.`id`)'); CM_Db_Db::exec('DROP TABLE IF EXISTS `cm_tmp_location_coordinates_new`'); CM_Db_Db::exec('CREATE TABLE `cm_tmp_location_coordinates_new` LIKE `cm_tmp_location_coordinates`'); CM_Db_Db::exec('INSERT INTO `cm_tmp_location_coordinates_new` (`level`,`id`,`coordinates`) SELECT 3, `id`, POINT(lat, lon) FROM `cm_model_location_city` WHERE `lat` IS NOT NULL AND `lon` IS NOT NULL UNION SELECT 4, `id`, POINT(lat, lon) FROM `cm_model_location_zip` WHERE `lat` IS NOT NULL AND `lon` IS NOT NULL'); CM_Db_Db::replaceTable('cm_tmp_location', 'cm_tmp_location_new'); CM_Db_Db::replaceTable('cm_tmp_location_coordinates', 'cm_tmp_location_coordinates_new'); }