00001 """SQL interface code.
00002
00003 $Id: sql.py 1873 2008-06-12 02:49:41Z sra $
00004
00005 Copyright (C) 2007--2008 American Registry for Internet Numbers ("ARIN")
00006
00007 Permission to use, copy, modify, and distribute this software for any
00008 purpose with or without fee is hereby granted, provided that the above
00009 copyright notice and this permission notice appear in all copies.
00010
00011 THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH
00012 REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
00013 AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT,
00014 INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
00015 LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
00016 OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
00017 PERFORMANCE OF THIS SOFTWARE.
00018 """
00019
00020 import MySQLdb, time, warnings, _mysql_exceptions
00021 import rpki.x509, rpki.resource_set, rpki.sundial, rpki.log
00022
00023 class session(object):
00024 """SQL session layer."""
00025
00026 _exceptions_enabled = False
00027
00028 def __init__(self, cfg):
00029
00030 if not self._exceptions_enabled:
00031 warnings.simplefilter("error", _mysql_exceptions.Warning)
00032 self.__class__._exceptions_enabled = True
00033
00034 self.username = cfg.get("sql-username")
00035 self.database = cfg.get("sql-database")
00036 self.password = cfg.get("sql-password")
00037
00038 self.cache = {}
00039 self.dirty = set()
00040
00041 self.connect()
00042
00043 def connect(self):
00044 self.db = MySQLdb.connect(user = self.username, db = self.database, passwd = self.password)
00045 self.cur = self.db.cursor()
00046
00047 def close(self):
00048 if self.cur:
00049 self.cur.close()
00050 self.cur = None
00051 if self.db:
00052 self.db.close()
00053 self.db = None
00054
00055 def ping(self):
00056 return self.db.ping(True)
00057
00058 def _wrap_execute(self, func, query, args):
00059 try:
00060 return func(query, args)
00061 except _mysql_exceptions.MySQLError:
00062 if self.dirty:
00063 rpki.log.warn("MySQL exception with dirty objects in SQL cache!")
00064 raise
00065
00066 def execute(self, query, args = None):
00067 return self._wrap_execute(self.cur.execute, query, args)
00068
00069 def executemany(self, query, args):
00070 return self._wrap_execute(self.cur.executemany, query, args)
00071
00072 def fetchall(self):
00073 return self.cur.fetchall()
00074
00075 def lastrowid(self):
00076 return self.cur.lastrowid
00077
00078 def cache_clear(self):
00079 """Clear the object cache."""
00080 self.cache.clear()
00081
00082 def assert_pristine(self):
00083 """Assert that there are no dirty objects in the cache."""
00084 assert not self.dirty, "Dirty objects in SQL cache: %s" % self.dirty
00085
00086 def sweep(self):
00087 """Write any dirty objects out to SQL."""
00088 for s in self.dirty.copy():
00089 rpki.log.debug("Sweeping %s" % repr(s))
00090 if s.sql_deleted:
00091 s.sql_delete()
00092 else:
00093 s.sql_store()
00094 self.assert_pristine()
00095
00096 class template(object):
00097 """SQL template generator."""
00098 def __init__(self, table_name, index_column, *data_columns):
00099 """Build a SQL template."""
00100 type_map = dict((x[0],x[1]) for x in data_columns if isinstance(x, tuple))
00101 data_columns = tuple(isinstance(x, tuple) and x[0] or x for x in data_columns)
00102 columns = (index_column,) + data_columns
00103 self.table = table_name
00104 self.index = index_column
00105 self.columns = columns
00106 self.map = type_map
00107 self.select = "SELECT %s FROM %s" % (", ".join(columns), table_name)
00108 self.insert = "INSERT %s (%s) VALUES (%s)" % (table_name, ", ".join(data_columns),
00109 ", ".join("%(" + s + ")s" for s in data_columns))
00110 self.update = "UPDATE %s SET %s WHERE %s = %%(%s)s" % \
00111 (table_name, ", ".join(s + " = %(" + s + ")s" for s in data_columns),
00112 index_column, index_column)
00113 self.delete = "DELETE FROM %s WHERE %s = %%s" % (table_name, index_column)
00114
00115 class sql_persistant(object):
00116 """Mixin for persistant class that needs to be stored in SQL.
00117 """
00118
00119
00120
00121
00122 sql_in_db = False
00123
00124
00125
00126
00127 sql_deleted = False
00128
00129
00130
00131
00132 sql_debug = False
00133
00134 @classmethod
00135 def sql_fetch(cls, gctx, id):
00136 """Fetch one object from SQL, based on its primary key.
00137
00138 Since in this one case we know that the primary index is also the
00139 cache key, we check for a cache hit directly in the hope of
00140 bypassing the SQL lookup entirely.
00141
00142 This method is usually called via a one-line class-specific
00143 wrapper. As a convenience, we also accept an id of None, and just
00144 return None in this case.
00145 """
00146
00147 if id is None:
00148 return None
00149 assert isinstance(id, (int, long)), "id should be an integer, was %s" % repr(type(id))
00150 key = (cls, id)
00151 if key in gctx.sql.cache:
00152 return gctx.sql.cache[key]
00153 else:
00154 return cls.sql_fetch_where1(gctx, "%s = %%s" % cls.sql_template.index, (id,))
00155
00156 @classmethod
00157 def sql_fetch_where1(cls, gctx, where, args = None):
00158 """Fetch one object from SQL, based on an arbitrary SQL WHERE expression."""
00159 results = cls.sql_fetch_where(gctx, where, args)
00160 if len(results) == 0:
00161 return None
00162 elif len(results) == 1:
00163 return results[0]
00164 else:
00165 raise rpki.exceptions.DBConsistancyError, \
00166 "Database contained multiple matches for %s where %s" % \
00167 (cls.__name__, where % tuple(repr(a) for a in args))
00168
00169 @classmethod
00170 def sql_fetch_all(cls, gctx):
00171 """Fetch all objects of this type from SQL."""
00172 return cls.sql_fetch_where(gctx, None)
00173
00174 @classmethod
00175 def sql_fetch_where(cls, gctx, where, args = None):
00176 """Fetch objects of this type matching an arbitrary SQL WHERE expression."""
00177 if where is None:
00178 assert args is None
00179 if cls.sql_debug:
00180 rpki.log.debug("sql_fetch_where(%s)" % repr(cls.sql_template.select))
00181 gctx.sql.execute(cls.sql_template.select)
00182 else:
00183 query = cls.sql_template.select + " WHERE " + where
00184 if cls.sql_debug:
00185 rpki.log.debug("sql_fetch_where(%s, %s)" % (repr(query), repr(args)))
00186 gctx.sql.execute(query, args)
00187 results = []
00188 for row in gctx.sql.fetchall():
00189 key = (cls, row[0])
00190 if key in gctx.sql.cache:
00191 results.append(gctx.sql.cache[key])
00192 else:
00193 results.append(cls.sql_init(gctx, row, key))
00194 return results
00195
00196 @classmethod
00197 def sql_init(cls, gctx, row, key):
00198 """Initialize one Python object from the result of a SQL query."""
00199 self = cls()
00200 self.gctx = gctx
00201 self.sql_decode(dict(zip(cls.sql_template.columns, row)))
00202 gctx.sql.cache[key] = self
00203 self.sql_in_db = True
00204 self.sql_fetch_hook()
00205 return self
00206
00207 def sql_mark_dirty(self):
00208 """Mark this object as needing to be written back to SQL."""
00209 self.gctx.sql.dirty.add(self)
00210
00211 def sql_mark_clean(self):
00212 """Mark this object as not needing to be written back to SQL."""
00213 self.gctx.sql.dirty.discard(self)
00214
00215 def sql_is_dirty(self):
00216 """Query whether this object needs to be written back to SQL."""
00217 return self in self.gctx.sql.dirty
00218
00219 def sql_mark_deleted(self):
00220 """Mark this object as needing to be deleted in SQL."""
00221 self.sql_deleted = True
00222
00223 def sql_store(self):
00224 """Store this object to SQL."""
00225 if not self.sql_in_db:
00226 self.gctx.sql.execute(self.sql_template.insert, self.sql_encode())
00227 setattr(self, self.sql_template.index, self.gctx.sql.lastrowid())
00228 self.gctx.sql.cache[(self.__class__, self.gctx.sql.lastrowid())] = self
00229 self.sql_insert_hook()
00230 else:
00231 self.gctx.sql.execute(self.sql_template.update, self.sql_encode())
00232 self.sql_update_hook()
00233 key = (self.__class__, getattr(self, self.sql_template.index))
00234 assert key in self.gctx.sql.cache and self.gctx.sql.cache[key] == self
00235 self.sql_mark_clean()
00236 self.sql_in_db = True
00237
00238 def sql_delete(self):
00239 """Delete this object from SQL."""
00240 if self.sql_in_db:
00241 id = getattr(self, self.sql_template.index)
00242 self.gctx.sql.execute(self.sql_template.delete, id)
00243 self.sql_delete_hook()
00244 key = (self.__class__, id)
00245 if self.gctx.sql.cache.get(key) == self:
00246 del self.gctx.sql.cache[key]
00247 self.sql_in_db = False
00248 self.sql_mark_clean()
00249
00250 def sql_encode(self):
00251 """Convert object attributes into a dict for use with canned SQL
00252 queries. This is a default version that assumes a one-to-one
00253 mapping between column names in SQL and attribute names in Python.
00254 If you need something fancier, override this.
00255 """
00256 d = dict((a, getattr(self, a, None)) for a in self.sql_template.columns)
00257 for i in self.sql_template.map:
00258 if d.get(i) is not None:
00259 d[i] = self.sql_template.map[i].to_sql(d[i])
00260 return d
00261
00262 def sql_decode(self, vals):
00263 """Initialize an object with values returned by self.sql_fetch().
00264 This is a default version that assumes a one-to-one mapping
00265 between column names in SQL and attribute names in Python. If you
00266 need something fancier, override this.
00267 """
00268 for a in self.sql_template.columns:
00269 if vals.get(a) is not None and a in self.sql_template.map:
00270 setattr(self, a, self.sql_template.map[a].from_sql(vals[a]))
00271 else:
00272 setattr(self, a, vals[a])
00273
00274 def sql_fetch_hook(self):
00275 """Customization hook."""
00276 pass
00277
00278 def sql_insert_hook(self):
00279 """Customization hook."""
00280 pass
00281
00282 def sql_update_hook(self):
00283 """Customization hook."""
00284 self.sql_delete_hook()
00285 self.sql_insert_hook()
00286
00287 def sql_delete_hook(self):
00288 """Customization hook."""
00289 pass
00290