Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[x]

  *   I truly hope Keith you'll continue making your tremendous contributions to
the SQLite community.



Ditto.



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

Re: [sqlite] WITHOUT ROWID tables

[x]

Re my earlier post (which didn’t go out to mailing list)

sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
returns false if table tblName is a without rowid table

seems to work for me.

Sent from Mail for Windows 10


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Sunday, February 16, 2020 6:11:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] WITHOUT ROWID tables

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:



" as of SQLite version 3.30.0 on 2019-10-04 "
___
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] Table has different rootpage in EXPLAIN and sqlite_master

[x]

Hi David,

The two queries were as follows

explain select vCol,* from Race,Meta.vCols;
and
select * from meta.sqlite_master where type='table';

Sorry, I don’t have a copy of the unvacuumed db.



From: sqlite-users  on behalf of 
David Raymond 
Sent: Monday, February 3, 2020 6:19:31 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

They shouldn't be different, no. Do you have a copy of the weird version of the 
database still, or have a copy of the explain text and the sqlite_master 
contents? Was there another index or table whose root page is what was listed 
in the explain output? For example, were you expecting it to use the table, but 
it used a covering index instead?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Monday, February 3, 2020 1:07 PM
To: Discussion of SQLite Database 
Subject: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
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

[sqlite] Table has different rootpage in EXPLAIN and sqlite_master

[x]

Differing by 1. I noticed this today for 1 table only but not always - 
sometimes they were equal. I’ve been unable to reproduce it after a vacuum. 
Does this indicate a corrupt db or is it a case of it can happen for some 
reason I’ve missed?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

[x]

Thanks David, getting them now.



From: sqlite-users  on behalf of 
David Raymond 
Sent: Friday, January 31, 2020 1:45:41 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Is Comment column in 'EXPLAIN' blank?

To get the comments the library needs to have been compiled with 
SQLITE_ENABLE_EXPLAIN_COMMENTS flag
https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off

That setting is off by default, but for the pre-compiled CLI they do include it.

I'm not seeing it in the list when I do a "pragma compile_options;" though. Is 
that setting one that's not reported by that pragma?


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Friday, January 31, 2020 6:22 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is Comment column in 'EXPLAIN' blank?

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
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

[sqlite] Is Comment column in 'EXPLAIN' blank?

[x]

I get the comments using the shell but running an explain query in c++ using 
sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is 
something up or is it my code?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find schema of a table in a query

[x]

Thanks Simon.


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Monday, January 20, 2020 4:49:04 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Find schema of a table in a query

On 20 Jan 2020, at 2:53pm, x  wrote:

> Is this possible in sql?

In SQL schema names and table names are entities.  You cannot bind a parameter 
to a either of them.

This is why, instead of keeping data in many different databases, you make one 
big schema, and use that value as a column.
___
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] Find schema of a table in a query

[x]

> select name from pragma_database_list d
> where (select name from pragma_table_info(?1) where schema==d.name)
> order by seq!=1, seq limit 1;

Is this possible in sql?

Given a variable ?1 which contains a valid attached DB name is it possible to 
retrieve the contents of

?1.table_name  ??

It’s not like ‘select ?1 as DB,* from ?1.table_name’ is valid sql. Am I just 
being flummoxed by the fact the top works because it’s metadata?

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

Re: [sqlite] Find schema of a table in a query

[x]

Brilliant Keith. Many thanks.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Monday, January 20, 2020 9:28:50 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Find schema of a table in a query


You are correct that same table names in temp obscure those names from main and 
other attached databases, so your order by addition is required.

  select name
from pragma_database_list as d
   where exists (select *
   from pragma_table_info
  where schema == d.name
and arg == ?)
order by seq != 1, seq
   limit 1;

--
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  On
>Behalf Of Keith Medcalf
>Sent: Monday, 20 January, 2020 02:18
>To: SQLite mailing list 
>Subject: Re: [sqlite] Find schema of a table in a query
>
>
>The "main" database is always seq == 0, the "temp" database is always seq
>== 1, and other databases are seq == 2 and greater in the order they were
>attached.  seq 2 -> whatever is always contiguous.  The table search
>order for unqualified names (when a search is required) is always in the
>seq returned by pragma database_list, so
>
>select name
>  from pragma_database_list as d
> where exists (select *
> from pragma_table_info
>where schema == d.name
>  and arg == 'x')
> limit 1;
>
>will always return the correct schema name, no order by required.  (and
>you can use table_info rather than table_xinfo because a table must
>always have one named column that is not hidden.  If this were not the
>case, then you would have to use table_xinfo to ensure that something is
>returned for that pragma lookup.
>
>SQLite version 3.31.0 2020-01-20 03:22:36
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> attach database ':memory:' as two;
>sqlite> attach database ':memory:' as three;
>sqlite> attach database ':memory:' as four;
>sqlite> attach database ':memory:' as five;
>sqlite> create table main.x(x);
>sqlite> insert into main.x values (0);
>sqlite> create table temp.x(x);
>sqlite> insert into temp.x values (1);
>sqlite> create table two.x(x);
>sqlite> insert into two.x values (2);
>sqlite> create table three.x(x);
>sqlite> insert into three.x values (3);
>sqlite> create table four.x(x);
>sqlite> insert into four.x values (4);
>sqlite> create table five.x(x);
>sqlite> insert into five.x values (5);
>sqlite> pragma database_list;
>0|main|
>1|temp|
>2|two|
>3|three|
>4|four|
>5|five|
>sqlite> detach database three;
>sqlite> pragma database_list;
>0|main|
>1|temp|
>2|two|
>3|four|
>4|five|
>sqlite> select * from x;
>1
>sqlite> select name
>   ...>   from pragma_database_list as d
>   ...>  where exists (select *
>   ...>  from pragma_table_xinfo
>   ...> where schema == d.name
>   ...>   and arg == 'x')
>   ...>  limit 1;
>main
>sqlite> drop table main.x;
>sqlite> select * from x;
>1
>sqlite> select name
>   ...>   from pragma_database_list as d
>   ...>  where exists (select *
>   ...>  from pragma_table_xinfo
>   ...> where schema == d.name
>   ...>   and arg == 'x')
>   ...>  limit 1;
>temp
>sqlite> drop table four.x;
>sqlite> select * from x;
>1
>sqlite> select name
>   ...>   from pragma_database_list as d
>   ...>  where exists (select *
>   ...>  from pragma_table_xinfo
>   ...>     where schema == d.name
>   ...>   and arg == 'x')
>   ...>  limit 1;
>temp
>sqlite> drop table temp.x;
>sqlite> select * from x;
>2
>sqlite> select name
>   ...>   from pragma_database_list as d
>   ...>  where exists (select *
>   ...>  from pragma_table_xinfo
>   ...> where schema == d.name
>   ...>   and arg == 'x')
>   ...>  limit 1;
>two
>sqlite> create table temp.x(x);
>sqlite> insert into temp.x values (1);
>sqlite> select * from x;
>1
>sqlite> select name
>   ...>   from pragma_database_list as d
>   ...>  where exists (select *
>   ...>  from pragma_table_xinfo
>   ...> where schema == d.n

Re: [sqlite] Find schema of a table in a query

[x]

WOW Keith. That’s the sqlite coding equivalent of scoring a hat trick in the 
world cup final. I’ve added an order by to get the solution

select name from pragma_database_list d
where (select name from pragma_table_xinfo where schema==d.name and arg==?1)
order by seq!=1, seq limit 1;

I’m assuming the temp db is always 1 in the seq column. Can anyone confirm that 
or should I change it to

order by lower(name)!=temp, seq limit 1;

Thanks. I also learned the parentheses are not required for pragma functions 
when there’s no params and alternate syntax when they are.



From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Sunday, January 19, 2020 8:32:06 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Find schema of a table in a query


On Sunday, 19 January, 2020 01:47, x  wrote:

>Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want
>to know the name of the schema that tbl belongs to. What’s the easiest
>way to do this?

>I know sqlite will use temp.tbl if it exists else main.tbl if it exists
>else it will search for the earliest attached schema with a table called
>tbl. Finding that involves the use of PRAGMA database_list and then
>querying each of the associated sqlite_master tables in turn for the
>existence of tbl until you get a match. Is there an easier way?

How about:

select name
  from pragma_database_list as d
 where (select name
  from pragma_table_xinfo
 where schema == d.name
   and arg == 'x') is not null;

Where you set "arg == 'tablename'" which will return all the schema's in which 
the specified tablename exists.

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find schema of a table in a query

[x]

If I could answer the “such as” I wouldn’t have asked the question. The word 
“hoping” is the clue as in I was hoping there was some function I had 
overlooked. I’ve settled for using the sqlite3_table_column_metadata function 
in a loop using each db name in turn in the order sqlite3 does.




From: sqlite-users  on behalf of 
Warren Young 
Sent: Sunday, January 19, 2020 4:02:40 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Find schema of a table in a query

On Jan 19, 2020, at 2:41 AM, x  wrote:
>
> I was hoping for something simpler.

Such as?

I mean, your question basically reduces to “I need to be inside the parse 
loop,” and SQLite has come along and said, “Hey, check this out, you can be 
inside the parse loop.”  I mean, how cool is that?
___
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] Find schema of a table in a query

[x]

Thanks Dominique. I was aware of that route but I was hoping for something 
simpler.




From: sqlite-users  on behalf of 
Dominique Devienne 
Sent: Sunday, January 19, 2020 9:32:28 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Find schema of a table in a query

On Sun, Jan 19, 2020 at 9:47 AM x  wrote:

> Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to
> know the name of the schema that tbl belongs to. What’s the easiest way to
> do this?


Set an authorizer. Requires to write code though, cannot be done in SQL.
https://www.sqlite.org/c3ref/set_authorizer.html
___
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] Find schema of a table in a query

[x]

Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to know 
the name of the schema that tbl belongs to. What’s the easiest way to do this?

I know sqlite will use temp.tbl if it exists else main.tbl if it exists else it 
will search for the earliest attached schema with a table called tbl. Finding 
that involves the use of PRAGMA database_list and then querying each of the 
associated sqlite_master tables in turn for the existence of tbl until you get 
a match. Is there an easier way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[x]

LOL. English isnae oor furst language either. Ye ken whit a mean. 😊




From: sqlite-users  on behalf of 
Jose Isaias Cabrera 
Sent: Monday, December 30, 2019 9:50:22 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] SQL help



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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[x]

Thanks Barry. I think that’s saying something like what I was saying but I put 
it better than them. I’m buoyed by the fact it’s not just Scottish education 
that could be done gooder 😉


From: Barry Smith
Sent: 28 December 2019 22:49
To: SQLite mailing list
Subject: Re: [sqlite] SQL help

Why does section 6.4.3.a.i of that linked standard not apply to his sub select?

In the quote below, CR is Column Reference, CN is Column Name.

3) If CR does not contain a , then CR shall be contained
within the scope of one or more s or s whose associated tables include a column whose  is CN. Let the phrase possible qualifiers denote those
s and s.

a) Case:

  i) If the most local scope contains exactly one possible
 qualifier, then the qualifier Q equivalent to that unique
  or  is implicit.

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

Re: [sqlite] SQL help

[x]

Thanks Simon.

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

[sqlite] SQL help

[x]

Consider the following

sqlite> create table t1(a int, b int, c int);
sqlite> create table t2(a int, b int);
sqlite> insert into t1 values (1, 2, 3);
sqlite> insert into t2 values (3, 4);
sqlite> select a,(select b, c from t2 where t2.a=t1.a)=(3,3) from t1;
1|

In the embedded select it takes c from the outer table t1 but doesn’t flag 
‘ambiguous column’ for b in the embedded select. Is it standard that tables at 
the same level are searched first for the column and tables from the 
surrounding  levels are only searched if it’s not found? If so, is this also 
the case if selects are embedded to several depths. E.g. depth 3 tables 
searched first, then depth 2  and finally lowest depth?

Apologies if that’s not worded correctly. Scottish education could be done 
gooder 😊
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[x]

It is possible using ‘with recursive’. The following is ugly and inefficient 
but might give you some ideas.

with recursive cte (x,str) as
(select 0,?1
union
select x-1,substr(?1,x-1) from cte limit length(?1))
select str from cte where substr(str,1,2)='/*' order by -x limit 1;




