Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: 0bb89cfb1c
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 84 lines (70 sloc) 3.743 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
BEGIN;
CREATE EXTENSION geoip;
INSERT INTO geoip_country(begin_ip, end_ip, country, name) VALUES ('78.31.24.0', '78.31.31.255', 'AA', 'Country A');
INSERT INTO geoip_country(begin_ip, end_ip, country, name) VALUES ('78.41.8.0', '78.41.23.255', 'BB', 'Country B');
INSERT INTO geoip_country(begin_ip, end_ip, country, name) VALUES ('78.44.0.0', '78.45.255.255', 'CC', 'Country C');
INSERT INTO geoip_country(begin_ip, end_ip, country, name) VALUES ('78.80.0.0', '78.80.255.255', 'DD', 'Country D');
INSERT INTO geoip_country(begin_ip, end_ip, country, name) VALUES ('78.102.0.0', '78.103.255.255', 'EE', 'Country E');
-- country CC
SELECT geoip_country_code('78.45.133.255'::inet);
 geoip_country_code
--------------------
 CC
(1 row)

SELECT * FROM geoip_country('78.45.133.255'::inet);
 begin_ip | end_ip | country | name
-----------+---------------+---------+-----------
 78.44.0.0 | 78.45.255.255 | CC | Country C
(1 row)

-- no matching country record
SELECT geoip_country_code('10.45.133.255'::inet);
 geoip_country_code
--------------------
 
(1 row)

SELECT * FROM geoip_country_code('10.45.133.255'::inet);
 geoip_country_code
--------------------
 
(1 row)

INSERT INTO geoip_city_location(loc_id, country, region, city, postal_code, latitude, longitude, metro_code, area_code) VALUES (21235, 'CZ', 52, 'A', NULL, 50.0833, 14.4667, NULL, NULL);
INSERT INTO geoip_city_location(loc_id, country, region, city, postal_code, latitude, longitude, metro_code, area_code) VALUES (37990, 'CZ', 78, 'B', NULL, 49.2, 16.6333, NULL, NULL);
INSERT INTO geoip_city_block(begin_ip, end_ip, loc_id) VALUES ('31.7.243.0', '31.7.243.31', 21235);
INSERT INTO geoip_city_block(begin_ip, end_ip, loc_id) VALUES ('31.30.3.72', '31.30.3.79', 21235);
INSERT INTO geoip_city_block(begin_ip, end_ip, loc_id) VALUES ('46.13.32.0', '46.13.63.255', 21235);
INSERT INTO geoip_city_block(begin_ip, end_ip, loc_id) VALUES ('46.13.240.0', '46.13.255.255', 37990);
-- city A
SELECT geoip_city_location('31.7.243.10'::inet);
 geoip_city_location
---------------------
               21235
(1 row)

SELECT * FROM geoip_city('31.7.243.10'::inet);
 loc_id | country | region | city | postal_code | latitude | longitude | metro_code | area_code
--------+---------+--------+------+-------------+----------+-----------+------------+-----------
  21235 | CZ | 52 | A | | 50.0833 | 14.4667 | |
(1 row)

-- no matching city record
SELECT geoip_city_location('10.7.243.10'::inet);
 geoip_city_location
---------------------
                    
(1 row)

SELECT * FROM geoip_city('10.7.243.10'::inet);
 loc_id | country | region | city | postal_code | latitude | longitude | metro_code | area_code
--------+---------+--------+------+-------------+----------+-----------+------------+-----------
        | | | | | | | |
(1 row)

INSERT INTO geoip_asn(begin_ip, end_ip, name) VALUES ('1.11.64.0', '1.11.95.255', 'AS38091 CJ-CABLENET');
INSERT INTO geoip_asn(begin_ip, end_ip, name) VALUES ('1.11.96.0', '1.11.127.255', 'AS38669 ChungNam Broadcastin Co.');
INSERT INTO geoip_asn(begin_ip, end_ip, name) VALUES ('1.11.128.0', '1.11.255.255', 'AS17839 DreamcityMedia');
-- ASN CABLENET
SELECT * FROM geoip_asn('1.11.66.10'::inet);
 begin_ip | end_ip | name
-----------+-------------+---------------------
 1.11.64.0 | 1.11.95.255 | AS38091 CJ-CABLENET
(1 row)

-- missing ASN records
SELECT * FROM geoip_asn('10.11.66.10'::inet);
  begin_ip | end_ip | name
------------+--------------+------------------------
 1.11.128.0 | 1.11.255.255 | AS17839 DreamcityMedia
(1 row)

ROLLBACK;
Something went wrong with that request. Please try again.