[sqlite] Multithreaded SQLite

[Keith Medcalf]

On Tuesday, 8 March, 2016 22:00 +07:00, Philippe Riand  said:

> I?m a bit lost with the multi-threaded concurrent access errors I?m
> getting, and looking for an advise on the best solution.

> Basically, I have a desktop/mobile app (single user) that embeds a tiny
> local http server. The UI is done through an embedded browser, calling the
> server for pages and data. And this browser component can submit multiple
> requests, which will be processed simultaneously on the server, by
> different threads. Each of these requests can update the same SQLIte
> database, and this is were the troubles start.

> 1- Is it safe to share a single connection between all these threads
> (assuming serialized mode)? 

Yes.  The purpose of serialized mode is to ensure that only one thread per 
connection is active at any given time, thus ensuring the concurrent entry 
prohibitions for multiple threads on a single connection.

> So far it seems to work, but what happens if a
> thread begins a transaction by calling ?BEGIN TRANSACTION"? 

Transactions are an artifact of the connection.  So a thread executing a "begin 
transaction" begins a transaction for the connection.  Any subsequent calls 
made on that connection (or statements on that connection) execute within the 
context of that transaction.

> Is this thread safe (the transaction bound to this thread), or will the 
> statements from
> the other threads be mixed up in that transaction? Are there other known
> limitations, for example how does sqlite3_last_insert_row_id() behave?

Yes, it is thread safe (if you are in serialized mode which will prevent 
multiple concurrent entry into the SQLite library from different threads on the 
same connection -- which is the only concurrency limitation for multiple 
threads).  No, the transaction has nothing to do with the thread -- a 
transaction is an artifact of the connection.  sqlite3_last_insert_row_id() 
will return the rowid of the last insert performed on the connection (without 
regard to which thread did what to the connection).  If you need isolation 
between threads then you need a connection per thread.

> 2- If I need to create one connection per thread, what are then the best
> options to set on the connection? I tried many ones (journal mode=WAL,
> busy_timeout, ?) but I?m getting errors like database is locked or even
> schema is locked.

You did not say what OS you are using.  What is the result of trying to put the 
database in WAL mode?  If you then ask what the journal_mode is, does it tell 
you WAL?

> 3- I tried to enable the shared cache, but I?m still getting database is
> locked (262). According to the doc, sqlite3_busy_handler does not help
> here. Is there a way to not get the error but simple have the thread wait
> until the lock is freed?

Shared Cache is for resource constrained devices.  You have not said what 
device/OS you are using.  Unless you really really need shared cache, you 
should probably not use it as it significantly alters the behaviour of SQLite.

> Or is there a better way to get this implemented using SQLite, beyond
> synchronizing my threads and making sure that only one is executing DB
> code at a time? I?m currently using 3.9.1, but can move to the latest
> version if it helps.

Serialized threading mode will do this for you automatically.  If you have a 
fixed thread pool you probably want one connection per thread.  If WAL is 
supported, then you can have concurrent readers with one writer.  If WAL is not 
supported, then readers will block writers and writers will block readers -- 
which setting a busy timeout will help with as long as you make sure to perform 
your transactions (which includes implicit read transactions) transactions 
expeditiously.

[sqlite] Creating & Dropping memory databases

[Dan Kennedy]

On 03/08/2016 09:14 AM, Simon Slavin wrote:
> On 8 Mar 2016, at 2:10am, Stephen Chrzanowski  wrote:
>
>> Out of curiosity, from a memory management standpoint, if I create a memory
>> database, populate simple tables and such, to what falls the responsibility
>> with cleaning up the memory used?
>>
>> Will SQLite handle :memory: databases internally, flushing out the tables
>> from memory?
>>
>> Does my application have to handle dropping the tables before actually
>> closing the connection?
> You can manually DROP the table.
>
> If the table still exists when you close the connection SQLite will DROP the 
> table for you.
>
> If you call sqlite3_shutdown(void) while SQLite still has connections open it 
> will close the connections for you.

I don't think it does that. sqlite3_shutdown() is for embedded platforms 
to shutdown sub-systems initialized by sqlite3_initialize(). Calling it 
with open connections will usually either leak resources or crash.

More here: https://www.sqlite.org/c3ref/initialize.html

[sqlite] File Locking Status

[Simon Slavin]

