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 OPTIONS_COLUMNS = ['name', 'option', 'value']
16 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
23 def get_connection(cls, name):
24 if name not in cls.__instances.keys():
25 if cherrypy.config.get('db.conn.log', False):
26 logging.debug('SqlStore new: %s', name)
27 cls.__instances[name] = SqlStore(name)
28 return cls.__instances[name]
30 def __init__(self, name):
31 self.db_conn_log = cherrypy.config.get('db.conn.log', False)
32 self.debug('SqlStore init: %s' % name)
35 if '://' not in engine_name:
36 engine_name = 'sqlite:///' + engine_name
37 # This pool size is per configured database. The minimum needed,
38 # determined by binary search, is 23. We're using 25 so we have a bit
39 # more playroom, and then the overflow should make sure things don't
40 # break when we suddenly need more.
41 pool_args = {'poolclass': QueuePool,
44 if engine_name.startswith('sqlite://'):
45 # It's not possible to share connections for SQLite between
46 # threads, so let's use the SingletonThreadPool for them
47 pool_args = {'poolclass': SingletonThreadPool}
48 self._dbengine = create_engine(engine_name, **pool_args)
49 self.is_readonly = False
51 def debug(self, fact):
53 super(SqlStore, self).debug(fact)
59 self.debug('SqlStore connect: %s' % self.name)
60 conn = self._dbengine.connect()
62 def cleanup_connection():
63 self.debug('SqlStore cleanup: %s' % self.name)
65 cherrypy.request.hooks.attach('on_end_request', cleanup_connection)
70 def at(self, *args, **kwargs):
72 return f(self, *args, **kwargs)
78 def __init__(self, db_obj, table, columns, trans=True):
80 self._con = self._db.connection()
81 self._trans = self._con.begin() if trans else None
82 self._table = self._get_table(table, columns)
84 def _get_table(self, name, columns):
85 table = Table(name, MetaData(self._db.engine()))
87 table.append_column(Column(c, Text()))
90 def _where(self, kvfilter):
92 if kvfilter is not None:
94 w = self._table.columns[k] == kvfilter[k]
101 def _columns(self, columns=None):
103 if columns is not None:
106 cols.append(self._table.columns[c])
108 cols = self._table.columns
112 self._trans.rollback()
118 self._table.create(checkfirst=True)
121 self._table.drop(checkfirst=True)
124 def select(self, kvfilter=None, columns=None):
125 return self._con.execute(select(self._columns(columns),
126 self._where(kvfilter)))
129 def insert(self, values):
130 self._con.execute(self._table.insert(values))
133 def update(self, values, kvfilter):
134 self._con.execute(self._table.update(self._where(kvfilter), values))
137 def delete(self, kvfilter):
138 self._con.execute(self._table.delete(self._where(kvfilter)))
141 class FileStore(Log):
143 def __init__(self, name):
144 self._filename = name
145 self.is_readonly = True
146 self._timestamp = None
149 def get_config(self):
151 stat = os.stat(self._filename)
153 self.error("Unable to check config file %s: [%s]" % (
157 timestamp = stat.st_mtime
158 if self._config is None or timestamp > self._timestamp:
159 self._config = ConfigParser.RawConfigParser()
160 self._config.optionxform = str
161 self._config.read(self._filename)
165 class FileQuery(Log):
167 def __init__(self, fstore, table, columns, trans=True):
168 self._fstore = fstore
169 self._config = fstore.get_config()
170 self._section = table
171 if len(columns) > 3 or columns[-1] != 'value':
172 raise ValueError('Unsupported configuration format')
173 self._columns = columns
182 raise NotImplementedError
185 raise NotImplementedError
187 def select(self, kvfilter=None, columns=None):
188 if self._section not in self._config.sections():
191 opts = self._config.options(self._section)
195 if self._columns[0] in kvfilter:
196 prefix = kvfilter[self._columns[0]]
197 prefix_ = prefix + ' '
200 if len(self._columns) == 3 and self._columns[1] in kvfilter:
201 name = kvfilter[self._columns[1]]
204 if self._columns[-1] in kvfilter:
205 value = kvfilter[self._columns[-1]]
209 if len(self._columns) == 3:
211 if prefix and not o.startswith(prefix_):
214 col1, col2 = o.split(' ', 1)
215 if name and col2 != name:
218 col3 = self._config.get(self._section, o)
219 if value and col3 != value:
222 r = [col1, col2, col3]
225 if prefix and o != prefix:
227 r = [o, self._config.get(self._section, o)]
232 s.append(r[self._columns.index(c)])
237 self.debug('SELECT(%s, %s, %s) -> %s' % (self._section,
243 def insert(self, values):
244 raise NotImplementedError
246 def update(self, values, kvfilter):
247 raise NotImplementedError
249 def delete(self, kvfilter):
250 raise NotImplementedError
254 def __init__(self, config_name=None, database_url=None):
255 if config_name is None and database_url is None:
256 raise ValueError('config_name or database_url must be provided')
258 if config_name not in cherrypy.config:
259 raise NameError('Unknown database %s' % config_name)
260 name = cherrypy.config[config_name]
263 if name.startswith('configfile://'):
264 _, filename = name.split('://')
265 self._db = FileStore(filename)
266 self._query = FileQuery
268 self._db = SqlStore.get_connection(name)
269 self._query = SqlQuery
272 def is_readonly(self):
273 return self._db.is_readonly
275 def _row_to_dict_tree(self, data, row):
281 self._row_to_dict_tree(d2, row[1:])
285 if data[name] is list:
286 data[name].append(value)
289 data[name] = [v, value]
293 def _rows_to_dict_tree(self, rows):
296 self._row_to_dict_tree(data, r)
299 def _load_data(self, table, columns, kvfilter=None):
302 q = self._query(self._db, table, columns, trans=False)
303 rows = q.select(kvfilter)
304 except Exception, e: # pylint: disable=broad-except
305 self.error("Failed to load data for table %s: [%s]" % (table, e))
306 return self._rows_to_dict_tree(rows)
308 def load_config(self):
310 columns = ['name', 'value']
311 return self._load_data(table, columns)
313 def load_options(self, table, name=None):
316 kvfilter['name'] = name
317 options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
318 if name and name in options:
322 def save_options(self, table, name, options):
326 q = self._query(self._db, table, OPTIONS_COLUMNS)
327 rows = q.select({'name': name}, ['option', 'value'])
329 curvals[row[0]] = row[1]
333 q.update({'value': options[opt]},
334 {'name': name, 'option': opt})
336 q.insert((name, opt, options[opt]))
339 except Exception, e: # pylint: disable=broad-except
342 self.error("Failed to save options: [%s]" % e)
345 def delete_options(self, table, name, options=None):
346 kvfilter = {'name': name}
349 q = self._query(self._db, table, OPTIONS_COLUMNS)
354 kvfilter['option'] = opt
357 except Exception, e: # pylint: disable=broad-except
360 self.error("Failed to delete from %s: [%s]" % (table, e))
363 def new_unique_data(self, table, data):
364 newid = str(uuid.uuid4())
367 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
369 q.insert((newid, name, data[name]))
371 except Exception, e: # pylint: disable=broad-except
374 self.error("Failed to store %s data: [%s]" % (table, e))
378 def get_unique_data(self, table, uuidval=None, name=None, value=None):
381 kvfilter['uuid'] = uuidval
383 kvfilter['name'] = name
385 kvfilter['value'] = value
386 return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
388 def save_unique_data(self, table, data):
391 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
394 rows = q.select({'uuid': uid}, ['name', 'value'])
401 if datum[name] is None:
402 q.delete({'uuid': uid, 'name': name})
404 q.update({'value': datum[name]},
405 {'uuid': uid, 'name': name})
407 if datum[name] is not None:
408 q.insert((uid, name, datum[name]))
411 except Exception, e: # pylint: disable=broad-except
414 self.error("Failed to store data in %s: [%s]" % (table, e))
417 def del_unique_data(self, table, uuidval):
418 kvfilter = {'uuid': uuidval}
420 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
422 except Exception, e: # pylint: disable=broad-except
423 self.error("Failed to delete data from %s: [%s]" % (table, e))
425 def _reset_data(self, table):
428 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
432 except Exception, e: # pylint: disable=broad-except
435 self.error("Failed to erase all data from %s: [%s]" % (table, e))
438 class AdminStore(Store):
441 super(AdminStore, self).__init__('admin.config.db')
443 def get_data(self, plugin, idval=None, name=None, value=None):
444 return self.get_unique_data(plugin+"_data", idval, name, value)
446 def save_data(self, plugin, data):
447 return self.save_unique_data(plugin+"_data", data)
449 def new_datum(self, plugin, datum):
450 table = plugin+"_data"
451 return self.new_unique_data(table, datum)
453 def del_datum(self, plugin, idval):
454 table = plugin+"_data"
455 return self.del_unique_data(table, idval)
457 def wipe_data(self, plugin):
458 table = plugin+"_data"
459 self._reset_data(table)
462 class UserStore(Store):
464 def __init__(self, path=None):
465 super(UserStore, self).__init__('user.prefs.db')
467 def save_user_preferences(self, user, options):
468 self.save_options('users', user, options)
470 def load_user_preferences(self, user):
471 return self.load_options('users', user)
473 def save_plugin_data(self, plugin, user, options):
474 self.save_options(plugin+"_data", user, options)
476 def load_plugin_data(self, plugin, user):
477 return self.load_options(plugin+"_data", user)
480 class TranStore(Store):
482 def __init__(self, path=None):
483 super(TranStore, self).__init__('transactions.db')