[sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

Feature request for the Shell Tool: ".mode json".

Output should be as a JSON array of objects, with one object for each row of 
the table.  Output should start with the "[" character and end with "]".  Rows 
should be separated with ",\n".  Quotes in strings should be escaped for JSON, 
with a leading backslash.  NULL should be supported as the four lower-case 
characters "null", ignoring ".nullvalue".

The above setting should also affect the ".import filename [table]" command as 
described in section 8 of  .  Columns should be 
created as necessary.  Signed zeros should be imported as zero.

The above facilities should be implemented whether or not DSQLITE_ENABLE_JSON1 
was enabled when compiling the shell tool.  They are for export and import, not 
for handling JSON within SQLite.

Implementation questions:

I am not sure what the program should do if asked to import a value which is an 
array or object.  Perhaps, for compatibility with the JSON1 extension, those 
should be imported as a string.

I am not sure whether the program should respect the settings for ".separator" 
for JSON mode, either for output or .import.

I am not sure how BLOBs should be handled, either for output or .import.

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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Besides PostrgreSQL, MySQL also says x is TABLE with exactly one row as x
UNION x = x

WITH x AS (SELECT rand()) SELECT * FROM x UNION SELECT * FROM x;
rand()
0.6710336931711377

Peter

On Sat, Jan 20, 2018 at 7:31 PM, Cory Nelson  wrote:

> CTEs are not as-if-memoized in SQL Server either. I can't find any
> standards language saying if they should or shouldn't be, which
> typically indicates "anything goes".
>
> On Sat, Jan 20, 2018 at 5:57 PM, petern 
> wrote:
> > Exactly.  But that doesn't resolve the problem of duplicated
> > non-deterministic tables in a CTE.
> > CTE is the acryonym for Common TABLE Expression, not Common View
> > Expression.
> >
> > eg:  WITH x AS ()
> >SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
> > x1 UNION x2.
> >
> > Peter
> >
> > On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev  wrote:
> >
> >> The following statement executes the random() function twice -
> >>
> >> sqlite> select random() union all select random();
> >> random()
> >> 2678358683566407062
> >> -5528866137931448843
> >> sqlite> explain select random() union all select random();
> >> addr  opcode p1p2p3p4 p5  comment
> >>   -        -  --  -
> >> 0 Init   0 1 000
> >> 1 Function0  0 0 1 random(0)  00
> >> 2 ResultRow  1 1 000
> >> 3 Function0  0 0 1 random(0)  00
> >> 4 ResultRow  1 1 000
> >> 5 Halt   0 0 000
> >>
> >> but if random() is replaced with round(1.1) -
> >>
> >> sqlite> select round(1.1) union all select round(1.1);
> >> round(1.1)
> >> 1.0
> >> 1.0
> >> sqlite> explain select round(1.1) union all select round(1.1);
> >> addr  opcode p1p2p3p4 p5  comment
> >>   -        -  --  -
> >> 0 Init   0 6 000
> >> 1 Copy   2 1 000
> >> 2 ResultRow  1 1 000
> >> 3 Copy   2 1 000
> >> 4 ResultRow  1 1 000
> >> 5 Halt   0 0 000
> >> 6 Real   0 3 0 1.100
> >> 7 Function0  1 3 2 round(1)   01
> >> 8 Goto   0 1 000
> >>
> >> It seems to be executed once only. Does this happen because random() is
> >> flagged non-deterministic?
> >>
> >>
> >> On 19 January 2018 at 09:10, Clemens Ladisch 
> wrote:
> >>
> >> > petern wrote:
> >> > > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> >> > > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> >> > > sum(s),"(SELECT sum(s) FROM flips)"
> >> > > 1,3
> >> > > --Expected output is 1,1.
> >> > >
> >> > > Why isn't the constant notional table table [flips] materialized
> just
> >> > once
> >> > > per CTE?
> >> > >
> >> > > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
> >> >
> >> > Its documentation says:
> >> > 
> >> > | A useful property of WITH queries is that they are evaluated only
> once
> >> > | per execution of the parent query, even if they are referred to more
> >> > | than once by the parent query or sibling WITH queries. Thus,
> expensive
> >> > | calculations that are needed in multiple places can be placed within
> >> > | a WITH query to avoid redundant work. Another possible application
> is
> >> > | to prevent unwanted multiple evaluations of functions with side-
> >> > | effects. However, the other side of this coin is that the optimizer
> is
> >> > | less able to push restrictions from the parent query down into a
> WITH
> >> > | query than an ordinary subquery.
> >> >
> >> > This is an implementation detail of Postgres, and it is not required
> by
> >> > the SQL specification.  SQLite chose the other side of the coin.
> >> >
> >> >
> >> > Regards,
> >> > Clemens
> >> > ___
> >> > 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
>
>
>
> --
> Cory Nelson
> http://int64.org
> 

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[Cory Nelson]

CTEs are not as-if-memoized in SQL Server either. I can't find any
standards language saying if they should or shouldn't be, which
typically indicates "anything goes".

On Sat, Jan 20, 2018 at 5:57 PM, petern  wrote:
> Exactly.  But that doesn't resolve the problem of duplicated
> non-deterministic tables in a CTE.
> CTE is the acryonym for Common TABLE Expression, not Common View
> Expression.
>
> eg:  WITH x AS ()
>SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
> x1 UNION x2.
>
> Peter
>
> On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev  wrote:
>
>> The following statement executes the random() function twice -
>>
>> sqlite> select random() union all select random();
>> random()
>> 2678358683566407062
>> -5528866137931448843
>> sqlite> explain select random() union all select random();
>> addr  opcode p1p2p3p4 p5  comment
>>   -        -  --  -
>> 0 Init   0 1 000
>> 1 Function0  0 0 1 random(0)  00
>> 2 ResultRow  1 1 000
>> 3 Function0  0 0 1 random(0)  00
>> 4 ResultRow  1 1 000
>> 5 Halt   0 0 000
>>
>> but if random() is replaced with round(1.1) -
>>
>> sqlite> select round(1.1) union all select round(1.1);
>> round(1.1)
>> 1.0
>> 1.0
>> sqlite> explain select round(1.1) union all select round(1.1);
>> addr  opcode p1p2p3p4 p5  comment
>>   -        -  --  -
>> 0 Init   0 6 000
>> 1 Copy   2 1 000
>> 2 ResultRow  1 1 000
>> 3 Copy   2 1 000
>> 4 ResultRow  1 1 000
>> 5 Halt   0 0 000
>> 6 Real   0 3 0 1.100
>> 7 Function0  1 3 2 round(1)   01
>> 8 Goto   0 1 000
>>
>> It seems to be executed once only. Does this happen because random() is
>> flagged non-deterministic?
>>
>>
>> On 19 January 2018 at 09:10, Clemens Ladisch  wrote:
>>
>> > petern wrote:
>> > > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
>> > > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
>> > > sum(s),"(SELECT sum(s) FROM flips)"
>> > > 1,3
>> > > --Expected output is 1,1.
>> > >
>> > > Why isn't the constant notional table table [flips] materialized just
>> > once
>> > > per CTE?
>> > >
>> > > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
>> >
>> > Its documentation says:
>> > 
>> > | A useful property of WITH queries is that they are evaluated only once
>> > | per execution of the parent query, even if they are referred to more
>> > | than once by the parent query or sibling WITH queries. Thus, expensive
>> > | calculations that are needed in multiple places can be placed within
>> > | a WITH query to avoid redundant work. Another possible application is
>> > | to prevent unwanted multiple evaluations of functions with side-
>> > | effects. However, the other side of this coin is that the optimizer is
>> > | less able to push restrictions from the parent query down into a WITH
>> > | query than an ordinary subquery.
>> >
>> > This is an implementation detail of Postgres, and it is not required by
>> > the SQL specification.  SQLite chose the other side of the coin.
>> >
>> >
>> > Regards,
>> > Clemens
>> > ___
>> > 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



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

[sqlite] Unexpected column scoping in GROUP BY produces wrong answer.

[petern]

SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS j FROM t
GROUP BY j;
j
4
8
7
--Wrong answer.
--GROUP BY unexpectedly scopes outer source table column j rather than the
nearer local column alias j.

WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM t
GROUP BY x;
x
3
4
7
8
--Correct answer when the local column alias happens to be unique.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] get stmt row count