On 8 Mar 2016, at 6:50pm, Denis Burke  wrote:

> Is it possible to tell if SQLite has a database file locked? Not through
> OS tools, but from System.Data.SQlite?

Can't answer your question but these seem relevant:





Simon.

[sqlite] CTE for a noob

[R Smith]

On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote:
> Now I'll have to use USING a bit more often to get the drift and get out of
> this multi-call thing.  I sort of see what is going on here, but practice
> is whats needed.

"USING" has three uses in SQLite, first to enlist a virtual table, 
secondly to suggest an Index to the Query Planner and thirdly as in the 
example Igor gave where "USING" is simply short-hand for a join where 
the joining index fields are simple and named the same. This is defined 
in the standard as a join operation, by the way, works everywhere so not 
special to SQLite.

Easiest is probably by dual example - these two statements are equivalent:
SELECT *
   FROM tA
   JOIN tB USING (ID)

vs.

SELECT *
   FROM tA
   JOIN tB ON tB.ID = tA.ID

Of course this next query can't be simplified since the field-names do 
not match:

SELECT *
   FROM tA
   JOIN tB ON tB.ParentID = tA.ID

which might make the "USING" thing seem a bit overrated at first glance, 
but consider the following equivalent queries to see its simplifying power:

SELECT *
   FROM tA
   JOIN tB ON tB.Surname = tA.Surname AND tB.FirstName = tA.FirstName 
AND tB.DateOfBirth = tA.DateOfBirth

vs.

SELECT *
   FROM tA
   JOIN tB USING (Surname, FirstName, DateOfbirth)

the basic format of which, I might add, covers a very large percentage 
of typical joined queries.


Cheers!
Ryan

[sqlite] Creating & Dropping memory databases

[Simon Slavin]

On 8 Mar 2016, at 4:35pm, Dan Kennedy  wrote:

> I don't think it does that. sqlite3_shutdown() is for embedded platforms to 
> shutdown sub-systems initialized by sqlite3_initialize(). Calling it with 
> open connections will usually either leak resources or crash.
> 
> More here: https://www.sqlite.org/c3ref/initialize.html

You're right:

"All open database connections must be closed and all other SQLite resources 
must be deallocated prior to invoking sqlite3_shutdown()."

Hmm.  So there's no neat way to shut down SQLite if you've lost track of your 
context.  Is there a way of asking SQLite for a list of connections ?

Simon.

[sqlite] File Locking Status

[Doug Currie]

> Is it possible to tell if SQLite has a database file locked?  Not through
> OS tools, but from System.Data.SQlite?


 If you can execute

BEGIN EXCLUSIVE TRANSACTION

and get back SQLITE_OK

then there were no locks on the database.

Of course you will then need to ROLLBACK.

Note that this will only work well if you have no busy timeout or bust
handler set.

e

[sqlite] compile switches: SQLITE_OMIT_ATTACH & SQLITE_OMIT_VIRTUALTABLE

[Gert Corthout]

hello,
when I compile the amalgation with these compile 
switches:SQLITE_OMIT_VIRTUALTABLEorSQLITE_OMIT_VIRTUALTABLE
the amalgation fails to compile: the implementation of some functions are 
removed but the definition + call to these functions still exist. I use VS 2010 
to compile. This is the full command line (without the problematic 
predefines):/I"..\Export\Include" /I"..\Include" /I"..\Ref\Include" /Zi /nologo 
/W3 /WX- /O2 /Oy- /D "SQLITE_OMIT_WAL" /D "SQLITE_OMIT_AUTOVACUUM" /D 
"SQLITE_OMIT_EXPLAIN" /D "SQLITE_OMIT_SHARED_CACHE" /D 
"SQLITE_OMIT_AUTHORIZATION" /D "WIN32" /D "NDEBUG" /D "_LIB" /D 
"_WIN32_WINNT=0X501" /D "WINVER=0X501" /D "_MBCS" /Gm- /EHsc /MD /Zp1 /GS 
/fp:precise /Zc:wchar_t /Zc:forScope 
/Fp"C:\Workspaces\grpExtern\gc_SQLite_main\VC100\Release\\SQLite.pch" 
/Fa"C:\Workspaces\grpExtern\gc_SQLite_main\VC100\Release\\" 
/Fo"C:\Workspaces\grpExtern\gc_SQLite_main\VC100\Release\\" 
/Fd"C:\Workspaces\grpExtern\gc_SQLite_main\VC100\Release\\SQLite.pdb" /Gd 
/FU"C:\Program Files (x86)\Reference 
Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Core.dll" /analyze- 
/errorReport:queue 
Is this a bug or am I doing something wrong?
kind regards,Gert 

