Re: [sqlite] bug? suggestion? add full version number to files, so tools can warn of forward-incompatibility

[Richard Hipp]

On 12/30/16, Bennett Haselton  wrote:
>
> Presumably it's not possible for a tool to output a detailed message
> like "Your file was generated by SQLite library version 3.6.2, but this
> tool only supports versions up to 3.5.1",

OK.  I have your request to enhance the error message that is issued
when the library encounters a later version of the database.  If that
change makes it into the core, it will take effect with version 3.17.0
or later.  (3.16.0 has already entered testing.)  Unfortunately, all
versions of SQLite from 3.0.0 through 3.16.0 just return
SQLITE_NOTADB.  And so the problem will persist for the billion copies
of SQLite that are already in circulation.  But we can't really change
that.

-- 
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] bug? suggestion? add full version number to files, so tools can warn of forward-incompatibility

[Richard Hipp]

On 12/29/16, Bennett Haselton  wrote:
> Yesterday I spent some time trying to solve a SQLite problem that turned
> out to be due to using an old version of sqlite3, so that when I tried
> to open a database created with a newer SQLite library, it would output
> "Error: file is encrypted or is not a database".

I think newer versions of SQLite give better error messages.

What was the version number for SQLite on the old tool?

-- 
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] bug? suggestion? add full version number to files, so tools can warn of forward-incompatibility

[Bennett Haselton]

Yesterday I spent some time trying to solve a SQLite problem that turned 
out to be due to using an old version of sqlite3, so that when I tried 
to open a database created with a newer SQLite library, it would output 
"Error: file is encrypted or is not a database".  Later I found out this 
was because that SQLite 3 file versions are generally not 
forward-compatible and my sqlite3 tool was too old.


Opening the database file in a hex editor, I saw it says at the very 
beginning, "SQLite format 3".


Can I suggest storing the full version number of the SQLite library in 
the format of the SQLite file.  That way, tools using the SQLite library 
can use this to output a couple of useful errors and warnings:
- If a user tries to use a tool to open a database file, and the 
database file was created with a more recent version of the library than 
the tool was, the tool can warn that it's probably not 
forward-compatible and you need a more recent version of the tool.
- If a user uses a tool to open a database file, and the library version 
of the tool is *newer* than the library version that created the 
database file (I assume this will work, if SQLite 3 format versions are 
backward-compatible), the tool can warn you that if you save changes to 
the database file, it will be saved in the newer file format, and the 
file may no longer be compatible with the program that generated it 
originally.


Bennett

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Dominique Pellé]

Bob Friesenhahn  wrote:

> On Thu, 29 Dec 2016, Darko Volaric wrote:
>
>> What are you basing that theory on?
>
>
> Perf is claimed to provide very good results but they are real results based
> on real measurements.  Due to this, the measured results are very different
> for the first time the program is executed and the second time it is
> executed.  Any other factor on the machine would impact perf results.
>
> It seems that cachegrind produces absolutely consistent results which do not
> depend on I/O, multi-core, or VM artifacts.

You're right. Consistency can matter more where measuring
small improvements that add up.

I just tried "valgrind --tool=cachegrind ..." and "perf stat ..."
with the same command. Valgrind result was more indeed
more consistent across multiple runs.

Regarding speed of measurement, the same command
took 13.8 sec with cachegrind vs only 0.28 sec with "perf stat"
and 0.27 sec with neither cachegrind nor perf stat. So
perf stat has almost no overhead whereas cachegrind has a
big overhead, making it impractical when measuring slow
commands.

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

On 12/29/16, Bob Friesenhahn  wrote:
>
> Perf is claimed to provide very good results but they are real results
> based on real measurements.  Due to this, the measured results are
> very different for the first time the program is executed and the
> second time it is executed.

If this is true, then perf is entirely unsuitable for
microoptimization, since microoptimization depends on having
reproducible results.
-- 
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] SQLite 3.16.0 enters testing

[Bob Friesenhahn]

