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 os
  21 import sqlite3
  22 import cherrypy
  23 from random import randint
  24 import sys
  25
  26
  27 class Store(object):
  28
  29     def __init__(self, path=None):
  30         if path is None:
  31             self._path = os.getcwd()
  32         else:
  33             self._path = path
  34         self._admin_dbname = self._get_admin_dbname()
  35         self._user_dbname = self._get_userprefs_dbname()
  36
  37     def _get_admin_dbname(self):
  38         path = None
  39         if 'admin.config.db' in cherrypy.config:
  40             path = cherrypy.config['admin.config.db']
  41         if not path:
  42             path = os.path.join(self._path, 'adminconfig.sqlite')
  43         return path
  44
  45     def _get_userprefs_dbname(self):
  46         path = None
  47         if 'user.prefs.db' in cherrypy.config:
  48             path = cherrypy.config['user.prefs.db']
  49         if not path:
  50             path = os.path.join(self._path, 'userprefs.sqlite')
  51         return path
  52
  53     def _load_config(self, dbname):
  54         con = None
  55         rows = []
  56         try:
  57             con = sqlite3.connect(dbname)
  58             cur = con.cursor()
  59             cur.executescript("""
  60                 CREATE TABLE IF NOT EXISTS config(name TEXT, value TEXT)
  61                 """)
  62             cur.execute("SELECT * FROM config")
  63             rows = cur.fetchall()
  64             con.commit()
  65         except sqlite3.Error, e:
  66             if con:
  67                 con.rollback()
  68             cherrypy.log.error("Failed to load config: [%s]" % e)
  69         finally:
  70             if con:
  71                 con.close()
  72
  73         conf = {}
  74         for row in rows:
  75             if row[0] in conf:
  76                 # multivalued
  77                 if conf[row[0]] is list:
  78                     conf[row[0]].append(row[1])
  79                 else:
  80                     v = conf[row[0]]
  81                     conf[row[0]] = [v, row[1]]
  82             else:
  83                 conf[row[0]] = row[1]
  84
  85         return conf
  86
  87     def get_admin_config(self):
  88         return self._load_config(self._admin_dbname)
  89
  90     def _load_user_prefs(self, dbname, user):
  91         con = None
  92         rows = []
  93         try:
  94             con = sqlite3.connect(dbname)
  95             cur = con.cursor()
  96             cur.executescript("""
  97                 CREATE TABLE IF NOT EXISTS users(name TEXT,
  98                                                  option TEXT,
  99                                                  value TEXT)
 100                 """)
 101             cur.execute("SELECT option, value FROM users "
 102                         "where name = '%s'" % user)
 103             rows = cur.fetchall()
 104             con.commit()
 105         except sqlite3.Error, e:
 106             if con:
 107                 con.rollback()
 108             cherrypy.log.error("Failed to load %s's prefs from "
 109                                "%s: [%s]" % (user, dbname, e))
 110         finally:
 111             if con:
 112                 con.close()
 113
 114         conf = {}
 115         for row in rows:
 116             conf[row[0]] = row[1]
 117
 118         return conf
 119
 120     def get_user_preferences(self, user):
 121         return self._load_user_prefs(self._user_dbname, user)
 122
 123     def save_user_preferences(self, user, options):
 124         SELECT = "SELECT option, value FROM users WHERE name=?"
 125         UPDATE = "UPDATE users SET value=? WHERE name=? AND option=?"
 126         INSERT = "INSERT INTO users VALUES(?,?,?)"
 127         con = None
 128         try:
 129             con = sqlite3.connect(self._user_dbname)
 130             cur = con.cursor()
 131             cur.execute("""
 132                 CREATE TABLE IF NOT EXISTS users(name TEXT,
 133                                                  option TEXT,
 134                                                  value TEXT)
 135                 """)
 136             curvals = dict()
 137             for row in cur.execute(SELECT, (user,)):
 138                 curvals[row[0]] = row[1]
 139
 140             for name in options:
 141                 if name in curvals:
 142                     cur.execute(UPDATE, (options[name], user, name))
 143                 else:
 144                     cur.execute(INSERT, (user, name, options[name]))
 145
 146             con.commit()
 147         except sqlite3.Error, e:
 148             if con:
 149                 con.rollback()
 150             cherrypy.log.error("Failed to store config: [%s]" % e)
 151             raise
 152         finally:
 153             if con:
 154                 con.close()
 155
 156     def get_plugins_config(self, facility):
 157         con = None
 158         rows = []
 159         try:
 160             con = sqlite3.connect(self._admin_dbname)
 161             cur = con.cursor()
 162             cur.execute("CREATE TABLE IF NOT EXISTS " +
 163                         facility + " (name TEXT,option TEXT,value TEXT)")
 164             cur.execute("SELECT * FROM " + facility)
 165             rows = cur.fetchall()
 166             con.commit()
 167         except sqlite3.Error, e:
 168             if con:
 169                 con.rollback()
 170             cherrypy.log.error("Failed to load %s config: [%s]" % (facility,
 171                                                                    e))
 172         finally:
 173             if con:
 174                 con.close()
 175
 176         lpo = []
 177         plco = dict()
 178         for row in rows:
 179             if row[0] == 'global':
 180                 if row[1] == 'order':
 181                     lpo = row[2].split(',')
 182                 continue
 183             if row[0] not in plco:
 184                 # one dict per provider
 185                 plco[row[0]] = dict()
 186
 187             conf = plco[row[0]]
 188             if row[1] in conf:
 189                 if conf[row[1]] is list:
 190                     conf[row[1]].append(row[2])
 191                 else:
 192                     v = conf[row[1]]
 193                     conf[row[1]] = [v, row[2]]
 194             else:
 195                 conf[row[1]] = row[2]
 196
 197         return (lpo, plco)
 198
 199     def get_plugin_config(self, facility, plugin):
 200         con = None
 201         rows = []
 202         try:
 203             con = sqlite3.connect(self._admin_dbname)
 204             cur = con.cursor()
 205             cur.execute("CREATE TABLE IF NOT EXISTS " +
 206                         facility + " (name TEXT,option TEXT,value TEXT)")
 207             cur.execute("SELECT option, value FROM " +
 208                         facility + " WHERE name=?", (plugin,))
 209             rows = cur.fetchall()
 210             con.commit()
 211         except sqlite3.Error, e:
 212             if con:
 213                 con.rollback()
 214             fpe = (facility, plugin, e)
 215             cherrypy.log.error("Failed to get %s/%s config: [%s]" % fpe)
 216             raise
 217         finally:
 218             if con:
 219                 con.close()
 220
 221         res = dict()
 222         for (option, value) in rows:
 223             if option in res:
 224                 if res[option] is list:
 225                     res[option].append(value)
 226                 else:
 227                     v = res[option]
 228                     res[option] = [v, value]
 229             else:
 230                 res[option] = value
 231
 232         return res
 233
 234     def save_plugin_config(self, facility, plugin, options):
 235         SELECT = "SELECT option, value FROM %s WHERE name=?" % facility
 236         UPDATE = "UPDATE %s SET value=? WHERE name=? AND option=?" % facility
 237         INSERT = "INSERT INTO %s VALUES(?,?,?)" % facility
 238         con = None
 239         try:
 240             con = sqlite3.connect(self._admin_dbname)
 241             cur = con.cursor()
 242             cur.execute("CREATE TABLE IF NOT EXISTS " +
 243                         facility + " (name TEXT,option TEXT,value TEXT)")
 244             curvals = dict()
 245             for row in cur.execute(SELECT, (plugin,)):
 246                 curvals[row[0]] = row[1]
 247
 248             for name in options:
 249                 if name in curvals:
 250                     cur.execute(UPDATE, (options[name], plugin, name))
 251                 else:
 252                     cur.execute(INSERT, (plugin, name, options[name]))
 253
 254             con.commit()
 255         except sqlite3.Error, e:
 256             if con:
 257                 con.rollback()
 258             cherrypy.log.error("Failed to store config: [%s]" % e)
 259             raise
 260         finally:
 261             if con:
 262                 con.close()
 263
 264     def wipe_plugin_config(self, facility, plugin):
 265         # Try to backup old data first, just in case ?
 266         try:
 267             con = sqlite3.connect(self._admin_dbname)
 268             cur = con.cursor()
 269             cur.execute("CREATE TABLE IF NOT EXISTS " +
 270                         facility + " (name TEXT,option TEXT,value TEXT)")
 271             cur.execute("DELETE FROM " + facility + " WHERE name=?",
 272                         (plugin,))
 273             con.commit()
 274         except sqlite3.Error, e:
 275             if con:
 276                 con.rollback()
 277             cherrypy.log.error("Failed to wipe %s config: [%s]" % (plugin, e))
 278             raise
 279         finally:
 280             if con:
 281                 con.close()
 282
 283     def get_data(self, plugin, idval=None, name=None, value=None):
 284         con = None
 285         rows = []
 286         names = None
 287         values = ()
 288         if idval or name or value:
 289             names = ""
 290             if idval:
 291                 names += " id=?"
 292                 values = values + (idval,)
 293             if name:
 294                 if len(names) != 0:
 295                     names += " AND"
 296                 names += " name=?"
 297                 values = values + (name,)
 298             if value:
 299                 if len(names) != 0:
 300                     names += " AND"
 301                 names += " value=?"
 302                 values = values + (value,)
 303         try:
 304             con = sqlite3.connect(self._admin_dbname)
 305             cur = con.cursor()
 306             cur.execute("CREATE TABLE IF NOT EXISTS " +
 307                         plugin + "_data (id INTEGER, name TEXT, value TEXT)")
 308             if not names:
 309                 cur.execute("SELECT * FROM " + plugin + "_data")
 310             else:
 311                 cur.execute("SELECT * FROM " + plugin + "_data WHERE" +
 312                             names, values)
 313             rows = cur.fetchall()
 314             con.commit()
 315         except sqlite3.Error, e:
 316             if con:
 317                 con.rollback()
 318             cherrypy.log.error("Failed to load %s data: [%s]" % (plugin, e))
 319             cherrypy.log.error(repr([names, values]))
 320         finally:
 321             if con:
 322                 con.close()
 323
 324         data = dict()
 325         for row in rows:
 326             if row[0] not in data:
 327                 data[row[0]] = dict()
 328
 329             item = data[row[0]]
 330             if row[1] in item:
 331                 if item[row[1]] is list:
 332                     item[row[1]].append(row[2])
 333                 else:
 334                     v = item[row[1]]
 335                     item[row[1]] = [v, row[2]]
 336             else:
 337                 item[row[1]] = row[2]
 338
 339         return data
 340
 341     def save_data(self, plugin, data):
 342         SELECT = "SELECT name, value FROM %s_data WHERE id=?" % plugin
 343         UPDATE = "UPDATE %s_data SET value=? WHERE id=? AND name=?" % plugin
 344         INSERT = "INSERT INTO %s_data VALUES(?,?,?)" % plugin
 345         con = None
 346         try:
 347             con = sqlite3.connect(self._admin_dbname)
 348             cur = con.cursor()
 349             for idval in data:
 350                 curvals = dict()
 351                 for row in cur.execute(SELECT, (idval,)):
 352                     curvals[row[0]] = row[1]
 353
 354                 datum = data[idval]
 355                 for name in datum:
 356                     if name in curvals:
 357                         cur.execute(UPDATE, (datum[name], idval, name))
 358                     else:
 359                         cur.execute(INSERT, (idval, name, datum[name]))
 360
 361             con.commit()
 362         except sqlite3.Error, e:
 363             if con:
 364                 con.rollback()
 365             cherrypy.log.error("Failed to store %s data: [%s]" % (plugin, e))
 366             raise
 367         finally:
 368             if con:
 369                 con.close()
 370
 371     def new_datum(self, plugin, datum):
 372         ID = "(SELECT IFNULL(MAX(id), 0) + 1 FROM %s_data)" % plugin
 373         INSERT_NEW = "INSERT INTO %s_data VALUES(%s,?,?)" % (plugin, ID)
 374         INSERT = "INSERT INTO %s_data VALUES(?,?,?)" % plugin
 375         SELECT = "SELECT id FROM %s_data WHERE name=? AND value=?" % plugin
 376         DELETE = "DELETE FROM %s_data WHERE name=? AND value=?" % plugin
 377         con = None
 378         try:
 379             con = sqlite3.connect(self._admin_dbname)
 380             cur = con.cursor()
 381             tmpid = ('new', str(randint(0, sys.maxint)))
 382             cur.execute(INSERT_NEW, tmpid)
 383             cur.execute(SELECT, tmpid)
 384             rows = cur.fetchall()
 385             idval = rows[0][0]
 386             for name in datum:
 387                 cur.execute(INSERT, (idval, name, datum[name]))
 388             cur.execute(DELETE, tmpid)
 389             con.commit()
 390         except sqlite3.Error, e:
 391             if con:
 392                 con.rollback()
 393             cherrypy.log.error("Failed to store %s data: [%s]" % (plugin, e))
 394             raise
 395         finally:
 396             if con:
 397                 con.close()
 398
 399     def wipe_data(self, plugin):
 400         # Try to backup old data first, just in case
 401         try:
 402             con = sqlite3.connect(self._admin_dbname)
 403             cur = con.cursor()
 404             cur.execute("DROP TABLE IF EXISTS " + plugin + "_data")
 405             cur.execute("CREATE TABLE " + plugin + "_data"
 406                         "(id INTEGER, name TEXT, value TEXT)")
 407             con.commit()
 408         except sqlite3.Error, e:
 409             if con:
 410                 con.rollback()
 411             cherrypy.log.error("Failed to wipe %s data: [%s]" % (plugin, e))
 412             raise
 413         finally:
 414             if con:
 415                 con.close()