ipsilon/util/data.py

   1 # Copyright (C) 2013 Ipsilon project Contributors, for license see COPYING
   2
   3 import cherrypy
   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
   9 import ConfigParser
  10 import os
  11 import uuid
  12 import logging
  13
  14
  15 OPTIONS_COLUMNS = ['name', 'option', 'value']
  16 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
  17
  18
  19 class SqlStore(Log):
  20     __instances = {}
  21
  22     @classmethod
  23     def get_connection(cls, name):
  24         if name not in cls.__instances.keys():
  25             if cherrypy.config.get('db.conn.log', False):
  26                 logging.debug('SqlStore new: %s', name)
  27             cls.__instances[name] = SqlStore(name)
  28         return cls.__instances[name]
  29
  30     def __init__(self, name):
  31         self.db_conn_log = cherrypy.config.get('db.conn.log', False)
  32         self.debug('SqlStore init: %s' % name)
  33         self.name = name
  34         engine_name = name
  35         if '://' not in engine_name:
  36             engine_name = 'sqlite:///' + engine_name
  37         # This pool size is per configured database. The minimum needed,
  38         #  determined by binary search, is 23. We're using 25 so we have a bit
  39         #  more playroom, and then the overflow should make sure things don't
  40         #  break when we suddenly need more.
  41         pool_args = {'poolclass': QueuePool,
  42                      'pool_size': 25,
  43                      'max_overflow': 50}
  44         if engine_name.startswith('sqlite://'):
  45             # It's not possible to share connections for SQLite between
  46             #  threads, so let's use the SingletonThreadPool for them
  47             pool_args = {'poolclass': SingletonThreadPool}
  48         self._dbengine = create_engine(engine_name, **pool_args)
  49         self.is_readonly = False
  50
  51     def debug(self, fact):
  52         if self.db_conn_log:
  53             super(SqlStore, self).debug(fact)
  54
  55     def engine(self):
  56         return self._dbengine
  57
  58     def connection(self):
  59         self.debug('SqlStore connect: %s' % self.name)
  60         conn = self._dbengine.connect()
  61
  62         def cleanup_connection():
  63             self.debug('SqlStore cleanup: %s' % self.name)
  64             conn.close()
  65         cherrypy.request.hooks.attach('on_end_request', cleanup_connection)
  66         return conn
  67
  68
  69 def SqlAutotable(f):
  70     def at(self, *args, **kwargs):
  71         self.create()
  72         return f(self, *args, **kwargs)
  73     return at
  74
  75
  76 class SqlQuery(Log):
  77
  78     def __init__(self, db_obj, table, columns, trans=True):
  79         self._db = db_obj
  80         self._con = self._db.connection()
  81         self._trans = self._con.begin() if trans else None
  82         self._table = self._get_table(table, columns)
  83
  84     def _get_table(self, name, columns):
  85         table = Table(name, MetaData(self._db.engine()))
  86         for c in columns:
  87             table.append_column(Column(c, Text()))
  88         return table
  89
  90     def _where(self, kvfilter):
  91         where = None
  92         if kvfilter is not None:
  93             for k in kvfilter:
  94                 w = self._table.columns[k] == kvfilter[k]
  95                 if where is None:
  96                     where = w
  97                 else:
  98                     where = where & w
  99         return where
 100
 101     def _columns(self, columns=None):
 102         cols = None
 103         if columns is not None:
 104             cols = []
 105             for c in columns:
 106                 cols.append(self._table.columns[c])
 107         else:
 108             cols = self._table.columns
 109         return cols
 110
 111     def rollback(self):
 112         self._trans.rollback()
 113
 114     def commit(self):
 115         self._trans.commit()
 116
 117     def create(self):
 118         self._table.create(checkfirst=True)
 119
 120     def drop(self):
 121         self._table.drop(checkfirst=True)
 122
 123     @SqlAutotable
 124     def select(self, kvfilter=None, columns=None):
 125         return self._con.execute(select(self._columns(columns),
 126                                         self._where(kvfilter)))
 127
 128     @SqlAutotable
 129     def insert(self, values):
 130         self._con.execute(self._table.insert(values))
 131
 132     @SqlAutotable
 133     def update(self, values, kvfilter):
 134         self._con.execute(self._table.update(self._where(kvfilter), values))
 135
 136     @SqlAutotable
 137     def delete(self, kvfilter):
 138         self._con.execute(self._table.delete(self._where(kvfilter)))
 139
 140
 141 class FileStore(Log):
 142
 143     def __init__(self, name):
 144         self._filename = name
 145         self.is_readonly = True
 146         self._timestamp = None
 147         self._config = None
 148
 149     def get_config(self):
 150         try:
 151             stat = os.stat(self._filename)
 152         except OSError, e:
 153             self.error("Unable to check config file %s: [%s]" % (
 154                 self._filename, e))
 155             self._config = None
 156             raise
 157         timestamp = stat.st_mtime
 158         if self._config is None or timestamp > self._timestamp:
 159             self._config = ConfigParser.RawConfigParser()
 160             self._config.optionxform = str
 161             self._config.read(self._filename)
 162         return self._config
 163
 164
 165 class FileQuery(Log):
 166
 167     def __init__(self, fstore, table, columns, trans=True):
 168         self._fstore = fstore
 169         self._config = fstore.get_config()
 170         self._section = table
 171         if len(columns) > 3 or columns[-1] != 'value':
 172             raise ValueError('Unsupported configuration format')
 173         self._columns = columns
 174
 175     def rollback(self):
 176         return
 177
 178     def commit(self):
 179         return
 180
 181     def create(self):
 182         raise NotImplementedError
 183
 184     def drop(self):
 185         raise NotImplementedError
 186
 187     def select(self, kvfilter=None, columns=None):
 188         if self._section not in self._config.sections():
 189             return []
 190
 191         opts = self._config.options(self._section)
 192
 193         prefix = None
 194         prefix_ = ''
 195         if self._columns[0] in kvfilter:
 196             prefix = kvfilter[self._columns[0]]
 197             prefix_ = prefix + ' '
 198
 199         name = None
 200         if len(self._columns) == 3 and self._columns[1] in kvfilter:
 201             name = kvfilter[self._columns[1]]
 202
 203         value = None
 204         if self._columns[-1] in kvfilter:
 205             value = kvfilter[self._columns[-1]]
 206
 207         res = []
 208         for o in opts:
 209             if len(self._columns) == 3:
 210                 # 3 cols
 211                 if prefix and not o.startswith(prefix_):
 212                     continue
 213
 214                 col1, col2 = o.split(' ', 1)
 215                 if name and col2 != name:
 216                     continue
 217
 218                 col3 = self._config.get(self._section, o)
 219                 if value and col3 != value:
 220                     continue
 221
 222                 r = [col1, col2, col3]
 223             else:
 224                 # 2 cols
 225                 if prefix and o != prefix:
 226                     continue
 227                 r = [o, self._config.get(self._section, o)]
 228
 229             if columns:
 230                 s = []
 231                 for c in columns:
 232                     s.append(r[self._columns.index(c)])
 233                 res.append(s)
 234             else:
 235                 res.append(r)
 236
 237         self.debug('SELECT(%s, %s, %s) -> %s' % (self._section,
 238                                                  repr(kvfilter),
 239                                                  repr(columns),
 240                                                  repr(res)))
 241         return res
 242
 243     def insert(self, values):
 244         raise NotImplementedError
 245
 246     def update(self, values, kvfilter):
 247         raise NotImplementedError
 248
 249     def delete(self, kvfilter):
 250         raise NotImplementedError
 251
 252
 253 class Store(Log):
 254     def __init__(self, config_name=None, database_url=None):
 255         if config_name is None and database_url is None:
 256             raise ValueError('config_name or database_url must be provided')
 257         if config_name:
 258             if config_name not in cherrypy.config:
 259                 raise NameError('Unknown database %s' % config_name)
 260             name = cherrypy.config[config_name]
 261         else:
 262             name = database_url
 263         if name.startswith('configfile://'):
 264             _, filename = name.split('://')
 265             self._db = FileStore(filename)
 266             self._query = FileQuery
 267         else:
 268             self._db = SqlStore.get_connection(name)
 269             self._query = SqlQuery
 270
 271     @property
 272     def is_readonly(self):
 273         return self._db.is_readonly
 274
 275     def _row_to_dict_tree(self, data, row):
 276         name = row[0]
 277         if len(row) > 2:
 278             if name not in data:
 279                 data[name] = dict()
 280             d2 = data[name]
 281             self._row_to_dict_tree(d2, row[1:])
 282         else:
 283             value = row[1]
 284             if name in data:
 285                 if data[name] is list:
 286                     data[name].append(value)
 287                 else:
 288                     v = data[name]
 289                     data[name] = [v, value]
 290             else:
 291                 data[name] = value
 292
 293     def _rows_to_dict_tree(self, rows):
 294         data = dict()
 295         for r in rows:
 296             self._row_to_dict_tree(data, r)
 297         return data
 298
 299     def _load_data(self, table, columns, kvfilter=None):
 300         rows = []
 301         try:
 302             q = self._query(self._db, table, columns, trans=False)
 303             rows = q.select(kvfilter)
 304         except Exception, e:  # pylint: disable=broad-except
 305             self.error("Failed to load data for table %s: [%s]" % (table, e))
 306         return self._rows_to_dict_tree(rows)
 307
 308     def load_config(self):
 309         table = 'config'
 310         columns = ['name', 'value']
 311         return self._load_data(table, columns)
 312
 313     def load_options(self, table, name=None):
 314         kvfilter = dict()
 315         if name:
 316             kvfilter['name'] = name
 317         options = self._load_data(table, OPTIONS_COLUMNS, kvfilter)
 318         if name and name in options:
 319             return options[name]
 320         return options
 321
 322     def save_options(self, table, name, options):
 323         curvals = dict()
 324         q = None
 325         try:
 326             q = self._query(self._db, table, OPTIONS_COLUMNS)
 327             rows = q.select({'name': name}, ['option', 'value'])
 328             for row in rows:
 329                 curvals[row[0]] = row[1]
 330
 331             for opt in options:
 332                 if opt in curvals:
 333                     q.update({'value': options[opt]},
 334                              {'name': name, 'option': opt})
 335                 else:
 336                     q.insert((name, opt, options[opt]))
 337
 338             q.commit()
 339         except Exception, e:  # pylint: disable=broad-except
 340             if q:
 341                 q.rollback()
 342             self.error("Failed to save options: [%s]" % e)
 343             raise
 344
 345     def delete_options(self, table, name, options=None):
 346         kvfilter = {'name': name}
 347         q = None
 348         try:
 349             q = self._query(self._db, table, OPTIONS_COLUMNS)
 350             if options is None:
 351                 q.delete(kvfilter)
 352             else:
 353                 for opt in options:
 354                     kvfilter['option'] = opt
 355                     q.delete(kvfilter)
 356             q.commit()
 357         except Exception, e:  # pylint: disable=broad-except
 358             if q:
 359                 q.rollback()
 360             self.error("Failed to delete from %s: [%s]" % (table, e))
 361             raise
 362
 363     def new_unique_data(self, table, data):
 364         newid = str(uuid.uuid4())
 365         q = None
 366         try:
 367             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 368             for name in data:
 369                 q.insert((newid, name, data[name]))
 370             q.commit()
 371         except Exception, e:  # pylint: disable=broad-except
 372             if q:
 373                 q.rollback()
 374             self.error("Failed to store %s data: [%s]" % (table, e))
 375             raise
 376         return newid
 377
 378     def get_unique_data(self, table, uuidval=None, name=None, value=None):
 379         kvfilter = dict()
 380         if uuidval:
 381             kvfilter['uuid'] = uuidval
 382         if name:
 383             kvfilter['name'] = name
 384         if value:
 385             kvfilter['value'] = value
 386         return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
 387
 388     def save_unique_data(self, table, data):
 389         q = None
 390         try:
 391             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 392             for uid in data:
 393                 curvals = dict()
 394                 rows = q.select({'uuid': uid}, ['name', 'value'])
 395                 for r in rows:
 396                     curvals[r[0]] = r[1]
 397
 398                 datum = data[uid]
 399                 for name in datum:
 400                     if name in curvals:
 401                         if datum[name] is None:
 402                             q.delete({'uuid': uid, 'name': name})
 403                         else:
 404                             q.update({'value': datum[name]},
 405                                      {'uuid': uid, 'name': name})
 406                     else:
 407                         if datum[name] is not None:
 408                             q.insert((uid, name, datum[name]))
 409
 410             q.commit()
 411         except Exception, e:  # pylint: disable=broad-except
 412             if q:
 413                 q.rollback()
 414             self.error("Failed to store data in %s: [%s]" % (table, e))
 415             raise
 416
 417     def del_unique_data(self, table, uuidval):
 418         kvfilter = {'uuid': uuidval}
 419         try:
 420             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
 421             q.delete(kvfilter)
 422         except Exception, e:  # pylint: disable=broad-except
 423             self.error("Failed to delete data from %s: [%s]" % (table, e))
 424
 425     def _reset_data(self, table):
 426         q = None
 427         try:
 428             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 429             q.drop()
 430             q.create()
 431             q.commit()
 432         except Exception, e:  # pylint: disable=broad-except
 433             if q:
 434                 q.rollback()
 435             self.error("Failed to erase all data from %s: [%s]" % (table, e))
 436
 437
 438 class AdminStore(Store):
 439
 440     def __init__(self):
 441         super(AdminStore, self).__init__('admin.config.db')
 442
 443     def get_data(self, plugin, idval=None, name=None, value=None):
 444         return self.get_unique_data(plugin+"_data", idval, name, value)
 445
 446     def save_data(self, plugin, data):
 447         return self.save_unique_data(plugin+"_data", data)
 448
 449     def new_datum(self, plugin, datum):
 450         table = plugin+"_data"
 451         return self.new_unique_data(table, datum)
 452
 453     def del_datum(self, plugin, idval):
 454         table = plugin+"_data"
 455         return self.del_unique_data(table, idval)
 456
 457     def wipe_data(self, plugin):
 458         table = plugin+"_data"
 459         self._reset_data(table)
 460
 461
 462 class UserStore(Store):
 463
 464     def __init__(self, path=None):
 465         super(UserStore, self).__init__('user.prefs.db')
 466
 467     def save_user_preferences(self, user, options):
 468         self.save_options('users', user, options)
 469
 470     def load_user_preferences(self, user):
 471         return self.load_options('users', user)
 472
 473     def save_plugin_data(self, plugin, user, options):
 474         self.save_options(plugin+"_data", user, options)
 475
 476     def load_plugin_data(self, plugin, user):
 477         return self.load_options(plugin+"_data", user)
 478
 479
 480 class TranStore(Store):
 481
 482     def __init__(self, path=None):
 483         super(TranStore, self).__init__('transactions.db')