Re: [sqlite] Explain results help...

[Jens Alfke]

> On Aug 16, 2017, at 8:36 PM, jose isaias cabrera  
> wrote:
> 
> The following query is taking a bit too long for my taste.  I did a quick 
> query with explain and I got this…  

The output of EXPLAIN QUERY PLAN is a lot easier to understand; have you tried 
that? Most importantly it shows which indexes / tables are being searched 
(fast) or scanned (slow), and if a covering index is used.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explain results help...

[Simon Slavin]

On 17 Aug 2017, at 4:36am, jose isaias cabrera  wrote:

> The following query is taking a bit too long for my taste.  I did a quick 
> query with explain and I got this...  Anything I can do to make it faster?  
> Yes, I know I need to do some major work on rearranging, but anything else?

Can you post again with just the SELECT command, and any indexes you have on 
either table ?

The solution is probably going to involve making an index which ideally suits 
the SELECT command.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Explain results help...

[jose isaias cabrera]

Greetings!

The following query is taking a bit too long for my taste.  I did a quick query 
with explain and I got this...  Anything I can do to make it faster?  Yes, I 
know I need to do some major work on rearranging, but anything else?

sqlite> ATTACH 'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS 
client;
sqlite>
sqlite> explain
   ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
   ...> (
   ...>   cl.login != 'lsuser' AND
   ...>   cl.id = ls.id AND
   ...>   cl.XtraB != ls.XtraB
   ...> ) LIMIT 100;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 17000  Start at 17
1 Integer100   1 000  r[1]=100; LIMIT 
counter
2 OpenRead   2 403349  2 k(4)   00  root=403349 iDb=2; 
Proj_id_login_XtraB
3 OpenRead   0 5 2 47 00  root=5 iDb=2; 
LSOpenProjects
4 Rewind 2 162 0  00
5   Column 2 1 200  
r[2]=LSOpenProjects.login
6   Eq 3 152 (BINARY)   51  if r[2]==r[3] goto 1
5
7   IdxRowid   2 4 000  r[4]=rowid
8   SeekRowid  0 15400  intkey=r[4]; pk
9   Column 2 2 500  
r[5]=LSOpenProjects.XtraB
10  Column 0 46600  
r[6]=LSOpenProjects.XtraB
11  Eq 6 155 (BINARY)   51  if r[5]==r[6] goto 1
5
12  Copy   4 7 000  r[7]=r[4]
13  ResultRow  7 1 000  output=r[7]
14  DecrJumpZero   1 16000  if (--r[1])==0 goto
16
15Next   2 5 001
16Halt   0 0 000
17Transaction2 0 597   0  01  usesStmtJournal=0
18String80 3 0 lsuser 00  r[3]='lsuser'
19Goto   0 1 000
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[jose isaias cabrera]

Simon Slavin wrote...


to cater for the lowest level of geekery.


I think that is the first time I have seen this statement... :-)  I know a 
few folks with that description.


josé 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Simon Slavin]

On 17 Aug 2017, at 1:04am, Richard Hipp  wrote:

> On 8/16/17, Simon Slavin  wrote:
> 
>> I worked at a university and we used network home directories.
> 
> 
> Why use the local disk when you can instead use a network filesystem
> that is less reliable and about 100x slower?
> 

Because our students can’t use local disk.  They may use any of over 700 
computers on campus, maintained by different departments.  And they expect 
their files to appear magically wherever they sit down.  Also, they expect 
their files to get backed up.

It isn’t really a problem for the students who were clever with computers.  But 
once you have computers which can be used by any student in the university you 
have to cater for the lowest level of geekery.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Jens Alfke]

> On Aug 16, 2017, at 5:04 PM, Richard Hipp  wrote:
> 
> 
> Why use the local disk when you can instead use a network filesystem
> that is less reliable and about 100x slower?
> 

That was pretty much my conclusion! After I realized that most of the OS's 
sluggishness was attributable to filesystem latency, I switched to a local home 
directory sometime in 2001. But a lot of Apple engineers found it useful to log 
into their account from any computer, and to have their home directory 
unaffected by erasing disks and reinstalling operating systems, which happens 
several times a week during OS development.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Jens Alfke]