[sqlite] sqlite3 3.11 and 3.11.1 fail to build with ...

[Michele Dionisio]

Hi,

downloading this  sqlite-autoconf-3110100.tar.gz and compiling with

./configure CFLAGS="-DUSE_PREAD -DSQLITE_OMIT_DEPRECATED 
-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN"

fail with the following error:

sqlite3.c:16183:28: warning: 'sqlite3OpcodeName' used but never defined 
[enabled by default]
  SQLITE_PRIVATE const char *sqlite3OpcodeName(int);
 ^
mv -f .deps/sqlite3-sqlite3.Tpo .deps/sqlite3-sqlite3.Po
/bin/sh ./libtool  --tag=CC   --mode=link gcc -D_REENTRANT=1 
-DSQLITE_THREADSAFE=1-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE 
-DSQLITE_ENABLE_EXPLAIN_COMMENTS -DUSE_PREAD -DSQLITE_OMIT_DEPRECATED 
-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN   
-o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o  -ldl -lpthread
libtool: link: gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE 
-DSQLITE_ENABLE_EXPLAIN_COMMENTS -DUSE_PREAD -DSQLITE_OMIT_DEPRECATED 
-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN 
-o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o  -ldl -lpthread
sqlite3-sqlite3.o: In function `displayComment':
sqlite3.c:(.text+0x31821): undefined reference to `sqlite3OpcodeName'
sqlite3-sqlite3.o: In function `codeAllEqualityTerms':
sqlite3.c:(.text+0x83370): undefined reference to `explainIndexColumnName'

everything works without -DSQLITE_OMIT_EXPLAIN

regards

Michele Dionisio

[sqlite] File Locking Status

[Denis Burke]

Thank you Simon - that is helpful.

I would still be interested if anyone can provide any further info
regarding locking as seen through System.Data.SQlite.

-Denis

--
On 8 Mar 2016, at 6:50pm, Denis Burke  wrote: > Is it
possible to tell if SQLite has a database file locked? Not through > OS
tools, but from System.Data.SQlite?

Can't answer your question but these seem relevant: <
https://www.sqlite.org/c3ref/unlock_notify.html> <
https://www.sqlite.org/unlock_notify.html>
Simon.

[sqlite] CTE for a noob

[Drago, William @ CSG - NARDA-MITEQ]

Have you seen this tutorial?

https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
> Sent: Monday, March 07, 2016 9:47 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] CTE for a noob
>
> I'd like to learn how to use CTEs by example when working on my own
> project, by mutating it from individual calls into one clean call.  I looked 
> at the
> page (As I said in my previous email/note/post(?)) and just thought I'd put
> this question out there.
>
> Given I have the schema posted here: http://pastebin.com/hA6weV4n
>
> Currently my application makes two queries to the database.  One to get the
> list of projects via [ select ProjectID,Description from Projects order by
> Description ]
>
> It then goes through each record retrieved and then gets another query via [
> select (select count(VideoID) from vViewedVideos where DateViewed is null
> and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos
> where ProjectID=:P) Videos ] where :P is the ProjectID.
>
> I then take the results of ProjectID, Description, Unwatched and Watched
> and format a string to put it into a listbox on the form.
>
> I want to change the order in which this listbox is populated based on a 
> single
> query and the final sort order of [ order by Unwatched=0, Unwatched ] so
> that anything that has no unwatched videos available are at the bottom of
> list, and any videos that have something to watch are sorted ascending at the
> top of the list, 1 to whatever  Maybe even add the flexibility later on down
> the line so that I can change the order based on [ order by Description ] or [
> Unwatched=0, Description ] or whatever, just by changing the final Order By
> clause at runtime.
>
> From what magic I've seen on the CTE page at sqlite.org, this should be easy
> to do, but I've not a clue where to start.
>
> If you'd like to look at the raw database, it is found here:
> https://dl.dropboxusercontent.com/u/1598459/sql/videos.db3
>
> Any hints and thoughts would be appreciated!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] File Locking Status

