Re: [sqlite] Does wal-file support MMAP？

[Dan Kennedy]

On 11/09/2017 08:59 AM, advancenOO wrote:

I am trying to understand how SQLITE_DEFAULT_MMAP_SIZE works and I think db
files can use MMAP by using this compilation option, as Sqlite3PagerGet()
mapped to getPageMMap().
And I know "/the current VFS implementations use a mmapped file for the
wal-index/"

But is there a way that wal-files can use MMAP? Why not? As I guess it can
improve the IO performance in WAL mode.


No way to do that right now. It would require a VFS interface change I 
think.


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

[sqlite] Does wal-file support MMAP？

[advancenOO]

I am trying to understand how SQLITE_DEFAULT_MMAP_SIZE works and I think db
files can use MMAP by using this compilation option, as Sqlite3PagerGet()
mapped to getPageMMap(). 
And I know "/the current VFS implementations use a mmapped file for the
wal-index/"

But is there a way that wal-files can use MMAP? Why not? As I guess it can
improve the IO performance in WAL mode.

Thanks for your insights.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, korablev  wrote:
> What is the timeline of this feature? Will it be implemented in the nearest
> future?

Low priority.  I do not like automatic index feature, because I think
that app developers should do an appropriate CREATE INDEX instead.  Of
course, it is not possible to a CREATE INDEX on a subquery, so
automatic indexes make sense in that context, which is why I keep the
optimization around.

But since this has never come up before, it does not seem like a huge
problem and we have a number of other priorities at the moment.

> And what optimizations doesn't also work on without rowid tables? Is
> the query planner good at optimizing stuff on without rowid tables at all?

AFAIK every (except automatic indexes) works for both rowid and
without-rowid tables.  But I had forgotten that automatic indexes
didn't work for without rowid tables, so perhaps there is something
else I have overlooked.

-- 
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] Automatic indexes don't work on without rowid tables

[korablev]

What is the timeline of this feature? Will it be implemented in the nearest
future? And what optimizations doesn't also work on without rowid tables? Is
the query planner good at optimizing stuff on without rowid tables at all?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most efficient way to detect on-disk change

[Wout Mertens]

Thank you all! I really have to do a thorough read of all the pragmas,
there are so many useful things in there! The user version sounds exactly
like what I should be using for storing the db version, and presumably the
data_version is a little faster still than reading the user version.

@Keith, the reason I'm caching is because the data itself is reasonably
small, and while I can query + parse the JSON in <2ms, using cached data
from memory takes microseconds…

Besides, memory is relatively cheap these days, and in general (safe)
caching is beneficial. I like reading about
https://en.wikipedia.org/wiki/Cache-oblivious_algorithm - any amount of
cache can improve performance with these…

On Wed, Nov 8, 2017 at 12:22 PM Dan Kennedy  wrote:

> On 11/08/2017 03:55 PM, Dominique Devienne wrote:
> > On Wed, Nov 8, 2017 at 7:45 AM, Dan Kennedy 
> wrote:
> >
> >> On 7 Nov 2017, at 6:53pm, David Raymond 
> wrote:
> >>> I think pragma data_version is what you're looking for.
>  http://www.sqlite.org/pragma.html#pragma_data_version
> 
> >> I think it's the opposite. For connection A, the value of "PRAGMA
> >> data_version" does not change as a result of commits by connection A. It
> >> changes if the db is modified by any other connection, regardless of
> >> whether or not that other connection resides in a different process or
> not.
> >>
> >> "The integer values returned by two invocations of "PRAGMA data_version"
> >> from the same connection will be different if changes were committed to
> the
> >> database by any other connection in the interim. The "PRAGMA
> data_version"
> >> value is unchanged for commits made on the same database connection."
> >
> > Hi Dan. So you confirm David's answer, provided OP also tracks change
> made
> > by the local connection, in addition to tracking pragma data_version?
>
> That's right.
>
> The original use case was an application-level cache of objects
> associated with a single database connection. The cache should be
> invalidated whenever the database is written. So the app would:
>
>a) invalidate the cache whenever it wrote to the db, and
>b) checked that "PRAGMA data_version" has not changed before using an
> object from the cache (and invalidating the entire cache it if it had).
>
> I guess the logic was that the app could implement more fine-grained
> cache invalidation in (a) if required.
>
> Dan.
>
>
>
>
>
>
>
> > I just want to make sure I understand your answer correctly. Thanks, --DD
> > ___
> > 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] Can I determine the collation of a text argument to a function?