> On Aug 16, 2017, at 4:55 PM, Richard Hipp  wrote:
> 
> Remember:  COLLATE binds more tightly than ==.  So
> 
> 'xyz' = 'XYZ' COLLATE nocase
> 
> gets parsed out as
> 
>'xyz' = ('XYZ' COLLATE nocase)
> 
> And that expression has a different meaning from your
> 
>   ('xyz' = 'XYZ') COLLATE nocase

Oh, so collation is actually an attribute of a string value, that gets applied 
during comparisons involving that value? That’s very different from my mental 
model!

In that case, what is the meaning of
('foo' COLLATE binary) = ('FOO' COLLATE nocase)
i.e. which of the two competing collations does the = operator decide to use?

And is the collation attribute passed around with the string value, through 
intervening expressions? I.e. is this expression true?
'FOO' = ifnull(null, 'foo' COLLATE nocase)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Richard Hipp]

On 8/16/17, Simon Slavin  wrote:
>
> I worked at a university and we used network home directories.


Why use the local disk when you can instead use a network filesystem
that is less reliable and about 100x slower?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Jens Alfke]

> On Aug 16, 2017, at 4:52 PM, Simon Slavin  wrote:
> 
> Is there a chance this isn’t doing what you think it’s doing ?  Perhaps it is 
> evaluating the contents of the brackets first, then getting a true/false 
> value, and applying the COLLATE to that value.


That would make sense if COLLATE were a normal unary operator, but it isn’t. 
COLLATE doesn’t apply to a value, it has to be applied _during_ the computation 
of that value (the “=“ operation) because it affects the way the strings are 
compared. So it’s really not an operator, more of an annotation of an 
expression that affects its behavior.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Simon Slavin]

On 17 Aug 2017, at 12:55am, Jens Alfke  wrote:

> I’ve never seen network home directories in the wild, but I believe it may be 
> used in educational settings for centralized administration.

I worked at a university and we used network home directories.  But they 
weren’t done using NFS.  We used SMB or CIF or AFP depending on which part of 
the university ran the servers.  Our techies didn’t like NFS.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Richard Hipp]

On 8/16/17, Jens Alfke  wrote:
>  So may I request taking out this #warn directive?
>

You can probably make the warning go away if you compile with
-DSQLITE_ENABLE_LOCKING_STYLE=0

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Jens Alfke]

> On Aug 16, 2017, at 1:42 PM, Richard Hipp  wrote:
> 
> The proxy-locking VFS is important for OS-X, we are
> told, because many applications store SQLite databases in the user's
> home directory, and many OS-X users like to have their home
> directories NSF-mounted.

It was certainly the case that a lot of *Apple engineers* used NFS-mounted home 
directories, at least in the early days of Mac OS X. (I was an engineer at 
Apple at the time.) This made any problems with that configuration very 
prominent when testing the OS. I’ve never seen network home directories in the 
wild, but I believe it may be used in educational settings for centralized 
administration.

Anyway, it’s very unlikely that any *iOS* apps are accessing SQLite databases 
over NFS, considering that they aren’t allowed to access files outside their 
sandbox. :) So may I request taking out this #warn directive?

Thanks,

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Richard Hipp]

On 8/16/17, Jens Alfke  wrote:
> I have code that generates SQL queries that sometimes use collation, via the
> COLLATE expression. In some cases the collation doesn’t take effect; after
> some debugging I’ve narrowed the problem down to this simple test case in
> the SQLite 3.16 shell:
>
>   sqlite> SELECT 1 WHERE 'foo' = 'FOO' COLLATE nocase;
>   1 = 1
>   sqlite> SELECT 1 WHERE ('foo' = 'FOO') COLLATE nocase;
>   sqlite>
>
> So it appears the COLLATE operator is ignored if the expression it applies
> to is parenthesized, which is surprising.
>
> The only documentation I can find says "The COLLATE operator is a unary
> postfix operator that assigns a collating sequence to an expression. The
> COLLATE operator has a higher precedence (binds more tightly) than any
> binary operator and any unary prefix operator except ‘~’.”
>

So when you say:

  ('xyz' = 'XYZ') COLLATE nocase

That is assigning the "nocase" collating sequence to the *result* of
'xyz'='XYZ', not the input operands.  So the "nocase" does not take
effect until after the comparison.

Remember:  COLLATE binds more tightly than ==.  So

 'xyz' = 'XYZ' COLLATE nocase