[Denis Burke]

Is it possible to tell if SQLite has a database file locked?  Not through
OS tools, but from System.Data.SQlite?  I would like my application to be
able to understand if its use of SQLite has the file locked at a given
moment.  (and yes I know other processes/apps could also lock the file).



Thanks,
Denis Burke

[sqlite] Default of "PRAGMA synchonous" for WAL mode

[Reinhard Max]

On Tue, 8 Mar 2016 at 13:25, Reinhard Max wrote:

> On Tue, 8 Mar 2016 at 12:46, Richard Hipp wrote:
>
>> Documentation bug.  Fixed at 
>> https://www.sqlite.org/docsrc/timeline?c=3540d6
>
> Thanks, but https://www.sqlite.org/pragma.html#pragma_synchronous 
> still says: "NORMAL is the default when in WAL mode."

https://www.sqlite.org/docsrc/artifact/be924d5af7398122 also still 
has:

"FULL is the usual default setting when not in [WAL mode]"

and

"NORMAL is the default when in [WAL mode]"

cu
Reinhard

[sqlite] Default of "PRAGMA synchonous" for WAL mode

[Reinhard Max]

On Tue, 8 Mar 2016 at 12:46, Richard Hipp wrote:

> Documentation bug.  Fixed at https://www.sqlite.org/docsrc/timeline?c=3540d6

Thanks, but https://www.sqlite.org/pragma.html#pragma_synchronous 
still says: "NORMAL is the default when in WAL mode."

cu
Reinhard

[sqlite] [sqlite-dev] Changing the default page_size in 3.12.0

[Scott Hess]

On Fri, Mar 4, 2016 at 7:48 AM, Richard Hipp  wrote:

> The tip of trunk (3.12.0 alpha) changes the default page size for new
> database file from 1024 to 4096 bytes.


I have noticed that the OSX sqlite library seems to use default page_size
of 4096, and default cache_size of either -2000 or 500.  I think that
provides some support for the notion that for many users this may be a
non-event.

-scott

[sqlite] How to read data from WAL?

[Donald Griggs]

Sairam,

Just in case it's useful and you've not see it, Simon Slavin pointed out
this "rqlite" project for replicated sqlite in an email from 25 Feb.



Donald

Simon Slavin slavins at bigfraud.org via

mailinglists.sqlite.org
Feb 25 (11 days ago)
to SQLite
This looks interesting.





It allows you to set up multiple computers on the internet to keep copies
of a database in synch.  Changes in one are promulgated to them all.  As a
side effect it proves a TCP server for SQL commands.

Haven't had time to evaluate it, but there are people here who can do that
far better than I can.

Simon.

On Mon, Mar 7, 2016 at 7:36 AM, Simon Slavin  wrote:

>
> On 7 Mar 2016, at 12:24pm, Sairam Gaddam  wrote:
>
> > I want to make note of those changes and replicate in another DB.
>
> Okay.
>
> Reading changes from the WAL file is perhaps a poor way to do this.
> First, not all SQLite databases have a WAL file.  Second, SQLite can put
> changes in the WAL file and then immediately process them and overwrite the
> contents of the file.  Third, because you are modifying database files
> outside of sqlite you stand the chance of corrupting those files.  Fourth,
> you would need to have a constantly-running process to see what is going on
> and this wastes a lot of CPU and power.
>
> The standards way to ensure database replication is to do the following.
> Either
> A) Always make changes via your own library routine which logs them OR
> B) Rewrite sqlite3_exec() or sqlite3_prepare() to log changes as well as
> do their normal job.
>
> To log a change, create a new SQL table called 'change_log' and add to it
> all commands which start with INSERT, UPDATE or DROP.
>
> Then your routine simply reads that table, executes those commands on
> another database file (you can use ATTACH or send the changes as a text
> file to another computer) then does "DELETE FROM change_log".
>
> If your database files are small and you do not make changes frequently
> then you could instead use the SQLite Backup API:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Default of "PRAGMA synchonous" for WAL mode

[Reinhard Max]

Hi,

the documentation for "PRAGMA synchronous"[1] says that the default 
for databases in WAL mode is NORMAL (1). But when I open a database in 
WAL mode or switch an open database to WAL mode, querying PRAGMA 
synchronous returns 2 (checked in 3.8.6, 3.8.10 and 3.11.1).

