Checkpoint

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

1 files changed, 61 insertions, 189 deletions

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 }