Checkpoint

svn path=/scripts/rpki/left_right.py; revision=967

3 files changed, 110 insertions, 298 deletions

diff --git a/scripts/rpki/left_right.py b/scripts/rpki/left_right.py
index 8f602137..d8ed5fa3 100644
--- a/scripts/rpki/left_right.py
+++ b/scripts/rpki/left_right.py
@@ -51,19 +51,21 @@ class base_elt(object):
   def __str__(self):
     lxml.etree.tostring(self.toXML(), pretty_print=True, encoding="us-ascii")
 
-class data_elt(base_elt):
+class data_elt(base_elt, rpki.sql.sql_persistant):
   """Virtual type for a left-right protocol message elements representing
   top-level persistant data elements.
   """
 
   pass
 
-class extension_preference_elt(base_elt, rpki.sql.sql_persistant):
+class extension_preference_elt(base_elt):
   """Container for extension preferences."""
 
   element_name = "extension_preference"
   attributes = ("name",)
 
+  raise NotImplementedError, "This needs to be rewritten to use the self_elt.*_hook() methods"
+
   sql_select_cmd = """SELECT pref_name, pref_value FROM self_pref WHERE self_id = %(self_id)s"""
   sql_insert_cmd = """INSERT self_pref (self_id, pref_name, pref_value) VALUES (%(self_id)s, %(name)s, %(value)s)"""
   sql_update_cmd = """UPDATE self_pref SET pref_value = %(value)s WHERE self_id = %(self_id)s AND pref_name = %(name)s"""
@@ -96,18 +98,14 @@ class extension_preference_elt(base_elt, rpki.sql.sql_persistant):
     elt.text = self.value
     return elt
 
-class bsc_elt(data_elt, rpki.sql.sql_persistant):
+class bsc_elt(data_elt):
   """<bsc/> (Business Signing Context) element."""
   
   element_name = "bsc"
   attributes = ("action", "type", "self_id", "bsc_id", "key_type", "hash_alg", "key_length")
   booleans = ("generate_keypair",)
 
-  sql_id_name = "bsc_id"
-  sql_select_cmd = """SELECT bsc_id, pub_key, priv_key_id FROM bsc WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT bsc (self_id, pub_key, priv_key_id) VALUES (%(self_id)s, %(pub_key)s, %(priv_key_id)s)"""
-  sql_update_cmd = """UPDATE bsc SET self_id = %(self_id)s, pub_key = %(pub_key)s, priv_key_id = %(priv_key_id)s WHERE bsc_id = %(bsc_id)s"""
-  sql_delete_cmd = """DELETE FROM bsc WHERE bsc_id = %(bsc_id)s"""
+  sql_template = rpki.sql.template("bsc", "bsc_id", "self_id", "pub_key", "priv_key_id")
 
   pkcs10_cert_request = None
   public_key = None
@@ -115,20 +113,6 @@ class bsc_elt(data_elt, rpki.sql.sql_persistant):
   def __init__(self):
     self.signing_cert = []
 
-  def sql_decode(self, sql_parent, bsc_id, pub_key, priv_key_id):
-    assert isinstance(sql_parent, self_elt)
-    self.self_obj = sql_parent
-    self.bsc_id = bsc_id
-    self.self_id = self_id
-    self.pub_key = pub_key
-    self.priv_key_id = priv_key_id
-
-  def sql_encode(self):
-    return { "self_id"     : self.self_obj.self_id,
-             "bsc_id"      : self.bsc_id,
-             "pub_key"     : self.pub_key,
-             "priv_key_id" : self.priv_key_id }
-
   def sql_fetch_hook(self, db, cur):
     cur.execute("""SELECT cert FROM bsc_cert WHERE bsc_id = %s""", self.bsc_id)
     self.signing_cert = [rpki.x509.X509(DER=x) for (x,) in cur.fetchall()]
