/** * 把qqwry的二进制文件转存到sqlite数据库当中 * @param string $filepath sqlite数据库的文件路径 * @param integer $version 要转存的sqlite的版本信息,2代表sqlite2,3代表sqlite3 * @param boolean $use_china_province_name 是否对中国地区的第一个字段信息使用省份的名字 */ public function saveAsSqlite($filepath, $version = 3, $use_china_province_name = true) { include_once 'IpLocationSeekerSqlite.php'; if (file_exists($filepath)) { unlink($filepath); } // 删掉旧库,重新建库 $sqlite_seeker = new IpLocationSeekerSqlite($filepath, $version); $sql_create_table = "create table qqwry (ip integer primary key,\n\t\t\t\tcountry varchar(255),\n\t\t\t\tarea varchar(255)\n\t\t)"; $sqlite_seeker->execute($sql_create_table); $sqlite_seeker->beginTransaction(); // 使用事务来加快数据的插入速度 for ($i = 0; $i < $this->index_count; $i++) { $ip_index_pos = $this->first_index_pos + $i * 7; $ip_record_pos_pos = $ip_index_pos + 4; fseek($this->handle, $ip_index_pos, SEEK_SET); $ip_int = self::fgetint($this->handle, false); // 这一条记录所对应的ip地址 fseek($this->handle, $ip_record_pos_pos, SEEK_SET); $ip_record_pos = self::fgetint_with_three_bytes($this->handle); list($country, $area) = $this->get_country_and_area($ip_record_pos); $country = str_replace("\t", "", $country); $country = str_replace("\n", "", $country); if ($use_china_province_name) { $country = self::real_province_name($country); } $area = str_replace("\t", "", $area); $area = str_replace("\n", "", $area); // echo long2ip($ip_int), "\t", $country, "\t", $area, "\n"; $area = str_replace("'", "", $area); $sql = "insert into qqwry (ip, country, area) values ({$ip_int}, '{$country}', '{$area}')"; $sqlite_seeker->execute($sql); } $sqlite_seeker->commit(); }
include_once dirname(dirname(__FILE__)) . "/src/IpLocationSeekerBinary.php"; include_once dirname(dirname(__FILE__)) . "/src/IpLocationSeekerBinaryExtension.php"; include_once dirname(dirname(__FILE__)) . "/src/IpLocationSeekerSqlite.php"; $int_max = 2147483647; $sqlite_filepath = "/Users/york/htdocs/video_kankan/protected/data/qqwry.sqlite.3"; $qqwry_filepath = "/Users/york/htdocs/video_kankan/protected/data/qqwry.dat"; // PHP + 二进制 $seeker = new IpLocationSeekerBinary($qqwry_filepath); $start_time = microtime(true); for ($i = 10000; $i >= 0; $i--) { $ip = long2ip(rand(0, $int_max)); $seeker->seek($ip); } printf("[binary]\ttimes:10000\t%.2fs used\t\n", microtime(true) - $start_time); // Ext + 二进制 $seeker = new IpLocationSeekerBinaryExtension($qqwry_filepath); $start_time = microtime(true); for ($i = 100000; $i >= 0; $i--) { $ip = long2ip(rand(0, $int_max)); $seeker->seek($ip); } printf("[binary-e]\ttimes:100000\t%.2fs used\t\n", microtime(true) - $start_time); // PHP + sqlite $seeker = new IpLocationSeekerSqlite($sqlite_filepath); $start_time = microtime(true); for ($i = 100000; $i >= 0; $i--) { $ip = long2ip(rand(0, $int_max)); $seeker->seek($ip); } printf("[sqlite]\ttimes:100000\t%.2fs used\t\n", microtime(true) - $start_time);