On Thu, 29 Dec 2016, Darko Volaric wrote:


What are you basing that theory on?


Perf is claimed to provide very good results but they are real results 
based on real measurements.  Due to this, the measured results are 
very different for the first time the program is executed and the 
second time it is executed.  Any other factor on the machine would 
impact perf results.


It seems that cachegrind produces absolutely consistent results which 
do not depend on I/O, multi-core, or VM artifacts.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.16.0 enters testing

[Darko Volaric]

What are you basing that theory on?

On Thu, Dec 29, 2016 at 10:29 PM, Dominique Pellé  wrote:

> Richard Hipp  wrote:
>
> > On 12/29/16, Bob Friesenhahn  wrote:
> >> Is there a way to know how well cachegrind CPU
> >> cycles map to real-world CPU usage?
> >
> > Not that I know of.  If you have any suggestions, please speak up.
>
> The 'stat' command of Linux perf tool would be a good
> candidate to replace cachgrind stats. It's much faster
> than cachegrind. I suppose that it also gives more realistic
> results. See:
>
> https://perf.wiki.kernel.org/index.php/Tutorial#Counting_with_perf_stat
>
> Dominique
> ___
> 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] SQLite 3.16.0 enters testing

[Dominique Pellé]

Richard Hipp  wrote:

> On 12/29/16, Bob Friesenhahn  wrote:
>> Is there a way to know how well cachegrind CPU
>> cycles map to real-world CPU usage?
>
> Not that I know of.  If you have any suggestions, please speak up.

The 'stat' command of Linux perf tool would be a good
candidate to replace cachgrind stats. It's much faster
than cachegrind. I suppose that it also gives more realistic
results. See:

https://perf.wiki.kernel.org/index.php/Tutorial#Counting_with_perf_stat

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Bob Friesenhahn]

On Thu, 29 Dec 2016, Richard Hipp wrote:


I discovered this technique starting at about 3.8.0.


Just to confirm:  I looked back through the old release checklists
(https://www.sqlite.org/checklists/) and the first reference to
cachegrind seems to be for 3.8.0, in the checklist item 25b.


That would explain the inflection point and more rapid progress 
starting at that time.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

On 12/29/16, Richard Hipp  wrote:
> On 12/29/16, Bob Friesenhahn  wrote:
>>
>> At which point in the timeline shown at
>> https://www.sqlite.org/draft/cpu.html did sqlite developers start
>> tuning the implementation based on cachegrind results?
>>
>
> I discovered this technique starting at about 3.8.0.

Just to confirm:  I looked back through the old release checklists
(https://www.sqlite.org/checklists/) and the first reference to
cachegrind seems to be for 3.8.0, in the checklist item 25b.

-- 
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] SQLite 3.16.0 enters testing

[Richard Hipp]

On 12/29/16, Bob Friesenhahn  wrote:
>
> At which point in the timeline shown at
> https://www.sqlite.org/draft/cpu.html did sqlite developers start
> tuning the implementation based on cachegrind results?
>

I discovered this technique starting at about 3.8.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] SQLite 3.16.0 enters testing

[Bob Friesenhahn]

On Thu, 29 Dec 2016, Richard Hipp wrote:


On 12/29/16, Bob Friesenhahn  wrote:

Is there a way to know how well cachegrind CPU
cycles map to real-world CPU usage?


Not that I know of.  If you have any suggestions, please speak up.

I tried to make clear in the docs that I consider cachegrind to be a
proxy for CPU usage rather than a precise measurement.


If the proxy maps well to real results, then the improved results are 
a considerable accomplishment.


At which point in the timeline shown at 
https://www.sqlite.org/draft/cpu.html did sqlite developers start 
tuning the implementation based on cachegrind results?


Thanks,

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

On 12/29/16, Bob Friesenhahn  wrote:
> Is there a way to know how well cachegrind CPU
> cycles map to real-world CPU usage?

Not that I know of.  If you have any suggestions, please speak up.

