Application notes

Download and create IP-address to country database.

The problem

Your application needs to know with some degree of certainty where a client is connecting from. Perhaps you are constrained to only deal with one of a set of countries. IP addresses seem a good solution to the problem, but the IP to country web services can get expensive.

The 8th solution

The solution we describe here demonstrates a few of the varied capabilities 8th brings to bear. The script:

  1. Creates a new SQLite database
  2. Downloads a ZIP file containing an IP to country database
  3. Extracts the relevant CSV file
  4. Process it into the database
All of these processes take place using 8th’s built-in functionality; no external programs are used, and the same code may be run on any of the platforms 8th supports.

The actual 8th code for the IP-to-country database is here; you may view it with any text-editor you like. A few highlights from the code will be mentioned below.

First, note that the remote ZIP-file is downloaded and the relevant content is unpacked, completely in-memory — it never hits the disk. This is a good solution if the data don’t take up too much of your system’s RAM, but beware that it isn’t always suitable.

Next, note the use of s:eachline to iterate over the CSV data. The script access the CSV data as one large string, and s:eachline splits it up into lines and feeds those lines to the actual data processing code.

Then note the use of prepared SQL statements and in particular the way they can take an array of data as arguments to the “bind” word.

Finally, note the manner in which we “fix up” problems. We start parsing the CSV file by splitting the string on “,”. But the last field is country-name, which may contain commas! So we look to see if the data to be inserted into the country table has more than two fields. If it does, we shift off the first item (which is the country code), and do a “join” on the remaining entries (using a “,” as the joining text). Then we “close” the two items on the stack into an array again.

After running the script, you’ll have a new SQLite database containing one table with the IP address ranges given to each country, and another containing the two-character country-code and long country-name. You would use the database by converting an IP address to a number, perhaps with code like this:

: ip2num \ -- n
  "." s:/ 
  ( swap >n swap 256 n:* n:+ )
  0 a:reduce ;
And then issuing a SQL query such as:
SELECT cc FROM ips WHERE ip1 <= ? and ip2 >= ?

Learn more

To find out more about 8th and how it can help meet your application development needs, please refer to the manual, browse this site or the 8th forum, or contact us.