ipsilon/util/data.py

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