First sort-of-complete version of conversion script. Only handles

self-hosted case so far, needs minor tweaks to deal with hosting.
Result looks vaguely sane but there's no code to test it yet.

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

2 files changed, 190 insertions, 32 deletions

diff --git a/rpkid/rpki/irdb/models.py b/rpkid/rpki/irdb/models.py
index 0821b5cd..5323e8d1 100644
--- a/rpkid/rpki/irdb/models.py
+++ b/rpkid/rpki/irdb/models.py
@@ -122,7 +122,6 @@ class ChildASN(django.db.models.Model):
   child = django.db.models.ForeignKey(Child, related_name = "asns")
 
 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
@@ -137,6 +136,7 @@ class Parent(Certificate):
   service_uri = django.db.models.CharField(max_length = 255)
   repository_type_map = ChoiceMap("none", "offer", "referral")
   repository_type = django.db.models.PositiveSmallIntegerField(choices = repository_type_map.choices)
+  referrer = HandleField(null = True, blank = True)
   referral_authorization = BinaryField(null = True, blank = True)
 
 class ROARequest(django.db.models.Model):
@@ -159,11 +159,11 @@ class GhostbusterRequest(django.db.models.Model):
 class Repository(Certificate):
   handle = HandleField()
   client_handle = HandleField()
-  bpki_ta = BinaryField()
+  ta = BinaryField()
   service_uri = django.db.models.CharField(max_length = 255)
   sia_base = django.db.models.TextField()
   parent = django.db.models.OneToOneField(Parent, related_name = "repository")
 
 class Client(Certificate):
   handle = HandleField()