From: sqlite-users  on behalf of 
Bart Smissaert 
Sent: Friday, December 6, 2019 11:59:06 PM
To: SQLite mailing list 
Subject: Re: [sqlite] last occurrence of /*

I think it can be done.
Just dealing with the forward slash.

RBS

On Fri, Dec 6, 2019 at 11:49 PM Simon Slavin  wrote:

> On 6 Dec 2019, at 11:00pm, Bart Smissaert 
> wrote:
>
> > How do I select the part of this statement starting with the last /*  ?
>
> Not in SQLite.  Do it in your code, or write your own function to do it
> and load this function into SQLite.
> ___
> 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] [draft patch] interface for retrieving values of bound parameters

[x]

Is http://www.sqlite.org/c3ref/expanded_sql.html no use to you?




From: sqlite-users  on behalf of 
tab 
Sent: Monday, November 11, 2019 5:26:42 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: [sqlite] [draft patch] interface for retrieving values of bound 
parameters

Hi all,

It'd be handy to be able to retrieve params previously bound to a statement in 
the C API. Per the advice on the SQLite copyright info page, this is much more 
of a suggestion than a full patch, though it is functional for binding and 
retrieving an sqlite_value* (but, for example, there might be further 
implications not considered here in allowing the contents of aVar to be used 
directly.) There wouldn't be much value in maintaining a fork for something 
like this, so I wanted to put that out here on the mailing list to see if it's 
something that might be considered for mainline.

___
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] Find stmt that's stopping a DB from closing

[x]

Thanks Simon. Used this

string str;
sqlite3_stmt *stmt=NULL;
do
{
stmt=sqlite3_next_stmt(FSQLiteDB,stmt);
if (stmt) str+=string(sqlite3_sql(stmt))+"\r\n";
}
while (stmt);




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Tuesday, November 5, 2019 7:55:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Find stmt that's stopping a DB from closing

On 5 Nov 2019, at 7:27pm, x  wrote:

> I’m sure the team added a fct that listed the offenders but I can’t find it 
> in the documentation.

The first statement associated with an open connection is pointed to by the 
pStmt of that connection.  Once you've found the first statement call

sqlite3_next_stmt()

on it to find the next one.

<https://www.sqlite.org/c3ref/next_stmt.html>

Unfortunately the database connection is documented as 'opaque'.  I'm not 
certain for the ideal way in C to get the first statement from it.

Simon
--
 http://www.bigfraud.org | I'd expect if a computer was involved
 No Buffy for you.   | it all would have been much worse.
 Leave quickly now. -- Anya  |-- John "West" McKenna

___
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] Find stmt that's stopping a DB from closing

[x]

>I think you're referring to "The SQLITE_STMT Virtual Table":
https://www.sqlite.org/stmt.html

That’s it David. Thanks.

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

[sqlite] Find stmt that's stopping a DB from closing

[x]

I’m sure the team added a fct that listed the offenders but I can’t find it in 
the documentation. I would’ve thought it would have been mentioned on the 
sqlite3_close page but no sign of it. Am I imagining things?



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

Re: [sqlite] 3.31

[x]

Thanks Richard.




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Monday, November 4, 2019 11:55:31 AM
To: SQLite mailing list 
Subject: Re: [sqlite] 3.31

On 11/4/19, x  wrote:
> I’m eager to try out the new virtual columns (thanks Richard and team). Is
> it imminent or is there a beta for testing?

Use the "Prerelease Snapshot" from the download page.

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

[sqlite] 3.31

[x]

I’m eager to try out the new virtual columns (thanks Richard and team). Is it 
imminent or is there a beta for testing?



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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[x]

Would any kind soul be willing to explain the deterministic problem to me? I 
can see that having now() in a virtual column would be a waste of time as the 
virtual column value would change every time it was read but if it was being 
stored then why not?



Also, I take it deterministic udf’s can be used in virtual columns. If that’s 
the case what’s to stop you from declaring a udf as deterministic and calling 
now() inside that?



I’m obviously missing something. I’m guessing there must be code in sqlite.c 
that needs the same value returned at different times.








From: sqlite-users  on behalf of 
Dominique Devienne 
Sent: Wednesday, October 30, 2019 9:21:12 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have 
SQLITE_FUNC_CONSTANT ?

On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf  wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin 
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:
>
> >> Before you change anything, I think that is incorrect for the various
> >> datetime functions.  I think they SHOULD be permitted in CHECK
> >> constraints and in generated always columns, whether those are stored or
> >> virtual, whether or not parameters are given since they are constant.
> >> They should not be permitted in indexes however unless they are pure
> >> (deterministic).
>
> > But can SQLite tell the difference at that stage ?  For instance,
>
> Personally, I do not see a problem with permitting them to be used
> anywhere EXCEPT in an index expression.
>

+1. I completely agree with Keith, and the use-cases he outlined are great
examples of enforcing business logic using CHECK constraints.

I especially like the fact one cannot alter these stored generated values,
again adding a level of integrity to the DB. --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] LEFT JOIN: ON vs. WHERE

[x]

I don’t think I did say that Marc. All I was saying was the ON didn’t have to 
refer to the closest join.



select * from

tblA inner join tblB ON tblA.key = tblC key – no mention of tblB in ON

inner join tblC ON tblA.key = tblB.key; – no mention of tblC in ON



Normally that would be written as



select * from

tblA inner join tblB ON tblA.key = tblB key

inner join tblC ON tblA.key = tblC.key;



I was just pointing out that they’re the same thing as the ONs are moved to the 
where



select * from tblA inner join tblB inner join tblC

where tblA.key = tblB.key AND tblA.key=tblC.key



which I thought illustrated Keith’s point.








From: sqlite-users  on behalf of 
Allen, Marc 
Sent: Monday, October 28, 2019 5:27:57 PM
To: SQLite mailing list 
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE

Sorry for top posting.. I'm way too lazy to figure out how to make Outlook play 
nicely.

You say:

FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND R.col = 'foo'

Here,

 AND R.col = 'foo'

 while valid syntax (unfortunately), has no effect.

However, this isn't true.  While it doesn't constrain R, it does constrain the 
join.  This will still return all rows of R, but all S columns will be NULL for 
any row where R.col is not 'foo'.

This is actually useful, especially for efficiency.  This allows the database 
to not even bother doing the lookup on S if R.col != 'foo' and still return 
that R record.

Marc


Marc L. Allen | Software Engineer IV, PDI Marketing Cloud Solutions
2551 Eltham Ave. Suite N, Norfolk, VA 23513
Office  757.853.3000 x6015
Direct  757.605.6015
mlal...@pdisoftware.com
www.pdisoftware.com 


On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden" 
 wrote:

On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher  wrote:

> My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs.

ON applies before JOIN.  WHERE applies after.

That's a loose interpretation, but IMO it's nevertheless a useful way
to think about it.

It *mostly* doesn't matter.  It was invented for outer joins.

In SQL-89, there was only the inner join. There was no JOIN keyword.
You listed all the tables in FROM:

 FROM R, S
 WHERE R.key = S.key

and all was good with the world.

To support outer joins, vendors invented various syntaxes.  To unifiy
them, SQL-92 defined JOIN and ON:

 FROM R JOIN S
 ON R.key = S.key

with that syntax, JOIN could be modifed to be LEFT or RIGHT outer, and
the inner table could be further restricted:

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND S.col = 'foo'

That means: all rows from R joined to rows in S for which col = 'foo'.
If no such S rows exist, every result row will be NULL for the S
columns.  Compare with

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key
 WHERE S.col = 'foo'

Here, R and S are joined, and the WHERE test is applied to the joined
result.  If no such S rows exist, there will be no result rows at all.

When wrestling with this topic, it's useful to remember that ON doesn't
constrain the outer table:

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND R.col = 'foo'

Here,

 AND R.col = 'foo'

while valid syntax (unfortunately), has no effect.  R is the outer
table, and all rows of the outer table are always produced, no matter
what ON says about it.  Perhaps the above should mean:

 FROM (select * from R WHERE R.col = 'foo') as R
 LEFT OUTER JOIN S
 ON R.key = S.key

but it does not.  Perfection remains, as ever, elusive.

--jkl


___
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] LEFT JOIN: ON vs. WHERE

[x]

If I remember correctly I found out by mistake that the ON doesn’t even have to 
relate to the JOIN it follows as long as it fits in with the WHERE. E.g.



SELECT * FROM Tbl1 INNER JOIN Tbl2 ON Tbl3.Col=Tbl1.Col INNER JOIN Tbl3 ON 
Tbl2.Col=Tbl1.Col;



Something like that.






From: sqlite-users  on behalf of 
James K. Lowden 
Sent: Monday, October 28, 2019 10:32:21 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf"  wrote:

> CREATE TABLE t1 (a, b);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(2,3);
> INSERT INTO t1 VALUES(1,3);
> CREATE TABLE t2 (b, c);
> INSERT INTO t2 VALUES(2,3);
> INSERT INTO t2 VALUES(3,4);
> select * from t1 left join t2 on t1.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4

Thank you, Keith.  What you're saying is that when ON applies to the
outer table, it still constrains the JOIN.  The tuple

2 3 3 4

would have been produced, but for the ON restriction

t.a = 1

> This sort of thing is useful

I don't doubt it.  I can't remember ever having written a query like
that (not knowing that's what it would do).  I would probably have
expressed the giraffe-neck problem as

select * from t1
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b

because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.

--jkl

___
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] Stream loading SQL script

[x]

Thanks Keith. I thought it was a documented function and couldn’t find anything 
in the documentation.








From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Sunday, October 27, 2019 4:46:17 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Stream loading SQL script


On Sunday, 27 October, 2019 07:40, x  wrote:

>Where is this function Keith? I can find any information on it?

The SQLite3 command line shell (shell.c) reads input from stdin or other file 
and processes the commands one at a time by either calling the appropriate 
sqlite3 functions and displaying the output, or handling them internally.  
process_input is the function within the shell.c code which reads the input and 
decides when sufficient input has been collected to constitute a complete 
command for processing.  It is internal to shell.c and not part of any external 
documented API so the only documentation is to read the code.  If you want to 
know how the command line shell converts between a stream of input characters 
and recognizes when a complete command line has accumulated, this is the 
internal function that does that.

>
>From: sqlite-users  on
>behalf of František Kučera 
>Sent: Saturday, October 26, 2019 4:49:26 PM
>To: sqlite-users@mailinglists.sqlite.org us...@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Stream loading SQL script
>
>Dne 25. 10. 19 v 21:41 Keith Medcalf napsal(a):
>> The sqlite3 command line shell already does this.  see function
>process_input
>
>Thanks, it helped.
>
>I see that it checks whether the input contains a semicolon and only
>then it calls sqlite3_complete(). So I implemented it in a similar way
>in C++.
>
>Franta

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stream loading SQL script

[x]

Where is this function Keith? I can find any information on it?








From: sqlite-users  on behalf of 
František Kučera 
Sent: Saturday, October 26, 2019 4:49:26 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Stream loading SQL script

Dne 25. 10. 19 v 21:41 Keith Medcalf napsal(a):
> The sqlite3 command line shell already does this.  see function process_input

Thanks, it helped.

I see that it checks whether the input contains a semicolon and only
then it calls sqlite3_complete(). So I implemented it in a similar way
in C++.

Franta


___
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] ALTER TABLE ADD COLUMN

[x]

Graham, as you probably realise from reading David & Simon’s replies, it’s 
really about avoiding those type code bytes altogether. You can use ADD COLUMN 
to add 10 columns to a billion row table in microseconds so obviously all 
that’s changed is the header. SQLITE_ENABLE_NULL_TRIM answers my question 
although Simon’s point about SQLITE_ENABLE_DEFAULT_TRIM is valid. I see that 
enabling it might trigger some obscure bug w.r.t. blobs and that it may be 
enabled by default in future versions.








From: sqlite-users  on behalf of 
Graham Holden 
Sent: Wednesday, October 23, 2019 2:09:19 PM
To: SQLite mailing list 
Subject: Re: [sqlite] ALTER TABLE ADD COLUMN

Wednesday, October 23, 2019, 1:53:10 PM, x  wrote:

> From the documentation

> “A record might have fewer values than the number of columns in the 
> corresponding table. This can happen, for example, after an ALTER TABLE ... 
> ADD COLUMN SQL statement has increased the number of
> columns in the table schema without modifying preexisting rows in the table. 
> Missing values at the end of the record are filled in using the default value 
> for the corresponding columns defined in
> the table schema.”

> Suppose you have a table with say 5 columns that are almost always
> the default value (probably zero or null). Does the above suggest
> you should make them the last 5 columns in the table as the last
> n columns that are the default value won’t take up space? Or does
> this state just exist after ADD COLUMN but any rows added thereafter
> use the space?

I believe it can only happen after an ADD COLUMN, however, zero or
NULL values will, essentially, take zero space whereever they are in a
row. If you look in-and-around:

   https://www.sqlite.org/fileformat.html#record_format,

you will see that the "type code" used for each column in a row has
specific values for "NULL" and zero (0 and 8, respectively). This
means that where those NULL/zero occurs, no extra space is used to
hold the value.

Graham


___
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] ALTER TABLE ADD COLUMN

[x]

From the documentation

“A record might have fewer values than the number of columns in the 
corresponding table. This can happen, for example, after an ALTER TABLE ... ADD 
COLUMN SQL statement has increased the number of columns in the table schema 
without modifying preexisting rows in the table. Missing values at the end of 
the record are filled in using the default value for the corresponding columns 
defined in the table schema.”

Suppose you have a table with say 5 columns that are almost always the default 
value (probably zero or null). Does the above suggest you should make them the 
last 5 columns in the table as the last n columns that are the default value 
won’t take up space? Or does this state just exist after ADD COLUMN but any 
rows added thereafter use the space?
*Assume the 5 columns are little used so it doesn’t matter that they are the 
last named columns.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using lower function with utf8

[x]

Yes, I’m using v5 JcD. Thanks.




From: sqlite-users  on behalf of 
Jean-Christophe Deschamps 
Sent: Thursday, September 19, 2019 3:49:39 PM
To: SQLite mailing list 
Subject: Re: [sqlite] using lower function with utf8


>I was messing about with this and tried the following in sqlite expert
>professional
>
>select unicode(lower(char(256)));
>
>I was quite surprised when it responded with the correct result 257.
>
>Looking at the sqlite3.c code I can’t see anything that suggests
>sqlite would handle lower() for non-ascii characters so I tried the
>same thing in a c programme and it came back with the expected wrong
>answer 256.
>
>Am I right in saying 256 is the expected sqlite answer?
>
>Can anyone suggest why it works in the former?

Expert v5 (I expect that's what you use) includes Unicode support and
overrides sqlite3.dll lower().

JcD

___
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] using lower function with utf8

[x]

I was messing about with this and tried the following in sqlite expert 
professional

select unicode(lower(char(256)));

I was quite surprised when it responded with the correct result 257.

Looking at the sqlite3.c code I can’t see anything that suggests sqlite would 
handle lower() for non-ascii characters so I tried the same thing in a c 
programme and it came back with the expected wrong answer 256.

Am I right in saying 256 is the expected sqlite answer?

Can anyone suggest why it works in the former?

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

Re: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined

[x]

Thanks Keith. I appended the contents of the ext/userauth/userauth.c file to 
the bottom of the amalgamation (as per the instructions in 
ext/userauth/user-auth.txt) and it works fine.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Thursday, August 1, 2019 4:11:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has 
internal linkage but is not defined


This define enables calls to the userauth code.  You need to include the 
userauth.c code into your compilation unit.
See ext/userauth/user-auth.txt or 
https://www.sqlite.org/src/artifact/e6641021a9210364

--
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: Thursday, 1 August, 2019 09:00
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has
>internal linkage but is not defined
>
>I’m using the amalgamation in my Embarcadero Rad Studio 10 app (Clang
>compiler). I defined SQLITE_USER_AUTHENTICATION to test something and
>received the above warning along with the following two
>
>[bcc32c Warning] sqlite3.c(16307): function 'sqlite3UserAuthInit' has
>internal linkage but is not defined
>
>[bcc32c Warning] sqlite3.c(16308): function 'sqlite3CryptFunc' has
>internal linkage but is not defined
>
>
>I’m clueless as to the significance but thought I better report it
>just in case.
>___
>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

[sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined

[x]

I’m using the amalgamation in my Embarcadero Rad Studio 10 app (Clang 
compiler). I defined SQLITE_USER_AUTHENTICATION to test something and received 
the above warning along with the following two

[bcc32c Warning] sqlite3.c(16307): function 'sqlite3UserAuthInit' has internal 
linkage but is not defined

[bcc32c Warning] sqlite3.c(16308): function 'sqlite3CryptFunc' has internal 
linkage but is not defined


I’m clueless as to the significance but thought I better report it just in case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[x]

I realise that Simon. I’m working on the basis that when things change I can 
use an older version until I can account for the changes. Seems easier than 
maintaining my own code. Thanks.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Monday, July 29, 2019 7:07:15 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

On 29 Jul 2019, at 5:44pm, x  wrote:

> It’s not as easy to get access to sqlite3TreeViewSelect on windows as it 
> would appear to be in the unix debugger. When I did find out how to access it 
> I noticed it returned a string that would enable me to do away with reams of 
> code I wrote to do the same thing. I’m finding it hard to let go now. ☹

Please remember that since the result of the call is not documented, it can 
change at any time.  So even if you write code which works perfectly with this 
version of SQLite, it may not work with later versions.
___
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] Help with sqlite3TreeViewSelect

[x]

Thanks Richard. I’ve being doing my best with the code but time is not on my 
side. It took me ages just to get the clang compiler to debug sqlite code (had 
to split the files).



It’s not as easy to get access to sqlite3TreeViewSelect on windows as it would 
appear to be in the unix debugger. When I did find out how to access it I 
noticed it returned a string that would enable me to do away with reams of code 
I wrote to do the same thing. I’m finding it hard to let go now. ☹



With a bit of extra code I’ve managed to get the result of the 
sqlite3TreeViewSelect sent to the authorizer callback a line at a time. How I 
would’ve loved a sqlite3_TreeView_sql(stmt) function although having messed 
about with the code I appreciate how difficult that would be.




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Monday, July 29, 2019 4:54:36 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

On 7/29/19, Hick Gunter  wrote:
> Qquestions about which internal
> representation ... is something only the developers would
> be able to divulge,

The internal representation and the output of sqlite3TreeViewSelect()
are emphatically not APIs. Both are undocumented and both can and do
change from one release to the next.  The sqlite3TreeViewSelect()
interface is intended for debugging use only.

If you want to know what the output of sqlite3TreeViewSelect() means,
you should consult the source code in the treeview.c source file for
the specific version of SQLite you are running.  That's what the
developers do.  The meanings of the fields in the internal
representation are well described by code comments, especially in the
sqliteInt.h file.   For information about specific fields or values,
try grepping in the source code.  There are usually nearby comments
that explain what is going on.

Sadly, the IF_NULL_ROW value is not as well commented as it might be.
But, by looking at the source code, you quickly get the idea that it
has something to do with LEFT JOIN.  And if you search the Fossil
repository history, you can see that the identifier was added here:

 https://www.sqlite.org/src/timeline?c=3a5860d86fadcf92

Using those clues, perhaps you can figure it out.  I added the
IF_NULL_ROW code myself but that was over two years ago and at this
point I don't remember exactly why.  I'll have to go back and reverse
engineer it, if and when I improve the comments, or should we ever
need to work on that particular aspect of the implementation.

As with all of the internal representation, we might find a better way
to accomplish the same thing tomorrow, and totally eliminate the
IF_NULL_ROW operator from the code.  So don't become too attached to
it.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[x]

I’m still not sure what you’re getting at Gunter. The example code doesn’t 
present a problem, it’s when subqueries within subqueries and withs are added 
it gets a lot more complex.



I’ve already written c++ code that interprets (more or less) any sql and works 
out the schema.table.column each token ID belongs to but it’s lengthy code and 
when sqlite’s syntax changes I’ve got to relearn that code and make the 
appropriate changes. That’s all something that sqlite does in any case and all 
I’m trying to do is find a way to make use of that instead of having to 
maintain my own code.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, July 29, 2019 10:53:02 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

Let's go back to your example statement with your join of two tables.

select b,c from tbl0 join tbl1 where tbl0.a = tbl1.a and tbl0.a > ?1;

SQLIte should ask the authorizer the following questions:

1) SELECT
2) READ tbl0
3) READ tbl1
4) READ tbl0 field a
5) READ tbl1 field a
6) READ tbl0 field b
7) READ tbl1 field c

Postulated internal dialog of the Query Planner

QP: "Oh, a Select! May I SELECT on this connection?"
QP: "Oh, FROM tbl0! May I READ tbl0?"
QP: "Oh, FROM tbl1 too! May I READ tbl1?"
QP: "Oh, I need tbl0.a for the join! May I READ tbl0.a?
QP: "Oh, and I need tbl1.a for the join too! May I READ tbl1.a?
QP: "Oh, I need tbl0.a again, but I already know the answer"
QP: "Oh I need tbl0.b for the result set! May I READ tbl0.b?"
QP: "Oh and I need tbl1.c the result set! May I READ tbl1.c?"

Note that except fort he last two questions (where DENY means SELECT NULL), a 
DENY answer means that no more questions need to be asked.

Maybe you would like to have the questions posed in a different order (e.g. 
grouped by table 1,2,4,6,3,5,7; or in the order they occur in the statement 
1,6,7,2,3,4,5) because your procedure of answering them is easier to program 
that way. Point is, you don't get to choose. You need to be able to deal with 
the questions in any order and still be consistent about your answers. Anything 
else woudl remind me of the following conversation:

The monks are sitting in their benches, praying their mandatory morning 
prayers, when ohne lights up a cigarette.
"Hey, you aren't allowed to smoke during prayer!"
"But I am, by special permission of the abbot!"
"How come? I asked for permission to smoke during prayer and he declined!"
"Well, I asked for permission to pray while smoking."

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 29. Juli 2019 11:09
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

>>That strikes me as purely procedural thinking. Does the set of allowed 
>>operations really depend on the order of the requests (which probably depends 
>>on the query plan)? E.g. "you can update this field of this table only if you 
>>read this other field from that other table *first*"?

I’m not really sure what you mean Gunter. My problem is relating the column 
reference sent to the authorizer callback to an ID token within the sql as the 
orders differ. Sqlite orders them roughly as subqueries first followed by 
columns followed by WHEREs followed by ONs (but omitting USINGs) followed by 
ORDER BYs (complicated further by HAVINGs and GROUP BYs etc.). I’m not saying 
there’s anything wrong with that but writing code to anticipate the order 
they’re sent in is as fraught as hacking the sql code.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

>>Your implicit claim is "not all instances of column reference are reported to 
>>the authorizer, notably those inside a USING clause

That and you’ve got to anticipate the order they’re sent to the callback in.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing 

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[x]

>>That strikes me as purely procedural thinking. Does the set of allowed 
>>operations really depend on the order of the requests (which probably depends 
>>on the query plan)? E.g. "you can update this field of this table only if you 
>>read this other field from that other table *first*"?

I’m not really sure what you mean Gunter. My problem is relating the column 
reference sent to the authorizer callback to an ID token within the sql as the 
orders differ. Sqlite orders them roughly as subqueries first followed by 
columns followed by WHEREs followed by ONs (but omitting USINGs) followed by 
ORDER BYs (complicated further by HAVINGs and GROUP BYs etc.). I’m not saying 
there’s anything wrong with that but writing code to anticipate the order 
they’re sent in is as fraught as hacking the sql code.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

>>Your implicit claim is "not all instances of column reference are reported to 
>>the authorizer, notably those inside a USING clause

That and you’ve got to anticipate the order they’re sent to the callback in.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] Help with sqlite3TreeViewSelect

[x]

>>Your implicit claim is "not all instances of column reference are reported to 
>>the authorizer, notably those inside a USING clause

That and you’ve got to anticipate the order they’re sent to the callback in.

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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[x]

The following simple example might illustrate what I’m trying to achieve

CREATE TABLE tbl0 (a INTEGER PRIMARY KEY, b INTEGER);
CREATE TABLE tbl1 (a INTEGER PRIMARY KEY, c INTEGER);

Suppose a user enters the following sql

SELECT b, c FROM tbl0 INNER JOIN tbl1 USING (a) WHERE a > ?1;

I want to scan the sql, get the tables and columns associated with each ID 
token, check authorization and do some manipulation of my own. The 
sqlite3TreeViewSelect returns the following text

'-- SELECT (1/2364128) selFlags=0xc4 nSelectRow=179
|-- result-set
|   |-- {0:1}  flags=0x82
|   '-- {1:1}  flags=0x82
|-- FROM
|   |-- {0,*} tbl0 tab='tbl0' nCol=2 ptr=2421C68
|   '-- {1,*} tbl1 tab='tbl1' nCol=2 ptr=2421D88
'-- WHERE
'-- AND
|-- GT
|   |-- {0:-1}  flags=0x82
|   '-- VARIABLE(?1,1)
'-- EQ
|-- {0:-1}
'-- {1:-1}

From this text it would be easy to extract the equivalent sql

SELECT t0.b, t1.c FROM tbl0 t0, tbl1 t1 WHERE t0.rowid > ?1 AND t0.rowid = 
t1.rowid;

and I’ll know exactly what every table and column each token ID maps to. I can 
check the user is allowed to access these and then change any of the sql I want 
to before executing it.

I could use the sqlite3_set_authorizer but the schema.table.column for each ID 
token isn’t sent to the authorizer callback in the order they appear in the 
sql. It also mysteriously omits some tokens.

e.g. for above sql the authorizer wouldn’t receive a call for the ‘a’ column in 
USING (a) yet if the sql read

SELECT b, c FROM tbl0 INNER JOIN tbl1 ON tbl0.a = tbl1.a WHERE tbl0.a > ?1;

the authorizer callback would be called for tbl0.a and tbl1.a.


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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[x]

Thanks Gunter. I didn’t include the text of the original SELECT as it was 
basically an unnecessarily complex jumble of WITHs and embedded SELECTS just so 
I could see the response of the TreeViewSelect. I hoped someone would’ve been 
able to say “yes, the WITH is just included for completeness but is already 
integrated into the main SELECT”. I also hoped the ‘IF-NULL-ROW 8’ would have a 
simple explanation so didn’t think including the complex query would’ve been 
relevant (similar to including 30 lines of C code and asking what ‘goto’ meant).

I also made a mistake in what I printed in any case as the original WITH 
doesn’t appear in the part of the tree I printed (it was below the FROM at the 
end of it).


From: Hick Gunter<mailto:h...@scigames.at>
Sent: 28 July 2019 09:34
To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

WITH is basically syntactic sugar that allows you to name the result set of a 
certain select and refer to it by name, so that select has to appear in the 
generated bytecode and also in the query resolution tree.

Guessing what an element of the query resolution tree does would be very much 
easier if you were to provide the text of the select statement itself. You 
might also like to look at EXPLAIN QUERY PLAN  and then EXPLAIN to view the 
bytecode. I would guess that the WITH is only executed if the table internally 
numbered 8 does not deliver a row.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Samstag, 27. Juli 2019 14:08
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Help with sqlite3TreeViewSelect
g the lin
I’ve been using this while debugging by inserting the following code just 
before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to 
take the output from TreeView.txt and rebuild the query from that so I have a 
version of the query where I know which schema, table and column each ID token 
refers to. It looks fairly easy given the tables and columns are all numbered 
but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement 
but it also seems to incorporate the associated select(s) into the main SELECT 
such that the WITH statement can be effectively ignored. Is that always the 
case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT 
below that line is what was contained in the WITH statement)?

|-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
|-- result-set
|   |-- {0:-1}  flags=0x82
|   |-- {0:7}  flags=0x82
|   '-- IF-NULL-ROW 8
|   '-- SELECT-expr flags=0x2220800
|   '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
|   |-- result-set
|   |   '-- {9:1}  flags=0x82
|   |-- FROM
|   |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
|   |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
|   |-- WHERE
|   |   '-- AND
|   |   |-- EQ
|   |   |   |-- {9:-1}  flags=0x82
|   |   |   '-- 28
|   |   '-- EQ
|   |   |-- {9:-1}  flags=0x20008
|   |   |   '-- 28
|   |   '-- {11:-1}  flags=0x82
|   '-- LIMIT
|   '-- 1
|-- FROM
.

Also, is there any way I could redirect the stdout to a memory buffer rather 
than a file (I’m using clang compiler on windows)? It would be great if there 
was a function along the lines of sqlite3_normalized_sql(stmt) that returned 
the sqlite3TreeViewSelect text.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] Help with sqlite3TreeViewSelect

[x]

I’ve been using this while debugging by inserting the following code just 
before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to 
take the output from TreeView.txt and rebuild the query from that so I have a 
version of the query where I know which schema, table and column each ID token 
refers to. It looks fairly easy given the tables and columns are all numbered 
but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement 
but it also seems to incorporate the associated select(s) into the main SELECT 
such that the WITH statement can be effectively ignored. Is that always the 
case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT 
below that line is what was contained in the WITH statement)?

|-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
|-- result-set
|   |-- {0:-1}  flags=0x82
|   |-- {0:7}  flags=0x82
|   '-- IF-NULL-ROW 8
|   '-- SELECT-expr flags=0x2220800
|   '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
|   |-- result-set
|   |   '-- {9:1}  flags=0x82
|   |-- FROM
|   |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
|   |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
|   |-- WHERE
|   |   '-- AND
|   |   |-- EQ
|   |   |   |-- {9:-1}  flags=0x82
|   |   |   '-- 28
|   |   '-- EQ
|   |   |-- {9:-1}  flags=0x20008
|   |   |   '-- 28
|   |   '-- {11:-1}  flags=0x82
|   '-- LIMIT
|   '-- 1
|-- FROM
.

Also, is there any way I could redirect the stdout to a memory buffer rather 
than a file (I’m using clang compiler on windows)? It would be great if there 
was a function along the lines of sqlite3_normalized_sql(stmt) that returned 
the sqlite3TreeViewSelect text.

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

Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

[x]

Would sqlite3_expanded_sql(stmt) be of any use?



https://sqlite.org/c3ref/expanded_sql.html






From: sqlite-users  on behalf of 
test user 
Sent: Monday, July 22, 2019 1:36:25 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

I understand the problem, but I want my library to be able to detect the
problem programatically.

Currently if a user mixes index-based and key-based placeholders, the only
thing a library using SQLite can do us run the query with unbound
placeholders set to null (the SQLite default).

Id like the ability to instead throw an error in this case.


I think SQLite internally knows how many placeholders are in the query at
parse time.

My question is how can I get the data via the API, or if it would be
considered to add a function to get this data?


On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf  wrote:

>
> On Monday, 22 July, 2019 04:34, Enzo  wrote:
>
> >It is not the same information.
>
> >I want to be able to determine "has the user bound all placeholder
> >values with data?".
>
> >The user provides this as input:
>
> >query="SELECT ?, ?10"
> >data={"0": "data-a", "10": "data-b"}
>
> >Note: This IS valid, as they have provided all data for placeholders.
>
> >Using the APIs you mentioned:
>
> >count=10
> >name(10) = "?10"
>
> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >are placeholders in the query.
>
> >As you can see, only indexes [1, 10] are valid placeholders in the
> >query.
>
> >So, as far as I can tell, it is not possible to get this from the
> >API:
>
> >query="SELECT ?, ?10"
> >valid_placeholder_indexes=[1, 10]
>
> >It is only possible to get this:
>
> >query="SELECT ?, ?10"
> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>
> The fundamental issue is that you are confusing POSITIONAL parameters with
> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
> parameters, so in the query SELECT ?, ?10 you are using positional
> parameter 1 and positional parameter 10.  That implies that there are 10
> positional parameters.  The fact that you are only using those positional
> parameters sparsely (only using positional parameter 1 and positional
> parameter 10) does not mean that you are using two parameters, it means
> that you are using 10, but only referencing 2 of them.
>
> Said another way, you are declaring a function that looks like this:
>
> int stmt(a, b, c, d, e, f, g, h, i, j)
>return a + j
>
> and expecting to be able to call it as
>
> z = stmt(1, 6)
>
> expecting some magic to know that the second parameter is really parameter
> 10.
>
> https://www.sqlite.org/lang_expr.html#varparam
>
> There may be many positional parameters (like 999 in the default build)
> and sqlite3_bind_parameter_count returns the "index" of the greatest
> parameter number used in the statement.  Having created 10 parameters
> sqlite has absolutely no clue that you happen to be using only parameter 1
> and parameter 10.  If you only needed 2 parameters you should have only
> created 2, not 10.  That is what NAMED parameters are for.
>
> If you change from using positional (?) parameters to using named (:) or
> (@) what happens?
>
> select :1, :10; should only create 2 parameters named :1 and :10 ...
>
> --
> 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
>
___
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] Location of error in SQL statements ?

[x]

I think pzTail just points to the trailing ‘/0’ of zSql if an error occurs.




From: sqlite-users  on behalf of 
Rowan Worth 
Sent: Tuesday, May 7, 2019 9:07:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Location of error in SQL statements ?

On Tue, 7 May 2019 at 16:00, Eric Grange  wrote:

> Is there are way to get more information about an SQL syntax error message
> ?
> For example on a largish SQL query I got the following error message
>
> near "on": syntax error
>
> but as the query is basically a long list of joins, this is not too helpful
> ;)
>

It's not clear from the docs whether this is guaranteed, but a quick look
at the code suggests that the pzTail argument (if provided to
sqlite3_prepare_v2) is also updated when there's a parse error. You might
be able to rely on that to infer how far through the statement the problem
lies.

-Rowan
___
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] Where are the likes of sqlite3_db and sqlite3_stmt defined?

[x]

Thanks Simon.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Saturday, May 4, 2019 1:23:44 PM
To: SQLite mailing list
Subject: Re: [sqlite] Where are the likes of sqlite3_db and sqlite3_stmt 
defined?

On 4 May 2019, at 11:16am, x  wrote:

> I can’t find any definition of them in the amalgamation code nor see any 
> #included files that are likely to contain them.

They're not defined in the way you'd expect.  The lines quoted in the 
documentation are intended to give you the structure definition, but that's not 
how SQLite does what it does.  See

<https://www.sqlite.org/src/artifact/3ba14553508d66f5>

"
** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
** is really a pointer to an instance of this structure.
"

<https://www.sqlite.org/src/artifact/78027c6231fbb19c>

"
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
"

For a list of all source files,

<https://www.sqlite.org/src/dir?ci=tip>

Source for the library is in /src .
___
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] Where are the likes of sqlite3_db and sqlite3_stmt defined?

[x]

I can’t find any definition of them in the amalgamation code nor see any 
#included files that are likely to contain them.

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

Re: [sqlite] Debugging sqlite3.c

[x]

If I put this line at the top of sqlite3.c

#define SQLITE_DEBUG 1

should that be the same as setting SQLITE_DEBUG? I’ve tried it and the IDE 
still doesn’t add debug info for the sqlite3.c file.
From: x<mailto:tam118...@hotmail.com>
Sent: 26 April 2019 13:23
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Debugging sqlite3.c

Could anyone give me an idiot’s guide on how to do this in the rad studio 10 
IDE? I’ve tried adding DEBUG_SQLITE to “Project | Options | C++ (Shared 
Options) | Conditional defines ... (for Debug configuration – 32 bit Windows 
platform)” but it doesn’t seem to work. Looked elsewhere in the Options but 
can’t find anywhere else to add the DEBUG_SQLITE directive.

___
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] Debugging sqlite3.c

[x]

Could anyone give me an idiot’s guide on how to do this in the rad studio 10 
IDE? I’ve tried adding DEBUG_SQLITE to “Project | Options | C++ (Shared 
Options) | Conditional defines ... (for Debug configuration – 32 bit Windows 
platform)” but it doesn’t seem to work. Looked elsewhere in the Options but 
can’t find anywhere else to add the DEBUG_SQLITE directive.

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

Re: [sqlite] Help with sqlite3_value_text

[x]

Thanks Keith, that I understand. Thanks also to everyone who contributed to 
this thread. I’ve learned a lot from it.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Monday, April 15, 2019 4:09:02 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text


sqlite3_value_text and sqlite3_value_bytes will cause the conversion (if 
required) to UTF-8, and then return the data requested.
sqlite3_value_text16 and sqlite3_value_bytes16 will cause the conversion (if 
required) to UTF-16 and then return the data requested.

So if you call sqlite3_value_text AND THEN sqlite3_value_bytes16 your original 
UTF-8 text pointer will be invalid.  (Because the data must be converted to 
UTF-16 so that you can get the bytes count of that, thus the original UTF-8 no 
longer exists).

If you call only "like for like" functions, then the conversion will only be 
carried out the first time it is required and not for the subsequent calls to 
the "other function" that does not require conversion ...

---
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: Monday, 15 April, 2019 04:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>>As long as you use _value_bytes after _text you're fine... so if any
>>conversion did take place the value will be right of the last
>returned
>>string type.
>
>JD, Could you explain that to me? I’m not sure why any conversion
>takes place and, on reading the text below, I would’ve thought it
>would be better to call sqlite3_value_bytes first (if it’s called
>“subsequently” the pointer returned by sqlite3_value_text “can be
>invalidated”).
>
>Please pay particular attention to the fact that the pointer returned
>from sqlite3_value_blob()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html> can
>be invalidated by a subsequent call to
>sqlite3_value_bytes()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_bytes16()<https://sqlite.org/c3ref/value_blob.html>,
>sqlite3_value_text()<https://sqlite.org/c3ref/value_blob.html>, or
>sqlite3_value_text16()<https://sqlite.org/c3ref/value_blob.html>.”
>
>
>___
>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] Help with sqlite3_value_text

[x]

>As long as you use _value_bytes after _text you're fine... so if any
>conversion did take place the value will be right of the last returned
>string type.

JD, Could you explain that to me? I’m not sure why any conversion takes place 
and, on reading the text below, I would’ve thought it would be better to call 
sqlite3_value_bytes first (if it’s called “subsequently” the pointer returned 
by sqlite3_value_text “can be invalidated”).

Please pay particular attention to the fact that the pointer returned from 
sqlite3_value_blob(), 
sqlite3_value_text(), or 
sqlite3_value_text16() can be 
invalidated by a subsequent call to 
sqlite3_value_bytes(), 
sqlite3_value_bytes16(), 
sqlite3_value_text(), or 
sqlite3_value_text16().”


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

Re: [sqlite] Help with sqlite3_value_text

[x]

On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case 
values(1) contains more than a single unicode character. This looks OK.

# define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))

char *c = (char *)sqlite3_value_text(values[0]);
char *Sep = (char *)sqlite3_value_text(values[1]);
int Count=0, Len, SepLen = CHARLEN(*Sep);

while (*c)
{
   if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0) Count++; // 
at start of Sep
   c += Len;
}
sqlite3_result_int(ctx, Count);

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

Re: [sqlite] Help with sqlite3_value_text

[x]

From: J Decker
Sent: 13 April 2019 20:05
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

>> char *c = (char *)sqlite3_value_text(values[0]);
>> char *Sep = (char *)sqlite3_value_text(values[1]);
>> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);

>you could use sqlite3_value_bytes(values[1]); instead.

Hi JD, Would I have to worry about this

“Please pay particular attention to the fact that the pointer returned from 
sqlite3_value_blob(), 
sqlite3_value_text(), or 
sqlite3_value_text16() can be 
invalidated by a subsequent call to 
sqlite3_value_bytes(), 
sqlite3_value_bytes16(), 
sqlite3_value_text(), or 
sqlite3_value_text16().”

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

Re: [sqlite] Help with sqlite3_value_text

[x]

This seems to work OK as a sqlite function.



// assume values[0] & [1] are supplied and not null

// find Count of values[1] in values[0]



char *c = (char *)sqlite3_value_text(values[0]);

char *Sep = (char *)sqlite3_value_text(values[1]);

int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);



while (*c)

{



   Byte1 = (*c) >> 4;

   if ((Byte1 & 8) == 0) NrBytes = 1;

   else if (Byte1 & 1) NrBytes = 4;

   else if (Byte1 & 2) NrBytes = 3;

   else NrBytes = 2; // (Byte1 & 4) == 4



   if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++; // 
at first byte of Sep

   c += NrBytes;

}

sqlite3_result_int(ctx, Count);




From: sqlite-users  on behalf of 
Scott Robison 
Sent: Friday, April 12, 2019 8:40:19 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
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] Help with sqlite3_value_text

[x]

Thanks for all the help. Things are much clearer now.

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

Re: [sqlite] Help with sqlite3_value_text

[x]

Thanks for the replies. There’s plenty for me to look at there.

I’ve been in poor health the last 5 years and after almost a year’s break I’m 
trying to get back into sqlite to preserve my sanity. I’m so rusty my opening 
post is riddled with errors.

I’ve just realised that, before my break, I was dealing with this as follows

#define VALTXT(i) String((wchar_t*)sqlite3_value_text16(values[i]))

and inside the function I’d access the parameter with

String S = VALTXT(0);

The String type is an embarcadero c++ builder typedef which I think is a wide 
string. I then accessed the i’th character using S[I]. The above involves 
copying the parameter to another string.

In an old post I made on this forum someone told me I should be using 
sqlite3_value_text( as that was sqlite’s default storage and would save sqlite 
having to convert it to utf16.

I’ve been asking myself if I could have done the above more efficiently as 
sqlite’s converting the original string then I’m converting it and copying it. 
While thinking about that I started to wonder how c++ handled utf8/16. E.g. To 
access the i’th character does it have to rattle through all previous I-1 
characters to find the start of character i, how pointer arithmetic was handled 
when pointing to utf8/16 chars etc.

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

[sqlite] Help with sqlite3_value_text

[x]

I’m still confused by utf strings. For simplicity, suppose I set up an sqlite 
function that takes a single string parameter and I want to scan the string to 
count the number of occurrences of a certain character . If I knew the string 
was made up entirely of ascii chars I’d do this

char *c = &sqlite3_value_text(0)[0];
int count=0;
while (*c) if (*c++ == SearchChar) count++;

How do I do the same thing if the string param is a utf-8 or utf-16 string and 
the SearchChar is a Unicode character?

I’m confused by the fact that Unicode characters are not a fixed number of 
bytes so if I do this e.g.

wchar_t *c = (wchar_t*) sqlite3_value_text(0);

does this mean a complete temporary copy of the value of sqlite3_value_text(0) 
has to be constructed by the compiler such that all characters of the newly 
constructed string are fixed width? If so, I’m just wanting to check if there’s 
a way  of avoiding this overhead.

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

Re: [sqlite] pragma database_list

[x]

Thanks Richard.




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Thursday, January 24, 2019 3:09:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] pragma database_list

On 1/24/19, x  wrote:
> Forgetting the temp db, Is the list guaranteed to be in the same order the
> databases were attached in?

No

There might not be a counter example in the current implementation,
but that could change at any moment.

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

[sqlite] pragma database_list

[x]

Forgetting the temp db, Is the list guaranteed to be in the same order the 
databases were attached in?


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

Re: [sqlite] Determine collation associated with sort

[x]

>I think it's explaned in the docs here:
>https://sqlite.org/datatype3.html#collation

Thanks Luuk. That’s what I was looking for.


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

[sqlite] Determine collation associated with sort

[x]

Suppose I have a select such as

‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’

I want to know the collation associated with the sort. I know a COLLATE 
condition could be attached to the order by BUT suppose there’s none. How would 
I determine the collation associated with the sort?

I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html

int sqlite3_table_column_metadata(
  sqlite3 *db,/* Connection handle */
  const char *zDbName,/* Database name or NULLtion sequence */
  const char *zTableName, /* Table name */
  const char *zColumnName,/* Column name */
  char const **pzDataType,/* OUTPUT: Declared data type */
 char const **pzCollSeq, /* OUTPUT: Collation sequence name */
  int *pNotNull,  /* OUTPUT: True if NOT NULL constraint exists */
  int *pPrimaryKey,   /* OUTPUT: True if column part of PK */
  int *pAutoinc   /* OUTPUT: True if column is auto-increment */
);

