1 # Copyright (C) 2013 Ipsilon project Contributors, for license see COPYING
4 from ipsilon.util.log import Log
5 from sqlalchemy import create_engine
6 from sqlalchemy import MetaData, Table, Column, Text
7 from sqlalchemy.pool import QueuePool, SingletonThreadPool
8 from sqlalchemy.sql import select
15 CURRENT_SCHEMA_VERSION = 1
16 OPTIONS_COLUMNS = ['name', 'option', 'value']
17 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
24 def get_connection(cls, name):
25 if name not in cls.__instances.keys():
26 if cherrypy.config.get('db.conn.log', False):
27 logging.debug('SqlStore new: %s', name)
28 cls.__instances[name] = SqlStore(name)
29 return cls.__instances[name]
31 def __init__(self, name):
32 self.db_conn_log = cherrypy.config.get('db.conn.log', False)
33 self.debug('SqlStore init: %s' % name)
36 if '://' not in engine_name:
37 engine_name = 'sqlite:///' + engine_name
38 # This pool size is per configured database. The minimum needed,
39 # determined by binary search, is 23. We're using 25 so we have a bit
40 # more playroom, and then the overflow should make sure things don't
41 # break when we suddenly need more.
42 pool_args = {'poolclass': QueuePool,
45 if engine_name.startswith('sqlite://'):
46 # It's not possible to share connections for SQLite between
47 # threads, so let's use the SingletonThreadPool for them
48 pool_args = {'poolclass': SingletonThreadPool}
49 self._dbengine = create_engine(engine_name, **pool_args)
50 self.is_readonly = False
52 def debug(self, fact):
54 super(SqlStore, self).debug(fact)
60 self.debug('SqlStore connect: %s' % self.name)
61 conn = self._dbengine.connect()
63 def cleanup_connection():
64 self.debug('SqlStore cleanup: %s' % self.name)
66 cherrypy.request.hooks.attach('on_end_request', cleanup_connection)
71 def at(self, *args, **kwargs):
73 return f(self, *args, **kwargs)
79 def __init__(self, db_obj, table, columns, trans=True):
81 self._con = self._db.connection()
82 self._trans = self._con.begin() if trans else None
83 self._table = self._get_table(table, columns)
85 def _get_table(self, name, columns):
86 table = Table(name, MetaData(self._db.engine()))
88 table.append_column(Column(c, Text()))
91 def _where(self, kvfilter):
93 if kvfilter is not None:
95 w = self._table.columns[k] == kvfilter[k]
102 def _columns(self, columns=None):
104 if columns is not None:
107 cols.append(self._table.columns[c])
109 cols = self._table.columns
113 self._trans.rollback()
119 self._table.create(checkfirst=True)
122 self._table.drop(checkfirst=True)
125 def select(self, kvfilter=None, columns=None):
126 return self._con.execute(select(self._columns(columns),
127 self._where(kvfilter)))
130 def insert(self, values):
131 self._con.execute(self._table.insert(values))
134 def update(self, values, kvfilter):
135 self._con.execute(self._table.update(self._where(kvfilter), values))
138 def delete(self, kvfilter):
139 self._con.execute(self._table.delete(self._where(kvfilter)))
142 class FileStore(Log):
144 def __init__(self, name):
145 self._filename = name
146 self.is_readonly = True
147 self._timestamp = None
150 def get_config(self):
152 stat = os.stat(self._filename)
154 self.error("Unable to check config file %s: [%s]" % (
158 timestamp = stat.st_mtime
159 if self._config is None or timestamp > self._timestamp:
160 self._config = ConfigParser.RawConfigParser()
161 self._config.optionxform = str
162 self._config.read(self._filename)
166 class FileQuery(Log):
168 def __init__(self, fstore, table, columns, trans=True):
169 self._fstore = fstore
170 self._config = fstore.get_config()
171 self._section = table
172 if len(columns) > 3 or columns[-1] != 'value':
173 raise ValueError('Unsupported configuration format')
174 self._columns = columns
183 raise NotImplementedError
186 raise NotImplementedError
188 def select(self, kvfilter=None, columns=None):
189 if self._section not in self._config.sections():
192 opts = self._config.options(self._section)
196 if self._columns[0] in kvfilter:
197 prefix = kvfilter[self._columns[0]]
198 prefix_ = prefix + ' '
201 if len(self._columns) == 3 and self._columns[1] in kvfilter:
202 name = kvfilter[self._columns[1]]
205 if self._columns[-1] in kvfilter:
206 value = kvfilter[self._columns[-1]]
210 if len(self._columns) == 3:
212 if prefix and not o.startswith(prefix_):
215 col1, col2 = o.split(' ', 1)
216 if name and col2 != name:
219 col3 = self._config.get(self._section, o)
220 if value and col3 != value:
223 r = [col1, col2, col3]
226 if prefix and o != prefix:
228 r = [o, self._config.get(self._section, o)]
233 s.append(r[self._columns.index(c)])
238 self.debug('SELECT(%s, %s, %s) -> %s' % (self._section,
244 def insert(self, values):
245 raise NotImplementedError
247 def update(self, values, kvfilter):
248 raise NotImplementedError
250 def delete(self, kvfilter):
251 raise NotImplementedError
255 def __init__(self, config_name=None, database_url=None):
256 if config_name is None and database_url is None:
257 raise ValueError('config_name or database_url must be provided')
259 if config_name not in cherrypy.config:
260 raise NameError('Unknown database %s' % config_name)
261 name = cherrypy.config[config_name]
264 if name.startswith('configfile://'):
265 _, filename = name.split('://')
266 self._db = FileStore(filename)
267 self._query = FileQuery
269 self._db = SqlStore.get_connection(name)
270 self._query = SqlQuery
271 self._upgrade_database()
273 def _upgrade_database(self):
275 # If the database is readonly, we cannot do anything to the
276 # schema. Let's just return, and assume people checked the
279 current_version = self.load_options('dbinfo').get('scheme', None)
280 if current_version is None or 'version' not in current_version:
281 # No version stored, storing current version
282 self.save_options('dbinfo', 'scheme',
283 {'version': CURRENT_SCHEMA_VERSION})
284 current_version = CURRENT_SCHEMA_VERSION
286 current_version = int(current_version['version'])
287 if current_version != CURRENT_SCHEMA_VERSION:
288 self.debug('Upgrading database schema from %i to %i' % (
289 current_version, CURRENT_SCHEMA_VERSION))
290 self._upgrade_database_from(current_version)
292 def _upgrade_database_from(self, old_schema_version):
293 # Insert code here to upgrade from old_schema_version to
294 # CURRENT_SCHEMA_VERSION
295 raise Exception('Unable to upgrade database to current schema'
296 ' version: version %i is unknown!' %
300 def is_readonly(self):
301 return self._db.is_readonly
303 def _row_to_dict_tree(self, data, row):
309 self._row_to_dict_tree(d2, row[1:])
313 if data[name] is list:
314 data[name].append(value)
317 data[name] = [v, value]
321 def _rows_to_dict_tree(self, rows):
324 self._row_to_dict_tree(data, r)
327 def _load_data(self, table, columns, kvfilter=None):
330 q = self._query(self._db, table, columns, trans=False)
331 rows = q.select(kvfilter)
332 except Exception, e: # pylint: disable=broad-except
333 self.error("Failed to load data for table %s: [%s]" % (table, e))
334 return self._rows_to_dict_tree(rows)
336 def load_config(self):
338 columns = ['name', 'value']
339 return self._load_data(table, columns)
341 def load_options(self, table, name=None):
344 kvfilter['name'] = name
345 options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
346 if name and name in options:
350 def save_options(self, table, name, options):
354 q = self._query(self._db, table, OPTIONS_COLUMNS)
355 rows = q.select({'name': name}, ['option', 'value'])
357 curvals[row[0]] = row[1]
361 q.update({'value': options[opt]},
362 {'name': name, 'option': opt})
364 q.insert((name, opt, options[opt]))
367 except Exception, e: # pylint: disable=broad-except
370 self.error("Failed to save options: [%s]" % e)
373 def delete_options(self, table, name, options=None):
374 kvfilter = {'name': name}
377 q = self._query(self._db, table, OPTIONS_COLUMNS)
382 kvfilter['option'] = opt
385 except Exception, e: # pylint: disable=broad-except
388 self.error("Failed to delete from %s: [%s]" % (table, e))
391 def new_unique_data(self, table, data):
392 newid = str(uuid.uuid4())
395 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
397 q.insert((newid, name, data[name]))
399 except Exception, e: # pylint: disable=broad-except
402 self.error("Failed to store %s data: [%s]" % (table, e))
406 def get_unique_data(self, table, uuidval=None, name=None, value=None):
409 kvfilter['uuid'] = uuidval
411 kvfilter['name'] = name
413 kvfilter['value'] = value
414 return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
416 def save_unique_data(self, table, data):
419 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
422 rows = q.select({'uuid': uid}, ['name', 'value'])
429 if datum[name] is None:
430 q.delete({'uuid': uid, 'name': name})
432 q.update({'value': datum[name]},
433 {'uuid': uid, 'name': name})
435 if datum[name] is not None:
436 q.insert((uid, name, datum[name]))
439 except Exception, e: # pylint: disable=broad-except
442 self.error("Failed to store data in %s: [%s]" % (table, e))
445 def del_unique_data(self, table, uuidval):
446 kvfilter = {'uuid': uuidval}
448 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
450 except Exception, e: # pylint: disable=broad-except
451 self.error("Failed to delete data from %s: [%s]" % (table, e))
453 def _reset_data(self, table):
456 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
460 except Exception, e: # pylint: disable=broad-except
463 self.error("Failed to erase all data from %s: [%s]" % (table, e))
466 class AdminStore(Store):
469 super(AdminStore, self).__init__('admin.config.db')
471 def get_data(self, plugin, idval=None, name=None, value=None):
472 return self.get_unique_data(plugin+"_data", idval, name, value)
474 def save_data(self, plugin, data):
475 return self.save_unique_data(plugin+"_data", data)
477 def new_datum(self, plugin, datum):
478 table = plugin+"_data"
479 return self.new_unique_data(table, datum)
481 def del_datum(self, plugin, idval):
482 table = plugin+"_data"
483 return self.del_unique_data(table, idval)
485 def wipe_data(self, plugin):
486 table = plugin+"_data"
487 self._reset_data(table)
490 class UserStore(Store):
492 def __init__(self, path=None):
493 super(UserStore, self).__init__('user.prefs.db')
495 def save_user_preferences(self, user, options):
496 self.save_options('users', user, options)
498 def load_user_preferences(self, user):
499 return self.load_options('users', user)
501 def save_plugin_data(self, plugin, user, options):
502 self.save_options(plugin+"_data", user, options)
504 def load_plugin_data(self, plugin, user):
505 return self.load_options(plugin+"_data", user)
508 class TranStore(Store):
510 def __init__(self, path=None):
511 super(TranStore, self).__init__('transactions.db')
514 class SAML2SessionStore(Store):
516 def __init__(self, path=None):
517 super(SAML2SessionStore, self).__init__('saml2.sessions.db')
518 self.table = 'sessions'
520 def _get_unique_id_from_column(self, name, value):
522 The query is going to return only the column in the query.
523 Use this method to get the uuidval which can be used to fetch
526 Returns None or the uuid of the first value found.
528 data = self.get_unique_data(self.table, name=name, value=value)
533 raise ValueError("Multiple entries returned")
534 return data.keys()[0]
536 def get_data(self, idval=None, name=None, value=None):
537 return self.get_unique_data(self.table, idval, name, value)
539 def new_session(self, datum):
540 return self.new_unique_data(self.table, datum)
542 def get_session(self, session_id=None, request_id=None):
544 uuidval = self._get_unique_id_from_column('session_id', session_id)
546 uuidval = self._get_unique_id_from_column('request_id', request_id)
548 raise ValueError("Unable to find session")
551 data = self.get_unique_data(self.table, uuidval=uuidval)
552 return uuidval, data[uuidval]
554 def get_user_sessions(self, user):
556 Retrun a list of all sessions for a given user.
558 rows = self.get_unique_data(self.table, name='user', value=user)
560 # We have a list of sessions for this user, now get the details
563 data = self.get_unique_data(self.table, uuidval=r)
564 logged_in.append(data)
568 def update_session(self, datum):
569 self.save_unique_data(self.table, datum)
571 def remove_session(self, uuidval):
572 self.del_unique_data(self.table, uuidval)
575 self._reset_data(self.table)