Category Archives: Web development

ISO 3166 country list: nicely formatted in CSV and SQL format

[updated 2014-06-29]

I recently had to add a country selection list on a form on the website of a customer who does business internationally, and couldn’t find an up-to-date, easy-to-use list of countries already available. The closest was the ISO 3166 country list (HTML version here), but there are a few issues with it in my opinion:

  • Country names are all capitalized, which makes them a bit harder to read (UNITED ARAB EMIRATES vs United Arab Emirates for example)
  • They list official country names, which can sometimes be quite long even in their “short” version, such as MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF.

I therefore took their latest list of 249 countries (as of February 2012) and fixed the capitalization/shortened the names of a few countries to more usual ones (e.g. Macedonia instead of MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF). I did the same with the list of French country names and merged the English and French lists together, resulting in a table with the following 3 columns:

  • ISO 3166 country code
  • English Name
  • French Name

These are free for everyone to download and redistribute. I hope it saves you some time.

Downloads – files last updated 2014-06-29

ISO 3166 country code/country name list – Excel format

ISO 3166 country code/country name list – CSV format

ISO 3166 country code/country name list – SQL format (creates an InnoDB table named countries with MySQL syntax)

Content Delivery Networks: Cloud Files vs CloudFront

In order to allow faster content delivery and reduce server load for Dinstinct, I started looking at content delivery network (CDN) options a few days ago. Dinstinct is fast-growing, but still relatively small, so using expensive big names was out of the question. I therefore turned my attention to cloud CDNs, namely Rackspace’s Cloud Files and Amazon’s CloudFront, and reviewed them to find which one would be best suited for the site’s needs.

Amazon CloudFront vs Rackspace Cloud Files

Pricing: Rackspace has a flat $0.18/GB fee, while Amazon starts at $0.12/GB in North America and its price goes down with volume. However, Amazon makes you pay for the bandwidth between their storage system (S3) and the CDN nodes, and they also charge a per request fee of $0.0075 per 10,000 requests, which could add an extra $0.75 per GB for 1 kB image thumbnails. Which one is the most affordable depends on your usage scenario – large files would be cheaper on S3, while smaller ones are cheaper on Cloud Files.

Network Reach: The Cloud Files comparison page currently states that

Rackspace has partnered with the market leader, Akamai Technologies, Inc. Akamai claims the most pervasive, highly-distributed CDN platform with over 84,000 servers in 72 countries within nearly 1,000 networks.

I was surprised after reading this, because my experience with Akamai is that they’re relatively expensive, and getting access to their network for $0.18/GB, with no monthly minimum, seemed almost too good to be true. When I ran a traceroute from my computer in Montreal to a Cloud Files host, however, I noticed that it took 7 hops, and the host was in New York. I found that a bit strange since a traceroute to www.akamai.com only requires 5 hops from here.

It turns out that while the two sentences that I quoted above from the Cloud Files comparison page are both individually correct, they are slightly misleading together, in my opinion. Yes, Rackspace uses Akamai. Yes, Akamai has servers within nearly 1,000 networks. However, Cloud Files CDN customers don’t have access to all these Akamai nodes – Cloud Files is connected to about 40 Akamai nodes, from what I later learned by contacting Rackspace support. That’s still twice as many as CloudFront and its 19 edge locations, but don’t expect to get access to the full Akamai network when using the Cloud Files CDN.

Support: I haven’t tried Amazon’s support, but I did contact Rackspace’s and must say that they lived by their “fanatical support” guarantee. They are very easy to get in touch with, be it by phone, by live chat or by opening a support ticket, and provide helpful answers even on accounts that only bring them a few dollars per month. I can’t say for sure that Rackspace is superior here, but given that Amazon charges for Premium Support, I think Rackspace at least has a slight advantage.

SSL Delivery: they both allow you to serve files from secure URLs (starting with https). Amazon charges a small premium for that, but Rackspace currenty does not.

Access Control: Amazon S3 (where you host files to be served with CloudFront) gives you some control on who can read or edit files. On Cloud Files, however, you only get one API key per account, and you need to open separate accounts (that will be billed separately) if you want separate API keys. S3 might therefore be a winner if you need to have different users accessing the same account with different permissions (different applications, for example).

I ended up selecting Rackspace for their support, network reach and simpler pricing, but I think Amazon’s offering is very competitive.

Getting started with Rackspace Cloud Files CDN: quick and easy

Once I decided to go with Cloud Files, the process was really easy. I opened an account and was told that they would call within an hour to verify it and then activate the account. I got the phone call about 15 minutes later, and after I answered a few questions, the account was activated.

I then downloaded their PHP API and played with it a little bit. The documentation was not 100% accurate (some variable names were no longer what the documentation mentioned), but I was able to connect to the account, list containers, create containers, and upload files very easily. I ended up only needing the following code to upload files:

require_once 'cloudfiles.php';
try
{
    $auth = new CF_Authentication(CLOUDFILES_USERNAME, CLOUDFILES_API_KEY);
    $auth->authenticate();
    $conn = new CF_Connection($auth);

    $cf_images = $conn->get_container(CLOUDFILES_CONTAINER_NAME);
    foreach ($files_to_upload as $filename)
    {
        $cf_file = $cf_images->create_object($filename);
        $cf_file->load_from_filename(OUR_IMAGE_DIRECTORY.'/'.$filename);
    }

    //close connection
    $conn->close();
}
catch (Exception $e) { }

I added this code to the product picture upload logic in Dinstinct’s admin interface, and product pictures are now automatically sent to Cloud Files when they are uploaded. We can therefore have the pictures served from the CDN without any extra work from the people uploading them and at a very reasonable price.

——

If you have any question or comment, please let me know by commenting this post. Thanks!