# HG changeset patch # User Goffi # Date 1486422903 -3600 # Node ID c3cac21157d45b4b730f7b57a32d2a3aedf1b859 # Parent be96beb7ca14c56da600278dd326ced47dac195f memory (sqlite): introduced component table, schema updated: components are special profiles, entry_point will be the plugin to launch when they are connected. diff -r be96beb7ca14 -r c3cac21157d4 src/memory/sqlite.py --- a/src/memory/sqlite.py Mon Feb 06 22:54:43 2017 +0100 +++ b/src/memory/sqlite.py Tue Feb 07 00:15:03 2017 +0100 @@ -34,7 +34,7 @@ import hashlib import sqlite3 -CURRENT_DB_VERSION = 3 +CURRENT_DB_VERSION = 4 # XXX: DATABASE schemas are used in the following way: # - 'current' key is for the actual database schema, for a new base @@ -44,11 +44,14 @@ # - 'CREATE': it contains an Ordered dict with table to create as keys, and a len 2 tuple as value, where value[0] are the columns definitions and value[1] are the table constraints # - 'INSERT': it contains an Ordered dict with table where values have to be inserted, and many tuples containing values to insert in the order of the rows (#TODO: manage named columns) # an update data dict (the ones with a number) can contains the keys 'create', 'delete', 'cols create', 'cols delete', 'cols modify', 'insert' or 'specific'. See Updater.generateUpdateData for more infos. This method can be used to autogenerate update_data, to ease the work of the developers. +# TODO: this database currently doesn't use indexes, it should DATABASE_SCHEMAS = { "current": {'CREATE': OrderedDict(( ('profiles', (("id INTEGER PRIMARY KEY ASC", "name TEXT"), ("UNIQUE (name)",))), + ('components', (("profile_id INTEGER PRIMARY KEY", "entry_point TEXT NOT NULL"), + ("FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE",))), ('message_types', (("type TEXT PRIMARY KEY",), tuple())), ('history', (("uid TEXT PRIMARY KEY", "update_uid TEXT", "profile_id INTEGER", "source TEXT", "dest TEXT", "source_res TEXT", "dest_res TEXT", @@ -85,6 +88,8 @@ )), )), }, + 4: {'create': {'components': (('profile_id INTEGER PRIMARY KEY', 'entry_point TEXT NOT NULL'), ('FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE',))} + }, 3: {'specific': 'update_v3' }, 2: {'specific': 'update2raw_v2' @@ -113,7 +118,9 @@ def __init__(self, db_filename, sat_version): """Connect to the given database - @param db_filename: full path to the Sqlite database""" + + @param db_filename: full path to the Sqlite database + """ self.initialized = defer.Deferred() # triggered when memory is fully initialised and ready self.profiles = {} # we keep cache for the profiles (key: profile name, value: profile id) @@ -154,7 +161,7 @@ init_defer.addCallback(commitStatements) def fillProfileCache(ignore): - d = self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback(self._profilesCache) + d = self.dbpool.runQuery("SELECT profile_id, entry_point FROM components").addCallback(self._cacheComponentsAndProfiles) d.chainDeferred(self.initialized) init_defer.addCallback(fillProfileCache) @@ -164,9 +171,21 @@ interaction.execute(statement) #Profiles - def _profilesCache(self, profiles_result): + + def _cacheComponentsAndProfiles(self, components_result): + """Get components results and send requests profiles + + they will be both put in cache in _profilesCache + """ + return self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback( + self._cacheComponentsAndProfiles2, components_result) + + def _cacheComponentsAndProfiles2(self, profiles_result, components): """Fill the profiles cache - @param profiles_result: result of the sql profiles query""" + + @param profiles_result: result of the sql profiles query + """ + self.components = dict(components) for profile in profiles_result: name, id_ = profile self.profiles[name] = id_ @@ -177,13 +196,23 @@ def hasProfile(self, profile_name): """return True if profile_name exists - @param profile_name: name of the profile to check""" + + @param profile_name: name of the profile to check + """ return profile_name in self.profiles + def profileIsComponent(self, profile_name): + try: + return self.profiles[profile_name] in self.components + except KeyError: + raise exceptions.NotFound(u"the requested profile doesn't exists") + def createProfile(self, name): """Create a new profile + @param name: name of the profile - @return: deferred triggered once profile is actually created""" + @return: deferred triggered once profile is actually created + """ def getProfileId(ignore): return self.dbpool.runQuery("SELECT (id) FROM profiles WHERE name = ?", (name, )) @@ -199,8 +228,10 @@ def deleteProfile(self, name): """Delete profile + @param name: name of the profile - @return: deferred triggered once profile is actually deleted""" + @return: deferred triggered once profile is actually deleted + """ def deletionError(failure_): log.error(_(u"Can't delete profile [%s]") % name) return failure_ @@ -216,6 +247,7 @@ txn.execute("DELETE FROM param_ind WHERE profile_id = ?", (profile_id,)) txn.execute("DELETE FROM private_ind WHERE profile_id = ?", (profile_id,)) txn.execute("DELETE FROM private_ind_bin WHERE profile_id = ?", (profile_id,)) + txn.execute("DELETE FROM components WHERE profile_id = ?", (profile_id,)) return None d = self.dbpool.runInteraction(delete) @@ -226,8 +258,10 @@ #Params def loadGenParams(self, params_gen): """Load general parameters + @param params_gen: dictionary to fill - @return: deferred""" + @return: deferred + """ def fillParams(result): for param in result: @@ -238,9 +272,11 @@ def loadIndParams(self, params_ind, profile): """Load individual parameters + @param params_ind: dictionary to fill @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ def fillParams(result): for param in result: @@ -253,10 +289,12 @@ def getIndParam(self, category, name, profile): """Ask database for the value of one specific individual parameter + @param category: category of the parameter @param name: name of the parameter @param profile: %(doc_profile)s - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("SELECT value FROM param_ind WHERE category=? AND name=? AND profile_id=?", (category, name, self.profiles[profile])) d.addCallback(self.__getFirstResult) return d @@ -273,11 +311,13 @@ def setIndParam(self, category, name, value, profile): """Save the individual parameters in database + @param category: category of the parameter @param name: name of the parameter @param value: value to set @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value)) d.addErrback(lambda ignore: log.error(_(u"Can't set individual parameter (%(category)s/%(name)s) for [%(profile)s] in database" % {"category": category, "name": name, "profile": profile}))) return d @@ -467,9 +507,11 @@ #Private values def loadGenPrivates(self, private_gen, namespace): """Load general private values + @param private_gen: dictionary to fill @param namespace: namespace of the values - @return: deferred""" + @return: deferred + """ def fillPrivates(result): for private in result: @@ -481,10 +523,12 @@ def loadIndPrivates(self, private_ind, namespace, profile): """Load individual private values + @param privates_ind: dictionary to fill @param namespace: namespace of the values @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ def fillPrivates(result): for private in result: @@ -497,10 +541,12 @@ def setGenPrivate(self, namespace, key, value): """Save the general private value in database + @param category: category of the privateeter @param key: key of the private value @param value: value to set - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("REPLACE INTO private_gen(namespace,key,value) VALUES (?,?,?)", (namespace, key, value)) d.addErrback(lambda ignore: log.error(_(u"Can't set general private value (%(key)s) [namespace:%(namespace)s] in database" % {"namespace": namespace, "key": key}))) @@ -508,11 +554,13 @@ def setIndPrivate(self, namespace, key, value, profile): """Save the individual private value in database + @param namespace: namespace of the value @param key: key of the private value @param value: value to set @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("REPLACE INTO private_ind(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], value)) d.addErrback(lambda ignore: log.error(_(u"Can't set individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" % {"namespace": namespace, "key": key, "profile": profile}))) @@ -520,9 +568,11 @@ def delGenPrivate(self, namespace, key): """Delete the general private value from database + @param category: category of the privateeter @param key: key of the private value - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("DELETE FROM private_gen WHERE namespace=? AND key=?", (namespace, key)) d.addErrback(lambda ignore: log.error(_(u"Can't delete general private value (%(key)s) [namespace:%(namespace)s] in database" % {"namespace": namespace, "key": key}))) @@ -530,10 +580,12 @@ def delIndPrivate(self, namespace, key, profile): """Delete the individual private value from database + @param namespace: namespace of the value @param key: key of the private value @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("DELETE FROM private_ind WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile])) d.addErrback(lambda ignore: log.error(_(u"Can't delete individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" % {"namespace": namespace, "key": key, "profile": profile}))) @@ -541,9 +593,11 @@ def loadGenPrivatesBinary(self, private_gen, namespace): """Load general private binary values + @param private_gen: dictionary to fill @param namespace: namespace of the values - @return: deferred""" + @return: deferred + """ def fillPrivates(result): for private in result: @@ -555,10 +609,12 @@ def loadIndPrivatesBinary(self, private_ind, namespace, profile): """Load individual private binary values + @param privates_ind: dictionary to fill @param namespace: namespace of the values @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ def fillPrivates(result): for private in result: @@ -571,10 +627,12 @@ def setGenPrivateBinary(self, namespace, key, value): """Save the general private binary value in database + @param category: category of the privateeter @param key: key of the private value @param value: value to set - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("REPLACE INTO private_gen_bin(namespace,key,value) VALUES (?,?,?)", (namespace, key, sqlite3.Binary(pickle.dumps(value, 0)))) d.addErrback(lambda ignore: log.error(_(u"Can't set general private binary value (%(key)s) [namespace:%(namespace)s] in database" % {"namespace": namespace, "key": key}))) @@ -582,11 +640,13 @@ def setIndPrivateBinary(self, namespace, key, value, profile): """Save the individual private binary value in database + @param namespace: namespace of the value @param key: key of the private value @param value: value to set @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("REPLACE INTO private_ind_bin(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], sqlite3.Binary(pickle.dumps(value, 0)))) d.addErrback(lambda ignore: log.error(_(u"Can't set individual binary private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" % {"namespace": namespace, "key": key, "profile": profile}))) @@ -594,9 +654,11 @@ def delGenPrivateBinary(self, namespace, key): """Delete the general private binary value from database + @param category: category of the privateeter @param key: key of the private value - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("DELETE FROM private_gen_bin WHERE namespace=? AND key=?", (namespace, key)) d.addErrback(lambda ignore: log.error(_(u"Can't delete general private binary value (%(key)s) [namespace:%(namespace)s] in database" % {"namespace": namespace, "key": key}))) @@ -604,10 +666,12 @@ def delIndPrivateBinary(self, namespace, key, profile): """Delete the individual private binary value from database + @param namespace: namespace of the value @param key: key of the private value @param profile: a profile which *must* exist - @return: deferred""" + @return: deferred + """ d = self.dbpool.runQuery("DELETE FROM private_ind_bin WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile])) d.addErrback(lambda ignore: log.error(_(u"Can't delete individual private binary value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" % {"namespace": namespace, "key": key, "profile": profile}))) @@ -638,23 +702,23 @@ def getLocalVersion(self): """ Get local database version + @return: version (int) - """ return self.dbpool.runQuery("PRAGMA user_version").addCallback(lambda ret: int(ret[0][0])) def _setLocalVersion(self, version): """ Set local database version + @param version: version (int) @return: deferred - """ return self.dbpool.runOperation("PRAGMA user_version=%d" % version) def getLocalSchema(self): """ return raw local schema + @return: list of strings with CREATE sql statements for local database - """ d = self.dbpool.runQuery("select sql from sqlite_master where type = 'table'") d.addCallback(lambda result: [row[0] for row in result]) @@ -663,8 +727,8 @@ @defer.inlineCallbacks def checkUpdates(self): """ Check if a database schema/content update is needed, according to DATABASE_SCHEMAS + @return: deferred which fire a list of SQL update statements, or None if no update is needed - """ local_version = yield self.getLocalVersion() raw_local_sch = yield self.getLocalSchema() @@ -718,9 +782,9 @@ @staticmethod def createData2Raw(data): """ Generate SQL statements from statements data + @param data: dictionary with table as key, and statements data in tuples as value @return: list of strings with raw statements - """ ret = [] for table in data: @@ -733,9 +797,9 @@ @staticmethod def insertData2Raw(data): """ Generate SQL statements from statements data + @param data: dictionary with table as key, and statements data in tuples as value @return: list of strings with raw statements - """ ret = [] for table in data: @@ -748,8 +812,8 @@ def statementHash(self, data): """ Generate hash of template data + useful to compare schemas - @param data: dictionary of "CREATE" statement, with tables names as key, and tuples of (col_defs, constraints) as values @return: hash as string @@ -768,9 +832,9 @@ def rawStatements2data(self, raw_statements): """ separate "CREATE" statements into dictionary/tuples data + @param raw_statements: list of CREATE statements as strings @return: dictionary with table names as key, and a (col_defs, constraints) tuple - """ schema_dict = {} for create_statement in raw_statements: @@ -806,7 +870,6 @@ - 'cols create': dictionary of columns to create (table as key, tuple of columns to create as value) - 'cols delete': dictionary of columns to delete (table as key, tuple of columns to delete as value) - 'cols modify': dictionary of columns to modify (table as key, tuple of old columns to transfert as value). With this table, a new table will be created, and content from the old table will be transfered to it, only cols specified in the tuple will be transfered. - """ create_tables_data = {}