-  bpki_ta = BinaryField()
+  ta = BinaryField()
diff --git a/scripts/convert-from-entitydb-to-sql.py b/scripts/convert-from-entitydb-to-sql.py
index b69e1ec2..11fc4b98 100644
--- a/scripts/convert-from-entitydb-to-sql.py
+++ b/scripts/convert-from-entitydb-to-sql.py
@@ -21,8 +21,8 @@ OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
 PERFORMANCE OF THIS SOFTWARE.
 """
 
-import sys, os, time, getopt, glob, subprocess
-import rpki.config, rpki.x509, rpki.relaxng
+import sys, os, time, getopt, glob, subprocess, base64
+import rpki.config, rpki.x509, rpki.relaxng, rpki.sundial
 from rpki.mysql_import import MySQLdb
 from lxml.etree import ElementTree
 
@@ -113,12 +113,12 @@ e = ElementTree(file = os.path.join(entitydb, "identity.xml")).getroot()
 rpki.relaxng.myrpki.assertValid(e)
 assert e.tag == tag_identity
 
-handle = e.get("handle")
-assert handle == cfg.get("handle", section = "myrpki")
+self_handle = e.get("handle")
+assert self_handle == cfg.get("handle", section = "myrpki")
 
 # Create identity if we haven't already
 
-identity = rpki.irdb.Identity.objects.get_or_create(handle = handle)[0]
+identity = rpki.irdb.Identity.objects.get_or_create(handle = self_handle)[0]
 
 # Some BPKI utillity routines
 
@@ -211,34 +211,184 @@ def xcert_hash(cert):
     hash =  hash[len("(stdin)="):]
   return hash
 
-# Build a table of all the cross-certified BPKI certificates.
-
-xcerts = {}
-
-for filename in glob.iglob(os.path.join("bpki", "*", "xcert.*.cer")):
-  h = filename.split(".")[-2]
-
-  if not h in xcerts:
-    xcerts[h] = []
-  xcerts[h].append(filename)
-
-  # While we're at this, check to make sure that our reproduction of
-  # the hash algorithm is working correctly.
-  #
-  assert xcert_hash(rpki.x509.X509(Auto_file = filename)) == h
+# OK, all this wretched cross-certification looks complicated, but
+# that's partly because of the way we've been doing it on disk.  The
+# new SQL/object based approach should make it much clearer:
+#
+#   Child cross certifies parent's resource TA in child's resource CA.
+#
+#   Parent cross certifies child's resource TA in parent's resource
+#   CA.
+#
+#   Repository cross certifies client's resource TA in repository's
+#   server CA.
+#
+#   Client cross certifies repository's server TA in client's resource
+#   CA.
+#
+# The remaining xcert files look to be TLS relics which no longer
+# serve any real purpose; in theory, those can just go away.
+
+# Let's try keeping track of all the xcert filenames we use, so we can
+# list the ones we didn't.
+
+xcert_filenames = set(glob.iglob(os.path.join(bpki, "*", "xcert.*.cer")))
+
+# Scrape child data out of the entitydb.
+
+for filename in glob.iglob(os.path.join(entitydb, "children", "*.xml")):
+  child_handle = os.path.splitext(os.path.split(filename)[1])[0]
+
+  e = ElementTree(file = filename).getroot()
+  rpki.relaxng.myrpki.assertValid(e)
+  assert e.tag == tag_parent
+
+  ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_child_ta))
+  xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta))
+  xcert_filenames.discard(xcfn)
+  xcert = rpki.x509.X509(Auto_file = xcfn)
 
+  cur.execute("""
+              SELECT registrant_id, valid_until FROM old_registrant
+              WHERE registry_handle = %s AND registrant_handle = %s
+              """, (self_handle, child_handle))
+  assert cur.rowcount == 1
+  registrant_id, valid_until = cur.fetchone()
+
+  valid_until = rpki.sundial.datetime.fromdatetime(valid_until)
+  assert valid_until == rpki.sundial.datetime.fromXMLtime(e.get("valid_until"))
+
+  child = rpki.irdb.Child.objects.get_or_create(
+    handle = child_handle,
+    valid_until = valid_until.to_sql(),
+    ta = ta.get_DER(),
+    certificate = xcert.get_DER(),
+    issuer = resource_ca)[0]
 
+  cur.execute("""
+              SELECT start_as, end_as FROM old_registrant_asn WHERE registrant_id = %s
+              """, (registrant_id,))
+  for start_as, end_as in cur.fetchall():
+    rpki.irdb.ChildASN.objects.get_or_create(
+      start_as = start_as,
+      end_as = end_as,
+      child = child)
 
-# Somewhere around here I'm going to run out of things to do other
-# than scraping through the horrible entitydb XML.  Bother.
+  cur.execute("""
+              SELECT start_ip, end_ip, version FROM old_registrant_net WHERE registrant_id = %s
+              """, (registrant_id,))
+  for start_ip, end_ip, version in cur.fetchall():
+    rpki.irdb.ChildNet.objects.get_or_create(
+      start_ip = start_ip,
+      end_ip = end_ip,
+      version = version,
+      child = child)
+
+# Scrape parent data out of the entitydb.
+
+for filename in glob.iglob(os.path.join(entitydb, "parents", "*.xml")):
+  parent_handle = os.path.splitext(os.path.split(filename)[1])[0]
+
+  e = ElementTree(file = filename).getroot()
+  rpki.relaxng.myrpki.assertValid(e)
+  assert e.tag == tag_parent
+
+  ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_resource_ta))
+  xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta))
+  xcert_filenames.discard(xcfn)
+  xcert = rpki.x509.X509(Auto_file = xcfn)
+
+  r = e.find(tag_repository)
+  repository_type = r.get("type")
+  if repository_type == "referral":
+    a = r.find(tag_authorization)
+    referrer = a.get("referrer")
+    referral_authorization = base64.b64decode(a.text)
+  else:
+    referrer = None
+    referral_authorization = None    
+
+  parent = rpki.irdb.Parent.objects.get_or_create(
+    handle = parent_handle,
+    parent_handle = e.get("parent_handle"),
+    child_handle = e.get("child_handle"),
+    ta = ta.get_DER(),
+    certificate = xcert.get_DER(),
+    repository_type = rpki.irdb.Parent.repository_type_map[repository_type],
+    referrer = referrer,
+    referral_authorization = referral_authorization,
+    issuer = resource_ca)[0]
+
+  # While we have the parent object in hand, load any Ghostbuster
+  # entries specific to this parent.
 
+  cur.execute("""
+              SELECT vcard FROM old_ghostbuster_request
+              WHERE self_handle = %s AND parent_handle = %s
+              """, (self_handle, parent_handle))
+  for row in cur.fetchall():
+    rpki.irdb.GhostbusterRequest.objects.get_or_create(
+      identity = identity,
+      parent = parent,
+      vcard = row[0])
+
+# Scrape repository data out of the entitydb.
+
+for filename in glob.iglob(os.path.join(entitydb, "repositories", "*.xml")):
+  repository_handle = os.path.splitext(os.path.split(filename)[1])[0]
+
+  e = ElementTree(file = filename).getroot()
+  rpki.relaxng.myrpki.assertValid(e)
+  assert e.tag == tag_repository
+
+  if e.get("type") != "confirmed":
+    continue
+
+  ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_server_ta))
+  xcfn = os.path.join(bpki, "resources", "xcert.%s.cer" % xcert_hash(ta))
+  xcert_filenames.discard(xcfn)
+  xcert = rpki.x509.X509(Auto_file = xcfn)
+
+  parent = rpki.irdb.Parent.objects.get(handle = e.get("parent_handle"))
+
+  rpki.irdb.Repository.objects.get_or_create(
+    handle = repository_handle,
+    client_handle = e.get("client_handle"),
+    ta = ta.get_DER(),
+    certificate = xcert.get_DER(),
+    service_uri = e.get("service_uri"),
+    sia_base = e.get("sia_base"),
+    parent = parent,
+    issuer = resource_ca)
+
+# Scrape client data out of the entitydb.
+
+for filename in glob.iglob(os.path.join(entitydb, "pubclients", "*.xml")):
+  client_handle = os.path.splitext(os.path.split(filename)[1])[0]
+
+  e = ElementTree(file = filename).getroot()
+  rpki.relaxng.myrpki.assertValid(e)
+  assert e.tag == tag_repository
+
+  assert e.get("type") == "confirmed"
+
+  ta = rpki.x509.X509(Base64 = e.findtext(tag_bpki_client_ta))
+  xcfn = os.path.join(bpki, "servers", "xcert.%s.cer" % xcert_hash(ta))
+  xcert_filenames.discard(xcfn)
+  xcert = rpki.x509.X509(Auto_file = xcfn)
+
+  rpki.irdb.Repository.objects.get_or_create(
+    handle = client_handle,
+    ta = ta.get_DER(),
+    certificate = xcert.get_DER(),
+    issuer = server_ca)
 
 # Copy over any ROA requests
 
 cur.execute("""
             SELECT roa_request_id, asn FROM old_roa_request
             WHERE roa_request_handle = %s
-            """, (handle,))
+            """, (self_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("""
@@ -253,17 +403,25 @@ for roa_request_id, asn in cur.fetchall():
       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.
+# Copy over any non-parent-specific Ghostbuster requests.
 
 cur.execute("""
             SELECT vcard FROM old_ghostbuster_request
             WHERE self_handle = %s AND parent_handle IS NULL
-            """, (handle,))
+            """, (self_handle,))
 for row in cur.fetchall():
-  rpki.irdb.GhostbusterRequest.objects.get_or_create(identity = identity, parent = None,
-                                                     vcard = row[0])
+  rpki.irdb.GhostbusterRequest.objects.get_or_create(
+    identity = identity,
+    parent = None,
+    vcard = row[0])
+
+# List cross certifications we didn't use.
+
+for filename in sorted(xcert_filenames):
+  cer = rpki.x509.X509(Auto_file = filename)
+  print "Unused cross-certificate:", filename, cer.getSubject()
+
+# Done!
 
 cur.close()
 db.close()