which would tell me if a particular column of the sort has a collation sequence 
but what if it’s the index itself that has a collation attached? How would I 
cover all possible angles?







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

Re: [sqlite] Shouldn't have to specify primary key explicitly

[x]

INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of the 
automatically created autoincrement rowid but you have to supply the values 
(I.e. they’re not created automatically).

On 28 Jun 2018, at 12:48, Scott Robertson  wrote:

> SQLite is supposed to autoincrement by default when a column is defined
> as "INTEGER PRIMARY KEY" according to everything I've read. But I've
> only gotten this to work if I let SQLite create its own PK column. If I
> have an explicit PK column, I am expected to specify an ID myself. What
> am I missing? I don't know why I'm getting this error. Thanks.

You've defined the table with three cols so you have to provide three values 
unless you name the cols you wish to fill. To get SQLite to auto increment, use 
NULL as the value fo your id column:

INSERT INTO test2 VALUES (NULL, 'Fletch', 245);



--
Cheers  --  Tim
___
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] ___fixunsdfdi

[x]

Thanks Guy. After reading that I was wondering if it was maybe something to do 
with the code I’ve added to the end of the sqlite3.c file which includes 
several extentions. After commenting out the #include “csv.c” (& associated) 
code it linked OK.




From: sqlite-users  on behalf of 
Guy Harris 
Sent: Monday, June 18, 2018 11:45:38 AM
To: SQLite mailing list
Subject: Re: [sqlite] ___fixunsdfdi

