Encapsulate a lot of sql_fetch*() calls

svn path=/scripts/README; revision=1322

5 files changed, 133 insertions, 43 deletions

diff --git a/scripts/README b/scripts/README
index d3725e0e..5ecb35bd 100644
--- a/scripts/README
+++ b/scripts/README
@@ -161,9 +161,9 @@ Current TO DO list:
   Considerations (1) and (3) have to dominate, which may mean we take
   a hit on (2).
 
-- More dumb little methods missing: objects on the many side of a
-  one-to-many relationship should have convenience methods for
-  retrieving their parents.   Too many explicit calls to sql_fetch().
+- Most of the explicit calls to sql_fetch*() are now encapsulated in
+  one-line methods.  The remaining ones are probably hints at minor
+  bits of abstraction still to be done.
 
 - Need a logging subsystem, including syslog support.  Absent a better
   plan, see the Python syslog module.
diff --git a/scripts/biz-certs/Bob-CA.srl b/scripts/biz-certs/Bob-CA.srl
index db6035c9..cf31b56f 100644
--- a/scripts/biz-certs/Bob-CA.srl
+++ b/scripts/biz-certs/Bob-CA.srl
@@ -1 +1 @@
-90801F1ED19454D1
+90801F1ED19454D8
diff --git a/scripts/rpki/left_right.py b/scripts/rpki/left_right.py
index bead5c2c..8446f2d1 100644
--- a/scripts/rpki/left_right.py
+++ b/scripts/rpki/left_right.py
@@ -61,6 +61,14 @@ class base_elt(object):
 class data_elt(base_elt, rpki.sql.sql_persistant):
   """Virtual class for top-level left-right protocol data elements."""
 
+  def self(this, gctx):
+    """Fetch self object to which this object links."""
+    return self_elt.sql_fetch(gctx, this.self_id)
+
+  def bsc(self, gctx):
+    """Return BSC object to which this object links."""
+    return bsc_elt.sql_fetch(gctx, self.bsc_id)
+
   def make_reply(self, r_pdu = None):
     """Construct a reply PDU."""
     if r_pdu is None:
@@ -206,6 +214,26 @@ class self_elt(data_elt):
     """Extra SQL delete actions for self_elt -- handle extension preferences."""
     gctx.cur.execute("DELETE FROM self_pref WHERE self_id = %s", self.self_id)
 
+  def bscs(self, gctx):
+    """Fetch all BSC objects that link to this self object."""
+    return bsc_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+
+  def repositories(self, gctx):
+    """Fetch all repository objects that link to this self object."""
+    return repository_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+
+  def parents(self, gctx):
+    """Fetch all parent objects that link to this self object."""
+    return parent_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+
+  def children(self, gctx):
+    """Fetch all child objects that link to this self object."""
+    return child_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+
+  def route_origins(self, gctx):
+    """Fetch all route_origin objects that link to this self object."""
+    return route_origin_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+  
   def serve_pre_save_hook(self, q_pdu, r_pdu):
     """Extra server actions for self_elt -- handle extension preferences."""
     if self is not q_pdu:
@@ -259,13 +287,12 @@ class self_elt(data_elt):
   def client_poll(self, gctx):
     """Run the regular client poll cycle with each of this self's parents in turn."""
 
-    for parent in parent_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id):
+    for parent in self.parents(gctx):
 
       # This will need a callback when we go event-driven
       r_pdu = rpki.up_down.list_pdu.query(gctx, parent)
 
-      ca_map = dict((ca.parent_resource_class, ca)
-                    for ca in rpki.sql.ca_obj.sql_fetch_where(gctx, "parent_id = %s" % parent.parent_id))
+      ca_map = dict((ca.parent_resource_class, ca) for ca in parent.cas(gctx))
       for rc in r_pdu.payload.classes:
         if rc.class_name in ca_map:
           ca = ca_map[rc.class_name]
@@ -285,7 +312,7 @@ class self_elt(data_elt):
 
     now = rpki.sundial.datetime.utcnow()
 
-    for child in child_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id):
+    for child in self.children(gctx):
       child_certs = rpki.sql.child_cert_obj.sql_fetch_where(gctx, "child_id = %s AND revoked IS NULL" % child.child_id)
       if not child_certs:
         continue