[Keith Medcalf]

You could use either, though I use rollback to make it clear that no changes 
are intended to be kept.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 20 January, 2018 11:12
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Thanks very much for that explanation Keith. Why ROLLBACK rather than
>END though?
>
>
>
>
>From: sqlite-users  on
>behalf of Keith Medcalf 
>Sent: Saturday, January 20, 2018 5:10:20 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>
>Clemens is correct, the best way is to keep the transaction open
>(perhaps if necessary dedicate a connection to this data).
>
>In WAL the open transaction will continue to see the "same data
>snapshot" independent of other read/write taking place on other
>connections (which will not be blocked).
>
>While holding a transaction active when in another (non-WAL) journal
>mode will have the same effect, it will lock out all database writers
>for the duration of the transactions (which achieves the same
>stability but by prohibiting changes rather than by repeatable-read
>isolation).
>
>BEGIN;
>SELECT RowID FROM ... WHERE ... ;
>...
>SELECT ...data... using RowID from above
>...
>ROLLBACK;
>
>The view of the data will be stable (repeatable read isolation)
>between the BEGIN/END transaction.  Other connections may read/update
>the database and will see the "current" database (or the applicable
>snapshot in effect when they started their transactions).
>
>This is how other "heavyweight" database engines/cli's implement
>scrollable cursors.  They just wrote the complicated code for you
>(that is why they are "heavy" -- as in 10,000 tonnes -- rather than
>"lite" as in 400 milligrams -- SQLite being in the "Lite" category.)
>SQLite does everything you need to be able to implement all the
>heavyweight featuritis you need -- it is just that YOU have to do it,
>it is not done for you (so it works the way you want it to work, at
>the speed you want it to work at, not in the manner and speed that
>someone else thought was splendid for them).
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of x
>>Sent: Saturday, 20 January, 2018 02:42
>>To: SQLite mailing list
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>>Getting both requires TWO queries (and may not be the same from one
>>nanosecond to the next).
>>
>>
>>
>>I hadn’t given that much thought until your post Keith but I was
>>thinking last night that my vector of RowIDs might no longer be
>valid
>>when I use them to retrieve a grid of data. It’s easily dealt with
>in
>>the app I’m working on but, for the general case, is there an easy
>>way of checking if the db file has been modified since the RowIDs
>>query has been run? I’m sure I read something about a db related
>>integer that was incremented after every update or vacuum but can’t
>>find the post.
>>
>>
>>
>>
>>
>>
>>From: sqlite-users  on
>>behalf of Keith Medcalf 
>>Sent: Friday, January 19, 2018 12:12:15 PM
>>To: SQLite mailing list
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>
>>Then you should have asked for the count(*) which would have
>returned
>>the count rather than you having to count the rows as they are
>>returned.
>>
>>Make up your mind what you want -- and ye shall get that for which
>>you asked -- the rows or the count of the rows?  Getting both
>>requires TWO queries (and may not be the same from one nanosecond to
>>the next).  Or you can request the rows AND count them if you need
>>both the rows and the count of them.
>>
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>>says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of x
>>>Sent: Friday, 19 January, 2018 07:07
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>>
>>>Gunter, I just ran a moderately complex query sorted on a non-
>>indexed
>>>column which returned 2.4 million rows. As you say, after the first
>>>step practically all of the work is done yet it still took almost 2
>>>secs to run
>>>
>>>
>>>
>>>Int Count=0;
>>>
>>>while (sqlite3_step(...)) Count++;
>>>
>>>
>>>
>>>that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly
>>>insignificant if you’re looking to set up a grid scroll bar.
>>>
>>>
>>>
>>>Tom
>>>
>>>

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Exactly.  But that doesn't resolve the problem of duplicated
non-deterministic tables in a CTE.
CTE is the acryonym for Common TABLE Expression, not Common View
Expression.