On Jun 18, 2018, at 3:21 AM, x  wrote:

> I’m using c++ builder 10.2 Tokyo on windows 10 pro.
> In a console app I’m getting this error message
>
> [ilink32 Error] Error: Unresolved external '___fixunsdfdi' referenced from 
> C:\...\PROJECTS\WIN32\DEBUG\SQLITE3.OBJ
>
> I can’t find any mention of it in sqlite3.h or sqlite3.c.
>
> Anyone know anything about it?

It's a support routine to which some compilers generate calls.  The Intel 
System Studio documentation:

https://software.intel.com/en-us/node/704849

says

These functions convert a to an unsigned 64-bit integer rounding toward 
zero. If the integral value cannot be represented by the integer type, zero is 
returned.

The incremental linker is probably not linking with whatever library the 
compiler that built sqlite3.obj expects the program to be linked with.  You'd 
probably have to check the C++ Builder documentation to see what library that 
might be and how to ensure that it gets linked with that library.

___
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] ___fixunsdfdi

[x]

I’m using c++ builder 10.2 Tokyo on windows 10 pro.
In a console app I’m getting this error message

[ilink32 Error] Error: Unresolved external '___fixunsdfdi' referenced from 
C:\...\PROJECTS\WIN32\DEBUG\SQLITE3.OBJ

I can’t find any mention of it in sqlite3.h or sqlite3.c.

Anyone know anything about it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, I posted I was wrong about the limit offset comparison between 23.1 and 
24.0 but I’m unsure where I  gave you the impression the remainder of your post 
on the subject would be news to me. I’m well aware sqlite has to read all the 
rows to reach that offset. My point was that if we were able to specify it 
shouldn’t be buffered (I.e. it did not have to be saved in memory) then we’d 
maybe be spared the missing 86 secs (in the worst case test). I certainly 
wasn’t advocating that having offset  in a 1 table was a good 
strategy.

Thanks for reporting the bug. Hopefully something will be done but I suspect 
it’s been around a long time. I read three threads today complaining about 
sqlite’s cold cache performance. They were many years old going back as far as 
2006.

I disagree about the severity though. It’s devastated my rowid collection 
methods. I’m averse to pagination as I want to see the record number / count at 
the bottom of my grid and the option to move the scrollbar thumb track to any 
point and have that page come up instantly with the record number being known. 
It all worked fine until I tried making the collection from both ends (thus 
making pages near the top / bottom of the table accessible immediately) and 
having them meet in the middle. It was at that point I discovered the 
descending queries were crippling the performance.

Thanks again for all your work on this.

Tom

From: Keith Medcalf<mailto:kmedc...@dessus.com>
Sent: 17 June 2018 16:02
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] .timer


No, when you use OFFSET you are reading and discarding rows.  There is no 
difference between:

select * from t;

and discarding all the results except the last row, and,

select * from t limit ,1;

for a table containing 1 rows.  In both cases you have to read the 
entire table in order to find the last row.

However,

select * from t order by _rowid_ desc limit 1;

returns the last row directly rather than requiring the entire table to be read 
and will be fast no matter whether the file is cached or not.

The optimization was for LIMIT without the OFFSET, meaning that you know you 
only want so many rows of the results from the beginning, not at some offset 
(which increases the number of rows included, they are just not returned to you 
-- being discarded -- they still have to be read and processed) by not even 
calculating the rows beyond the LIMIT, if those can be determined and primarily 
affects complicated queries.

I have fed the bug report for the Windows cache handling back to the OEM where 
it will get to Microsoft.  While not severe, it does affect the usage of the 
FileSystem cache and when triggered under memory pressure can force page 
eviction (or swapping) that is unnecessary or even detrimental.  I suspect it 
was not found previously because the specific access pattern for a file is 
rather uncommon -- however that should make it easy to find and fix I should 
think.

However, what you are talking about here is now the difference between doing 
physical I/O and avoiding I/O by reading from a cache in RAM.

I/O is slow and the best way to optimize it (speed it up) is not to do it.  
(Gee that is old, from the 1960's I believe -- cannot remember who to attribute 
it to though).

