RPKI Engine 1.0
|
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