RPKI Engine 1.0

sql.py (3551)

Go to the documentation of this file.
00001 """
00002 SQL interface code.
00003 
00004 $Id: sql.py 3551 2010-11-14 23:28:45Z sra $
00005 
00006 Copyright (C) 2009  Internet Systems Consortium ("ISC")
00007 
00008 Permission to use, copy, modify, and distribute this software for any
00009 purpose with or without fee is hereby granted, provided that the above
00010 copyright notice and this permission notice appear in all copies.
00011 
00012 THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH
00013 REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
00014 AND FITNESS.  IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT,
00015 INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
00016 LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
00017 OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
00018 PERFORMANCE OF THIS SOFTWARE.
00019 
00020 Portions copyright (C) 2007--2008  American Registry for Internet Numbers ("ARIN")
00021 
00022 Permission to use, copy, modify, and distribute this software for any
00023 purpose with or without fee is hereby granted, provided that the above
00024 copyright notice and this permission notice appear in all copies.
00025 
00026 THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH
00027 REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
00028 AND FITNESS.  IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT,
00029 INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
00030 LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
00031 OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
00032 PERFORMANCE OF THIS SOFTWARE.
00033 """
00034 
00035 from __future__ import with_statement
00036 
00037 import warnings
00038 
00039 # Silence warning while loading MySQLdb in Python 2.6, sigh
00040 if hasattr(warnings, "catch_warnings"):
00041   with warnings.catch_warnings():
00042     warnings.simplefilter("ignore", DeprecationWarning)
00043     import MySQLdb
00044 else:
00045   import MySQLdb
00046 
00047 import _mysql_exceptions
00048 import rpki.x509, rpki.resource_set, rpki.sundial, rpki.log
00049 
00050 class session(object):
00051   """
00052   SQL session layer.
00053   """
00054 
00055   _exceptions_enabled = False
00056 
00057   def __init__(self, cfg):
00058 
00059     if not self._exceptions_enabled:
00060       warnings.simplefilter("error", _mysql_exceptions.Warning)
00061       self.__class__._exceptions_enabled = True
00062 
00063     self.username = cfg.get("sql-username")
00064     self.database = cfg.get("sql-database")
00065     self.password = cfg.get("sql-password")
00066 
00067     self.cache = {}
00068     self.dirty = set()
00069 
00070     self.connect()
00071 
00072   def connect(self):
00073     self.db = MySQLdb.connect(user = self.username, db = self.database, passwd = self.password)
00074     self.cur = self.db.cursor()
00075     self.db.autocommit(True)
00076 
00077   def close(self):
00078     if self.cur:
00079       self.cur.close()
00080     self.cur = None
00081     if self.db:
00082       self.db.close()
00083     self.db = None
00084 
00085   def ping(self):
00086     return self.db.ping(True)
00087 
00088   def _wrap_execute(self, func, query, args):
00089     try:
00090       return func(query, args)
00091     except _mysql_exceptions.MySQLError:
00092       if self.dirty:
00093         rpki.log.warn("MySQL exception with dirty objects in SQL cache!")
00094       raise
00095 
00096   def execute(self, query, args = None):
00097     return self._wrap_execute(self.cur.execute, query, args)
00098 
00099   def executemany(self, query, args):
00100     return self._wrap_execute(self.cur.executemany, query, args)
00101 
00102   def fetchall(self):
00103     return self.cur.fetchall()
00104 
00105   def lastrowid(self):
00106     return self.cur.lastrowid
00107 
00108   def cache_clear(self):
00109     """
00110     Clear the object cache.
00111     """
00112     self.cache.clear()
00113 
00114   def assert_pristine(self):
00115     """
00116     Assert that there are no dirty objects in the cache.
00117     """
00118     assert not self.dirty, "Dirty objects in SQL cache: %s" % self.dirty
00119 
00120   def sweep(self):
00121     """
00122     Write any dirty objects out to SQL.
00123     """
00124     for s in self.dirty.copy():
00125       rpki.log.debug("Sweeping %r" % s)
00126       if s.sql_deleted:
00127         s.sql_delete()
00128       else:
00129         s.sql_store()
00130     self.assert_pristine()
00131 
00132 class template(object):
00133   """
00134   SQL template generator.
00135   """
00136 
00137   def __init__(self, table_name, index_column, *data_columns):
00138     """
00139     Build a SQL template.
00140     """
00141     type_map     = dict((x[0], x[1]) for x in data_columns if isinstance(x, tuple))
00142     data_columns = tuple(isinstance(x, tuple) and x[0] or x for x in data_columns)
00143     columns      = (index_column,) + data_columns
00144     self.table   = table_name
00145     self.index   = index_column
00146     self.columns = columns
00147     self.map     = type_map
00148     self.select  = "SELECT %s FROM %s" % (", ".join("%s.%s" % (table_name, c) for c in columns), table_name)
00149     self.insert  = "INSERT %s (%s) VALUES (%s)" % (table_name,
00150                                                    ", ".join(data_columns),
00151                                                    ", ".join("%(" + s + ")s" for s in data_columns))
00152     self.update  = "UPDATE %s SET %s WHERE %s = %%(%s)s" % (table_name,
00153                                                             ", ".join(s + " = %(" + s + ")s" for s in data_columns),
00154                                                             index_column,
00155                                                             index_column)
00156     self.delete  = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
00157 
00158 class sql_persistent(object):
00159   """
00160   Mixin for persistent class that needs to be stored in SQL.
00161   """
00162 
00163   ## @var sql_in_db
00164   # Whether this object is already in SQL or not.
00165 
00166   sql_in_db = False
00167 
00168   ## @var sql_deleted
00169   # Whether our cached copy of this object has been deleted.
00170 
00171   sql_deleted = False
00172 
00173   ## @var sql_debug
00174   # Enable logging of SQL actions
00175 
00176   sql_debug = False
00177 
00178   @classmethod
00179   def sql_fetch(cls, gctx, id):
00180     """
00181     Fetch one object from SQL, based on its primary key.
00182 
00183     Since in this one case we know that the primary index is also the
00184     cache key, we check for a cache hit directly in the hope of
00185     bypassing the SQL lookup entirely.
00186 
00187     This method is usually called via a one-line class-specific
00188     wrapper.  As a convenience, we also accept an id of None, and just
00189     return None in this case.
00190     """
00191 
00192     if id is None:
00193       return None
00194     assert isinstance(id, (int, long)), "id should be an integer, was %r" % type(id)
00195     key = (cls, id)
00196     if key in gctx.sql.cache:
00197       return gctx.sql.cache[key]
00198     else:
00199       return cls.sql_fetch_where1(gctx, "%s = %%s" % cls.sql_template.index, (id,))
00200 
00201   @classmethod
00202   def sql_fetch_where1(cls, gctx, where, args = None, also_from = None):
00203     """
00204     Fetch one object from SQL, based on an arbitrary SQL WHERE expression.
00205     """
00206     results = cls.sql_fetch_where(gctx, where, args, also_from)
00207     if len(results) == 0:
00208       return None
00209     elif len(results) == 1:
00210       return results[0]
00211     else:
00212       raise rpki.exceptions.DBConsistancyError, \
00213             "Database contained multiple matches for %s where %s: %r" % \
00214             (cls.__name__, where % tuple(repr(a) for a in args), results)
00215 
00216   @classmethod
00217   def sql_fetch_all(cls, gctx):
00218     """
00219     Fetch all objects of this type from SQL.
00220     """
00221     return cls.sql_fetch_where(gctx, None)
00222 
00223   @classmethod
00224   def sql_fetch_where(cls, gctx, where, args = None, also_from = None):
00225     """
00226     Fetch objects of this type matching an arbitrary SQL WHERE expression.
00227     """
00228     if where is None:
00229       assert args is None and also_from is None
00230       if cls.sql_debug:
00231         rpki.log.debug("sql_fetch_where(%r)" % cls.sql_template.select)
00232       gctx.sql.execute(cls.sql_template.select)
00233     else:
00234       query = cls.sql_template.select
00235       if also_from is not None:
00236         query += "," + also_from
00237       query += " WHERE " + where
00238       if cls.sql_debug:
00239         rpki.log.debug("sql_fetch_where(%r, %r)" % (query, args))
00240       gctx.sql.execute(query, args)
00241     results = []
00242     for row in gctx.sql.fetchall():
00243       key = (cls, row[0])
00244       if key in gctx.sql.cache:
00245         results.append(gctx.sql.cache[key])
00246       else:
00247         results.append(cls.sql_init(gctx, row, key))
00248     return results
00249 
00250   @classmethod
00251   def sql_init(cls, gctx, row, key):
00252     """
00253     Initialize one Python object from the result of a SQL query.
00254     """
00255     self = cls()
00256     self.gctx = gctx
00257     self.sql_decode(dict(zip(cls.sql_template.columns, row)))
00258     gctx.sql.cache[key] = self
00259     self.sql_in_db = True
00260     self.sql_fetch_hook()
00261     return self
00262 
00263   def sql_mark_dirty(self):
00264     """
00265     Mark this object as needing to be written back to SQL.
00266     """
00267     self.gctx.sql.dirty.add(self)
00268 
00269   def sql_mark_clean(self):
00270     """
00271     Mark this object as not needing to be written back to SQL.
00272     """
00273     self.gctx.sql.dirty.discard(self)
00274 
00275   def sql_is_dirty(self):
00276     """
00277     Query whether this object needs to be written back to SQL.
00278     """
00279     return self in self.gctx.sql.dirty
00280 
00281   def sql_mark_deleted(self):
00282     """
00283     Mark this object as needing to be deleted in SQL.
00284     """
00285     self.sql_deleted = True
00286     self.sql_mark_dirty()
00287 
00288   def sql_store(self):
00289     """
00290     Store this object to SQL.
00291     """
00292     args = self.sql_encode()
00293     if not self.sql_in_db:
00294       if self.sql_debug:
00295         rpki.log.debug("sql_fetch_store(%r, %r)" % (self.sql_template.insert, args))
00296       self.gctx.sql.execute(self.sql_template.insert, args)
00297       setattr(self, self.sql_template.index, self.gctx.sql.lastrowid())
00298       self.gctx.sql.cache[(self.__class__, self.gctx.sql.lastrowid())] = self
00299       self.sql_insert_hook()
00300     else:
00301       if self.sql_debug:
00302         rpki.log.debug("sql_fetch_store(%r, %r)" % (self.sql_template.update, args))
00303       self.gctx.sql.execute(self.sql_template.update, args)
00304       self.sql_update_hook()
00305     key = (self.__class__, getattr(self, self.sql_template.index))
00306     assert key in self.gctx.sql.cache and self.gctx.sql.cache[key] == self
00307     self.sql_mark_clean()
00308     self.sql_in_db = True
00309 
00310   def sql_delete(self):
00311     """
00312     Delete this object from SQL.
00313     """
00314     if self.sql_in_db:
00315       id = getattr(self, self.sql_template.index)
00316       if self.sql_debug:
00317         rpki.log.debug("sql_fetch_delete(%r, %r)" % (self.sql_template.delete, id))
00318       self.sql_delete_hook()
00319       self.gctx.sql.execute(self.sql_template.delete, id)
00320       key = (self.__class__, id)
00321       if self.gctx.sql.cache.get(key) == self:
00322         del self.gctx.sql.cache[key]
00323       self.sql_in_db = False
00324     self.sql_mark_clean()
00325 
00326   def sql_encode(self):
00327     """
00328     Convert object attributes into a dict for use with canned SQL
00329     queries.  This is a default version that assumes a one-to-one
00330     mapping between column names in SQL and attribute names in Python.
00331     If you need something fancier, override this.
00332     """
00333     d = dict((a, getattr(self, a, None)) for a in self.sql_template.columns)
00334     for i in self.sql_template.map:
00335       if d.get(i) is not None:
00336         d[i] = self.sql_template.map[i].to_sql(d[i])
00337     return d
00338 
00339   def sql_decode(self, vals):
00340     """
00341     Initialize an object with values returned by self.sql_fetch().
00342     This is a default version that assumes a one-to-one mapping
00343     between column names in SQL and attribute names in Python.  If you
00344     need something fancier, override this.
00345     """
00346     for a in self.sql_template.columns:
00347       if vals.get(a) is not None and a in self.sql_template.map:
00348         setattr(self, a, self.sql_template.map[a].from_sql(vals[a]))
00349       else:
00350         setattr(self, a, vals[a])
00351 
00352   def sql_fetch_hook(self):
00353     """
00354     Customization hook.
00355     """
00356     pass
00357 
00358   def sql_insert_hook(self):
00359     """
00360     Customization hook.
00361     """
00362     pass
00363   
00364   def sql_update_hook(self):
00365     """
00366     Customization hook.
00367     """
00368     self.sql_delete_hook()
00369     self.sql_insert_hook()
00370 
00371   def sql_delete_hook(self):
00372     """
00373     Customization hook.
00374     """
00375     pass
00376 
 All Classes Namespaces Files Functions Variables