Постановка задачи достаточно тривиальна: нужно по IP адресу пользователя определить провайдера. Эти данные далее должны использоваться в своей системе аналитики, а также должна быть возможность сверить их с данными, например Google или Ripe.
Сразу скажу, что код, который будет приведен в статье — не идеален. Используемый язык программирования — PHP (конечно же лучше использовать для подобных задач C или Perl). БД — MySQL (тут лучше выбрать БД, которая будет по шустрее и может обрабатывать большое кол-во селектов. Например Tarantool). Но в повседневной жизни хватит и данных технологий\языков.
Итак, приступим. Откуда же взять данные по провайдерам, да еще и желательно бесплатно? Очень быстро выбор пал на сервис RIPE. Минусом было то, что вся БД у них хранится в текстовых файлах. Некоторое время «погуглив» я не нашел конвертера для MySQL. Что ж, это немного осложняло задачу. Но не беда, вспоминая достаточно известный ролик и фразу из него: «Ты же специалист», я решил поискать описание файлов БД и написать парсер данных из текстового формата в MySQL.
Но и тут была небольшая засада. Описание полей есть, а вот связи между таблицами мне найти не удалось (может, конечно, плохо искал). А вот это уже стало достаточно ощутимой проблемой.
Далее я начал думать не в правильную сторону и попытался как-то сопоставить поля из файлов «налету». То есть запускал скрипт по парсингу одного файла (очень «костыльно» его парсил, что вспоминаю об этом примерно так: Праздник, ты напился и ничего не помнишь. А на следующий день тебе друзья рассказывают, как ты голый бегал по подъезду и орал матерные частушки и тебе безумно стыдно).
В итоге через несколько часов (да-да, именно через несколько часов) я осознал, что творю ерунду и решили все данные загнать в MySQL. Благо в ходе изучения данных удалось понять какие поля мне нужны для сопоставления.
Так как поля были известны, я создал следующие таблицы:
Поля в таблицах sip и eip — это декодированные ip2long IP адреса начала диапазона и его конца.
Класс для парсинга данных:
Я пишу данные блоками, то есть блок получили, сразу записали. Для более лучшей производительности конечно же лучше данные вставлять пачками. Код для загрузки и для записи в БД организаций выглядит так:
Поля в таблицу заносятся по тем же ключам, что есть в файлах. Если нужно изменить поля, то у метода save есть обработчик «перед вставкой», с помощью которого можно поменять названия полей и писать в измененные.
Теперь, когда данные получены, можно создать итоговую таблицу, по которой уже и будет проходить само определение.
Теперь осталось самое простое — это в итоговую таблицу перенести необходимые данные.
Все решается парой запросов:
Полученные данные загоняем в таблицу test_ripe и получаем счастье.
Результаты превзошли мои ожидания. Определение провайдера работает достаточно точно (протестировал на пуле адресов). Также, приятным бонусом оказалось то, что по этой базе определение работает лучше чем у 2ip
Ну и собственно определить провайдера по текущей таблице можно таким запросом:
Сортировка в запросе обязательно нужна, так как бывает, что один диапазон входит в другой.
Сразу скажу, что код, который будет приведен в статье — не идеален. Используемый язык программирования — PHP (конечно же лучше использовать для подобных задач C или Perl). БД — MySQL (тут лучше выбрать БД, которая будет по шустрее и может обрабатывать большое кол-во селектов. Например Tarantool). Но в повседневной жизни хватит и данных технологий\языков.
Поиск БД и извлечение из нее данных
Итак, приступим. Откуда же взять данные по провайдерам, да еще и желательно бесплатно? Очень быстро выбор пал на сервис RIPE. Минусом было то, что вся БД у них хранится в текстовых файлах. Некоторое время «погуглив» я не нашел конвертера для MySQL. Что ж, это немного осложняло задачу. Но не беда, вспоминая достаточно известный ролик и фразу из него: «Ты же специалист», я решил поискать описание файлов БД и написать парсер данных из текстового формата в MySQL.
Но и тут была небольшая засада. Описание полей есть, а вот связи между таблицами мне найти не удалось (может, конечно, плохо искал). А вот это уже стало достаточно ощутимой проблемой.
Далее я начал думать не в правильную сторону и попытался как-то сопоставить поля из файлов «налету». То есть запускал скрипт по парсингу одного файла (очень «костыльно» его парсил, что вспоминаю об этом примерно так: Праздник, ты напился и ничего не помнишь. А на следующий день тебе друзья рассказывают, как ты голый бегал по подъезду и орал матерные частушки и тебе безумно стыдно).
В итоге через несколько часов (да-да, именно через несколько часов) я осознал, что творю ерунду и решили все данные загнать в MySQL. Благо в ходе изучения данных удалось понять какие поля мне нужны для сопоставления.
Импорт данных в нашу БД
Так как поля были известны, я создал следующие таблицы:
CREATE TABLE `test_inetnum` (
`sip` bigint(20) unsigned NOT NULL,
`eip` bigint(20) unsigned NOT NULL,
`org` varchar(255) NOT NULL,
PRIMARY KEY (`sip`,`eip`),
KEY `idx_org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test_organization` (
`organisation` varchar(255) NOT NULL,
`org-name` varchar(255) NOT NULL,
PRIMARY KEY (`organisation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test_route` (
`sip` bigint(20) unsigned NOT NULL,
`eip` bigint(20) unsigned NOT NULL,
`origin` varchar(255) NOT NULL,
PRIMARY KEY (`sip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test_aut_num` (
`aut-num` varchar(255) NOT NULL,
`org` varchar(255) NOT NULL,
PRIMARY KEY (`aut-num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Поля в таблицах sip и eip — это декодированные ip2long IP адреса начала диапазона и его конца.
Класс для парсинга данных:
<?php
namespace Ripe;
class Ripe
{
/**
* @var string - папка для сохранения файлов от RIPE
*/
public $folder;
/**
* @var int - время, хранения файла
*/
public $time = 86400;
/**
* Ripe constructor.
*
* @param string $folder
*/
function __construct($folder = '')
{
if (empty($folder))
{
$folder = __DIR__ . '/../../config/ripe';
}
if (!is_dir(__DIR__ . '/../../config/ripe'))
{
mkdir(__DIR__ . '/../../config/ripe', 0777, true);
}
$this->folder = $folder;
}
/**
* Нужно ли обновлять файл.
*
* @param string $file
*
* @return bool
*/
function needUpdate($file = '')
{
$current = time();
if ($current - filectime($this->folder . '/' . $file) > $this->time)
{
return true;
}
return false;
}
/**
* Загрузка файла.
*
* @param string $url
*/
function download($url = '')
{
if (!empty($url)
&& true === $this->needUpdate($url)
)
{
system("cd " . realpath($this->folder) . " && wget ftp://ftp.ripe.net/ripe/dbase/split/" . $url . " && gunzip $url");
}
}
/**
* Чтение файла по блокам.
*
* @param string $file
* @param $callback
*/
function read($file = '', $callback)
{
if (is_file($this->folder . '/' . $file))
{
$f = fopen($this->folder . '/' . $file, 'r');
if (!empty($f))
{
$string = [];
while (($buffer = fgets($f)) !== false)
{
// запоминаем блок
if ("\n" != $buffer)
{
$string[] = trim($buffer);
}
else
{
$blockArray = [];
// дошли до конца блока
for ($i = 0; $i < $ic = count($string); $i++)
{
if (strpos($string[$i], ': ') === false)
{
break;
}
else
{
$arBlockData = explode(": ", $string[$i]);
if (!empty($arBlockData))
{
$key = trim($arBlockData[0]);
$value = trim($arBlockData[1]);
if (!empty($blockArray[$key]))
{
$blockArray[$key] .= $value . "\n";
}
else
{
$blockArray[$key] = $value;
}
}
}
}
// callback
if (!empty($callback)
&& is_callable($callback)
&& !empty($blockArray)
)
{
call_user_func_array($callback, [
$blockArray,
$file
]);
}
$string = '';
}
}
}
}
}
}
Я пишу данные блоками, то есть блок получили, сразу записали. Для более лучшей производительности конечно же лучше данные вставлять пачками. Код для загрузки и для записи в БД организаций выглядит так:
$ripe->download('ripe.db.organisation.gz');
$ripe->read('ripe.db.organisation', function ($block, $file) {
$ripeRoute = new \Ripe\RipeOrganization();
$ripeRoute->save($block);
});
Поля в таблицу заносятся по тем же ключам, что есть в файлах. Если нужно изменить поля, то у метода save есть обработчик «перед вставкой», с помощью которого можно поменять названия полей и писать в измененные.
Анализ данных и получение итоговой таблицы.
Теперь, когда данные получены, можно создать итоговую таблицу, по которой уже и будет проходить само определение.
CREATE TABLE `test_ripe` (
`sip` bigint(20) unsigned NOT NULL,
`eip` bigint(20) unsigned NOT NULL,
`org_code` varchar(100) NOT NULL,
`org_name` varchar(255) NOT NULL,
PRIMARY KEY (`sip`,`eip`),
KEY `idx_org_name` (`org_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Теперь осталось самое простое — это в итоговую таблицу перенести необходимые данные.
Все решается парой запросов:
select * from test_inetnum as t1
inner join test_organization as t2 on t1.org = t2.organisation;
select * from test_route as t1
inner join test_aut_num as t2 on t1.origin = t2.`aut-num`
inner join test_organization as t3 on t2.org = t3.organisation;
Полученные данные загоняем в таблицу test_ripe и получаем счастье.
Результаты
Результаты превзошли мои ожидания. Определение провайдера работает достаточно точно (протестировал на пуле адресов). Также, приятным бонусом оказалось то, что по этой базе определение работает лучше чем у 2ip
Ну и собственно определить провайдера по текущей таблице можно таким запросом:
SELECT * FROM `test_ripe` WHERE `sip` <= '33554435' AND `eip` >= '33554435' ORDER BY `eip` DESC LIMIT 1
Сортировка в запросе обязательно нужна, так как бывает, что один диапазон входит в другой.