[Richard Hipp]

On 11/8/17, Jens Alfke  wrote:
> Is it possible for a user-defined function to determine the collation
> associated with its argument(s)? I'm implementing some custom string
> matching/comparison functions, and I want their case-sensitivity to depend
> on the collation of the first argument (the LHS).

It is not currently possible for a UDF to find the collation
associated with its arguments.

Some built-in functions (ex: min() and max()) do this.  But they do so
using internal APIs and with help from the query planner.  The
capability is not available to ordinary UDFs.

-- 
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] Can I determine the collation of a text argument to a function?

[Jens Alfke]

Is it possible for a user-defined function to determine the collation 
associated with its argument(s)? I'm implementing some custom string 
matching/comparison functions, and I want their case-sensitivity to depend on 
the collation of the first argument (the LHS). 

I’ve searched sqlite3.h for “collate” and “collation" but didn’t find anything 
that looks relevant.

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

Re: [sqlite] CLI: .dbinfo does not work in version 3.21

[Richard Hipp]

On 11/8/17, Richard Hipp  wrote:
>
> Expect to see fresh precompiled binaries that fix this problem within
> a few hours.
>

New builds for the "sqlite-tools-*-321.zip" packages are now
available from the https://sqlite.org/download.html webpage

-- 
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] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, Peter Da Silva  wrote:
> On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp"
> 
> wrote:
>> The technical reason for this is that, from the point of view of the query
>> planner, a WITHOUT ROWID table is really a covering index
>
> So basically the whole table is stored inline with the primary key index?

That's the basic idea, yes.

-- 
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] CLI: .dbinfo does not work in version 3.21

[Richard Hipp]

On 11/8/17, Klaas Van B.  wrote:
> In 3.20.1 the dot-command dbinfo still worked, but since 3.21.0 not anymore

This was a boo-boo on our precompiled-binary build script.  The
".dbinfo" command was modified to require a special compile-time
option (specifically -DSQLITE_ENABLE_DBPAGE_VTAB) and we failed to
make that change in our build script for the website.  The new
compile-time option is added to all the makefiles, so if you will
download any of the source code packages and build your own, the
.dbinfo command should work fine.

So this is not a bug in SQLite but rather a bug in our website build procedure.

Thanks for pointing this out.

The precompiled-binary build script is out-of-tree and testing the
precompiled-binary builds of the website is not part of our release
checklist (https://www.sqlite.org/checklists/321/index) which is
how this bug managed to slip through.  We'll be sure to add a test for
the precompiled-binary build script prior to the next release.

Expect to see fresh precompiled binaries that fix this problem within
a few hours.

-- 
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] Automatic indexes don't work on without rowid tables

[Peter Da Silva]

On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:
> The technical reason for this is that, from the point of view of the query 
> planner, a WITHOUT ROWID table is really a covering index

So basically the whole table is stored inline with the primary key index?

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

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, korablev  wrote:
> sqlite> create table t1(a primary key, b) without rowid;
> sqlite> create table t2(a primary key, b) without rowid;
> sqlite> explain query plan select * from t1, t2 where t1.b = t2.b;
> 0|0|0|SCAN TABLE t1
> 0|1|1|SCAN TABLE t2
> sqlite> create table t3(a primary key, b);
> sqlite> create table t4(a primary key, b);
> sqlite> explain query plan select * from t4, t3 where t3.b = t4.b;
> 0|0|0|SCAN TABLE t4
> 0|1|1|SEARCH TABLE t3 USING AUTOMATIC COVERING INDEX (b=?)
>
> Even if I inserted ~100 rows in t3 and t4, query planner anyways
> wouldn't use automatic indexes. So, why tables without rowid can't use
> automatic indexes optimization?