@@ -294,7 +321,7 @@ class self_elt(data_elt):
       irdb_resources = rpki.left_right.irdb_query(gctx, child.self_id, child.child_id)
 
       for child_cert in child_certs:
-        ca_detail = rpki.sql.ca_detail_obj.sql_fetch(gctx, child_cert.ca_detail_id)
+        ca_detail = child_cert.ca_detail(gctx)
         if ca_detail.state != "active":
           continue
         old_resources = child_cert.cert.get_3779resources()
@@ -304,10 +331,10 @@ class self_elt(data_elt):
             gctx      = gctx,
             ca_detail = ca_detail,
             resources = new_resources,
-            sia       = rpki.sql.ca_obj.sql_fetch(gctx, ca_detail.ca_id).sia_uri())
+            sia       = ca_detail.ca(gctx).sia_uri())
         elif old_resources.valid_until < now:
-          parent = parent_elt.sql_fetch(gctx, ca.parent_id)
-          repository = repository_elt.sql_fetch(gctx, parent.repository_id)
+          parent = ca.parent(gctx)
+          repository = parent.repository(gctx)
           child_cert.sql_delete(gctx)
           ca_detail.generate_manifest(gctx)
           repository.publish(gctx,  (ca_detail.latest_manifest, ca_detail.manifest_uri(ca)))
@@ -321,9 +348,9 @@ class self_elt(data_elt):
     """
 
     now = rpki.sundial.datetime.utcnow()
-    for parent in parent_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id):
-      repository = repository_elt.sql_fetch(gctx, parent.repository_id)
-      for ca in rpki.sql.ca_obj.sql_fetch_where(gctx, "parent_id = %s" % parent.parent_id):
+    for parent in self.parents(gctx):
+      repository = parent.repository(gctx)
+      for ca in parent.cas(gctx):
         ca_detail = ca.fetch_active(gctx)
         #
         # Temporary kludge until I sort out initial publication.
@@ -370,6 +397,18 @@ class bsc_elt(data_elt):
     """Extra SQL delete actions for bsc_elt -- handle signing certs."""
     gctx.cur.execute("DELETE FROM bsc_cert WHERE bsc_id = %s", self.bsc_id)
 
+  def repositories(self, gctx):
+    """Fetch all repository objects that link to this BSC object."""
+    return repository_elt.sql_fetch_where(gctx, "bsc_id = %s" % self.bsc_id)
+
+  def parents(self, gctx):
+    """Fetch all parent objects that link to this BSC object."""
+    return parent_elt.sql_fetch_where(gctx, "bsc_id = %s" % self.bsc_id)
+
+  def children(self, gctx):
+    """Fetch all child objects that link to this BSC object."""
+    return child_elt.sql_fetch_where(gctx, "bsc_id = %s" % self.bsc_id)
+
   def serve_pre_save_hook(self, q_pdu, r_pdu):
     """Extra server actions for bsc_elt -- handle signing certs and key generation."""
     if self is not q_pdu:
@@ -432,6 +471,14 @@ class parent_elt(data_elt):
   cms_ta = None
   https_ta = None
 
+  def repository(self, gctx):
+    """Fetch repository object to which this parent object links."""
+    return repository_elt.sql_fetch(gctx, self.repository_id)
+
+  def cas(self, gctx):
+    """Fetch all CA objects that link to this parent object."""
+    return rpki.sql.ca_obj.sql_fetch_where(gctx, "parent_id = %s" % self.parent_id)
+
   def serve_post_save_hook(self, q_pdu, r_pdu):
     """Extra server actions for parent_elt."""
     if self.rekey or self.reissue or self.revoke:
@@ -476,7 +523,7 @@ class parent_elt(data_elt):
 
     For now, keep this dead simple lock step, rewrite it later.
     """
-    bsc = bsc_elt.sql_fetch(gctx, self.bsc_id)
+    bsc = self.bsc(gctx)
     if bsc is None:
       raise rpki.exceptions.BSCNotFound, "Could not find BSC %s" % self.bsc_id
     q_msg = rpki.up_down.message_pdu.make_query(q_pdu)
