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 new_query(self, table, columns=None, autotable=True, autocommit=True):
 130         return self._query(self._db, table, columns, autotable, autocommit)
 131
 132     def _row_to_dict_tree(self, data, row):
 133         name = row[0]
 134         if len(row) > 2:
 135             if name not in data:
 136                 data[name] = dict()
 137             d2 = data[name]
 138             self._row_to_dict_tree(d2, row[1:])
 139         else:
 140             value = row[1]
 141             if name in data:
 142                 if data[name] is list:
 143                     data[name].append(value)
 144                 else:
 145                     v = data[name]
 146                     data[name] = [v, value]
 147             else:
 148                 data[name] = value
 149
 150     def _rows_to_dict_tree(self, rows):
 151         data = dict()
 152         for r in rows:
 153             self._row_to_dict_tree(data, r)
 154         return data
 155
 156     def load_data(self, table, columns, kvfilter=None):
 157         rows = []
 158         try:
 159             q = self._query(self._db, table, columns, trans=False)
 160             rows = q.select(kvfilter)
 161         except Exception, e:  # pylint: disable=broad-except
 162             self.error("Failed to load data for table %s: [%s]" % (table, e))
 163         return self._rows_to_dict_tree(rows)
 164
 165     def load_config(self):
 166         table = 'config'
 167         columns = ['name', 'value']
 168         return self.load_data(table, columns)
 169
 170     def load_options(self, table, name=None):
 171         kvfilter = dict()
 172         if name:
 173             kvfilter['name'] = name
 174         options = self.load_data(table, OPTIONS_COLUMNS, kvfilter)
 175         if name and name in options:
 176             return options[name]
 177         return options
 178
 179     def save_options(self, table, name, options):
 180         curvals = dict()
 181         q = None
 182         try:
 183             q = self._query(self._db, table, OPTIONS_COLUMNS)
 184             rows = q.select({'name': name}, ['option', 'value'])
 185             for row in rows:
 186                 curvals[row[0]] = row[1]
 187
 188             for opt in options:
 189                 if opt in curvals:
 190                     q.update({'value': options[opt]},
 191                              {'name': name, 'option': opt})
 192                 else:
 193                     q.insert((name, opt, options[opt]))
 194
 195             q.commit()
 196         except Exception, e:  # pylint: disable=broad-except
 197             if q:
 198                 q.rollback()
 199             self.error("Failed to save options: [%s]" % e)
 200             raise
 201
 202     def delete_options(self, table, name, options=None):
 203         kvfilter = {'name': name}
 204         q = None
 205         try:
 206             q = self._query(self._db, table, OPTIONS_COLUMNS)
 207             if options is None:
 208                 q.delete(kvfilter)
 209             else:
 210                 for opt in options:
 211                     kvfilter['option'] = opt
 212                     q.delete(kvfilter)
 213             q.commit()
 214         except Exception, e:  # pylint: disable=broad-except
 215             if q:
 216                 q.rollback()
 217             self.error("Failed to delete from %s: [%s]" % (table, e))
 218             raise
 219
 220     def new_unique_data(self, table, data):
 221         newid = str(uuid.uuid4())
 222         q = None
 223         try:
 224             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 225             for name in data:
 226                 q.insert((newid, name, data[name]))
 227             q.commit()
 228         except Exception, e:  # pylint: disable=broad-except
 229             if q:
 230                 q.rollback()
 231             self.error("Failed to store %s data: [%s]" % (table, e))
 232             raise
 233         return newid
 234
 235     def get_unique_data(self, table, uuidval=None, name=None, value=None):
 236         kvfilter = dict()
 237         if uuidval:
 238             kvfilter['uuid'] = uuidval
 239         if name:
 240             kvfilter['name'] = name
 241         if value:
 242             kvfilter['value'] = value
 243         return self.load_data(table, UNIQUE_DATA_COLUMNS, kvfilter)
 244
 245     def save_unique_data(self, table, data):
 246         q = None
 247         try:
 248             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 249             for uid in data:
 250                 curvals = dict()
 251                 rows = q.select({'uuid': uid}, ['name', 'value'])
 252                 for r in rows:
 253                     curvals[r[0]] = r[1]
 254
 255                 datum = data[uid]
 256                 for name in datum:
 257                     if name in curvals:
 258                         q.update({'value': datum[name]},
 259                                  {'uuid': uid, 'name': name})
 260                     else:
 261                         q.insert((uid, name, datum[name]))
 262
 263             q.commit()
 264         except Exception, e:  # pylint: disable=broad-except
 265             if q:
 266                 q.rollback()
 267             self.error("Failed to store data in %s: [%s]" % (table, e))
 268             raise
 269
 270     def del_unique_data(self, table, uuidval):
 271         kvfilter = {'uuid': uuidval}
 272         try:
 273             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS, trans=False)
 274             q.delete(kvfilter)
 275         except Exception, e:  # pylint: disable=broad-except
 276             self.error("Failed to delete data from %s: [%s]" % (table, e))
 277
 278     def reset_data(self, table):
 279         try:
 280             q = self._query(self._db, table, UNIQUE_DATA_COLUMNS)
 281             q.drop()
 282             q.create()
 283             q.commit()
 284         except Exception, e:  # pylint: disable=broad-except
 285             if q:
 286                 q.rollback()
 287             self.error("Failed to erase all data from %s: [%s]" % (table, e))
 288
 289
 290 class AdminStore(Store):
 291
 292     def __init__(self):
 293         super(AdminStore, self).__init__('admin.config.db')
 294
 295     def get_data(self, plugin, idval=None, name=None, value=None):
 296         return self.get_unique_data(plugin+"_data", idval, name, value)
 297
 298     def save_data(self, plugin, data):
 299         return self.save_unique_data(plugin+"_data", data)
 300
 301     def new_datum(self, plugin, datum):
 302         table = plugin+"_data"
 303         return self.new_unique_data(table, datum)
 304
 305     def del_datum(self, plugin, idval):
 306         table = plugin+"_data"
 307         return self.del_unique_data(table, idval)
 308
 309     def wipe_data(self, plugin):
 310         table = plugin+"_data"
 311         self.reset_data(table)
 312
 313
 314 class UserStore(Store):
 315
 316     def __init__(self, path=None):
 317         super(UserStore, self).__init__('user.prefs.db')
 318
 319     def save_user_preferences(self, user, options):
 320         return self.save_options('users', user, options)
 321
 322
 323 class TranStore(Store):
 324
 325     def __init__(self, path=None):
 326         super(TranStore, self).__init__('transactions.db')