Checkpoint. Conversion of some SQL tables works now.

svn path=/branches/tk100/; revision=4104

2 files changed, 78 insertions, 18 deletions

diff --git a/rpkid/rpki/irdb/models.py b/rpkid/rpki/irdb/models.py
index 5afbebb3..d2db4067 100644
--- a/rpkid/rpki/irdb/models.py
+++ b/rpkid/rpki/irdb/models.py
@@ -64,10 +64,17 @@ class BinaryField(django.db.models.Field):
     else:
       return "BLOB"
 
-## @var IP_VERSION_MAP
-# Choice map for IP version enumerations.
-
-IP_VERSION_MAP = ChoiceMap("IPv4", "IPv6")
+## @var ip_version_map
+# Custom choice map for IP version enumerations, so we can use the
+# obvious numeric values in the database, which is a bit easier on
+# anybody reading the raw SQL.
+#
+ip_version_map = { "IPv4" : 4, "IPv6" : 6 }
+
+## @var ip_version_choices
+# Choice argument for fields implementing IP version numbers.
+#
+ip_version_choices = [(y, x) for (x, y) in ip_version_map.iteritems()]
 
 ###
 
@@ -113,8 +120,8 @@ class ChildNet(django.db.models.Model):
   child_net_id = django.db.models.BigIntegerField(unique = True)
   start_ip = django.db.models.CharField(max_length = 40)
   end_ip   = django.db.models.CharField(max_length = 40)
-  version_map = IP_VERSION_MAP
-  version = django.db.models.PositiveSmallIntegerField(choices = version_map.choices)
+  version_map = ip_version_map
+  version = django.db.models.PositiveSmallIntegerField(choices = ip_version_choices)
   child = django.db.models.ForeignKey(Child, related_name = "address_ranges")
 
 class Parent(BPKICertificate):
@@ -129,13 +136,12 @@ class Parent(BPKICertificate):
 
 class ROARequest(django.db.models.Model):
   identity = django.db.models.ForeignKey(Identity, related_name = "roa_requests")
-  handle = HandleField()
   asn = django.db.models.BigIntegerField()
 
 class ROARequestPrefix(django.db.models.Model):
   roa_request = django.db.models.ForeignKey(ROARequest, related_name = "prefixes")
-  version_map = IP_VERSION_MAP
-  version = django.db.models.PositiveSmallIntegerField(choices = version_map.choices)
+  version_map = ip_version_map
+  version = django.db.models.PositiveSmallIntegerField(choices = ip_version_choices)
   prefix = django.db.models.CharField(max_length = 40)
   prefixlen = django.db.models.PositiveSmallIntegerField()
   max_prefixlen = django.db.models.PositiveSmallIntegerField()
diff --git a/scripts/convert-from-entitydb-to-sql.py b/scripts/convert-from-entitydb-to-sql.py
index 4e82783c..09c5e973 100644
--- a/scripts/convert-from-entitydb-to-sql.py
+++ b/scripts/convert-from-entitydb-to-sql.py
@@ -50,6 +50,8 @@ sql_database = cfg.get("sql-database")
 sql_username = cfg.get("sql-username")
 sql_password = cfg.get("sql-password")
 
+# Rename the old SQL tables, if they exist
+
 db = MySQLdb.connect(user = sql_username, db = sql_database, passwd = sql_password)
 cur = db.cursor()
 
@@ -67,6 +69,8 @@ for table in tables:
     print "Renaming %s to old_%s" % (table, table)
     cur.execute("ALTER TABLE %s RENAME TO old_%s" % (table, table))
 
+# Configure the Django model system
+
 from django.conf import settings
 
 settings.configure(
@@ -82,21 +86,71 @@ settings.configure(
 
 import rpki.irdb
 
+# Create the model-based tables if they don't already exist
+
 import django.core.management
 
-django.core.management.call_command("syncdb", verbosity = 4)
+django.core.management.call_command("syncdb", verbosity = 4, load_initial_data = False)
 
-def ns(tag):
-  return "{http://www.hactrn.net/uris/rpki/myrpki/}" + tag
+# From here down will be an awful lot of messing about with XML and
+# X.509 data, extracting stuff from the old database and whacking it
+# into the new.  Still working out these bits.
 
-e = ElementTree(file = os.path.join(entitydb, "identity.xml")).getroot()
+xmlns = "{http://www.hactrn.net/uris/rpki/myrpki/}"
 
-t = ns("identity")
+tag_authorization    = xmlns + "authorization"
+tag_bpki_child_ta    = xmlns + "bpki_child_ta"
+tag_bpki_client_ta   = xmlns + "bpki_client_ta"
+tag_bpki_resource_ta = xmlns + "bpki_resource_ta"
+tag_bpki_server_ta   = xmlns + "bpki_server_ta"
+tag_bpki_ta          = xmlns + "bpki_ta"
+tag_contact_info     = xmlns + "contact_info"
+tag_identity         = xmlns + "identity"
+tag_parent           = xmlns + "parent"
+tag_repository       = xmlns + "repository"
 
-if e.tag == t:
-  print "Found", t, "handle", e.get("handle")
-else:
-  print "Didn't find", t, "found", e.tag, "instead, oops"
+e = ElementTree(file = os.path.join(entitydb, "identity.xml")).getroot()
+assert e.tag == tag_identity
+
+handle = e.get("handle")
+
+# Check handle against what's in rpki.conf?
+
+# Create identity if we haven't already
+
+identity = rpki.irdb.Identity.objects.get_or_create(handle = handle)[0]
+
+# Copy over any ROA requests
+
+cur.execute("""
+            SELECT roa_request_id, asn FROM old_roa_request
+            WHERE roa_request_handle = %s
+            """, (handle,))
+for roa_request_id, asn in cur.fetchall():
+  roa_request = rpki.irdb.ROARequest.objects.get_or_create(identity = identity, asn = asn)[0]
+  cur.execute("""
+              SELECT prefix, prefixlen, max_prefixlen, version FROM old_roa_request_prefix
+              WHERE roa_request_id = %s
+              """, (roa_request_id,))
+  for prefix, prefixlen, max_prefixlen, version in cur.fetchall():
+    rpki.irdb.ROARequestPrefix.objects.get_or_create(
+      roa_request = roa_request,
+      version = version,
+      prefix = prefix,
+      prefixlen = prefixlen,
+      max_prefixlen = max_prefixlen)
+
+# Copy over any Ghostbuster requests.  This doesn't handle
+# Ghostbusters bound to specific parents yet, because I haven't yet
+# written the code to copy parent objects from entitydb.
+
+cur.execute("""
+            SELECT vcard FROM old_ghostbuster_request
+            WHERE self_handle = %s AND parent_handle IS NULL
+            """, (handle,))
+for row in cur.fetchall():
+  rpki.irdb.GhostbusterRequest.objects.get_or_create(identity = identity, vcard = row[0],
+                                                     defaults = { "parent" : None })
 
 cur.close()
 db.close()