ipsilon/util/data.py

   1 #!/usr/bin/python
   2 #
   3 # Copyright (C) 2013  Simo Sorce <simo@redhat.com>
   4 #
   5 # see file 'COPYING' for use and warranty information
   6 #
   7 # This program is free software; you can redistribute it and/or modify
   8 # it under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation, either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # This program is distributed in the hope that it will be useful,
  13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19
  20 import cherrypy
  21 from ipsilon.util.log import Log
  22 from sqlalchemy import create_engine
  23 from sqlalchemy import MetaData, Table, Column, Text
  24 from sqlalchemy.sql import select
  25 import uuid
  26
  27
  28 OPTIONS_COLUMNS = ['name', 'option', 'value']
  29 UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value']
  30
  31
  32 class SqlStore(Log):
  33
  34     def __init__(self, name):
  35         if name not in cherrypy.config:
  36             raise NameError('Unknown database %s' % name)
  37         engine_name = cherrypy.config[name]
  38         if '://' not in engine_name:
  39             engine_name = 'sqlite:///' + engine_name
  40         self._dbengine = create_engine(engine_name)
  41
  42     def engine(self):
  43         return self._dbengine
  44
  45     def connection(self):
  46         return self._dbengine.connect()
  47
  48
  49 def SqlAutotable(f):
  50     def at(self, *args, **kwargs):
  51         if self.autotable:
  52             self.create()
  53         return f(self, *args, **kwargs)
  54     return at
  55
  56
  57 class SqlQuery(Log):
  58
  59     def __init__(self, db_obj, table, columns, autotable=True, trans=True):
  60         self._db = db_obj
  61         self.autotable = autotable
  62         self._con = self._db.connection()
  63         self._trans = self._con.begin() if trans else None
  64         self._table = self._get_table(table, columns)
  65
  66     def _get_table(self, name, columns):
  67         table = Table(name, MetaData(self._db.engine()))
  68         for c in columns:
  69             table.append_column(Column(c, Text()))
  70         return table
  71
  72     def _where(self, kvfilter):
  73         where = None
  74         if kvfilter is not None:
  75             for k in kvfilter:
  76                 w = self._table.columns[k] == kvfilter[k]
  77                 if where is None:
  78                     where = w
  79                 else:
  80                     where = where & w
  81         return where
  82
  83     def _columns(self, columns=None):
  84         cols = None
  85         if columns is not None:
  86             cols = []
  87             for c in columns:
  88                 cols.append(self._table.columns[c])
  89         else:
  90             cols = self._table.columns
  91         return cols
  92
  93     def rollback(self):
  94         self._trans.rollback()
  95
  96     def commit(self):
  97         self._trans.commit()
  98
  99     def create(self):
 100         self._table.create(checkfirst=True)
 101
 102     def drop(self):
 103         self._table.drop(checkfirst=True)
 104
 105     @SqlAutotable
 106     def select(self, kvfilter=None, columns=None):
 107         return self._con.execute(select(self._columns(columns),
 108                                         self._where(kvfilter)))
 109
 110     @SqlAutotable
 111     def insert(self, values):
 112         self._con.execute(self._table.insert(values))
 113
 114     @SqlAutotable
 115     def update(self, values, kvfilter):
 116         self._con.execute(self._table.update(self._where(kvfilter), values))
 117
 118     @SqlAutotable
 119     def delete(self, kvfilter):
 120         self._con.execute(self._table.delete(self._where(kvfilter)))
 121
 122
 123 class Store(Log):
 124
 125     def __init__(self, config_name):
 126         self._db = SqlStore(config_name)
 127         self._query = SqlQuery
 128
 129     def _row_to_dict_tree(self, data, row):
 130         name = row[0]
 131         if len(row) > 2:
 132             if name not in data:
 133                 data[name] = dict()
 134             d2 = data[name]
 135             self._row_to_dict_tree(d2, row[1:])
 136         else:
 137             value = row[1]
 138             if name in data:
 139                 if data[name] is list:
 140                     data[name].append(value)
 141                 else:
 142                     v = data[name]
 143                     data[name] = [v, value]
 144             else:
 145                 data[name] = value
 146
 147     def _rows_to_dict_tree(self, rows):
 148         data = dict()
 149         for r in rows:
 150             self._row_to_dict_tree(data, r)
 151         return data
 152
 153     def load_data(self, table, columns, kvfilter=None):
 154         rows = []
 155         try:
 156             q = self._query(self._db, table, columns, trans=False)
 157             rows = q.select(kvfilter)
 158         except Exception, e:  # pylint: disable=broad-except
 159             self.error("Failed to load data for table %s: [%s]" % (table, e))
 160         return self._rows_to_dict_tree(rows)
 161
 162     def load_config(self):
 163         table = 'config'
 164         columns = ['name', 'value']
 165         return self.load_data(table, columns)
 166
 167     def load_options(self, table, name=None):
 168         kvfilter = dict()
 169         if name:
 170             kvfilter['name'] = name
 171         options = self.load_data(table, OPTIONS_COLUMNS, kvfilter)
 172         if name and name in options:
 173             return options[name]
 174         return options
 175
 176     def save_options(self, table, name, options):
 177         curvals = dict()
 178         q = None
 179         try:
 180             q = self._query(self._db, table, OPTIONS_COLUMNS)
 181             rows = q.select({'name': name}, ['option', 'value'])
 182             for row in rows:
 183                 curvals[row[0]] = row[1]
 184
 185             for opt in options:
 186                 if opt in curvals:
 187                     q.update({'value': options[opt]},
 188                              {'name': name, 'option': opt})
 189                 else:
 190                     q.insert((name, opt, options[opt]))
 191
 192             q.commit()
 193         except Exception, e:  # pylint: disable=broad-except
 194             if q:
 195                 q.rollback()
 196             self.error("Failed to save options: [%s]" % e)
 197             raise
 198
 199     def delete_options(self, table, name, options=None):
 200         kvfilter = {'name': name}
 201         q = None
 202         try:
 203             q = self._query(self._db, table, OPTIONS_COLUMNS)
 204             if options is None:
 205                 q.delete(kvfilter)
 206             else:
 207                 for opt in options:
 208                     kvfilter['option'] = opt
 209                     q.delete(kvfilter)
 210             q.commit()
 211         except Exception, e:  # pylint: disable=broad-except
 212             if q:
 213                 q.rollback()
 214             self.error("Failed to delete from %s: [%s]" % (table, e))
 215             raise
 216
 217     def new_unique_data(self, table, data):
 218         newid = str(uuid.uuid4())
 219         q = None
 220         try:
 221             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 222             for name in data:
 223                 q.insert((newid, name, data[name]))
 224             q.commit()
 225         except Exception, e:  # pylint: disable=broad-except
 226             if q:
 227                 q.rollback()
 228             self.error("Failed to store %s data: [%s]" % (table, e))
 229             raise
 230         return newid
 231
 232     def get_unique_data(self, table, uuidval=None, name=None, value=None):
 233         kvfilter = dict()
 234         if uuidval:
 235             kvfilter['uuid'] = uuidval
 236         if name:
 237             kvfilter['name'] = name
 238         if value:
 239             kvfilter['value'] = value
 240         return self.load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
 241
 242     def save_unique_data(self, table, data):
 243         q = None
 244         try:
 245             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 246             for uid in data:
 247                 curvals = dict()
 248                 rows = q.select({'uuid': uid}, ['name', 'value'])
 249                 for r in rows:
 250                     curvals[r[0]] = r[1]
 251
 252                 datum = data[uid]
 253                 for name in datum:
 254                     if name in curvals:
 255                         q.update({'value': datum[name]},
 256                                  {'uuid': uid, 'name': name})
 257                     else:
 258                         q.insert((uid, name, datum[name]))
 259
 260             q.commit()
 261         except Exception, e:  # pylint: disable=broad-except
 262             if q:
 263                 q.rollback()
 264             self.error("Failed to store data in %s: [%s]" % (table, e))
 265             raise
 266
 267     def del_unique_data(self, table, uuidval):
 268         kvfilter = {'uuid': uuidval}
 269         try:
 270             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
 271             q.delete(kvfilter)
 272         except Exception, e:  # pylint: disable=broad-except
 273             self.error("Failed to delete data from %s: [%s]" % (table, e))
 274
 275     def reset_data(self, table):
 276         try:
 277             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 278             q.drop()
 279             q.create()
 280             q.commit()
 281         except Exception, e:  # pylint: disable=broad-except
 282             if q:
 283                 q.rollback()
 284             self.error("Failed to erase all data from %s: [%s]" % (table, e))
 285
 286
 287 class AdminStore(Store):
 288
 289     def __init__(self):
 290         super(AdminStore, self).__init__('admin.config.db')
 291
 292     def get_data(self, plugin, idval=None, name=None, value=None):
 293         return self.get_unique_data(plugin+"_data", idval, name, value)
 294
 295     def save_data(self, plugin, data):
 296         return self.save_unique_data(plugin+"_data", data)
 297
 298     def new_datum(self, plugin, datum):
 299         table = plugin+"_data"
 300         return self.new_unique_data(table, datum)
 301
 302     def del_datum(self, plugin, idval):
 303         table = plugin+"_data"
 304         return self.del_unique_data(table, idval)
 305
 306     def wipe_data(self, plugin):
 307         table = plugin+"_data"
 308         self.reset_data(table)
 309
 310
 311 class UserStore(Store):
 312
 313     def __init__(self, path=None):
 314         super(UserStore, self).__init__('user.prefs.db')
 315
 316     def save_user_preferences(self, user, options):
 317         self.save_options('users', user, options)
 318
 319     def load_user_preferences(self, user):
 320         return self.load_options('users', user)
 321
 322     def save_plugin_data(self, plugin, user, options):
 323         self.save_options(plugin+"_data", user, options)
 324
 325     def load_plugin_data(self, plugin, user):
 326         return self.load_options(plugin+"_data", user)
 327
 328
 329 class TranStore(Store):
 330
 331     def __init__(self, path=None):
 332         super(TranStore, self).__init__('transactions.db')