Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Michael Falconer
>
> There is no "year 0" between 1 BC and 1 AD.  This is perhaps the most
> common fencepost problem in existance.  The "great renaming" of AD to CE
> and doing away with BC by replacing them with "off by one" numbers less
> than 1 does not change the fact that there was, in fact, no year 0.

Obviously the character(s) responsible  for dates etc were NOT C
programmers!

On Tue, 31 Dec 2019 at 14:45, Richard Damon 
wrote:

> On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote:
> > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf 
> wrote:
> >>
> >> On Monday, 30 December, 2019 19:29, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
> >>
> >>> As we approach the end of yet another year ( and indeed decade ).
> >> Technically, every year is the end of a decade, if one means the
> immediately preceding ten years.
> >>
> >> However, if you mean the end of the second decade of the 21st century,
> you will have to wait another year for that.  January 1st, 0001 AD was the
> first day of the year 1.  The first decade ended at the end of December
> 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic
> Gregorian calendar).
> > Languages don't work like this.
> >
> > https://www.collinsdictionary.com/dictionary/english/decade
> >
> > Cheers
>
> Its a difference between ordinals and numerals. The 20th century was
> from the beginning of 1901 to the end of 2000. We also have the century
> called the 1900's which went from 1900 to the end of 1999.
>
> Decade would work the same way, the 202st decade goes from 2011 to end
> of 2020, but the 2010s go from 2010 to end of 2019.
>
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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


Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Richard Damon

On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote:

On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf  wrote:


On Monday, 30 December, 2019 19:29, Michael Falconer 
 wrote:


As we approach the end of yet another year ( and indeed decade ).

Technically, every year is the end of a decade, if one means the immediately 
preceding ten years.

However, if you mean the end of the second decade of the 21st century, you will 
have to wait another year for that.  January 1st, 0001 AD was the first day of 
the year 1.  The first decade ended at the end of December 31st 0011 AD, not 
December 31st, 0010 AD. (if following the proleptic Gregorian calendar).

Languages don't work like this.

https://www.collinsdictionary.com/dictionary/english/decade

Cheers


Its a difference between ordinals and numerals. The 20th century was 
from the beginning of 1901 to the end of 2000. We also have the century 
called the 1900's which went from 1900 to the end of 1999.


Decade would work the same way, the 202st decade goes from 2011 to end 
of 2020, but the 2010s go from 2010 to end of 2019.


--
Richard Damon

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


Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Pierpaolo Bernardi
On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf  wrote:
>
>
> On Monday, 30 December, 2019 19:29, Michael Falconer 
>  wrote:
>
> > As we approach the end of yet another year ( and indeed decade ).
>
> Technically, every year is the end of a decade, if one means the immediately 
> preceding ten years.
>
> However, if you mean the end of the second decade of the 21st century, you 
> will have to wait another year for that.  January 1st, 0001 AD was the first 
> day of the year 1.  The first decade ended at the end of December 31st 0011 
> AD, not December 31st, 0010 AD. (if following the proleptic Gregorian 
> calendar).

Languages don't work like this.

https://www.collinsdictionary.com/dictionary/english/decade

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


Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Keith Medcalf

On Monday, 30 December, 2019 19:29, Michael Falconer 
 wrote:

> As we approach the end of yet another year ( and indeed decade ).

Technically, every year is the end of a decade, if one means the immediately 
preceding ten years.

However, if you mean the end of the second decade of the 21st century, you will 
have to wait another year for that.  January 1st, 0001 AD was the first day of 
the year 1.  The first decade ended at the end of December 31st 0011 AD, not 
December 31st, 0010 AD. (if following the proleptic Gregorian calendar).

The day before January 1st 0001 AD was December 31st 0001 BC.

The first day of the 21st century was January 1st, 2001; it was also the first 
day of the first decade of the 21st century.
The first day of the 2nd decade of the 21st century was January 1st, 2011.
Next year is the last year of the 2nd decade of the 21st century.
The 3rd decade of the 21st century will commence at midnight January 1st, 2021.

There is no "year 0" between 1 BC and 1 AD.  This is perhaps the most common 
fencepost problem in existance.  The "great renaming" of AD to CE and doing 
away with BC by replacing them with "off by one" numbers less than 1 does not 
change the fact that there was, in fact, no year 0.