---
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: Sunday, 17 June, 2018 06:35
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>It’s like having a world class soccer team and having to play in a
>league where the players are forced to wear stiletto heels. And it
>gets worse.
>
>Richard was saying he had sped up LIMIT queries in 3.24. I checked
>this out and was running LIMIT queries in sqlite expert (3.23.1) and
>my app (3.24). The former was taking 3 times as long to run the
>queries (not sure how much of that was down to improvements or sqlite
>expert). Anyway, I was getting quite excited until I realised I was
>conducting the test with a warm cache. When I tried the same with a
>cold cache I felt depressed even though the factor of 3 was still
>evident.
>
>The code below will illustrate. In one case it takes 109 secs to
>retrieve one record from a table that only took 77 secs to create.
>Keith mentioned earlier he was unable to test FILE_FLAG_NO_BUFFERING
>because of some setting in sqlite3. While I’ve no experience of the
>ramifications of setting this flag the LIMIT OFFSET looks like it’s
>tailor made for such a setting. Short of getting Microsoft to fix
>this, maybe what we need is a ‘pragma buffering = on/off’ although
>I’ve no idea if that’s possible or the difficulty involved.
>
>SQLite versi

Re: [sqlite] .timer

[x]

>Richard was saying he had sped up LIMIT queries in 3.24. I checked this out 
>and was running LIMIT queries >in sqlite expert (3.23.1) and my app (3.24). 
>The former was taking 3 times as long to run the queries (not >sure how much 
>of that was down to improvements or sqlite expert). Anyway, I was getting 
>quite excited >until I realised I was conducting the test with a warm cache. 
>When I tried the same with a cold cache I felt >depressed even though the 
>factor of 3 was still evident.

I’m talking drivel there. Having checked I must’ve been doing the sqlite expert 
queries on a cold cache and the ones in my app on a warm cache.

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

Re: [sqlite] .timer

[x]

It’s like having a world class soccer team and having to play in a league where 
the players are forced to wear stiletto heels. And it gets worse.

Richard was saying he had sped up LIMIT queries in 3.24. I checked this out and 
was running LIMIT queries in sqlite expert (3.23.1) and my app (3.24). The 
former was taking 3 times as long to run the queries (not sure how much of that 
was down to improvements or sqlite expert). Anyway, I was getting quite excited 
until I realised I was conducting the test with a warm cache. When I tried the 
same with a cold cache I felt depressed even though the factor of 3 was still 
evident.

The code below will illustrate. In one case it takes 109 secs to retrieve one 
record from a table that only took 77 secs to create. Keith mentioned earlier 
he was unable to test FILE_FLAG_NO_BUFFERING because of some setting in 
sqlite3. While I’ve no experience of the ramifications of setting this flag the 
LIMIT OFFSET looks like it’s tailor made for such a setting. Short of getting 
Microsoft to fix this, maybe what we need is a ‘pragma buffering = on/off’ 
although I’ve no idea if that’s possible or the difficulty involved.

SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> .open mytemp.db
sqlite> create table test as -- creates test table with 100 million rows
   ...> with recursive cte(x,y) as
   ...> (values(1,'012345678901234567890123456789')
   ...> union all select x+1,y from cte where x<1)
   ...> select * from cte;
Run Time: real 77.348 user 68.156250 sys 8.234375

sqlite> .shell flushmem
Available RAM - pre flush = 13.6147 GBs - post flush = 14.1428 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 30.722 user 3.515625 sys 17.609375

sqlite> .shell flushmem
Available RAM - pre flush = 14.2898 GBs - post flush = 14.4573 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 33.748 user 5.00 sys 18.078125

sqlite> .shell flushmem
Available RAM - pre flush = 14.4758 GBs - post flush = 14.4825 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 44.493 user 5.281250 sys 25.625000

Above results unimpressive and inconsistent.

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.269 user 2.609375 sys 4.656250

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.230 user 2.859375 sys 4.375000

Above 2 with warm cache.

sqlite> .shell flushmem
Available RAM - pre flush = 14.497 GBs - post flush = 14.4306 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 103.339 user 4.062500 sys 20.671875

sqlite> .shell flushmem
Available RAM - pre flush = 10.1498 GBs - post flush = 14.4109 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 92.210 user 3.812500 sys 15.50

sqlite> .shell flushmem
Available RAM - pre flush = 10.3382 GBs - post flush = 14.5637 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 109.676 user 3.796875 sys 21.562500

Woeful results and again inconsistent.

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.405 user 2.062500 sys 5.343750

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.440 user 2.546875 sys 4.890625

Above 2 with warm cache.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, are you rebooting or flushing the memory between these creates? I only 
notice the slowdown when they’re being started from scratch.



Have you any idea why, in my last set of tests, writing the ascending select 
RowIDs to file exhibited ‘normal’ behaviour on the ascending yet the slowdown 
occurred when the exact same ascending select RowIDs  were copied to a vector?




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Saturday, June 16, 2018 6:56:19 PM
To: SQLite mailing list
Subject: Re: [sqlite] .timer


I have confirmed that the distributed shell on sqlite.org displays the same 
behaviour.  You can duplicate this on Windows with the included.

When the select(s) containing the "order by _rowid_ desc" is processed Windows 
appears to convert the cached file entry in the Windows Cache from a straight 
"cached" file (pages fully discardable and reuseable) into a "memory mapped 
file" which causes other files/cache/working set to be ejected from memory.  
You can see this by the increase in memory being allocated and also if you use 
the RAMMAP tool available with the SysInternals package from SysInternals (now 
part of Microsoft) available at
https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite

Note that I am using 4 for the stop on the generate series.  This 
results in a 16GB database, (you can make this 1 for a 4GB database).  
I upped the size to make what was going on easier to see on a machine with 32GB 
of physical RAM (and no swap file).  In the script uncomment EITHER the CTE or 
the generate_series code to generate the test data.

Sqlite is NOT using memmap'ed files at all, just regular I/O and I do not see 
where this can be anything in the sqlite3 code -- it is Windows itself -- in 
this case WIndows 10 1803 Pro for Workstations (though it happens on at least 
the regular Pro version -- and probably all versions of Windows 10 and maybe 
earlier).

I tried it with a value that created a database that would be bigger than my 
Physical RAM to see what happened.  I will report that in a moment as I expect 
explosions and fireworks!


.echo on
.timer on
.eqp on
.stats on
pragma temp_store=file;
pragma cache_size=5120;
---
create table t
(
x integer not null,
y text collate nocase not null
);
---
--- Use either the generate_series or the CTE to populate the table t
--- generate_series is much faster but if you do not have the extension
--- available or builtin, using the CTE works just as well (although
--- slower.  4 is the number of rows.  1 generates
--- about 4 GB of data so you can change the number of rows generated
--- to match the size of the test data you want to generate
---
---.load series
---insert into t
---select value,
---   '012345678901234567890123456789'
---   from generate_series
---  where start=1
---and stop=4;
---
--- OR
---
insert into t
with recursive cte(x,y) as
 (values(1,'012345678901234567890123456789')
  union all select x+1,y from cte where x<4)
select * from cte;
---
---
  create temp table ta1 as
  select _rowid_
from t
order by _rowid_ asc;
---
  create temp table ta2 as
  select _rowid_
from t
order by _rowid_ asc;
---
  create temp table td1 as
  select _rowid_
from t
order by _rowid_ desc;
---
create temp table td2 as
select _rowid_
  from t
order by _rowid_ desc;
---
  create temp table ta3 as
  select _rowid_
from t
order by _rowid_ asc;
---
  create temp table td3 as
  select _rowid_
from t
order by _rowid_ desc;
---
  create temp table td4 as
  select _rowid_
from t
order by _rowid_ desc;
---
  create temp table ta4 as
  select _rowid_
from t
order by _rowid_ asc;
---




---
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 Keith Medcalf
>Sent: Friday, 15 June, 2018 18:28
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>Normally there is a small "Memory Mapped" buffer into the FileSystem
>cache that maintains a constant size.  This area is "mapped" into the
>Cache Working Set.  As you read/write different parts of files in the
>cache, the "mapped" are is "moved" so that the I/O can be carried out
>by the pager.  The same thing is done when you explicitly create a
>memory mapped file, except that you are in control of the mapped
>region and its size.
>
>When the heuristics detect "backwards" sequential/strided movement
>though a cached file, the new "beginning" of the mapped area is
>changed, but the "end" of the mapped area is not (like I said, this
>is probably a missing brace-bracket in the code, or a switch that
&

Re: [sqlite] .timer

[x]

> Yeah, I had a lot of problems with the fileio.c extension after the fsdir 
> virtual table was added.  It needs a >header file "test_windirent.h" to be 
> available.

That was the first thing I had to fix. The compiler couldn’t find the 
test_windirent.h file. I added the .../sqlite/src path to my system include 
path and got beyond that. I’ve no idea how to resolve the errors though and I’m 
still unsure of working with shell.c.

[bcc64 Error] fileio.c(507): unknown type name 'DIR'
[bcc64 Error] fileio.c(661): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(663): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(666): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
>Failed


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

Re: [sqlite] .timer

[x]

I’m trying to add fileio.c to my core_init function so I can investigate if the 
readfile and writefile functions would help here. I’ve added

#include “fileio.c”

Inside the core_init function what do I add ?

nErr += sqlite3_auto_extension((void(*)())sqlite3_fileio_init); // 
??
or
nErr += sqlite3_auto_extension((void(*)())sqlite3_readfile_init); // 
??
nErr += sqlite3_auto_extension((void(*)())sqlite3_writefile_init); // 
?

When the compiler reaches the ‘#include “fileio.c” I get several error messages

[bcc64 Error] fileio.c(507): unknown type name 'DIR'
[bcc64 Error] fileio.c(661): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(663): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(666): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
Failed
Elapsed time: 00:00:01.3

I can see these are defined in test_windirent.h and fileio.c has the line 
“include “test_windirent.h” so I don’t know why I’m getting these.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, many thanks for your detective work. Much of this is way above my head 
but, even if a solution doesn’t exist, your confirmation that I’m not mad is a 
good start to the day for me.

Given how bad the problem is I’m surprised there’s not a lot more about it on 
the web. In my travels the only thing I found was a tumbleweed thread on 
stackoverflow where an sqlite user was complaining that the first time he ran a 
query it took 10 times longer than subsequent executions. He said he raised the 
point on this forum but was told it was a windows issue.

> And you are correct, it appears to be related to "reading backwards" somehow.

I’m not sure I agree with that. I’m sure I’ve had cases in the past where the 
problem was reversed. Test 4 in my last post was an ascending case which slowed 
considerably after changing from storing the RowIDs in a tmpfile to storing in 
a vector. That one was really freaky as a speed up would’ve been expected.

Simon, the .333 didn’t help. I was unable to test the memory stick as the only 
one I had wouldn’t accept a file of MyTemp.db size. There was enough room so 
I’m not sure what the problem was.



From: Keith Medcalf<mailto:kmedc...@dessus.com>
Sent: 15 June 2018 23:22
Subject: Re: [sqlite] .timer


Ok, I have been able to reproduce this but have not figured out what is 
happening.

And you are correct, it appears to be related to "reading backwards" somehow.  
It is unaffected by the WIN32 cache mode that is set (I tested all modes and 
they all behave the same -- except that I could not test the NOBUFFER mode 
since SQLite3 I/Os are not page and cluster aligned), it happens whether using 
the WIN32 allocator or the system malloc.  It happens with both 32-bit code and 
64-bit code.

The memory *IS NOT* being allocated to SQLite3 or whatever process is running 
the sqlite3 database engine (its memory usage stays constant) and it is not 
actually being "used by" the FileSystem cache, it is not allocated to the 
process at all.  Even though it appears to be "eating" several Gigs of RAM 
(actually, it almost looks like a duplicate cache of the file being read, but 
this makes no logical sense whatsoever), this ram is not being allocated to the 
process because the 32-process does not get an Out-of-Memory error, and 32-bit 
processes have a limited arena to play in.  It is also not "dirty" write 
buffers since those are accounted for separately.

The RAM is being allocated without being committed (so it is a temporary usage 
thing).  However unlike actual FileSystem cache (which uses RAM not allocated 
for any other purpose) this appears usage appears to have "priority" over the 
FileSystem cache and over the process working set thus forcing working set 
and/or filesystem cache to be discarded.

Interestingly, sometimes the allocated memory "cleans itself up", however, if 
you follow the "desc" by an "asc" operation it appears to "clean up" and 
convert that usage into something else.  It is really very weird and is 
something in Windows itself, though what I have not a clue.

I never noticed it because I have lots of physical space and no swap file and 
their associated management overhead/tables, but it does show up as "allocated" 
memory and I presume that if you do not have lots and lots of extra RAM 
available it has an impact on performance as Windows fracks and un-fracks 
itself ...

---
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, 15 June, 2018 10:53
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>Thanks Keith. I did look on the performance page but didn’t realise
>clicking the items on the left brought up different info. I am on
>windows 10 pro.
>
>I haven’t had time to try creating the ‘personalised’ sqlite3.exe so
>the following relates to my own app run under the original conditions
>(i.e. without your code changes). In that app, rather than asking
>sqlite to create the table of RowIDs, my code steps through the
>select and stores the RowIDs in either a vector or a temporary file.
>For storing to the tmpfile it stores the data in a vector of size
>8192, writes those 8192 int64’s to the file using fwrite and so on in
>blocks of 8192. Note that if the procedure is run more than once
>without closing the app the same tmpfile is reused.
>
>The following relates to storing the RowIDs in a temp file run in 64
>bit mode.
>
>select RowID from Test order by RowID;
>-
>FlushMem
>Cached < 1 GB
>Run
>During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs.
>Run again without clos

Re: [sqlite] .timer

[x]

Thanks Keith. I did look on the performance page but didn’t realise clicking 
the items on the left brought up different info. I am on windows 10 pro.

I haven’t had time to try creating the ‘personalised’ sqlite3.exe so the 
following relates to my own app run under the original conditions (i.e. without 
your code changes). In that app, rather than asking sqlite to create the table 
of RowIDs, my code steps through the select and stores the RowIDs in either a 
vector or a temporary file. For storing to the tmpfile it stores the data in a 
vector of size 8192, writes those 8192 int64’s to the file using fwrite and so 
on in blocks of 8192. Note that if the procedure is run more than once without 
closing the app the same tmpfile is reused.

The following relates to storing the RowIDs in a temp file run in 64 bit mode.

select RowID from Test order by RowID;
-
FlushMem
Cached < 1 GB
Run
During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs.
Run again without closing app
During run Cached increases slightly (5.4 max) then returns to 4.7 GB in 16.5 
secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again.
App closed down.
Cached stays ~ 4.7 GB
Run returns much the same results had it not been shut down.
All of this is pretty much the results I’d expect – first run a bit slower than 
subsequent runs where the data is already cached. Nothing to see here imo.

select RowID from Test order by RowID desc;

FlushMem
Cached < 1 GB
Run
During run Cached rises very slowly but never above 1.5 GB. When the procedure 
finishes running Cached is showing under 1 GB but at the moment it finishes it 
jumps to 5 GB. Time = 91.4 secs.
Run again without closing app.
During run Cached gradually reduces from 5GB to 1GB in linear fashion but 
bursts back up 5 GB when procedure finishes. Time = 16 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run again 
takes over 90 secs with Cached < 1 GB throughout but bursts to 4.7 GB as 
procedure finishes).
Plenty to fathom here.

We now switch to vector mode. Select is stepped through and each RowID returned 
is stored in the vector of size 100,000,000. The tmpfile is never created. NB 
I’m doing the desc select first this time just in case.

select RowID from Test order by RowID desc;
-
FlushMem
Cached < 1 GB
Run
During run Cached never gets beyond 600 MB (for about 20 secs it seemed frozen 
on 297 MB). When the procedure finishes it’s showing 600 MB then shortly after 
shows 4.8 GB. Time = 85.3 secs.
Run again without closing app.
During run Cached linearly decreases to 900 MB then bursts to 4.8 GB on 
completion. Time =  14 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run takes 96? 
secs with Cached < 1 GB throughout but bursts to 4.7 GB as procedure finishes).
Similar to previous test.

