+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
+
+
+CURRENT_SCHEMA_VERSION = 1
+OPTIONS_COLUMNS = ['name', 'option', 'value']
+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):
+ 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 _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