-- 
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] Causal profiling

2019-12-30 Thread Simon Slavin
On 31 Dec 2019, at 2:21am, Alexander Vega  wrote:

> Could there be some compile time option to force Linux or Windows
> statically?

One of the problems with this is that there are a lot of utility libraries 
built around SQLite.  These are libraries provided for general-purpose use, 
often a language shim to make it easy to call SQLite from some language which 
isn't C or C++.

Compiler options which make SQLite behave significantly different generate 
fault reports (support calls, messages to this list, etc.).  Because someone 
gets the 'different' version of SQLite and wonders why it doesn't do what the 
version they're used to does.

If SQLite3 ever moves to SQLite4, a big change in behaviour may be acceptable: 
people are used to that in full version increases.  But while SQLite remains 
version 3.x.x, an option like that would have to be thought through very 
carefully.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] 18 minutes 41 seconds

2019-12-30 Thread Michael Falconer
Great work but pretty much what we have come to expect from DRH and the
SQLite team. As we approach the end of yet another year ( and indeed decade
) can I indulge the list in a simple congratulations to all involved and to
the outstanding support on offer when a member of this email list. SQLite
is personally my favourite software release  and IMHO right up there as the
most useful and reliable software of all time! Thanks to all who have made
it possible.


On Tue, 31 Dec 2019 at 11:13, Richard Hipp  wrote:

> That's the total elapse time from me checking in a bug (check-in
> https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel
> Rigger's fuzzer had located the bug and issued a ticket against it:
> (ticket https://www.sqlite.org/src/info/892575cdba4e1e36).
>
> Well, at least the bisect didn't take very long!
>
> --
> 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
>


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


Re: [sqlite] Causal profiling

2019-12-30 Thread Alexander Vega
I watched the video and wondered about the virtual table calls within
sqlite as well. Without them you would have no VFS though... so they are
needed. Could there be some compile time option to force Linux or Windows
statically? Maybe.

I do not know the effect of collecting debug information using -g, but if
using -O2 to compile sqlite with GCC the following optimizations from
https://gcc.gnu.org/onlinedocs/gcc/Optimize-Options.html#Optimize-Options are
enabled:
-fdevirtualize
-fdevirtualize-speculatively

So depending on what compiler used and version etc, those virtual calls
could all disappear anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] 18 minutes 41 seconds

2019-12-30 Thread Richard Hipp
That's the total elapse time from me checking in a bug (check-in
https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel
Rigger's fuzzer had located the bug and issued a ticket against it:
(ticket https://www.sqlite.org/src/info/892575cdba4e1e36).

Well, at least the bisect didn't take very long!

-- 
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] SQL help

2019-12-30 Thread Jose Isaias Cabrera


x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

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


Re: [sqlite] Bug Report

2019-12-30 Thread Jose Isaias Cabrera