eg:  WITH x AS ()
   SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
x1 UNION x2.

Peter

On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev  wrote:

> The following statement executes the random() function twice -
>
> sqlite> select random() union all select random();
> random()
> 2678358683566407062
> -5528866137931448843
> sqlite> explain select random() union all select random();
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 1 000
> 1 Function0  0 0 1 random(0)  00
> 2 ResultRow  1 1 000
> 3 Function0  0 0 1 random(0)  00
> 4 ResultRow  1 1 000
> 5 Halt   0 0 000
>
> but if random() is replaced with round(1.1) -
>
> sqlite> select round(1.1) union all select round(1.1);
> round(1.1)
> 1.0
> 1.0
> sqlite> explain select round(1.1) union all select round(1.1);
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 6 000
> 1 Copy   2 1 000
> 2 ResultRow  1 1 000
> 3 Copy   2 1 000
> 4 ResultRow  1 1 000
> 5 Halt   0 0 000
> 6 Real   0 3 0 1.100
> 7 Function0  1 3 2 round(1)   01
> 8 Goto   0 1 000
>
> It seems to be executed once only. Does this happen because random() is
> flagged non-deterministic?
>
>
> On 19 January 2018 at 09:10, Clemens Ladisch  wrote:
>
> > petern wrote:
> > > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > > sum(s),"(SELECT sum(s) FROM flips)"
> > > 1,3
> > > --Expected output is 1,1.
> > >
> > > Why isn't the constant notional table table [flips] materialized just
> > once
> > > per CTE?
> > >
> > > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
> >
> > Its documentation says:
> > 
> > | A useful property of WITH queries is that they are evaluated only once
> > | per execution of the parent query, even if they are referred to more
> > | than once by the parent query or sibling WITH queries. Thus, expensive
> > | calculations that are needed in multiple places can be placed within
> > | a WITH query to avoid redundant work. Another possible application is
> > | to prevent unwanted multiple evaluations of functions with side-
> > | effects. However, the other side of this coin is that the optimizer is
> > | less able to push restrictions from the parent query down into a WITH
> > | query than an ordinary subquery.
> >
> > This is an implementation detail of Postgres, and it is not required by
> > the SQL specification.  SQLite chose the other side of the coin.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] Defect: Redundant CTE table materialization gives wrong answer.