@@ -167,36 +151,27 @@ class bsc_elt(data_elt, rpki.sql.sql_persistant):
     self.make_b64elt(elt, "public_key")
     return elt
 
-class parent_elt(data_elt, rpki.sql.sql_persistant):
+class parent_elt(data_elt):
   """<parent/> element."""
 
   element_name = "parent"
   attributes = ("action", "type", "self_id", "parent_id", "bsc_link", "repository_link", "peer_contact", "sia_base")
   booleans = ("rekey", "reissue", "revoke")
 
-  sql_id_name = "parent_id"
-  sql_select_cmd = """SELECT parent_id, ta, uri, sia_base, bsc_id, repos_id FROM parent WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT parent (ta, url, sia_base, self_id, bsc_id, repos_id)
-                      VALUES (%(ta)s, %(url)s, %(sia_base)s, %(self_id)s, %(bsc_id)s, %(repos_id)s)"""
-  sql_update_cmd = """UPDATE repos SET ta = %(ta)s, uri = %(uri)s, sia_base = %(sia_base)s, self_id = %(self_id)s, bsc_id = %(bsc_id)s, repos_id = %(repos_id)s
-                      WHERE parent_id = %(parent_id)s"""
-  sql_delete_cmd = """DELETE FROM parent WHERE parent_id = %(parent_id)s"""
-
-  sql_children = (("cas", rpki.sql.ca_obj),)
+  sql_template = rpki.sql.template("parent", "parent_id", "self_id", "bsc_id", "repos_id", "ta", "uri", "sia_base")
 
-  def sql_decode(self, sql_parent, parent_id, ta, uri, sia_base, bsc_id, repos_id):
-    assert isinstance(sql_parent, self_elt)
-    self.self_obj = sql_parent
-    self.bsc_obj = bsc_elt.sql_cache_find(bsc_id)
-    self.repository_obj = repository_elt.sql_cache_find(repos_id)
-    self.parent_id = parent_id
-    self.peer_contact = uri
-    self.peer_ta = rpki.x509.X509(DER=ta)
+  def sql_decode(self, vals):
+    self.self_id         = vals["self_id"]
+    self.bsc_link        = vals["bsc_id"]
+    self.repository_link = vals["repos_id"]
+    self.parent_id       = vals["parent_id"]
+    self.peer_contact    = vals["uri"]
+    self.peer_ta         = rpki.x509.X509(DER=vals["ta"])
 
   def sql_encode(self):
