Re: [sqlite] Find stmt that's stopping a DB from closing

[Simon Slavin]

On 5 Nov 2019, at 8:39pm, Keith Medcalf  wrote:

> If pStmt is NULL then this interface returns a pointer to the first prepared 
> statement associated with the database connection pDb.

Ah, that's brilliant.  I missed that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find stmt that's stopping a DB from closing

[Keith Medcalf]

>The first statement associated with an open connection is pointed to by
>the pStmt of that connection.  Once you've found the first statement call

>sqlite3_next_stmt()

>on it to find the next one.

>
>
>Unfortunately the database connection is documented as 'opaque'.  I'm not
>certain for the ideal way in C to get the first statement from it.

It is documented:

This interface returns a pointer to the next prepared statement after pStmt 
associated with the database connection pDb. If pStmt is NULL then this 
interface returns a pointer to the first prepared statement associated with the 
database connection pDb. If no prepared statement satisfies the conditions of 
this routine, it returns NULL.

This is how the sqlite_stmt vtab finds the non-finalized statements associated 
with a connection.

-- 
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] Find stmt that's stopping a DB from closing

[x]

Thanks Simon. Used this

string str;
sqlite3_stmt *stmt=NULL;
do
{
stmt=sqlite3_next_stmt(FSQLiteDB,stmt);
if (stmt) str+=string(sqlite3_sql(stmt))+"\r\n";
}
while (stmt);




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Tuesday, November 5, 2019 7:55:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Find stmt that's stopping a DB from closing

On 5 Nov 2019, at 7:27pm, x  wrote:

> I’m sure the team added a fct that listed the offenders but I can’t find it 
> in the documentation.

The first statement associated with an open connection is pointed to by the 
pStmt of that connection.  Once you've found the first statement call

sqlite3_next_stmt()

on it to find the next one.



Unfortunately the database connection is documented as 'opaque'.  I'm not 
certain for the ideal way in C to get the first statement from it.

Simon
--
 http://www.bigfraud.org | I'd expect if a computer was involved
 No Buffy for you.   | it all would have been much worse.
 Leave quickly now. -- Anya  |-- John "West" McKenna

___
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] Find stmt that's stopping a DB from closing

[Simon Slavin]

On 5 Nov 2019, at 7:27pm, x  wrote:

> I’m sure the team added a fct that listed the offenders but I can’t find it 
> in the documentation.

The first statement associated with an open connection is pointed to by the 
pStmt of that connection.  Once you've found the first statement call

sqlite3_next_stmt()

on it to find the next one.



Unfortunately the database connection is documented as 'opaque'.  I'm not 
certain for the ideal way in C to get the first statement from it.

Simon
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 No Buffy for you.   | it all would have been much worse.
 Leave quickly now. -- Anya  |-- John "West" McKenna

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

Re: [sqlite] Find stmt that's stopping a DB from closing

[x]

>I think you're referring to "The SQLITE_STMT Virtual Table":
https://www.sqlite.org/stmt.html

That’s it David. Thanks.

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

Re: [sqlite] Find stmt that's stopping a DB from closing

[David Raymond]

I think you're referring to "The SQLITE_STMT Virtual Table":
https://www.sqlite.org/stmt.html


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Tuesday, November 5, 2019 2:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Find stmt that's stopping a DB from closing

I’m sure the team added a fct that listed the offenders but I can’t find it in 
the documentation. I would’ve thought it would have been mentioned on the 
sqlite3_close page but no sign of it. Am I imagining things?



___
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] Find stmt that's stopping a DB from closing

[x]

I’m sure the team added a fct that listed the offenders but I can’t find it in 
the documentation. I would’ve thought it would have been mentioned on the 
sqlite3_close page but no sign of it. Am I imagining things?



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

Re: [sqlite] SQLite with branching

[Jens Alfke]

> On Nov 5, 2019, at 1:27 AM, Dominique Devienne  wrote:
> 
> AFAIK, that was one of the goals of SQLite4 [1], to change the backend to LSM.

LMDB (LiteTree's back-end) doesn't use LSM; it's a B-tree manager. The speedup 
appears to come from a combination of techniques like eliminating caching 
through memory-mapping, and eliminating locking through MVCC. There's a paper 
describing it in somewhat more detail[1].

I know there is memory-mapping support in SQLite, but it doesn't seem to result 
in as much speedup, and it has some nasty data-corruption bugs on macOS 
(possibly iOS too?)

LSM and LMDB would seem to have differing goals — LSM is best for applications 
with high write throughput, while LMDB is optimized more for read performance. 
I would guess that the latter is closer to the majority of SQLite use cases, 
since small/embedded systems tend to take in less data than big servers do.

—Jens

[1]: http://www.lmdb.tech/media/20120829-LinuxCon-MDB-txt.pdf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Jose Isaias Cabrera]

Adrian Sherwin, on Monday, November 4, 2019 03:41 AM, wrote...
>
> Hi,
>
> I would like to report the following as a bug in SQLITE:
>
> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
> correctly to x decimal places when held as x+1 decimal places.
>
> The simplest example I have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

I asked the same question before, but it is part of the SQLite FAQ [1].  Ihth.

josé

[1] https://www.sqlite.org/faq.html#q16


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

Re: [sqlite] SQLite with branching

[Dominique Devienne]

On Tue, Nov 5, 2019 at 10:01 AM Wout Mertens  wrote:

> On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke  wrote:
>
> > I don't have a practical use for the branching features, though they're
> cool, but I'm salivating at the thought of a 2x speedup.
> > With all the work that's put into eking out small performance increases
> in SQLite, I'd imagine the devs would be interested in
> > something that made that big of a difference...
>
> What I would like to know is how such a performance increase is
> achieved, and why regular SQLite can't do the same?
>

AFAIK, that was one of the goals of SQLite4 [1], to change the backend to
LSM.
We know now SQLite4 is basically abandoned, but LSM was refactored as an
SQLite3 extension [2].
Here's an article that goes into more depth on the subject [3]. Hope this
helps. --DD

[1] https://sqlite.org/src4/doc/trunk/www/index.wiki
[2] https://www.sqlite.org/src/dir?ci=5710845b6314f924&name=ext/lsm1
[3] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Wout Mertens]

On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke  wrote:

> I don't have a practical use for the branching features, though they're cool, 
> but I'm salivating at the thought of a 2x speedup.
> With all the work that's put into eking out small performance increases in 
> SQLite, I'd imagine the devs would be interested in
> something that made that big of a difference...

What I would like to know is how such a performance increase is
achieved, and why regular SQLite can't do the same?

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