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