libervia-backend: src/memory/sqlite.py comparison

comparison src/memory/sqlite.py @ 2143:c3cac21157d4

memory (sqlite): introduced component table, schema updated: components are special profiles, entry_point will be the plugin to launch when they are connected.

author	Goffi <goffi@goffi.org>
date	Tue, 07 Feb 2017 00:15:03 +0100
parents	b536dd121da1
children	1d3f73e065e1

comparison

equal deleted inserted replaced

-:be96beb7ca14
+:c3cac21157d4
 import os.path
 import cPickle as pickle
 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
 #      - x(int) is for update needed between x-1 and x. All number are needed between y and z to do an update
 #        e.g.: if CURRENT_DB_VERSION is 6, 'current' is the actuel DB, and to update from version 3, numbers 4, 5 and 6 are needed
 #      a 'current' data dict can contains the keys:
 #      - '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",
 "timestamp DATETIME NOT NULL", "received_timestamp DATETIME", # XXX: timestamp is the time when the message was emitted. If received time stamp is not NULL, the message was delayed and timestamp is the declared value (and received_timestamp the time of reception)
 "type TEXT", "extra BLOB"),
 ("'normal'",),
 ("'info'",) # info is not standard, but used to keep track of info like join/leave in a MUC
 )),
 )),
 },
+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'
 },
 1:         {'cols create': {'history': ('extra BLOB',)},
 class SqliteStorage(object):
 """This class manage storage with Sqlite database"""
 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)
 log.info(_("Connecting database"))
 new_base = not os.path.exists(db_filename)  # do we have to create the database ?
 init_defer.addCallback(lambda ignore: getNewBaseSql() if new_base else getUpdateSql())
 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)
 def _updateDb(self, interaction, statements):
 for statement in statements:
 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_
 def getProfilesList(self):
 """"Return list of all registered profiles"""
 return self.profiles.keys()
 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, ))
 def profile_created(profile_id):
 d.addCallback(profile_created)
 return d
 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_
 def delete(txn):
 #        this need more investigation
 txn.execute("DELETE FROM history WHERE profile_id = ?", (profile_id,))
 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)
 d.addCallback(lambda ignore: log.info(_("Profile [%s] deleted") % name))
 d.addErrback(deletionError)
 return d
 #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:
 category, name, value = param
 params_gen[(category, name)] = value
 log.debug(_(u"loading general parameters from database"))
 return self.dbpool.runQuery("SELECT category,name,value FROM param_gen").addCallback(fillParams)
 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:
 category, name, value = param
 params_ind[(category, name)] = value
 d.addCallback(fillParams)
 return d
 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
 def setGenParam(self, category, name, value):
 d.addErrback(lambda ignore: log.error(_(u"Can't set general parameter (%(category)s/%(name)s) in database" % {"category": category, "name": name})))
 return d
 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
 #History
 return d
 #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:
 key, value = private
 private_gen[key] = value
 d = self.dbpool.runQuery("SELECT key,value FROM private_gen WHERE namespace=?", (namespace, )).addCallback(fillPrivates)
 return d.addErrback(lambda x: log.debug(_(u"No data present in database for namespace %s") % namespace))
 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:
 key, value = private
 private_ind[key] = value
 d.addCallback(fillPrivates)
 return d.addErrback(lambda x: log.debug(_(u"No data present in database for namespace %s") % namespace))
 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})))
 return d
 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})))
 return d
 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})))
 return d
 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})))
 return d
 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:
 key, value = private
 private_gen[key] = pickle.loads(str(value))
 d = self.dbpool.runQuery("SELECT key,value FROM private_gen_bin WHERE namespace=?", (namespace, )).addCallback(fillPrivates)
 return d.addErrback(lambda x: log.debug(_(u"No binary data present in database for namespace %s") % namespace))
 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:
 key, value = private
 private_ind[key] = pickle.loads(str(value))
 d.addCallback(fillPrivates)
 return d.addErrback(lambda x: log.debug(_(u"No binary data present in database for namespace %s") % namespace))
 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})))
 return d
 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})))
 return d
 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})))
 return d
 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})))
 return d
 ##Helper methods##
 self._sat_version = sat_version
 self.dbpool = dbpool
 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])
 return d
 @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()
 local_sch = self.rawStatements2data(raw_local_sch)
 current_sch = DATABASE_SCHEMAS['current']['CREATE']
 defer.returnValue(update_raw)
 @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:
 defs, constraints = data[table]
 assert isinstance(defs, tuple)
 return ret
 @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:
 values_tuple = data[table]
 assert isinstance(values_tuple, tuple)
 ret.append(Updater.INSERT_SQL % (table, ', '.join(values)))
 return ret
 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
 """
 hash_ = hashlib.sha1()
 hash_.update("%s:%s:%s" % (table, stmnts2str(col_defs), stmnts2str(col_constr)))
 return hash_.digest()
 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:
 if not create_statement.startswith("CREATE TABLE "):
 log.warning("Unexpected statement, ignoring it")
 - 'create': dictionary of tables to create
 - 'delete': tuple of tables to delete
 - '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 = {}
 create_cols_data = {}
 modify_cols_data = {}

Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 2143:c3cac21157d4