Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread Basile Starynkevitch 



On 07/13/2017 08:49 PM, Richard Hipp wrote:

The 3.20.0 release of SQLite is expected in about a week.  Please
report any issues that you have with the beta as soon as possible.

Code is available in the "prerelease snapshot" at
https://sqlite.org/download.html

An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html

You can send issues to this email list, or directly to me.



https://sqlite.org/draft/releaselog/3_20_0.html  says:

 * Added the "--newlines" option to the ".dump
   " command to cause U+000a
   and U+000d characters to be output literally rather than escaped
   using the replace()
    function.

This change is welcome, but quite strange. See my message (wishing it)
 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-July/073642.html
and 
https://stackoverflow.com/questions/44989176/sqlite3-dump-inserts-replace-function-in-dump-change-from-3-18-to-3-19

What is strange is that according to the release log, that options only apply 
to .dump; I would expect
that .mode insert also got an option similar to that, or perhaps a new .mode 
rawinsert
(in both cases the effect would be that the INSERT statements generated would 
keep
newlines and returns character verbatim).

Why would there be a way to modify the output of newlines & returns character 
in dump but not in .mod insert outputs?

Cheers.
--
Basile STARYNKEVITCH   == http://starynkevitch.net/Basile
opinions are mine only - les opinions sont seulement miennes
Bourg La Reine, France

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

Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

2017-07-13 Thread Keith Medcalf 

Not really.  (At least not for anyone else's Link Time Optimizer)

LTO outputs the intermediate parse tree rather than object code into the 
.obj/.a files.  The LTO linker merges all the parse tree's into a single parse 
tree then generates actual object code.  In other words, it makes it "look" 
like all your code was compiled as one file while at the same time maintaining 
symbol separation between modules.  

Some IBM and other dinosaur pen compilers have been able to do that since the 
60's.  
Watcom has been able to do that since the 90's or so.  
GCC has done it for many years (most of this century anyway, if not before).  

CLang must be just catching up.  

That is better though than Intel, Microsoft, et al, who make really shoddy 
compilers (buggy and suitable for amateur use only) let alone have any actual 
"advanced" features.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jens Alfke
> Sent: Thursday, 13 July, 2017 10:20
> To: SQLite mailing list
> Subject: Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) -
> log and hints for a possible solution
> 
> 
> > On Jul 13, 2017, at 7:49 AM, Richard Hipp  wrote:
> >
> > I've spent some time investigating this, and I think the answer is
> > that GCC simply generates better code.
> 
> Have you tested Clang? These days it may generate even better code,
> especially with -Ofast and the link-time optimizer. (SQLite itself won’t
> benefit from LTO because it’s already agglomerated before being compiled,
> but LTO allows SQLite code to be inlined into calling functions in the
> client program.)
> 
> —Jens
> ___
> 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] Version 3.20.0 coming soon...

2017-07-13 Thread Peter da Silva 
Nicely put. There’s a couple of prepared statements that I’m using that 
absolutely will benefit from this, now I understand it.

On 7/13/17, 3:30 PM, "sqlite-users on behalf of Marc L. Allen" 
 wrote:

If it's going to change in future releases, then perhaps your "key point" 
is the main takeaway that should be documented?

Something like:

SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared statement 
is going to linger for a long time and that SQLite should take that into 
account for internal performance optimizations.  At present the only 
optimization is to avoid using lookaside... etc.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, July 13, 2017 4:22 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Version 3.20.0 coming soon...

On 7/13/17, Dominique Pellé  wrote:
>
> Looking at the description of the new SQLITE_PREPARE_PERSISTENT at 
> https://sqlite.org/draft/c3ref/c_prepare_persistent.html
> it's not clear to me what are the benefits.
> Shouldn't the description say why it can be beneficial to use it or 
> not?
>

That documentation is the contract of what  SQLITE_PREPARE_PERSISTENT is 
suppose to do, so it needs to be a little vague in order to allow for changes 
in the future.

