rework routeview parser to use bulk sql inserts

remove index from model definition, and create it automatically after
bulk inserts to speed up processing

add family element to unique_together

svn path=/branches/tk161/; revision=4160

2 files changed, 29 insertions, 11 deletions

diff --git a/rpkid/portal-gui/scripts/rpkigui-import-routes.py b/rpkid/portal-gui/scripts/rpkigui-import-routes.py
index dab4f4b6..b8d2413d 100644
--- a/rpkid/portal-gui/scripts/rpkigui-import-routes.py
+++ b/rpkid/portal-gui/scripts/rpkigui-import-routes.py
@@ -1,6 +1,6 @@
 import sys, itertools, re
 
-from django.db import transaction
+from django.db import transaction, connection
 
 from rpki.gui.routeview import models
 from rpki.resource_set import resource_range_ipv4, resource_range_ipv6
@@ -45,18 +45,36 @@ for row in itertools.islice(f, 5, None):
 
 f.close()
 
-@transaction.commit_on_success
 def commit():
+    cursor = connection.cursor()
+
+    # an OperationalError exception is thrown when the index doesn't exist
+    try:
+        print 'Removing existing index...'
+        cursor.execute('DROP INDEX routeview_routeorigin_idx ON routeview_routeorigin')
+    except Exception, e:
+        print type(e)
+        print e
+    cursor.execute('BEGIN')
+
     print 'Deleting rows from table...'
-    models.RouteOrigin.objects.all().delete()
+    cursor.execute('DELETE FROM routeview_routeorigin')
 
+    print 'Adding rows to table...'
     for prefix, asns in prefixes.iteritems():
         family = 6 if ':' in prefix else 4
         cls = resource_range_ipv6 if family == 6 else resource_range_ipv4
         rng = cls.parse_str(prefix)
 
-        for asn in asns:
-            print 'Creating row for prefix=%s asn=%d' % (prefix, asn)
-            models.RouteOrigin.objects.create(prefix_min=rng.min, prefix_max=rng.max, family=family, asn=asn)
+        cursor.executemany("INSERT INTO routeview_routeorigin SET family=%s, asn=%s, prefix_min=X%s, prefix_max=X%s",
+                    [(family, asn, '%032x' % rng.min, '%032x' % rng.max) for asn in asns])
+
+    print 'Committing...'
+    cursor.execute('COMMIT')
+
+    print 'Creating index on table...'
+    cursor.execute('CREATE INDEX routeview_routeorigin_idx ON routeview_routeorigin (family, prefix_min, prefix_max)')
+
+    transaction.commit_unless_managed()
 
 commit()
diff --git a/rpkid/rpki/gui/routeview/models.py b/rpkid/rpki/gui/routeview/models.py
index 411f4876..4bb6eef5 100644
--- a/rpkid/rpki/gui/routeview/models.py
+++ b/rpkid/rpki/gui/routeview/models.py
@@ -29,12 +29,12 @@ class PositiveHugeIntegerField(models.Field):
 
 class RouteOrigin(models.Model):
 
-    asn = models.PositiveIntegerField(help_text='origin AS', db_index=True)
+    asn = models.PositiveIntegerField(help_text='origin AS')
     family = models.PositiveSmallIntegerField(help_text='IP version')
 
     # address stored as unsigned integer to faciliate lookups
-    prefix_min = PositiveHugeIntegerField(db_index=True)
-    prefix_max = PositiveHugeIntegerField(db_index=True)
+    prefix_min = PositiveHugeIntegerField()
+    prefix_max = PositiveHugeIntegerField()
 
     def as_range(self):
         """
@@ -61,8 +61,8 @@ class RouteOrigin(models.Model):
 
     class Meta:
         # sort order reflects what "sh ip bgp" outputs
-        ordering = ( 'prefix_min', 'prefix_max', 'asn' )
+        ordering = ( 'family', 'prefix_min', 'prefix_max', 'asn' )
 
-        unique_together = ('asn', 'prefix_min', 'prefix_max')
+        unique_together = ('family', 'asn', 'prefix_min', 'prefix_max')
 
 # vim:sw=4 ts=8 expandtab