Correct.  Automatic indexes do not work with WITHOUT ROWID tables. I
had forgotten about that limitation. Adding support for automatic
indexes on WITHOUT ROWID tables is a reasonable feature request.

The technical reason for this is that, from the point of view of the
query planner, a WITHOUT ROWID table is really a covering index, and
the query planner never tries to create an index on an index.  We'll
need to go in and teach the query planner that a WITHOUT ROWID table
is a special case.


-- 
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] CLI: .dbinfo does not work in version 3.21

[Klaas Van B.]

In 3.20.1 the dot-command dbinfo still worked, but since 3.21.0 not anymore
sqlite32001 e0.sqb
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
sqlite> .dbinfo
database page size:  512
write format:    1
read format: 1
reserved bytes:  0
file change counter: 15
database page count: 6
freelist page count: 0
schema cookie:   4
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:    1106
application id:  0
software version:    3009002
number of tables:    4
number of indexes:   0
number of triggers:  0
number of views: 0
schema size: 359

I did not make any changes to the file and now I get:
sqlite3 e0.sqb
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> .dbinfo
unable to read database header

ll e0.sqb
-rw-rw-r--  1 staff  3072 Nov  6 18:03:30 2015 e0.sqb



Kind regards | Vriendelijke groeten | Cordiali saluti,
Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy
https://www.linkedin.com/in/klaas-van-buiten-0325b2102
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most efficient way to detect on-disk change

[Dan Kennedy]

On 11/08/2017 03:55 PM, Dominique Devienne wrote:

On Wed, Nov 8, 2017 at 7:45 AM, Dan Kennedy  wrote:


On 7 Nov 2017, at 6:53pm, David Raymond  wrote:

I think pragma data_version is what you're looking for.

http://www.sqlite.org/pragma.html#pragma_data_version


I think it's the opposite. For connection A, the value of "PRAGMA
data_version" does not change as a result of commits by connection A. It
changes if the db is modified by any other connection, regardless of
whether or not that other connection resides in a different process or not.

"The integer values returned by two invocations of "PRAGMA data_version"
from the same connection will be different if changes were committed to the
database by any other connection in the interim. The "PRAGMA data_version"
value is unchanged for commits made on the same database connection."


Hi Dan. So you confirm David's answer, provided OP also tracks change made
by the local connection, in addition to tracking pragma data_version?


That's right.

The original use case was an application-level cache of objects 
associated with a single database connection. The cache should be 
invalidated whenever the database is written. So the app would:


  a) invalidate the cache whenever it wrote to the db, and
  b) checked that "PRAGMA data_version" has not changed before using an 
object from the cache (and invalidating the entire cache it if it had).


I guess the logic was that the app could implement more fine-grained 
cache invalidation in (a) if required.


Dan.








I just want to make sure I understand your answer correctly. Thanks, --DD
___
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] Most efficient way to detect on-disk change

[Dominique Devienne]

On Wed, Nov 8, 2017 at 7:45 AM, Dan Kennedy  wrote:

> On 7 Nov 2017, at 6:53pm, David Raymond  wrote:
>>
>> I think pragma data_version is what you're looking for.
>>> http://www.sqlite.org/pragma.html#pragma_data_version
>>>
>>
> I think it's the opposite. For connection A, the value of "PRAGMA
> data_version" does not change as a result of commits by connection A. It
> changes if the db is modified by any other connection, regardless of
> whether or not that other connection resides in a different process or not.
>
> "The integer values returned by two invocations of "PRAGMA data_version"
> from the same connection will be different if changes were committed to the
> database by any other connection in the interim. The "PRAGMA data_version"
> value is unchanged for commits made on the same database connection."


Hi Dan. So you confirm David's answer, provided OP also tracks change made
by the local connection, in addition to tracking pragma data_version?
I just want to make sure I understand your answer correctly. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users