select RowID from Test order by RowID;
-
FlushMem
Cached < 1 GB
Run
During run Cached climbs in linear fashion to ~4.7 GB in 63.3 secs. Unexpected 
time?
Run again without closing app
During run Cached stays approximately the same (~4.7 GB) in 14 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run again 
took 60 secs).
App closed down.
Cached stays ~ 4.7 GB
Run returns much the same results had it not been shut down (14s).

This last unexpected set of results suggests it’s nothing to do with the select 
direction. Because it differed so much from the first test I went through test 
4 several times but always got similar results. I also ran the first test 
(tmpfile version) another couple of times but, again, there was no change. It 
was the only one that gave me the expected results. I’m totally lost.







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

Re: [sqlite] .timer

[x]

Keith,



You spoon fed me on a previous thread how to load extensions using a core_init 
function placed at the end of the sqlite3.c code. I do have the series.c in my 
core_init so it is available to me and works fine in my cpp code. I don’t see 
how that relates to sqlite3.exe though. How do you get generate_series into 
that (to say nothing of those code changes)? The documentation says



“The generate_series(START,END,STEP) table-valued 
function<https://sqlite.org/vtab.html#tabfunc2> is a loadable 
extension<https://sqlite.org/loadext.html> included in the SQLite source tree, 
and compiled into the command-line shell<https://sqlite.org/cli.html>.”



That said, I can tell from my own app that having SQLITE_WIN32_FILE_RANDOM 
defined made no difference, in fact it made it considerably worse for the 
descending query. With the memory flushed the descending query was taking 10 
times the time it took with the cache unflushed. That’s up from around 4.



Having widows defender turned off changed nothing.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Friday, June 15, 2018 9:41:18 AM
To: SQLite mailing list
Subject: Re: [sqlite] .timer


Yes, that is correct.  Then compile with the preprocessor symbol 
SQLITE_WIN32_FILE_RANDOM defined.

generate_series is an extension module located in ext/misc/series.c

https://www.sqlite.org/src/dir?ci=567e09ef2a8cd84a&name=ext/misc

By default I load almost every one of those extensions in every sqlite3.dll and 
sqlite3.exe I compile (along with the INIT hook to get them loaded on each 
connection automatically -- basically make them all builtin extensions), along 
with a bunch of others I wrote myself that do things like add the entire 
standard math library, running statistics, a bunch of Windows functions (get 
the computer name, username, sids, translate sids between blobs and strings, 
check the token for sids, handle uuid functions etc), ip address functions, 
unicode case/accent folding, etc.  It makes the DLL and static shell about 2 
MB, but I have no shortage of CPU or memory anywhere ... (though I have to keep 
remembering whenever I move stuff to other servers and VMs that use actual slow 
hard drives that I have to keep the I/O rate within the piddly bounds of 
spinning rust -- NVMe and SSD drives with GB/s rather than KB/s IO rates can 
kill you).

I presume you are running current Win10Pro.

There was a "bug" introduced in Defender back in late November that has now 
supposedly been fixed (don't know if it was in Defender or in Windows itself, 
but turning off the real-time defender fixed the issue) that adversely affected 
a bunch of crap (the main thing I noticed is that is doubled or more my build 
times -- by introducing the generic "doing nothing" (ie, some build processes 
loaded up all the SMT threads on the processor, and ran at 100% CPU usage for 
each thread on each core for a couple of minutes -- that time doubled and the 
CPU usage dropped to about 50% average on each thread) for which Microsoft is 
famous).  That has since been fixed (in March or something) and I haven't 
noticed anything else acting "weird" -- though like I said I have no idea if it 
was Defender that was the problem or if that just brought out some other issue 
in the windows kernel itself.

---
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, 15 June, 2018 02:06
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>Keith, I can find no instance of 2966 in my sqlite.c code. I have
>found the code you mentioned
>
>
>
>  if( isDelete ){
>
>#if SQLITE_OS_WINCE
>
>dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN;
>
>isTemp = 1;
>
>#else
>
>dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
>
>  |
>FILE_ATTRIBUTE_HIDDEN
>
>  |
>FILE_FLAG_DELETE_ON_CLOSE;
>
>#endif
>
>  }else{
>
>dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL;
>
>  }
>
>
>
>Immediately after that section of code I’ve replaced
>
>
>
>#if SQLITE_OS_WINCE
>
>  dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;
>
>#endif
>
>
>
>With
>
>
>
> /* Reports from the internet are that performance is always
>
>  ** better if FILE_FLAG_RANDOM_ACCESS is used.  Ticket #2699. */
>
>#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM
>
>  dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;
>
>#elif SQLITE_WIN32_FILE_SEQUENTIAL
>
>  dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN;
>
>

Re: [sqlite] .timer

[x]

>Actually I cannot reproduce even if I turn off forced RANDOM mode for the 
>cache, reduce the size of the >sqlite3 cache to the default, and make sure the 
>temp_store is on disk (not my default).

Are you rebooting or flushing the cache between the commands? I don’t have any 
problems (except maybe on the first call) if I don’t flush.

>I do note, however, that the actual CPU used is relatively constant (USER+SYS) 
>and that it is the REAL time >only that is going "bonkers" which suggests some 
>process other than sqlite is what is causing the >slowdown.  It could be 
>something the hardware is doing -- I have never used a "tablet" as if it were 
>a >computer ...

After rebooting it doesn’t matter how long I wait before running the test. If 
it’s some background task it must surely be triggered by what I’m doing. That 
said, if the slowness and increasing / decreasing memory is down to windows 
caching the data why is ‘sys’ not able to include it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, I can find no instance of 2966 in my sqlite.c code. I have found the 
code you mentioned



  if( isDelete ){

#if SQLITE_OS_WINCE

dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN;

isTemp = 1;

#else

dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY

  | 
FILE_ATTRIBUTE_HIDDEN

  | 
FILE_FLAG_DELETE_ON_CLOSE;

#endif

  }else{

dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL;

  }



Immediately after that section of code I’ve replaced



#if SQLITE_OS_WINCE

  dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;

#endif



With



 /* Reports from the internet are that performance is always

  ** better if FILE_FLAG_RANDOM_ACCESS is used.  Ticket #2699. */

#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM

  dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;

#elif SQLITE_WIN32_FILE_SEQUENTIAL

  dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN;

#elif SQLITE_WIN32_FILE_WRITETHROUGH

  dwFlagsAndAttributes |= FILE_FLAG_WRITE_THROUGH;

#elif SQLITE_WIN32_FILE_NOBUFFER

  dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING;

#endif



Is that correct?



BTW ‘select * from generate_series(1,10)’ gives me an error ‘no such table : 
generate_series’ in sqlite3.exe. I thought it was compiled into the shell by 
default?








From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Friday, June 15, 2018 8:10:19 AM
To: SQLite mailing list
Subject: Re: [sqlite] .timer


Yes.  Search your sqlite3.c for #2966 (there is only one instance and it is in 
the winOpen function, somewhere around line 44847).  The code that is there 
will be an #ifdef that forces RANDOM_ACCESS only for WINCE.  Just change it so 
that you can compile it with RANDOM_ACCESS set and see if that makes a 
difference.  And yes, it will work in both 32 and 64 bit on Windows ... It will 
at least make the Windows caching deterministic (technically LRU).

I know that Windows supposedly has some builtin rules about how it sets the 
default cache mode, but you should really have the whole file in the cache 
after each command since that is the purpose of the cache (memory not used is 
money wasted).

Then re-run your test without freeing up physical cache memory between the runs 
(so the database stays in the cache).  The first run though the file will take 
a long time (SYS I/O Time) but subsequent passes should be very fast since the 
data is all already in the cache ... (the USER time should stay about the same, 
but the SYS time will change depending on whether the file is in the cache or 
not).

---
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, 15 June, 2018 00:50
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>Thanks for all the replies and great suggestions. I’m just up but
>will shortly investigate all.
>
>
>
>After reading this post by Clemens Ladisch
>
>
>
>http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts-
>tp102034p102105.html
>
>
>
>I was guessing the answer might be something along the lines of what
>is suggested below although I was clueless about how to go about it.
>To be honest I’m still not sure Keith.
>
>
>
>1 Is #2699 an sqlite ticket? I can’t find it.
>
>2 Do I add this code to my sqlite3.c file? If so, whereabouts?
>
>3 Will it still work if I compile in 64 bit mode?
>
>
>
>
>From: sqlite-users  on
>behalf of Keith Medcalf 
>Sent: Thursday, June 14, 2018 10:09:50 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>See the following web page for how the default "Microsoft Magical
>Mystery Cache Mode" works on Windows.  The term "Intelligent Read-
>Ahead" applies only if you are 12 years old (typical Microsoft
>behaviour).
>
>http://flylib.com/books/en/4.491.1.101/1/
>
>Note that the default mode is completely fracking useless for most
>intents and purposes, and for databases the SEQUENTIAL mode is bloody
>awful as well.
>
>However, the SEQUENTIAL mode seems to match what is being seen (the
>look ahead is only working in one direction and pages are being
>unmapped from the system cache at the wrong (read most stoopid) time
>possible).
>
>So, the changes I made are to function winOpen as follows (after the
>ticket #2699 comment).  Effectively I always set the flags for RANDOM
>mode even though I am not Winders Crappy Edition ...
>
>  if( isDelete ){
>#if SQLITE_OS_WINCE
>dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN;
>isTemp = 1;
>#else
>dwFlagsAndAttributes = FILE_A

Re: [sqlite] .timer

[x]

Thanks for all the replies and great suggestions. I’m just up but will shortly 
investigate all.



After reading this post by Clemens Ladisch



http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts-tp102034p102105.html



I was guessing the answer might be something along the lines of what is 
suggested below although I was clueless about how to go about it. To be honest 
I’m still not sure Keith.



1 Is #2699 an sqlite ticket? I can’t find it.

2 Do I add this code to my sqlite3.c file? If so, whereabouts?

3 Will it still work if I compile in 64 bit mode?




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Thursday, June 14, 2018 10:09:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] .timer


See the following web page for how the default "Microsoft Magical Mystery Cache 
Mode" works on Windows.  The term "Intelligent Read-Ahead" applies only if you 
are 12 years old (typical Microsoft behaviour).

http://flylib.com/books/en/4.491.1.101/1/

Note that the default mode is completely fracking useless for most intents and 
purposes, and for databases the SEQUENTIAL mode is bloody awful as well.

However, the SEQUENTIAL mode seems to match what is being seen (the look ahead 
is only working in one direction and pages are being unmapped from the system 
cache at the wrong (read most stoopid) time possible).

So, the changes I made are to function winOpen as follows (after the ticket 
#2699 comment).  Effectively I always set the flags for RANDOM mode even though 
I am not Winders Crappy Edition ...

  if( isDelete ){
#if SQLITE_OS_WINCE
dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN;
isTemp = 1;
#else
dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
   | FILE_ATTRIBUTE_HIDDEN
   | FILE_FLAG_DELETE_ON_CLOSE;
#endif
  }else{
dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL;
  }
  /* Reports from the internet are that performance is always
  ** better if FILE_FLAG_RANDOM_ACCESS is used.  Ticket #2699. */
#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM
  dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;
#elif SQLITE_WIN32_FILE_SEQUENTIAL
  dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN;
#elif SQLITE_WIN32_FILE_WRITETHROUGH
  dwFlagsAndAttributes |= FILE_FLAG_WRITE_THROUGH;
#elif SQLITE_WIN32_FILE_NOBUFFER
  dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING;
#endif

This adds the SQLITE_WIN32_FILE_ ... defines and applies them in order to all 
files opened by the Windows VFS.  I define SQLITE_WIN32_FILE_RANDOM to make 
sure that the cache mode is always set for RANDOM access and that read-ahead 
and idiot-mode (ie, Microsoft-style) cache pruning are disabled, thus making 
the FileSystem cache act in a deterministic LRU page ejecting fashion.

---
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 Keith Medcalf
>Sent: Thursday, 14 June, 2018 14:16
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>Cannot reproduce.
>
>I am using the current trunk that I compile myself with MinGW 8.1.0
>on Windows 10 1803 Pro for Workstations.  The laptop has a 4 Ghz Quad
>Core Xeon and the disk is a Samsung NVMe drive.  About the only
>relevant change is that I have forced the Windows caching mode from
>"magical mystery Microsoft mode" to always use "Random access mode".
>It is also using the Win32 heap allocator.  I also have 32 GB of RAM
>and no third or fourth level page indirection or Virtual Arena
>diddling (that is, swapping is turned off).  I also have SQLite set
>to a 4K pagesize and have set 262144 pages of cache in SQLite by
>default.
>
>I forgot how slow CTE's are until I did this ... almost 3 times
>slower than using generate_series
>
>In RAM only -- Windows caching modes and disk I/O are irrelevant:
>
>>sqlite
>SQLite version 3.25.0 2018-06-13 17:19:20
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table test as -- creates test table with 100 million
>rows
>   ...> with recursive cte(x,y) as
>   ...> (values(1,'012345678901234567890123456789')
>   ...>  union all select x+1,y from cte where x<1)
>   ...> select * from cte;
>Run Time: real 34.619 user 34.625000 sys 0.00
>sqlite> drop table test;
>Run Time: real 1.578 user 1.578125 sys 0.00
>sqlite> create table test (x integer, y text);
>Run Time: real 0.000 user 0.00 sys 0.00
>sqlite> insert into test select value,
>'012345678901234567890123456789' from generate_series where start=1
>and st

Re: [sqlite] .timer

[x]

Thanks for the replies. It is Windows Defender I’m using on a ms surface pro 4 
with 16 GB ram and 512 GB SSD. OS is Windows 10 Pro. I’ve come up with the 
following demo using the sqlite shell. In it I use a couple of small apps 
called AvlRAM and FlushMem. Apart from minor background tasks sqlite3.exe is 
the only app running.

I downloaded FlushMem from here https://chadaustin.me/flushmem/ and changed it 
slightly so that it reported the available RAM before and after the flush. I 
was primarily interested in how sqlite was performing after restarting the 
computer before any info was lying about in caches as I was encountering some 
strange timings with such. FlushMem saved me from continually rebooting and 
yielded results similar to the ones I was experiencing with rebooting.

The code for AvlRAM is as follows

double FreeGBs()
{
MEMORYSTATUSEX status;
status.dwLength = sizeof(status);
GlobalMemoryStatusEx(&status);
return status.ullAvailPhys / (1024.0 * 1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
std::cout << FreeGBs() << " GBs" << std::endl;
return 0;
}


Here’s the demo interspersed with comments

SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> .open MyTemp.db -- open empty DB
sqlite> .shell FlushMem
Available RAM - pre flush = 12.913 GBs - post flush = 14.1749 GBs

sqlite> create table test as -- creates test table with 100 million rows
   ...> with recursive cte(x,y) as
   ...> (values(1,'012345678901234567890123456789')
   ...> union all select x+1,y from cte where x<1)
   ...> select * from cte;
Run Time: real 77.239 user 68.75 sys 7.468750
sqlite> .shell AvlRAM
14.1059 GBs

sqlite> create temp table tasc as select RowID from test order by RowID;
Run Time: real 32.473 user 25.203125 sys 7.203125
sqlite> .shell AvlRAM
14.1084 GBs
// little change to RAM, respectable time

sqlite> create temp table tdesc as select RowID from test order by RowID desc;
Run Time: real 32.056 user 24.515625 sys 7.531250
sqlite> .shell FlushMem
Available RAM - pre flush = 9.96339 GBs - post flush = 14.7108 GBs
// why does the RAM drop 4+ GB due to executing the above?, respectable time


sqlite> create temp table tasc2 as select RowID from test order by RowID;
Run Time: real 38.285 user 26.109375 sys 9.00
sqlite> .shell FlushMem
Available RAM - pre flush = 14.5936 GBs - post flush = 14.7553 GBs
// no change to RAM, time a bit more sluggish after ‘reboot’ but still 
respectable

sqlite> create temp table tdesc2 as select RowID from test order by RowID desc;
Run Time: real 117.765 user 28.265625 sys 13.828125
sqlite> .shell FlushMem
Available RAM - pre flush = 10.5519 GBs - post flush = 14.6888 GBs
// RAM drops 4+ GB, time is woeful. Why?

// repeat above 2 ‘create temp table’ commands to demonstrate not fluke

sqlite> create temp table tasc3 as select RowID from test order by RowID;
Run Time: real 41.747 user 26.562500 sys 10.625000
sqlite> .shell FlushMem
Available RAM - pre flush = 14.78 GBs - post flush = 14.754 GBs
// no change to RAM, time a bit more sluggish after ‘reboot’ but still 
respectable

sqlite> create temp table tdesc3 as select RowID from test order by RowID desc;
Run Time: real 118.282 user 29.406250 sys 13.765625
sqlite> .shell FlushMem
Available RAM - pre flush = 10.6947 GBs - post flush = 14.5856 GBs
// RAM drops 4- GB, time is woeful. Why?

sqlite> .exit



Why is using ‘RowID desc’ so much slower than ‘RowID asc’ after a ‘reboot’? I 
get the impression Windows is caching the pages from the ‘desc’ but not the 
‘asc’ and that’s what’s slowing it down. On this particular journey I’ve come 
across tables where the reverse was the case (I.e. it was the ‘asc’ that was 
woefully slow).

Anyone cast any light on this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 14. 
This is related to the thread

http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts-td102034.html

or at least the latter part of it.



I’m currently working on a repeatable shell demonstration to show how bad a 
light it shows sqlite in.




From: sqlite-users  on behalf of 
R Smith 
Sent: Thursday, June 14, 2018 10:34:58 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] .timer


On 2018/06/14 10:28 AM, x wrote:
> Thanks for the detail Simon. I’m consistently getting some really big 
> differences where user + sys < real. I’ll post another thread on it but it 
> does seem to be something windows is doing rather than sqlite.

If sys + user << real  -  you are probably running Windows. :)


On a more serious note, if I may - how big are those "big differences"
and on which OS and media and what kind(s) of query?


___
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] .timer