-    return { "self_id"   : self.self_obj.self_id,
-             "bsc_id"    : self.bsc_obj.bsc_id,
-             "repos_id"  : self.repository_obj.repository_id,
+    return { "self_id"   : self.self_id,
+             "bsc_id"    : self.bsc_link,
+             "repos_id"  : self.repository_link,
              "parent_id" : self.parent_id,
              "uri"       : self.peer_contact,
              "ta"        : self.peer_ta.get_DER(),
@@ -225,29 +200,24 @@ class parent_elt(data_elt, rpki.sql.sql_persistant):
       self.make_b64elt(elt, "peer_ta", self.peer_ta.get_DER())
     return elt
 
-class child_elt(data_elt, rpki.sql.sql_persistant):
+class child_elt(data_elt):
   """<child/> element."""
 
   element_name = "child"
   attributes = ("action", "type", "self_id", "child_id", "bsc_link", "child_db_id")
   booleans = ("reissue", )
 
-  sql_id_name = "child_id"
-  sql_select_cmd = """SELECT child_id, ta, bsc_id FROM child WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT child (ta, self_id, bsc_id) VALUES (%(ta)s, %(self_id)s, %(bsc_id)s)"""
-  sql_update_cmd = """UPDATE repos SET ta = %(ta)s, self_id = %(self_id)s, bsc_id = %(bsc_id)s WHERE child_id = %(child_id)s"""
-  sql_delete_cmd = """DELETE FROM child WHERE child_id = %(child_id)s"""
+  sql_template = rpki.sql.template("child", "child_id", "self_id", "bsc_id", "ta")
 
-  def sql_decode(self, sql_parent, child_id, ta, bsc_id):
-    assert isinstance(sql_parent, self_elt)
-    self.self_obj = sql_parent
-    self.bsc_obj = bsc_elt.sql_cache_find(bsc_id)
-    self.child_id = child_id
-    self.peer_ta = rpki.x509.X509(DER=ta)
+  def sql_decode(self, vals):
+    self.self_id = vals["self_id"]
+    self.bsc_link = vals["bsc_id"]
+    self.child_id = vals["child_id"]
+    self.peer_ta = rpki.x509.X509(DER=vals["ta"])
 
   def sql_encode(self):
-    return { "self_id"  : self.self_obj.self_id,
-             "bsc_id"   : self.bsc_obj.bsc_id,
+    return { "self_id"  : self.self_id,
+             "bsc_id"   : self.bsc_id,
              "child_id" : self.child_id,
              "ta"       : self.peer_ta.get_DER() }
 
@@ -297,29 +267,24 @@ class child_elt(data_elt, rpki.sql.sql_persistant):
       self.make_b64elt(elt, "peer_ta", self.peer_ta.get_DER())
     return elt
 
-class repository_elt(data_elt, rpki.sql.sql_persistant):
+class repository_elt(data_elt):
   """<repository/> element."""
 
   element_name = "repository"
   attributes = ("action", "type", "self_id", "repository_id", "bsc_link", "peer_contact")
 
-  sql_id_name = "repos_id"
-  sql_select_cmd = """SELECT bsc_id, repos_id, uri, ta FROM repos WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT repos (uri, ta, bsc_id, self_id) VALUES (%(uri)s, %(ta)s, %(bsc_id)s, %(self_id)s)"""
-  sql_update_cmd = """UPDATE repos SET uri = %(uri)s, ta = %(ta)s, bsc_id = %(bsc_id)s, self_id = %(self_id)s WHERE repos_id = %(repos_id)s"""
-  sql_delete_cmd = """DELETE FROM repos WHERE repos_id = %(repos_id)s"""
+  sql_template = rpki.sql.template("repository", "repos_id", "self_id", "bsc_id", "ta", "uri")
 
-  def sql_decode(self, sql_parent, bsc_id, repos_id, uri, ta):
-    assert isinstance(sql_parent, self_elt)
-    self.self_obj = sql_parent
-    self.bsc_obj = bsc_elt.sql_cache_find(bsc_id)
-    self.repository_id = repos_id
-    self.peer_contact = uri
-    self.peer_ta = rpki.x509.X509(DER=ta)
+  def sql_decode(self, vals):
+    self.self_id = vals["self_id"]
+    self.bsc_link = vals["bsc_id"]
+    self.repository_id = vals["repos_id"]
+    self.peer_contact = vals["uri"]
+    self.peer_ta = rpki.x509.X509(DER=vals["ta"])
 
   def sql_encode(self):
-    return { "self_id"  : self.self_obj.self_id,
-             "bsc_id"   : self.bsc_obj.bsc_id,
+    return { "self_id"  : self.self_id,
+             "bsc_id"   : self.bsc_id,
              "repos_id" : self.repository_id,
              "uri"      : self.peer_contact,
              "ta"       : self.peer_ta.get_DER() }
@@ -347,27 +312,22 @@ class repository_elt(data_elt, rpki.sql.sql_persistant):
       self.make_b64elt(elt, "peer_ta", self.peer_ta.get_DER())
     return elt
 
-class route_origin_elt(data_elt, rpki.sql.sql_persistant):
+class route_origin_elt(data_elt):
   """<route_origin/> element."""
 
   element_name = "route_origin"
   attributes = ("action", "type", "self_id", "route_origin_id", "asn", "ipv4", "ipv6")
   booleans = ("suppress_publication",)
 
-  sql_id_name = "route_origin_id"
-  sql_select_cmd = """SELECT route_origin_id, as_number FROM route_origin WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT route_origin (as_number, self_id) VALUES (%(as_number)s, %(self_id)s)"""
-  sql_update_cmd = """UPDATE route_origin SET as_number = %(as_number)s, self_id = %(self_id)s WHERE repos_id = %(route_origin_id)s"""
-  sql_delete_cmd = """DELETE FROM route_origin WHERE repos_id = %(route_origin_id)s"""
+  sql_template = rpki.sql.template("route_origin", "route_origin_id", "self_id", "as_number")
 
-  def sql_decode(self, sql_parent, route_origin_id, as_number):
-    assert isinstance(sql_parent, self_elt)
-    self.self_obj = sql_parent
-    self.asn = as_number
-    self.route_origin = route_origin_id
+  def sql_decode(self, vals):
+    self.self_id = vals["self_id"]
+    self.asn = vals["as_number"]
+    self.route_origin = vals["route_origin_id"]
 
   def sql_encode(self):
-    return { "self_id"         : self.self_obj.self_id,
+    return { "self_id"         : self.self_id,
              "route_origin_id" : self.route_origin_id,
              "as_number"       : self.asn }
 
@@ -409,40 +369,20 @@ class route_origin_elt(data_elt, rpki.sql.sql_persistant):
     """Generate <route_origin/> element."""
     return self.make_elt()
 
-class self_elt(data_elt, rpki.sql.sql_persistant):
+class self_elt(data_elt):
   """<self/> element."""
 
   element_name = "self"
   attributes = ("action", "type", "self_id")
   booleans = ("rekey", "reissue", "revoke", "run_now", "publish_world_now")
 
-  sql_id_name = "self_id"
-  sql_select_cmd = """SELECT self_id, use_hsm FROM self WHERE self_id = %(self_id)s"""
-  sql_insert_cmd = """INSERT self (use_hsm) VALUES (%(use_hsm)s)"""
-  sql_update_cmd = """UPDATE self SET use_hsm = %(use_hsm)s WHERE self_id = %(self_id)s"""
-  sql_delete_cmd = """DELETE FROM self WHERE self_id = %(self_id)s"""
-  sql_children = (("prefs",         extension_preference_elt),
-                  ("bscs",          bsc_elt),
-                  ("repos",         repository_elt),
-                  ("parents",       parent_elt),
-                  ("children",      child_elt),
-                  ("route_origins", route_origin_elt))
+  sql_template = rpki.sql.template("self", "self_id", "use_hsm")
 
   self_id = None
   use_hsm = False
 
   def __init__(self):
-    for k,v in self.sql_children:
-      setattr(self, k, [])
-
-  def sql_decode(self, sql_parent, self_id, use_hsm):
-    assert sql_parent is None
-    self.self_id = self_id
-    self.use_hsm = use_hsm
-
-  def sql_encode(self):
-    return { "self_id" : self.self_id,
-             "use_hsm" : self.use_hsm }
+    self.prefs = []
 
   def startElement(self, stack, name, attrs):
     """Handle <self/> element."""
diff --git a/scripts/rpki/sql.py b/scripts/rpki/sql.py
index 04f51661..23c00db8 100644
--- a/scripts/rpki/sql.py
+++ b/scripts/rpki/sql.py
@@ -11,6 +11,18 @@ def connect(cfg, section="sql"):
                          db     = cfg.get(section, "sql-database"),
                          passwd = cfg.get(section, "sql-password"))
 
+class template(object):
+  """SQL template generator."""
+  def __init__(self, table_name, *columns):
+    index_column = columns[0]
+    data_columns = columns[1:]
+    self.index   = index_column
+    self.columns = columns
+    self.select  = "SELECT %s FROM %s WHERE %s = %%s" % (", ".join(columns), table_name, index_column)
+    self.insert  = "INSERT %s (%s) VALUES (%s)" % (table_name, ", ".join(data_columns), ", ".join("%(" + s + ")s" for s in data_columns))
+    self.update  = "UPDATE %s SET %s WHERE %s = %%(%s)s" % (table_name, ", ".join(s + " = %(" + s + ")s" for s in data_columns), index_column, index_column)
+    self.delete  = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
+
 ## @var sql_cache
 # Cache of objects pulled from SQL.
 
@@ -22,24 +34,17 @@ def cache_clear():
   sql_cache = {}
 
 
+def get_column(db, cur, query):
+  """Pull a single column from SQL, return it as a list."""
+
+  cur.execute(query)
+  return [x[0] for x in cur.fetchall()]
+
+
 class sql_persistant(object):
   """Mixin for persistant class that needs to be stored in SQL.
   """
 
-  ## @var sql_children
-  # Tuple of tuples associating this class's children in the tree of
-  # SQL tables with the attribute names by which this class refers to
-  # them.  Conceptually, this is an ordered dictionary; not being able
-  # to use a real Python dictionary here is a minor inconvenience.
-  # Making this an ordered data structure allows us to defer the
-  # objects with complex cross-linking until after the simpler objects
-  # to which they link have already been loaded.
-  #
-  # "Key" is the name of the attribute in this class at which a list
-  # of the resulting child objects are stored; "value" is is the class
-  # object of a child.
-  sql_children = ()
-
   ## @var sql_in_db
   # Whether this object is already in SQL or not.
   sql_in_db = False
@@ -49,120 +54,63 @@ class sql_persistant(object):
   # to SQL.
   sql_dirty = False
 
-  ## @var sql_id_name
-  # Name of the attribute in which to store the auto-increment ID
-  # column for this table; None if it doesn't have an auto-increment
-  # ID or we don't want to store it.
-  sql_id_name = None
-
-  ## @var sql_select_cmd
-  # Command to SELECT this object from SQL
-  sql_select_cmd = None
-
-  ## @var sql_insert_cmd
-  # Command to INSERT this object into SQL
-  sql_insert_cmd = None
-
-  ## @var sql_update_cmd
-  # Command to UPDATE this object into SQL
-  sql_update_cmd = None
-
-  ## @var sql_delete_cmd
-  # Command to DELETE this object from SQL
-  sql_delete_cmd = None
-
-  def sql_cache_add(self):
-    """Add self to the object cache."""
-
-    assert self.sql_id_name is not None
-    sql_cache[(self.__class__, self.sql_id_name)] = self
-
-  @classmethod
-  def sql_cache_find(*keys):
-    """Find an object in the object cache."""
-
-    return sql_cache.get(keys)
-
-  def cache_delete(*keys):
-    """Delete self from the object cache."""
-    
-    assert self.sql_id_name is not None
-    del sql_cache[(self.__class__, self.sql_id_name)]
-
   @classmethod
-  def sql_fetch(cls, db, cur=None, select_dict=None, sql_parent=None):
-    """Fetch rows from SQL based on a canned query and a set of
-    keyword arguments, and instantiate them as objects, returning a
-    list of the instantiated objects.  If the object definition
-    indicates an index field (sql_id_name), this method instead
-    returns as dictionary using the index field as the key.
-
-    This is a class method because in general we don't even know how
-    many matches the SQL lookup will return until after we've
-    performed it.
-    """
-
-    result = []
-    if cur is None:
-      cur = db.cursor()
-    cur.execute(self.sql_select_cmd, select_dict)
-    for row in cur.fetchall():
-      self = cls()
-      self.in_sql = True
-      self.sql_decode(sql_parent, *row)
-      if self.sql_id_name is not None:
-        self.sql_cache_add()
-      self_dict = self.sql_encode()
-      self.sql_fetch_hook(db, cur)
-      result.append(self)
-      for k,v in self.sql_children:
-        setattr(self, k, v.sql_fetch(db, cur, self_dict, self))
-    return result
+  def sql_fetch(cls, db, cur, id):
+    key = (cls, id)
+    if key in sql_cache:
+      return sql_cache[key]
+    cur.execute(cls.sql_template.select, id)
+    result = cur.fetchone()
+    if result is None:
+      return None
+    data = dict(zip(cls.sql_template.columns, result))
+    self = cls()
+    self.sql_decode(data)
+    sql_cache[key] = self
+    self.sql_dirty = False
+    self.sql_in_db = True
+    self.sql_fetch_hook(db, cur)
+    return self
 
-  def sql_store(self, db, cur=None):
-    """Save an object and its descendents to SQL.
-    """
-    if cur is None:
-      cur = db.cursor()
+  def sql_store(self, db, cur):
     if not self.sql_in_db:
-      cur.execute(self.sql_insert_cmd, self.sql_encode())
-      if self.sql_id_name is not None:
-        setattr(self, self.sql_id_name, cur.lastrowid)
-        self.sql_cache_add()
+      cur.execute(self.sql_template.insert, self.sql_encode())
+      setattr(self, self.sql_template.index, cur.lastrowid)
+      sql_cache[(self.__class__, cur.lastrowid)] = self
       self.sql_insert_hook(db, cur)
     elif self.sql_dirty:
-      cur.execute(self.sql_update_cmd, self.sql_encode())
+      cur.execute(self.sql_template.update, self.sql_encode())
       self.sql_update_hook(db, cur)
+    assert sql_cache[(self.__class__, getattr(self, self.sql_template.index))] == self
     self.sql_dirty = False
     self.sql_in_db = True
-    for k,v in self.sql_children:
-      for kid in getattr(self, k):
-        kid.sql_store(db, cur)
 
-  def sql_delete(self, db, cur=None):
-    """Delete an object and its descendants from SQL.
-    """
-    if cur is None:
-      cur = db.cursor()
+  def sql_delete(self, db, cur):
     if self.sql_in_db:
-      cur.execute(self.sql_delete_cmd, self.sql_encode())
+      cur.execute(self.sql_template.delete, self.sql_encode())
       self.sql_delete_hook(db, cur)
+      key = (self.__class__, getattr(self, self.sql_template.index))
+      if sql_cache.get(key) == self:
+        del sql_cache[key]
       self.sql_in_db = False
-    for k,v in self.sql_children:
-      for kid in getattr(self, k):
-        kid.sql_delete(db, cur)
 
   def sql_encode(self):
-    """Convert object attributes into a dict for use with canned
-    SQL queries.
+    """Convert object attributes into a dict for use with canned SQL
+    queries.  This is a default version that assumes a one-to-one
+    mapping between column names in SQL and attribute names in Python,
+    with no datatype conversion.  If you need something fancier,
+    override this.
     """
-    raise NotImplementedError
-    #return dict((a, getattr(self, a)) for a in self.sql_attributes)
+    return dict((a, getattr(self, a)) for a in self.sql_template.columns)
 
-  def sql_decode(self):
+  def sql_decode(self, vals):
     """Initialize an object with values returned by self.sql_fetch().
+    This is a default version that assumes a one-to-one mapping
+    between column names in SQL and attribute names in Python, with no
+    datatype conversion.  If you need something fancier, override this.
     """
-    raise NotImplementedError
+    for a in self.sql_template.columns:
+      setattr(self, a, vals[a])
 
   def sql_fetch_hook(self, db, cur):
     """Customization hook."""
@@ -187,92 +135,16 @@ class sql_persistant(object):
 class ca_detail_obj(sql_persistant):
   """Internal CA detail object."""
 
-  sql_select_cmd = """SELECT ca_detail_id, priv_key_handle, pub_key, latest_ca_cert_over_pubkey, manifest_ee_priv_key_handle, manifest_ee_pub_key,
-                             latest_manifest_ee_cert, latest_manifest, latest_crl, ca_id
-                      FROM ca_detail
-                      WHERE ca_id = %(ca_id)s"""
-
-  sql_insert_cmd = """INSERT ca_detail (priv_key_handle, pub_key, latest_ca_cert_over_pubkey, manifest_ee_priv_key_handle, manifest_ee_pub_key,
-                                        latest_manifest_ee_cert, latest_manifest, latest_crl, ca_id)
-                      VALUES (%(priv_key_handle)s, %(pub_key)s, %(latest_ca_cert_over_pubkey)s, %(manifest_ee_priv_key_handle)s,
-                              %(manifest_ee_pub_key)s, %(latest_manifest_ee_cert)s, %(latest_manifest)s, %(latest_crl)s, %(ca_id)s)"""
-
-  sql_update_cmd = """UPDATE ca
-                      SET priv_key_handle = %(priv_key_handle)s, pub_key = %(pub_key)s, latest_ca_cert_over_pubkey = %(latest_ca_cert_over_pubkey)s,
-                          manifest_ee_priv_key_handle = %(manifest_ee_priv_key_handle)s, manifest_ee_pub_key = %(manifest_ee_pub_key)s,
-                          latest_manifest_ee_cert = %(latest_manifest_ee_cert)s, latest_manifest = %(latest_manifest)s, latest_crl = %(latest_crl)s, ca_id = %(ca_id)s
-                      WHERE ca_detail_id = %(ca_detail_id)s"""
-
-  sql_delete_cmd = """DELETE FROM ca_detail WHERE ca_detail_id = %(ca_detail_id)s"""
-
+  sql_template = template("ca", "ca_detail_id", "priv_key_handle", "pub_key", "latest_ca_cert_over_pubkey", "manifest_ee_priv_key_handle", "manifest_ee_pub_key",
+                          "latest_manifest_ee_cert", "latest_manifest", "latest_crl", "ca_id")
 
   def __init__(self):
     self.certs = []
 
-  def sql_decode(self, sql_parent, ca_detail_id, priv_key_handle, pub_key, latest_ca_cert_over_pubkey,
-                 manifest_ee_priv_key_handle, manifest_ee_pub_key, latest_manifest_ee_cert, latest_manifest, latest_crl, ca_id):
-    assert isinstance(sql_parent, ca_obj)
-    self.ca_obj = sql_parent
-    self.ca_detail_id = ca_detail_id
-    self.priv_key_handle = priv_key_handle
-    self.pub_key = pub_key
-    self.latest_ca_cert_over_pubkey = latest_ca_cert_over_pubkey
-    self.manifest_ee_priv_key_handle = manifest_ee_priv_key_handle
-    self.manifest_ee_pub_key = manifest_ee_pub_key
-    self.latest_manifest_ee_cert = latest_manifest_ee_cert
-    self.latest_manifest = latest_manifest
-    self.latest_crl = latest_crl
-    self.ca_id = ca_id
-
-  def sql_encode(self):
-    return { "ca_detail_id"                     : self.ca_detail_id,
-             "priv_key_handle"                  : self.priv_key_handle,
-             "pub_key"                          : self.pub_key,
-             "latest_ca_cert_over_pubkey"       : self.latest_ca_cert_over_pubkey,
-             "manifest_ee_priv_key_handle"      : self.manifest_ee_priv_key_handle,
-             "manifest_ee_pub_key"              : self.manifest_ee_pub_key,
-             "latest_manifest_ee_cert"          : self.latest_manifest_ee_cert,
-             "latest_manifest"                  : self.latest_manifest,
-             "latest_crl"                       : self.latest_crl,
-             "ca_id"                            : self.ca_id }
-
 class ca_obj(sql_persistant):
   """Internal CA object."""
 
-  sql_select_cmd = """SELECT ca_id, last_crl_sn, next_crl_update, last_issued_sn, last_manifest_sn, next_manifest_update, sia_uri, parent_id
-                      FROM ca
-                      WHERE parent_id = %(parent_id)s"""
-  sql_insert_cmd = """INSERT ca (last_crl_sn, next_crl_update, last_issued_sn, last_manifest_sn, next_manifest_update, sia_uri, parent_id)
-                      VALUES (%(last_crl_sn)s, %(next_crl_update)s, %(last_issued_sn)s, %(last_manifest_sn)s, %(next_manifest_update)s, %(sia_uri)s, %(parent_id)s)"""
-  sql_update_cmd = """UPDATE ca
-                      SET last_crl_sn = %(last_crl_sn)s, next_crl_update = %(next_crl_update)s, last_issued_sn = %(last_issued_sn)s,
-                          last_manifest_sn = %(last_manifest_sn)s, next_manifest_update = %(next_manifest_update)s, sia_uri = %(sia_uri)s, parent_id = %(parent_id)s
-                      WHERE ca_id = %(ca_id)s"""
-  sql_delete_cmd = """DELETE FROM ca WHERE ca_id = %(ca_id)s"""
-
-  sql_children = (("ca_details", ca_detail_obj),)
+  sql_template = template("ca", "ca_id", "last_crl_sn", "next_crl_update", "last_issued_sn", "last_manifest_sn", "next_manifest_update", "sia_uri", "parent_id")
 
   def __init__(self):
     self.children = []
-
-  def sql_decode(self, sql_parent, ca_id, last_crl_sn, next_crl_update, last_issued_sn, last_manifest_sn, next_manifest_update, sia_uri, parent_id):
-    assert isinstance(sql_parent, rpki.left_right.parent_elt)
-    self.parent_obj = sql_parent
-    self.ca_id = ca_id
-    self.last_crl_sn = last_crl_sn
-    self.next_crl_update = next_crl_update
-    self.last_issued_sn = last_issued_sn
-    self.last_manifest_sn = last_manifest_sn
-    self.next_manifest_update = next_manifest_update
-    self.sia_uri = sia_uri
-    self.parent_id = parent_id
-
-  def sql_encode(self):
-    return { "ca_id"                    : self.ca_id,
-             "last_crl_sn"              : self.last_crl_sn,
-             "next_crl_update"          : self.next_crl_update,
-             "last_issued_sn"           : self.last_issued_sn,
-             "last_manifest_sn"         : self.last_manifest_sn,
-             "next_manifest_update"     : self.next_manifest_update,
-             "sia_uri"                  : self.sia_uri,
-             "parent_id"                : self.parent_id }
diff --git a/scripts/rpkid.py b/scripts/rpkid.py
index be3c8bec..cc794e11 100755
--- a/scripts/rpkid.py
+++ b/scripts/rpkid.py
@@ -32,7 +32,7 @@ def left_right_handler(query, path):
   def destroy_handler(q_pdu):
     r_pdu = make_reply(q_pdu)
     r_pdu.self_id = q_pdu.self_id
-    setattr(r_pdu, q_pdu.sql_id_name, getattr(q_pdu, q_pdu.sql_id_name))
+    setattr(r_pdu, q_pdu.sql_template.index, getattr(q_pdu, q_pdu.sql_template.index))
     q_pdu.sql_delete()    
     r_msg.append(r_pdu)
 
@@ -40,7 +40,7 @@ def left_right_handler(query, path):
     r_pdu = make_reply(q_pdu)
     q_pdu.sql_store(db, cur)
     r_pdu.self_id = q_pdu.self_id
-    setattr(r_pdu, q_pdu.sql_id_name, getattr(q_pdu, q_pdu.sql_id_name))
+    setattr(r_pdu, q_pdu.sql_template.index, getattr(q_pdu, q_pdu.sql_template.index))
     r_msg.append(r_pdu)
 
   def get_handler():