gets parsed out as

'xyz' = ('XYZ' COLLATE nocase)

And that expression has a different meaning from your

   ('xyz' = 'XYZ') COLLATE nocase


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Simon Slavin]

On 17 Aug 2017, at 12:46am, Jens Alfke  wrote:

>   sqlite> SELECT 1 WHERE 'foo' = 'FOO' COLLATE nocase;
>   1 = 1
>   sqlite> SELECT 1 WHERE ('foo' = 'FOO') COLLATE nocase;
>   sqlite> 
> 
> So it appears the COLLATE operator is ignored if the expression it applies to 
> is parenthesized, which is surprising.

Is there a chance this isn’t doing what you think it’s doing ?  Perhaps it is 
evaluating the contents of the brackets first, then getting a true/false value, 
and applying the COLLATE to that value.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Jens Alfke]

I have code that generates SQL queries that sometimes use collation, via the 
COLLATE expression. In some cases the collation doesn’t take effect; after some 
debugging I’ve narrowed the problem down to this simple test case in the SQLite 
3.16 shell:

sqlite> SELECT 1 WHERE 'foo' = 'FOO' COLLATE nocase;
1 = 1
sqlite> SELECT 1 WHERE ('foo' = 'FOO') COLLATE nocase;
sqlite> 

So it appears the COLLATE operator is ignored if the expression it applies to 
is parenthesized, which is surprising.

The only documentation I can find says "The COLLATE operator is a unary postfix 
operator that assigns a collating sequence to an expression. The COLLATE 
operator has a higher precedence (binds more tightly) than any binary operator 
and any unary prefix operator except ‘~’.”

What I’m now inferring from this behavior is that when the docs say "assigns a 
collating sequence to an expression” they mean only the exact parse-tree node 
the COLLATE node follows, not any node inside that. So in this case the 
parentheses would be collated with nocase, but not the actual = comparison 
within the parentheses. 

To me this seems pretty abstruse; only someone working on a parser would draw a 
distinction between ‘foo’=‘FOO’ and (‘foo’=‘FOO’)! Also, this behavior means a 
collation can’t be applied to a grouped expression, e.g. "(x = ‘foo’ or y = 
‘bar’) COLLATE nocase” which would otherwise be a useful shorthand.

Anyway, I can adjust my code accordingly to avoid generating parentheses around 
a collated expression; but I’d like to request the docs be amended to point out 
that the COLLATE keyword has to exactly follow the expression with no 
intervening parentheses.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Richard Hipp]

On 8/16/17, Jens Alfke  wrote:
>
> What I don’t understand is why SQLite would want to call gethostuuid(),

The gethostuuid() system call is used only by the OS-X "proxy locking"
VFS, which provides better performance across network filesystems.
This VFS that was written at Apple and contributed to the SQLite
source tree.  (We have copyright releases signed by Apple lawyers in
our firesafe.)  The proxy-locking VFS is important for OS-X, we are
told, because many applications store SQLite databases in the user's
home directory, and many OS-X users like to have their home
directories NSF-mounted.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Jens Alfke]

When compiling sqlite3.c for the iOS platform, the compiler emits a custom 
warning "gethostuuid() is disabled”, triggered by a #warn directive (see code 
below.)

I understand why gethostuuid() isn’t available: the iOS platform intentionally 
blocks 3rd party apps from accessing device-specific UUIDs, to avoid privacy 
problems caused by ad networks that try to correlate a user’s activity across 
apps or across reinstalls of an app.

What I don’t understand is why SQLite would want to call gethostuuid(), or what 
functionality might be lost by its absence. 

Also, usually a compiler warning means the developer is doing something 
questionable or dangerous; but in this case the warning is triggered simply by 
the presence of a target platform, which isn’t something the developer has any 
control over. So it doesn’t seem useful to produce this warning when there’s 
nothing that can be done about it.

—Jens

PS: The code that triggers the warning is:

#if defined(__APPLE__) && ((__MAC_OS_X_VERSION_MIN_REQUIRED > 1050) || \
   (__IPHONE_OS_VERSION_MIN_REQUIRED > 2000))
#  if (!defined(TARGET_OS_EMBEDDED) || (TARGET_OS_EMBEDDED==0)) \
   && (!defined(TARGET_IPHONE_SIMULATOR) || (TARGET_IPHONE_SIMULATOR==0))
