Add mirror URL country and location fields

And add new functionality to our mirrorresolv management command to
populate the country code and location columns from data retrieved from
GeoIP data.

Signed-off-by: Dan McGee <dan@archlinux.org>

4 files changed, 131 insertions, 15 deletions

diff --git a/mirrors/management/commands/mirrorresolv.py b/mirrors/management/commands/mirrorresolv.py
index 0370f8ed..7684e7d5 100644
--- a/mirrors/management/commands/mirrorresolv.py
+++ b/mirrors/management/commands/mirrorresolv.py
@@ -8,14 +8,17 @@ available.
 Usage: ./manage.py mirrorresolv
 """
 
-from django.core.management.base import NoArgsCommand
-
-import sys
 import logging
 import socket
+import sys
+
+from django.core.management.base import NoArgsCommand
+from django.contrib.gis.geos import Point
+from django.contrib.gis.utils import GeoIP
 
 from mirrors.models import MirrorUrl
 
+
 logging.basicConfig(
     level=logging.WARNING,
     format='%(asctime)s -> %(levelname)s: %(message)s',
@@ -23,6 +26,7 @@ logging.basicConfig(
     stream=sys.stderr)
 logger = logging.getLogger()
 
+
 class Command(NoArgsCommand):
     help = "Runs a check on all active mirror URLs to determine if they are reachable via IPv4 and/or v6."
 
@@ -37,20 +41,47 @@ class Command(NoArgsCommand):
 
         return resolve_mirrors()
 
+
 def resolve_mirrors():
     logger.debug("requesting list of mirror URLs")
+    gi = GeoIP()
     for mirrorurl in MirrorUrl.objects.filter(mirror__active=True):
         try:
             # save old values, we can skip no-op updates this way
-            oldvals = (mirrorurl.has_ipv4, mirrorurl.has_ipv6)
+            oldvals = (mirrorurl.has_ipv4, mirrorurl.has_ipv6,
+                    mirrorurl.country_code, mirrorurl.location)
             logger.debug("resolving %3i (%s)", mirrorurl.id, mirrorurl.hostname)
-            families = mirrorurl.address_families()
-            mirrorurl.has_ipv4 = socket.AF_INET in families
-            mirrorurl.has_ipv6 = socket.AF_INET6 in families
+            ipv4, ipv6 = mirrorurl.addresses()
+            mirrorurl.has_ipv4 = len(ipv4) > 0
+            mirrorurl.has_ipv6 = len(ipv6) > 0
             logger.debug("%s: v4: %s v6: %s", mirrorurl.hostname,
                     mirrorurl.has_ipv4, mirrorurl.has_ipv6)
+
+            records = [gi.record_by_addr(addr[0]) for addr in ipv4]
+            # if we had multiple addresses and they didn't all resolve to the
+            # same data, we don't want to record anything.
+            country_codes = set(record['country_code'] for record in records
+                    if record is not None)
+            if len(country_codes) == 1:
+                mirrorurl.country_code = country_codes.pop()
+                logger.debug("%s: country_code: %s", mirrorurl.hostname,
+                        mirrorurl.country_code)
+            elif len(country_codes) > 1:
+                mirrorurl.country_code = None
+
+            lon_lats = set((record['longitude'], record['latitude'])
+                    for record in records if record is not None)
+            if len(lon_lats) == 1:
+                lon_lat = lon_lats.pop()
+                mirrorurl.location = Point(lon_lat, srid=4326)
+                logger.debug("%s: location: %s", mirrorurl.hostname,
+                        mirrorurl.location)
+            elif len(lon_lats) > 1:
+                mirrorurl.location = None
+
             # now check new values, only update if new != old
-            newvals = (mirrorurl.has_ipv4, mirrorurl.has_ipv6)
+            newvals = (mirrorurl.has_ipv4, mirrorurl.has_ipv6,
+                    mirrorurl.country_code, mirrorurl.location)
             if newvals != oldvals:
                 logger.debug("values changed for %s", mirrorurl)
                 mirrorurl.save(force_update=True)
diff --git a/mirrors/migrations/0013_auto__add_field_mirrorurl_country_code__add_field_mirrorurl_location.py b/mirrors/migrations/0013_auto__add_field_mirrorurl_country_code__add_field_mirrorurl_location.py
new file mode 100644
index 00000000..d5d80077
--- /dev/null
+++ b/mirrors/migrations/0013_auto__add_field_mirrorurl_country_code__add_field_mirrorurl_location.py
@@ -0,0 +1,75 @@
+# -*- coding: utf-8 -*-
+from south.db import db
+from south.v2 import SchemaMigration
+from django.db import models
+
+
+class Migration(SchemaMigration):
+
+    def forwards(self, orm):
+        db.add_column('mirrors_mirrorurl', 'country_code',
+                      self.gf('django.db.models.fields.CharField')(default='', max_length=2, null=True, blank=True),
+                      keep_default=False)
+
+        db.add_column('mirrors_mirrorurl', 'location',
+                      self.gf('django.contrib.gis.db.models.fields.PointField')(null=True, geography=True),
+                      keep_default=False)
+
+    def backwards(self, orm):
+        db.delete_column('mirrors_mirrorurl', 'country_code')
+        db.delete_column('mirrors_mirrorurl', 'location')
+
+    models = {
+        'mirrors.mirror': {
+            'Meta': {'ordering': "('country', 'name')", 'object_name': 'Mirror'},
+            'active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'admin_email': ('django.db.models.fields.EmailField', [], {'max_length': '255', 'blank': 'True'}),
+            'country': ('django.db.models.fields.CharField', [], {'max_length': '255', 'db_index': 'True'}),
+            'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
+            'isos': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '255'}),
+            'notes': ('django.db.models.fields.TextField', [], {'blank': 'True'}),
+            'public': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'rsync_password': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '50', 'blank': 'True'}),
+            'rsync_user': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '50', 'blank': 'True'}),
+            'tier': ('django.db.models.fields.SmallIntegerField', [], {'default': '2'}),
+            'upstream': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['mirrors.Mirror']", 'null': 'True', 'on_delete': 'models.SET_NULL'})
+        },
+        'mirrors.mirrorlog': {
+            'Meta': {'object_name': 'MirrorLog'},
+            'check_time': ('django.db.models.fields.DateTimeField', [], {'db_index': 'True'}),
+            'duration': ('django.db.models.fields.FloatField', [], {'null': 'True'}),
+            'error': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '255', 'blank': 'True'}),
+            'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
+            'is_success': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'last_sync': ('django.db.models.fields.DateTimeField', [], {'null': 'True'}),
+            'url': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'logs'", 'to': "orm['mirrors.MirrorUrl']"})
+        },
+        'mirrors.mirrorprotocol': {
+            'Meta': {'ordering': "('protocol',)", 'object_name': 'MirrorProtocol'},
+            'default': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
+            'is_download': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'protocol': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '10'})
+        },
+        'mirrors.mirrorrsync': {
+            'Meta': {'object_name': 'MirrorRsync'},
+            'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
+            'ip': ('django.db.models.fields.CharField', [], {'max_length': '24'}),
+            'mirror': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'rsync_ips'", 'to': "orm['mirrors.Mirror']"})
+        },
+        'mirrors.mirrorurl': {
+            'Meta': {'object_name': 'MirrorUrl'},
+            'country': ('mirrors.models.NullCharField', [], {'db_index': 'True', 'max_length': '255', 'null': 'True', 'blank': 'True'}),
+            'country_code': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '2', 'null': 'True', 'blank': 'True'}),
+            'has_ipv4': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
+            'has_ipv6': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
+            'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
+            'location': ('django.contrib.gis.db.models.fields.PointField', [], {'null': 'True', 'geography': 'True'}),
+            'mirror': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'urls'", 'to': "orm['mirrors.Mirror']"}),
+            'protocol': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'urls'", 'on_delete': 'models.PROTECT', 'to': "orm['mirrors.MirrorProtocol']"}),
+            'url': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '255'})
+        }
+    }
+
+    complete_apps = ['mirrors']
diff --git a/mirrors/models.py b/mirrors/models.py
index 06b483d5..75c43a5f 100644
--- a/mirrors/models.py
+++ b/mirrors/models.py
@@ -1,7 +1,7 @@
 import socket
 from urlparse import urlparse
 
-from django.db import models
+from django.contrib.gis.db import models
 from django.core.exceptions import ValidationError
 from django_countries import CountryField
 
@@ -69,12 +69,20 @@ class MirrorUrl(models.Model):
             editable=False)
     has_ipv6 = models.BooleanField("IPv6 capable", default=False,
             editable=False)
+    country_code = models.CharField("GeoIP-resolved country code",
+           max_length=2, editable=False, null=True, blank=True,
+            default="")
+    location = models.PointField("GeoIP-resolved location",
+            editable=False, null=True, geography=True)
 
-    def address_families(self):
+    objects = models.GeoManager()
+
+    def addresses(self):
         hostname = urlparse(self.url).hostname
         info = socket.getaddrinfo(hostname, None, 0, socket.SOCK_STREAM)
-        families = [x[0] for x in info]
-        return families
+        ipv4 = [x[4] for x in info if x[0] == socket.AF_INET]
+        ipv6 = [x[4] for x in info if x[0] == socket.AF_INET6]
+        return (ipv4, ipv6)
 
     @property
     def hostname(self):
@@ -92,9 +100,9 @@ class MirrorUrl(models.Model):
         except Exception as e:
             raise ValidationError(e)
         try:
-            families = self.address_families()
-            self.has_ipv4 = socket.AF_INET in families
-            self.has_ipv6 = socket.AF_INET6 in families
+            ipv4, ipv6 = self.addresses()
+            self.has_ipv4 = len(ipv4) > 0
+            self.has_ipv6 = len(ipv6) > 0
         except socket.error as e:
             # We don't fail in this case; we'll just set both to False
             self.has_ipv4 = False
diff --git a/settings.py b/settings.py
index a75cc3ee..8d411a87 100644
--- a/settings.py
+++ b/settings.py
@@ -149,6 +149,8 @@ LOGGING = {
     },
 }
 
+## Directory where GeoIP data files are located
+GEOIP_PATH = '/usr/share/GeoIP/'
 
 ## Server used for linking to PGP keysearch results
 PGP_SERVER = 'pgp.mit.edu:11371'