00001 """
00002 SQL interface code.
00003
00004 $Id: sql.py 2895 2009-12-21 17:17:55Z 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
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 """Clear the object cache."""
00110 self.cache.clear()
00111
00112 def assert_pristine(self):
00113 """Assert that there are no dirty objects in the cache."""
00114 assert not self.dirty, "Dirty objects in SQL cache: %s" % self.dirty
00115
00116 def sweep(self):
00117 """
00118 Write any dirty objects out to SQL.
00119 """
00120 for s in self.dirty.copy():
00121 rpki.log.debug("Sweeping %r" % s)
00122 if s.sql_deleted:
00123 s.sql_delete()
00124 else:
00125 s.sql_store()
00126 self.assert_pristine()
00127
00128 class template(object):
00129 """
00130 SQL template generator.
00131 """
00132
00133 def __init__(self, table_name, index_column, *data_columns):
00134 """Build a SQL template."""
00135 type_map = dict((x[0], x[1]) for x in data_columns if isinstance(x, tuple))
00136 data_columns = tuple(isinstance(x, tuple) and x[0] or x for x in data_columns)
00137 columns = (index_column,) + data_columns
00138 self.table = table_name
00139 self.index = index_column
00140 self.columns = columns
00141 self.map = type_map
00142 self.select = "SELECT %s FROM %s" % (", ".join("%s.%s" % (table_name, c) for c in columns), table_name)
00143 self.insert = "INSERT %s (%s) VALUES (%s)" % (table_name,
00144 ", ".join(data_columns),
00145 ", ".join("%(" + s + ")s" for s in data_columns))
00146 self.update = "UPDATE %s SET %s WHERE %s = %%(%s)s" % (table_name,
00147 ", ".join(s + " = %(" + s + ")s" for s in data_columns),
00148 index_column,
00149 index_column)
00150 self.delete = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
00151
00152 class sql_persistent(object):
00153 """
00154 Mixin for persistent class that needs to be stored in SQL.
00155 """
00156
00157
00158
00159
00160 sql_in_db = False
00161
00162
00163
00164
00165 sql_deleted = False
00166
00167
00168
00169
00170 sql_debug = False
00171
00172 @classmethod
00173 def sql_fetch(cls, gctx, id):
00174 """
00175 Fetch one object from SQL, based on its primary key.
00176
00177 Since in this one case we know that the primary index is also the
00178 cache key, we check for a cache hit directly in the hope of
00179 bypassing the SQL lookup entirely.
00180
00181 This method is usually called via a one-line class-specific
00182 wrapper. As a convenience, we also accept an id of None, and just
00183 return None in this case.
00184 """
00185
00186 if id is None:
00187 return None
00188 assert isinstance(id, (int, long)), "id should be an integer, was %r" % type(id)
00189 key = (cls, id)
00190 if key in gctx.sql.cache:
00191 return gctx.sql.cache[key]
00192 else:
00193 return cls.sql_fetch_where1(gctx, "%s = %%s" % cls.sql_template.index, (id,))
00194
00195 @classmethod
00196 def sql_fetch_where1(cls, gctx, where, args = None, also_from = None):
00197 """
00198 Fetch one object from SQL, based on an arbitrary SQL WHERE expression.
00199 """
00200 results = cls.sql_fetch_where(gctx, where, args, also_from)
00201 if len(results) == 0:
00202 return None
00203 elif len(results) == 1:
00204 return results[0]
00205 else:
00206 raise rpki.exceptions.DBConsistancyError, \
00207 "Database contained multiple matches for %s where %s: %r" % \
00208 (cls.__name__, where % tuple(repr(a) for a in args), results)
00209
00210 @classmethod
00211 def sql_fetch_all(cls, gctx):
00212 """Fetch all objects of this type from SQL."""
00213 return cls.sql_fetch_where(gctx, None)
00214
00215 @classmethod
00216 def sql_fetch_where(cls, gctx, where, args = None, also_from = None):
00217 """
00218 Fetch objects of this type matching an arbitrary SQL WHERE expression.
00219 """
00220 if where is None:
00221 assert args is None and also_from is None
00222 if cls.sql_debug:
00223 rpki.log.debug("sql_fetch_where(%r)" % cls.sql_template.select)
00224 gctx.sql.execute(cls.sql_template.select)
00225 else:
00226 query = cls.sql_template.select
00227 if also_from is not None:
00228 query += "," + also_from
00229 query += " WHERE " + where
00230 if cls.sql_debug:
00231 rpki.log.debug("sql_fetch_where(%r, %r)" % (query, args))
00232 gctx.sql.execute(query, args)
00233 results = []
00234 for row in gctx.sql.fetchall():
00235 key = (cls, row[0])
00236 if key in gctx.sql.cache:
00237 results.append(gctx.sql.cache[key])
00238 else:
00239 results.append(cls.sql_init(gctx, row, key))
00240 return results
00241
00242 @classmethod
00243 def sql_init(cls, gctx, row, key):
00244 """
00245 Initialize one Python object from the result of a SQL query.
00246 """
00247 self = cls()
00248 self.gctx = gctx
00249 self.sql_decode(dict(zip(cls.sql_template.columns, row)))
00250 gctx.sql.cache[key] = self
00251 self.sql_in_db = True
00252 self.sql_fetch_hook()
00253 return self
00254
00255 def sql_mark_dirty(self):
00256 """Mark this object as needing to be written back to SQL."""
00257 self.gctx.sql.dirty.add(self)
00258
00259 def sql_mark_clean(self):
00260 """Mark this object as not needing to be written back to SQL."""
00261 self.gctx.sql.dirty.discard(self)
00262
00263 def sql_is_dirty(self):
00264 """Query whether this object needs to be written back to SQL."""
00265 return self in self.gctx.sql.dirty
00266
00267 def sql_mark_deleted(self):
00268 """Mark this object as needing to be deleted in SQL."""
00269 self.sql_deleted = True
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 """Customization hook."""
00337 pass
00338
00339 def sql_insert_hook(self):
00340 """Customization hook."""
00341 pass
00342
00343 def sql_update_hook(self):
00344 """Customization hook."""
00345 self.sql_delete_hook()
00346 self.sql_insert_hook()
00347
00348 def sql_delete_hook(self):
00349 """Customization hook."""
00350 pass
00351