Have a read of this;
Should I use SQLite as my web server database? This has come up before and last week it came up again in the "Chit Chat" forum. The question has already been answered here before and there are many arguments for and against using SQLite, especially when you have lots of users who may be reading...
www.b4x.com
Sample project handles thousands of read and writes per second with no issues.
I made a simple example with two handlers. One that does a very long ExecQuery, and the other that does a very short ExecQuery on the same SQL object declared in the Main module. If we launch from a browser localhost:5100/long followed by localhost:5100/fast, the fast query is deferred until the end of the execution of the long query.
[CODE lang="b4x" title="Main"]#Region Project Attributes
#CommandLineArgs:
#MergeLibraries: True
#AdditionalJar: sqlite-jdbc-3.32.3.2
#End Region
Sub Process_Globals
Private srvr As Server
Dim DB As SQL
End Sub
Sub AppStart (Args() As String)
DB.InitializeSQLite(File.DirApp, "test.db", True)
srvr.Initialize("srvr")
srvr.Port = 5100
srvr.AddHandler("/long", "LongExecQuery", False)
srvr.AddHandler("/fast", "FastExecQuery", False)
srvr.Start
StartMessageLoop
End Sub
[/CODE]
[CODE lang="b4x" title="LongExecQuery"]'Handler class
Sub Class_Globals
End Sub
Public Sub Initialize
End Sub
Sub Handle(req As ServletRequest, resp As ServletResponse)
Dim limit As Long = 100000000 ' give about 30 seconds execQuery on my computer
Dim start As Long = DateTime.now
Main.DB.ExecQuery($"WITH RECURSIVE r(i) AS ( VALUES(0) UNION ALL SELECT i FROM r LIMIT ${limit} ) SELECT i FROM r WHERE i = 1; "$)
resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub[/CODE]
[CODE lang="b4x" title="FastExecQuery"]'Handler class
Sub Class_Globals
End Sub
Public Sub Initialize
End Sub
Sub Handle(req As ServletRequest, resp As ServletResponse)
Dim start As Long = DateTime.now
Main.DB.ExecQuery($"SELECT 1"$)
resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub[/CODE]
On the other hand if I declare another SQL object in the handler of the FastExecQuery . Then I have a real parallelism. The short query is executed during the long query.
[CODE lang="b4x" title="FastExecQuery with a local SQL Object"]'Handler class
Sub Class_Globals
End Sub
Public Sub Initialize
End Sub
Sub Handle(req As ServletRequest, resp As ServletResponse)
Dim lDB As SQL
lDB.InitializeSQLite(File.DirApp, "test.db", True)
Dim start As Long = DateTime.now
lDB.ExecQuery($"SELECT 1"$)
resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub[/CODE]
Also it seems to me that the SQLITE engine queues the read requests, and that there is no concurrent access. I had understood from Erel's posts that SQLITE allows concurrent access with a single SQL object. Is there something I don't understand?