#define HAVE_GETHOSTUUID 1
#  else
#warning "gethostuuid() is disabled."
#  endif
#endif

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[David Raymond]

The way I read the responses is that you can't specify this. You might be able 
to fudge things using the naming conventions where an unqualified name resolves 
in attached database order. But that only works if there's no table of that 
name in the main database as well. And wouldn't work if you want to go in the 
other direction either.

SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> attach database 'deleteMe1.sqlite' as db1;

sqlite> create table db1.t (a, b, c);

sqlite> attach database 'deleteMe2.sqlite' as db2;

sqlite> create table db2.t (a, b, c);

sqlite> create temp trigger trg after insert on db2.t begin insert into db1.t 
values (new.a, new.b, new.c); end;
Error: qualified table names are not allowed on INSERT, UPDATE, and DELETE 
statements within triggers

sqlite> create temp trigger trg after insert on db2.t /*attached second*/ begin 
insert into t /*db1, attached first*/ values (new.a, new.b, new.c); end;

sqlite> insert into db2.t values (1, 2, 3);

sqlite> select * from db1.t;
a|b|c
1|2|3

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Roman Fleysher
Sent: Friday, August 11, 2017 3:46 PM
To: SQLite mailing list
Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT 
Statements Within Triggers

Dear Richard,
Dear SQLiters,

This is not clear to me as well.

If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I 
want to create a TEMPORARY trigger that upon insert in db1.t does something 
with db2.t.  Because:

TEMP triggers are not subject to the same-database rule. A TEMP trigger is 
allowed to query or modify any table in any ATTACH-ed database.

I need to be able to specify db2.t in the body of the trigger. But this is not 
allowed, right? Then how could it work? I mean it does not. Should it work?

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Friday, June 09, 2017 9:23 AM
To: SQLite mailing list
Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT 
Statements Within Triggers

On 6/9/17, Mark Brand  wrote:
>
>
> On 09/06/17 14:47, Richard Hipp wrote:
>> The documentation has been updated to clarify the ambiguity and to
>> hopefully make it easier to understand.
>
> Thanks. The exception for non-TEMP triggers is something I was hoping
> for too:
>
>> For non-TEMP triggers, the table to be modified or queried must exist
>> in the same database as the table or view to which the trigger is
>> attached. TEMP triggers are not subject to the same-database rule. A
>> TEMP trigger is allowed to query or modify any table in any ATTACH
>> -ed database.
>
> But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?

The . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 schema is relaxed for TEMP triggers.

>
>
> create table t (x);
> insert into t values (0);
> create temp view v as select * from t;
>
> create temp trigger trg1 instead of update on temp.v
> begin
>  update temp.t set x = new.x;
> end;
>
> Error: near line 5: qualified table names are not allowed on INSERT,
> UPDATE, and DELETE statements within triggers
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Dominique Devienne]

On Wed, Aug 16, 2017 at 4:15 PM, Keith Medcalf  wrote:

> Setting "multithreaded" mode disables these checks in the SQLite3 library
> and it is up to the application level code to ensure the single-entrance
> per connection is enforced at the application level.  If the application
> makes a "mistake" and violates the rules then corruption and not nice
> consequences will ensue.  However, since the SQLite3 core is no longer
> passing through a mutex on each entry, the code runs slightly faster.
>
> Setting "single-threaded" mode disables all mutexes in the library and is
> intended for use when the code is entirely single threaded.  Since there is
> no protection at all (even for the VFS layer) if you perform any kind of
> multiple-entrance nasty consequences may ensue.
>
> Generally speaking if you leave the default "serialized" mode, then you
> are protected against accidental re-entrancy application errors on multiple
> threads at a price penalty for the extra safety.
>

+1 Keith. Very useful, thanks! I actually find this clearer than the doc on
this subject, which is a bit too terse IMHO.

I understand one doesn't want to explain implementation details too much in
doc,
but Keith's "annotated" version made it "click" in my head better, when the
doc didn't.

The doc is not wrong of course, just too terse for my liking (as a
developer). My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Keith Medcalf]

>According to your interpretation, "Single-thread" and "Multi-thread"
>modes are equivalent:

>> Multi-thread. In this mode, SQLite can be safely used by multiple
>threads provided that no single database connection is used
>simultaneously in two or more threads.