[Shane Dev]

The following statement executes the random() function twice -

sqlite> select random() union all select random();
random()
2678358683566407062
-5528866137931448843
sqlite> explain select random() union all select random();
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000
1 Function0  0 0 1 random(0)  00
2 ResultRow  1 1 000
3 Function0  0 0 1 random(0)  00
4 ResultRow  1 1 000
5 Halt   0 0 000

but if random() is replaced with round(1.1) -

sqlite> select round(1.1) union all select round(1.1);
round(1.1)
1.0
1.0
sqlite> explain select round(1.1) union all select round(1.1);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 6 000
1 Copy   2 1 000
2 ResultRow  1 1 000
3 Copy   2 1 000
4 ResultRow  1 1 000
5 Halt   0 0 000
6 Real   0 3 0 1.100
7 Function0  1 3 2 round(1)   01
8 Goto   0 1 000

It seems to be executed once only. Does this happen because random() is
flagged non-deterministic?


On 19 January 2018 at 09:10, Clemens Ladisch  wrote:

> petern wrote:
> > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > sum(s),"(SELECT sum(s) FROM flips)"
> > 1,3
> > --Expected output is 1,1.
> >
> > Why isn't the constant notional table table [flips] materialized just
> once
> > per CTE?
> >
> > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
>
> Its documentation says:
> 
> | A useful property of WITH queries is that they are evaluated only once
> | per execution of the parent query, even if they are referred to more
> | than once by the parent query or sibling WITH queries. Thus, expensive
> | calculations that are needed in multiple places can be placed within
> | a WITH query to avoid redundant work. Another possible application is
> | to prevent unwanted multiple evaluations of functions with side-
> | effects. However, the other side of this coin is that the optimizer is
> | less able to push restrictions from the parent query down into a WITH
> | query than an ordinary subquery.
>
> This is an implementation detail of Postgres, and it is not required by
> the SQL specification.  SQLite chose the other side of the coin.
>
>
> Regards,
> Clemens
> ___
> 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] WAL and pragma uncommitted

[Dan Kennedy]

