changeset 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 (2017-02-06)
parents be96beb7ca14
children 1d3f73e065e1
files src/memory/sqlite.py
diffstat 1 files changed, 96 insertions(+), 33 deletions(-) [+]
line wrap: on
line diff
--- 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 = {}