status of sqlite DB and threads?

[Thomas]

I have an sqlite database that might be accessed by multiple concurrent threads. I saw this blog post: https://www.codenameone.com/blog/threadsafe-sqlite.html but the ThreadSafeDatabase class is deprecated https://www.codenameone.com/javadoc/com/codename1/db/ThreadSafeDatabase.html . 

So what is the current recommanded way to create a Database object that may be accessed through multiple threads? 

Also does the warning found into the CN1 documentation regarding the usage of SQLite with iOS notifying that "The biggest issue with SQLite portability is in iOS. The SQLite version for most platforms is threadsafe and as a result very stable. However, the iOS version is not! This might not seem like a big deal normally, however if you forget to close a connection the GC might close it for you thus producing a crash. This is such a common occurrence that Codename One logs a warning when the GC collects a database resource on the simulator" still hold or is the iOS version threadsafe now, like with other platforms?

[Shai Almog]

We made some improvements to that but still strongly recommend writing to the database from a single thread. EasyThread is a relatively simple solution for that and it can be implemented in the level of the data access class. E.g. instead of calling SQL directly just call a wrapper class that does the SQL operation for you. That class can use EasyThread to make sure it's on the right thread and do the operation there.

[Thomas]

On Friday, March 29, 2019 at 4:31:27 AM UTC+1, Shai Almog wrote:

We made some improvements to that but still strongly recommend writing to the database from a single thread. EasyThread is a relatively simple solution for that and it can be implemented in the level of the data access class. E.g. instead of calling SQL directly just call a wrapper class that does the SQL operation for you. That class can use EasyThread to make sure it's on the right thread and do the operation there.

Isn't it exactly what the ThreadSafeDatabase class is doing (wrapping all SQL operations calls into an EasyThread queue)? If so, why has it been tagged as deprecated then?

 

[Shai Almog]

Because the constructor happens on a separate thread.

[Thomas]

On Saturday, March 30, 2019 at 6:28:45 AM UTC+1, Shai Almog wrote:

Because the constructor happens on a separate thread.

Sorry but I am afraid I don't get you here. What do you mean by "the constructor happens on a separate thread"? Are you talking about the Wrapper (=ThreadSafeDatabase) constructor or the Database one?

The ThreadSafeDatabase class is just a wrapper that declare an easythread and the targeted database in its constructor so it looks like it is performing exactly as you suggested to do (it is a wrapper class that does the SQL operations on the db by passing them to it's easythread). 

Is the issue the fact that the "Database" object should also be created through the easythread thread? (so the call to the Display.getInstance().openOrCreate() should be called from a runnable passed to the easythread wrapper instead of beeing directly passed as an already created "Database" object passed as argument of the ThreadSafeDatabase constructor). If so, why not modify the ThreadSafeDatabase class to be created with an empty private constructor that only create the easythread and then have a ThreadSafeDatabase.openOrCreate(String dbfile) static constructor function that return a working ThreadSafeDatabase object by creating a ThreadSafeDatabase and calling the  Display.getInstance().openOrCreate(dbfile) through its easythread to create the database? It would be better than having a ThreadSafeDatabase class that is tagged as deprecated because not working properly (meaning it is useless and pollute the API for nothing)

In any case, could you give a simple example of how the wrapper you suggest should be created (an example with the database creation and 2 SQLite calls (possibly comming from 2 different threads), so we can easilly see in which thread exactly each of these operations are supposed to happen)?

Thanks

[Shai Almog]

The database connection (Database class constructor) happens on a separate thread here.

But if you use a custom implementation you can control that too.