RPKI Engine  1.0
sql.py (3946)
Go to the documentation of this file.
00001 """
00002 SQL interface code.
00003 
00004 $Id: sql.py 3946 2011-07-27 18:21: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 rpki.mysql_import import (MySQLdb, _mysql_exceptions)
00036 
00037 import rpki.x509, rpki.resource_set, rpki.sundial, rpki.log
00038 
00039 class session(object):
00040   """
00041   SQL session layer.
00042   """
00043 
00044   def __init__(self, cfg):
00045 
00046     self.username = cfg.get("sql-username")
00047     self.database = cfg.get("sql-database")
00048     self.password = cfg.get("sql-password")
00049 
00050     self.cache = {}
00051     self.dirty = set()
00052 
00053     self.connect()
00054 
00055   def connect(self):
00056     self.db = MySQLdb.connect(user = self.username, db = self.database, passwd = self.password)
00057     self.cur = self.db.cursor()
00058     self.db.autocommit(True)
00059 
00060   def close(self):
00061     if self.cur:
00062       self.cur.close()
00063     self.cur = None
00064     if self.db:
00065       self.db.close()
00066     self.db = None
00067 
00068   def ping(self):
00069     return self.db.ping(True)
00070 
00071   def _wrap_execute(self, func, query, args):
00072     try:
00073       return func(query, args)
00074     except _mysql_exceptions.MySQLError:
00075       if self.dirty:
00076         rpki.log.warn("MySQL exception with dirty objects in SQL cache!")
00077       raise
00078 
00079   def execute(self, query, args = None):
00080     return self._wrap_execute(self.cur.execute, query, args)
00081 
00082   def executemany(self, query, args):
00083     return self._wrap_execute(self.cur.executemany, query, args)
00084 
00085   def fetchall(self):
00086     return self.cur.fetchall()
00087 
00088   def lastrowid(self):
00089     return self.cur.lastrowid
00090 
00091   def cache_clear(self):
00092     """
00093     Clear the object cache.
00094     """
00095     self.cache.clear()
00096 
00097   def assert_pristine(self):
00098     """
00099     Assert that there are no dirty objects in the cache.
00100     """
00101     assert not self.dirty, "Dirty objects in SQL cache: %s" % self.dirty
00102 
00103   def sweep(self):
00104     """
00105     Write any dirty objects out to SQL.
00106     """
00107     for s in self.dirty.copy():
00108       rpki.log.debug("Sweeping %r" % s)
00109       if s.sql_deleted:
00110         s.sql_delete()
00111       else:
00112         s.sql_store()
00113     self.assert_pristine()
00114 
00115 class template(object):
00116   """
00117   SQL template generator.
00118   """
00119 
00120   def __init__(self, table_name, index_column, *data_columns):
00121     """
00122     Build a SQL template.
00123     """
00124     type_map     = dict((x[0], x[1]) for x in data_columns if isinstance(x, tuple))
00125     data_columns = tuple(isinstance(x, tuple) and x[0] or x for x in data_columns)
00126     columns      = (index_column,) + data_columns
00127     self.table   = table_name
00128     self.index   = index_column
00129     self.columns = columns
00130     self.map     = type_map
00131     self.select  = "SELECT %s FROM %s" % (", ".join("%s.%s" % (table_name, c) for c in columns), table_name)
00132     self.insert  = "INSERT %s (%s) VALUES (%s)" % (table_name,
00133                                                    ", ".join(data_columns),
00134                                                    ", ".join("%(" + s + ")s" for s in data_columns))
00135     self.update  = "UPDATE %s SET %s WHERE %s = %%(%s)s" % (table_name,
00136                                                             ", ".join(s + " = %(" + s + ")s" for s in data_columns),
00137                                                             index_column,
00138                                                             index_column)
00139     self.delete  = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
00140 
00141 class sql_persistent(object):
00142   """
00143   Mixin for persistent class that needs to be stored in SQL.
00144   """
00145 
00146   ## @var sql_in_db
00147   # Whether this object is already in SQL or not.
00148 
00149   sql_in_db = False
00150 
00151   ## @var sql_deleted
00152   # Whether our cached copy of this object has been deleted.
00153 
00154   sql_deleted = False
00155 
00156   ## @var sql_debug
00157   # Enable logging of SQL actions
00158 
00159   sql_debug = False
00160 
00161   @classmethod
00162   def sql_fetch(cls, gctx, id):
00163     """
00164     Fetch one object from SQL, based on its primary key.
00165 
00166     Since in this one case we know that the primary index is also the
00167     cache key, we check for a cache hit directly in the hope of
00168     bypassing the SQL lookup entirely.
00169 
00170     This method is usually called via a one-line class-specific
00171     wrapper.  As a convenience, we also accept an id of None, and just
00172     return None in this case.
00173     """
00174 
00175     if id is None:
00176       return None
00177     assert isinstance(id, (int, long)), "id should be an integer, was %r" % type(id)
00178     key = (cls, id)
00179     if key in gctx.sql.cache:
00180       return gctx.sql.cache[key]
00181     else:
00182       return cls.sql_fetch_where1(gctx, "%s = %%s" % cls.sql_template.index, (id,))
00183 
00184   @classmethod
00185   def sql_fetch_where1(cls, gctx, where, args = None, also_from = None):
00186     """
00187     Fetch one object from SQL, based on an arbitrary SQL WHERE expression.
00188     """
00189     results = cls.sql_fetch_where(gctx, where, args, also_from)
00190     if len(results) == 0:
00191       return None
00192     elif len(results) == 1:
00193       return results[0]
00194     else:
00195       raise rpki.exceptions.DBConsistancyError, \
00196             "Database contained multiple matches for %s where %s: %r" % \
00197             (cls.__name__, where % tuple(repr(a) for a in args), results)
00198 
00199   @classmethod
00200   def sql_fetch_all(cls, gctx):
00201     """
00202     Fetch all objects of this type from SQL.
00203     """
00204     return cls.sql_fetch_where(gctx, None)
00205 
00206   @classmethod
00207   def sql_fetch_where(cls, gctx, where, args = None, also_from = None):
00208     """
00209     Fetch objects of this type matching an arbitrary SQL WHERE expression.
00210     """
00211     if where is None:
00212       assert args is None and also_from is None
00213       if cls.sql_debug:
00214         rpki.log.debug("sql_fetch_where(%r)" % cls.sql_template.select)
00215       gctx.sql.execute(cls.sql_template.select)
00216     else:
00217       query = cls.sql_template.select
00218       if also_from is not None:
00219         query += "," + also_from
00220       query += " WHERE " + where
00221       if cls.sql_debug:
00222         rpki.log.debug("sql_fetch_where(%r, %r)" % (query, args))
00223       gctx.sql.execute(query, args)
00224     results = []
00225     for row in gctx.sql.fetchall():
00226       key = (cls, row[0])
00227       if key in gctx.sql.cache:
00228         results.append(gctx.sql.cache[key])
00229       else:
00230         results.append(cls.sql_init(gctx, row, key))
00231     return results
00232 
00233   @classmethod
00234   def sql_init(cls, gctx, row, key):
00235     """
00236     Initialize one Python object from the result of a SQL query.
00237     """
00238     self = cls()
00239     self.gctx = gctx
00240     self.sql_decode(dict(zip(cls.sql_template.columns, row)))
00241     gctx.sql.cache[key] = self
00242     self.sql_in_db = True
00243     self.sql_fetch_hook()
00244     return self
00245 
00246   def sql_mark_dirty(self):
00247     """
00248     Mark this object as needing to be written back to SQL.
00249     """
00250     self.gctx.sql.dirty.add(self)
00251 
00252   def sql_mark_clean(self):
00253     """
00254     Mark this object as not needing to be written back to SQL.
00255     """
00256     self.gctx.sql.dirty.discard(self)
00257 
00258   def sql_is_dirty(self):
00259     """
00260     Query whether this object needs to be written back to SQL.
00261     """
00262     return self in self.gctx.sql.dirty
00263 
00264   def sql_mark_deleted(self):
00265     """
00266     Mark this object as needing to be deleted in SQL.
00267     """
00268     self.sql_deleted = True
00269     self.sql_mark_dirty()
00270 
00271   def sql_store(self):
00272     """
00273     Store this object to SQL.
00274     """
00275     args = self.sql_encode()
00276     if not self.sql_in_db:
00277       if self.sql_debug:
00278         rpki.log.debug("sql_fetch_store(%r, %r)" % (self.sql_template.insert, args))
00279       self.gctx.sql.execute(self.sql_template.insert, args)
00280       setattr(self, self.sql_template.index, self.gctx.sql.lastrowid())
00281       self.gctx.sql.cache[(self.__class__, self.gctx.sql.lastrowid())] = self
00282       self.sql_insert_hook()
00283     else:
00284       if self.sql_debug:
00285         rpki.log.debug("sql_fetch_store(%r, %r)" % (self.sql_template.update, args))
00286       self.gctx.sql.execute(self.sql_template.update, args)
00287       self.sql_update_hook()
00288     key = (self.__class__, getattr(self, self.sql_template.index))
00289     assert key in self.gctx.sql.cache and self.gctx.sql.cache[key] == self
00290     self.sql_mark_clean()
00291     self.sql_in_db = True
00292 
00293   def sql_delete(self):
00294     """
00295     Delete this object from SQL.
00296     """
00297     if self.sql_in_db:
00298       id = getattr(self, self.sql_template.index)
00299       if self.sql_debug:
00300         rpki.log.debug("sql_fetch_delete(%r, %r)" % (self.sql_template.delete, id))
00301       self.sql_delete_hook()
00302       self.gctx.sql.execute(self.sql_template.delete, id)
00303       key = (self.__class__, id)
00304       if self.gctx.sql.cache.get(key) == self:
00305         del self.gctx.sql.cache[key]
00306       self.sql_in_db = False
00307     self.sql_mark_clean()
00308 
00309   def sql_encode(self):
00310     """
00311     Convert object attributes into a dict for use with canned SQL
00312     queries.  This is a default version that assumes a one-to-one
00313     mapping between column names in SQL and attribute names in Python.
00314     If you need something fancier, override this.
00315     """
00316     d = dict((a, getattr(self, a, None)) for a in self.sql_template.columns)
00317     for i in self.sql_template.map:
00318       if d.get(i) is not None:
00319         d[i] = self.sql_template.map[i].to_sql(d[i])
00320     return d
00321 
00322   def sql_decode(self, vals):
00323     """
00324     Initialize an object with values returned by self.sql_fetch().
00325     This is a default version that assumes a one-to-one mapping
00326     between column names in SQL and attribute names in Python.  If you
00327     need something fancier, override this.
00328     """
00329     for a in self.sql_template.columns:
00330       if vals.get(a) is not None and a in self.sql_template.map:
00331         setattr(self, a, self.sql_template.map[a].from_sql(vals[a]))
00332       else:
00333         setattr(self, a, vals[a])
00334 
00335   def sql_fetch_hook(self):
00336     """
00337     Customization hook.
00338     """
00339     pass
00340 
00341   def sql_insert_hook(self):
00342     """
00343     Customization hook.
00344     """
00345     pass
00346   
00347   def sql_update_hook(self):
00348     """
00349     Customization hook.
00350     """
00351     self.sql_delete_hook()
00352     self.sql_insert_hook()
00353 
00354   def sql_delete_hook(self):
00355     """
00356     Customization hook.
00357     """
00358     pass
00359 
 All Classes Namespaces Files Functions Variables Properties