On 01/19/2018 11:26 PM, Hannah Massey wrote:

Currently we access a single SQLite database in a single thread but I am
working on changing this as performance has become a real problem. We will
be using WAL mode and there will be one thread for writes and multiple
threads for reads. For many cases, speed will be of a priority and it will
not matter if the data returned from a read is slightly out of date so I
can considering using #pragma uncommitted in some of the reader threads.
Will #pragma uncommitted work in WAL mode and will it have the effect I'm
looking for (where the read will be faster because it can ignore the
recently written information in the WAL File) and simply use the database
file only?


Don't use "PRAGMA read_uncommitted". It is a no-op unless you turn on 
shared-cache mode. And using shared-cache mode reduces the concurrency 
provided by using wal mode.


Dan.


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

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

Thanks very much for that explanation Keith. Why ROLLBACK rather than END 
though?




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Saturday, January 20, 2018 5:10:20 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count


Clemens is correct, the best way is to keep the transaction open (perhaps if 
necessary dedicate a connection to this data).

In WAL the open transaction will continue to see the "same data snapshot" 
independent of other read/write taking place on other connections (which will 
not be blocked).

While holding a transaction active when in another (non-WAL) journal mode will 
have the same effect, it will lock out all database writers for the duration of 
the transactions (which achieves the same stability but by prohibiting changes 
rather than by repeatable-read isolation).

BEGIN;
SELECT RowID FROM ... WHERE ... ;
...
SELECT ...data... using RowID from above
...
ROLLBACK;

The view of the data will be stable (repeatable read isolation) between the 
BEGIN/END transaction.  Other connections may read/update the database and will 
see the "current" database (or the applicable snapshot in effect when they 
started their transactions).