[x]

Thanks for the detail Simon. I’m consistently getting some really big 
differences where user + sys < real. I’ll post another thread on it but it does 
seem to be something windows is doing rather than sqlite.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Thursday, June 14, 2018 9:08:31 AM
To: SQLite mailing list
Subject: Re: [sqlite] .timer

On 14 Jun 2018, at 8:33am, x  wrote:

> Could someone describe what the return values real, user and sys mean and why 
> there’s sometimes a big difference between real and the sum of user & sys?.

[The following is simplified for clarity.]

'real' -- Elapsed time between the start and end of the command, as measured by 
the clock on your wall.  Sometimes called 'wall time'.

The other two figures both concern just the process you're interested in, 
ignoring the many other things the computer is doing at the same time like 
seeing if you've clicked your mouse, updating your screen, checking to see if 
your laptop battery is going to run out, etc..

'user' -- Processor time taken by the command itself.  If you look at all the 
source code for that command, this is the time taken to run that source code.

'sys' -- Processor time taken to execute the system calls the command used.  If 
the command used system calls to find the current time, allocate memory, and 
write some bytes to a file, the amount of time each system call took 
contributes to 'sys', not 'user'.

If 'sys' + 'user' > 'real', something weird happened.

If 'sys' + 'user' < 'real', your computer is busy doing a lot of stuff in the 
background.  You probably have a printer plugged in, an ethernet or WiFi 
connection active, a keyboard and mouse pointer being monitored, etc..  This is 
normal on a modern computer,

Simon.
___
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] .timer

[x]

Could someone describe what the return values real, user and sys mean and why 
there’s sometimes a big difference between real and the sum of user & sys?.

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

Re: [sqlite] [EXTERNAL] Selecting multiple similar columnname.

[x]

Does ‘hidden’ apply to normal tables as well as virtual tables? I tried



CREATE TABLE t(x,y,z hidden);

select * from t;



but it showed all three cols.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Friday, June 8, 2018 7:03:56 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Selecting multiple similar columnname.

Adding the attribute "hidden" to a column prevents it from showing up in the 
expansion of '*' in the select list, while still allowing it to be queried by 
name. There is no RE matching in the select list; you are expected to know 
exactly what you want.

Having multiple instances of a field or fields in the row suggests that you 
schema is not properly normalized. This needs to be a concious design decision.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von mukeshkb4u
Gesendet: Donnerstag, 07. Juni 2018 18:12
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Selecting multiple similar columnname.

Hi All,
I have a table with multiple similar column names, like abc_1,abc_2,abc_3...

Is there a way i can do a select on only these columns in a table, without 
specifiying the full column list?
Can i use  a regular expression in selecting column names ?

Regards
Mukesh



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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] Reset the cursor

[x]

If the first loop exits with res3 == SQLITE_DONE then !result will be true and 
the second loop should process exactly the same (assuming underlying data is 
unchanged). I can’t see why the code below wouldn’t work although I’m confused 
by the fact you say that sqlite3_step(stmt3)  returns SQLITE_DONE immediately 
after the sqlite3_reset(stmt3) but later say it’s returning 1 (SQLITE_ERROR).


int result = 0,  res3 = SQLITE_OK;
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
// initial processing
}
else if( res3 == SQLITE_DONE )
break;
else
{
// error handling
result = 1;
}
}
if( !result )
{
res3 = sqlite3_reset( stmt3 );
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
   // actual processing
}
else if( res3 == SQLITE_DONE )
   break;
else
{
   // error handling
}
}
}

Not sure where this code belongs

if( res3 != SQLITE_DONE )
break;
}

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

Re: [sqlite] Reset the cursor

[x]

Sorry, I didn’t notice res3 was reassigned just before the comparison. I was 
confused by you setting res3 = SQLITE_OK on the first line as I can see no 
purpose to that.




From: sqlite-users  on behalf of 
Igor Korot 
Sent: Monday, June 4, 2018 3:44:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] Reset the cursor

x,

On Mon, Jun 4, 2018 at 9:42 AM, x  wrote:
> int result = 0,  res3 = SQLITE_OK;
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
>
>
> As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK 
> so
>
> res3 == SQLITE_ROW is never true.

But SQLITE_OK != SQLITE_ROW.

Thank you.

>
>
>
>
>
> 
> From: sqlite-users  on behalf 
> of Igor Korot 
> Sent: Monday, June 4, 2018 3:33:54 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Reset the cursor
>
> Hi, Igor,
>
> On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik  wrote:
>> On 6/4/2018 12:31 AM, Igor Korot wrote:
>>>
>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record
>>> 1
>>> so I can process those records again.
>>>
>>> I thought that this will be a job of sqlite_reset(), but when I called
>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>> very first iteration.
>>
>>
>> sqlite_reset definitely works. The problem must be somewhere in the code you
>> haven't shown. Can you reproduce in a small complete example?
>
> Following the exact code taken from y source.
> Can you spot an error?
>
> [code]
> int result = 0,  res3 = SQLITE_OK;
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
> // initial processing
> }
> else if( res3 == SQLITE_DONE )
> break;
> else
> {
> // error handling
> result = 1;
> }
> }
> if( !result )
> {
> res3 = sqlite3_reset( stmt3 );
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
> // actual processing
> }
> else if( res3 == SQLITE_DONE )
> break;
> else
> {
> // error handling
> }
> }
> }
> if( res3 != SQLITE_DONE )
> break;
> }[/code]
>
> Thank you.
>
>> --
>> Igor Tandetnik
>>
>>
>>
>> ___
>> 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
___
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] Reset the cursor

[x]

int result = 0,  res3 = SQLITE_OK;
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )


As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK so

res3 == SQLITE_ROW is never true.






From: sqlite-users  on behalf of 
Igor Korot 
Sent: Monday, June 4, 2018 3:33:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] Reset the cursor

Hi, Igor,

On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik  wrote:
> On 6/4/2018 12:31 AM, Igor Korot wrote:
>>
>> Now I'd like the cursor in the recordset of the "stmt" to go to the record
>> 1
>> so I can process those records again.
>>
>> I thought that this will be a job of sqlite_reset(), but when I called
>> it and started re-processing the recordset I got SQLITE_DONE on the
>> very first iteration.
>
>
> sqlite_reset definitely works. The problem must be somewhere in the code you
> haven't shown. Can you reproduce in a small complete example?

Following the exact code taken from y source.
Can you spot an error?

[code]
int result = 0,  res3 = SQLITE_OK;
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
// initial processing
}
else if( res3 == SQLITE_DONE )
break;
else
{
// error handling
result = 1;
}
}
if( !result )
{
res3 = sqlite3_reset( stmt3 );
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
// actual processing
}
else if( res3 == SQLITE_DONE )
break;
else
{
// error handling
}
}
}
if( res3 != SQLITE_DONE )
break;
}[/code]

Thank you.

> --
> Igor Tandetnik
>
>
>
> ___
> 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] Reset the cursor

[x]

Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW



But note that a successful sqlite3_step does not return SQLITE_OK (0). Are you 
maybe converting the result to Boolean?




From: sqlite-users  on behalf of 
Igor Korot 
Sent: Monday, June 4, 2018 1:52:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] Reset the cursor

Hi,

On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot  wrote:
> Hi, Clemens et al,
>
> On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch  wrote:
>> Igor Korot wrote:
>>> res = sqlite3_step( stmt );
>>>
>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
>>> so I can process those records again.
>>
>> Strictly speaking, it is not possible to go back in the _same_ cursor.
>> You'd have to execute the query again (by calling sqlite3_reset() and
>> sqlite3_step()), and if you're not in a transaction, the data might
>> have been modified between these two calls.
>>
>>> I thought that this will be a job of sqlite_reset(), but when I called
>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>> very first iteration.
>>
>> In theory, executing the same query on the same data should work again.
>> Did you accidentally call sqlite3_clear_bindings()?
>
> No, I didn't clear anything.
> I just call sqlite3_reset() and sqlite3_step() and receive an error.

In addition:
As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error).
However calling sqlite3_errcode() right after returns 0 (which is
success, right)

Thank you.

>
> Thank you.
>
>>
>>
>> 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] Reset the cursor

[x]

Igor, if sqlite3_step is successful it returns SQLITE_ROW which is 1.




From: sqlite-users  on behalf of 
Igor Korot 
Sent: Monday, June 4, 2018 1:52:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] Reset the cursor

Hi,

On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot  wrote:
> Hi, Clemens et al,
>
> On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch  wrote:
>> Igor Korot wrote:
>>> res = sqlite3_step( stmt );
>>>
>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
>>> so I can process those records again.
>>
>> Strictly speaking, it is not possible to go back in the _same_ cursor.
>> You'd have to execute the query again (by calling sqlite3_reset() and
>> sqlite3_step()), and if you're not in a transaction, the data might
>> have been modified between these two calls.
>>
>>> I thought that this will be a job of sqlite_reset(), but when I called
>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>> very first iteration.
>>
>> In theory, executing the same query on the same data should work again.
>> Did you accidentally call sqlite3_clear_bindings()?
>
> No, I didn't clear anything.
> I just call sqlite3_reset() and sqlite3_step() and receive an error.

In addition:
As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error).
However calling sqlite3_errcode() right after returns 0 (which is
success, right)

Thank you.

>
> Thank you.
>
>>
>>
>> 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] This is driving me nuts

[x]

Yes, I think you’re right. I suppose maybe it recognises the desc sequence from 
page access.




From: sqlite-users  on behalf of 
Andy Ling 
Sent: Thursday, May 31, 2018 3:57:27 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] This is driving me nuts

> While that makes sense Clemens it again fails to explain why it does no
> caching when desc is stepped through first. I am at least satisfied that it’s 
> not
> something I should dwell on or, more the point, it isn’t down to a bug in my
> code which is why I got embroiled in it in the first place.
>

My understanding from what Clemens said, which might clarify.

When ascending pages are read sequentially, so sequential mode is used and
the pages get saved in the cache, but when descending, pages are read in
reverse order which makes the cache think it's random so it doesn't keep them.

So if you do ascending first the cache gets filled and eats up memory.
Then when you do descending it gradually deletes all the pages from the cache
and frees up the memory. If you do it the other way round, descending
uses one page worth of memory then ascending eats up enough for all the pages.

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
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] This is driving me nuts

[x]

While that makes sense Clemens it again fails to explain why it does no caching 
when desc is stepped through first. I am at least satisfied that it’s not 
something I should dwell on or, more the point, it isn’t down to a bug in my 
code which is why I got embroiled in it in the first place.



For completeness here’s the ‘ac-dc’ figures



for (int i=0; i<5; i++)

{

for (int j=0; j<1000; j++) sqlite3_step(asc);

std::cout << "After asc " << FreeMBs() << std::endl;

for (int j=0; j<1000; j++) sqlite3_step(desc);

std::cout << "After desc " << FreeMBs() << std::endl;

}



10mill Ascending - 10mill Descending

After asc 12324

After desc 12334

After asc 11391

After desc 11434

After asc 10484

After desc 10484

After asc 9534

After desc 9535

After asc 8585

After desc 8586





Tom




From: sqlite-users  on behalf of 
Clemens Ladisch 
Sent: Thursday, May 31, 2018 2:06:08 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] This is driving me nuts

x wrote:
> Why would window’s increase it’s cache size during step ascending then
> reduce it during step descending in such a consistent manner?

Windows has several different caching strategies.
<https://msdn.microsoft.com/en-us/library/windows/desktop/aa363858.aspx>
says:
| FILE_FLAG_RANDOM_ACCESS
|
|Access is intended to be random. The system can use this as a hint to
|optimize file caching.
|
| FILE_FLAG_SEQUENTIAL_SCAN
|
|Access is intended to be sequential from beginning to end. The system
|can use this as a hint to optimize file caching.
|
|This flag should not be used if read-behind (that is, reverse scans)
|will be used.
|
| If none of these flags is specified, the system uses a default general-
| purpose caching scheme.

SQLite does not specify these flags.

Apparently, the default general-purpose caching scheme tries to detect
what access pattern the application uses.

The rows are stored in rowid order in the file, so the ascending scan
looks as if it might be a sequential scan.  This means that Windows will
prefetch following data, but it does not know if some data will be read
again later, so it will keep the data in the cache.

The descending scan looks like a reverse scan.  By that point, Windows
probably assumes that you are definitely using sequential scans, so it
thinks the data can be thrown out of the cache after you've read it.


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] This is driving me nuts

[x]

>While SQLite reads more and more rows from your database, Windows caches more 
>and more pages from the file, explaining the physical available memory 
>diminishes, which does not mean SQLite, nor your program 'use' it: the OS does.

Olivier, your post makes perfect sense but the above begs the question why does 
Windows not bother filling the cache when the descending steps are run first? 
The following will make it even more mysterious.

In the original app I was experimenting with mixing ascending and descending. 
Taking 8192 ascending steps then 8192 descending steps and so on (this was 
being done in a background thread) and the available RAM decreased as the 
number of steps increased. While investigating I found that almost all the RAM 
decreases happened during the 8192 descending steps (in contrast to the console 
app). Freaky or what?

Tom