In the current implementation, SQLITE_PREPARE_PERSISTENT avoids using 
lookaside memory to store pieces of the prepared statement, to avoid soaking up 
all of the fast lookaside memory and preventing it from being used for more 
useful things.  But the effect of SQLITE_PREPARE_PERSISTENT might change in 
future releases.  The key point (and the part of this interface that will not 
change) is that SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared 
statement is going to linger for a long time.

How would you recommend that be stated more clearly?

--
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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
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] Version 3.20.0 coming soon...

2017-07-13 Thread Richard Hipp 
On 7/13/17, Simon Slavin  wrote:
>
> Can I conclude that if "SELECT sql FROM STMT" returns no rows then closing
> the connection should work properly and immediately ?
>

You'll probably always get one row of result, which is the query itself.

Yes, if you only get that one row, then it is certainly safe to close
the connection.  However, if you get multiple rows, that does not mean
it is unsafe.  Those extra entries might be prepared statements that
have been cached by extensions (ex: FTS5) that will be released
automatically when the connection is closed.

The purpose of the STMT virtual table is to help users debug the case
where sqlite3_close() returns SQLITE_BUSY.  Using the STMT virtual
tables, the developers can get a listing of unfinalized prepared
statements and quickly see which ones where overlooked.

While testing the STMT virtual table, we noticed that the cached
prepared statements from FTS5 were using up a lot of lookaside memory.
We had never noticed that before, and it is what prompted the
SQLITE_PREPARE_PERSISTENT enhancement.  So if the STMT table helped us
core developers to notice a problem in our own SQLite code, we figured
it would be good to publish it to perhaps help others notice subtle
problems in their code.

-- 
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] Version 3.20.0 coming soon...

2017-07-13 Thread Simon Slavin 


On 13 Jul 2017, at 7:49pm, Richard Hipp  wrote:

> An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html

Could I suggest that the STMT virtual table be renamed as something like 
sqlite_statements ?  Or anything else that starts with "sqlite_" ?  I’m 
uncomfortable with a "magic" entity which isn’t clearly marked as not being 
part of your database.

Can I conclude that if "SELECT sql FROM STMT" returns no rows then closing the 
connection should work properly and immediately ?

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

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread Marc L. Allen 
If it's going to change in future releases, then perhaps your "key point" is 
the main takeaway that should be documented?

Something like:

SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared statement is 
going to linger for a long time and that SQLite should take that into account 
for internal performance optimizations.  At present the only optimization is to 
avoid using lookaside... etc.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, July 13, 2017 4:22 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Version 3.20.0 coming soon...

On 7/13/17, Dominique Pellé  wrote:
>
> Looking at the description of the new SQLITE_PREPARE_PERSISTENT at 
> https://sqlite.org/draft/c3ref/c_prepare_persistent.html
> it's not clear to me what are the benefits.
> Shouldn't the description say why it can be beneficial to use it or 
> not?
>

That documentation is the contract of what  SQLITE_PREPARE_PERSISTENT is 
suppose to do, so it needs to be a little vague in order to allow for changes 
in the future.

In the current implementation, SQLITE_PREPARE_PERSISTENT avoids using lookaside 
memory to store pieces of the prepared statement, to avoid soaking up all of 
the fast lookaside memory and preventing it from being used for more useful 
things.  But the effect of SQLITE_PREPARE_PERSISTENT might change in future 
releases.  The key point (and the part of this interface that will not change) 
is that SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared 
statement is going to linger for a long time.

How would you recommend that be stated more clearly?

--
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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread Richard Hipp 
On 7/13/17, Dominique Pellé  wrote:
>
> Looking at the description of the new SQLITE_PREPARE_PERSISTENT
> at https://sqlite.org/draft/c3ref/c_prepare_persistent.html
> it's not clear to me what are the benefits.
> Shouldn't the description say why it can be beneficial to
> use it or not?
>

