1 # Copyright (C) 2013 Ipsilon project Contributors, for license see COPYING
5 from ipsilon.util.log import Log
6 from sqlalchemy import create_engine
7 from sqlalchemy import MetaData, Table, Column, Text
8 from sqlalchemy.pool import QueuePool, SingletonThreadPool
9 from sqlalchemy.schema import (PrimaryKeyConstraint, Index, AddConstraint,
11 from sqlalchemy.sql import select, and_
18 CURRENT_SCHEMA_VERSION = 2
19 OPTIONS_TABLE = {'columns': ['name', 'option', 'value'],
20 'primary_key': ('name', 'option'),
21 'indexes': [('name',)]
23 UNIQUE_DATA_TABLE = {'columns': ['uuid', 'name', 'value'],
24 'primary_key': ('uuid', 'name'),
25 'indexes': [('uuid',)]
29 class DatabaseError(Exception):
34 # Some helper functions used for upgrades
35 def add_constraint(self, table):
36 raise NotImplementedError()
38 def add_index(self, index):
39 raise NotImplementedError()
42 class SqlStore(BaseStore):
46 def get_connection(cls, name):
47 if name not in cls.__instances:
48 if cherrypy.config.get('db.conn.log', False):
49 logging.debug('SqlStore new: %s', name)
50 cls.__instances[name] = SqlStore(name)
51 return cls.__instances[name]
53 def __init__(self, name):
54 self.db_conn_log = cherrypy.config.get('db.conn.log', False)
55 self.debug('SqlStore init: %s' % name)
58 if '://' not in engine_name:
59 engine_name = 'sqlite:///' + engine_name
60 # This pool size is per configured database. The minimum needed,
61 # determined by binary search, is 23. We're using 25 so we have a bit
62 # more playroom, and then the overflow should make sure things don't
63 # break when we suddenly need more.
64 pool_args = {'poolclass': QueuePool,
67 if engine_name.startswith('sqlite://'):
68 # It's not possible to share connections for SQLite between
69 # threads, so let's use the SingletonThreadPool for them
70 pool_args = {'poolclass': SingletonThreadPool}
71 self._dbengine = create_engine(engine_name, **pool_args)
72 self.is_readonly = False
74 def add_constraint(self, constraint):
75 if self._dbengine.dialect.name != 'sqlite':
76 # It is impossible to add constraints to a pre-existing table for
78 # source: http://www.sqlite.org/omitted.html
79 create_constraint = AddConstraint(constraint, bind=self._dbengine)
80 create_constraint.execute()
82 def add_index(self, index):
83 add_index = CreateIndex(index, bind=self._dbengine)
86 def debug(self, fact):
88 super(SqlStore, self).debug(fact)
94 self.debug('SqlStore connect: %s' % self.name)
95 conn = self._dbengine.connect()
97 def cleanup_connection():
98 self.debug('SqlStore cleanup: %s' % self.name)
100 cherrypy.request.hooks.attach('on_end_request', cleanup_connection)
106 def __init__(self, db_obj, table, table_def, trans=True):
108 self._con = self._db.connection()
109 self._trans = self._con.begin() if trans else None
110 self._table = self._get_table(table, table_def)
112 def _get_table(self, name, table_def):
113 if isinstance(table_def, list):
114 table_def = {'columns': table_def,
118 for col_name in table_def['columns']:
119 table_creation.append(Column(col_name, Text()))
120 if table_def['primary_key']:
121 table_creation.append(PrimaryKeyConstraint(
122 *table_def['primary_key']))
123 for index in table_def['indexes']:
124 idx_name = 'idx_%s_%s' % (name, '_'.join(index))
125 table_creation.append(Index(idx_name, *index))
126 table = Table(name, MetaData(self._db.engine()), *table_creation)
129 def _where(self, kvfilter):
131 if kvfilter is not None:
133 w = self._table.columns[k] == kvfilter[k]
140 def _columns(self, columns=None):
142 if columns is not None:
145 cols.append(self._table.columns[c])
147 cols = self._table.columns
151 self._trans.rollback()
157 self._table.create(checkfirst=True)
160 self._table.drop(checkfirst=True)
162 def select(self, kvfilter=None, columns=None):
163 return self._con.execute(select(self._columns(columns),
164 self._where(kvfilter)))
166 def insert(self, values):
167 self._con.execute(self._table.insert(values))
169 def update(self, values, kvfilter):
170 self._con.execute(self._table.update(self._where(kvfilter), values))
172 def delete(self, kvfilter):
173 self._con.execute(self._table.delete(self._where(kvfilter)))
176 class FileStore(BaseStore):
178 def __init__(self, name):
179 self._filename = name
180 self.is_readonly = True
181 self._timestamp = None
184 def get_config(self):
186 stat = os.stat(self._filename)
188 self.error("Unable to check config file %s: [%s]" % (
192 timestamp = stat.st_mtime
193 if self._config is None or timestamp > self._timestamp:
194 self._config = ConfigParser.RawConfigParser()
195 self._config.optionxform = str
196 self._config.read(self._filename)
199 def add_constraint(self, table):
200 raise NotImplementedError()
202 def add_index(self, index):
203 raise NotImplementedError()
206 class FileQuery(Log):
208 def __init__(self, fstore, table, table_def, trans=True):
209 # We don't need indexes in a FileQuery, so drop that info
210 if isinstance(table_def, dict):
211 columns = table_def['columns']
214 self._fstore = fstore
215 self._config = fstore.get_config()
216 self._section = table
217 if len(columns) > 3 or columns[-1] != 'value':
218 raise ValueError('Unsupported configuration format')
219 self._columns = columns
228 raise NotImplementedError
231 raise NotImplementedError
233 def select(self, kvfilter=None, columns=None):
234 if self._section not in self._config.sections():
237 opts = self._config.options(self._section)
241 if self._columns[0] in kvfilter:
242 prefix = kvfilter[self._columns[0]]
243 prefix_ = prefix + ' '
246 if len(self._columns) == 3 and self._columns[1] in kvfilter:
247 name = kvfilter[self._columns[1]]
250 if self._columns[-1] in kvfilter:
251 value = kvfilter[self._columns[-1]]
255 if len(self._columns) == 3:
257 if prefix and not o.startswith(prefix_):
260 col1, col2 = o.split(' ', 1)
261 if name and col2 != name:
264 col3 = self._config.get(self._section, o)
265 if value and col3 != value:
268 r = [col1, col2, col3]
271 if prefix and o != prefix:
273 r = [o, self._config.get(self._section, o)]
278 s.append(r[self._columns.index(c)])
283 self.debug('SELECT(%s, %s, %s) -> %s' % (self._section,
289 def insert(self, values):
290 raise NotImplementedError
292 def update(self, values, kvfilter):
293 raise NotImplementedError
295 def delete(self, kvfilter):
296 raise NotImplementedError
302 def __init__(self, config_name=None, database_url=None):
303 if config_name is None and database_url is None:
304 raise ValueError('config_name or database_url must be provided')
306 if config_name not in cherrypy.config:
307 raise NameError('Unknown database %s' % config_name)
308 name = cherrypy.config[config_name]
311 if name.startswith('configfile://'):
312 _, filename = name.split('://')
313 self._db = FileStore(filename)
314 self._query = FileQuery
316 self._db = SqlStore.get_connection(name)
317 self._query = SqlQuery
319 if not self._is_upgrade:
320 self._check_database()
322 def _code_schema_version(self):
323 # This function makes it possible for separate plugins to have
324 # different schema versions. We default to the global schema
326 return CURRENT_SCHEMA_VERSION
328 def _get_schema_version(self):
329 # We are storing multiple versions: one per class
330 # That way, we can support plugins with differing schema versions from
331 # the main codebase, and even in the same database.
332 q = self._query(self._db, 'dbinfo', OPTIONS_TABLE, trans=False)
334 cls_name = self.__class__.__name__
335 current_version = self.load_options('dbinfo').get('%s_schema'
337 if 'version' in current_version:
338 return int(current_version['version'])
340 # Also try the old table name.
341 # "scheme" was a typo, but we need to retain that now for compat
342 fallback_version = self.load_options('dbinfo').get('scheme',
344 if 'version' in fallback_version:
345 # Explanation for this is in def upgrade_database(self)
350 def _check_database(self):
352 # If the database is readonly, we cannot do anything to the
353 # schema. Let's just return, and assume people checked the
357 current_version = self._get_schema_version()
358 if current_version is None:
359 self.error('Database initialization required! ' +
360 'Please run ipsilon-upgrade-database')
361 raise DatabaseError('Database initialization required for %s' %
362 self.__class__.__name__)
363 if current_version != self._code_schema_version():
364 self.error('Database upgrade required! ' +
365 'Please run ipsilon-upgrade-database')
366 raise DatabaseError('Database upgrade required for %s' %
367 self.__class__.__name__)
369 def _store_new_schema_version(self, new_version):
370 cls_name = self.__class__.__name__
371 self.save_options('dbinfo', '%s_schema' % cls_name,
372 {'version': new_version})
374 def _initialize_schema(self):
375 raise NotImplementedError()
377 def _upgrade_schema(self, old_version):
378 # Datastores need to figure out what to do with bigger old_versions
380 # They might implement downgrading if that's feasible, or just throw
381 # NotImplementedError
382 # Should return the new schema version
383 raise NotImplementedError()
385 def upgrade_database(self):
386 # Do whatever is needed to get schema to current version
387 old_schema_version = self._get_schema_version()
388 if old_schema_version is None:
389 # Just initialize a new schema
390 self._initialize_schema()
391 self._store_new_schema_version(self._code_schema_version())
392 elif old_schema_version == -1:
393 # This is a special-case from 1.0: we only created tables at the
394 # first time they were actually used, but the upgrade code assumes
395 # that the tables exist. So let's fix this.
396 self._initialize_schema()
397 # The old version was schema version 1
398 self._store_new_schema_version(1)
399 self.upgrade_database()
400 elif old_schema_version != self._code_schema_version():
401 # Upgrade from old_schema_version to code_schema_version
402 self.debug('Upgrading from schema version %i' % old_schema_version)
403 new_version = self._upgrade_schema(old_schema_version)
405 error = ('Schema upgrade error: %s did not provide a ' +
406 'new schema version number!' %
407 self.__class__.__name__)
409 raise Exception(error)
410 self._store_new_schema_version(new_version)
411 # Check if we are now up-to-date
412 self.upgrade_database()
415 def is_readonly(self):
416 return self._db.is_readonly
418 def _row_to_dict_tree(self, data, row):
424 self._row_to_dict_tree(d2, row[1:])
428 if data[name] is list:
429 data[name].append(value)
432 data[name] = [v, value]
436 def _rows_to_dict_tree(self, rows):
439 self._row_to_dict_tree(data, r)
442 def _load_data(self, table, columns, kvfilter=None):
445 q = self._query(self._db, table, columns, trans=False)
446 rows = q.select(kvfilter)
447 except Exception, e: # pylint: disable=broad-except
448 self.error("Failed to load data for table %s: [%s]" % (table, e))
449 return self._rows_to_dict_tree(rows)
451 def load_config(self):
453 columns = ['name', 'value']
454 return self._load_data(table, columns)
456 def load_options(self, table, name=None):
459 kvfilter['name'] = name
460 options = self._load_data(table, OPTIONS_TABLE, kvfilter)
461 if name and name in options:
465 def save_options(self, table, name, options):
469 q = self._query(self._db, table, OPTIONS_TABLE)
470 rows = q.select({'name': name}, ['option', 'value'])
472 curvals[row[0]] = row[1]
476 q.update({'value': options[opt]},
477 {'name': name, 'option': opt})
479 q.insert((name, opt, options[opt]))
482 except Exception, e: # pylint: disable=broad-except
485 self.error("Failed to save options: [%s]" % e)
488 def delete_options(self, table, name, options=None):
489 kvfilter = {'name': name}
492 q = self._query(self._db, table, OPTIONS_TABLE)
497 kvfilter['option'] = opt
500 except Exception, e: # pylint: disable=broad-except
503 self.error("Failed to delete from %s: [%s]" % (table, e))
506 def new_unique_data(self, table, data):
507 newid = str(uuid.uuid4())
510 q = self._query(self._db, table, UNIQUE_DATA_TABLE)
512 q.insert((newid, name, data[name]))
514 except Exception, e: # pylint: disable=broad-except
517 self.error("Failed to store %s data: [%s]" % (table, e))
521 def get_unique_data(self, table, uuidval=None, name=None, value=None):
524 kvfilter['uuid'] = uuidval
526 kvfilter['name'] = name
528 kvfilter['value'] = value
529 return self._load_data(table, UNIQUE_DATA_TABLE, kvfilter)
531 def save_unique_data(self, table, data):
534 q = self._query(self._db, table, UNIQUE_DATA_TABLE)
537 rows = q.select({'uuid': uid}, ['name', 'value'])
544 if datum[name] is None:
545 q.delete({'uuid': uid, 'name': name})
547 q.update({'value': datum[name]},
548 {'uuid': uid, 'name': name})
550 if datum[name] is not None:
551 q.insert((uid, name, datum[name]))
554 except Exception, e: # pylint: disable=broad-except
557 self.error("Failed to store data in %s: [%s]" % (table, e))
560 def del_unique_data(self, table, uuidval):
561 kvfilter = {'uuid': uuidval}
563 q = self._query(self._db, table, UNIQUE_DATA_TABLE, trans=False)
565 except Exception, e: # pylint: disable=broad-except
566 self.error("Failed to delete data from %s: [%s]" % (table, e))
568 def _reset_data(self, table):
571 q = self._query(self._db, table, UNIQUE_DATA_TABLE)
575 except Exception, e: # pylint: disable=broad-except
578 self.error("Failed to erase all data from %s: [%s]" % (table, e))
581 class AdminStore(Store):
584 super(AdminStore, self).__init__('admin.config.db')
586 def get_data(self, plugin, idval=None, name=None, value=None):
587 return self.get_unique_data(plugin+"_data", idval, name, value)
589 def save_data(self, plugin, data):
590 return self.save_unique_data(plugin+"_data", data)
592 def new_datum(self, plugin, datum):
593 table = plugin+"_data"
594 return self.new_unique_data(table, datum)
596 def del_datum(self, plugin, idval):
597 table = plugin+"_data"
598 return self.del_unique_data(table, idval)
600 def wipe_data(self, plugin):
601 table = plugin+"_data"
602 self._reset_data(table)
604 def _initialize_schema(self):
605 for table in ['config',
609 q = self._query(self._db, table, OPTIONS_TABLE, trans=False)
612 def _upgrade_schema(self, old_version):
614 # In schema version 2, we added indexes and primary keys
615 for table in ['config',
619 # pylint: disable=protected-access
620 table = self._query(self._db, table, OPTIONS_TABLE,
622 self._db.add_constraint(table.primary_key)
623 for index in table.indexes:
624 self._db.add_index(index)
627 raise NotImplementedError()
629 def create_plugin_data_table(self, plugin_name):
630 table = plugin_name+'_data'
631 q = self._query(self._db, table, UNIQUE_DATA_TABLE,
636 class UserStore(Store):
638 def __init__(self, path=None):
639 super(UserStore, self).__init__('user.prefs.db')
641 def save_user_preferences(self, user, options):
642 self.save_options('users', user, options)
644 def load_user_preferences(self, user):
645 return self.load_options('users', user)
647 def save_plugin_data(self, plugin, user, options):
648 self.save_options(plugin+"_data", user, options)
650 def load_plugin_data(self, plugin, user):
651 return self.load_options(plugin+"_data", user)
653 def _initialize_schema(self):
654 q = self._query(self._db, 'users', OPTIONS_TABLE, trans=False)
657 def _upgrade_schema(self, old_version):
659 # In schema version 2, we added indexes and primary keys
660 # pylint: disable=protected-access
661 table = self._query(self._db, 'users', OPTIONS_TABLE,
663 self._db.add_constraint(table.primary_key)
664 for index in table.indexes:
665 self._db.add_index(index)
668 raise NotImplementedError()
671 class TranStore(Store):
673 def __init__(self, path=None):
674 super(TranStore, self).__init__('transactions.db')
676 def _initialize_schema(self):
677 q = self._query(self._db, 'transactions', UNIQUE_DATA_TABLE,
681 def _upgrade_schema(self, old_version):
683 # In schema version 2, we added indexes and primary keys
684 # pylint: disable=protected-access
685 table = self._query(self._db, 'transactions', UNIQUE_DATA_TABLE,
687 self._db.add_constraint(table.primary_key)
688 for index in table.indexes:
689 self._db.add_index(index)
692 raise NotImplementedError()
695 class SAML2SessionStore(Store):
697 def __init__(self, database_url):
698 super(SAML2SessionStore, self).__init__(database_url=database_url)
699 self.table = 'saml2_sessions'
700 # pylint: disable=protected-access
701 table = SqlQuery(self._db, self.table, UNIQUE_DATA_TABLE)._table
702 table.create(checkfirst=True)
704 def _get_unique_id_from_column(self, name, value):
706 The query is going to return only the column in the query.
707 Use this method to get the uuidval which can be used to fetch
710 Returns None or the uuid of the first value found.
712 data = self.get_unique_data(self.table, name=name, value=value)
717 raise ValueError("Multiple entries returned")
718 return data.keys()[0]
720 def remove_expired_sessions(self):
721 # pylint: disable=protected-access
722 table = SqlQuery(self._db, self.table, UNIQUE_DATA_TABLE)._table
723 sel = select([table.columns.uuid]). \
724 where(and_(table.c.name == 'expiration_time',
725 table.c.value <= datetime.datetime.now()))
726 # pylint: disable=no-value-for-parameter
727 d = table.delete().where(table.c.uuid.in_(sel))
730 def get_data(self, idval=None, name=None, value=None):
731 return self.get_unique_data(self.table, idval, name, value)
733 def new_session(self, datum):
734 if 'supported_logout_mechs' in datum:
735 datum['supported_logout_mechs'] = ','.join(
736 datum['supported_logout_mechs']
738 return self.new_unique_data(self.table, datum)
740 def get_session(self, session_id=None, request_id=None):
742 uuidval = self._get_unique_id_from_column('session_id', session_id)
744 uuidval = self._get_unique_id_from_column('request_id', request_id)
746 raise ValueError("Unable to find session")
749 data = self.get_unique_data(self.table, uuidval=uuidval)
750 return uuidval, data[uuidval]
752 def get_user_sessions(self, user):
754 Return a list of all sessions for a given user.
756 rows = self.get_unique_data(self.table, name='user', value=user)
758 # We have a list of sessions for this user, now get the details
761 data = self.get_unique_data(self.table, uuidval=r)
762 data[r]['supported_logout_mechs'] = data[r].get(
763 'supported_logout_mechs', '').split(',')
764 logged_in.append(data)
768 def update_session(self, datum):
769 self.save_unique_data(self.table, datum)
771 def remove_session(self, uuidval):
772 self.del_unique_data(self.table, uuidval)
775 self._reset_data(self.table)
777 def _initialize_schema(self):
778 q = self._query(self._db, self.table, UNIQUE_DATA_TABLE,
782 def _upgrade_schema(self, old_version):
784 # In schema version 2, we added indexes and primary keys
785 # pylint: disable=protected-access
786 table = self._query(self._db, self.table, UNIQUE_DATA_TABLE,
788 self._db.add_constraint(table.primary_key)
789 for index in table.indexes:
790 self._db.add_index(index)
793 raise NotImplementedError()