Before we start designing a database-driven tasklist application, let's first familiarize ourselves with SQLite in the context of a seemingly much simpler set of requirements: storing username/password combinations in a database and refactoring the Logon class to interact with this database.
The functional requirements are deceptively simple: to verify whether a username/password combination is valid, all we have to do is verify that the username/password combination given is present in the table of usernames and passwords. Such a table consists of two columns, one named username and the other named password. As it is never a good idea to store a collection of passwords in plaintext, we encrypt the passwords with a hash function so even if the password database is compromised, the bad guys will have a difficult time retrieving the passwords. This means, of course, that we have to hash a given password with the same hash function before comparing it to the stored password for the username but that doesn't add much complexity.
What does add complexity is the fact that CherryPy is multi-threaded, meaning that CherryPy consists of multiple lightweight processes accessing the same data. And although the developers of SQLite maintain the opinion that threads are evil (http://www.sqlite.org/faq.html#q6), threads make perfect sense in situations where a lot of time in the application is spent on waiting. This certainly is the case in web applications that spend a lot of time waiting for network traffic to complete, even in this time of broadband connections. The most effective way of using this waiting time is to enable a different thread to serve another connection so more users might enjoy a better interactive experience.
Note
Hash functions (or Cryptographic hash functions to be more specific) convert any input string to an output string of limited length in such a way that it is very unlikely that two input strings that are different produce the same output. Also, conversion from input to output is a one way operation or at least it will cost a large amount of computing power to construct the input from the output. There are many useful hash functions known, the most popular ones are available in Python's hashlib module. The specific hash function we use here is called SHA1.
More about hashing can be found in the Python documentation at http://docs.python.org/py3k/library/hashlib.html, or on Wikipedia at http://en.wikipedia.org/wiki/Cryptographic_hash_function.
However, in SQLite, the connection object cannot be shared among threads. This doesn't mean that that we cannot use SQLite in a multi-threaded environment (despite the evilness of threads), but it does mean we have to make sure that if we want to access the same SQLite database from different threads, each thread must use a connection object that is exclusively created for that thread.
Fortunately, it is quite easy to instruct CherryPy to call a function the moment it starts a new thread and let that function create a new connection to our database, as we will see in the next section. If we would employ many different threads, this might be wasteful because the connection objects use some memory, but with a few tens of threads this doesn't pose much of a problem (The default number of threads in CherryPy is 10 and can be configured with the server.thread_pool configuration option). If the memory consumption is a problem, there are alternative solutions available, for example, in the form of a separate worker thread that handles all database interaction or a small pool of such threads. A starting point for this might be http://tools.cherrypy.org/wiki/Databases.