IP Tool - IP Address Database
Introduction
For a long time I used the SxGeo library from zapimir . And until recently, everything was fine with me. Satisfied until it was necessary to add your data to the database.
Not finding the data packer from SxGeo on the Internet and not finding the strength to demand the functionality I needed from the developer, it was decided to write my own crutch. Although this decision was also influenced by 2 more drawbacks of the library used:
- limit on the number of directories;
- the inability to know the interval of addresses, which includes the desired address;
- no packagist pack
Actually, I share with you my development .
Differences between the prototype and my solution:
- IPTool is just a tool for creating a database and searching in it, while the SxGeo project is a project that provides not only the tools, but also the databases themselves;
- The IPTool database takes up more space (because the first address of the range is stored completely and takes 4 bytes, while in SxGeo only 3 bytes);
- IPTool has only one mode - reading data from a disk (the mode of loading the base into memory is in the plans);
- In addition to the data, IPTool returns the range of IP addresses that the search address is for;
- IPTool provides methods for obtaining data from reference books (all or by ordinal number);
- The IPTool database provides for the possibility of licensing the database itself;
- IPTool is easily installed using Composer ;
Using
IP Tool Initialization
/* - /path/to/iptool.database */ $iptool = new \Ddrv\Iptool\Iptool('/path/to/iptool.database'); Retrieving database information
print_r($iptool->about()); Array ( [created] => 1507199627 [author] => Anonymous Author [license] => MIT [networks] => Array ( [count] => 276148 [data] => Array ( [country] => Array ( [0] => code [1] => name ) ) ) ) Search for IP Address Information
print_r($iptool->find('81.32.17.89')); Array ( [network] => Array ( [0] => 81.32.0.0 [1] => 81.48.0.0 ) [data] => Array ( [country] => Array ( [code] => es [name] => Spain ) ) ) Get all items of the directory
print_r($iptool->getRegister('country')); Array ( [1] => Array ( [code] => cn [name] => China ) [2] => Array ( [code] => es [name] => Spain ) ... [N] => Array ( [code] => jp [name] => Japan ) ) Receiving an item of the directory on its ordinal number
print_r($iptool->getRegister('country',2)); Array ( [code] => cn [name] => China ) ) The process of creating a database is more time consuming, but it is described from the documentation, which is available in the repository and on the GitHub wiki in Russian and broken English.
UPD1. Comparison of IPTool and SxGeo speeds
For more reliable results, I created a database for IPTool based on SxGeo data.
Preparing for the comparative test
$ cd /path/to/test/dir $ mkdir csv $ mkdir csv/sxgeo $ mkdir t you need to copy the files SxGeo.php and SxGeoCity.dat to the current directory (/ path / to / test / dir)
Install IPTool
$ composer require ddrv/iptool:~1.0 Import SxGeo DB into csv files
import.php
<?php /* SxGeo csv */ include_once __DIR__.DIRECTORY_SEPARATOR.'SxGeo.php'; class ExtSxGeo extends SxGeo { public function parseBase() { $s=0; $firstIp = '0.0.0.0'; $seek = 0; $data = $this->parseCity($seek,1); $sxNet = fopen(__DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxNet.csv','w'); $sxCnt = fopen(__DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxCnt.csv','w'); $sxRgn = fopen(__DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxRgn.csv','w'); $sxCts = fopen(__DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxCts.csv','w'); $ids = [ 'cnt' => [], 'rgn' => [], 'cts' => [], ]; for ($octet=1;$octet<=223;$octet++) { $bip = pack('C',$octet); $min = $this->b_idx_arr[$octet-1]; $max = $this->b_idx_arr[$octet]; for ($b=$min; $b<=$max;$b++) { fseek($this->fh, $this->db_begin + $b * $this->block_len); $block = fread($this->fh, $this->block_len); $i = unpack('C4',$bip.substr($block,0,3)); $ip = implode('.',$i); $lastIp = long2ip(ip2long($ip)-1); $csvNet = [ $firstIp, $lastIp, $data['city']['id'], $data['region']['id'], $data['country']['id'], ]; fputcsv($sxNet,$csvNet); if (!isset($ids['cts'][$data['city']['id']])) { $ids['cts'][$data['city']['id']] = true; $csvCts = [ $data['city']['id'], $data['city']['lat'], $data['city']['lon'], $data['city']['name_ru'], $data['city']['name_en'], ]; fputcsv($sxCts,$csvCts); } if (!isset($ids['rgn'][$data['region']['id']])) { $ids['rgn'][$data['region']['id']] = true; $csvRgn = [ $data['region']['id'], $data['region']['iso'], $data['region']['name_ru'], $data['region']['name_en'], ]; fputcsv($sxRgn,$csvRgn); } if (!isset($ids['cnt'][$data['country']['id']])) { $ids['cnt'][$data['country']['id']] = true; $csvCnt = [ $data['country']['id'], $data['country']['iso'], $data['country']['lat'], $data['country']['lon'], $data['country']['name_ru'], $data['country']['name_en'], ]; fputcsv($sxCnt,$csvCnt); } $firstIp = $ip; $seek = hexdec(bin2hex(substr($block, $this->block_len - $this->id_len, $this->id_len))); $data = $this->parseCity($seek,1); } } $lastIp = '255.255.255.255'; $csvNet = [ $firstIp, $lastIp, $data['city']['id'], $data['region']['id'], $data['country']['id'], ]; fputcsv($sxNet,$csvNet); if (!isset($ids['cts'][$data['city']['id']])) { $ids['cts'][$data['city']['id']] = true; $csvCts = [ $data['city']['id'], $data['city']['lat'], $data['city']['lon'], $data['city']['name_ru'], $data['city']['name_en'], ]; fputcsv($sxCts,$csvCts); } if (!isset($ids['rgn'][$data['region']['id']])) { $ids['rgn'][$data['region']['id']] = true; $csvRgn = [ $data['region']['id'], $data['region']['iso'], $data['region']['name_ru'], $data['region']['name_en'], ]; fputcsv($sxRgn,$csvRgn); } if (!isset($ids['cnt'][$data['country']['id']])) { $ids['cnt'][$data['country']['id']] = true; $csvCnt = [ $data['country']['id'], $data['country']['iso'], $data['country']['lat'], $data['country']['lon'], $data['country']['name_ru'], $data['country']['name_en'], ]; fputcsv($sxCnt,$csvCnt); } fclose($sxNet); fclose($sxCnt); fclose($sxRgn); fclose($sxCts); } } $sxgeo = new ExtSxGeo( __DIR__.DIRECTORY_SEPARATOR.'SxGeoCity.dat',2); $sxgeo->parseBase(); Run the script and wait.
$ php import.php Creating an IPTool database from received csv files
convert.php
<?php /* IPTool csv */ require_once(__DIR__.DIRECTORY_SEPARATOR.'vendor'.DIRECTORY_SEPARATOR.'autoload.php'); /* . . */ $tmpDir = __DIR__.'/t'; /* Converter. */ $converter = new \Ddrv\Iptool\Converter($tmpDir); /* . . */ $dbFile = __DIR__.DIRECTORY_SEPARATOR.'iptool.sxgeo.city.dat'; /* CSV . */ $sxNet = __DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxNet.csv'; $sxCnt = __DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxCnt.csv'; $sxRgn = __DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxRgn.csv'; $sxCts = __DIR__.DIRECTORY_SEPARATOR.'csv'.DIRECTORY_SEPARATOR.'sxgeo'.DIRECTORY_SEPARATOR.'sxCts.csv'; /* . */ $converter->setAuthor('Ivan Dudarev'); /* . */ $converter->setLicense('MIT'); /* CSV. */ $converter->addCSV('sxNet',$sxNet); $converter->addCSV('sxCnt',$sxCnt); $converter->addCSV('sxRgn',$sxRgn); $converter->addCSV('sxCts',$sxCts); /* Country. */ $country = array( 'id' => array( 'type' => 'int', 'column' => 0, ), 'iso' => array( 'type' => 'string', 'column' => 1, 'transform' => 'low', ), 'lat' => array( 'type' => 'double', 'column' => 2, ), 'lon' => array( 'type' => 'double', 'column' => 3, ), 'nameRu' => array( 'type' => 'string', 'column' => 4, ), 'nameEn' => array( 'type' => 'string', 'column' => 5, ), ); $converter->addRegister('country','sxCnt',0, $country); /* Region. */ $region = array( 'id' => array( 'type' => 'int', 'column' => 0, ), 'iso' => array( 'type' => 'string', 'column' => 1, 'transform' => 'low', ), 'nameRu' => array( 'type' => 'string', 'column' => 2, ), 'nameEn' => array( 'type' => 'string', 'column' => 3, ), ); $converter->addRegister('region','sxRgn',0, $region); /* City. */ $city = array( 'id' => array( 'type' => 'int', 'column' => 0, ), 'lat' => array( 'type' => 'double', 'column' => 1, ), 'lon' => array( 'type' => 'double', 'column' => 2, ), 'nameRu' => array( 'type' => 'string', 'column' => 3, ), 'nameEn' => array( 'type' => 'string', 'column' => 4, ), ); $converter->addRegister('city','sxCts',0, $city); /* . */ $data = array( 'city' => 2, 'region' => 3, 'country' => 4, ); $converter->addNetworks('sxNet', 'ip', 0, 1, $data); $errors = $converter->getErrors(); if (!$errors) { $converter->create($dbFile); } else { print_r($errors); } Run the script and wait.
$ php convert.php Database size comparison
$ ls -l *.dat ... -rw-r--r-- 1 www www 13435116 Jun 30 15:46 SxGeoCity.dat -rw-r--r-- 1 www www 33190825 Oct 12 06:40 iptool.sxgeo.city.dat ... IPTool base volume is 3 times larger (which is not a plus)
compare.php
<?php require_once(__DIR__.DIRECTORY_SEPARATOR.'Iptool.php'); require_once(__DIR__.DIRECTORY_SEPARATOR.'SxGeo.php'); $dbFile = __DIR__.DIRECTORY_SEPARATOR.'iptool.sxgeo.city.dat'; $iptool = new \Ddrv\Iptool\Iptool($dbFile); $sxgeo = new SxGeo( __DIR__.DIRECTORY_SEPARATOR.'SxGeoCity.dat',2); /* */ $ips = []; for ($i=0;$i<100;$i++) { $ipa = []; for($octet = 0;$octet<4;$octet++) { $ipa[] = rand(0,255); } $ip = implode('.',$ipa); $ips[] = $ip; } /* IPTool */ $res = []; $t1 = microtime(true); foreach ($ips as $ip) { $res[] = $iptool->find($ip); } $t2 = microtime(true); echo 'IP Tool : '.($t2-$t1).PHP_EOL; /* SxGeo */ $res = []; $t1 = microtime(true); foreach ($ips as $ip) { $res[] = $sxgeo->getCityFull($ip); } $t2 = microtime(true); echo 'SxGeo : '.($t2-$t1).PHP_EOL; Comparative speed test iptool-1.0.6 and SxGeo-2.2.3
$ php compare.php The result of three tests on 100 addresses
IP Tool : 0.026905059814453 SxGeo : 0.031632900238037 IP Tool : 0.025413036346436 SxGeo : 0.023004055023193 IP Tool : 0.016932010650635 SxGeo : 0.022341012954712 The result of three tests at 1 address
IP Tool : 0.0013048648834229 SxGeo : 0.00016021728515625 IP Tool : 0.00047779083251953 SxGeo : 0.00011301040649414 IP Tool : 0.00046205520629883 SxGeo : 0.00035595893859863 UPD2. In version 1.0.7, the search algorithm is transferred to binary search.
Comparative speed test iptool-1.0.7 and SxGeo-2.2.3
$ php compare.php The result of three tests on 100 addresses
IP Tool : 0.012892961502075 SxGeo : 0.033740043640137 IP Tool : 0.0073931217193604 SxGeo : 0.032436847686768 IP Tool : 0.0043089389801025 SxGeo : 0.028012990951538 The result of three tests at 1 address
IP Tool : 0.0011000633239746 SxGeo : 0.0009000301361084 IP Tool : 0.00040006637573242 SxGeo : 0.00079989433288574 IP Tool : 0.00030016899108887 SxGeo : 0.00020003318786621 Conclusion
Need to work on the size of the database;
- to realize the connection between directories, it will noticeably reduce the size of the base ranges;
- glue the intervals with the same data (although in this database there are none, they are taken from SxGeo as it is);
- store the starting addresses of the ranges as 3x bytes, as in SxGeo.
UPD 3
On some projects, the converter is not needed (the database is generated in one project and duplicated in others), which added an extra dependency to the project (pdo_sqlite). In this regard, it was decided to divide the libraries into 2 projects. Well, under the guise of changing the namespace.
Now the project lives here:
Creating a base GitHub Packagist
Search GitHub Packagist database
')
Source: https://habr.com/ru/post/339748/
All Articles