X-Git-Url: http://git.cascardo.info/?p=cascardo%2Fipsilon.git;a=blobdiff_plain;f=ipsilon%2Futil%2Fdata.py;h=26fa8599a8655c52ff2a35744b1aff3ff775b9ac;hp=5a144a4086b372897da927a311d5c6a068e77b91;hb=abcefb0f2eece549371f951b58144188d2ac9307;hpb=e0895efb26de64a28de7b9219f524b715c396b2b diff --git a/ipsilon/util/data.py b/ipsilon/util/data.py old mode 100755 new mode 100644 index 5a144a4..26fa859 --- a/ipsilon/util/data.py +++ b/ipsilon/util/data.py @@ -1,5 +1,3 @@ -#!/usr/bin/python -# # Copyright (C) 2013 Simo Sorce # # see file 'COPYING' for use and warranty information @@ -17,87 +15,277 @@ # You should have received a copy of the GNU General Public License # along with this program. If not, see . -import os -import sqlite3 import cherrypy from ipsilon.util.log import Log +from sqlalchemy import create_engine +from sqlalchemy import MetaData, Table, Column, Text +from sqlalchemy.pool import QueuePool, SingletonThreadPool +from sqlalchemy.sql import select +import ConfigParser +import os import uuid +import logging OPTIONS_COLUMNS = ['name', 'option', 'value'] UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value'] -class Store(Log): +class SqlStore(Log): + __instances = {} + + @classmethod + def get_connection(cls, name): + if name not in cls.__instances.keys(): + if cherrypy.config.get('db.conn.log', False): + logging.debug('SqlStore new: %s', name) + cls.__instances[name] = SqlStore(name) + return cls.__instances[name] def __init__(self, name): - self._dbname = name - - def _build_where(self, kvfilter, kvout): - where = "" - sep = "WHERE" - for k in kvfilter: - mk = "where_%s" % k - kvout[mk] = kvfilter[k] - where += "%s %s=:%s" % (sep, k, mk) - sep = " AND" + self.db_conn_log = cherrypy.config.get('db.conn.log', False) + self.debug('SqlStore init: %s' % name) + self.name = name + engine_name = name + if '://' not in engine_name: + engine_name = 'sqlite:///' + engine_name + # This pool size is per configured database. The minimum needed, + # determined by binary search, is 23. We're using 25 so we have a bit + # more playroom, and then the overflow should make sure things don't + # break when we suddenly need more. + pool_args = {'poolclass': QueuePool, + 'pool_size': 25, + 'max_overflow': 50} + if engine_name.startswith('sqlite://'): + # It's not possible to share connections for SQLite between + # threads, so let's use the SingletonThreadPool for them + pool_args = {'poolclass': SingletonThreadPool} + self._dbengine = create_engine(engine_name, **pool_args) + self.is_readonly = False + + def debug(self, fact): + if self.db_conn_log: + super(SqlStore, self).debug(fact) + + def engine(self): + return self._dbengine + + def connection(self): + self.debug('SqlStore connect: %s' % self.name) + conn = self._dbengine.connect() + + def cleanup_connection(): + self.debug('SqlStore cleanup: %s' % self.name) + conn.close() + cherrypy.request.hooks.attach('on_end_request', cleanup_connection) + return conn + + +def SqlAutotable(f): + def at(self, *args, **kwargs): + self.create() + return f(self, *args, **kwargs) + return at + + +class SqlQuery(Log): + + def __init__(self, db_obj, table, columns, trans=True): + self._db = db_obj + self._con = self._db.connection() + self._trans = self._con.begin() if trans else None + self._table = self._get_table(table, columns) + + def _get_table(self, name, columns): + table = Table(name, MetaData(self._db.engine())) + for c in columns: + table.append_column(Column(c, Text())) + return table + + def _where(self, kvfilter): + where = None + if kvfilter is not None: + for k in kvfilter: + w = self._table.columns[k] == kvfilter[k] + if where is None: + where = w + else: + where = where & w return where - def _build_select(self, table, kvfilter=None, kvout=None, columns=None): - SELECT = "SELECT %(cols)s FROM %(table)s %(where)s" - cols = "*" - if columns: - cols = ",".join(columns) - where = "" - if kvfilter is not None: - where = self._build_where(kvfilter, kvout) - return SELECT % {'table': table, 'cols': cols, 'where': where} - - def _select(self, cursor, table, kvfilter=None, columns=None): - kv = dict() - select = self._build_select(table, kvfilter, kv, columns) - cursor.execute(select, kv) - return cursor.fetchall() - - def _create(self, cursor, table, columns): - CREATE = "CREATE TABLE IF NOT EXISTS %(table)s(%(cols)s)" - cols = ",".join(columns) - create = CREATE % {'table': table, 'cols': cols} - cursor.execute(create) - - def _update(self, cursor, table, values, kvfilter): - UPDATE = "UPDATE %(table)s SET %(setval)s %(where)s" - kv = dict() - - setval = "" - sep = "" - for k in values: - mk = "setval_%s" % k - kv[mk] = values[k] - setval += "%s%s=:%s" % (sep, k, mk) - sep = " , " - - where = self._build_where(kvfilter, kv) - - update = UPDATE % {'table': table, 'setval': setval, 'where': where} - cursor.execute(update, kv) - - def _insert(self, cursor, table, values): - INSERT = "INSERT INTO %(table)s VALUES(%(values)s)" - vals = "" - sep = "" - for _ in values: - vals += "%s?" % sep - sep = "," - insert = INSERT % {'table': table, 'values': vals} - cursor.execute(insert, values) - - def _delete(self, cursor, table, kvfilter): - DELETE = "DELETE FROM %(table)s %(where)s" - kv = dict() - where = self._build_where(kvfilter, kv) - delete = DELETE % {'table': table, 'where': where} - cursor.execute(delete, kv) + def _columns(self, columns=None): + cols = None + if columns is not None: + cols = [] + for c in columns: + cols.append(self._table.columns[c]) + else: + cols = self._table.columns + return cols + + def rollback(self): + self._trans.rollback() + + def commit(self): + self._trans.commit() + + def create(self): + self._table.create(checkfirst=True) + + def drop(self): + self._table.drop(checkfirst=True) + + @SqlAutotable + def select(self, kvfilter=None, columns=None): + return self._con.execute(select(self._columns(columns), + self._where(kvfilter))) + + @SqlAutotable + def insert(self, values): + self._con.execute(self._table.insert(values)) + + @SqlAutotable + def update(self, values, kvfilter): + self._con.execute(self._table.update(self._where(kvfilter), values)) + + @SqlAutotable + def delete(self, kvfilter): + self._con.execute(self._table.delete(self._where(kvfilter))) + + +class FileStore(Log): + + def __init__(self, name): + self._filename = name + self.is_readonly = True + self._timestamp = None + self._config = None + + def get_config(self): + try: + stat = os.stat(self._filename) + except OSError, e: + self.error("Unable to check config file %s: [%s]" % ( + self._filename, e)) + self._config = None + raise + timestamp = stat.st_mtime + if self._config is None or timestamp > self._timestamp: + self._config = ConfigParser.RawConfigParser() + self._config.optionxform = str + self._config.read(self._filename) + return self._config + + +class FileQuery(Log): + + def __init__(self, fstore, table, columns, trans=True): + self._fstore = fstore + self._config = fstore.get_config() + self._section = table + if len(columns) > 3 or columns[-1] != 'value': + raise ValueError('Unsupported configuration format') + self._columns = columns + + def rollback(self): + return + + def commit(self): + return + + def create(self): + raise NotImplementedError + + def drop(self): + raise NotImplementedError + + def select(self, kvfilter=None, columns=None): + if self._section not in self._config.sections(): + return [] + + opts = self._config.options(self._section) + + prefix = None + prefix_ = '' + if self._columns[0] in kvfilter: + prefix = kvfilter[self._columns[0]] + prefix_ = prefix + ' ' + + name = None + if len(self._columns) == 3 and self._columns[1] in kvfilter: + name = kvfilter[self._columns[1]] + + value = None + if self._columns[-1] in kvfilter: + value = kvfilter[self._columns[-1]] + + res = [] + for o in opts: + if len(self._columns) == 3: + # 3 cols + if prefix and not o.startswith(prefix_): + continue + + col1, col2 = o.split(' ', 1) + if name and col2 != name: + continue + + col3 = self._config.get(self._section, o) + if value and col3 != value: + continue + + r = [col1, col2, col3] + else: + # 2 cols + if prefix and o != prefix: + continue + r = [o, self._config.get(self._section, o)] + + if columns: + s = [] + for c in columns: + s.append(r[self._columns.index(c)]) + res.append(s) + else: + res.append(r) + + self.debug('SELECT(%s, %s, %s) -> %s' % (self._section, + repr(kvfilter), + repr(columns), + repr(res))) + return res + + def insert(self, values): + raise NotImplementedError + + def update(self, values, kvfilter): + raise NotImplementedError + + def delete(self, kvfilter): + raise NotImplementedError + + +class Store(Log): + def __init__(self, config_name=None, database_url=None): + if config_name is None and database_url is None: + raise ValueError('config_name or database_url must be provided') + if config_name: + if config_name not in cherrypy.config: + raise NameError('Unknown database %s' % config_name) + name = cherrypy.config[config_name] + else: + name = database_url + if name.startswith('configfile://'): + _, filename = name.split('://') + self._db = FileStore(filename) + self._query = FileQuery + else: + self._db = SqlStore.get_connection(name) + self._query = SqlQuery + + @property + def is_readonly(self): + return self._db.is_readonly def _row_to_dict_tree(self, data, row): name = row[0] @@ -124,22 +312,12 @@ class Store(Log): return data def _load_data(self, table, columns, kvfilter=None): - con = None rows = [] try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._create(cur, table, columns) - rows = self._select(cur, table, kvfilter) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() + q = self._query(self._db, table, columns, trans=False) + rows = q.select(kvfilter) + except Exception, e: # pylint: disable=broad-except self.error("Failed to load data for table %s: [%s]" % (table, e)) - finally: - if con: - con.close() - return self._rows_to_dict_tree(rows) def load_config(self): @@ -158,74 +336,58 @@ class Store(Log): def save_options(self, table, name, options): curvals = dict() - con = None + q = None try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._create(cur, table, OPTIONS_COLUMNS) - rows = self._select(cur, table, {'name': name}, - ['option', 'value']) + q = self._query(self._db, table, OPTIONS_COLUMNS) + rows = q.select({'name': name}, ['option', 'value']) for row in rows: curvals[row[0]] = row[1] for opt in options: if opt in curvals: - self._update(cur, table, - {'value': options[opt]}, - {'name': name, 'option': opt}) + q.update({'value': options[opt]}, + {'name': name, 'option': opt}) else: - self._insert(cur, table, (name, opt, options[opt])) + q.insert((name, opt, options[opt])) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() - self.error("Failed to store config: [%s]" % e) + q.commit() + except Exception, e: # pylint: disable=broad-except + if q: + q.rollback() + self.error("Failed to save options: [%s]" % e) raise - finally: - if con: - con.close() def delete_options(self, table, name, options=None): kvfilter = {'name': name} + q = None try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._create(cur, table, OPTIONS_COLUMNS) + q = self._query(self._db, table, OPTIONS_COLUMNS) if options is None: - self._delete(cur, table, kvfilter) + q.delete(kvfilter) else: for opt in options: kvfilter['option'] = opt - self._delete(cur, table, kvfilter) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() + q.delete(kvfilter) + q.commit() + except Exception, e: # pylint: disable=broad-except + if q: + q.rollback() self.error("Failed to delete from %s: [%s]" % (table, e)) raise - finally: - if con: - con.close() def new_unique_data(self, table, data): - con = None + newid = str(uuid.uuid4()) + q = None try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._create(cur, table, UNIQUE_DATA_COLUMNS) - newid = str(uuid.uuid4()) + q = self._query(self._db, table, UNIQUE_DATA_COLUMNS) for name in data: - self._insert(cur, table, (newid, name, data[name])) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() - cherrypy.log.error("Failed to store %s data: [%s]" % (table, e)) + q.insert((newid, name, data[name])) + q.commit() + except Exception, e: # pylint: disable=broad-except + if q: + q.rollback() + self.error("Failed to store %s data: [%s]" % (table, e)) raise - finally: - if con: - con.close() return newid def get_unique_data(self, table, uuidval=None, name=None, value=None): @@ -239,65 +401,59 @@ class Store(Log): return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter) def save_unique_data(self, table, data): - curvals = dict() - con = None + q = None try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._create(cur, table, UNIQUE_DATA_COLUMNS) + q = self._query(self._db, table, UNIQUE_DATA_COLUMNS) for uid in data: curvals = dict() - rows = self._select(cur, table, {'uuid': uid}, - ['name', 'value']) + rows = q.select({'uuid': uid}, ['name', 'value']) for r in rows: curvals[r[0]] = r[1] datum = data[uid] for name in datum: if name in curvals: - self._update(cur, table, - {'value': datum[name]}, + if datum[name] is None: + q.delete({'uuid': uid, 'name': name}) + else: + q.update({'value': datum[name]}, {'uuid': uid, 'name': name}) else: - self._insert(cur, table, (uid, name, datum[name])) + if datum[name] is not None: + q.insert((uid, name, datum[name])) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() + q.commit() + except Exception, e: # pylint: disable=broad-except + if q: + q.rollback() self.error("Failed to store data in %s: [%s]" % (table, e)) raise - finally: - if con: - con.close() def del_unique_data(self, table, uuidval): kvfilter = {'uuid': uuidval} - con = None try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - self._delete(cur, table, kvfilter) - except sqlite3.Error, e: + q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False) + q.delete(kvfilter) + except Exception, e: # pylint: disable=broad-except self.error("Failed to delete data from %s: [%s]" % (table, e)) - finally: - if con: - con.close() + + def _reset_data(self, table): + q = None + try: + q = self._query(self._db, table, UNIQUE_DATA_COLUMNS) + q.drop() + q.create() + q.commit() + except Exception, e: # pylint: disable=broad-except + if q: + q.rollback() + self.error("Failed to erase all data from %s: [%s]" % (table, e)) class AdminStore(Store): - def __init__(self, path=None): - if path is None: - self._path = os.getcwd() - else: - self._path = path - self._name = None - if 'admin.config.db' in cherrypy.config: - self._name = cherrypy.config['admin.config.db'] - if not self._name: - self._name = os.path.join(self._path, 'adminconfig.sqlite') - super(AdminStore, self).__init__(self._name) + def __init__(self): + super(AdminStore, self).__init__('admin.config.db') def get_data(self, plugin, idval=None, name=None, value=None): return self.get_unique_data(plugin+"_data", idval, name, value) @@ -315,51 +471,28 @@ class AdminStore(Store): def wipe_data(self, plugin): table = plugin+"_data" - # Try to backup old data first, just in case - try: - con = sqlite3.connect(self._dbname) - cur = con.cursor() - cur.execute("DROP TABLE IF EXISTS " + table) - self._create(cur, table, UNIQUE_DATA_COLUMNS) - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() - cherrypy.log.error("Failed to wipe %s data: [%s]" % (plugin, e)) - raise - finally: - if con: - con.close() + self._reset_data(table) class UserStore(Store): def __init__(self, path=None): - if path is None: - self._path = os.getcwd() - else: - self._path = path - self._name = None - if 'user.prefs.db' in cherrypy.config: - self._name = cherrypy.config['user.prefs.db'] - if not self._name: - self._name = os.path.join(self._path, 'userprefs.sqlite') - super(UserStore, self).__init__(self._name) + super(UserStore, self).__init__('user.prefs.db') def save_user_preferences(self, user, options): - return self.save_options('users', user, options) + self.save_options('users', user, options) + + def load_user_preferences(self, user): + return self.load_options('users', user) + + def save_plugin_data(self, plugin, user, options): + self.save_options(plugin+"_data", user, options) + + def load_plugin_data(self, plugin, user): + return self.load_options(plugin+"_data", user) class TranStore(Store): def __init__(self, path=None): - if path is None: - self._path = os.getcwd() - else: - self._path = path - self._name = None - if 'transactions.db' in cherrypy.config: - self._name = cherrypy.config['transactions.db'] - if not self._name: - self._name = os.path.join(self._path, 'transactions.sqlite') - super(TranStore, self).__init__(self._name) + super(TranStore, self).__init__('transactions.db')