I tried to make clear in the docs that I consider cachegrind to be a
proxy for CPU usage rather than a precise measurement.
-- 
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] SQLite 3.16.0 enters testing

[Bob Friesenhahn]

On Thu, 29 Dec 2016, Richard Hipp wrote:


Change log for the 3.16.0 release:
https://www.sqlite.org/draft/releaselog/3_16_0.html


What caught my attention the most about the release log was the "Uses 
9% fewer CPU cycles" and link to https://www.sqlite.org/draft/cpu.html 
where it describes using valgrind's cachegrind to meausure CPU cycle 
usage.


While the CPU is instrumented (by valgrind) at the instruction level 
and cache misses are recorded, it seems to me that cachegrind is an 
advanced simulation.  Is there a way to know how well cachegrind CPU 
cycles map to real-world CPU usage?  If sqlite3 consumes 2X less "CPU 
cycles" since 2009 is there a way to measure how much less actual CPU 
time that it takes in order to validate that there is a clear 
relationship?


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.16.0 enters testing

[Simon Slavin]

On 29 Dec 2016, at 8:09pm, Richard Hipp  wrote:

> All corrections should be caught up now.  Please continue proofreading!

You fixed the problem I noted.  I’m itching to see if I can make '.mode quote' 
do the wrong thing with some of my test data.

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

All corrections should be caught up now.  Please continue proofreading!


-- 
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] SQLite 3.16.0 enters testing

[Simon Slavin]

On 29 Dec 2016, at 6:52pm, Richard Hipp  wrote:

> Draft 3.16.0 website: https://www.sqlite.org/draft/

The documentation for the shell tool



does not yet reflect the new dot commands (.imposter and .mode quote ?  others 
?).

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Simon Slavin]

On 29 Dec 2016, at 6:52pm, Richard Hipp  wrote:

> Change log for the 3.16.0 release:
> https://www.sqlite.org/draft/releaselog/3_16_0.html

In which I find ...

>   • This feature could be used to implement information schema by first 
> creating a separate schema using
> 
> ATTACH
>  ':memory:' AS 'information_schema';
> 
> Then creating a VIEWs in that schema that implement the official information 
> schema tables using table-valued PRAGMA functions.

I anticipate three different implementations of this in the next three months.

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

Re: [sqlite] SQLite 3.16.0 enters testing

[Donald Griggs]

Sorry to have dribbled these out:

From the same page: https://www.sqlite.org/draft/imposter.html

"can be accessed as if it where a "   (were)

"table can be queries to see" (queried)

"Each table and each index in SQLite is stored"  (It's a trivial nit at any
rate, but I suspect should be "are stored."
http://www.quickanddirtytips.com/education/grammar/compound-subjects )



On Thu, Dec 29, 2016 at 2:17 PM, Donald Griggs  wrote:

> Tiny typo in docs:
>
> Page: https://www.sqlite.org/draft/imposter.html
>
> "This will not cause any immediately problems"  (remove "ly")
>
>
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

On 12/29/16, Donald Griggs  wrote:
> Tiny typo in docs:
>
> "This will not cause any immediately problems"  (remove "ly")

Fixed.  Thanks for reporting.

-- 
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] SQLite 3.16.0 enters testing

[Donald Griggs]

Tiny typo in docs:

Page: https://www.sqlite.org/draft/imposter.html

"This will not cause any immediately problems"  (remove "ly")


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

[sqlite] SQLite 3.16.0 enters testing

[Richard Hipp]

SQLite version 3.16.0 has entered testing.  Only bug fixes will go on
trunk until the release.

Please download and test the latest pre-release snapshot from
https://www.sqlite.org/download.html and report any problems to this
mailing list, or directly to me.

Change log for the 3.16.0 release:
https://www.sqlite.org/draft/releaselog/3_16_0.html

Draft 3.16.0 website: https://www.sqlite.org/draft/

A new release checklist is up at
https://www.sqlite.org/checklists/316/index - the release will
occur when that checklist goes all green.

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