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