"Sachin Gupta" <scgu...@ipolicynetworks.com>
schrieb im Newsbeitrag
news:51b335651c9a0c4ea520d47f23a580b414f5cd5...@sinnodmbx001.techmahindra.com...
> --- The Application will be using the in memory SQLite
> as Cache.
You could also consider some sort of "sorted Dictionary"
(a Tree-Structure which allows fast "sorted indexed-access")
instead of the SQLite-engine in this case.
> --- One thread in the application, is listening on the device
> and the data sent by the device is saved in the Cache or
> inserted to SQLite. This is the writer Thread.
Ah Ok, as guessed...
> --- Multiple GUI's are connected to the Application which are being
> serviced by individual threads. These Threads are interacting with the
> cache (SQLite) and providing data to the GUI(s). These are the
> Reader Threads.
Ok.
> --- The rate of data received at the Writer Thread will be varying.
> It is not constant as stated by me earlier.
No problem, if your previous reply was describing
a worst-case data-rate, then you're fine - you will only
need to "sleep" within your SQLite-Thread, if "no-next-job"
was found in your round-robin-cycling.
> --- At any instant of time, if the data rate from device is very high
> and simultaneously the GUI is asking for Data, it is quite possible
> that Writer and Reader may collide.
That's clear, but there's no real collision possible (you don't
even need locking).
Your writer-devicethread will share a memory-structure with
the SQLite-thread, which in its first members is holding a
NextJobIndex (the index pointing into a JobQueue-array,
which at each index-position describes an entire new DataRow
(all the Fields which make up a new record) + a JobState-
EnumEntry (JobPreparing / JobPrepared / JobDone / JobFailed).
Same thing with your GUI-reader-threads (a Struct with
a NextJobIndex-member + a preallocated array which in
this case contains the same JobState-EnumEntry at
each array-index + a String-Entry for the Select-Command,
and a Resultset-pointer which will point to a copy of the
data, filled-in wihtin the sqlite_step-loop, when the SQLite-
thread is processing this current read-request.
Just ensure, that these shared structs hold JobQueue-arrays,
which are large enough to hold data for - let's say - 2-3
seconds worst-case incoming rate.
> Your Questions:
> Q1. Don't know, what you need these Reader-Threads for?
> Ans1: The application is client Server based, where there can
> be multiple instances of GUI connecting to the Application
> (running sqlite).
Till here, it is not yet entirely clear, what you mean (Client/Server
can mean anything) - please let's talk in terms of "processes"
and "threads".
When you talk about "multiple instances of GUI" - are these
instances running within the same process, which hosts your
SQLite-DBCache- and the DeviceWriter-Threads?
> The Application would handle display requests coming from
> these GUI threads. These are the reader threads.
This again sounds as if your "GUI-threads" and the "reader-
threads" are identically (and run within the same process).
> Q2. So, what is your usecase ... do you want to work with
> something like a ringbuffer? Are Deletes involved - and after
> what time do they begin "to kick in"?
> Ans2: Yes, kind of. We are using timers to maintain the size
> of the sqlite cache and limit it to a certain size (~1 Million Records)
> so that the DB does not bloat out of hand. We are using timers
> to click in every 15 seconds to delete the old records.
You will not need any timers for that, the isolated (single)
SQLite-Thread can take care of that whilst processing the
current insert (keeping a count-of already inserted records
in memory, you will not even need a "Select" for determining
the current recordcount).
> Questions:
> Ques. No there will be no such instances, since the InMemory-
> DB-Connection is used on only one thread - but that isn't
> really a problem, if you "do it right"...
> >>>> Does that mean that only one connection can be made
> to In Memory DB?
Yes, of course - but there's no real problem here with that,
because even one thread is enough in your case IMO
(because SQLite is fast enough in InMemory-Mode,
to give you the "illusion" of parallel processing of Write-
*and* Read-Requests, if you work in small enough
chunks (Write-direction inserting only 1-20 records at
a time - Read-Direction processing only one single
queued Select at a given time, which should be finished
within - let's say - 10msec.
> Ques: Does SQLite support Connection Pooling:
> Both for In Memory DB or Disk Based?
No - not in InMemory-Mode - and as said, not really
needed in your case.
> If not supported, then how can we handle multiple simultaneous
> accesses to DB (R-R,R-W,W-W)? Will we have to do
> concurrency handling by ourselves?
Of course - and it is not difficult, if you work with the
(isolated) mem-structures I've described already above.
Within the SQLite-Thread there's a loop:
While Not SQLiteThreadCancelled
ActivitySteps = 0
If WriterQ.JobArray(WriterQ.NextJobIndex).JobState = JobPrepared
Result = DoInsert(WriterQ.JobArray(WriterQ.NextJobIndex).RecordData)
WriterQ.JobArray(WriterQ.NextJobIndex).JobState = Result
WriterQ.NextJobIndex = WriterQ.NextJobIndex + 1 Modulo WriterQ.Lenght
If CurrentRecordCount > MaxDBRecords
DeleteOldestRecord()
End If
ActivitySteps++
End If
ReaderQ = GetNextReadersQueueStruct_RoundRobin()
If ReaderQ.JobArray(ReaderQ.NextJobIndex).JobState = JobPrepared
Result = DoSelect(pResultSet ,
ReaderQ.JobArray(ReaderQ.NextJobIndex).SelectString)
ReaderQ.JobArray(ReaderQ.NextJobIndex).JobState = Result
ReaderQ.JobArray(ReaderQ.NextJobIndex).pResultSet = pResultSet
ReaderQ.NextJobIndex = ReaderQ.NextJobIndex + 1 Modulo ReaderQ.Lenght
ActivitySteps++
End If
If ActivitySteps = 0 Then
Sleep 2
End If
Loop
> If not supported, then is there any thirdparty providing support
> for Connection Pooling?
You don't really need such a thing, the above scheme is
"simple enough" and also responsive enough I'd say.
> Ques: Can we use "SQLite Shared-Cache Mode" with "In Memory"
> Databases?
No, as Igor already pointed out, Shared-Cache mode is there
for multiple Connections (running in their own threads and
working against a *file*-DB).
There's only *one* Connection possible to an InMemory-DB.
> Ques: You mentioned the following:
> 1. inserts of these 15 records in about 1-2msec max
> 2. it would be your responsibility, to perform only that many
> of these queued selects, that you stay within your 8-10msec-
> limit Idle-Time
>
> Are we gonna have to handle the read/write requests in our
> application so that we are able to create Idle Time between
> multiple writes?
Yes, using the above scheme. And there's automatically
"Idle Time between Requests", since SQLite is more than
fast enough in InMemory-Mode, to handle your incoming
write and read-requests, using the above mentioned loop.
If the max data-rates you mentioned earlier are correct -
and if your reader-queries perform usually faster than
10msec, then the SQLite-Thread will sit mostly there,
just sleeping.
> Ques: Am I correct in saying that you are only considering
> one thread that is handling both Read and Write requests
> in sequential manner?
Of course.
The usual TFT-Screenrefresh-Rates are 60-75Hz nowadays.
So you have about 12-15msec Intervals available between "new
GUI-RenderOutputs" anyways - and even if your Read-Queries
should take 20-25msec, you will only have to leave out one
Screen-Refresh, which would barely be noticed by a
User, looking at some "scrolling graph" or whatever.
As long as you achieve around 30Hz Screenrefresh, then
everything will look "movielike and smooth".
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
