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 if not self.is_readonly:
631 table = plugin_name+'_data'
632 q = self._query(self._db, table, UNIQUE_DATA_TABLE,
637 class UserStore(Store):
639 def __init__(self, path=None):
640 super(UserStore, self).__init__('user.prefs.db')
642 def save_user_preferences(self, user, options):
643 self.save_options('users', user, options)
645 def load_user_preferences(self, user):
646 return self.load_options('users', user)
648 def save_plugin_data(self, plugin, user, options):
649 self.save_options(plugin+"_data", user, options)
651 def load_plugin_data(self, plugin, user):
652 return self.load_options(plugin+"_data", user)
654 def _initialize_schema(self):
655 q = self._query(self._db, 'users', OPTIONS_TABLE, trans=False)
658 def _upgrade_schema(self, old_version):
660 # In schema version 2, we added indexes and primary keys
661 # pylint: disable=protected-access
662 table = self._query(self._db, 'users', OPTIONS_TABLE,
664 self._db.add_constraint(table.primary_key)
665 for index in table.indexes:
666 self._db.add_index(index)
669 raise NotImplementedError()
672 class TranStore(Store):
674 def __init__(self, path=None):
675 super(TranStore, self).__init__('transactions.db')
677 def _initialize_schema(self):
678 q = self._query(self._db, 'transactions', UNIQUE_DATA_TABLE,
682 def _upgrade_schema(self, old_version):
684 # In schema version 2, we added indexes and primary keys
685 # pylint: disable=protected-access
686 table = self._query(self._db, 'transactions', UNIQUE_DATA_TABLE,
688 self._db.add_constraint(table.primary_key)
689 for index in table.indexes:
690 self._db.add_index(index)
693 raise NotImplementedError()
696 class SAML2SessionStore(Store):
698 def __init__(self, database_url):
699 super(SAML2SessionStore, self).__init__(database_url=database_url)
700 self.table = 'saml2_sessions'
701 # pylint: disable=protected-access
702 table = SqlQuery(self._db, self.table, UNIQUE_DATA_TABLE)._table
703 table.create(checkfirst=True)
705 def _get_unique_id_from_column(self, name, value):
707 The query is going to return only the column in the query.
708 Use this method to get the uuidval which can be used to fetch
711 Returns None or the uuid of the first value found.
713 data = self.get_unique_data(self.table, name=name, value=value)
718 raise ValueError("Multiple entries returned")
719 return data.keys()[0]
721 def remove_expired_sessions(self):
722 # pylint: disable=protected-access
723 table = SqlQuery(self._db, self.table, UNIQUE_DATA_TABLE)._table
724 sel = select([table.columns.uuid]). \
725 where(and_(table.c.name == 'expiration_time',
726 table.c.value <= datetime.datetime.now()))
727 # pylint: disable=no-value-for-parameter
728 d = table.delete().where(table.c.uuid.in_(sel))
731 def get_data(self, idval=None, name=None, value=None):
732 return self.get_unique_data(self.table, idval, name, value)
734 def new_session(self, datum):
735 if 'supported_logout_mechs' in datum:
736 datum['supported_logout_mechs'] = ','.join(
737 datum['supported_logout_mechs']
739 return self.new_unique_data(self.table, datum)
741 def get_session(self, session_id=None, request_id=None):
743 uuidval = self._get_unique_id_from_column('session_id', session_id)
745 uuidval = self._get_unique_id_from_column('request_id', request_id)
747 raise ValueError("Unable to find session")
750 data = self.get_unique_data(self.table, uuidval=uuidval)
751 return uuidval, data[uuidval]
753 def get_user_sessions(self, user):
755 Return a list of all sessions for a given user.
757 rows = self.get_unique_data(self.table, name='user', value=user)
759 # We have a list of sessions for this user, now get the details
762 data = self.get_unique_data(self.table, uuidval=r)
763 data[r]['supported_logout_mechs'] = data[r].get(
764 'supported_logout_mechs', '').split(',')
765 logged_in.append(data)
769 def update_session(self, datum):
770 self.save_unique_data(self.table, datum)
772 def remove_session(self, uuidval):
773 self.del_unique_data(self.table, uuidval)
776 self._reset_data(self.table)
778 def _initialize_schema(self):
779 q = self._query(self._db, self.table, UNIQUE_DATA_TABLE,
783 def _upgrade_schema(self, old_version):
785 # In schema version 2, we added indexes and primary keys
786 # pylint: disable=protected-access
787 table = self._query(self._db, self.table, UNIQUE_DATA_TABLE,
789 self._db.add_constraint(table.primary_key)
790 for index in table.indexes:
791 self._db.add_index(index)
794 raise NotImplementedError()