Постановка задачи достаточно тривиальна: нужно по IP адресу пользователя определить провайдера. Эти данные далее должны использоваться в своей системе аналитики, а также должна быть возможность сверить их с данными, например Google или Ripe.

Сразу скажу, что код, который будет приведен в статье — не идеален. Используемый язык программирования — 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

Сортировка в запросе обязательно нужна, так как бывает, что один диапазон входит в другой.