@@ -507,6 +554,14 @@ class child_elt(data_elt):
 
   cms_ta = None
 
+  def child_certs(self, gctx):
+    """Fetch all child_cert objects that link to this child object."""
+    return rpki.sql.child_cert_obj.sql_fetch_where(gctx, "child_id = %s" % self.child_id)
+
+  def parents(self, gctx):
+    """Fetch all parent objects that link to self object to which this child object links."""
+    return parent_elt.sql_fetch_where(gctx, "self_id = %s" % self.self_id)
+
   def serve_post_save_hook(self, q_pdu, r_pdu):
     """Extra server actions for child_elt."""
     if self.reissue:
@@ -536,7 +591,7 @@ class child_elt(data_elt):
 
   def serve_up_down(self, gctx, query):
     """Outer layer of server handling for one up-down PDU from this child."""
-    bsc = bsc_elt.sql_fetch(gctx, self.bsc_id)
+    bsc = self.bsc(gctx)
     if bsc is None:
       raise rpki.exceptions.BSCNotFound, "Could not find BSC %s" % self.bsc_id
     q_elt = rpki.cms.xml_verify(query, self.cms_ta)
@@ -577,6 +632,10 @@ class repository_elt(data_elt):
   cms_ta = None
   https_ta = None
 
+  def parents(self, gctx):
+    """Fetch all parent objects that link to this repository object."""
+    return parent_elt.sql_fetch_where(gctx, "repository_id = %s" % self.repository_id)
+
   def startElement(self, stack, name, attrs):
     """Handle <repository/> element."""
     if name not in ("cms_ta", "https_ta"):
@@ -676,6 +735,10 @@ class route_origin_elt(data_elt):
     """Extra SQL delete actions for route_origin_elt -- handle address ranges."""
     gctx.cur.execute("DELETE FROM route_origin_range WHERE route_origin_id = %s", self.route_origin_id)
 
+  def ca_detail(self, gctx):
+    """Fetch all ca_detail objects that link to this route_origin object."""
+    return rpki.sql.ca_detail_obj.sql_fetch(gctx, self.ca_detail_id)
+
   def serve_post_save_hook(self, q_pdu, r_pdu):
     """Extra server actions for route_origin_elt."""
     if self.suppress_publication:
diff --git a/scripts/rpki/sql.py b/scripts/rpki/sql.py
index 0e148988..efafb889 100644
--- a/scripts/rpki/sql.py
+++ b/scripts/rpki/sql.py
@@ -211,12 +211,24 @@ class ca_obj(sql_persistant):
   last_issued_sn = 0
   last_manifest_sn = 0
 
+  def parent(self, gctx):
+    """Fetch parent object to which this CA object links."""
+    return rpki.left_right.parent_elt.sql_fetch(gctx, self.parent_id)
+
+  def ca_details(self, gctx):
+    """Fetch all ca_detail objects that link to this CA object."""
+    return ca_detail_obj.sql_fetch_where(gctx, "ca_id = %s" % self.ca_id)
+
+  def fetch_active(self, gctx):
+    """Return the active ca_detail for this CA, if any."""
+    return ca_detail_obj.sql_fetch_where1(gctx, "ca_id = %s AND state = 'active'" % self.ca_id)
+
   def construct_sia_uri(self, gctx, parent, rc):
     """Construct the sia_uri value for this CA given configured
     information and the parent's up-down protocol list_response PDU.
     """
 
-    repository = rpki.left_right.repository_elt.sql_fetch(gctx, parent.repository_id)
+    repository = parent.repository(gctx)
     sia_uri = rc.suggested_sia_head and rc.suggested_sia_head.rsync()
     if not sia_uri or not sia_uri.startswith(parent.sia_base):
       sia_uri = parent.sia_base