This is how other "heavyweight" database engines/cli's implement scrollable 
cursors.  They just wrote the complicated code for you (that is why they are 
"heavy" -- as in 10,000 tonnes -- rather than "lite" as in 400 milligrams -- 
SQLite being in the "Lite" category.)  SQLite does everything you need to be 
able to implement all the heavyweight featuritis you need -- it is just that 
YOU have to do it, it is not done for you (so it works the way you want it to 
work, at the speed you want it to work at, not in the manner and speed that 
someone else thought was splendid for them).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 20 January, 2018 02:42
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>>Getting both requires TWO queries (and may not be the same from one
>nanosecond to the next).
>
>
>
>I hadn’t given that much thought until your post Keith but I was
>thinking last night that my vector of RowIDs might no longer be valid
>when I use them to retrieve a grid of data. It’s easily dealt with in
>the app I’m working on but, for the general case, is there an easy
>way of checking if the db file has been modified since the RowIDs
>query has been run? I’m sure I read something about a db related
>integer that was incremented after every update or vacuum but can’t
>find the post.
>
>
>
>
>
>
>From: sqlite-users  on
>behalf of Keith Medcalf 
>Sent: Friday, January 19, 2018 12:12:15 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>
>Then you should have asked for the count(*) which would have returned
>the count rather than you having to count the rows as they are
>returned.
>
>Make up your mind what you want -- and ye shall get that for which
>you asked -- the rows or the count of the rows?  Getting both
>requires TWO queries (and may not be the same from one nanosecond to
>the next).  Or you can request the rows AND count them if you need
>both the rows and the count of them.
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of x
>>Sent: Friday, 19 January, 2018 07:07
>>To: SQLite mailing list
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>Gunter, I just ran a moderately complex query sorted on a non-
>indexed
>>column which returned 2.4 million rows. As you say, after the first
>>step practically all of the work is done yet it still took almost 2
>>secs to run
>>
>>
>>
>>Int Count=0;
>>
>>while (sqlite3_step(...)) Count++;
>>
>>
>>
>>that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly
>>insignificant if you’re looking to set up a grid scroll bar.
>>
>>
>>
>>Tom
>>
>>
>>
>>
>>From: sqlite-users  on
>>behalf of Hick Gunter 
>>Sent: Friday, January 19, 2018 10:27:30 AM
>>To: 'SQLite mailing list'
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>No. The only viable query plan is scan and sort (see "explain query
>>plan" output). The sqlite3_prepare() family of calls creates the SQL
>>program (see "explain" output) and sets ist initial state. If the
>>value you require is not null, you must call one of the
>>sqlite3_bind() functions. 

Re: [sqlite] [EXTERNAL] get stmt row count

[Keith Medcalf]

Clemens is correct, the best way is to keep the transaction open (perhaps if 
necessary dedicate a connection to this data).  

In WAL the open transaction will continue to see the "same data snapshot" 
independent of other read/write taking place on other connections (which will 
not be blocked).  

While holding a transaction active when in another (non-WAL) journal mode will 
have the same effect, it will lock out all database writers for the duration of 
the transactions (which achieves the same stability but by prohibiting changes 
rather than by repeatable-read isolation).

BEGIN;
SELECT RowID FROM ... WHERE ... ;
...
SELECT ...data... using RowID from above
...
ROLLBACK;

The view of the data will be stable (repeatable read isolation) between the 
BEGIN/END transaction.  Other connections may read/update the database and will 
see the "current" database (or the applicable snapshot in effect when they 
started their transactions).

This is how other "heavyweight" database engines/cli's implement scrollable 
cursors.  They just wrote the complicated code for you (that is why they are 
"heavy" -- as in 10,000 tonnes -- rather than "lite" as in 400 milligrams -- 
SQLite being in the "Lite" category.)  SQLite does everything you need to be 
able to implement all the heavyweight featuritis you need -- it is just that 
YOU have to do it, it is not done for you (so it works the way you want it to 
work, at the speed you want it to work at, not in the manner and speed that 
someone else thought was splendid for them).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 20 January, 2018 02:42
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>>Getting both requires TWO queries (and may not be the same from one
>nanosecond to the next).
>
>
>
>I hadn’t given that much thought until your post Keith but I was
>thinking last night that my vector of RowIDs might no longer be valid
>when I use them to retrieve a grid of data. It’s easily dealt with in
>the app I’m working on but, for the general case, is there an easy
>way of checking if the db file has been modified since the RowIDs
>query has been run? I’m sure I read something about a db related
>integer that was incremented after every update or vacuum but can’t
>find the post.
>
>
>
>
>
>
>From: sqlite-users  on
>behalf of Keith Medcalf 
>Sent: Friday, January 19, 2018 12:12:15 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>
>Then you should have asked for the count(*) which would have returned
>the count rather than you having to count the rows as they are
>returned.
>
>Make up your mind what you want -- and ye shall get that for which
>you asked -- the rows or the count of the rows?  Getting both
>requires TWO queries (and may not be the same from one nanosecond to
>the next).  Or you can request the rows AND count them if you need
>both the rows and the count of them.
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of x
>>Sent: Friday, 19 January, 2018 07:07
>>To: SQLite mailing list
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>Gunter, I just ran a moderately complex query sorted on a non-
>indexed
>>column which returned 2.4 million rows. As you say, after the first
>>step practically all of the work is done yet it still took almost 2
>>secs to run
>>
>>
>>
>>Int Count=0;
>>
>>while (sqlite3_step(...)) Count++;
>>
>>
>>
>>that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly
>>insignificant if you’re looking to set up a grid scroll bar.
>>
>>
>>
>>Tom
>>
>>
>>
>>
>>From: sqlite-users  on
>>behalf of Hick Gunter 
>>Sent: Friday, January 19, 2018 10:27:30 AM
>>To: 'SQLite mailing list'
>>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>>
>>No. The only viable query plan is scan and sort (see "explain query
>>plan" output). The sqlite3_prepare() family of calls creates the SQL
>>program (see "explain" output) and sets ist initial state. If the
>>value you require is not null, you must call one of the
>>sqlite3_bind() functions. This sets the value of the variable (line
>>2). Calling sqlite3_step() on the statement causes the program to
>run
>>up to either the ResultRow or the Halt statement. By the time the
>>firs call to sqlite3_step() returns, all the table rows have been
>>read, the values sorted and the first output row produced. This is
>>practically all of the work. 

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

Thanks Clemens.




From: sqlite-users  on behalf of 
Clemens Ladisch 
Sent: Saturday, January 20, 2018 10:39:15 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

x wrote:
> is there an easy way of checking if the db file has been modified
> since the RowIDs query has been run?

The easiest way is to keep the transaction open, then you know that there
have not been any modifications, as far as you can see.  :o)

> I’m sure I read something about a db related integer that was
> incremented after every update or vacuum but can’t find the post.



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


Regards,
Clemens
___
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] [EXTERNAL] get stmt row count

