Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 2357:fa43e285df1d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/sqlite): better stability: - timeout increased to 15 s - if a query fail, it's retried up to 5 times - foreign key PRAGMA is executed on every new connection object (should fix non working foreign keys) those are Q&D workarounds for issues with Sqlite, before switching to a higher level module to handle it correctly.
author Goffi <goffi@goffi.org>
date Thu, 21 Sep 2017 21:53:28 +0200
parents ea41cf1e6d29
children 71b10dd7a13a
comparison
equal deleted inserted replaced
2356:6084aa557425 2357:fa43e285df1d
104 104
105 105
106 class ConnectionPool(adbapi.ConnectionPool): 106 class ConnectionPool(adbapi.ConnectionPool):
107 # Workaround to avoid IntegrityError causing (i)pdb to be launched in debug mode 107 # Workaround to avoid IntegrityError causing (i)pdb to be launched in debug mode
108 def _runQuery(self, trans, *args, **kw): 108 def _runQuery(self, trans, *args, **kw):
109 retry = kw.pop('query_retry', 6)
109 try: 110 try:
110 trans.execute(*args, **kw) 111 trans.execute(*args, **kw)
111 except sqlite3.IntegrityError as e: 112 except sqlite3.IntegrityError as e:
112 raise failure.Failure(e) 113 raise failure.Failure(e)
114 except Exception as e:
115 # FIXME: in case of error, we retry a couple of times
116 # this is a workaround, we need to move to better
117 # Sqlite integration, probably with high level library
118 retry -= 1
119 if retry == 0:
120 log.error(_(u'too many db tries, we abandon! Error message: {msg}').format(
121 msg = e))
122 raise e
123 log.warning(_(u'exception while running query, retrying ({try_}): {msg})').format(
124 try_ = 6 - retry,
125 msg = e))
126 kw['query_retry'] = retry
127 return self._runQuery(trans, *args, **kw)
113 return trans.fetchall() 128 return trans.fetchall()
114 129
115 130
116 class SqliteStorage(object): 131 class SqliteStorage(object):
117 """This class manage storage with Sqlite database""" 132 """This class manage storage with Sqlite database"""
128 new_base = not os.path.exists(db_filename) # do we have to create the database ? 143 new_base = not os.path.exists(db_filename) # do we have to create the database ?
129 if new_base: # the dir may not exist if it's not the XDG recommended one 144 if new_base: # the dir may not exist if it's not the XDG recommended one
130 dir_ = os.path.dirname(db_filename) 145 dir_ = os.path.dirname(db_filename)
131 if not os.path.exists(dir_): 146 if not os.path.exists(dir_):
132 os.makedirs(dir_, 0700) 147 os.makedirs(dir_, 0700)
133 self.dbpool = ConnectionPool("sqlite3", db_filename, check_same_thread=False) 148
134 149 def foreignKeysOn(sqlite):
135 # init_defer is the initialisation deferred, initialisation is ok when all its callbacks have been done 150 sqlite.execute('PRAGMA foreign_keys = ON')
136 # XXX: foreign_keys activation doesn't seem to work, probably because of the multi-threading 151
137 # All the requests that need to use this feature should be run with runInteraction instead, 152 self.dbpool = ConnectionPool("sqlite3", db_filename, cp_openfun=foreignKeysOn, check_same_thread=False, timeout=15)
138 # so you can set the PRAGMA as it is done in self.deleteProfile
139 init_defer = self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: log.error(_("Can't activate foreign keys")))
140 153
141 def getNewBaseSql(): 154 def getNewBaseSql():
142 log.info(_("The database is new, creating the tables")) 155 log.info(_("The database is new, creating the tables"))
143 database_creation = ["PRAGMA user_version=%d" % CURRENT_DB_VERSION] 156 database_creation = ["PRAGMA user_version=%d" % CURRENT_DB_VERSION]
144 database_creation.extend(Updater.createData2Raw(DATABASE_SCHEMAS['current']['CREATE'])) 157 database_creation.extend(Updater.createData2Raw(DATABASE_SCHEMAS['current']['CREATE']))
155 return defer.succeed(None) 168 return defer.succeed(None)
156 log.debug(u"===== COMMITTING STATEMENTS =====\n%s\n============\n\n" % '\n'.join(statements)) 169 log.debug(u"===== COMMITTING STATEMENTS =====\n%s\n============\n\n" % '\n'.join(statements))
157 d = self.dbpool.runInteraction(self._updateDb, tuple(statements)) 170 d = self.dbpool.runInteraction(self._updateDb, tuple(statements))
158 return d 171 return d
159 172
173 # init_defer is the initialisation deferred, initialisation is ok when all its callbacks have been done
174
175 init_defer = defer.succeed(None)
176
160 init_defer.addCallback(lambda ignore: getNewBaseSql() if new_base else getUpdateSql()) 177 init_defer.addCallback(lambda ignore: getNewBaseSql() if new_base else getUpdateSql())
161 init_defer.addCallback(commitStatements) 178 init_defer.addCallback(commitStatements)
162 179
163 def fillProfileCache(ignore): 180 def fillProfileCache(ignore):
164 d = self.dbpool.runQuery("SELECT profile_id, entry_point FROM components").addCallback(self._cacheComponentsAndProfiles) 181 d = self.dbpool.runQuery("SELECT profile_id, entry_point FROM components").addCallback(self._cacheComponentsAndProfiles)
251 log.error(_(u"Can't delete profile [%s]") % name) 268 log.error(_(u"Can't delete profile [%s]") % name)
252 return failure_ 269 return failure_
253 270
254 def delete(txn): 271 def delete(txn):
255 profile_id = self.profiles.pop(name) 272 profile_id = self.profiles.pop(name)
256 txn.execute("PRAGMA foreign_keys = ON")
257 txn.execute("DELETE FROM profiles WHERE name = ?", (name,)) 273 txn.execute("DELETE FROM profiles WHERE name = ?", (name,))
258 # FIXME: the following queries should be done by the ON DELETE CASCADE 274 # FIXME: the following queries should be done by the ON DELETE CASCADE
259 # but it seems they are not, so we explicitly do them by security 275 # but it seems they are not, so we explicitly do them by security
260 # this need more investigation 276 # this need more investigation
261 txn.execute("DELETE FROM history WHERE profile_id = ?", (profile_id,)) 277 txn.execute("DELETE FROM history WHERE profile_id = ?", (profile_id,))