X-Git-Url: http://git.cascardo.info/?p=cascardo%2Fipsilon.git;a=blobdiff_plain;f=ipsilon%2Futil%2Fdata.py;h=7d0e0ff5f8db927c523c855308d34c26594b4afc;hp=15bf5b774662f068999699efa4e166cfe974c1d8;hb=71edd1146556eecb9f9375da46313e94bf82874c;hpb=e34f21377d30704c342abb24a3a84c27fd8cc957 diff --git a/ipsilon/util/data.py b/ipsilon/util/data.py index 15bf5b7..7d0e0ff 100755 --- a/ipsilon/util/data.py +++ b/ipsilon/util/data.py @@ -20,6 +20,8 @@ import os import sqlite3 import cherrypy +from random import randint +import sys class Store(object): @@ -29,6 +31,24 @@ 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 @@ -65,13 +85,7 @@ class Store(object): 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 @@ -104,10 +118,315 @@ class Store(object): 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()