tell.py and sqlite locks

[edwardslabs]

tell.py does a db query on every message to see if there is a tell to send to someone. This is an expensive operation and it might be a better to populate a cache variable with {conn:nick} or just a list of nicks that have unread tells and then if the nick talking does have an unread tell the db query can be made.

I am seeing rather frequent sqlalchemy/sqlite errors reporting the database is locked, similar to the following:

Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: database is locked

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user/ircbots/bot/cloudbot/plugin.py", line 376, in _execute_hook
    out = yield from self.bot.loop.run_in_executor(None, self._execute_hook_threaded, hook, event)
  File "/usr/lib/python3.4/asyncio/futures.py", line 388, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.4/asyncio/tasks.py", line 285, in _wakeup
    value = future.result()
  File "/usr/lib/python3.4/asyncio/futures.py", line 277, in result
    raise self._exception
  File "/usr/lib/python3.4/concurrent/futures/thread.py", line 54, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/home/user/ircbots/bot/cloudbot/plugin.py", line 340, in _execute_hook_threaded
    return hook.function(*parameters)
  File "/home/user/ircbots/bot/plugins/tell.py", line 85, in tellinput
    tells = get_unread(db, conn.name, nick)
  File "/home/user/ircbots/bot/plugins/tell.py", line 30, in get_unread
    return db.execute(query).fetchall()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 991, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) database is locked 'SELECT tells.sender, tells.message, tells.time_sent \nFROM tells \nWHERE tells.connection = ? AND tells.target = ? AND tells.is_read = ? ORDER BY tells.time_sent' ('connection', 'creatif_righter', 0)