1 # Copyright (C) 2013 Simo Sorce <simo@redhat.com>
3 # see file 'COPYING' for use and warranty information
5 # This program is free software; you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by
7 # the Free Software Foundation, either version 3 of the License, or
8 # (at your option) any later version.
10 # This program is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program. If not, see <http://www.gnu.org/licenses/>.
19 from ipsilon.util.log import Log
20 from sqlalchemy import create_engine
21 from sqlalchemy import MetaData, Table, Column, Text
22 from sqlalchemy.pool import QueuePool, SingletonThreadPool
23 from sqlalchemy.sql import select
30 OPTIONS_COLUMNS = ['name', 'option', 'value']
31 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
38 def get_connection(cls, name):
39 if name not in cls.__instances.keys():
40 if cherrypy.config.get('db.conn.log', False):
41 logging.debug('SqlStore new: %s', name)
42 cls.__instances[name] = SqlStore(name)
43 return cls.__instances[name]
45 def __init__(self, name):
46 self.db_conn_log = cherrypy.config.get('db.conn.log', False)
47 self.debug('SqlStore init: %s' % name)
50 if '://' not in engine_name:
51 engine_name = 'sqlite:///' + engine_name
52 # This pool size is per configured database. The minimum needed,
53 # determined by binary search, is 23. We're using 25 so we have a bit
54 # more playroom, and then the overflow should make sure things don't
55 # break when we suddenly need more.
56 pool_args = {'poolclass': QueuePool,
59 if engine_name.startswith('sqlite://'):
60 # It's not possible to share connections for SQLite between
61 # threads, so let's use the SingletonThreadPool for them
62 pool_args = {'poolclass': SingletonThreadPool}
63 self._dbengine = create_engine(engine_name, **pool_args)
64 self.is_readonly = False
66 def debug(self, fact):
68 super(SqlStore, self).debug(fact)
74 self.debug('SqlStore connect: %s' % self.name)
75 conn = self._dbengine.connect()
77 def cleanup_connection():
78 self.debug('SqlStore cleanup: %s' % self.name)
80 cherrypy.request.hooks.attach('on_end_request', cleanup_connection)
85 def at(self, *args, **kwargs):
87 return f(self, *args, **kwargs)
93 def __init__(self, db_obj, table, columns, trans=True):
95 self._con = self._db.connection()
96 self._trans = self._con.begin() if trans else None
97 self._table = self._get_table(table, columns)
99 def _get_table(self, name, columns):
100 table = Table(name, MetaData(self._db.engine()))
102 table.append_column(Column(c, Text()))
105 def _where(self, kvfilter):
107 if kvfilter is not None:
109 w = self._table.columns[k] == kvfilter[k]
116 def _columns(self, columns=None):
118 if columns is not None:
121 cols.append(self._table.columns[c])
123 cols = self._table.columns
127 self._trans.rollback()
133 self._table.create(checkfirst=True)
136 self._table.drop(checkfirst=True)
139 def select(self, kvfilter=None, columns=None):
140 return self._con.execute(select(self._columns(columns),
141 self._where(kvfilter)))
144 def insert(self, values):
145 self._con.execute(self._table.insert(values))
148 def update(self, values, kvfilter):
149 self._con.execute(self._table.update(self._where(kvfilter), values))
152 def delete(self, kvfilter):
153 self._con.execute(self._table.delete(self._where(kvfilter)))
156 class FileStore(Log):
158 def __init__(self, name):
159 self._filename = name
160 self.is_readonly = True
161 self._timestamp = None
164 def get_config(self):
166 stat = os.stat(self._filename)
168 self.error("Unable to check config file %s: [%s]" % (
172 timestamp = stat.st_mtime
173 if self._config is None or timestamp > self._timestamp:
174 self._config = ConfigParser.RawConfigParser()
175 self._config.optionxform = str
176 self._config.read(self._filename)
180 class FileQuery(Log):
182 def __init__(self, fstore, table, columns, trans=True):
183 self._fstore = fstore
184 self._config = fstore.get_config()
185 self._section = table
186 if len(columns) > 3 or columns[-1] != 'value':
187 raise ValueError('Unsupported configuration format')
188 self._columns = columns
197 raise NotImplementedError
200 raise NotImplementedError
202 def select(self, kvfilter=None, columns=None):
203 if self._section not in self._config.sections():
206 opts = self._config.options(self._section)
210 if self._columns[0] in kvfilter:
211 prefix = kvfilter[self._columns[0]]
212 prefix_ = prefix + ' '
215 if len(self._columns) == 3 and self._columns[1] in kvfilter:
216 name = kvfilter[self._columns[1]]
219 if self._columns[-1] in kvfilter:
220 value = kvfilter[self._columns[-1]]
224 if len(self._columns) == 3:
226 if prefix and not o.startswith(prefix_):
229 col1, col2 = o.split(' ', 1)
230 if name and col2 != name:
233 col3 = self._config.get(self._section, o)
234 if value and col3 != value:
237 r = [col1, col2, col3]
240 if prefix and o != prefix:
242 r = [o, self._config.get(self._section, o)]
247 s.append(r[self._columns.index(c)])
252 self.debug('SELECT(%s, %s, %s) -> %s' % (self._section,
258 def insert(self, values):
259 raise NotImplementedError
261 def update(self, values, kvfilter):
262 raise NotImplementedError
264 def delete(self, kvfilter):
265 raise NotImplementedError
269 def __init__(self, config_name=None, database_url=None):
270 if config_name is None and database_url is None:
271 raise ValueError('config_name or database_url must be provided')
273 if config_name not in cherrypy.config:
274 raise NameError('Unknown database %s' % config_name)
275 name = cherrypy.config[config_name]
278 if name.startswith('configfile://'):
279 _, filename = name.split('://')
280 self._db = FileStore(filename)
281 self._query = FileQuery
283 self._db = SqlStore.get_connection(name)
284 self._query = SqlQuery
287 def is_readonly(self):
288 return self._db.is_readonly
290 def _row_to_dict_tree(self, data, row):
296 self._row_to_dict_tree(d2, row[1:])
300 if data[name] is list:
301 data[name].append(value)
304 data[name] = [v, value]
308 def _rows_to_dict_tree(self, rows):
311 self._row_to_dict_tree(data, r)
314 def _load_data(self, table, columns, kvfilter=None):
317 q = self._query(self._db, table, columns, trans=False)
318 rows = q.select(kvfilter)
319 except Exception, e: # pylint: disable=broad-except
320 self.error("Failed to load data for table %s: [%s]" % (table, e))
321 return self._rows_to_dict_tree(rows)
323 def load_config(self):
325 columns = ['name', 'value']
326 return self._load_data(table, columns)
328 def load_options(self, table, name=None):
331 kvfilter['name'] = name
332 options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
333 if name and name in options:
337 def save_options(self, table, name, options):
341 q = self._query(self._db, table, OPTIONS_COLUMNS)
342 rows = q.select({'name': name}, ['option', 'value'])
344 curvals[row[0]] = row[1]
348 q.update({'value': options[opt]},
349 {'name': name, 'option': opt})
351 q.insert((name, opt, options[opt]))
354 except Exception, e: # pylint: disable=broad-except
357 self.error("Failed to save options: [%s]" % e)
360 def delete_options(self, table, name, options=None):
361 kvfilter = {'name': name}
364 q = self._query(self._db, table, OPTIONS_COLUMNS)
369 kvfilter['option'] = opt
372 except Exception, e: # pylint: disable=broad-except
375 self.error("Failed to delete from %s: [%s]" % (table, e))
378 def new_unique_data(self, table, data):
379 newid = str(uuid.uuid4())
382 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
384 q.insert((newid, name, data[name]))
386 except Exception, e: # pylint: disable=broad-except
389 self.error("Failed to store %s data: [%s]" % (table, e))
393 def get_unique_data(self, table, uuidval=None, name=None, value=None):
396 kvfilter['uuid'] = uuidval
398 kvfilter['name'] = name
400 kvfilter['value'] = value
401 return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
403 def save_unique_data(self, table, data):
406 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
409 rows = q.select({'uuid': uid}, ['name', 'value'])
416 if datum[name] is None:
417 q.delete({'uuid': uid, 'name': name})
419 q.update({'value': datum[name]},
420 {'uuid': uid, 'name': name})
422 if datum[name] is not None:
423 q.insert((uid, name, datum[name]))
426 except Exception, e: # pylint: disable=broad-except
429 self.error("Failed to store data in %s: [%s]" % (table, e))
432 def del_unique_data(self, table, uuidval):
433 kvfilter = {'uuid': uuidval}
435 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
437 except Exception, e: # pylint: disable=broad-except
438 self.error("Failed to delete data from %s: [%s]" % (table, e))
440 def _reset_data(self, table):
443 q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
447 except Exception, e: # pylint: disable=broad-except
450 self.error("Failed to erase all data from %s: [%s]" % (table, e))
453 class AdminStore(Store):
456 super(AdminStore, self).__init__('admin.config.db')
458 def get_data(self, plugin, idval=None, name=None, value=None):
459 return self.get_unique_data(plugin+"_data", idval, name, value)
461 def save_data(self, plugin, data):
462 return self.save_unique_data(plugin+"_data", data)
464 def new_datum(self, plugin, datum):
465 table = plugin+"_data"
466 return self.new_unique_data(table, datum)
468 def del_datum(self, plugin, idval):
469 table = plugin+"_data"
470 return self.del_unique_data(table, idval)
472 def wipe_data(self, plugin):
473 table = plugin+"_data"
474 self._reset_data(table)
477 class UserStore(Store):
479 def __init__(self, path=None):
480 super(UserStore, self).__init__('user.prefs.db')
482 def save_user_preferences(self, user, options):
483 self.save_options('users', user, options)
485 def load_user_preferences(self, user):
486 return self.load_options('users', user)
488 def save_plugin_data(self, plugin, user, options):
489 self.save_options(plugin+"_data", user, options)
491 def load_plugin_data(self, plugin, user):
492 return self.load_options(plugin+"_data", user)
495 class TranStore(Store):
497 def __init__(self, path=None):
498 super(TranStore, self).__init__('transactions.db')