>Assuming there *is* a difference, I thus believe you are mistaken.
>Now documentation is ambiguous, I agree. My cautious interpretation
>says that only "Multi-thread" and "Serialized" modes are safe for
>serialized accesses from multiple threads (where "Multi-thread" is
>enough, and "Serialized" does too much).

I believe there is a difference and that has to do with cache management, 
internal b-tree page cache management, and the VFS layer.  When set to 
multi-threaded, there are still some mutexes used in the VFS and cache 
management code as well as an expectation of multiple simultaneous access to 
the cache.  When in single-threaded mode, these protections and management is 
unnecessary.  So single-threaded is not entirely equivalent to multi-threaded.

Whether globally single-entrant serialized multithreaded access at the 
application level will not cause problems if the SQLite engine is in 
single-threaded mode is something that can only be answered by Richard of one 
of the other developers.

>Finally, we don't know which threading mode the OP is using. We don't
>even know if OP knows about threading modes. This topic may interest
>him.

Generally speaking I would leave the access mode as the default serialized 
unless there is a specific performance related reason to change that.  It 
provides the most safety.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Keith Medcalf]

On Wednesday, 16 August, 2017 00:11, Gwendal Roué  
wrote:
>> Le 15 août 2017 à 08:44, Clemens Ladisch  a écrit :

>> sanhua.zh wrote:

>>> All 1. 2. 3. steps are run sequentially, which means that the step 2
>>> runs after step 1 finished and step 3 runs after step 2 finished
>>> theoretically .
>>> Also, I can make sure the memory order between threads.
>>>
>>> Then, is it a safe way to use sqlite connection ?

>> Yes.

>> Multi-threading problems are caused by multiple threads accessing the
>> same data at the same time.  If code in multiple threads is serialized,
>> it is, for practical purpose, identical to single-threaded code.

>Serialized accesses from multiple threads is OK when the connection
>is in the "Multi-thread" or "Serialized" threading modes, but not in
>the "Single-thread" threading mode.

>Have a look at https://www.sqlite.org/threadsafe.html for detailed
>information.

Basically, the restriction is on simultaneous entry from multiple threads to 
objects on the same connection (that is, simultaneous calls to 
prepare/bind/step/colun/reset/finalize from multiple threads at the same time). 
 The default is to SERIALIZE any such accesses to ensure that the 
single-entrance requirement for each connection is enforced through built-in 
mutexes.

Setting "multithreaded" mode disables these checks in the SQLite3 library and 
it is up to the application level code to ensure the single-entrance per 
connection is enforced at the application level.  If the application makes a 
"mistake" and violates the rules then corruption and not nice consequences will 
ensue.  However, since the SQLite3 core is no longer passing through a mutex on 
each entry, the code runs slightly faster.

Setting "single-threaded" mode disables all mutexes in the library and is 
intended for use when the code is entirely single threaded.  Since there is no 
protection at all (even for the VFS layer) if you perform any kind of 
multiple-entrance nasty consequences may ensue.

Generally speaking if you leave the default "serialized" mode, then you are 
protected against accidental re-entrancy application errors on multiple threads 
at a price penalty for the extra safety.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Clemens Ladisch]

Gwendal Roué wrote:
>> Le 16 août 2017 à 08:38, Clemens Ladisch  a écrit :
>> Gwendal Roué wrote:
>>> Serialized accesses from multiple threads is OK when the connection is
>>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>>> "Single-thread" threading mode.
>>
>>  says:
>> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
>> |is unsafe to use in more than a single thread at once.
>>
>> When the accesses are serialized, they are not _at once_.
>
> According to your interpretation, "Single-thread" and "Multi-thread" modes 
> are equivalent:
>
>> Multi-thread. In this mode, SQLite can be safely used by multiple threads
>> provided that no single database connection is used simultaneously in two
>> or more threads.

"Multi-thread" mode allows concurrent accesses to different connections
(which might end up accessing the same internal, global variables).

When all accesses are serialized, there are not concurrent accesses at all.

> Finally, we don't know which threading mode the OP is using.

Serializing everything, as the OP is doing, is safe in any mode.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WinForms | WPF: Using SQLite DataBase

[ajm]

Perhaps maybe of interest to those beginners using SQLite in Windows.

WPF: Using SQLite DataBase

