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')