From 9e45a0dda4cfa26b531a423fc8b1c4b10d382a0f Mon Sep 17 00:00:00 2001 From: Simo Sorce Date: Mon, 8 Sep 2014 15:55:34 -0400 Subject: [PATCH] Refactor the data store a bit Reduce code duplication, and clearly separates admin and user dbs. Move plugin wrapper away and let plugin code use native functions. This patch also changes the indexed data to use a uuid and assumes 2 identical uuid cannot be created concurrently. Signed-off-by: Simo Sorce Reviewed-by: Patrick Uiterwijk --- ipsilon/admin/common.py | 5 +- ipsilon/install/ipsilon-server-install | 4 +- ipsilon/ipsilon | 6 +- ipsilon/util/data.py | 536 +++++++++++-------------- ipsilon/util/plugin.py | 24 +- ipsilon/util/user.py | 6 +- 6 files changed, 251 insertions(+), 330 deletions(-) diff --git a/ipsilon/admin/common.py b/ipsilon/admin/common.py index 2c8ff89..85bd5fd 100755 --- a/ipsilon/admin/common.py +++ b/ipsilon/admin/common.py @@ -18,7 +18,6 @@ # along with this program. If not, see . import cherrypy -from ipsilon.util.data import Store from ipsilon.util.page import Page from ipsilon.util.page import admin_protect @@ -83,9 +82,7 @@ class AdminPluginPage(Page): if len(new_values) != 0: # First we try to save in the database try: - store = Store() - store.save_plugin_config(self.facility, - self._obj.name, new_values) + self._obj.save_plugin_config(self.facility, new_values) message = "New configuration saved." message_type = "success" except Exception: # pylint: disable=broad-except diff --git a/ipsilon/install/ipsilon-server-install b/ipsilon/install/ipsilon-server-install index e088b39..598a025 100755 --- a/ipsilon/install/ipsilon-server-install +++ b/ipsilon/install/ipsilon-server-install @@ -21,7 +21,7 @@ from ipsilon.login.common import LoginMgrsInstall from ipsilon.info.common import InfoProviderInstall from ipsilon.providers.common import ProvidersInstall from ipsilon.helpers.common import EnvHelpersInstall -from ipsilon.util.data import Store +from ipsilon.util.data import UserStore from ipsilon.tools import files import ConfigParser import argparse @@ -144,7 +144,7 @@ def install(plugins, args): users_db = cherrypy.config['user.prefs.db'] if os.path.exists(users_db): shutil.move(users_db, '%s.backup.%s' % (users_db, now)) - db = Store() + db = UserStore() db.save_user_preferences(args['admin_user'], {'is_admin': 1}) logger.info('Configuring environment helpers') diff --git a/ipsilon/ipsilon b/ipsilon/ipsilon index 8cabc85..9700106 100755 --- a/ipsilon/ipsilon +++ b/ipsilon/ipsilon @@ -23,7 +23,7 @@ import glob import os import atexit import cherrypy -from ipsilon.util.data import Store +from ipsilon.util.data import AdminStore from ipsilon.util import page from ipsilon.root import Root from jinja2 import Environment, FileSystemLoader @@ -55,8 +55,8 @@ cherrypy.config.update(cfgfile) nuke_session_locks() -datastore = Store() -admin_config = datastore.get_admin_config() +datastore = AdminStore() +admin_config = datastore.load_config() for option in admin_config: cherrypy.config[option] = admin_config[option] diff --git a/ipsilon/util/data.py b/ipsilon/util/data.py index 7d0e0ff..bdf93e7 100755 --- a/ipsilon/util/data.py +++ b/ipsilon/util/data.py @@ -20,407 +20,307 @@ import os import sqlite3 import cherrypy -from random import randint -import sys - - -class Store(object): - - def __init__(self, path=None): - if path is None: - self._path = os.getcwd() +from ipsilon.util.log import Log +import uuid + + +OPTIONS_COLUMNS = ['name', 'option', 'value'] +UNIQUE_DATA_COLUMNS = ['uuid', 'name', 'value'] + + +class Store(Log): + + def __init__(self, name): + self._dbname = name + + def _build_where(self, kvfilter, kvout): + where = "" + sep = "WHERE" + for k in kvfilter: + mk = "where_%s" % k + kvout[mk] = kvfilter[k] + where += "%s %s=:%s" % (sep, k, mk) + sep = " AND" + return where + + def _build_select(self, table, kvfilter=None, kvout=None, columns=None): + SELECT = "SELECT %(cols)s FROM %(table)s %(where)s" + cols = "*" + if columns: + cols = ",".join(columns) + where = "" + if kvfilter is not None: + where = self._build_where(kvfilter, kvout) + return SELECT % {'table': table, 'cols': cols, 'where': where} + + def _select(self, cursor, table, kvfilter=None, columns=None): + kv = dict() + select = self._build_select(table, kvfilter, kv, columns) + cursor.execute(select, kv) + return cursor.fetchall() + + def _create(self, cursor, table, columns): + CREATE = "CREATE TABLE IF NOT EXISTS %(table)s(%(cols)s)" + cols = ",".join(columns) + create = CREATE % {'table': table, 'cols': cols} + cursor.execute(create) + + def _update(self, cursor, table, values, kvfilter): + UPDATE = "UPDATE %(table)s SET %(setval)s %(where)s" + kv = dict() + + setval = "" + sep = "" + for k in values: + mk = "setval_%s" % k + kv[mk] = values[k] + setval += "%s%s=:%s" % (sep, k, mk) + sep = " , " + + where = self._build_where(kvfilter, kv) + + update = UPDATE % {'table': table, 'setval': setval, 'where': where} + cursor.execute(update, kv) + + def _insert(self, cursor, table, values): + INSERT = "INSERT INTO %(table)s VALUES(%(values)s)" + vals = "" + sep = "" + for _ in values: + vals += "%s?" % sep + sep = "," + insert = INSERT % {'table': table, 'values': vals} + cursor.execute(insert, values) + + def _delete(self, cursor, table, kvfilter): + DELETE = "DELETE FROM %(table)s %(where)s" + kv = dict() + where = self._build_where(kvfilter, kv) + delete = DELETE % {'table': table, 'where': where} + cursor.execute(delete, kv) + + def _row_to_dict_tree(self, data, row): + name = row[0] + if len(row) > 2: + if name not in data: + data[name] = dict() + d2 = data[name] + self._row_to_dict_tree(d2, row[1:]) 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 - rows = [] - try: - con = sqlite3.connect(dbname) - cur = con.cursor() - cur.executescript(""" - CREATE TABLE IF NOT EXISTS config(name TEXT, value TEXT) - """) - cur.execute("SELECT * FROM config") - rows = cur.fetchall() - con.commit() - except sqlite3.Error, e: - if con: - con.rollback() - cherrypy.log.error("Failed to load config: [%s]" % e) - finally: - if con: - con.close() - - conf = {} - for row in rows: - if row[0] in conf: - # multivalued - if conf[row[0]] is list: - conf[row[0]].append(row[1]) + value = row[1] + if name in data: + if data[name] is list: + data[name].append(value) else: - v = conf[row[0]] - conf[row[0]] = [v, row[1]] + v = data[name] + data[name] = [v, value] else: - conf[row[0]] = row[1] + data[name] = value - return conf - - def get_admin_config(self): - return self._load_config(self._admin_dbname) + def _rows_to_dict_tree(self, rows): + data = dict() + for r in rows: + self._row_to_dict_tree(data, r) + return data - def _load_user_prefs(self, dbname, user): + def _load_data(self, table, columns, kvfilter=None): con = None rows = [] try: - con = sqlite3.connect(dbname) + con = sqlite3.connect(self._dbname) cur = con.cursor() - cur.executescript(""" - CREATE TABLE IF NOT EXISTS users(name TEXT, - option TEXT, - value TEXT) - """) - cur.execute("SELECT option, value FROM users " - "where name = '%s'" % user) - rows = cur.fetchall() + self._create(cur, table, columns) + rows = self._select(cur, table, kvfilter) con.commit() except sqlite3.Error, e: if con: con.rollback() - cherrypy.log.error("Failed to load %s's prefs from " - "%s: [%s]" % (user, dbname, e)) + self.error("Failed to load data for table %s: [%s]" % (table, e)) finally: if con: con.close() - conf = {} - for row in rows: - conf[row[0]] = row[1] + return self._rows_to_dict_tree(rows) - return conf + def load_config(self): + table = 'config' + columns = ['name', 'value'] + return self._load_data(table, columns) - def get_user_preferences(self, user): - return self._load_user_prefs(self._user_dbname, user) + def load_options(self, table, name=None): + kvfilter = dict() + if name: + kvfilter['name'] = name + options = self._load_data(table, OPTIONS_COLUMNS, kvfilter) + if name and name in options: + return options[name] + return options - 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(?,?,?)" + def save_options(self, table, name, options): + curvals = dict() con = None try: - con = sqlite3.connect(self._user_dbname) + con = sqlite3.connect(self._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,)): + self._create(cur, table, OPTIONS_COLUMNS) + rows = self._select(cur, table, {'name': name}, + ['option', 'value']) + for row in rows: curvals[row[0]] = row[1] - for name in options: - if name in curvals: - cur.execute(UPDATE, (options[name], user, name)) + for opt in options: + if opt in curvals: + self._update(cur, table, + {'value': options[opt]}, + {'name': name, 'option': opt}) else: - cur.execute(INSERT, (user, name, options[name])) + self._insert(cur, table, (name, opt, options[opt])) con.commit() except sqlite3.Error, e: if con: con.rollback() - cherrypy.log.error("Failed to store config: [%s]" % e) + self.error("Failed to store config: [%s]" % e) raise finally: if con: con.close() - def get_plugins_config(self, facility): - con = None - rows = [] + def delete_options(self, table, name, options=None): + kvfilter = {'name': name} try: - con = sqlite3.connect(self._admin_dbname) + con = sqlite3.connect(self._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() + self._create(cur, table, OPTIONS_COLUMNS) + if options is None: + self._delete(cur, table, kvfilter) + else: + for opt in options: + kvfilter['option'] = opt + self._delete(cur, table, kvfilter) con.commit() except sqlite3.Error, e: if con: con.rollback() - cherrypy.log.error("Failed to load %s config: [%s]" % (facility, - e)) + self.error("Failed to delete from %s: [%s]" % (table, e)) + raise 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): + def new_unique_data(self, table, data): con = None - rows = [] try: - con = sqlite3.connect(self._admin_dbname) + con = sqlite3.connect(self._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() + self._create(cur, table, UNIQUE_DATA_COLUMNS) + newid = str(uuid.uuid4()) + for name in data: + self._insert(cur, table, (newid, name, data[name])) 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) + cherrypy.log.error("Failed to store %s data: [%s]" % (table, e)) 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 + return newid + + def get_unique_data(self, table, uuidval=None, name=None, value=None): + kvfilter = dict() + if uuidval: + kvfilter['uuid'] = uuidval + if name: + kvfilter['name'] = name + if value: + kvfilter['value'] = value + return self._load_data(table, UNIQUE_DATA_COLUMNS, kvfilter) + + def save_unique_data(self, table, data): + curvals = dict() con = None try: - con = sqlite3.connect(self._admin_dbname) + con = sqlite3.connect(self._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])) + self._create(cur, table, UNIQUE_DATA_COLUMNS) + for uid in data: + curvals = dict() + rows = self._select(cur, table, {'uuid': uid}, + ['name', 'value']) + for r in rows: + curvals[r[0]] = r[1] - 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() + datum = data[uid] + for name in datum: + if name in curvals: + self._update(cur, table, + {'value': datum[name]}, + {'uuid': uid, 'name': name}) + else: + self._insert(cur, table, (uid, name, datum[name])) - 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)) + self.error("Failed to store data in %s: [%s]" % (table, e)) raise finally: if con: con.close() - def get_data(self, plugin, idval=None, name=None, value=None): + def del_unique_data(self, table, uuidval): + kvfilter = {'uuid': uuidval} 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) + con = sqlite3.connect(self._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() + self._delete(cur, table, kvfilter) 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])) + self.error("Failed to delete data from %s: [%s]" % (table, e)) 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 +class AdminStore(Store): - 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] + def __init__(self, path=None): + if path is None: + self._path = os.getcwd() + else: + self._path = path + self._name = None + if 'admin.config.db' in cherrypy.config: + self._name = cherrypy.config['admin.config.db'] + if not self._name: + self._name = os.path.join(self._path, 'adminconfig.sqlite') + super(AdminStore, self).__init__(self._name) - 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])) + def get_data(self, plugin, idval=None, name=None, value=None): + return self.get_unique_data(plugin+"_data", idval, name, value) - 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 save_data(self, plugin, data): + return self.save_unique_data(plugin+"_data", data) 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() + table = plugin+"_data" + return self.new_unique_data(table, datum) 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() + table = plugin+"_data" + return self.del_unique_data(table, idval) def wipe_data(self, plugin): + table = plugin+"_data" # Try to backup old data first, just in case try: - con = sqlite3.connect(self._admin_dbname) + con = sqlite3.connect(self._dbname) cur = con.cursor() - cur.execute("DROP TABLE IF EXISTS " + plugin + "_data") - cur.execute("CREATE TABLE " + plugin + "_data" - "(id INTEGER, name TEXT, value TEXT)") + cur.execute("DROP TABLE IF EXISTS " + table) + self._create(cur, table, UNIQUE_DATA_COLUMNS) con.commit() except sqlite3.Error, e: if con: @@ -430,3 +330,21 @@ class Store(object): finally: if con: con.close() + + +class UserStore(Store): + + def __init__(self, path=None): + if path is None: + self._path = os.getcwd() + else: + self._path = path + self._name = None + if 'user.prefs.db' in cherrypy.config: + self._name = cherrypy.config['user.prefs.db'] + if not self._name: + self._name = os.path.join(self._path, 'userprefs.sqlite') + super(UserStore, self).__init__(self._name) + + def save_user_preferences(self, user, options): + return self.save_options('users', user, options) diff --git a/ipsilon/util/plugin.py b/ipsilon/util/plugin.py index edfda16..903f548 100755 --- a/ipsilon/util/plugin.py +++ b/ipsilon/util/plugin.py @@ -21,7 +21,7 @@ import os import imp import cherrypy import inspect -from ipsilon.util.data import Store +from ipsilon.util.data import AdminStore from ipsilon.util.log import Log @@ -72,11 +72,15 @@ class Plugins(object): class PluginLoader(object): def __init__(self, baseobj, facility, plugin_type): - (whitelist, config) = Store().get_plugins_config(facility) + config = AdminStore().load_options(facility) + cherrypy.log('LOAD: %s\n' % repr(config)) + whitelist = [] + if 'global' in config: + sec = config['global'] + if 'order' in sec: + whitelist = sec['order'].split(',') if cherrypy.config.get('debug', False): cherrypy.log('[%s] %s: %s' % (facility, whitelist, config)) - if whitelist is None: - whitelist = [] if config is None: config = dict() @@ -109,7 +113,7 @@ class PluginObject(Log): self.name = None self._config = None self._options = None - self._data = Store() + self._data = AdminStore() def get_config_desc(self): """ The configuration description is a dictionary that provides @@ -146,10 +150,12 @@ class PluginObject(Log): self._config[option] = value def get_plugin_config(self, facility): - return self._data.get_plugin_config(facility, self.name) + return self._data.load_options(facility, self.name) - def save_plugin_config(self, facility): - self._data.save_plugin_config(facility, self.name, self._config) + def save_plugin_config(self, facility, config=None): + if config is None: + config = self._config + self._data.save_options(facility, self.name, config) def get_data(self, idval=None, name=None, value=None): return self._data.get_data(self.name, idval=idval, name=name, @@ -165,7 +171,7 @@ class PluginObject(Log): self._data.del_datum(self.name, idval) def wipe_config_values(self, facility): - self._data.wipe_plugin_config(facility, self.name) + self._data.delete_options(facility, self.name, None) def wipe_data(self): self._data.wipe_data(self.name) diff --git a/ipsilon/util/user.py b/ipsilon/util/user.py index 2731d3c..47cb23c 100755 --- a/ipsilon/util/user.py +++ b/ipsilon/util/user.py @@ -17,7 +17,7 @@ # You should have received a copy of the GNU General Public License # along with this program. If not, see . -from ipsilon.util.data import Store +from ipsilon.util.data import UserStore from ipsilon.util.log import Log import cherrypy @@ -39,8 +39,8 @@ class User(object): self.name = username def _get_user_data(self, username): - store = Store() - return store.get_user_preferences(username) + store = UserStore() + return store.load_options('users', username) def reset(self): self.name = None -- 2.20.1