That documentation is the contract of what  SQLITE_PREPARE_PERSISTENT
is suppose to do, so it needs to be a little vague in order to allow
for changes in the future.

In the current implementation, SQLITE_PREPARE_PERSISTENT avoids using
lookaside memory to store pieces of the prepared statement, to avoid
soaking up all of the fast lookaside memory and preventing it from
being used for more useful things.  But the effect of
SQLITE_PREPARE_PERSISTENT might change in future releases.  The key
point (and the part of this interface that will not change) is that
SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared
statement is going to linger for a long time.

How would you recommend that be stated more clearly?

-- 
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] Version 3.20.0 coming soon...

2017-07-13 Thread Dominique Pellé 
Richard Hipp  wrote:

> The 3.20.0 release of SQLite is expected in about a week.  Please
> report any issues that you have with the beta as soon as possible.
>
> Code is available in the "prerelease snapshot" at
> https://sqlite.org/download.html
>
> An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html
>
> You can send issues to this email list, or directly to me.


Looking at the description of the new SQLITE_PREPARE_PERSISTENT
at https://sqlite.org/draft/c3ref/c_prepare_persistent.html
it's not clear to me what are the benefits.
Shouldn't the description say why it can be beneficial to
use it or not?

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

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread Richard Hipp 
On 7/13/17, jungle Boogie  wrote:
>
> On doesn't have to be capitalized:
> SQLite Release 3.20.0 On 2017-07-20
>

That is the same capitalization used for all prior releases (ex:
https://www.sqlite.org/releaselog/3_19_3.html and
https://www.sqlite.org/releaselog/3_0_0.html) so I think I will leave
it.  All other fixes implemented.

-- 
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] Version 3.20.0 coming soon...

2017-07-13 Thread jungle Boogie 
On 13 July 2017 at 11:49, Richard Hipp  wrote:
> The 3.20.0 release of SQLite is expected in about a week.  Please
> report any issues that you have with the beta as soon as possible.
>
> Code is available in the "prerelease snapshot" at
> https://sqlite.org/download.html
>
> An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html
>

https://sqlite.org/draft/c3ref/result_blob.html
Duplicate from:
space obtained from from sqlite3_malloc() before it

It's mighty impressive that you and your team make rather large
releases like this and you do it so frequently.
Tip of the hat to you!



> You can send issues to this email list, or directly to me.
>
> --
> D. Richard Hipp
> d...@sqlite.org


-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread jungle Boogie 
On 13 July 2017 at 11:49, Richard Hipp  wrote:
> The 3.20.0 release of SQLite is expected in about a week.  Please
> report any issues that you have with the beta as soon as possible.
>
> Code is available in the "prerelease snapshot" at
> https://sqlite.org/download.html
>
> An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html
>
> You can send issues to this email list, or directly to me.
>

On doesn't have to be capitalized:
SQLite Release 3.20.0 On 2017-07-20


Period missing on these items:
Added the STMT
Provide the SQLITE_PREPARE_PERSISTENT






> --
> D. Richard Hipp
> d...@sqlite.org


-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-13 Thread Richard Hipp 
On 7/13/17, David Raymond  wrote:
> Is there a page for the LSM1 extension?
>

We were just discussing that internally.  The current decision is to
omit the LSM1 bullet from the change log for this release.  We need to
spend more time on that extension before it is officially announced.
The code is still in the source tree but it is still unofficial and
experimental.

If you reload the change log, you will find that bullet is now missing.

I might add back a bullet about the code being present in the source
tree as "work-in-progress" and not for general consumption, yet.
-- 
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] Version 3.20.0 coming soon...

2017-07-13 Thread David Raymond 
Is there a page for the LSM1 extension?

Minor typos:

https://sqlite.org/draft/releaselog/3_20_0.html
"Evaluation WHERE clause constraints" -> "Evaluate WHERE clause constraints"

https://sqlite.org/draft/stmt.html
"The enable the STMT extension" -> "To enable the STMT extension"
"in default builts of" -> "in default builds of"

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, July 13, 2017 2:50 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Version 3.20.0 coming soon...

