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