From aa9feac66922993763c85c918dc02a820df8677c Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Sun, 16 Sep 2007 07:14:43 +0000 Subject: Checkpoint svn path=/scripts/rpki/left_right.py; revision=967 --- scripts/rpki/sql.py | 250 +++++++++++++--------------------------------------- 1 file changed, 61 insertions(+), 189 deletions(-) (limited to 'scripts/rpki/sql.py') 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 } -- cgit v1.2.3