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