geoip_convert.pwn

#include <a_samp>
#include <sscanf2>
#include <sqlitei>

new intRanges[] =
{
	0, // 0
	0, // 1
	0, // 2
	0, // 3
	0, // 4
	0, // 5
	0, // 6
	0, // 7
	16777216, // 8
	8388608, // 9
	4194304, // 10
	2097152, // 11
	1048576, // 12
	524288, // 13
	262144, // 14
	131072, // 15
	65535, // 16
	32768, // 17
	16394, // 18
	8192, // 19
	4096, // 20
	2048, // 21
	1024, // 22
	512, // 23
	256, // 24
	128, // 25
	64, // 26
	32, // 27
	16, // 28
	8, // 29
	4, // 30
	2, // 31
	1, // 32

};

public OnFilterScriptInit() {
	// much faster to use 2 files than to use UPDATE queries on one of them!
	new DB:geoip = db_open("geoip_city.db");
	new DB:geoip2 = db_open("geoip_city2.db");

	new string[1024];
	format(string, sizeof(string), "UPDATE `blocks` SET `geoname_id` = `registered_country_geoname_id`, `latitude` = '0.0', `longitude` = '0.0' WHERE `geoname_id` IS NULL");
	db_free_result(db_query(geoip, string));
	format(string, sizeof(string), "DELETE FROM `blocks` WHERE `geoname_id` IS NULL");
	db_free_result(db_query(geoip, string));
	format(string, sizeof(string), "SELECT * FROM `blocks`");
	new DBResult:res = db_query(geoip, string);
	
	new DBResult:res2 = db_query(geoip2, "SELECT * from `blocks`");
	
	new x = tickcount();
	
	new i;
	do {
		i++;
		printf("row: %d", i);
		if(i < db_num_rows(res2)) continue;
		
	    new startip[MAX_PLAYER_NAME];
	    new masklen[5];
	    new locid[25];
	    new latitude[30];
	    new longitude[30];
	    new proxy[30];
	    
	    db_get_field_assoc(res, "network_start_ip", startip, sizeof(startip));
	    db_get_field_assoc(res, "geoname_id", locid, sizeof(locid));
	    db_get_field_assoc(res, "latitude", latitude, sizeof(latitude));
	    db_get_field_assoc(res, "longitude", longitude, sizeof(longitude));
	    db_get_field_assoc(res, "network_mask_length", masklen, sizeof(masklen));
	    db_get_field_assoc(res, "is_anonymous_proxy", proxy, sizeof(proxy));
	    
	    format(string, sizeof(string), "REPLACE INTO `blocks` (`locId`, `latitude`, `longitude`, `is_anonymous_proxy`, `startIpNum`, `endIpNum`) VALUES (%s, '%s', '%s', %s, %s, %s + %d)", locid, latitude, longitude, proxy, ip2long(startip), ip2long(startip), intRanges[strval(masklen)]);
		db_free_result(db_query(geoip2, string));
	} while(db_next_row(res));
	
	db_free_result(res);
	db_free_result(res2);
	
	db_close(geoip);
	db_close(geoip2);
	
	printf("Took %d:%d minutes to update geo ip db", floatround((((tickcount() - x) / 1000) / 60) % 60), floatround((((tickcount() - x) / 1000) % 60) % 60));
	
}

stock ip2long(ip[]) {
	new ips[4];
	sscanf(ip, "p<.>dddd", ips[0], ips[1], ips[2], ips[3]);
	new tmp[90];
	format(tmp, sizeof(tmp), "((16777216 * %d) + (65536 * %d) + (256 * %d) + %d)", ips[0], ips[1], ips[2], ips[3]);
	// math done later in the sql query to avoid 32 bit int limits
	return tmp;
}