import os
import sqlite3
import cherrypy
+from random import randint
+import sys
+
class Store(object):
self._path = os.getcwd()
else:
self._path = path
+ self._admin_dbname = self._get_admin_dbname()
+ self._user_dbname = self._get_userprefs_dbname()
+
+ def _get_admin_dbname(self):
+ path = None
+ if 'admin.config.db' in cherrypy.config:
+ path = cherrypy.config['admin.config.db']
+ if not path:
+ path = os.path.join(self._path, 'adminconfig.sqlite')
+ return path
+
+ def _get_userprefs_dbname(self):
+ path = None
+ if 'user.prefs.db' in cherrypy.config:
+ path = cherrypy.config['user.prefs.db']
+ if not path:
+ path = os.path.join(self._path, 'userprefs.sqlite')
+ return path
def _load_config(self, dbname):
con = None
return conf
def get_admin_config(self):
- path = None
- if 'admin.config.db' in cherrypy.config:
- path = cherrypy.config['admin.config.db']
- if not path:
- path = os.path.join(self._path, 'adminconfig.sqlite')
-
- return self._load_config(path)
+ return self._load_config(self._admin_dbname)
def _load_user_prefs(self, dbname, user):
con = None
if con:
con.rollback()
cherrypy.log.error("Failed to load %s's prefs from "
- "%s: [%s]" % ( user, dbname, e))
+ "%s: [%s]" % (user, dbname, e))
finally:
if con:
con.close()
return conf
def get_user_preferences(self, user):
- path = None
- if 'user.prefs.db' in cherrypy.config:
- path = cherrypy.config['user.prefs.db']
- if not path:
- path = os.path.join(self._path, 'userprefs.sqlite')
+ return self._load_user_prefs(self._user_dbname, user)
+
+ def save_user_preferences(self, user, options):
+ SELECT = "SELECT option, value FROM users WHERE name=?"
+ UPDATE = "UPDATE users SET value=? WHERE name=? AND option=?"
+ INSERT = "INSERT INTO users VALUES(?,?,?)"
+ con = None
+ try:
+ con = sqlite3.connect(self._user_dbname)
+ cur = con.cursor()
+ cur.execute("""
+ CREATE TABLE IF NOT EXISTS users(name TEXT,
+ option TEXT,
+ value TEXT)
+ """)
+ curvals = dict()
+ for row in cur.execute(SELECT, (user,)):
+ curvals[row[0]] = row[1]
+
+ for name in options:
+ if name in curvals:
+ cur.execute(UPDATE, (options[name], user, name))
+ else:
+ cur.execute(INSERT, (user, name, options[name]))
+
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to store config: [%s]" % e)
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def get_plugins_config(self, facility):
+ con = None
+ rows = []
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("CREATE TABLE IF NOT EXISTS " +
+ facility + " (name TEXT,option TEXT,value TEXT)")
+ cur.execute("SELECT * FROM " + facility)
+ rows = cur.fetchall()
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to load %s config: [%s]" % (facility,
+ e))
+ finally:
+ if con:
+ con.close()
+
+ lpo = []
+ plco = dict()
+ for row in rows:
+ if row[0] == 'global':
+ if row[1] == 'order':
+ lpo = row[2].split(',')
+ continue
+ if row[0] not in plco:
+ # one dict per provider
+ plco[row[0]] = dict()
+
+ conf = plco[row[0]]
+ if row[1] in conf:
+ if conf[row[1]] is list:
+ conf[row[1]].append(row[2])
+ else:
+ v = conf[row[1]]
+ conf[row[1]] = [v, row[2]]
+ else:
+ conf[row[1]] = row[2]
+
+ return (lpo, plco)
+
+ def get_plugin_config(self, facility, plugin):
+ con = None
+ rows = []
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("CREATE TABLE IF NOT EXISTS " +
+ facility + " (name TEXT,option TEXT,value TEXT)")
+ cur.execute("SELECT option, value FROM " +
+ facility + " WHERE name=?", (plugin,))
+ rows = cur.fetchall()
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ fpe = (facility, plugin, e)
+ cherrypy.log.error("Failed to get %s/%s config: [%s]" % fpe)
+ raise
+ finally:
+ if con:
+ con.close()
+
+ res = dict()
+ for (option, value) in rows:
+ if option in res:
+ if res[option] is list:
+ res[option].append(value)
+ else:
+ v = res[option]
+ res[option] = [v, value]
+ else:
+ res[option] = value
+
+ return res
+
+ def save_plugin_config(self, facility, plugin, options):
+ SELECT = "SELECT option, value FROM %s WHERE name=?" % facility
+ UPDATE = "UPDATE %s SET value=? WHERE name=? AND option=?" % facility
+ INSERT = "INSERT INTO %s VALUES(?,?,?)" % facility
+ con = None
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("CREATE TABLE IF NOT EXISTS " +
+ facility + " (name TEXT,option TEXT,value TEXT)")
+ curvals = dict()
+ for row in cur.execute(SELECT, (plugin,)):
+ curvals[row[0]] = row[1]
+
+ for name in options:
+ if name in curvals:
+ cur.execute(UPDATE, (options[name], plugin, name))
+ else:
+ cur.execute(INSERT, (plugin, name, options[name]))
- return self._load_user_prefs(path, user)
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to store config: [%s]" % e)
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def wipe_plugin_config(self, facility, plugin):
+ # Try to backup old data first, just in case ?
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("CREATE TABLE IF NOT EXISTS " +
+ facility + " (name TEXT,option TEXT,value TEXT)")
+ cur.execute("DELETE FROM " + facility + " WHERE name=?",
+ (plugin,))
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to wipe %s config: [%s]" % (plugin, e))
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def get_data(self, plugin, idval=None, name=None, value=None):
+ con = None
+ rows = []
+ names = None
+ values = ()
+ if idval or name or value:
+ names = ""
+ if idval:
+ names += " id=?"
+ values = values + (idval,)
+ if name:
+ if len(names) != 0:
+ names += " AND"
+ names += " name=?"
+ values = values + (name,)
+ if value:
+ if len(names) != 0:
+ names += " AND"
+ names += " value=?"
+ values = values + (value,)
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("CREATE TABLE IF NOT EXISTS " +
+ plugin + "_data (id INTEGER, name TEXT, value TEXT)")
+ if not names:
+ cur.execute("SELECT * FROM " + plugin + "_data")
+ else:
+ cur.execute("SELECT * FROM " + plugin + "_data WHERE" +
+ names, values)
+ rows = cur.fetchall()
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to load %s data: [%s]" % (plugin, e))
+ cherrypy.log.error(repr([names, values]))
+ finally:
+ if con:
+ con.close()
+
+ data = dict()
+ for row in rows:
+ if row[0] not in data:
+ data[row[0]] = dict()
+
+ item = data[row[0]]
+ if row[1] in item:
+ if item[row[1]] is list:
+ item[row[1]].append(row[2])
+ else:
+ v = item[row[1]]
+ item[row[1]] = [v, row[2]]
+ else:
+ item[row[1]] = row[2]
+
+ return data
+
+ def save_data(self, plugin, data):
+ SELECT = "SELECT name, value FROM %s_data WHERE id=?" % plugin
+ UPDATE = "UPDATE %s_data SET value=? WHERE id=? AND name=?" % plugin
+ INSERT = "INSERT INTO %s_data VALUES(?,?,?)" % plugin
+ con = None
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ for idval in data:
+ curvals = dict()
+ for row in cur.execute(SELECT, (idval,)):
+ curvals[row[0]] = row[1]
+
+ datum = data[idval]
+ for name in datum:
+ if name in curvals:
+ cur.execute(UPDATE, (datum[name], idval, name))
+ else:
+ cur.execute(INSERT, (idval, name, datum[name]))
+
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to store %s data: [%s]" % (plugin, e))
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def new_datum(self, plugin, datum):
+ ID = "(SELECT IFNULL(MAX(id), 0) + 1 FROM %s_data)" % plugin
+ INSERT_NEW = "INSERT INTO %s_data VALUES(%s,?,?)" % (plugin, ID)
+ INSERT = "INSERT INTO %s_data VALUES(?,?,?)" % plugin
+ SELECT = "SELECT id FROM %s_data WHERE name=? AND value=?" % plugin
+ DELETE = "DELETE FROM %s_data WHERE name=? AND value=?" % plugin
+ con = None
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ tmpid = ('new', str(randint(0, sys.maxint)))
+ cur.execute(INSERT_NEW, tmpid)
+ cur.execute(SELECT, tmpid)
+ rows = cur.fetchall()
+ idval = rows[0][0]
+ for name in datum:
+ cur.execute(INSERT, (idval, name, datum[name]))
+ cur.execute(DELETE, tmpid)
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to store %s data: [%s]" % (plugin, e))
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def del_datum(self, plugin, idval):
+ DELETE = "DELETE FROM %s_data WHERE id=?" % plugin
+ con = None
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute(DELETE, (idval,))
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to delete %s data: [%s]" % (plugin, e))
+ raise
+ finally:
+ if con:
+ con.close()
+
+ def wipe_data(self, plugin):
+ # Try to backup old data first, just in case
+ try:
+ con = sqlite3.connect(self._admin_dbname)
+ cur = con.cursor()
+ cur.execute("DROP TABLE IF EXISTS " + plugin + "_data")
+ cur.execute("CREATE TABLE " + plugin + "_data"
+ "(id INTEGER, name TEXT, value TEXT)")
+ con.commit()
+ except sqlite3.Error, e:
+ if con:
+ con.rollback()
+ cherrypy.log.error("Failed to wipe %s data: [%s]" % (plugin, e))
+ raise
+ finally:
+ if con:
+ con.close()