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