Is this a bug in the documentation or in the code, or is 1 being used 
internally even if 2 is reported?


cu
Reinhard

[1] https://www.sqlite.org/pragma.html#pragma_synchronous

[sqlite] [sqlite-users] RBU Bit Level Diff

[김기웅]

Hello, I'm KiWong.

recently I'm looking for a sqlite update method using something.

so I found the rbu extension. (https://www.sqlite.org/rbu.html)

and tested that diff two sqlite3 dbs with rbu option.

but I have a problem of big diff size. this db has the BLOB Data in one
column and bit level data. found the rbu diff function in 'sqldiff.c' file
and i knew that this function will do byte level diff method.

therefore, I want to use a RBU custom delta format and found the below.

To use a custom delta format, the RBU application must register a
user-defined SQL function named "rbu_delta" before beginning to process the
update. *rbu_delta()* will be invoked with *two arguments* - the original
value stored in the target table column and the delta value provided as
part of the RBU update. It should return the result of applying the delta
to the original value. To use the custom delta function, the character of
the rbu_control value corresponding to the target column to update must be
set to 'd' instead of 'x'. Then, instead of updating the target table with
the value stored in the corresponding data_% column, RBU invokes the
user-defined SQL function "rbu_delta()" and the store in the target table
column.

but I don't understand about this exactly.

Please let me know how can I make a custom delta format and function,
update with rbu application in Lib. some example or something.

[sqlite] Default of "PRAGMA synchonous" for WAL mode

[Richard Hipp]

Thanks.  New changes checked in.

On 3/8/16, Reinhard Max  wrote:
>
> On Tue, 8 Mar 2016 at 13:25, Reinhard Max wrote:
>
>> On Tue, 8 Mar 2016 at 12:46, Richard Hipp wrote:
>>
>>> Documentation bug.  Fixed at
>>> https://www.sqlite.org/docsrc/timeline?c=3540d6
>>
>> Thanks, but https://www.sqlite.org/pragma.html#pragma_synchronous
>> still says: "NORMAL is the default when in WAL mode."
>
> https://www.sqlite.org/docsrc/artifact/be924d5af7398122 also still
> has:
>
> "FULL is the usual default setting when not in [WAL mode]"
>
> and
>
> "NORMAL is the default when in [WAL mode]"
>
> cu
>   Reinhard
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] [sqlite-users] RBU Bit Level Diff

[Richard Hipp]

On 3/7/16, ???  wrote:
>
> but I have a problem of big diff size. this db has the BLOB Data in one
> column and bit level data. found the rbu diff function in 'sqldiff.c' file
> and i knew that this function will do byte level diff method.
>
> therefore, I want to use a RBU custom delta format and found the below.
>

The function that creates a delta is
(https://www.sqlite.org/src/artifact/5a262051?ln=966-972) and the
function that applies a delta is
(https://www.sqlite.org/src/artifact/371e8bf06cfb?ln=460-466).  If you
replace these two functions with your own bit-level delta
implementation, then it should do what you want.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Default of "PRAGMA synchonous" for WAL mode

[Richard Hipp]

On 3/8/16, Reinhard Max  wrote:
> Hi,
>
> the documentation for "PRAGMA synchronous"[1] says that the default
> for databases in WAL mode is NORMAL (1). But when I open a database in
> WAL mode or switch an open database to WAL mode, querying PRAGMA
> synchronous returns 2 (checked in 3.8.6, 3.8.10 and 3.11.1).
>
> Is this a bug in the documentation or in the code, or is 1 being used
> internally even if 2 is reported?
>

Documentation bug.  Fixed at https://www.sqlite.org/docsrc/timeline?c=3540d6

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Creating & Dropping memory databases

[Simon Slavin]

On 8 Mar 2016, at 2:10am, Stephen Chrzanowski  wrote:

> Out of curiosity, from a memory management standpoint, if I create a memory
> database, populate simple tables and such, to what falls the responsibility
> with cleaning up the memory used?
> 
> Will SQLite handle :memory: databases internally, flushing out the tables
> from memory?
> 
> Does my application have to handle dropping the tables before actually
> closing the connection?

You can manually DROP the table.

If the table still exists when you close the connection SQLite will DROP the 
table for you.

If you call sqlite3_shutdown(void) while SQLite still has connections open it 
will close the connections for you.

Simon.