Bigthing Do, on Friday, December 27, 2019 01:56 PM, wrote...
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1
> ) SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY
> col1 DESC ) FROM table1 ;
>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10
> 20:19:45`

Also with 3.30.0...

16:41:27.70>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM 
table1 ) SELECT col2 FROM table1 ORDER BY 1 ;
sqlite> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER 
BY col1 DESC ) FROM table1 ;

16:42:07.53>

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


Re: [sqlite] Causal profiling

2019-12-30 Thread Jens Alfke


> On Dec 30, 2019, at 7:19 AM, Doug  wrote:
> 
> I am suggesting that if the we added the global calls to the underlying 
> functions to the API - that is, the functions that are called by the function 
> table indirection - then one could code the application to call the 
> underlying functions.

What you're describing is basically the SQLITE_CONFIG_SINGLETHREAD compile 
option. In that configuration SQLite does not use any mutexes, and you're 
responsible for "call[ing] the underlying functions" (the mutex lock/unlock 
functions) yourself.

However, in this configuration any client code that makes concurrent calls to 
SQLite — and there is a lot of code like that — will crash and burn.

> If you are going to tell me that you need to maintain two versions of your 
> application if you run it in a single-thread environment or a multi-thread 
> environment

I don't know if someone else was saying that; what I was pointing out earlier 
is that many clients of SQLite link to it as a shared library provided by the 
OS, either built-in (as on iOS and macOS) or installed by a central package 
manager (Linux). This means they don't specify compile-time options, they 
implicitly get the behavior configured by the OS or package vendor, which I'd 
guess is probably MULTITHREAD. If the SQLite they link with changes its 
behavior to avoid mutexes, many of these programs will break.

> Thinking about it, I'm surprised that the C API isn't just a set of macros 
> already.

What you're describing is basically the effect of link-time optimization (LTO). 
The optimizer runs over the entire program's compiled code (not just one 
compilation unit) and applies optimizations such as inlining. This can indeed 
have a big impact.

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


Re: [sqlite] Causal profiling

2019-12-30 Thread Richard Damon

On 12/30/19 10:19 AM, Doug wrote:

OK, I get that the definition of the SQLite API is a (large) set of C function 
calls. And that changing the way they work under the covers would break 
something. (Check out the IDEA below!)

I'm wondering if your use case is - in the same application - that you 
dynamically change from option SQLITE_CONFIG_SINGLETHREAD to 
SQLITE_CONFIG_MULTITHREAD to SQLITE_CONFIG_SERIALIZED while your application is 
running? If that is the case, then your application knows which option to use 
dynamically; Otherwise, your application doesn't know and doesn't care which 
option is in effect.

I am suggesting that if the we added the global calls to the underlying 
functions to the API - that is, the functions that are called by the function 
table indirection - then one could code the application to call the underlying 
functions. If the application knows it's single-thread, then code it that way 
and get a 25% improvement (see the talk). If the application makes the choice 
of thread option dynamically, then the penalty for single-thread is at least 
double (application choice, SQLite indirection), so calling the (new) 
underlying function once you made the choice, performs better for that path. I 
grant you that probably will see little improvement on the threaded path.

If you are going to tell me that you need to maintain two versions of your 
application if you run it in a single-thread environment or a multi-thread 
environment, then let's define the (new) API to use a preprocessor macro to 
generate the right code for the option selected based on the values of 
SQLITE_CONFIG_SINGLETHREAD, el al. So now you have a single source but multiple 
executables corresponding to that source. And the choice of which executable to 
use becomes a configuration problem at application deployment time, or at 
application run time.

 IDEA! 
Thinking about it, I'm surprised that the C API isn't just a set of macros 
already. I can visualize a  C API composed of a set of macro definitions 
_identical_ to the current C function calls. They just use the extra knowledge 
of SQLITE_THREADSAFE and other SQLite compiler options to decide what 
application code to generate. Then the formal API doesn't change from a coding 
point of view. The generated code calls a set of under-the-cover functions 
which are not part of the API. The change doesn't require a new layer of 
testing; presumably, we already have test cases that test the same code using 
different compiler options. What about that?

Best, Doug

One BIG reason the C API can't be made into a set of macros is that the 
C API is basically the multi-language API for using the SQLite shared 
library in other languages. This requires that the C API functions be 
REAL functions that provide entry points into the shared library. It 
also, perhaps as a much more minor point, prevents taking the address of 
those functions to use in the application either to pass SQLite routines 
as call backs or make build your own virtual functions (admittedly, I 
don't know of a case where you would really want to do that).


I suspect that by far the vast majority of SQLite uses don't bundle the 
SQLite source code into the project, but link to it as an external resource.


Yes, there is perhaps an option to provide some specific configuration 
macros to allow SQLite to be optimized when included statically in a 
project, but those options shouldn't change the API.


--
Richard Damon

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


Re: [sqlite] Causal profiling

2019-12-30 Thread Jonathan Brandmeyer
On Wed, Dec 25, 2019 at 4:25 PM Richard Hipp  wrote:

>
> The video provided details on what they did.  I could not find any
> performance improvement by making mutexes direct calls instead of
> indirect calls.


This kind of optimization improves performance by reducing pressure on the
CPU's branch branch-target address caching and prediction.


> Maybe they are measuring performance differently than
> me.  (I use CPU cycle counts measured by valgrind.)
>

By default, valgrind doesn't model either branch predictors or
branch-target address caches.  Its model is somewhat primitive[0], but it
is available through command-line option `--branch-sim` [1,2].  When you
performance tested this change, did you enable that option?
tool/run-speed-test.sh certainly doesn't.

Of course, since those structures are shared globally, the performance
benefit for de-virtualizing any given function call is highly context
dependent.

Sincerely,
-- 
Jonathan Brandmeyer

[0]: https://valgrind.org/docs/manual/cg-manual.html#branch-sim
[1]:
https://www.valgrind.org/docs/manual/cl-manual.html#cl-manual.options.simulation
[2]: https://valgrind.org/docs/manual/cg-manual.html#cg-manual.cgopts
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Causal profiling

2019-12-30 Thread Doug
OK, I get that the definition of the SQLite API is a (large) set of C function 
calls. And that changing the way they work under the covers would break 
something. (Check out the IDEA below!)

I'm wondering if your use case is - in the same application - that you 
dynamically change from option SQLITE_CONFIG_SINGLETHREAD to 
SQLITE_CONFIG_MULTITHREAD to SQLITE_CONFIG_SERIALIZED while your application is 
running? If that is the case, then your application knows which option to use 
dynamically; Otherwise, your application doesn't know and doesn't care which 
option is in effect.

I am suggesting that if the we added the global calls to the underlying 
functions to the API - that is, the functions that are called by the function 
table indirection - then one could code the application to call the underlying 
functions. If the application knows it's single-thread, then code it that way 
and get a 25% improvement (see the talk). If the application makes the choice 
of thread option dynamically, then the penalty for single-thread is at least 
double (application choice, SQLite indirection), so calling the (new) 
underlying function once you made the choice, performs better for that path. I 
grant you that probably will see little improvement on the threaded path.

If you are going to tell me that you need to maintain two versions of your 
application if you run it in a single-thread environment or a multi-thread 
environment, then let's define the (new) API to use a preprocessor macro to 
generate the right code for the option selected based on the values of 
SQLITE_CONFIG_SINGLETHREAD, el al. So now you have a single source but multiple 
executables corresponding to that source. And the choice of which executable to 
use becomes a configuration problem at application deployment time, or at 
application run time. 

 IDEA! 
Thinking about it, I'm surprised that the C API isn't just a set of macros 
already. I can visualize a  C API composed of a set of macro definitions 
_identical_ to the current C function calls. They just use the extra knowledge 
of SQLITE_THREADSAFE and other SQLite compiler options to decide what 
application code to generate. Then the formal API doesn't change from a coding 
point of view. The generated code calls a set of under-the-cover functions 
which are not part of the API. The change doesn't require a new layer of 
testing; presumably, we already have test cases that test the same code using 
different compiler options. What about that?

Best, Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Friday, December 27, 2019 2:22 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Causal profiling
> 
> 
> 
> > On Dec 26, 2019, at 3:45 PM, Doug  wrote:
> >
> > Can you explain what the API is that you use to "set concurrency
> levels per connection", please? Is it a parameter on open() or its
> own function call? And how would the API break, exactly?
> 
> sqlite3_config(), specifically the SQLITE_CONFIG_SINGLETHREAD,
> SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED options.
> 
> This API would break because configuring those options at runtime
> would have no effect on behavior; the only thing that would change
> threading behavior would be the compile-time flags
> SQLITE_MUTEX_OMIT, etc.
> 
> (This is actually global, not per-connection, but that doesn't
> invalidate what I said.)
> 
> > The talk suggested removing the SQLite virtual table of
> functions (specifically the call to free a mutex). The user calls
> the function directly. How does that break an API?
> 
> If SQLite's implementation directly called the mutex lock/unlock
> functions, instead of indirecting, then there would be no way to
> control whether or not mutexes were used. In other words, it would
> be impossible to change any of the above options at runtime.
> 
> > The talk suggested removing the SQLite virtual table of
> functions (specifically the call to free a mutex). The user calls
> the function directly. How does that break an API?
> 
> No, the user does not call those functions directly. The code
> shown in the video is deep inside SQLite itself and not visible
> through the API. (You say you're using a TCL wrapper … so you may
> not be aware of what is or isn't in the C API. Trust me, I use the
> C API a lot.)
> 
> —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