@@ -291,9 +303,9 @@ class ca_obj(sql_persistant):
     CA, then finally delete this CA itself.
     """
 
-    repository = rpki.left_right.repository_elt.sql_fetch(gctx, parent.repository_id)
-    for ca_detail in ca_detail_obj.sql_fetch_where(gctx, "ca_id = %s" % self.ca_id):
-      for child_cert in child_cert_obj.sql_fetch_where(gctx, "ca_detail_id = %s" % ca_detail.ca_detail_id):
+    repository = parent.repository(gctx)
+    for ca_detail in self.ca_details(gctx):
+      for child_cert in ca_detail.child_certs(gctx):
         repository.withdraw(gctx, (child_cert.cert, child_cert.uri(self)))
         child_cert.sql_delete(gctx)
       repository.withdraw(gctx, (ca_detail.latest_crl, ca_detail.crl_uri()), (ca_detail.latest_manifest, ca_detail.manifest_uri(self)))
@@ -318,10 +330,6 @@ class ca_obj(sql_persistant):
     self.sql_mark_dirty()
     return self.last_crl_sn
 
-  def fetch_active(self, gctx):
-    """Fetch the current active ca_detail for this ca."""
-    return ca_detail_obj.sql_fetch_where1(gctx, "ca_id = %s AND state = 'active'" % self.ca_id)
-
 class ca_detail_obj(sql_persistant):
   """Internal CA detail object."""
 
@@ -349,6 +357,18 @@ class ca_detail_obj(sql_persistant):
     assert (self.manifest_public_key is None and self.manifest_private_key_id is None) or \
            self.manifest_public_key.get_DER() == self.manifest_private_key_id.get_public_DER()
 
+  def ca(self, gctx):
+    """Fetch CA object to which this ca_detail links."""
+    return ca_obj.sql_fetch(gctx, self.ca_id)
+
+  def child_certs(self, gctx):
+    """Fetch all child_cert objects that link to this ca_detail."""
+    return child_cert_obj.sql_fetch_where(gctx, "ca_detail_id = %s" % self.ca_detail_id)
+
+  def route_origins(self, gctx):
+    """Fetch all route_origin objects that link to this ca_detail."""
+    return rpki.left_right.route_origin_elt.sql_fetch_where(gctx, "ca_detail_id = %s" % self.ca_detail_id)
+
   def crl_uri(self, ca):
     """Return publication URI for this ca_detail's CRL."""
     return ca.sia_uri + self.public_key.gSKI() + ".crl"
@@ -384,7 +404,7 @@ class ca_detail_obj(sql_persistant):
     new_resources = self.latest_ca_cert.get_3779resources()
 
     if sia_uri_changed or old_resources.oversized(new_resources):