The 3.20.0 release of SQLite is expected in about a week.  Please
report any issues that you have with the beta as soon as possible.

Code is available in the "prerelease snapshot" at
https://sqlite.org/download.html

An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html

You can send issues to this email list, or directly to me.

-- 
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] Version 3.20.0 coming soon...

2017-07-13 Thread Richard Hipp 
The 3.20.0 release of SQLite is expected in about a week.  Please
report any issues that you have with the beta as soon as possible.

Code is available in the "prerelease snapshot" at
https://sqlite.org/download.html

An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html

You can send issues to this email list, or directly to me.

-- 
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] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

2017-07-13 Thread Richard Hipp 
On 7/13/17, Jens Alfke  wrote:
>
> Have you tested Clang? These days it may generate even better code,

We do a lot of testing with clang - it is part of our standard test
protocol.  It generates code that is slightly larger and slower than
the GCC-generated code, but not nearly as bad as ICC.  I have not
studied the machine code generated by clang to see what the difference
is, as I just did for ICC.

-- 
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] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

2017-07-13 Thread Jens Alfke 

> On Jul 13, 2017, at 7:49 AM, Richard Hipp  wrote:
> 
> I've spent some time investigating this, and I think the answer is
> that GCC simply generates better code.

Have you tested Clang? These days it may generate even better code, especially 
with -Ofast and the link-time optimizer. (SQLite itself won’t benefit from LTO 
because it’s already agglomerated before being compiled, but LTO allows SQLite 
code to be inlined into calling functions in the client program.)

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

Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

2017-07-13 Thread Richard Hipp 
On 7/9/17, Bob Friesenhahn  wrote:
> On Sat, 8 Jul 2017, Richard Hipp wrote:
>>
>> gcc-5.4: 491585 bytes, 1,124 million CPU cycles.
>> icc-17.0: 536596 bytes, 1,274 million CPU cycles
>
> Sqlite3 has been cycle-optimized for GCC.  You now have 29-days to
> also cycle-optimize it for ICC.

I've spent some time investigating this, and I think the answer is
that GCC simply generates better code.

Here is one simple example.  The source code at
https://www.sqlite.org/src/artifact/a7ca64?ln=58-60 shows a function
that takes two parameters.  If the second parameter is not NULL, then
invoke another function, otherwise return WRC_Continue (which happens
to be zero).

ICC generates the following code for that function:

sqlite3WalkExpr:
   pushq  %rsi
   testq%rsi, %rsi
   je ..B1210.4
   call  walkExpr
   jmp  ..B1210.5
..B1210.4:
   xorl  %eax, %eax
..B1210.5:
   popq%rci
   ret

GCC uses tail recursion on the walkExpr() call, which is faster, since
"jmp" does not have to push the return address onto the stack and is
thus faster than "call".  GCC also avoids saving the second parameter
on the stack (which is pointless - why does ICC even do that?) and
thereby avoids a push and a pop.  The GCC code is as follows:

sqlite3WalkExpr:
   testq%rsi, %rsi
   je .L2706
   jmp  walkExpr
.L2706:
   xorl  %eax, %eax
   ret

Thus the GCC implementation is both smaller and about twice as fast as
the ICC implementation.  I don't know of anything I can do at the
source-code level to help ICC generate better code here.

The above is just one example.  I looked at many others.  As far as I
can see, GCC is simply a better compiler than ICC when targeting x64.

The assembly code examples above were generated using -Os
optimization.  The performance results are similar (GCC is faster than
ICC) when I use -O3, but there is a lot more code movement and so the
examples are more difficult to follow.

-- 
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] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-13 Thread mohan_gn 
Hi Simon,

Thank you for your suggestion. Will check it out.

Regards,
Mohan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Query-on-bug-fix-for-ticket-7ffd1ca1d2ad4ec-tp96175p96608.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users