P.S. The 60 sec sleep made no difference.

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

Re: [sqlite] This is driving me nuts

[x]

Clemens, when I change to ullAvailVirtual the figure stays pretty much the same 
throughout the programme as you suggested. I still don’t understand the 
ullAvailPhys numbers though. Why would window’s increase it’s cache size during 
step ascending then reduce it during step descending in such a consistent 
manner?



If I code it to step through desc before asc this is the output



Descending

13275

13276

13314

13314

13315

13315



Ascending

13315

11425

9525

7625

5722

3830



and again those results are repeatable. I don’t understand it but I’m convinced 
it’s not reacting to any other app.




From: sqlite-users  on behalf of 
Clemens Ladisch 
Sent: Thursday, May 31, 2018 11:50:19 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] This is driving me nuts

x wrote:
> I’ve written the console app below to illustrate. It prints out the amount of 
> RAM available every 20 million steps.

> GlobalMemoryStatusEx(&status);
> return status.ullAvailPhys / (1024 * 1024);

This is "physical memory currently available", which is affected by other 
things,
such as the Windows file cache.

Try ullAvailVirtual to see the how much your own process could allocate; this is
affected by both your code and by the SQLite library.  (This value is unlikely 
to
change by much because SQLite's default cache size is 2 MB.)

> It seems to lose RAM while step ascending and then give it back when step 
> descending.

RAM that Windows chooses to use for the file cache is not lost.


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] This is driving me nuts

[x]

> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?

After others pointed out threads weren’t that expensive I concluded there was a 
bug in my code. On checking though I couldn’t find anything wrong yet the 
programme was seemingly making 4 GB disappear as it ran. It gets even stranger 
though and I’ve written the console app below to illustrate. It prints out the 
amount of RAM available every 20 million steps. All other front end apps were 
closed during running although there were background tasks running. It was run 
in 64 bit mode although it also works for 32 bit.

#include 
#include 
#pragma hdrstop
#pragma argsused
#include 
#include 
#include 
#include 
#include "sqlite.h"

uint64_t FreeMBs()
{
MEMORYSTATUSEX status;
status.dwLength = sizeof(status);
GlobalMemoryStatusEx(&status);
return status.ullAvailPhys / (1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
const int Million=100;
const int Gap=20*Million;
sqlite3 *DB;
sqlite3_open("c:/SQLiteData/MyTemp.db",&DB);
sqlite3_stmt *asc,*desc;
sqlite3_prepare_v2(DB,"select RowID from big order by 
RowID",-1,&asc,NULL);
sqlite3_prepare_v2(DB,"select RowID from big order by RowID 
desc",-1,&desc,NULL);

std::cout << "Ascending" << std::endl;
for (int i=0; sqlite3_step(asc)==SQLITE_ROW; i++)
if (i%Gap==0) std::cout << FreeMBs() << std::endl;
std::cout << FreeMBs() << std::endl;

std::cout << std::endl << "Descending" << std::endl;
for (int i=0; sqlite3_step(desc)==SQLITE_ROW; i++)
if (i%Gap==0) std::cout << FreeMBs() << std::endl;
std::cout << FreeMBs() << std::endl;

sqlite3_finalize(asc);
sqlite3_finalize(desc);
sqlite3_close(DB);
getch();
return 0;
}

The big table can be emulated with this sql which creates a table with 100 
million records

sqlite3_exec(DB, "create table wee as "
"with cte(a, b, c) as "
"(values (1, '', '') "
"union all "
"select a+1, b, c from cte where a<1) "
"select * from cte;"
"create table big as select * from wee t1, wee t2;", 0, 0, 0);

OUTPUT

Ascending
13227
11355
9465
7582
5683
3801

Descending
3801
5868
7773
9683
11586
13473

On each run the numbers may be different but the overall results are the same. 
It seems to lose RAM while step ascending and then give it back when step 
descending. I can’t imagine it’s down to sqlite but can’t see anything wrong 
with my code. Can anyone put me out of my misery?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

> why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

Jim, it was only 3.2 GB and it was compiled as 64 bit just in case you’re 
thinking 32.


>Either this statement is wrong, or you've misattributed the 4 GB of memory.

Rowan, I’ve misattributed. There’s a bug in the thread. As the data is assigned 
to the vector in the thread the available memory keeps decreasing but I’ve yet 
to determine how this is happening.

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

Re: [sqlite] This is driving me nuts

[x]

I’ve just discovered the thread in the original app decreases the available 
memory by around 4 GB. Are they really that expensive? It has very little data 
of its own and just calls a function declared in the main thread.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

SetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be multiple of page size (typically 4096 bytes).
> https://msdn.microsoft.com/en-us/library/ms686234
>
> -
> int enableIncreaseWorkingSetPrivilege()
> {
>int rc;
>DWORD err;
>HANDLE hToken;
>TOKEN_PRIVILEGES privilege;
>memset(&privilege, 0, sizeof(privilege));
>privilege.PrivilegeCount = 1;
>privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
>rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
> &privilege.Privileges[0].Luid);
>if (!rc)
>return -1;
>rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES,
> &hToken);
>if (!rc)
>return -2;
>rc = AdjustTokenPrivileges(hToken, 0, &privilege, sizeof(privilege),
> NULL, NULL);
>err = GetLastError();
>CloseHandle(hToken);
>if (!rc || err)
>return -3;
>return 0;
> }
> -
>
>
>
>
> You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
> -DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
> memory.
> Locked memory won't get swapped so SQLite won't encounter page faults.
>
> Function configSqliteMemory listed below allocs few megabytes of
> memory, locks it and configures SQLite. It also increases working set
> size because maximum quantity of memory that process can lock is equal
> to minimum working set size (locked memory is included in the working
> set).
>
> -
> int configSqliteMemory()
> {
>int rc;
>void* memPtr;
>SIZE_T memSize = 64 * 1024 * 1024;
>SIZE_T wsMinSize, wsMaxSize;
>rc = GetProcessWorkingSetSize(GetCurrentProcess(), &wsMinSize,
> &wsMaxSize);
>if (!rc)
>return -1;
>wsMinSize += memSize;
>wsMaxSize += memSize;
>rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, 
> wsMaxSize);
>if (!rc)
>return -2;
>memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT,
> PAGE_READWRITE);
>if (!memPtr)
>return -3;
>rc = VirtualLock(memPtr, memSize);
>if (!rc)
>{
>VirtualFree(memPtr, 0, MEM_RELEASE);
>return -4;
>}
>rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
>if (rc != SQLITE_OK)
>{
>VirtualFree(memPtr, 0, MEM_RELEASE);
>return -5;
>}
>return 0;
> }
> -
>
>
>
>
> 2018-05-27 20:03 GMT+02:00, x :
>> Starting to mess about with windows handles and pages I’ve never heard of
>> is
>> beyond my pain threshold Abrozy. Thanks anyway.
>>
>>
>>
>> 
>> From: sqlite-users  on
>> behalf
>> of Abroży Nieprzełoży 
>> Sent: Sunday, May 27, 2018 5:23:12 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] This is driving me nuts
>>
>> I think you can experiment with changing the the working set size
>> limits and see what the effect will be.
>> https://msdn.microsoft.com/en-us/library/cc441804
>>
>> 2018-05-27 17:09 GMT+02:00, curmudgeon :
>>> It seems the array was being optimised away. I had to initialise every
>>> value
>>> to get the OS to claim the RAM. Once I did that the timings for the
>>> array
>>> were on a par with the vector with the second pass being slower than the
>>> first.
>>>
>>> While that clears up that part of the mystery I'm no closer to a
>>> solution.
>>> Going back to the latest set of results why is the assignments in the
>>> second
>>> pass taking so much longer when there's still 5+ GB of memory free?
>>>
>>>
>>>
>>> --
>>> 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
>>>
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Starting to mess about with windows handles and pages I’ve never heard of is 
beyond my pain threshold Abrozy. Thanks anyway.




From: sqlite-users  on behalf of 
Abroży Nieprzełoży 
Sent: Sunday, May 27, 2018 5:23:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon :
> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> 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
>
___
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] This is driving me nuts

[x]

Strange. I repeated the test with v an int64 array (see below) however the 
FreeMB() doesn’t change. Both Memi’s return pretty much the value of Mem 
???

for (int i=0; i<2; i++)
{
v=new int64_t[Size[i]];
int64_t Memi=FreeMBs();

clock_t Start=clock();
for (int i=0; ihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

I’ve changed the app to populate v with the query result and noted the free ram 
before and after each resize. I’m hoping that addresses some of the concerns re 
compiler optimisations even if it doesn’t supply any real answers? Results were 
similar to before.

#include 
#include 
#pragma hdrstop
#pragma argsused
#include 
#include 
#include 
#include 
#include 
#include "sqlite.h"

std::vector v;
const int Size[]={11200,10}; // 112 million, 1 billion

uint64_t FreeMBs()
{
MEMORYSTATUSEX status;
status.dwLength = sizeof(status);
GlobalMemoryStatusEx(&status);
return status.ullAvailPhys / (1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
sqlite3 *DB;
sqlite3_open("c:/SQLiteData/MyTemp.db",&DB);
sqlite3_stmt *stmt;
sqlite3_prepare_v2(DB,"select RowID from Big order by 
RowID",-1,&stmt,NULL);

std::cout << FreeMBs() << " MB" << std::endl;

for (int i=0; i<2; i++)
{
v.resize(Size[i]);
v.shrink_to_fit();
int64_t Memi=FreeMBs();

clock_t Start=clock();
for (int i=0; ihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Thanks for the input gents. I’m going to have to look into the memory stuff as 
it’s something I know little about and it’s near bedtime in Scotland.



Warren, the actual programme does use the vector (it’s used to store the 
RowIDs). The console app mark 1 also did this (results were the same) but I 
left it out when I noticed it made no difference to the timings to  add to the 
lunacy.



Your point about the swapping was something I thought about but can’t 
understand why this apparently doesn’t happen when I use an array created with 
new and why no slowdown occurs despite the fact it’s grabbing the same memory. 
Maybe it’s the array that’s being optimised away but, like I said, the results 
were the same when I was assigning the results of the sqlite3_steps to the 
vector.



The actual programme only does the resize once, setting the size to the largest 
one it’s likely to require.



If you look back at my original post you’ll see that on one test I did remove 
all the sqlite code and replace it with code assigning rand() numbers to the 
array. No difference in the timings of the two passes were noted in this case.



Will look in tomorrow. Thanks again.






From: sqlite-users  on behalf of 
Warren Young 
Sent: Saturday, May 26, 2018 8:54:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

On May 26, 2018, at 9:43 AM, x  wrote:
>
> I was sizing the vector beforehand and then timing how long it took to store 
> the RowIDs returned by an sqlite query.

You’re not actually modifying the vector, only allocating it and then not using 
it. A sufficiently smart optimizer might optimize that vector away.

> the mistakenly sized vector only used 8 GB?

How much memory is actually available to your program when it runs, and how 
does that change from run to run?

You say the machine has 16 GB of RAM, but Windows 10 will be using a huge chunk 
of that, and it won’t give all of what it isn’t immediately using to a single 
user program.

It is quite possible you’ve driven your computer into swapping with this test, 
if only to swap out other programs’ pages to disk so that Windows *can* give 
your single program half of the system RAM.

Never forget that your program isn’t the only one running on the machine.  Pull 
up process monitor and put it into the advanced view; you’ll find dozens of 
other programs trying to run at the same time, if only the OS and its core 
services.

> sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL);

How representative is this of the real code you intend to write?  If you are 
indeed going to be processing billions of rows in your real DB, you will 
typically either want to be processing the data as you read it in from the DB, 
not re-storing it in a std::vector unprocessed, or you will want to put WHERE 
clauses and such into the query to limit the amount of data you pull to only 
that which you actually require.

If you’re scanning the whole table on every program run, SQL — any dialect — 
isn’t buying you very much.  SQLite is just acting as a data file format for 
you in that case, offering little more above that than a flat data file.  
SQLite is giving you a lot of power here; can you use more of it to get some of 
the benefit of the complexity you’ve bought?

>v.resize(Size[i]);

As another reply said, this doesn’t necessarily do what you expect it will.  
std::vector is allowed to do several clever things to avoid unnecessary 
reallocations, and you’re leaving the door open to them here.

If you want to ensure that the vector is completely reallocated every time 
through, declare it inside the loop to prevent std::vector from reusing 
previously allocated space.

> What's it got to do with sqlite, if anything? Why was sqlite_step slowed down?

Why do you believe that is the correct diagnosis?  Have you got a fine-grained 
measurement to prove it, such as from a profiler?

Or, have you repeated the measurement in two parts to separate the SQLite 
operations from the std::vector operations?

You’re on the path to Science here, but you’re missing a key tenet: test only 
one thing at a time.  This program of yours tests at least three different 
things — OS memory manager, std::vector memory allocator strategy, and SQLite — 
at the same time without providing a provision to isolate the confounds.
___
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] This is driving me nuts

[x]

I also added v.shrink_to_fit() after the resize and tried again with Size[0] 
and Size[1] swapped. The time difference reappeared with the second pass 4+ 
secs faster than the first.




From: sqlite-users  on behalf of 
Abroży Nieprzełoży 
Sent: Saturday, May 26, 2018 5:39:03 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :
> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include 
> #include 
> #pragma hdrstop
> #pragma argsused
> #include 
> #include 
> #include 
> #include 
> #include 
> #include "sqlite.h"
>
> std::vector v;
> const int Size[]={11200,10}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
> sqlite3 *DB;
> sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
> sqlite3_stmt *stmt;
> sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,&stmt,NULL);
>
> for (int i=0; i<2; i++)
> {
> v.resize(Size[i]);
>
> // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
> // changes is the size() of v and v isn't even used in the
> timed code below.
>
> clock_t Start=clock();
>
> while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
> // Above just steps through stmt (111,724,900 steps to be
> exact).
>
> std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
> sqlite3_reset(stmt);
> }
> sqlite3_finalize(stmt);
> sqlite3_close(DB);
> getch();
> return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.61024.172
> 20.34424.594
> 19.95324.375
> 19.89123.594
> 19.93825.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+11200 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.59425.672
> 20.56324.406
> 21.21923.843
> 20.48425.343
> 20.56225.17

Re: [sqlite] This is driving me nuts

[x]

Hi Abrozy.



I inserted the line

int64_t Mem=sqlite3_memory_highwater(0);

immediately after the while loop and added Mem to the cout. The values returned 
were

2234704 (I==0)

2234768 (I==1).



Tom




From: sqlite-users  on behalf of 
Abroży Nieprzełoży 
Sent: Saturday, May 26, 2018 5:39:03 PM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :
> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include 
> #include 
> #pragma hdrstop
> #pragma argsused
> #include 
> #include 
> #include 
> #include 
> #include 
> #include "sqlite.h"
>
> std::vector v;
> const int Size[]={11200,10}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
> sqlite3 *DB;
> sqlite3_open("c:/SQLiteData/Formbook.db",&DB);
> sqlite3_stmt *stmt;
> sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,&stmt,NULL);
>
> for (int i=0; i<2; i++)
> {
> v.resize(Size[i]);
>
> // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
> // changes is the size() of v and v isn't even used in the
> timed code below.
>
> clock_t Start=clock();
>
> while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
> // Above just steps through stmt (111,724,900 steps to be
> exact).
>
> std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
> sqlite3_reset(stmt);
> }
> sqlite3_finalize(stmt);
> sqlite3_close(DB);
> getch();
> return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.61024.172
> 20.34424.594
> 19.95324.375
> 19.89123.594
> 19.93825.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+11200 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.59425.672
> 20.56324.406
> 21.21923.843
> 20.484