https://www.codeproject.com/Tips/988690/WinForms-WPF-Using-SQLite-DataBase-For-User-For-Ap

HTH

--
Adolfo J. Millán.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Petite Abeille]

> On Aug 16, 2017, at 11:11 AM, Jean-Luc Hainaut  
> wrote:
> 
> The implementation of SQLite "group_concat" (a very powerful but often 
> overlooked function) provides some but not all the features found in other 
> DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):

For diversity's sake, Oracle’s LISTAGG:

https://docs.oracle.com/cloud/latest/db112/SQLRF/functions089.htm#SQLRF30030


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat() reverses order given where clause?

[Jean-Luc Hainaut]

On 15/08/2017 17:12, Bob Friesenhahn wrote:

I am surprised by this behavior of group_concat():


The implementation of SQLite "group_concat" (a very powerful but often 
overlooked function) provides some but not all the features found in 
other DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):

- the values to concatenate (SQL string expression),
- the separator, default (comma) or user-defined (SQL string expression),
- a uniqueness contraint on the values of each group,
- the order of the values.

As far as I understand the specs, SQLlite provides the first three, but 
with a frustrating constraint: you must choose between the uniqueness 
and the user-defined separator but you cannot have both.
The "order by" is badly needed, so, programmers tend to use the 
workaround suggested in this thread: sorting the values in a "from" 
subquery. This is intuitive and works fine in the current version but, 
as said in the documentation, this order is not guaranteed to propagate 
to the concatenated list .


The uniqueness constraint can be enforced in a "from" subquery and the 
user-defined separator can be merged with the values to concatenate, 
followed by some cleaning. As I saw in various forums, it seems possible 
to force the ordering with a CTE (I have not checked). However this 
makes the final expression horribly complicated.


I personally have implemented (in Python) a UDF aggregate function that 
simulates the full group_concat version.  But it would be nice to 
include a full-fledged function (whatever the syntax) in a future SQLite 
version.

Why not in the Christmast version for example?

Best regards

Jean-Luc Hainaut

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Gwendal Roué]

> Le 16 août 2017 à 08:38, Clemens Ladisch  a écrit :
> 
> Gwendal Roué wrote:
>> Serialized accesses from multiple threads is OK when the connection is
>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>> "Single-thread" threading mode.
> 
>  says:
> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
> |is unsafe to use in more than a single thread at once.
> 
> When the accesses are serialized, they are not _at once_.

According to your interpretation, "Single-thread" and "Multi-thread" modes are 
equivalent:

> Multi-thread. In this mode, SQLite can be safely used by multiple threads 
> provided that no single database connection is used simultaneously in two or 
> more threads.

Assuming there *is* a difference, I thus believe you are mistaken. Now 
documentation is ambiguous, I agree. My cautious interpretation says that only 
"Multi-thread" and "Serialized" modes are safe for serialized accesses from 
multiple threads (where "Multi-thread" is enough, and "Serialized" does too 
much).

Finally, we don't know which threading mode the OP is using. We don't even know 
if OP knows about threading modes. This topic may interest him.

Gwendal

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Clemens Ladisch]

Gwendal Roué wrote:
> Serialized accesses from multiple threads is OK when the connection is
> in the "Multi-thread" or "Serialized" threading modes, but not in the
> "Single-thread" threading mode.

 says:
| 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
|is unsafe to use in more than a single thread at once.

When the accesses are serialized, they are not _at once_.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Gwendal Roué]

> Le 15 août 2017 à 08:44, Clemens Ladisch  a écrit :
> 
> sanhua.zh wrote:
>> All 1. 2. 3. steps are run sequentially, which means that the step 2
>> runs after step 1 finished and step 3 runs after step 2 finished
>> theoretically .
>> Also, I can make sure the memory order between threads.
>> 
>> Then, is it a safe way to use sqlite connection ?
> 
> Yes.
> 
> Multi-threading problems are caused by multiple threads accessing the
> same data at the same time.  If code in multiple threads is serialized,
> it is, for practical purpose, identical to single-threaded code.

Serialized accesses from multiple threads is OK when the connection is in the 
"Multi-thread" or "Serialized" threading modes, but not in the "Single-thread" 
threading mode.

Have a look at https://www.sqlite.org/threadsafe.html for detailed information.

Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users