[Clemens Ladisch]

x wrote:
> is there an easy way of checking if the db file has been modified
> since the RowIDs query has been run?

The easiest way is to keep the transaction open, then you know that there
have not been any modifications, as far as you can see.  :o)

> I’m sure I read something about a db related integer that was
> incremented after every update or vacuum but can’t find the post.



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


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

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

>Getting both requires TWO queries (and may not be the same from one nanosecond 
>to the next).



I hadn’t given that much thought until your post Keith but I was thinking last 
night that my vector of RowIDs might no longer be valid when I use them to 
retrieve a grid of data. It’s easily dealt with in the app I’m working on but, 
for the general case, is there an easy way of checking if the db file has been 
modified since the RowIDs query has been run? I’m sure I read something about a 
db related integer that was incremented after every update or vacuum but can’t 
find the post.






From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Friday, January 19, 2018 12:12:15 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count


Then you should have asked for the count(*) which would have returned the count 
rather than you having to count the rows as they are returned.

Make up your mind what you want -- and ye shall get that for which you asked -- 
the rows or the count of the rows?  Getting both requires TWO queries (and may 
not be the same from one nanosecond to the next).  Or you can request the rows 
AND count them if you need both the rows and the count of them.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 07:07
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Gunter, I just ran a moderately complex query sorted on a non-indexed
>column which returned 2.4 million rows. As you say, after the first
>step practically all of the work is done yet it still took almost 2
>secs to run
>
>
>
>Int Count=0;
>
>while (sqlite3_step(...)) Count++;
>
>
>
>that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly
>insignificant if you’re looking to set up a grid scroll bar.
>
>
>
>Tom
>
>
>
>
>From: sqlite-users  on
>behalf of Hick Gunter 
>Sent: Friday, January 19, 2018 10:27:30 AM
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>No. The only viable query plan is scan and sort (see "explain query
>plan" output). The sqlite3_prepare() family of calls creates the SQL
>program (see "explain" output) and sets ist initial state. If the
>value you require is not null, you must call one of the
>sqlite3_bind() functions. This sets the value of the variable (line
>2). Calling sqlite3_step() on the statement causes the program to run
>up to either the ResultRow or the Halt statement. By the time the
>firs call to sqlite3_step() returns, all the table rows have been
>read, the values sorted and the first output row produced. This is
>practically all of the work. Stopping now saves nothing.
>
>There is no count of "records selected from base table" or "records
>inserted into sorter". Keeping such scores would add work to be done,
>with no benefit except perhaps satisfying your intellectual
>curiosity.
>
>asql> create temp table test (rowid integer primary key, value
>integer);
>asql> .explain
>asql> explain query plan select rowid from test where value > ?1
>order by value;
>sele  order  from  deta
>  -    
>0 0  0 SCAN TABLE test (~33 rows)
>0 0  0 USE TEMP B-TREE FOR ORDER BY
>asql> explain select rowid from test where value > ?1 order by value;
>addr  opcode p1p2p3p4 p5  comment
>  -        -  --  ---
>--
>0 Trace  0 0 000  NULL
>1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL
>2 Variable   1 1 0 ?1 00  NULL
>3 Goto   0 27000  NULL
>4 OpenRead   0 2 1 2  00  test
>5 Rewind 0 16000  NULL
>6 Column 0 1 200  test.value
>7 Le 1 152 collseq(BINARY)  6c  NULL
>8 Rowid  0 4 000  NULL
>9 MakeRecord 4 1 200  NULL
>10Column 0 1 500  test.value
>11Sequence   1 6 000  NULL
>12Move   2 7 100  NULL
>13MakeRecord 5 3 300  NULL
>14SorterInsert   1 3 000  NULL
>15Next   0 6 001  NULL
>16Close  0 0 000  NULL
>17OpenPseudo 2 2 100  NULL