X-Git-Url: http://git.cascardo.info/?p=cascardo%2Fipsilon.git;a=blobdiff_plain;f=ipsilon%2Futil%2Fdata.py;h=26fa8599a8655c52ff2a35744b1aff3ff775b9ac;hp=2af2bdc0c7d96094956b7009580f111e3a810c58;hb=abcefb0f2eece549371f951b58144188d2ac9307;hpb=4c56c3f3801fcfbf463906cff3c8dbbb0e23ed87 diff --git a/ipsilon/util/data.py b/ipsilon/util/data.py old mode 100755 new mode 100644 index 2af2bdc..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 @@ -21,8 +19,12 @@ 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'] @@ -30,35 +32,66 @@ UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value'] 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): - if name not in cherrypy.config: - raise NameError('Unknown database %s' % name) - engine_name = cherrypy.config[name] + 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 - self._dbengine = create_engine(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): - return self._dbengine.connect() + 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): - if self.autotable: - self.create() + self.create() return f(self, *args, **kwargs) return at class SqlQuery(Log): - def __init__(self, db_obj, table, columns, autotable=True, trans=True): + def __init__(self, db_obj, table, columns, trans=True): self._db = db_obj - self.autotable = autotable self._con = self._db.connection() self._trans = self._con.begin() if trans else None self._table = self._get_table(table, columns) @@ -120,11 +153,139 @@ class SqlQuery(Log): 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 - def __init__(self, config_name): - self._db = SqlStore(config_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] @@ -252,10 +413,14 @@ class Store(Log): datum = data[uid] for name in datum: if name in curvals: - q.update({'value': datum[name]}, - {'uuid': uid, 'name': name}) + if datum[name] is None: + q.delete({'uuid': uid, 'name': name}) + else: + q.update({'value': datum[name]}, + {'uuid': uid, 'name': name}) else: - q.insert((uid, name, datum[name])) + if datum[name] is not None: + q.insert((uid, name, datum[name])) q.commit() except Exception, e: # pylint: disable=broad-except @@ -273,6 +438,7 @@ class Store(Log): self.error("Failed to delete data from %s: [%s]" % (table, e)) def _reset_data(self, table): + q = None try: q = self._query(self._db, table, UNIQUE_DATA_COLUMNS) q.drop()