-      for child_cert in child_cert_obj.sql_fetch_where(gctx, "ca_detail_id = %s" % self.ca_detail_id):
+      for child_cert in self.child_certs(gctx):
         child_resources = child_cert.cert.get_3779resources()
         if sia_uri_changed or child_resources.oversized(new_resources):
           child_cert.reissue(
@@ -463,8 +483,8 @@ class ca_detail_obj(sql_persistant):
 
     self.generate_manifest(gctx)
     
-    parent = rpki.left_right.parent_elt.sql_fetch(gctx, ca.parent_id)
-    repository = rpki.left_right.repository_elt.sql_fetch(gctx, parent.repository_id)
+    parent = ca.parent(gctx)
+    repository = parent.repository(gctx)
 
     repository.publish(gctx, (child_cert.cert, child_cert.uri(ca)), (self.latest_manifest, self.manifest_uri(ca)))
 
@@ -476,9 +496,9 @@ class ca_detail_obj(sql_persistant):
     new CRL is needed.
     """
 
-    ca = ca_obj.sql_fetch(gctx, self.ca_id)
-    parent = rpki.left_right.parent_elt.sql_fetch(gctx, ca.parent_id)
-    self_obj = rpki.left_right.self_elt.sql_fetch(gctx, parent.self_id)
+    ca = self.ca(gctx)
+    parent = ca.parent(gctx)
+    self_obj = parent.self(gctx)
     crl_interval = rpki.sundial.timedelta(seconds = self_obj.crl_interval)
     now = rpki.sundial.datetime.utcnow()
 
@@ -501,9 +521,9 @@ class ca_detail_obj(sql_persistant):
   def generate_manifest(self, gctx):
     """Generate a new manifest for this ca_detail."""
 
-    ca = ca_obj.sql_fetch(gctx, self.ca_id)
-    parent = rpki.left_right.parent_elt.sql_fetch(gctx, ca.parent_id)
-    self_obj = rpki.left_right.self_elt.sql_fetch(gctx, parent.self_id)
+    ca = self.ca(gctx)
+    parent = ca.parent(gctx)
+    self_obj = parent.self(gctx)
     certs = child_cert_obj.sql_fetch_where(gctx, "child_cert.ca_detail_id = %s AND child_cert.revoked IS NULL" % self.ca_detail_id)
 
     m = rpki.x509.SignedManifest()
@@ -529,6 +549,14 @@ class child_cert_obj(sql_persistant):
     if child_id or ca_detail_id or cert:
       self.sql_mark_dirty()
 
+  def child(self, gctx):
+    """Fetch child object to which this child_cert object links."""
+    return rpki.left_right.child_elt.sql_fetch(gctx, self.child_id)
+
+  def ca_detail(self, gctx):
+    """Fetch ca_detail object to which this child_cert object links."""
+    return ca_detail_obj.sql_fetch(gctx, self.ca_detail_id)
+
   def uri_tail(self):
     """Return the tail (filename) portion of the URI for this child_cert."""
     return self.cert.gSKI() + ".cer"
@@ -552,8 +580,8 @@ class child_cert_obj(sql_persistant):
     child_cert_obj must use the return value from this method.
     """
 
-    ca = ca_obj.sql_fetch(gctx, ca_detail.ca_id)
-    child = rpki.left_right.child_elt.sql_fetch(gctx, self.child_id)
+    ca = ca_detail.ca(gctx)
+    child = self.child(gctx)
 
     old_resources = self.cert.get_3779resources()
     old_sia       = self.cert.get_SIA()
diff --git a/scripts/rpki/up_down.py b/scripts/rpki/up_down.py
index c46b8a1d..809ac80e 100644
--- a/scripts/rpki/up_down.py
+++ b/scripts/rpki/up_down.py
@@ -172,8 +172,8 @@ class list_pdu(base_elt):
     # This will require a callback when we go event-driven
     irdb_resources = rpki.left_right.irdb_query(gctx, child.self_id, child.child_id)
 
-    for parent in rpki.left_right.parent_elt.sql_fetch_where(gctx, "parent.self_id = %s" % child.self_id):
-      for ca in rpki.sql.ca_obj.sql_fetch_where(gctx, "ca.parent_id = %s" % parent.parent_id):
+    for parent in child.parents(gctx):
+      for ca in parent.cas(gctx):
         ca_detail = ca.fetch_active(gctx)
         if not ca_detail:
           continue
@@ -253,8 +253,7 @@ class issue_pdu(base_elt):
     # Check the request
     if not self.class_name.isdigit():
       raise rpki.exceptions.BadClassNameSyntax, "Bad class name %s" % self.class_name
-    ca_id = long(self.class_name)
-    ca = rpki.sql.ca_obj.sql_fetch(gctx, ca_id)
+    ca = rpki.sql.ca_obj.sql_fetch(gctx, long(self.class_name))
     ca_detail = ca.fetch_active(gctx)
     if ca is None or ca_detail is None:
       raise rpki.exceptions.NotInDatabase
@@ -296,7 +295,7 @@ class issue_pdu(base_elt):
     c.cert_url = multi_uri(child_cert.uri(ca))
     c.cert = child_cert.cert
     rc = class_elt()
-    rc.class_name = str(ca_id)
+    rc.class_name = self.class_name
     rc.cert_url = multi_uri(ca_detail.ca_cert_uri)
     rc.from_resource_bag(resources)
     rc.certs.append(c)
@@ -363,8 +362,8 @@ class revoke_pdu(revoke_syntax):
   @classmethod
   def query(cls, gctx, ca_detail):
     """Send a "revoke" request to parent associated with ca_detail."""
-    ca = rpki.sql.ca_obj.sql_fetch(gctx, ca_detail.ca_id)
-    parent = rpki.left_right.parent_elt.sql_fetch(gctx, ca.parent_id)
+    ca = ca_detail.ca(gctx)
+    parent = ca.parent(gctx)
     self = cls()
     self.class_name = ca.parent_resource_class
     self.ski = ca_detail.latest_ca_cert.gSKI()