Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Hick Gunter]

Weren't we all...

When I requested working from home office on march 10th, the company went from 
"No, we can't do that until it is mandated by the law" to "Come to the office 
only if you can't work from home" in a matter of days. With "Please verify if 
you can access the company VPN from home" in between.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:26
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]

OK i must have must the posts from the 12th of March till the end of that week 
😉, being busy with other things.

On 24-3-2020 09:19, Hick Gunter wrote:
> See announcement on the mailing list dated march 12th
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Richard Hipp
> Gesendet: Donnerstag, 12. März 2020 21:18
> An: General Discussion of SQLite Database
> 
> Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this
> mailing list is deprecated
>
> I have set up an on-line forum as a replacement for this mailing list:
>
>  https://sqlite.org/forum
>  https://www.sqlite.org/forum/forumpost/a6a27d79ac
>
> Please consider subscribing to the new Forum.  The intent is that the forum 
> will eventually replace this mailing list.
>
> The Forum is powered by Fossil.  It has been in active use in the Fossil 
> community for a couple of years, and has worked well.  See the second link 
> above for more information.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Luuk
> Gesendet: Dienstag, 24. März 2020 09:13
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re:
> [EXTERNAL] No such column error]
>
>
> On 24-3-2020 09:04, Luuk wrote:
>> "The mailing list is deprecated. You need to go to
>> https://sqlite.org/forum/ for the sqlite forum." 
>>
>> Can anyone give the source of this?
> No, i do NOT mean the source of the forum, but the source for "The list is 
> deprecated"
>
>
> ___
> 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


___
 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

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Hick Gunter]

See announcement on the mailing list dated march 12th

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 12. März 2020 21:18
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this mailing list 
is deprecated

I have set up an on-line forum as a replacement for this mailing list:

https://sqlite.org/forum
https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the forum 
will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the Fossil 
community for a couple of years, and has worked well.  See the second link 
above for more information.

--
D. Richard Hipp
d...@sqlite.org
___


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:13
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]


On 24-3-2020 09:04, Luuk wrote:
> "The mailing list is deprecated. You need to go to
> https://sqlite.org/forum/ for the sqlite forum." 
>
> Can anyone give the source of this?

No, i do NOT mean the source of the forum, but the source for "The list is 
deprecated"


___
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

Re: [sqlite] [EXTERNAL] No such column error

[Hick Gunter]

The mailing list is deprecated. You need to go to https://sqlite.org/forum/ for 
the sqlite forum.

Can you replicate the problem while using the sqlite shell? Are you checking 
column names returned from the second statement? Note that a.BIRTH.YYY from 
your example looks a bit weird for a qualified column name.

My guess is that you are either not accessing the database file you want (rumor 
has it that windows has some strange goings on under the hood for certain file 
locations) or not handling transactions properly.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Montag, 23. März 2020 13:30
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] No such column error

I have been developing a c++ program in windows. In this program, in a loop, I 
open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS a;"

I randomly encounter with this error while executing sqlite3_prepare_v2 method. 
When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value was 
SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Hick Gunter]

Exactly what I gained from the EXPLAIN output.

The SQL "compiler" is extracting the constant expression ABS(...) and 
evaluating it in the program prolog (where schema is checked and locks taken). 
See instructions 11 and 12

asql> explain select coalesce((SELECT 'hello'),ABS(-9223372036854775808));
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 11000  Start at 11
1 Once   0 6 000
2 Null   0 2 200  r[2..2]=NULL; Init 
subquery result
3 Integer1 3 000  r[3]=1; LIMIT counter
4 String80 2 0 hello  00  r[2]='hello'
5 DecrJumpZero   3 6 000  if (--r[3])==0 goto 6
6 SCopy  2 1 000  r[1]=r[2]
7 NotNull1 9 000  if r[1]!=NULL goto 9
8 SCopy  4 1 000  r[1]=r[4]
9 ResultRow  1 1 000  output=r[1]
10Halt   0 0 000
11Int64  0 5 0 -9223372036854775808  00  
r[5]=-9223372036854775808
12Function0  1 5 4 abs(1) 01  r[4]=func(r[5])
13Goto   0 1 000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jay Kreibich
Gesendet: Mittwoch, 11. März 2020 20:53
An: SQLite mailing list 
Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes


> On Mar 11, 2020, at 2:16 PM, Justin Ng  wrote:
>
> They generally do short-circuit but there are edge cases where they don't. It 
> isn't entirely intuitive to me what the conditions are, though.
>


"ABS(-9223372036854775808)" is a constant expression, and as such, it makes 
sense that it is evaluate during the parse/prepare phase of the processing, not 
the execution.  There are similar problems in more traditional languages 
(especially scripting languages) that attempt to optimize out or pre-compute 
constant expressions.

If that’s the case, then the issue is not so much that the COALESCE() is 
failing to short-circuit, but rather than the SQL statement failing to 
“compiling” an invalid statement.

If you’re doing this in code as separate prepare/step/finalize, it would be 
interesting to see where it fails.  My guess is prepare, not step.

  -j

___
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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Hick Gunter]

While ORACLE does state that COALESCE will short circuit, SQLite does not.

May I suggest implementing your own user defined function to do this instead.

void THROW_IF_NULL(
sqlite3_context *ctx,
int argc,
sqlite3_value**argv) {

int ii;

for( ii == 0; ii < argc; ii++)
{
if (sqlite3_value_type(argv[ii]) != SQLITE_NULL)
{
sqlite3_result_value(ctx,argv[ii]));
return;
}
}

sqlite3_result_error(ctx,"NULL or empty", SQLITE_TRANSIENT);
}

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Justin Ng
Gesendet: Mittwoch, 11. März 2020 16:24
An: sqlite-users@mailinglists.sqlite.org
Cc: 
vi1p195mb06545f03a24d50dd2785ac2bde...@vi1p195mb0654.eurp195.prod.outlook.com
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes

> Why do you think that that it should not evaluate ABS?  It is there and you 
> asked for it.  I believe it's a good idea to say, "hey, the amount you placed 
> here is out of boundary, think about what you are doing here." IMO, of 
> course. Thanks.
>
> josé

Sometimes, when querying data, rather than letting NULLs propagate, it might be 
better to throw an error if NULLs are not expected from an expression.

The presence of NULLs might indicate an error in logic, data, or both.

So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to 
sanity-check queries.

Something like THROW_IF_NULL(x)
___
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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Hick Gunter]

It is possible to infer, from the EXPLAIN output, that the SQLite program 
generator attempts to isolate constant expressions and evaluates them first, 
before it enters the COALESCE loop.

From my experience in reading SQL Programs, the general structure is

GOTO INIT
START:
- load constant values
- load paramters
- open tables
LOOP:
- create next result row
IF end-of-results THEN EXIT ELSE GOTO LOOP
INIT:
- check schema version
- take required locks
- evaluate constant expressions
GOTO START

Which is quite nifty, as it allows stuff unknown until the end of parsing to be 
appended to the program

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 11. März 2020 13:19
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes

On Wed, Mar 11, 2020 at 12:03 PM Justin Ng  wrote:
> -- Query 3
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> ABS(-9223372036854775808)
>   );
> [...]. It should short-circuit and not evaluate ABS()

Interestingly, found this as well:
https://github.com/AnyhowStep/tsql/issues/233
SQLite COALESCE() does not short-circuit sometimes #233 
___
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

Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

[Hick Gunter]

Addendum: Note that CREATE TABLE has an optional schema name which must be one 
of

- main (which is the default)
- temp (which is the same as specifying TEMP or TEMPORARY between CREATE and 
TABLE)
- the name of an attached database

See https://sqlite.org/lang_createtable.html


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Donnerstag, 5. März 2020 08:48
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with 
attached DBs

I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 
(in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only 
table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in 
step 3

see https://sqlite.org/lang_naming.html

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached 
DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat <https://www.sqlite.org/lang_naming.html

I think I would also appreciate a pragma that requires full schema paths for a) 
modification statements and/or b) all statements.

--
Mark Lawrence
___
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


___
 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

Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

[Hick Gunter]

I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 
(in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only 
table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in 
step 3

see https://sqlite.org/lang_naming.html

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached 
DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat 

Re: [sqlite] [EXTERNAL] Sql update script. check for existing rows before inserting...

[Hick Gunter]

SQLite is not a procedural language. IF is not a programming construct, it is 
part of an expression.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Andy KU7T
Gesendet: Montag, 24. Februar 2020 05:17
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Sql update script. check for existing rows before 
inserting...

Hi,

I would like to write a script that checks whether certain records already 
exist, and if not, insert them. If they do exist, it should be a no op.

I am trying this:

IF (SELECT COUNT(*) FROM [Antennas]) = 0 BEGIN
  /* Table data [Antennas] Record count: 16 */
  INSERT OR REPLACE INTO [Antennas]([Code], [Antenna], [Bands], [Ports], 
[Offset], [Bidirectional]) VALUES(0, '', '', null, null, '0');
  -- a few of those...
END;

However, I am getting a syntax error near IF. Any ideas where my error is?

Thanks
Andy

Sent from Mail for Windows 10

___
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

Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

[Hick Gunter]

Round(1299.6) returns the floating point number 1300.0,
passing 1300.0 to the rtrim function converts it tot he string '1300.0'
removing all '.' and '0' characters from '1300.0' yields 13
This is no suprise

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Donnerstag, 20. Februar 2020 17:03
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] rtrim and round functions unexpected result


Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

josé
___
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

Re: [sqlite] [EXTERNAL] question about INTEGER PRIMARY KEY AUTOINCREMENT

[Hick Gunter]

The next value for an INTEGER PRIMARY KEY AUTOINCREMENT does not depend on the 
current contents of the table, only its history. While ROWIDs are monotnically 
increasing, there may be gaps in the sequence, caused by rows that failed to 
insert due to constraint violations. However, ROWIDs that get rolled back will 
be reused.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ethan he
Gesendet: Donnerstag, 20. Februar 2020 05:24
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

Hi,

There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY AUTOINCREMENT), 
Is that possible to delete the data but still keep the  MeslocallD consistence?

Thanks for your help


___
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

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

[Hick Gunter]

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von Jens Alfke
>> On Feb 12, 2020, at 5:30 AM, Hick Gunter  wrote:
>>
>> This is documented here https://sqlite.org/partialindex.html
>> <https://sqlite.org/partialindex.html> and here
>> https://sqlite.org/queryplanner.html
>> <https://sqlite.org/queryplanner.html>
>>
>> Specifically, SQLIte does not prove theorems in first-order logic.
>
>Thanks — I hadn't seen the section "Queries Using Partial Indexes" before, and 
>it gives more detail about how the matching is done. >However, it seems that 
>my query does match one of the rules:
>
>   "If W [the query's WHERE clause] is AND-connected terms
>and X [the index's WHERE clause]  is OR-connected terms
>and if any term of W appears as a term of X,
>then the partial index is usable."
>
>Here W = (expr1 > val1 OR expr2 > val2) AND expr3  and X = expr3, which is a 
>degenerate case of one OR-connected term.
>
>So I'm not sure why the indexes aren't useable, unless there are limitations 
>of the actual rule that aren't described in that English text.

My guess ist hat SQLite is looking at the "expr1>val1" and "expr2>val2" terms 
respectively, which don't have a reference to expr3, and thus concludes that 
the indices are not usable.

However, "expr1>val1 AND expr3" clearly matches the rule and thus should use 
the index.


___
 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

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

[Hick Gunter]

This is documented here https://sqlite.org/partialindex.html and here 
https://sqlite.org/queryplanner.html

Specifically, SQLIte does not prove theorems in first-order logic.

To have a chance of using the partial indices, you would need to have your 
query translator formulate (expr1>val1 AND expr 3) OR (expr2>val2 AND expr3)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jens Alfke
Gesendet: Mittwoch, 12. Februar 2020 00:09
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Optimizer limitation with partial indexes

I'm running into a problem with partial indexes; apparently the query optimizer 
isn't smart enough.

I currently have indexes of the form
CREATE INDEX Index1 ON Table (expr1)
CREATE INDEX Index2 ON Table (expr2)
where expr1 and expr2 are expressions involving table columns.

The problematic queries are of the form
SELECT * FROM Table WHERE (expr1 > val1 OR expr2 > val2) AND expr3 Such 
a query correctly uses the above indexes — the EXPLAIN command shows it's using 
a multi-index OR combining two 'search table using index' loops.

If, however, I try to make the indexes smaller by changing them to
CREATE INDEX Index1 ON Table (expr1) WHERE expr3
CREATE INDEX Index2 ON Table (expr2) WHERE expr3 the query stops using 
the indexes effectively. It's reduced to doing 'scan table using index', i.e. 
O(n).

It looks like what happens is that the optimizer doesn't associate the "AND 
expr3" clause with the "expr1" and "expr2" comparisons. In other words, it 
doesn't realize that (A OR B) AND C is equivalent to (A AND C) OR (B AND C).

If this were a hand-written SELECT statement it would be easy to work around 
this, but it's not. It's the output of a query translator that generates SQL, 
and it can generate arbitrary queries with arbitrary combinations of operators.

I know the SQLite optimizer isn't a Mathematica-grade symbolic logic analyzer! 
But I'm wondering if in this case there's a way around this limitation?

—Jens
___
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

Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

[Hick Gunter]

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von Dominique Devienne
>
>On Thu, Feb 6, 2020 at 9:32 AM Hick Gunter  wrote:
>> >Of course, it may be that the writer of the VTable should know what they 
>> >are doing and generate a VTable definition that is consistent with how 
>> >their cursor methods return data, however ... this will omit the 
>> >OP_Affinity if no >>column type was specified when the VTable was defined 
>> >and most of the VTable declarations in the existing code that I looked at 
>> >do not specify column affinities in the declarations.
>>
>> Very nice. but detrimental for our use case. Please refrain from adding this 
>> tot he distribution by default.
>> We are almost exclusively using virtual tables to allow queries against our 
>> internal data sources, which are C language structs and thus strictly typed. 
>> The column affinities provided by the VTab implementations are used for 
>> documentation purposes and the xColumn methods always return the same type 
>> (calling the "wrong" sqlite3_result function is considered a programming 
>> error). Coercing the returned value to the same type would be just a waste 
>> of memory and CPU cycles.
>
>+1. I fear what it would do to our app, also making extensive use of vtables.
>Like Gunter mentions, the type is there more for documentation, I'm unsure 
>"what havoc this could wreak".
>
>Note that our vtables are all read-only, if that matters here. It's unclear to 
>me if the above applies to writes only, or also applies to reads. If to writes 
>only, then I don't care much at the moment, although I might in the future, 
>and would likely prefer seeing the raw value in my code, than the result of 
>affinity-coercion. --DD

The patch affects the value returned from the VTable implementation if 
affinities are provided by the create table statement from the xCreate method


___
 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

Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

[Hick Gunter]

Unfortunately no, since the code is all proprietary and includes extensive 
generated code.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nelson, Erik - 2
Gesendet: Donnerstag, 6. Februar 2020 14:57
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and 
Change Request

Hick Gunter wrote on  Thursday, February 6, 2020 3:32 AM

>We are almost exclusively using virtual tables to allow queries against
>our internal data sources, which are C language structs and thus
>strictly typed. The column affinities provided by the VTab
>implementations are used for documentation purposes and the xColumn methods 
>always return the same type (calling the "wrong" sqlite3_result function is 
>considered a programming error).
>Coercing the returned value to the same type would be just a waste of memory 
>and CPU cycles.

That sounds really cool- is it something that could possibly be added  to the 
list of vtables? (https://www.sqlite.org/vtablist.html)

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
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

Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

[Hick Gunter]

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von Keith Medcalf
>Betreff: [EXTERNAL] Re: [sqlite] Patch: VTable Column Affinity Question and 
>Change Request
>
>
>Patch to Fix Column Affinity not applied to Virtual Columns.
>
>In expr.c function sqlite3ExprCodeGetColumnOfTable At or about line 3555 where 
>the OP_Column or OP_VColumn opcode is added to the VDBE program, and the 
>default code is generated, make this:
>
>sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut);
>sqlite3ColumnDefault(v, pTab, iCol, regOut);
>
>look like this:
>
>sqlite3VdbeAddOp3(v, op, iTabCur, x, regOut);
>if ( IsVirtual(pTab) && pTab->aCol[iCol].affinity > SQLITE_AFF_BLOB)
>  sqlite3VdbeAddOp4(v, OP_Affinity, regOut, 1, 0, 
> &(pTab->aCol[iCol].affinity), 1);
>sqlite3ColumnDefault(v, pTab, iCol, regOut);
>
>Of course, it may be that the writer of the VTable should know what they are 
>doing and generate a VTable definition that is consistent with how their 
>cursor methods return data, however ... this will omit the OP_Affinity if no 
>>column type was specified when the VTable was defined and most of the VTable 
>declarations in the existing code that I looked at do not specify column 
>affinities in the declarations.

Very nice. but detrimental for our use case. Please refrain from adding this 
tot he distribution by default.

We are almost exclusively using virtual tables to allow queries against our 
internal data sources, which are C language structs and thus strictly typed. 
The column affinities provided by the VTab implementations are used for 
documentation purposes and the xColumn methods always return the same type 
(calling the "wrong" sqlite3_result function is considered a programming 
error). Coercing the returned value to the same type would be just a waste of 
memory and CPU cycles.

We do have our own implementation of csv for export/import purposes (which 
predates the c orresponding SQLite features) that also allow changing the 
schema by providing default values for new columns. But this is only used in 
the context of INSERT INTO ... SELECT which applies the affinity of the target 
table.


___
 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

Re: [sqlite] [EXTERNAL] Unsubscribe from Sqlite users list

[Hick Gunter]

Visit the link given at the bottom of every message, including this one

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ainhoa B
Gesendet: Mittwoch, 5. Februar 2020 15:11
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Unsubscribe from Sqlite users list

Hi,

I would like to unsubscribe from this list and stop receiving messages, but I 
do not know how to do it.

Can someone help me?

Thanks!
___
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

Re: [sqlite] [EXTERNAL] Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Hick Gunter]

WHERE x IN carray($PTR, $DIM)

With $PTR being the address of the array and $DIM ist cardinality. Should be 
tons faster than parsing and binding a gazillion parameters.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Deon Brewis
Gesendet: Dienstag, 4. Februar 2020 20:14
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Please increase the default for 
SQLITE_MAX_VARIABLE_NUMBER

WHERE x IN (?1,?2,?3,?4...,?1000 )

And the IN clause is filled by a list or array that's held inside the calling 
application memory rather than in SQLITE.

The alternate to this is to create a virtual table wrapper over the internal 
datasets of the app. Which is of course better, but harder. (We need an STL for 
SQLite. SqliteTL?).

PS: Doesn't SQLITE internally order an IN list and do a join across it? It 
seems to perform better than I would expect from a flat array.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
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


___
 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

Re: [sqlite] [EXTERNAL] Re: Default isolation_level for sqlite3.connect?

[Hick Gunter]

"Autocommit" means that each SQL Statement executes in it's own transaction. 
Just as if you were to execute "begin; ; commit;" each time.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Freitag, 31. Januar 2020 02:14
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Default isolation_level for sqlite3.connect?

I still have a hard time to understand what the difference is according to the 
python manual. It keeps saying see somewhere else in the python manual. But I 
don't see where it explains the differences between the differences 
comprehensively and clearly.

https://www.sqlite.org/lang_transaction.html

On the above page, I only see

- BEGIN
- BEGIN DEFERRED
- BEGIN IMMEDIATE
- BEGIN EXCLUSIVE

, where BEGIN is just BEGIN DEFERRED.

But what is isolation_level = None?

https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions

"The underlying sqlite3 library operates in autocommit mode by default, but the 
Python sqlite3 module by default does not."

According to the above, since isolation_level="" is the default in python, so 
it is not autocommit and it is BEGIN.

But what is autocommit mode? The following doesn't provide a definition. Is 
there a definition on sqlite.com website?

https://www.sqlite.org/c3ref/get_autocommit.html

> The default is an empty string (ie, '').  It can take the value None,
> '', 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None)
> is appended to the BEGIN when a magical transaction is started, and if
> None, then you are indicating that you will be using manual
> transaction control
>
> isolation_level   command
> None  None
> '' (default)  BEGIN
> 'DEFERRED'BEGIN DEFERRED
> 'IMMEDIATE'   BEGIN IMMEDIATE
> 'EXCLUSIVE'   BEGIN EXCLUSIVE
>
> You can set the isolation_level in the connect call (the default is
> '') or read/change it with the .isolation_level property of a connection 
> object.

--
Regards,
Peng
___
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

Re: [sqlite] [EXTERNAL] Either a bug or I don't understand SQL update

[Hick Gunter]

You have fallen into the double quote trap.

SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit 
STRING CONSTANTS.

When asking for "M" or "G", you get the contents of the column named m and g 
respectively (column names are case insensitive).

When asking for "P" or "R", there is no column with that name, so SQLite 
pretends that you meant to provide a string constant.

BTW: You have columns with NUMERIC affinity obviously representing amounts, 
which will get stored as REAL numbers. Don't do that if you expect to do 
anything financially relevant. You will incur rounding errors. Instead, store 
integer values of cents.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul Ausbeck
Gesendet: Donnerstag, 30. Januar 2020 00:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Either a bug or I don't understand SQL update

I've been using sqlite for some time but haven't used SQL update until 
recently. On my first real use, I've encountered a problem that I can't 
understand at all.

To pare this down, I have two tables, citytax and taxitems2, and I've attached 
a database with just these two tables, total size ~12Kbytes.

I perform the following update using the sqlite3 command line interface:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and taxitems2.taxrate = "M");

The result is that the salesm  column is set to NULL for all citytax rows. 
However, I would expect that rows 9 and 22 would be non-NULL. I get the the 
same all-NULL result for a taxrate of "G" as well, though I would expect that 
rows 14, 19, and 58 would be non-NULL.

The strangest part is that if I specify taxrates of "P", "R", or "", I get the 
result that I expect. I've included a listing of the taxitems2 table below. As 
one can see, there are rows where the second column, taxrate, is all of "", 
"P", "R", "G", and "M". The last column is the amount column, non-null for all 
rows. I must admit that I don't understand what is going on here.

Perhaps even stranger is if I extract the select from the update and give it an 
appropriate row number, I get the expected non-NULL result:

sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and 
taxitems2.taxrate = "M";
1176.72

I've included the contents of the taxitems2 table as well as the schema for 
tables taxitems2 and citytax below. One the one hand, this seems almost 
certainly to be a bug, but on the other, it is so basic, that I can't believe 
that I'm the first to encounter it.

Any assistance that anyone can provide would of course be much appreciated. I'm 
using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both 
environments behave the same in this regard.

As I was writing I had yet another test idea:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* 
taxrate = "M" */

and

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* 
taxrate = "G" */

Both of these updates perform as expected.

Regards,

Paul Ausbeck

sqlite> select * from taxitems2;
WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5
SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0
USX,P,58,14,1,0,0,0,0,0,0,7104.0
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
USX,P,58,14,1,0,0,0,0,0,0,5446.4
USX,P,58,14,1,0,0,0,0,0,0,1657.6
AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3
PEM,G,58,10,2,0,0,0.75,2,1.5,4,8
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
HVY,R,58,14,1,0,0,0,0,0,0,72.5
HVY,R,58,14,1,0,0,0,0,0,0,176.4
VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,168
CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2
CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,65
WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9
WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4
WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72
WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3
ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394
DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4
DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8
DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7

sqlite> .schema taxitems2
CREATE TABLE taxitems2(
   code TEXT,
   taxrate TEXT,
   citynum INT,
   countynum INT,
   statenum INT,
   citym NUM,
   cityg NUM,
   countym NU

Re: [sqlite] [EXTERNAL] Is mutliple-execute-one-commit slower than multiple single-execute-single-commit?

[Hick Gunter]

If you could provide more information then maybe someone can suggest a reason 
or even a solution for the effect you are seeing. Some of the following may be 
helpful.

What schema are you using?
Which journal mode is your database running in?
What kind of statements are executed?
How are you controlling transactions?
How are you measuring speed?
Can you replicate the problem by running the statements in the sqlite shell?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Mittwoch, 29. Januar 2020 07:42
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Is mutliple-execute-one-commit slower than 
multiple single-execute-single-commit?

Hi,

I have two python programs using sqlite3. They function the same, except the 
following.

In the first, execute() is called in batches and then commit() is called 
following them. In the second, commit() is called after each execute(). It 
seems that the second case is faster (I can not separate my code in a 
self-contained test case to show here).

This is counterintuitive. I thought the first should be faster.

Is it expected that the 2nd case should be slightly faster?

--
Regards,
Peng
___
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

Re: [sqlite] [EXTERNAL] Re: Row length in SQLITE

[Hick Gunter]

You are missing

maxsize += _varIntSize_(maxsize)

fort he size varint at the begin oft he header just before the return

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Keith Medcalf
Gesendet: Montag, 27. Januar 2020 12:43
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Row length in SQLITE


Here is a wee bit of C code that you can compile as a plugin that will give you 
the row size (well, it may be bigger than the actual record size by a few bytes 
but it is pretty close) ...

works properly for utf-16 encoded databases as well.

-//- sqlsize.c -//-
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

#ifndef SQLITE_PRIVATE
#define SQLITE_PRIVATE static
#endif

static inline sqlite_int64 _varIntSize_(sqlite_int64 v) {
sqlite_int64 uu;

if (v<0)
uu = ~v;
else
uu = v;
if (uu <= 127 )
return 1;
else if (uu <= 32767)
return 2;
else if (uu <= 8388607)
return 3;
else if (uu <= 2147483647)
return 4;
else if (uu <= 140737488355327LL)
return 6;
else return 8;
}

SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv) {
sqlite_int64 maxsize = 0;
sqlite_int64 sz;
int i;

for (i=0; isqlite3 \data\apps\splunk\splunk.db
SQLite version 3.31.0 2020-01-27 11:17:22 Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
ApplianceID integer not null references Appliances(ApplianceID) on 
delete cascade,
SrcIntIDinteger not null references Interfaces(InterfaceID) on 
delete cascade,
DstIntIDinteger not null references Interfaces(InterfaceID) on 
delete cascade,
Transport   text not null collate nocase,
SrcHostID   integer not null references Hosts(HostID) on delete cascade,
SrcPort integer not null,
DstHostID   integer not null references Hosts(HostID) on delete cascade,
DstPort integer not null,
Action  integer not null,
Count   integer not null,
FileID  integer not null references Files(FileID) on delete cascade
);
sqlite> select _rowid_, recsize(ApplianceID, SrcIntID, DstIntID,
sqlite> Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action,
sqlite> Count, FileID) from details limit 10;
1|27
2|27
3|27
4|27
5|27
6|28
7|27
8|27
9|28
10|27
sqlite> select max(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
31
sqlite> select avg(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
29.4473800800817
sqlite> select min(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
26
sqlite> select skew(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
-0.378384651017371
sqlite> select kurt(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
0.130516904446944

--
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 Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list 
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This
>doesn't need to be performant - it's for usage analysis during development 
>time.
>
>- Deon
>
>-Original Message-
>From: sqlite-users  On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list 
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis  wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
>   SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere.  It stores the entries in a tree and it would
>have to manually count the leaves of the tree.
>
>
>
>Or do you mean the count of columns in a table / index ?
>
>SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned.  Or in C
>
>sqlite3_column_count()
>___
>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://mail

Re: [sqlite] [EXTERNAL] Re: Row length in SQLITE

[Hick Gunter]

As previously mentioned, SQLite uses a compressed format to store rows. You 
would have to reverse engineer at least the calculation

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Deon Brewis
Gesendet: Samstag, 25. Januar 2020 05:14
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Row length in SQLITE

No I mean e.g.

row 1 = 500 bytes,
row 2 = 600 bytes
row 3 = 80 bytes
row 4 = 300 bytes

etc.

Like the info that DBSTAT gives, but per row, not per page. This doesn't need 
to be performant - it's for usage analysis during development time.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, January 22, 2020 5:24 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Row length in SQLITE

On 22 Jan 2020, at 11:44pm, Deon Brewis  wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number 
handy anywhere.  It stores the entries in a tree and it would have to manually 
count the leaves of the tree.



Or do you mean the count of columns in a table / index ?

SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

sqlite3_column_count()
___
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


___
 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

Re: [sqlite] [EXTERNAL] Row length in SQLITE

[Hick Gunter]

SQLite uses a compressed format to store records (be it rows of a table or 
entries in an index), so the length of a specific record depends on its 
contents.

See https://sqlite.org/fileformat.html

Storing a row of (NULL, NULL, NULL, NULL) takes just 5 bytes, whereas (1024, 
1.234, 'some string', X'0123456789ABCDEF') requires 34 bytes.

The best you can determine is the average length of a record for the dataset 
you have loaded.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Deon Brewis
Gesendet: Donnerstag, 23. Jänner 2020 00:45
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Row length in SQLITE

Is there any way to get the length of rows in a table / index in sqlite?

DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but 
it's an aggregate sum & max per page - I need the data per row (cell).

- Deon

___
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

Re: [sqlite] [EXTERNAL] Re: 18 minutes 41 seconds

[Hick Gunter]

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

No, they still using Roman Numerals instead of Indian Numbers and were 
oblivious of the number 0. As indeed Abu Dschaʿfar Muhammad ibn Musa 
al-Chwārizmī published his book "De numero Indorum" (the earliest latin 
translation of the arabic original, which is lost) somewhere around the year 
825, that introduced their algebra (taken from the original title "al-Kitāb 
al-muḫtaṣar fī ḥisāb al-ğabr wa-ʾl-muqābala") into the arabic world; later 
translations referred to the digits 0-9 as arabic numbers.


___
 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

Re: [sqlite] [EXTERNAL] Only enter higher values in table

[Hick Gunter]

You need an UPDATE trigger for this, since the comparison requires knowledge of 
the old and new values.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Freitag, 27. Dezember 2019 13:05
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Only enter higher values in table

Just to have a way to see my progress at Udemy, I created the following table 
and view:
CREATE TABLE rawSummaryUdemy (
dateTEXTNOT NULL DEFAULT CURRENT_DATE,
total   INTEGER NOT NULL,
completed   INTEGER NOT NULL,

CONSTRAINT formatDate   CHECK(date  = date(strftime('%s',
date), 'unixepoch')),
CONSTRAINT notInFuture  CHECK(date <= date()),
CONSTRAINT totalIsInt   CHECK(TYPEOF(total) = 'integer'),
CONSTRAINT completedIsInt   CHECK(TYPEOF(completed) = 'integer'),
CONSTRAINT totalGEZero  CHECK(total >= 0),
CONSTRAINT completedGEZero  CHECK(completed >= 0),
CONSTRAINT completedLETotal CHECK(completed <= total),

PRIMARY KEY(date)
);
CREATE VIEW summaryUdemy AS
SELECT *
,  total - completed AS toComplete
FROM   rawSummaryUdemy
;

If this can be done better: let me know.

Normally speaking total and completed should never decrease. It is not really 
important, but just as an exercise: is it possible to add constraints so that 
you cannot enter a total, or a completed that is lower as the previous one?

--
Cecil Westerhof
___
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

Re: [sqlite] [EXTERNAL] Compiling SQLite without the database storage?

[Hick Gunter]

No. You need the BTree and table code to handle the sqlite3_master table.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jens Alfke
Gesendet: Samstag, 21. Dezember 2019 19:50
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Compiling SQLite without the database storage?

This may sound crazy, but is there a way to compile SQLite without its B-tree 
and table code? This would be for a use case with _only_ virtual tables — i.e. 
SQLite being used as a SQL query engine on top of a different data store*. It 
would be nice not to drag in too much unused code.

—Jens

* Yes, I happen to be experimenting with LMDB...
___
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

Re: [sqlite] [EXTERNAL] Implementing a statement cache

[Hick Gunter]

Any statement that has been stepped but not to completion will hold open the 
transaction on a connection. This may interfere with your expectations. 
Clearing bindings as a precaution will prevent inadvertent re-use of old 
bindings. The statement may have to be reset first, see documentation. 
Statements prepared with the V2 (or later) interface will recompile if 
necessary.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von carsten.muencheberg
Gesendet: Montag, 16. Dezember 2019 22:38
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Implementing a statement cache

Hi,
I am working on a generic cache for prepared statements and would like to make 
sure that I am not overlooking anything important.

The cache is a simple map from an SQL string to a statement pointer.

1. When to call sqlite3_reset()? It looks like the safest and easiest approach 
is to call sqlite3_reset() immediately after retrieving a statement from the 
cache. Is there any disadvantage in regards to concurrency or performance in 
keeping dozens or hundreds of statements alive in a non reset state e.g. SELECT 
statements which have not stepped over all rows?

2. When to call sqlite3_clear_bindings()? If I understand correctly new values 
can be bound without clearing old ones first, but calling
sqlite3_clear_bindings() can be a safeguard against accidentally executing a 
statement with old values?

3. When to clear the cache? I read that in some cases statements are 
automatically recompiled when a different value is bound to a parameter inside 
the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, would it make 
sense to clear the cache afterwards or can we trust SQLite to maintain existing 
statements under all circumstances?

4. Other ideas, comments?

Thanks in advance.
Carsten
___
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

Re: [sqlite] [EXTERNAL] Difference between hex notation and string notation

[Hick Gunter]

The X'' notation returns a blob. LIKE works with strings. Comparing a string to 
a blob of the same content always returns false.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Sascha Ziemann
Gesendet: Freitag, 13. Dezember 2019 10:16
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Difference between hex notation and string notation

I have a problem to find rows in a database when I write in hex notation:

CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL); INSERT INTO LOG VALUES 
(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
down');
SELECT ROWID,MSG FROM LOG; --
returns both rows
SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
returns just the second
SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; -- returns 
both rows

This looks like a bug to me.

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

Re: [sqlite] [EXTERNAL] Re: Result set column names

[Hick Gunter]

Think about same column names in distinct tables within the same select and 
then throw in a couple of AS clauses and maybe an SQL parameter.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Doug
Gesendet: Dienstag, 10. Dezember 2019 18:13
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] Result set column names

Just to be clear: If I define a column in a table named "xxx", and I "select 
xxx from table", the report will show a column named "xxx" always. That's what 
the standard says, right? It's not implementation dependent, right?

CREATE TABLE foo(xxx)
INSERT INTO foo(xxx) VALUES(25)
SELECT xxx FROM foo
---|-
xxx 25

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Adrian Ho
> Sent: Tuesday, December 10, 2019 8:04 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Result set column names
>
> On 9/12/19 3:45 PM, Graham Holden wrote:
> > Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch
>  wrote:
> >
> >> The SQL-92 standard actually says:
> >>
> >> |Syntax Rules
> >> |
> >> |9) Case:
> >> |
> >> |   b) If the i-th  in the 
> does not
> >> | specify an  and the  of
> that
> >> |  is a single , then
> the
> >> |  of the i-th column of the result is C.
> >> |
> > Presumably the third line of clause (b) of the standard SHOULD
> have
> > read: "is a single  C,", otherwise "is C" has
> > nothing to refer to.
>
> C is actually defined in a preceding paragraph:
>
>  5) Let C be some column. Let QS be the  specification>. Let
> DCi, for i ranging from 1 to the number of  column>s
> inclusively, be the i-th  simply contained
> in
> the  of QS. For all i, C is an underlying
> column
> of DCi, and of any  that identifies DCi,
> if
> and only if C is an underlying column of the  expression>
> of DCi, or C is an underlying column of the  expression>
> immediately contained in QS.
>
> --
> Best Regards,
> Adrian
>
> ___
> 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


___
 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

Re: [sqlite] [EXTERNAL] virtual tables vs. expression-based indexes

[Hick Gunter]

If your external data store can maintain an index on some expression, then 
exposing that index as a computed field is the way to go with a virtual table. 
Alternatively, you can expose the index as a separate virtual table with a 
"foreign key" that references the original virtual table and join them together.

CREATE VIRTUAL TABLE my_data USING my_module;
-> declares (data_id INTEGER PRIMARY KEY, name TEXT);

CREATE VIRTUAL TABLE my_name_length USING my_module('name_length;length(name)');
-> declares (name_length INTEGER, data_id INTEGER);

SELECT d.* from my_data d JOIN my_name_length l ON (d.data_id = l.data_id) 
WHERE l.name_length > 100;

xBestIndex for my_data should report 1 unique record for access via data_id, 
and card(my_data) for full table scan
xBestIndex form y_name_length should report card(my_data)/card(unique 
name_length) for access via name_length

This should allow SQLite to compute the correct query plan. Or you could just 
CROSS JOIN it.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jens Alfke
Gesendet: Donnerstag, 28. November 2019 03:10
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] virtual tables vs. expression-based indexes

I'm considering using a virtual table to query an external-to-SQLite data 
store. However, I've carefully read about the xBestIndex method, and it appears 
that virtual tables cannot have indexes on expressions; or rather that the 
SQLite query engine can't make use of such indexes, only indexes on columns.

Consider for example a virtual table with a column named "text", and a query 
with `WHERE length(text) > 100`. In my external data store I can create an 
index on `length(text)`, but it doesn't look as though SQLite has any way of 
asking me about it, so I assume it will just brute-force scan through every row.

The only workaround I can see is to add a virtual table column for every 
possible expression that might be queried against — like "text_length" — but 
the query interface in my project is open-ended enough that I can't delimit the 
set of expressions that might need to be exposed this way. (It might be 
feasible if I could alter the table on the fly to add columns as needed, but 
the docs explicitly say I can't do that.)

—Jens
___
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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Hick Gunter]

You are using text columns as primary keys and referencing them directly in 
foreign keys. This is probably not what you want, because it duplicates the 
text key. Also, with foreign keys enabled, your join is not accomplishing 
anything more than a direct select from joining_table, just with more effort 
(and circumventing the count() optimization).

SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the 
table. This is what you should be using as a foreign key, because it is twice 
as fast as using an index.

OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those 
too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Dienstag, 26. November 2019 10:25
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes

Hi List,
I have a relational table setup where I've built indexes but I'm still seeing 
very slow join times on middling amounts of data. I'm guessing I'm doing 
something wrong but I can't see what. (SQLite: 3.24.0)

Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table only 
because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.

--==

-- 1.7 million items
CREATE TABLE data_table (
 data_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE,
 data_1TEXT,
 data_2 TEXT );

-- 1.9 million items
CREATE TABLE joining_table (
 data_id TEXT REFERENCES data_table (data_id)
 NOT NULL
 COLLATE NOCASE,
 ignored_id TEXTREFERENCES ignore_me (ignored_id)
 NOT NULL
 COLLATE NOCASE,
 misc_col_1TEXT,
 misc_col_2TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
 ignored_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE );

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

-- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS 
joining_table__data_ignored_id__fk_idx ON joining_table (
 data_id ASC,
 ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS 
joining_table__ignored_data_id__fk_idx ON joining_table (
 ignored_id ASC,
 data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO 
data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES 
('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 
'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, 
ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249');




-- Then to test the speed I'm simply doing:
 SELECT
 count(1)
 FROM
 data_table
 JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
 SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
 SEARCH TABLE data_table USING COVERING INDEX data_table__data_id__pk_idx 
(data_id=?)

But it takes about 20 seconds to do that count on the full dataset.

The full EXPLAIN from the full dataset:

0Init016000
1Null01100
2OpenRead27718750k(3,NOCASE,NOCASE,)00
3OpenRead37377150k(2,NOCASE,)02
4Rewind2122000
5Column21200
6SeekGE3112100
7IdxGT3112100
8Integer13000
9AggStep0031count(1)01
10Next37100
11Next25001
12AggFinal110count(1)00
13Copy14000
14ResultRow41000
15Halt00000
16Transaction0077001
17Goto01000

Thoughts? What (probably obvious) thing am I missing?

Thanks,
Jonathan



__

Re: [sqlite] [EXTERNAL] Symlink to update a database table

[Hick Gunter]

What is the use case?

The statement you give will set the value of the "column" field of table 
"table" to the whole contents of file.txt in each and every row that matches 


SQLite does not have a symbolic link type. You can store the text of a symbolic 
link, but accessing the contents would still require readfile() or something 
similar..

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sofiestoc
Gesendet: Donnerstag, 21. November 2019 13:57
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Symlink to update a database table

Hello,

Complete newbie here. I have a text file that contains contigs from an 
assembly. I was wondering if there is a way to symlink the file to table in 
sqlite by using bash command. I came across the function readfile but I would 
prefer to create a symbolic link to the pathway instead of reading the contents 
of the file.

#!/bin/bash

sqlite3 db "update table set column=readfile('file.txt') where "

Best regards,
Sofia





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

Re: [sqlite] [EXTERNAL] [Importing CSV] Empty colums != NULL?

[Hick Gunter]

CSV is unable to represent the NULL value. The best it can do is "empty 
string", which gets converted to - drumroll - an empty string (or the value 0 
for numeric fields).

The field names you are using suggest that you may have not sufficiently 
normalized your data, since you have duplicated postcode and city fields. Have 
you considered that the same person might be the contact for more than one shop?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Winfried
Gesendet: Donnerstag, 21. November 2019 12:32
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] [Importing CSV] Empty colums != NULL?

Hello,

I imported data from a CSV file.

Some of the columns are empty, so I expected them to be set to NULL, but it 
looks like it's not how SQLite handles them internally:

=
sqlite> .schema
CREATE TABLE Shops(
  "id" TEXT,
  "name" TEXT,
  "contact:postcode" TEXT,
  "contact:city" TEXT,
  "addr:postcode" TEXT,
  "addr:city" TEXT,
  "contact:phone" TEXT,
  "email" TEXT,
  "website" TEXT
);
=

Here's a record where "addr:postcode" is filled, but "contact:postcode" is
not:
=
sqlite> select * from Shops limit 10;
id;name;contact:postcode;contact:city;addr:postcode;addr:city;contact:phone;email;website
251373376;Acme;;;12345;My City;;;http://www.acme.com etc.
=

But SQLite returns an empy column when using IFNULL:
=
sqlite> select name, IFNULL("contact:postcode","addr:postcode") zipcode
sqlite> from
Shops where id="251373376";
name;zipcode
Acme;
=

Should I use a different command when importing data or running IFNULL?

Thank you.



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

Re: [sqlite] [EXTERNAL] Re: Question about: Adding a record to a table with select failure

[Hick Gunter]

The Magic Max at work, forcing the query to return at least one record.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Montag, 18. November 2019 20:11
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] Question about: Adding a record to a table 
with select failure


Doug, on Monday, November 18, 2019 12:31 PM, wrote...
Jose Isaias Cabrera
[clip]
> > >
> > > INSERT INTO t (a, b, c, d, e, idate)​ SELECT IfNull('p006',
> > > Max(idate)),​
> > >IfNull(b, 1),​
> > >IfNull(c, 2),​
> > >'y',​
> > >IfNull(e, 4),​
> > >'2019-20-12'​
> > >   FROM t​
> > >  WHERE a = 'p006';​
>
> I think that you will never insert the first record with a query like
> this, since the select returns 0 records of there are none in the database 
> yet.

Well, it does...
sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a, idate));
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-20-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
sqlite> select * from t;
p001|1|2|n|4|2019-20-11
sqlite>

And, since I put an uniqueness on a and idate, now these can not be repeated, 
so if I run the same command again,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
Error: UNIQUE constraint failed: t.a, t.idate
sqlite>

I do not get a repeated record for 'p001' and 2019-02-11; But if they are 
different,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p002', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p002';
sqlite> select * from t;
p001|1|2|n|4|2019-02-11
p002|1|2|n|4|2019-02-11

It'll work. Thanks.

josé


___
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

Re: [sqlite] [EXTERNAL] Re: Question about: Adding a record to a table with select failure

[Hick Gunter]

Nothing. The select returns no rows so no rows are inserted.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Doug
Gesendet: Freitag, 15. November 2019 17:42
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] Question about: Adding a record to a table 
with select failure

WRT Jose's original context, and just for my enlightment, what happens with the 
following:

insert into t (a, b, c, d, e, idate)
SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';

where p999 does not define a record? Is a new record inserted with values of 
a,b,c, and e null?

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Friday, November 15, 2019 6:21 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Adding a record to a table with one value change
>
>
> Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote...
> >
> > On 14 Nov 2019, at 10:27pm, Jake Thaw, on
> >
> > > Why not like this?
> > >
> > > insert into t (a, b, c, d, e, idate) SELECT a, b, c, 'y', e,
> > > '2019-02-12' FROM t WHERE a = 'p001'
> ORDER BY
> > > idate desc limit 1;
> >
> > Dammit.  I thought I had tried this, and received a syntax
> error.  Now I see that it was because I missed out a comma.
> Thanks for the correction.
>
> That just lets you know that you are a human. ;-)
>
> > Good illustration of why responses should go to the list rather
> than direct to the OP.
>
> Indeed.
> ___
> 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


___
 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

Re: [sqlite] [EXTERNAL] Re: Things you shouldn't assume when you store names

[Hick Gunter]

>> A growing number of organisations now ask me for my DOB or my
>> postcode, rather than my name, when looking me up.  I think you just
>> explained why.  In my country we have an increasing number of foreign
>> family names, which probably helps it along.
>
>UK postcodes are incredibly fine-grained, compared to most of the rest of the 
>world, where they would be much less useful for identification.
>
>Eric

Back when I lived in England our postcode was DT1 2DQ with the first substring 
indicating the postmans' route (Dorchester Town 1) and the second substring 
indicating the approximate position along the route i.e. the "visitation order" 
of addresses along that route. A simple sort by postcode made sure that the 
mail landed in the correct bag and that the postman needed to look only at the 
next letters' postcode to know where to go next.

Postcodes here in Austria only designate the nearest delivery post office 
(which may change)


___
 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

Re: [sqlite] [EXTERNAL] Specific sqlite_autoindex_* missing in recent SQLite versions

[Hick Gunter]

Maybe you are confusing the autoindex logic by including superflous attributes:

... Id INTEGER NOT NULL PRIMARY KEY UNIQUE ...

NOT NULL is enforced for WITHOUT ROWID tables and a single field PRIMARY KEY 
already implies UNIQUE, so no autoindex is required for Id

This leaves only the autoindex required for the UNIQUE constraint on name.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Vincas Dargis
Gesendet: Donnerstag, 14. November 2019 10:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Specific sqlite_autoindex_* missing in recent 
SQLite versions

Hi list,

Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from 
Debian 8 stretch) binary on some database file created with more recent SQLite 
(like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that 
database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` 
table (checked by test suite).

I was puzzled at the beginning, but after some fiddling I see that creating 
this table:
```
CREATE TABLE "equipment_type"(
  id INTEGER NOT NULL PRIMARY KEY UNIQUE,
  name TEXT NOT NULL UNIQUE
) WITHOUT ROWID;
```
with recent SQLite, we get this set (only one) of internal indexes:
```
SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%'
index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I 
create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for 
two fields:
```
index|sqlite_autoindex_equipment_type_1|equipment_type|3|
index|sqlite_autoindex_equipment_type_2|equipment_type|4|
```

It feels strange if UNIQUE and PRIMARY KEY did not provoke creating 
`sqlite_autoindex`, but maybe we are missing something? Is this behavior change 
expected?

Thanks!
___
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

Re: [sqlite] [EXTERNAL] Re: select for power-meter accumulated total readings

[Hick Gunter]

>What about if I want 1 hour granity? (to plot a graph of daily consumption for 
>example)

For a (meaningful, as opposed to "all interpolated values") granularity of 1 
hour, information theory states that you need a sample every 30 minutes or less.

The desire to charge consumers more for "peak power" (just like utilities have 
to pay more for "peak power" and less for "base load") is the driving force 
behind the installation of "smart meters". Currently, consumers pay "flat rate" 
based on their total annual consumption, irrespective of their contribution to 
bas and peak loads.


___
 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

Re: [sqlite] [EXTERNAL] Roadmap?

[Hick Gunter]

The "virtual table playground gadget" was our primary reason for selecting 
SQLite in the first place, because none of our production data sources are 
native SQLite tables. Instead, we have about 20 virtual table modules that 
implement about 1000 virtual table instances.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Sonntag, 20. Oktober 2019 09:53
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Roadmap?

I'd kindly ask whether there is some sort of roadmap for SQLite development?

Someone recently pointed out how much he loves the "lite" and well-thought 
features. I cannot see that: I observe that many "playground" gadgets keep 
being implemented (like virtual columns, virtual tables, FTS3/4/5, ...), where 
one might wonder about their relationship to "Liteness", whereas other 
features, essential basics of the SQL standards, are still missing and there is 
no indication they are to be added.

Without wanting to offend someone, I cannot see the logic in development, so: 
Is there some kind of roadmap?

___
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

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

[Hick Gunter]

If you should happen to have the need for storing columns that each have only a 
small set of possible values, maybe you would be better off looking into 
FastBit, which has coulmn-oriented storage

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Donnerstag, 17. Oktober 2019 15:46
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

 Hi!

Thanks for this input. So you are saying that sqlite3_column 100k times per row 
is slow, but retrieving 100k rows to construct one "original" row will be 
faster? So not sure if I understand why reading and decoding cells in over 
multiple columns is so much slower than reading and decoding cells in over 
multiple rows?

Mitar

On Thu, Oct 17, 2019 at 3:38 PM Hick Gunter  wrote:
>
> I have the impression that you still do not grasp the folly of a 100k column 
> schema.
>
> See the example below, which only has 6 fields. As you can see, each field 
> requires a Column opcode and arguments (about 10 bytes) and a "register" to 
> hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to 
> retrieve a row from the database. It ill also involve SQLite decoding 100k 
> field values and your application calling sqlite3_column interface 100k times 
> for each and every row, which yield an expected performance of about 2 rows 
> per second. Can you afford to use that much memory and time?
>
> asql> create temp table genes (id integer primary key, name char, f1
> asql> char, f2 char, f3 char, f4 char); .explain explain select * from
> asql> genes;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 13000  Start at 13
> 1 OpenRead   0 2 1 6  00  root=2 iDb=1; genes
> 2 Explain2 0 0 SCAN TABLE genes  00
> 3 Rewind 0 12000
> 4   Rowid  0 1 000  r[1]=rowid
> 5   Column 0 1 200  r[2]=genes.name
> 6   Column 0 2 300  r[3]=genes.f1
> 7   Column 0 3 400  r[4]=genes.f2
> 8   Column 0 4 500  r[5]=genes.f3
> 9   Column 0 5 600  r[6]=genes.f4
> 10  ResultRow  1 6 000  output=r[1..6]
> 11Next   0 4 001
> 12Halt   0 0 000
> 13Transaction1 0 1 0  01  usesStmtJournal=0
> 14Goto   0 1 000
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Mitar
> Gesendet: Donnerstag, 17. Oktober 2019 15:11
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite
> table
>
> Hi!
>
> On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> > my suggestion would be to store them as JSON in a blob, and use the
> > JSON functions of SQLite to extract the data
>
> JSON has some crazy limitations like by standard it does not support full 
> floating point spec, so NaN and infinity cannot be represented there. So JSON 
> is really no a great format when you want to preserve as much of the input as 
> possible (like, integers, floats, text, and binary). SQLite seems to be spot 
> on in this regard.
>
> But yes, if there would be some other standard to SQLite and supported format 
> to embed, that approach would be useful. Like composite value types.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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



--
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-u

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

[Hick Gunter]

If you need to perform queries over the 100k rows, then normalization is the 
only practical way and reconstructing a row will similarly slow.

But you have stated that you use case is "retrieving complete rows". In this 
case, SQLite does not need to know the 100k details of the row. Keep your data 
in whatever format they come in - which is another requirement you have 
expressed. All you have to extract from the 100k details is the columns that 
you need to identify the row(s) you want back.

Create table gene_data (sample_id integer primary key, name char, raw_data 
blob);

Anything else you need to do with the row data goes into your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Donnerstag, 17. Oktober 2019 15:46
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

 Hi!

Thanks for this input. So you are saying that sqlite3_column 100k times per row 
is slow, but retrieving 100k rows to construct one "original" row will be 
faster? So not sure if I understand why reading and decoding cells in over 
multiple columns is so much slower than reading and decoding cells in over 
multiple rows?

Mitar

On Thu, Oct 17, 2019 at 3:38 PM Hick Gunter  wrote:
>
> I have the impression that you still do not grasp the folly of a 100k column 
> schema.
>
> See the example below, which only has 6 fields. As you can see, each field 
> requires a Column opcode and arguments (about 10 bytes) and a "register" to 
> hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to 
> retrieve a row from the database. It ill also involve SQLite decoding 100k 
> field values and your application calling sqlite3_column interface 100k times 
> for each and every row, which yield an expected performance of about 2 rows 
> per second. Can you afford to use that much memory and time?
>
> asql> create temp table genes (id integer primary key, name char, f1
> asql> char, f2 char, f3 char, f4 char); .explain explain select * from
> asql> genes;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 13000  Start at 13
> 1 OpenRead   0 2 1 6  00  root=2 iDb=1; genes
> 2 Explain2 0 0 SCAN TABLE genes  00
> 3 Rewind 0 12000
> 4   Rowid  0 1 000  r[1]=rowid
> 5   Column 0 1 200  r[2]=genes.name
> 6   Column 0 2 300  r[3]=genes.f1
> 7   Column 0 3 400  r[4]=genes.f2
> 8   Column 0 4 500  r[5]=genes.f3
> 9   Column 0 5 600  r[6]=genes.f4
> 10  ResultRow  1 6 000  output=r[1..6]
> 11Next   0 4 001
> 12Halt   0 0 000
> 13Transaction1 0 1 0  01  usesStmtJournal=0
> 14Goto   0 1 000
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Mitar
> Gesendet: Donnerstag, 17. Oktober 2019 15:11
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite
> table
>
> Hi!
>
> On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> > my suggestion would be to store them as JSON in a blob, and use the
> > JSON functions of SQLite to extract the data
>
> JSON has some crazy limitations like by standard it does not support full 
> floating point spec, so NaN and infinity cannot be represented there. So JSON 
> is really no a great format when you want to preserve as much of the input as 
> possible (like, integers, floats, text, and binary). SQLite seems to be spot 
> on in this regard.
>
> But yes, if there would be some other standard to SQLite and supported format 
> to embed, that approach would be useful. Like composite value types.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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 | F

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

[Hick Gunter]

I have the impression that you still do not grasp the folly of a 100k column 
schema.

See the example below, which only has 6 fields. As you can see, each field 
requires a Column opcode and arguments (about 10 bytes) and a "register" to 
hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to 
retrieve a row from the database. It ill also involve SQLite decoding 100k 
field values and your application calling sqlite3_column interface 100k times 
for each and every row, which yield an expected performance of about 2 rows per 
second. Can you afford to use that much memory and time?

asql> create temp table genes (id integer primary key, name char, f1 char, f2 
char, f3 char, f4 char);
asql> .explain
asql> explain select * from genes;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 13000  Start at 13
1 OpenRead   0 2 1 6  00  root=2 iDb=1; genes
2 Explain2 0 0 SCAN TABLE genes  00
3 Rewind 0 12000
4   Rowid  0 1 000  r[1]=rowid
5   Column 0 1 200  r[2]=genes.name
6   Column 0 2 300  r[3]=genes.f1
7   Column 0 3 400  r[4]=genes.f2
8   Column 0 4 500  r[5]=genes.f3
9   Column 0 5 600  r[6]=genes.f4
10  ResultRow  1 6 000  output=r[1..6]
11Next   0 4 001
12Halt   0 0 000
13Transaction1 0 1 0  01  usesStmtJournal=0
14Goto   0 1 000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Donnerstag, 17. Oktober 2019 15:11
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite table

Hi!

On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> my suggestion would be to store them as JSON in a blob, and use the
> JSON functions of SQLite to extract the data

JSON has some crazy limitations like by standard it does not support full 
floating point spec, so NaN and infinity cannot be represented there. So JSON 
is really no a great format when you want to preserve as much of the input as 
possible (like, integers, floats, text, and binary). SQLite seems to be spot on 
in this regard.

But yes, if there would be some other standard to SQLite and supported format 
to embed, that approach would be useful. Like composite value types.


Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
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

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

[Hick Gunter]

Since your data is at least mostly opaque in the sense that SQLite is not 
expected to interpret the contents, why not split your data into "stuff you 
want to query ins SQLite" and "stuff you want to just store"? The former means 
individual columns, whereas the latter could be stored in a single BLOB field, 
which only your application knows how to extract data from.

This allows SQLite to efficiently process the fields it needs to know about, 
and return BLOB data efficiently as one single field instead of having to pick 
it apart into 100k bits.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Donnerstag, 17. Oktober 2019 10:56
An: Richard Hipp 
Cc: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite table

Hi!

I can see how this is a reasonable limit when SQLite is used for querying power 
it provides. In our case we are really focusing on it as a standard long-term 
storage format. So in the "Appropriate Uses For SQLite" document [1] you have a 
section called "File archive and/or data container" and this is why we started 
considering SQLite as a dataset archive format. We would not like to store 
files directly, but contents of those files (like contents of CSV). But try to 
not modify them more than necessary. So we got interested especially in the 
"SQLite is a good solution for any situation that requires bundling diverse 
content into a self-contained and self-describing package for shipment across a 
network." statement. So I can understand how supporting a large number of 
columns might be inappropriate when you want to run complicated SQL queries on 
data, but to just store data and then extract all rows to do some data 
processing, Or as the most complicated query it would be to extract just a 
subsample of rows. But not really do to any JOIN queries or something like 
that. it looks like except for artificial limit in SQLite, because it is not 
useful for general case, there is no other reason why it could not be supported.

So why not increase the limit to 2 billion, and have it at runtime by default 
limited to 2000. And then using PRAGMA one could increase this if needed to 2 
billion? PRAGMA already can decrease the limit, so we can keep the existing 
2000 limit, but to support it without having to recompile, people could 
increase it all the way to 2 billion. Is there any significant performance 
downside to this?

[1] https://www.sqlite.org/whentouse.html


Mitar

On Wed, Oct 16, 2019 at 8:21 PM Richard Hipp  wrote:
>
> SQLite could, in theory, be enhanced (with just a few minor tweaks) to
> support up to 2 billion columns.  But having a relation with a large
> number of columns seems like a very bad idea stylistically.  That's
> not how relational databases are intended to be used.  Normally when a
> table acquires more than a couple dozen columns, that is a good
> indication that you need normalize and/or refactor your schema. Schema
> designers almost unanimously follow that design principle.  And so
> SQLite is optimized for the overwhelmingly common case of a small
> number of columns per table.
>
> Hence, adding the ability to have a table with a huge number of
> columns is not something that I am interested in supporting in SQLite
> at this time.
>
> --
> D. Richard Hipp
> d...@sqlite.org



--
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
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

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

[Hick Gunter]

That would be falling off the other side of the horse. Neither column nor table 
names are supposed to be derived from data items, but rather from abstract 
categories.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von J Decker
Gesendet: Donnerstag, 17. Oktober 2019 08:24
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

On Wed, Oct 16, 2019 at 11:03 AM Mitar  wrote:

> Hi!
>
> On Wed, Oct 16, 2019 at 3:16 PM Hick Gunter  wrote:
> > 100k distinct column names? Or is that 1 repeats of 10 attributes?
>
> 100k distinct names. Like each column a different gene expression.
>
To me that sounds more like a table distinction; such that you;'d have 100k 
tables with 2 columns each (their row, and their value of that gene)...
it's too bad table names cant be bound parameters.


>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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


___
 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

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

[Hick Gunter]

The I suggest normalizing the schema.

Table genes (gene_id integer primary key, name char)

Table variant (variant_id integer primary key, gene_id integer, name char)

Table sample (sample_id integer primary key, name char, ...);

Table sample_gene_variant (sample_id integer, gene_id integer, variant_id 
integer, unique (sample_id, gene_id));

Appropriate foreign keys are recommended.

Then you can store your 100k bits of information per sample in the 
sample_gene_variant relation. Also, should the need arise to store new genes or 
variants, all it takes is new entries in the gene or variant tables, instead of 
adding more columns to an already bloated table.

Getting the value of a specific column from the intiial schema is then a simple 
join:

SELECT s.name, g.name, v.name from sample s join gene g join 
sample_gene_variant sgv on (sgv.sample_id = s.sample_id and sgv.gene_id =  
gene.gene_id) join variant v on (v.variant_id = sgv.variant_id) where s.name = 
'John Doe' and g.name = 'BCL11B';


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Mittwoch, 16. Oktober 2019 20:03
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

Hi!

On Wed, Oct 16, 2019 at 3:16 PM Hick Gunter  wrote:
> 100k distinct column names? Or is that 1 repeats of 10 attributes?

100k distinct names. Like each column a different gene expression.


Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
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

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

[Hick Gunter]

100k distinct column names? Or is that 1 repeats of 10 attributes?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mitar
Gesendet: Mittwoch, 16. Oktober 2019 14:57
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Limit on number of columns in SQLite table

Hi!

We are considering using SQLite as a ML dataset archival format for datasets in 
OpenML (https://www.openml.org/). When investigating it, we noticed that it has 
a very low limit on number of columns. Quite some of datasets we are dealing 
with have 100k or so columns. Are there any fundamental reasons why this limit 
is so low (even if we extend it during compiling, it can be at most 32k 
columns), while others are comfortably large? Any plans to extend this limit in 
the future?


Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
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

Re: [sqlite] [EXTERNAL] Re: Last record

[Hick Gunter]

Then the first peanut may well be the last one, irrespective of the cardinality 
of the tin.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 15. Oktober 2019 21:52
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Last record

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera 
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record
> > >with sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW.
> > >Is there a function for that case which returns SQLITE_DONE? A
> > >function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the
> > next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate
> > another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that
> > when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty",
> > rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and
> more than that, funny.  I appreciate them. Thanks.
>
> 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


___
 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

Re: [sqlite] [EXTERNAL] Last record

[Hick Gunter]

The order of rows returned by a query is undefined - i.e. from the point of 
view of the application, a random member of the result set will be returned 
last - unless you include an ORDER BY clause that uniquely defines the order of 
the records to be returned. Given the latter, it is easy to define an exactly 
opposite ORDER BY clause and retrieve only the first record via LIMIT 1.

Why do you think you need to know if/how many records remain?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Philippe RIO
Gesendet: Dienstag, 15. Oktober 2019 17:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Last record

A short question : how could I know if I am reading the last record with sqlite 
 (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a function for 
that case which returns SQLITE_DONE? A function which is one record in advance 
from sqlite3_step.

Thank every one



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
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

Re: [sqlite] [EXTERNAL] Possible bug in storing text values in numeric columns

[Hick Gunter]

You are getting exactly what is documented and exactly what you asked for.

Declaring a column NUMERIC means you intend to store NUMBERS. Leading zeros do 
not change the value of a number. 0012 == 12 unless you have a convention of 
interpreting a leading zero as indicating octal base.

If you need to display numbers zero filled on the left, that would be the task 
of the presentation layer. See also the printf() function.

Lossless and reversible means 15 significant digits. Leading zeros are NOT 
significant.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Shawn Wagner
Gesendet: Sonntag, 13. Oktober 2019 23:12
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Possible bug in storing text values in numeric 
columns

The documentation for a column with NUMERIC affinity says

> When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such 
conversion is lossless and reversible.

But consider:

sqlite> create table foo(bar numeric);
sqlite> insert into foo values ('0012');
sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
--  ---12  integer


As you can see, the leading zeros in the original string are gone and it's been 
converted to an integer. This seems to violate the "lossless and reversible" 
constraint. Shouldn't it be kept as 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

Re: [sqlite] [EXTERNAL] Re: Date time input

[Hick Gunter]

What it boils down to is asking the data storage layer to perform a 
presentation layer task.

If you insist on solving the problem inside an SQL statement, you can always 
write your own extension function to "easily" perform the necessary conversion.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Dienstag, 08. Oktober 2019 14:32
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Date time input


Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
[clip]
> I swear, half the questions on this list build down to "Why
> doesn't SQLite act like MS Access?" If you need all the bells and
> whistles of formatting input and output, then use a fancy DBMS
> application. SQLite is for embedded use _inside_
> applications.

No, that is not what I was trying to say or ask.  Not even close. What I was 
trying to say, and most of you missed it was, that if I give date a date 
format, and I also provide the format of how that date is to be understood, ie.

date('5/22/2019','m/d/')

where the date is the first entry, '5/22/2019', and the format is the second 
entry, 'm/d/', that SQLite could take that set of data and easily convert 
and return the ISO date I want.  Yes, I know I can write that outside the code, 
or inside in SQL, but "it would be nice to have this."  Thanks for all the 
responses.

josé
___
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

Re: [sqlite] [EXTERNAL] Re: Newbie Issues with COLLATE

[Hick Gunter]

I'm guessing that LOCALE_NOCASE will probably be causing things that collate 
distinct in NOCASE to collate equal, so the risk of breaking UNIQUE constraints 
seems rather small

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Dienstag, 01. Oktober 2019 19:41
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Newbie Issues with COLLATE

On 10/1/19, Jim and Carol Ingram  wrote:
> QUESTION
> ONE:  Are these "LOCALE_..." collations custom collations developed by
> the software programmers,

Yes.  The application is using the sqlite3_create_collation() API
(https://www.sqlite.org/c3ref/create_collation.html) to create a new collating 
sequence that they are calling "LOCALE_NOCASE".  We can guess at what that 
collating sequence does from its name, but without seeing the code, we don't 
really know.

>
> I have noted by trial and error that modifying the original query's
> last line to include COLLATE NOCASE ("group by k.Keyword COLLATE
> NOCASE;") makes the query work against the original Keywords table
> without error.  I suspect this is the correct way to address the error
> message rather than my admittedly messy and time-consuming workaround.
> QUESTION TWO:  Can anyone verify that this is indeed the acceptable
> way to address the error message, or provide a more acceptable way to do it?

That seems like a reasonable approach to me!

Another thing to consider, depending on how much data there is, is to run the 
".dump" command to convert the whole database into a big pile of SQL.  Then 
edit the SQL to change LOCALE_NOCASE into just NOCASE, and reimport it into a 
new SQLite database.  Then all of your queries will work correctly.

Or, you could set "PRAGMA writable_schema=ON" and then do an UPDATE statement 
on the sqlite_master table to actually change the text of the CREATE TABLE 
statement:

PRAGMA writable_schema=on;
UPDATE sqlite_master SET sql=replace(sql,'LOCALE_NOCASE','NOCASE')
  WHERE name LIKE 'keywords';

Then exit the command-line tool and reopen and type "REINDEX".  Then you should 
be good to go.  Warning:  Make a backup copy first, as things might to wrong.  
In particular, the change from LOCALE_NOCASE to just NOCASE might possibly 
cause the UNIQUE constraint to start failing in one or more cases.  (Unlikely, 
but possible.)  So be prepared to work around such difficulties.

> And finally, QUESTION THREE: Can anyone point me to a thorough and
> detailed tutorial of the whole SQLite COLLATE subject, including
> creation of custom collations and loading them into the SQLite
> command-line executable if possible?  The documentation seems really thin in 
> this area!
>

The https://www.sqlite.org/c3ref/create_collation.html document is about all we 
have.  There might be more information in some of the books about SQLite.

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


___
 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

Re: [sqlite] [EXTERNAL] The LIKE operator and Swift

[Hick Gunter]

You can't have a variable inside a pattern. Use like '%' || ? || '%'

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Daniel Odom
Gesendet: Donnerstag, 26. September 2019 15:26
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] The LIKE operator and Swift

I am just now getting around to learning Swift and XCode. I am having a problem 
with 'LIKE'. When I do this:

let queryString = "select name, phone, street, city, state from phone where 
name like '%?%'"

And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) != 
SQLITE_OK {do whatever}

I get an error "column index out of range". The rest of the code is fine. When 
I do this:

let queryString = "select name, phone, street, city, state from phone where 
name = ?"

everything works just fine. What am I missing?

___
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

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

[Hick Gunter]

The dialogue from the stackoverflow discussion shows this quite clearly.

"The code for looping over an index goes backwards only when needed. For 
implementing GROUP BY itself, going backwards is never needed, so it is never 
tried.

It is possible that a future SQLite version might add code to the GROUP BY 
implementation to check for this special case.

Make the index DESC on x. – CL. 18 hours ago

Already tried some variations of DESC in index, same result as before. 
Declerative descriptions are nice until you hit some annoying thing that the 
fancy optimizer don't handle very well. If only I had a bit more control over 
the query-planner – frelars 18 hours ago "

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Freitag, 20. September 2019 11:12
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as 
expected

On Thu, Sep 19, 2019 at 6:15 PM Hick Gunter  wrote:

> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Fredrik Larsen
> Gesendet: Donnerstag, 19. September 2019 17:29
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not
> work as expected ...
> Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows
> traversal both ways. You can see this if you remove GROUP_BY.
> ...
> True and nothing new, but not the point.
>
> After doing GROUP BY x over the covering index, the result rows would
> be returned by x ASC.

There is no index on the rowset returned by the GROUP BY, as the rows only
> exist one at a time.

Therefore, the only way to get them into ORDER BY X DESC is to sort them.
>

But who says the GROUP BY must return rows in ASCending order?

A lot of us "oldies" of this ML well know the order is arbitrary and subject to 
change w/o an explicit ORDER BY.
So the GROUP BY is allowed, AFAIK, to return rows in DESCending order just the 
same. And to do so efficiently as Fredrik points out, since indexes (or 
indices, I never know) work equally well in both directions. In fact, it could 
return rows in arbitrary / random order too!

The query-planner does see the ORDER BY that follows the GROUP BY after all, so 
it could well decide to group in DESCending order, thus avoiding the ordering 
completely, like it already does for ASCending.
This would be a great optimisation, and from 30,000ft, it does indeed seem like 
a "simple" one compared to all the advanced optimisations already implements, 
as Fredrik mentioned.

I might even say that it looks like a "low-hanging-fruit", if I dared :).
Dunno, perhaps GROUP BY has some
requirement an ordering, or GROUP BY impls somehow can't easily work "in 
reverse", I'm no expert of the code. I wish the experts would chime in. Too 
often we never hear any rational for doing or not doing things.
This is a "users" list and there's no "dev" list. I wish more was shared about 
the internal structures, impls, etc... explaining why something is harder to 
implement that it sounds. Oh well... --DD 
___
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

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

[Hick Gunter]

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Fredrik Larsen
Gesendet: Donnerstag, 19. September 2019 17:29
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as 
expected
...
Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows traversal 
both ways. You can see this if you remove GROUP_BY.
...

True and nothing new, but not the point.

After doing GROUP BY x over the covering index, the result rows would be 
returned by x ASC. There is no index on the rowset returned by the GROUP BY, as 
the rows only exist one at a time. Therefore, the only way to get them into 
ORDER BY X DESC is to sort them.

Gunter


___
 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

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

[Hick Gunter]

An ORDER BY clause will omit sorting only if the visitation order exactly 
fulfills the clause.

A GROUP BY clause is able to avoid creating a temporary table if the visitation 
order exactly fulfills the clause.

If a SELECT references only fields present in an index, that (covering) index 
may be used instead of the table.

In your case, the SELECT references fields x and y, both of which are present 
in the index, so the QP uses the covering index. This also happens to be sorted 
by x, allowing the GROUP BY to avoid a temporary table and producing rows 
odered by x.

The ORDER BY x in the first query is thus already fulfilled and so no sorting 
is required.

The ORDER BY x DESC in the second query is thus NOT fulfilled, so a sorting 
step is required.

Unfortunately, trying to be clever by creating an index on (x desc, y) does not 
help.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Fredrik Larsen
Gesendet: Donnerstag, 19. September 2019 14:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Group-by and order-by-desc does not work as 
expected

I have a aggregate query that works as expected when the ordering is ascending, 
but uses a TMP B-TREE when changing order to descending, see stackoverflow link 
below.

Is there something I'm missing? I would expect same performance when ordering 
both directions.

Link:
https://stackoverflow.com/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected


Fredrik Larsen
___
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

Re: [sqlite] [EXTERNAL] Re: How to do setdiff but update the original table by the result?

[Hick Gunter]

The mathlab function setdiff(a,b) returns the rows from a that are not in b. 
The equivalent SQL (assuming identical tables a and b) would be

SELECT  FROM a EXCEPT SELECT  FROM b

You can then

INSERT INTO b SELECT * FROM a WHERE  IN (SELECT  FROM 
a EXCEPT SELECT  FROM b);
DELETE FROM a WHERE  IN (SELECT  FROM a EXCEPT SELECT 
 FROM b);


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Keith Medcalf
Gesendet: Mittwoch, 18. September 2019 17:12
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] How to do setdiff but update the original 
table by the result?


On Wednesday, 18 September, 2019 08:38, Peng Yu  wrote:

>I'd like to perform setdiff. I think this should be relevant. But it
>does not update the original table. I want to update the original table
>by the result. Is there a command to do so? Thanks.

UPDATE ...

Please define what is "setdiff".
Please define what you mean by "update the original table by the result".

For example:

I want to find all the rows in table2 that are not in table1 and then insert 
those rows into table1.
I want to find all the rows in table1 that are not in table2 and then delete 
those rows from table1.

Or something to that effect.  You need to be very explicit.  There is no magic.

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


___
 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

Re: [sqlite] [EXTERNAL] Comparison of incompatible types

[Hick Gunter]

This is well documented in https://sqlite.org/datatypes.html and 
https://sqlite.org/datatype3.html


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von kapil
Gesendet: Samstag, 14. September 2019 11:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Comparison of incompatible types

Hi all,

I was wondering whether sqlite does type checking for fields which are 
referenced in WHERE clause. Because when i tried to compare a field generated 
by printf function and tried to compare it with a float value, the comparison 
didn't work

Eg.
SELECT count.theme as theme, printf("%.2f",
(count.num_sets*100.00/sum.total)) as percentage

FROM count,sum

WHERE percentage >= 5.00;

It was not giving expected results.
When i replaced printf with ROUND function, it worked.
So i got to think whether this was because printf returns string value.

If so then shouldn't there be type check on fields in comparisons so that user 
can get a meaningful error message or in this case, any error message at all

Regards
Kapil Garg

___
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

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

[Hick Gunter]

WITH list (key) AS (VALUES (mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jens Alfke
Gesendet: Freitag, 13. September 2019 18:39
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Fastest way to SELECT on a set of keys?

If I have a set of primary keys (let's say a few hundred) and need to fetch 
data from the table rows with those keys, what's the fastest way to do so? The 
options seem to be:

(a) Execute "SELECT … FROM table WHERE key=?", once for each key.
(b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of the 
key strings.

If I do (a), I can pre-prepare the statement and save the overhead of 
compilation. But SQLite has to go through the rest of its work (starting the 
virtual machine, b-tree lookup, etc.) once for each key.

If I do (b), SQLite has less setup work to do, and it could potentially 
optimize the b-tree lookup. On the downside, I have to prepare a statement 
every time since the RHS of an "IN" isn't substitutable.

Does anyone have intuition or actual knowledge about which approach is better? 
Or know of a 3rd better approach?

—Jens
___
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

Re: [sqlite] [EXTERNAL] Re: How to increase performance when inserting a lot of small data into table using indices

[Hick Gunter]

While a write transaction is open, SQLite needs to keep the changed pages in 
memory. When the size of a transaction (measured in changed pages) exceeds the 
available memory, SQLite starts to spill the transaction to disk. The optimal 
transaction size would be just before this occurs, but there is no interface 
available to determine this, so the number of records is a commonly used proxy.

Creating indexes after insert is faster because the writes are localized to the 
index pages, so you get more logical inserts per disk write and file system 
buffering and read ahead have more cache hits too.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von mailing lists
Gesendet: Dienstag, 10. September 2019 17:26
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] How to increase performance when inserting a 
lot of small data into table using indices

Hi,

I cannot really put all the inserts into one transaction because in case of a 
failure I loose all the already inserted data. Though I made some tests. There 
is hardly any performance gain anymore when doing 1000 or 10 000 insertions in 
one transaction including immediate insertion into indices (in my case the 
difference is in the per cent range).

What is the background that index creation is so much faster than insertion 
using indices? Once I heard something about fragmentation but on solid state 
disks fragmentation should not play a role as long as indices and data are 
separated, are they?

Regards,
Hartwig


> Am 2019-09-10 um 17:16 schrieb Richard Hipp :
>
> On 9/10/19, mailing lists  wrote:
>
>> So, the best solution I found so far is to disable indexing while
>> insertion and to index the table afterwards
>
> I think that is the best solution.  Be sure to also do all of your
> inserts (and the CREATE INDEX statements) inside of a transaction.
>
> --
> 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


___
 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

Re: [sqlite] [EXTERNAL] Re: insert: how to force application to provide value for int primary key?

[Hick Gunter]

BTrees as per concept are aware of sorted load vs random load and will adjust 
their node splitting algorithm accordingly (e.g. 90/10 split for ordered and 
50/50 for random load). The rationale being that an ordered load tends to 
indicate that new data is unlikely or added at the end, whereas a random load 
suggests that additional data will be added with in-between keys.

So it really depends on the order of adding records more than the presence or 
absence of a rowid.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Dienstag, 10. September 2019 16:50
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] insert: how to force application to provide 
value for int primary key?

On Tue, Sep 10, 2019 at 4:32 PM R Smith  wrote:

> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there,
> > and as typical of SQLite is often fast enough for most DB sizes, but
> > it *can* matter. Just
> be
> > aware of it.
>
> That is interesting - could you elaborate on how exactly lots of
> inserts would be worse in WITHOUT_ROWID tables than in normal tables?*
>

WITHOUT ROWID tables have a "real" natural key, which as such is much more 
likely to have a random distribution, resulting in splicing new rows all over 
the place. While regular tables typically have an auto-incrementing ROWID, 
acting as the B-Tree key, which means new rows are mostly inserted "at the 
end", in the last page, yielding fewer non-leaf page rewrites I'm guessing.

You're probably right to challenge what I wrote. It's mostly intuition, not 
hard-facts, so I could well be completely off-base. I may also have read the 
above when I looked into Oracle IOTs (Index Organized Tables), which are 
similar I think (again, perhaps I'm wrong).

I guess one would need to run experiments with a shim VFS to track IO to verify 
my claims above :). For now, just take it with a grain of salt or just plain 
assume it was talking out of my a..! --DD 
___
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

Re: [sqlite] [EXTERNAL] Endless loop in update recursive query with UNION ALL

[Hick Gunter]

Does your "parent" relationship contain (at least one) loop(s)? UNION will 
break the loop by eliminating already visited rows, whereas UNION ALL will run 
faster precisely because it does not keep track of the visited rows.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Slava G
Gesendet: Freitag, 06. September 2019 10:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Endless loop in update recursive query with UNION 
ALL

I have query that enters into endless loop in update recursive query with UNION 
ALL, but when I remove ALL from the UNION it's works fine :
WITH recursive parentitems(itemid) AS (
VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a")

*UNION ALL *
SELECT snapshotdata.itemid
FROM   snapshotdata,
   parentitems
WHERE  folder = parentitems.itemid
ANDifnull(deleteddate ,99) = 99 )
UPDATE snapshotdata
SETdeleteddate = 20190903142833
WHERE  itemid IN parentitems
ANDbackupdate < 20190903142833
ANDifnull(deleteddate,99) = 99

As far as I understand ALL in UNION should provide better performance, but 
somehow it enters into endless loop and eat all computer resources.

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

Re: [sqlite] [EXTERNAL] What concurrency level is of sqlite?

[Hick Gunter]

Just the same.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Dienstag, 03. September 2019 22:14
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] What concurrency level is of sqlite?

Hi,

In other words, if two processes write to the same sqlite file but to different 
tables, will one wait for the other? What if to the same table but different 
rows? Thanks.

--
Regards,
Peng
___
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

Re: [sqlite] [EXTERNAL] How to create a new table or overwrite an existing table in one command?

[Hick Gunter]

There is only IF NOT EXISTS in the CREATE TABLE command. This assumes that you 
may want to keep a pre-existing table and the data it contains.

If you don't care about any old table or ist contents, just issue DROP TABLE IF 
EXISTS and CREATE TABLE in a single transaction.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Dienstag, 03. September 2019 21:58
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] How to create a new table or overwrite an existing 
table in one command?

Hi,

If I try to create a table that already exists, sqlite will give me an error. 
Is there way to issue one command to create a table, but if there is already a 
table with the same name, drop it then create the new table? Thanks.

--
Regards,
Peng
___
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

Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/

[Hick Gunter]

The base table is also a virtual table (we have nearly no native SQLite tables) 
that stores variable length, variable content logfiles and supports access via 
record offset, serial number and stored datetime. The effort of decoding 
specific attributes is significant (sequential read and decode is 10% CPU and 
90% IO bound). Certain well defined and commonly used discrete attributes (e.g. 
ACK/NAK, transaction type, retailer number,..) were lifted from the records via 
batch programs and inserted into the bitmap index. This reduced the numbe rof 
records to be read from disk and decoded by a factor of about 1000-4000 with 
proportional performance gains.

Cardinality of columns ranged from 2 (true/false) to several thousand; the 
software does a very good job of compressing and processing the bitmaps, 
leading to high performance.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Montag, 02. September 2019 13:50
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/

On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter  wrote:

> Back in 2011 I implemented a virtual table using the "fastbit" library
> by John Wu of the Lawrence Berekely National Laboratory. This allowed
> selects of the form
>
> SELECT ... FROM  WHERE rowid IN (SELECT rowid FROM
>  WHERE );
>

Did it work well? Did you get any speedup compared to a normal BTree index?
Available anywhere?
How low the cardinality of indexed columns value-space needs to be to benefit 
from a bitmap index?


> provided that the data had been inserted before by running
>
> INSERT INTO  SELECT rowid,;


Custom (user-defined) indexes is an area that I'd welcome in SQLite. You can 
work around it as you did above, but that implies the index maintenance rests 
on the user's shoulders. While it would be relatively easy I suspect for SQLite 
core to notify a custom index of table changes.

Conversely, you can't use SQLite (sole for now) BTree indexes with a virtual 
table, AFAIK, (I have a doubt all of a sudden...), the vtable must do all the 
indexing itself.
___
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

Re: [sqlite] [EXTERNAL] INSERT vs BEGIN

[Hick Gunter]

For batch loading via script, you should limit the number of values per 
statement (SQLite compiles each statement into memory) and per transaction 
(SQLite needs to write to disk after a certain number of pages are modified).

For batch loading via program, you can prepare the insert statement for 1 row 
once and bind the values for ech row to be inserted.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Grincheux
Gesendet: Sonntag, 01. September 2019 08:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] INSERT vs BEGIN

What is the best ?

INSERT INTO artists (name) VALUES
("Gene Vincent")
("John Lennon")
("Ringo Starr")
("Paul McCartney")
.
.
.
("Moi _ Me");

I want to insert 1 000 000 records.
The other manner tot do is creating a transaction with one insert command by 
line.
My question is what is the best thing to do ?
Or having a transaction for the first sample?

One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because 
others threads needs to access to tables.

Please help me.

Grincheux




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

Re: [sqlite] [EXTERNAL] char(0) with SQLite

[Hick Gunter]

Dimensions are ignored by SQLite. A field defined CHAR(0) can hold any length 
(up to the internal limit) of string. SQlite will only store the actual length 
of the string plus its contents, no space  is wasted.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Grincheux
Gesendet: Sonntag, 01. September 2019 08:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] char(0) with SQLite

Into my db I store passwords having differents lengths (from 1 to 50).
I don't want to give the max size that woud be using space I don't need.
I found that sqlite permits char(0) but what is the incidence for my db.



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

Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/

[Hick Gunter]

Back in 2011 I implemented a virtual table using the "fastbit" library by John 
Wu of the Lawrence Berekely National Laboratory. This allowed selects of the 
form

SELECT ... FROM  WHERE rowid IN (SELECT rowid FROM  
WHERE );

provided that the data had been inserted before by running

INSERT INTO  SELECT rowid,;



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Montag, 02. September 2019 10:59
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] http://roaringbitmap.org/

On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch 
wrote:

> Hi, I think that SQLite use some bitmap indexes


Not that I know of, but I don't know the full source code. Maybe FTS[345] 
do/es, but SQLite itself only uses BTree-indexes AFAIK.


> and this here might be of interest if not already used/known:
> http://roaringbitmap.org/ I think it’s from the same guy how did SIMDJSON.
>

Thanks for sharing. --DD
___
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

Re: [sqlite] [EXTERNAL] Non-keyword quoted identifiers parsed as string literals

[Hick Gunter]

This is documented behaviour. Use single quotes for literal strings. SQLite 
will assume you meant 'literlal' if your write "literal" and there is no column 
of that name. There is no need to quote names in SQLite unless the name 
contains non-alpha characters.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von William Chargin
Gesendet: Sonntag, 01. September 2019 08:26
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Non-keyword quoted identifiers parsed as string 
literals

I tracked down a perplexing issue to the following behavior:

sqlite> CREATE TABLE tab (col);
sqlite> SELECT nope FROM tab;  -- fails; good
Error: no such column: nope
sqlite> SELECT "nope" FROM tab;  -- works?
sqlite> INSERT INTO tab (col) VALUES (77);
sqlite> SELECT col FROM tab WHERE nope IS NOT NULL;  -- fails; good
Error: no such column: nope
sqlite> SELECT col FROM tab WHERE "nope" IS NOT NULL;  -- works?
77

It seems that "nope" is being interpreted as a string literal here, while 
quoted names of valid columns are not:

sqlite> SELECT "nope", "col" FROM tab;
nope|77

I see that this is discussed briefly in the documentation, though the exception 
as written only applies to quoted keywords, which "nope" is
not: 

But it seems especially surprising that the parse tree should depend on the 
actual identifier values and table schemata, making the grammar not 
context-free.

Is this working as intended? Are there plans to make SQLite reject such 
examples as malformed queries instead of implicitly coercing?

My `sqlite3 --version`:

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
___
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

Re: [sqlite] [EXTERNAL] ORDER BY is ignored during INSERT INTO

[Hick Gunter]

Why would you want to do this?

If you require a SELECT to return rows in a certain order, you need to specify 
ORDER BY on the SELECT statement. And not rely on ascending insert time or any 
other visitation order effect.

Additionally - unless specific precautions are taken - sorted insert results in 
a half empty tree structure.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von André Borchert
Gesendet: Sonntag, 25. August 2019 23:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] ORDER BY is ignored during INSERT INTO

Hello,

I try to copy one table into a second identical one. Once the second table is 
created I want to move the content over sorted by ASC.

The issue is that the ORDER BY statement gets ignored during a INSERT INTO:

INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE 
CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

When the SELECT query is executed alone, the content is sorted fine as expected.

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

Re: [sqlite] [EXTERNAL] Attached databases and union view.

[Hick Gunter]

I think you are looking for UNION ALL to avoid creating an ephemeral table to 
implement the implied DISTINCT

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter da Silva
Gesendet: Donnerstag, 22. August 2019 17:28
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Attached databases and union view.

Have an existing application that's pushing the limit on how fast it can read 
data and add it to the database, and thinking of sharding the database file so 
I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0; ATTACH DATABASE 'shard1.sqlite' as 
shard1; ...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table ...;

What's the best way to construct this union view so the query optimizer won't 
be horribly confused? If I run something like "SELECT count(*) FROM 
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower than 
the same query against the original main_table. Running the query against each 
shardN.main_table it's actually faster (in total time for all queries in 
sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best query 
performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard databases.
___
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

Re: [sqlite] [EXTERNAL] Re: Schema updates across threads in WAL & multithread mode

[Hick Gunter]

As already stated, this looks like you have at least one transaction underways. 
Your schema change will become visible only after

1) they are committed on ther "writer" connection AND
2) a new transaction is started on the "reader" connection

If your "readers" are failing to reset or finalize any of their statements, the 
corresponding connection will have an active transaction that you may be 
unaware of and that prevents the schema change being seen.

For diagnostic purposes ONLY, try opening with shared cache enabled and execute 
pragma read_uncommitted. If the problem goes away, then you are keeping 
transactions open longer than you think.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ben Asher
Gesendet: Freitag, 16. August 2019 21:49
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Schema updates across threads in WAL & 
multithread mode

To clarify, we add a column on our writer connection, and then "SELECT * FROM 
table" on the reader connection does not include the column that was added.

Ben

On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:

> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
> schema update (adding a column to a table) on our writer connection,
> but then the schema update isn't immediately available on the
> read-only connections that we use on other threads, which causes a
> crash in our application (app expects the column to exist at that
> point). I've verified that the column does indeed get added, and
> everything works fine after restarting the application (i.e. all
> connections loaded fresh pickup the schema update).
>
> Is there something we need to do proactively to ensure that schema
> update appears immediately from other threads?
>
> Some notes about our setup:
>
> sqlite 3.27.2
> Using multithread mode (SQLITE_OPEN_NOMUTEX) Using WAL mode
>
> Thanks!
>
> Ben
>


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

Re: [sqlite] [EXTERNAL] Re: Getting a notification when a write lock is released.

[Hick Gunter]

Maybe you are looking for semaphores. These can be tricky to use correctly in 
the case of cooperating processes, where you have to handle the case of the 
current owner of the semaphore terminating within the monitored section of code.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von test user
Gesendet: Freitag, 16. August 2019 01:35
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Getting a notification when a write lock is 
released.

Thanks Simon,


> You can use any other combination that suits you.  Perhaps set a short
> timeout, after which SQLite calls your busy handler, which can do
> whatever it wants then return SQLITE_BUSY to your program.  When the
> short timeout gets exhausted, SQLite calls your own busy handler, and
> /that's/ your notification.
>


> None of these would use the hint system or try to monitor files directly.
> As you see, there's an existing way to monitor locks.  You don't need
> to abuse a different notification which isn't really suited to this purpose.



The reason for the notification is to minimize time spent waiting.

Lets say you can categorize the time spent waiting for a lock into these
sets:

- A. Time waiting whilst lock is locked.
- B. Time waiting whilst lock is unlocked.


With the current locking system, if you have many processes contending for 
write locks, time spent in set B will be > 0.

With a notification when the lock is released, B can equal 0 for the duration 
of the system uptime.

I understand the current lock system works well, and that this is a more 
complex system.


Regarding your short timeout suggestion: Is polling the lock frequently in very 
short intervals resource intensive?


Is SQLITE_ENABLE_SETLK_TIMEOUT the best way to try and implement this in my own 
library?


On Thu, Aug 15, 2019 at 11:08 PM Simon Slavin  wrote:

> On 15 Aug 2019, at 10:43pm, test user 
> wrote:
>
> > Currently the API lets you set a timeout. Does this just retry again
> after a set amount of time?
>
> SQLite's built-in busy handler (which it uses unless you tell it to
> use yours instead) repeatedly backs off and retries until the timeout
> you set is complete.  The amount of time it backs off for is decided
> internally and you cannot depend on it being the same amount of time
> every time.  The entire time the busy handler takes should never much
> exceed the amount of time you set as your timeout.
>
> > But I was thinking more along the lines of keeping BUSY and the
> > current
> locking system as is, but using the notification as a hint, that is
> possibly unreliable.
> >
> > E.g. if BUSY (try again (after x seconds OR when hint arrives))
>
> The assumption behind SQLite is that you will do one of three things.
> The first of them is what most users do:
>
> 1) Set a long timeout, but not set your own busy handler.  Then let
> SQLite handle the wait-and-retry loop until it finally gives up and
> returns SQLITE_BUSY.  Then your program announces that the database is
> inaccessible and quits (or gives up that function).
>
> 2) Set no timeout and no busy handler.  Receive and handle SQLITE_BUSY
> by doing whatever the program finds suitable.  Perhaps implement your
> own wait-and-retry loop, perhaps do something a lot more
> sophisticated, appropriate to whatever the user expects.
>
> 3) Set no timeout and implement your own busy handler.
>
> You can use any other combination that suits you.  Perhaps set a short
> timeout, after which SQLite calls your busy handler, which can do
> whatever it wants then return SQLITE_BUSY to your program.  When the
> short timeout gets exhausted, SQLite calls your own busy handler, and
> /that's/ your notification.
>
> None of these would use the hint system or try to monitor files directly.
> As you see, there's an existing way to monitor locks.  You don't need
> to abuse a different notification which isn't really suited to this purpose.
> ___
> 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


___
 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

Re: [sqlite] [EXTERNAL] Re: Documentation update request

[Hick Gunter]

Reminds me of "... two mice ran up the clock, the clock struck one, and the 
other escaped with minor injuries"

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kevin Benson
Gesendet: Donnerstag, 15. August 2019 20:40
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Documentation update request

On Thu, Aug 15, 2019 at 2:33 PM Jose Isaias Cabrera 
wrote:

> Richard Hipp, on Thursday, August 15, 2019 01:32 PM, wrote...
> >
> > On 8/15/19, Simon Slavin, on
> > > On 15 Aug 2019, at 5:20pm, Richard Damon, on
> > >
> > >> You under quote, the faq says it “can not be changed (except
> > >> under extra-ordinary conditions).”, and those extra-ordinary
> > >> conditions are
> a
> > >> link to the second section you mention.
> > >
> > > I didn't notice that, for some reason.  Thanks for the correction.
> >
> > You didn't notice it because I only added it moments ago, in
> > response to your documentation update request.
>
> Oh! Trickery!
>
>
Dickery (sometimes a nickname for RICHARD ;-) Doc (...umentation; the something 
Richard "Dick" Hipp was doing ;-) 
___
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

Re: [sqlite] [EXTERNAL] Re: Programming methodology (was DEF CON (wasL A license plate of NULL))

[Hick Gunter]

How about

#define is_true(tf) ((uintptr_t)0 != (uintptr_t)(tf))

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 13. August 2019 22:42
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Programming methodology (was DEF CON (wasL A 
license plate of NULL))

If I were to have coded that junk (and I do see it too many times to count), I 
would have coded it even junkier, as in

bool is_true (bool tf)
{
if (tf == true) return true; else return false; }

If it's single statement following an if and that statement isn't beyond 80 
characters, I will code it as a single line

bool is_true (bool tf)
{
if (tf == true) { return true;}
return false;
}

I would normally comment the method with something like

// SWEET MOTHER OF MOSES, WHO CODED THIS
// But since it is existing code, I have chosen not to touch it and kick the 
can down the road.
// I don't want to be responsible for retesting the 97 million functions that 
actually employ // this piece of




On Tue, Aug 13, 2019 at 2:52 PM Keith Medcalf  wrote:

>
> On Tuesday, 13 August, 2019 13:17, Jose Isaias Cabrera
> 
> wrote:
>
> >James K. Lowden, on Tuesday, August 13, 2019 12:31 PM, wrote...
>
> >> On Mon, 12 Aug 2019 14:14:08 -0600 "Keith Medcalf", on
>
> >>> Perhaps I am just lazy but I see no point in engaging in extra
> >>> work for no advantage
>
> >> bool
> >> is_true (bool tf) {
> >> if (tf == true) {
> >> return true;
> >> }
> >> return false;
> >> }
>
> >Completely, completely off the subject, but since I see this code
> >here, and I have always wanted to ask this...
>
> >When I started programming, back in 1982, my teachers taught me to
> >match my end bracket to the same column where the beginning bracket
> >was.  And they explained the whole theory behind it, which I think
> >it's true, to today.  For example the above code, I would have
> >written it this way:'
>
> >bool is_true (bool tf)
> >{
> >if (tf == true)
> >{
> >return true;
> >}
> >return false;
> >}
>
> >Where, the brackets, begins/ends, would match the same column.  When
> >did this ideology change?  I see all of you smart programmers using
> >this non-column matching behavior, and I ask myself why?  Thoughts?
> >Or not. :-)  Thanks.
>
> It is a matter of taste I suppose, since there are numerous bits of
> software which can prettify various languages to a number of different
> formats.  The primary reason I have heard putting the opening brace on
> the same line is that it takes less space on the screen, and after all
> we can only afford 5 line monitors, am-I-right?
>
> Personally I like the format where the braces line up with the start
> of the statement that they belong to and appear on a line by
> themselves, and the contained block is indented.  Then again I can
> afford an absolutely humongous monitor that can display about 50 lines per 
> page.
>
> Some people are severely allergic to white-space and so eliminate
> every non-required space/tab character all line-feeds/carriage-returns
> that are not within a quoted string and write their software as one
> big never-ending single line of code 40 miles long.
>
> There are also some wierd formats that some seem to like as well where
> they half-indent the braces and other such malarky.
>
> It is all a matter of taste and what you can see easily.  I also tend
> to use blocks around code that technically does not need them (as in
> the above
> example) because it makes it easier to see what is going on -- the
> visual appearance matches the parse tree generated by the compiler as it were.
> Only the folks that do not use blocks obviously are struck by decades
> old code editing errors that they did not intend (and we have had a
> few of those in the last couple of years where the "visual depiction"
> did not match the "computer generated parse tree" ...
>
> --
> 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


___
 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

Re: [sqlite] [EXTERNAL] Re: DEF CON (wasL A license plate of NULL)

[Hick Gunter]

But surely any compiler worth ist salt would optimize away all of that code and 
just use the result of the expression given as argument in the call ;)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von James K. Lowden
Gesendet: Dienstag, 13. August 2019 18:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] DEF CON (wasL A license plate of NULL)

On Mon, 12 Aug 2019 14:14:08 -0600
"Keith Medcalf"  wrote:

> Perhaps I am just lazy but I see no point in engaging in extra work
> for no advantage

bool
is_true (bool tf) {
if (tf == true) {
return true;
}
return false;
}

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

Re: [sqlite] [EXTERNAL] Re: Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Hick Gunter]

OK now I see. I think this is a major bug in the code generator because it 
breaks documented behaviour.

In lines 8 to 12, SQlite is building records for an ephemeral "to do" table.

Line 8: retrieve the value of the primary key with OPFLAG_NOCHNG set and store 
result in R6
Line 9: store the replacement string into R7
Line 10: retrieve hte value of the primary key without OPFLAG_NOCHNG and store 
result in R4
Line 11: Copy R6 to R5 <-- THIS IS WRONG, it should be copy R4 to R5 as per 
documentation
Line 12: Make a record of R4 through R7

This sets up a later (lines 17-21) call to VUpdate with

Argc=4
Argv[0] = R4 "primary key"
Argv[1] = R5 "copy of unchanged NULL" instead of "copy of primary key"
Argv[2] = R6 "unchanged NULL"
Argv[2] = R7 "new value"

And that explains the strange behaviour you noticed.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kevin Martin
Gesendet: Dienstag, 13. August 2019 17:47
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Correct use of 
sqlite3_vtab_nochange/sqlite3_value_nochange


> On 13 Aug 2019, at 14:08, Richard Hipp  wrote:
>
> I think that is correct.
>

Great, thanks.

> But it never occurred to me that somebody might do this on the PRIMARY
> KEY.  I don't see any reason why it wouldn't work, though.

I have a c++ interface built on top of the virtual table api which multiple 
modules are then built on top of, it doesn't know which columns are 
large/expensive, so the idea was just to use call sqlite3_vtab_nochange for all 
of them, which includes the primary key.


> On 13 Aug 2019, at 13:00, Hick Gunter  wrote:
>
> Very strange and AFAICT not documented. I would not have though that calling 
> sqlite3_value_nochange on argv[1] was even legal, given that it would 
> correspond to field number -1. Could you provide an "explain" (SQlite 
> bytecode program dump) of your statement?

See below JSON for the explain as the table is not accessible in the command 
line tool.

Thanks,
Kev

---

[
  {
"sql":"pragma table_info(modeloption_vt_writable);",
"cols":["cid", "name", "type", "notnull", "dflt_value", "pk"],
"time":0.016,
"results":[
  ["0", "option", "text", "1", "", "1"],
  ["1", "value", "text", "0", "", "0"]
]
  },
  {
"sql":"\n\nexplain update modeloption_vt_writable set value = 'v' where 
option='o';",
"cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
"time":0,
"results":[
  ["0", "Init", "0", "25", "0", "", "00", "Start at 25"],
  ["1", "OpenEphemeral", "2", "4", "0", "", "00", "nColumn=4"],
  ["2", "VOpen", "1", "0", "0", "vtab:C3289DFC0", "00", ""],
  ["3", "Integer", "0", "10", "0", "", "00", "r[10]=0"],
  ["4", "Integer", "0", "11", "0", "", "00", "r[11]=0"],
  ["5", "VFilter", "1", "16", "10", "", "00", "iplan=r[10] zplan=''"],
  ["6", "VColumn", "1", "0", "12", "", "00", "r[12]=vcolumn(0); 
modeloption_vt_writable.option"],
  ["7", "Ne", "13", "15", "12", "(BINARY)", "52", "if r[12]!=r[13] goto 
15"],
  ["8", "VColumn", "1", "0", "6", "", "01", "r[6]=vcolumn(0)"],
  ["9", "String8", "0", "7", "0", "v", "00", "r[7]='v'"],
  ["10", "VColumn", "1", "0", "4", "", "00", "r[4]=vcolumn(0)"],
  ["11", "SCopy", "6", "5", "0", "", "00", "r[5]=r[6]"],
  ["12", "MakeRecord", "4", "4", "8", "", "00", "r[8]=mkrec(r[4..7])"],
  

Re: [sqlite] [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Hick Gunter]

Very strange and AFAICT not documented. I would not have though that calling 
sqlite3_value_nochange on argv[1] was even legal, given that it would 
correspond to field number -1. Could you provide an "explain" (SQlite bytecode 
program dump) of your statement?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kevin Martin
Gesendet: Dienstag, 13. August 2019 13:23
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Correct use of 
sqlite3_vtab_nochange/sqlite3_value_nochange


> On 12 Aug 2019, at 07:53, Hick Gunter  wrote:
>
> You did not state your argc and argv[0] values,

Apologies, it is a 2 column table. Full details are:

- argc is 4
- argv[0] is the value of the primary key for the row I want to update.
- argv[1] is SQLITE_NULL, but as described, sqlite3_value_nochange(argv[1]) 
returns true
- argv[2] is the same as argv[1] (null, but sqlite3_value_nochange returns true)
- argv[3] is the new value of the non-primary key column.

> so looking at the documentation would suggest that SQLite is actually asking 
> for an INSERT into a WITHOUT ROWID virtual table.

I see that is what the documentation leads you to believe, but I can assure you 
I am exciting an update of the form

update t set notprimarykey='some value' where primarykey='other value'

Removing the sqlite3_vtab_nochange from the xColumn call gets the documented 
behaviour. If I keep the sqlite3_vtab_nochange and change my code in xUpdate as 
described in the last email, everything seems to work. I just want to check it 
is correct.

Thanks,
Kev
___
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

Re: [sqlite] [EXTERNAL] Why does WAL prevent the need for async IO?

[Hick Gunter]

I don't think so.

Async IO module creates a queue of pages that will be written to the database 
file on disk according to available IO bandwidth.

WAL mode creats a queue of pages from committed transactions that are written 
to the database file on disk according to available IO bandwidth.

Both allow the writer to not have to wait until the data physically hits the 
disk. Introducing a second level of buffering is not likely to help with speed 
but multiplies the risk of losing durability. Since WAL mode in standard sync 
does not call fsync() during commit, a crash could prevent the commit record 
reaching the WAL file. And since async IO stores writes in a queue and pretends 
they are alredy completed, a crash during a checkpoint operation could cause 
(possibly very much older) writes to be lost even though the WAL file has 
recorded them as completed.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von test user
Gesendet: Dienstag, 13. August 2019 11:44
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Why does WAL prevent the need for async IO?

Hello,

On this page:
https://www.sqlite.org/asyncvfs.html

Quote: The use of WAL mode largely obviates the need for this asynchronous I/O 
module.


The WAL mode does not change the fact that these operations will still block 
the application process that embeds SQLite:

   - Slow read queries.
   -  A large amount of writes.



So would something like the original async module still be useful, even with 
WAL mode?


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

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

[Hick Gunter]

So how do you propose to have consistency and isolation if SELECT does not 
create an automatic transaction if no explicit transaction exists?

Consider:

SELECT  FROM ;
BEGIN;
UPDATE  SET field =  +1;
COMMIT;

If the SELECT and UPDATE statements are not part of the same transaction, there 
is no guarantee that  is still valid by the time you use it in the 
UPDATE.

Or even better:

SELECT  FROM ;
SELECT  FROM ;

How do you know that the expression  +  ever had that specific 
value (either one, or both, could have been changed between the two read 
operations)?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 11:01
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a 
simple SELECT query -- Documentation needed!

PS:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y 
> makes a changes to the database using UPDATE.


Same goes for this example! We did not start a read transaction with BEGIN. 
This part says that you need to start a read transaction with BEGIN, which we 
did not do, and the other part says that SELECTs do
*NOT* start an automatic transaction.

So is this maybe even a bug in sqlite? Or the documentation is incorrect?

kind regards,
Kira Backes
___
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

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

[Hick Gunter]

Works as intended.

"our code base does not use transactions at all" does NOT mean that there are 
no transactions, just that SQLite uses *implicit* transactions, i.e. every 
statement is in it's own transaction.

"we can share a connection between threads as long as we don't read/write into 
the same table at the same time" is NOT a correct assumption. The assertion you 
need to prove for SQLite running in multi-threaded mode is "we can share a 
connection between threads as long as the connection is not used simultaneously 
in two or more threads".

What you program is doing is guaranteed to provoke SQLITE_BUSY_SNAPSHOT errors.

Your first thread is creating an implicit transaction on connection FIRST by 
reading from table FIRST.
Your second thread is creating an implicit transaction on connection SECOND by 
writing into table SECOND. The implied COMMIT "invalidates" the snapshot on 
connection FIRST.
Your third thread now attempts to insert into table THIRD on connection FIRST. 
But connection FIRST is within an READ transaction started by your first 
thread, so it has to escalate it's transaction to a WRITE transaction. But 
because it is in an "invalid" snapshot, it cannot do so.

This is well documented behaviour.

https://sqlite.org/isolation.html

"Another example: X starts a read transaction using BEGIN and SELECT, then Y 
makes a changes to the database using UPDATE. Then X tries to make a change to 
the database using UPDATE. The attempt by X to escalate its transaction from a 
read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT 
error because the snapshot of the database being viewed by X is no longer the 
latest version of the database. If X were allowed to write, it would fork the 
history of the database file, which is something SQLite does not support. In 
order for X to write to the database, it must first release its snapshot (using 
ROLLBACK for example) then start a new transaction with a subsequent BEGIN. "

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 09:33
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple 
SELECT query -- Documentation needed!

Dear sqlite mailing list,

I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading the 
documentation did not help me. Our code base does not use transactions at all 
(we have a segmented code base protected by mutexes for a whole section, so 
reads/writes do not conflict ever). We neither had a crashing sqlite connection 
nor a corrupted database file. Even reading everything in isolation did not 
help me, since we're supposed to be able to share a connection between threads 
as long as we do not read/write into the same table at the same time, which we 
were able to ensure due to the section mutexes.

After thinking a very long time about this I found the reason: You absolutely 
can not share a WAL connection between threads or risk SQLITE_BUSY events. Yes, 
you heard right. If connection A runs a SELECT query in table A, then 
connection B inserts something into table B, and then you try in a concurrent 
thread to INSERT into table C using connection A you will get 
SQLITE_BUSY_SNAPSHOT errors until all SELECT queries are finished on connection 
A (in my case this took about a minute because I was reading some cache tables 
with several threads, so there was never a second where all queries were 
finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes even 
though I never used transactions anywhere... Just because I had some long 
running SELECT statements in unrelated tables it made the connection completely 
unusable.

This is not a theoretical case, this can happen *VERY* easily and as far as I 
can tell this is not documented anywhere (and believe me, I've ready nearly 
every single page of the sqlite3 documentation). So you should really really 
document this very easy to trigger case.

And if you don't believe me: since I'm a nice girl I've written a unit test 
(C++, catch2) for this which reliably reproduces this behavior:


TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]") {


  std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"};
  if (std::filesystem::exists(db_name)) {
REQUIRE(std::filesystem::remove(db_name));
  }



  std::mt19937_64 engine{std::random_device{}()};
  std::uniform_int_distribution u(INT64_MIN, INT64_MAX);

  auto open_db_fn = [&]{
sqlite3* handle;

REQUIRE(sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK);
REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA 
synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr,
nullptr) == SQLITE_OK);
REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK);

return handle;
  };



  std::string insert_into_FIRST = "INSERT 

Re: [sqlite] [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Hick Gunter]

To correctly determine what SQLite is asking of your xUpdate routine requires 
looking at argc, argv[0] and possibly argv[1] (if argc > 1).

You did not state your argc and argv[0] values, so looking at the documentation 
would suggest that SQLite is actually asking for an INSERT into a WITHOUT ROWID 
virtual table.

https://sqlite.org/vtab.html#xupdate

"INSERT: ... The argv[1] will be NULL for a WITHOUT ROWID virtual table, in 
which case the implementation should take the PRIMARY KEY value from the 
appropriate column in argv[2] and following."


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kevin Martin
Gesendet: Samstag, 10. August 2019 16:29
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Correct use of 
sqlite3_vtab_nochange/sqlite3_value_nochange

Hi,

I have a without rowid virtual table with an implementation of xColumn that 
begins with

if(sqlite3_vtab_nochange(ctx)) return SQLITE_OK;

If I try to perform an update on this table that doesn't involve a primary key 
change, then my understanding from the documentation is that xUpdate will be 
called and the value of argv[0] and argv[1] will be the same. What I am seeing 
is that argv[1] is set an sql null value, although when I call 
sqlite3_value_nochange(argv[1]) I do get true returned.

Am I therefore right in thinking that the correct detection of whether there is 
an update without a primary key change when using sqlite3_vtab_nochange is 
actually

sqlite3_value_nochange(argv[1]) || values_are_equal(argv[0], argv[1])

Thanks,
Kev
___
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

Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings

[Hick Gunter]

I see two subproblems in this query

a) estimating total electricity consumption for points in time that do not have 
an entry
b) generating regular points in time

ad a) assume a linear consumption of power between two measurements

So for a time tx that is between ta and tb with values of pa and pb 
respectively, px = pa + (tx -ta) * (pb -pa) / (tb - ta)

You only need to extrapolate if you have a point in time that has measurements 
only on one side. And you have to decide which average consumption to use 
(daily/weekly/monthly/seasonal/yearly average) for extrapolation.

tx < ta : px = pa + (ta -tx) * (pb -pa) / (tb -ta)
tx > tb: px = pb + (tx -tb) * (pb -pa) / (tb -ta)

ad b) generate regular points in time

Use a recursive CTE or the sequence generator eponymous table for current day = 
(starting day + sequence number * 1 day)

Then join your extrapolation query to your time sequence generator for the 
results

Once you have accumulated enough mesurements, you could do a fourier analysis 
to quantify daily/weekly/yearly variations and a "base load"

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Petr Jakeš
Gesendet: Donnerstag, 08. August 2019 09:36
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] select for power-meter accumulated total readings

I am storing electricity consumption data to the sqlite.

The simple table to store kWh consumption looks like following example 
(accumulated total readings in each row - exactly as you see on your 
electricity meter):

|ID|timestamp|kWh   ||1 | 2019-07-31 14:24:25 | 270.8||2 |
2019-07-31 14:31:02 | 272.1||3 | 2019-08-01 06:56:45 | 382.5||4 |
2019-08-01 16:29:01 | 382.5||5 | 2019-08-01 20:32:53 | 582.5||6 |
2019-08-02 16:18:14 | 612.1|
|7 | 2019-08-08 07:13:04 | 802.7|
|..|.|..|


   - The data interval is not predictable (is random).
   - There can be a day with no records at all (if data transmission
   failure for example).
   - There can be many records with the identical (equal) power consumption
   (no energy consumption) for one or more days.

My question is how to write SQL select to get energy consumption for required 
interval summarized  by days, weeks or months ...

The real challenge is to get an average if for each day for days when records 
were not taken (in the example table days between ID 6 and ID7) - each day as a 
row.

It looks like simple question but I am pulling out my hair for two days to find 
a solution.
___
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

Re: [sqlite] [EXTERNAL] Re: Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

[Hick Gunter]

There is no decimal type in SQLite, and you are lucky that they are converted 
to string instead of real, because you cannot do proper (implied) decimal 
(point) arithmetic with real (ieee binary floating point) values. The rounding 
errors intruduced by binary floating point not beeing able to represent many 
decimal fractions exactly will go forth and multiply.

I suggest using integers to store the values and keeping track of their scale 
in a separate integer or implied in program logic (e.g. storing amounts in 
cents and remembering to divide by 100 for presentation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ainhoa B
Gesendet: Mittwoch, 07. August 2019 11:46
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Problem with int and DateTime types with 
EntityFrameWorkCore.Sqlite

And what about Decimal type? Because Decimals are converted to string. It's a 
problem because it forces the programmer to format read values from database 
and cannot use them directly...

Best regards

El mié., 7 ago. 2019 a las 10:15, Olivier Mascia ()
escribió:

> > Le 7 août 2019 à 09:43, Ainhoa B  a écrit :
> >
> > So, in SQLite, it doesn't matter if I create a table with a column
> > of
> int,
> > smallint or long type, it will always be trated as a INTEGER type of
> > 64 bits?
>
> Regarding integers, yes they are always signed 64 bits integers.  See
> https://www.sqlite.org/datatype3.html for more details.
>
> When physically storing the integer value, the internal format is
> varying on the magnitude of the value stored. There is nothing really
> lost
> (storage-wise) by using signed 64 bits integers at the interface
> level, even though your values would only be 0, 1, 3, and 12347.
>
> SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits
> payloads.  Values 0 and 1 even only store their type, with no payload.
>
> https://www.sqlite.org/fileformat2.html#record_format
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit
> besten Grüßen, Olivier Mascia
>
> ___
> 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


___
 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

Re: [sqlite] [EXTERNAL] mode insert dumps

[Hick Gunter]

You need to

.mode insert 
SELECT * FROM ;

Repeat for all your tables.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luca Ferrari
Gesendet: Freitag, 02. August 2019 10:04
An: SQLite 
Betreff: [EXTERNAL] [sqlite] mode insert dumps

Hi,
I've got a lot of database files that I would like to dump using INSERT 
statements.
unluckily, .mode insert does not work for me, or I'm not able to understand how 
it works.

sqlite> select mude_anno, mude_numero from catdaemo;
INSERT INTO table VALUES(2019,1161);

My questions are:
1) why is the insert operating against "table" instead of the real table I was 
selecting from (catdaemo)?
2) is there a way to force the INSER to have also the columns I selected 
listed? I would like something like:
INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161);

The reason is that I want to exclude automatic keys from my inserts because I'm 
going to pump those inserts into another database with automatic keys.

Thanks,
Luca
___
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

Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database

[Hick Gunter]

The error is due to a full disk. You should not be deleting files associated 
with an SQLite db file.

Have you tried running pragma integrity_check(); before the disk actually 
becomes full?

Other than corruption of the file, the two candidates are internal 
fragmentation (doing lots of INSERT and DELETE operations), which would be 
remedied by running VACUUM (NOTE: may require up to double the current file 
size); or just the sheer volume of data (doing lots of INSERTS and never 
DELETEing outdated rows), which you would need to handle in your application.

The fact that "backing up" (how? Export/import or the SQLite backup API?) the 
db file helps would indicate either fragmentation or corruption.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von bhandari_nikhil
Gesendet: Donnerstag, 01. August 2019 10:48
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database

This is my table, I just wanted to show the error that it is throwing. Is the 
error due to /tmp being full on my device ? Further debugging at our end showed 
that one way to replicate the problem is to remove the hot journal file in the 
middle of an insert transaction but we are not sure if that is the actual cause 
of the problem or not.

In the application, we have a logic to check the db file size and if it grows 
beyond a certain threshold, we would take a backup of the db file. But once we 
do the delete-the journal-in-the middle-of-a-transaction operation, this size 
check never passes and the db size keeps on growing. Why could that be ?



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

Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database

[Hick Gunter]

What is this tbllog table?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von bhandari_nikhil
Gesendet: Donnerstag, 01. August 2019 07:39
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Error in recover sqlite3 database

My sqlite3 database keeps on increasing in memory (although it is limited by 
memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it ? 
BTW, even after a reboot of the device, we land up in the same situation after 
some time. Is there some problem with the journal ?



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

Re: [sqlite] [EXTERNAL] storing blobs in a separate table

[Hick Gunter]

SQLite stores rows in a compressed format that requires decoding. To access the 
nth field, all the fields that come before it need to be decoded. If there is a 
large blob stoed in a blob field, any field after that will suffer a 
performance penalty (unless, of course, both fields are required). "Any field" 
includes another blob field in the previous sentence.

So if you always want to "SELECT data1, data2, picture FROM blob_table WHERE 
..." then it is ok, but if you want to "SELECT picture FROM blob_table WHERE 
..." then that will be nearly just as slow as the first statement.

Consider adding a blob_type field if you need to store more than one blob per 
associated record.

CREATE TABLE blob_data (rec_id INTEGER, blob_type INTEGER, blob_data BLOB, 
PRIMARY KEY (rec_id,blob_type) ) WITHOUT ROWID;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rael Bauer
Gesendet: Mittwoch, 31. Juli 2019 13:49
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] storing blobs in a separate table

Hi,

I am aware that in sqlite for a given "entity" one should generally store blobs 
in a separate table to the other standard fields (such as text/number etc..)

So for maximum efficiency, in such a blob table, is there a problem storing 
multiple blob fields?

E.g.  data1, data2, picture, etc..

or should the blob table only have 1 blob field? (perhaps with a second field 
indicating what is stored in the blob, or store different blobs in different 
tables?)

Thanks

Rael

___
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

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Hick Gunter]

Sorry if I came across impolite or destructive. The way you are attempting to 
do what you feel you need to raises a whole bunch of red flags, which I have 
been trying to explain, based on 40+ years of coding experience from assembler 
upwards (including COBOL, FORTRAN, BASIC, FORTH, PASCAL, C, lex, yacc and Perl).

As others have pointed out, it is safe and reasonable to write your own 
replacement for sqlite3_exec(), as long as you call it something else and stick 
to the documented interface.

Your diagnosis ("lots of time is wasted converting numeric data to text and 
back again") is correct, I only happen to think that your proposed therapy 
might easily kill the patient (and maybe some bystanders too).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 16:40
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

I really wish to keep the argument polite and constructive, so please dont get 
me wrong if I reply, I just want to understand what you are referring to, 
realize and evaluate which is the best way to go for me, not for the sqlite 
library, that's why I writing to the sqlite library.

Il giorno mar 30 lug 2019 alle ore 15:50 Hick Gunter  ha
scritto:

> f) There are exactly 2 documented functions in your code. Did you not
> read their documentation???
>
> See https://sqlite.org/c3ref/column_blob.html
>
> " After a type conversion, the result of calling sqlite3_column_type()
> is undefined, though harmless. Future versions of SQLite may change
> the behavior of sqlite3_column_type() following a type conversion."
>

which type conversion are you referring to? the second one erroneously called 
in the example code? I do understand the 'type conversion' to be one of the six 
functions listed in the page you linked (which was read).
The statement before the one you quoted specifically says:  "The return value 
of sqlite3_column_type() can be used to decide which of the first six interface 
should be used to extract the column value". Isnt this exactly what I want to 
be doing? Read the column type from the statement, according to the return 
value call the relevant extraction function. The documentation describes 
'automatic conversions' being performed if I am trying to extract a datatype 
that is different from the internal datatype.
But this is exactly what I want to stay away from, unless, of course I am 
missing something, which I would very like understand.


> b) Breaking encapsulation includes referencing internal .h files in
> your own code. You should be using sqlite3.h *only*
>

I am not referencing ANY internal.h file in my own code. I am just using the 
amalgamation distribution, and I am writing code in sqlite.c, not in my own 
source files, so nobody outside of sqlite.c calls any function or uses any 
datatype that is not declared in sqlite.h.



> You do realise that declared types are not enforced in SQLite? If
> somehow a real with a non-integer value got inserted into an integer
> field, the type of the returned value would still be real, but you
> would be expecting integer. And silently reading the bits of the
> double value as an integer, which would probalby cause a practically 
> untetectable error.
>

True, agreed. This is the reason why the types of each value being inserted 
into, updated and read from the database is strictly enforced outside of 
sqlite. To state in a cear way: SQLite might not enforce declared types, by the 
application around sqlite has been specifically designed to enforce different 
types.


>
> c) There is no guarantee that the internal fields referring to a
> previous state of a Mem structure are preserved across conversions.
>

why do you refer to a "previous state of a mem structure"? the code is being 
executed inside a custom sqlite_exec function, which mirrors the behaviour of 
the provided sqlite_exec function, except for the conversions to/from text 
(which return the pointer to the internal data type, without any conversion in 
case of Integer and float value), WAY before calling the statement 
finalization. The callback itself that must be provided to sql3_exec, is called 
before finalizing and freeing the memory allocated by the statement execution.


> I do hope you are statically linking your modified SQLite code to your
> application and not installing it as a shared image. In the latter
> case, some unsuspecting application might inadvertently stumble across
> your version of the interface, which no longer conforms to the
> published interface, and cause failures there. That would probably
> cause some really rave reviews. "I just installed XXX on my phone and
> it died" is not a viable recommendat

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Hick Gunter]

f) There are exactly 2 documented functions in your code. Did you not read 
their documentation???

See https://sqlite.org/c3ref/column_blob.html

" After a type conversion, the result of calling sqlite3_column_type() is 
undefined, though harmless. Future versions of SQLite may change the behavior 
of sqlite3_column_type() following a type conversion."

b) Breaking encapsulation includes referencing internal .h files in your own 
code. You should be using sqlite3.h *only*

You do realise that declared types are not enforced in SQLite? If somehow a 
real with a non-integer value got inserted into an integer field, the type of 
the returned value would still be real, but you would be expecting integer. And 
silently reading the bits of the double value as an integer, which would 
probalby cause a practically untetectable error.

c) There is no guarantee that the internal fields referring to a previous state 
of a Mem structure are preserved across conversions.

I do hope you are statically linking your modified SQLite code to your 
application and not installing it as a shared image. In the latter case, some 
unsuspecting application might inadvertently stumble across your version of the 
interface, which no longer conforms to the published interface, and cause 
failures there. That would probably cause some really rave reviews. "I just 
installed XXX on my phone and it died" is not a viable recommendation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 14:51
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Thanks for the response
you gave me the kind of answer I was looking for.


a) you are circumventing the intended interface
>

I know, I am aware, and that is exactly what I wanted to do; I Wrote to the 
mailing list to decide if I will really do it or not


> b) you are breaking encapsulation, because columnMem returns a pointer
> to an internal type, which is useless to you, unless you have made
> public all the SQLite internals
>

The change is not indended for distribution, it is going to be a change in our 
codebase. The Internal type goes down to a struct having a union at its base 
address, such union contains either a double or a int64 value, for REAL and 
INTEGER values respectively (unless I wrongly assume that the internal 
representation of Mem pointer, is, under specific circumstances (which I havent 
yet stumpled upon in testing), inconstistent with the type reported by 
sqlite3_column_type. This means that I have no need to make all SQLite 
internals public. As I said I already keep track of the base type of each 
column in my sqlite database; in all the testing performed, I never failed to 
identify the correct datatype for the pointer. I cannot exclude that there are 
situation where I will fail, and that is why I posted here.

c) you are assuming that type conversion of a Mem type will conserve the
> original fields
>

This is the real 'unknown' to me issue, could you please elaborate a little 
more? Which are the original fields you are referring to? Which type 
conversions of a mem type are you referring to?


> d) you are duplicating work by calling sqlite3_column_text() twice,
> unless the type is numerical
> e) you are duplicating work by calling sqlite3_column_type() twice,
> instead of using the value returned from the first call
>

True, will be fixed... I focused too much on trying out the consistency of the 
change and the lack of regressions.


> f) you are using the result of a function call that is documented to
> be undefined in the exact context you are using it in
>

 Which is the function you are referring to? which context are you referring to?


> IMHO, you would be much better off attempting to master the official
> SQLite API.
>

Opinion acknowledged and taken in serious consideration, Nothing (except 
eventual development time constraints) prevents me from embracing your advice.

Thanks again
___
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

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Hick Gunter]

What you are doing is a very bad idea indeed.

a) you are circumventing the intended interface
b) you are breaking encapsulation, because columnMem returns a pointer to an 
internal type, which is useless to you, unless you have made public all the 
SQLite internals
c) you are assuming that type conversion of a Mem type will conserve the 
original fields
d) you are duplicating work by calling sqlite3_column_text() twice, unless the 
type is numerical
e) you are duplicating work by calling sqlite3_column_type() twice, instead of 
using the value returned from the first call
f) you are using the result of a function call that is documented to be 
undefined in the exact context you are using it in

IMHO, you would be much better off attempting to master the official SQLite API.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 13:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] sqlite3_exec without ubiqitous text conversions

Hello,

I have been working on an application based on SQLite for 2 years now, and 
recently we started running some performance profiling to check if there are 
areas where we can squeeze some extra performance.

SQlite query execution is used almost exclusively through sqlite3_exec, and the 
implementation of callbacks.

One of the areas of interest we identified is that every column is always 
passed to the callback as a c-char-string. Then we need to convert that string 
back to the intended data type. Apparently a consistent amount of time is spent 
doing this.

I started looking into SQLite code and realized that int and real values 
retrieved from database, is actually fecthed as binary value, then converted to 
string, passed to the callback, and in the callback we convert it back to its 
original data type (as we keep track of 'true' datatype for each column).

I decided to try skipping these conversions, by creating a custom 
implementation of sqlite3_exec, which does the following in the inside 'step' 
loop:

if (rc == SQLITE_ROW) {
azVals = azCols;
for (i = 0; i < nCol; i++) {
azVals[i] = (char *)sqlite3_column_text(pStmt, i);
int col_type = sqlite3_column_type(pStmt, i);
switch (col_type)
{
case SQLITE_INTEGER:
case SQLITE_FLOAT:
{
azVals[i] = (char*)columnMem(pStmt, i);
columnMallocFailure(pStmt);
break;
}
default:
{
azVals[i] = (char *)sqlite3_column_text(pStmt, 
i);
break;
}
}
if (!azVals[i] && sqlite3_column_type(pStmt, i) !=
SQLITE_NULL) {
sqlite3OomFault(db);
goto exec_out;
}
}
}

Instead of ALWAYS converting to text, in case of INTEGER or FLOAT columns 
types, I fetch the address of the data  through columnMem, put it in the azVals 
array, and go on. The callback knows which is the expected data type for the 
column, reads and copies data in the destination variable through a simple 
assignment.

I am aware that forcing a 'typed' pointer into a generic char* pointer 
(azVals[x]), rings a lot of alarm bells; I am, as well, aware that I could 
avoid sqlite3_exec, and call sqlite3_step myself.
As of now the application is running smoothly and faster as far as satabase 
access is concerned.

I am writing here to get opinions about other potential pitfalls or oversights 
in this approach, as well as reason why I should not proceed on this path.

Thanks in advance
___
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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[Hick Gunter]

You stated your goals were
a) authorization
b) some unspecified manipulation

I don't see how the order of the information available on the Sqlite authorizer 
interface is at all relevant to the result of authorization. Either the set of 
necessary operations are all allowed individually (total answer: yes) or at 
least one operation is not (total answer: no) with the possible exception of 
fields that are confidential and need to be replaced by NULL when an 
unprivileged user requests them.

e.g. the user's password could be protected from being  viewed for 
non-administrators by denying the request to read this field, thus returning 
null instead of the field contents, and using ifnull() to set a replacement 
string.

Whatever your unspecified manipulation is, questions about which internal 
representation was selected, and why, is something only the developers would be 
able to divulge, given willingness to do so.

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

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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[Hick Gunter]

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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[Hick Gunter]

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*"?

-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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[Hick Gunter]

Note that parsing debug output is not a stable method of analysis (meaning 
SQlite Dev can change anything at whim), whereas the authorizer interface is 
documented.

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

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

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


___
 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

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

[Hick Gunter]

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

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

Re: [sqlite] [EXTERNAL] Virtual Tables xConnect Called When?

[Hick Gunter]

This assumes that there is some kind of backing store that needs to be created 
once (xCreate) but may be conncted to (xConnect) later.

CREATE VIRTUAL TABLE calls the xCreate function (and DROP TABLE will call 
xDestroy), whereas queries will call the xConnect function. IIRC connecting to 
an SQLite database may also call the xCreate function during initial parsing of 
the schema.

Think of xCreate/xConnect as like fopen() with or without the create flag set.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Justin Olbrantz
Gesendet: Donnerstag, 25. Juli 2019 02:58
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Virtual Tables xConnect Called When?

The documentation says that "The difference is that xConnect is called to 
establish a new connection to an existing virtual table whereas xCreate is 
called to create a new virtual table from scratch." But this leaves me unclear 
on the circumstances where xConnect would be called (assuming I have a 
non-eponymous table). How would you create a new connection to an exiting 
virtual table?

--
Justin Olbrantz (Quantam)
"Ardente veritate
Urite mala mundi
Ardente veritate
Incendite tenebras mundi"
___
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

Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs

[Hick Gunter]

With the current interface, the xBestIndex function has the possibility of 
returning "effort" and "result set size" separately, instead of just an 
aggregate "effort" (which was at the time documented to assume "result set 
size").

Since we have nearly no native tables here, the question of "relative to native 
tables" does not pop up.

For disk-based native tables and CTree based VT I would tend to assume about 
equal effort-to-result-set ratios, whereas memory section VTs are probably 
significantly faster.

Since the QP operates with logarithmic cost estimates, I return 1 for a single 
row unique key retreival and the number of records for a full table scan. 
Partial key retrievals score a best estimate based on the assumption of equal 
selectivity per key field (e.g. for a 1000 row table and a three field key, it 
would be 1 for full key, 10 for the leading 2 key fields, 100 for a single 
leading key field and 1000 for no key fields = full table scan).

This is also factored in when choosing between several indexes that all match 
the constraints to some degree (2 leading fields form a 3 field key is 
preferred over 3 leading fields from a 7 field key), and also considering a 
requested ORDER BY clause.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 24. Juli 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs

On Wed, Jul 24, 2019 at 10:45 AM Hick Gunter  wrote:

> The speed of a virtual table depends on the backing store and software
> used to implement it.
>

[DD] Sure. virtual-tables can also access the disk and do expensive things.
[DD] I did say "example given" for my fast-pure-memory-no-decoding case.


> We have virtual tables that reference CTree files as well as virtual
> tables that reference memory sections here.

The advantage is that the VT implementation can adjust it's answers in the
> xBestIndex function.


[DD] I'm not sure I see your point. My point (and Justin's if I understand him 
right), is that the relative [DD] costs from tables vs virtual-tables is hard 
to figure out, which could skew results of the planner [DD] toward sub-optimal 
plans.

[DD] Most of my queries involve only my own virtual tables, so I use arbitrary 
relative costs, like [DD] 1 if returning a single row via a (virtual) unique 
index or PK, 2 if returning a range of rows, and 4 for a full table scan.
[DD] But these "relative for my vtable costs" are probably completely wrong 
when mixed with "real" tables, [DD] disk-based or in-memory. There must be some 
meaningful correlations between all costs for an optimal plan.
[DD] Or am I missing something? --DD
___
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

Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs

[Hick Gunter]

The speed of a virtual table depends on the backing store and software used to 
implement it.

We have virtual tables that reference CTree files as well as virtual tables 
that reference memory sections here. The advantage is that the VT 
implementation can adjust it's answers in the xBestIndex function.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 24. Juli 2019 10:02
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Estimated Costs and Memory DBs

On Wed, Jul 24, 2019 at 2:55 AM Justin Olbrantz 
wrote:

> [...] my virtual table will be held completely in memory. What should
> I do with the estimatedCost value from xBestIndex? According to the
> documentation this should be an approximation of the number of disk
> accesses for the query, which would be 0 in this case. But it's
> clearly vastly faster to do a query on an indexed column, meaning the
> cost for an indexed column should be much lower than the cost for an
> unindexed column. How should I be doing this?
>

This is unfortunately similar to questions I asked on this ML in the past, with 
no good answers as far as I remember...

For queries that mix "normal" disk-tables, in-memory-tables (i.e. the 
equivalent of a disk-table but with the DB file on a RAM disk, entirely in 
memory), and virtual-tables (e.g. accessing a C++ in-memory container of 
structs), the cost structure of these 3 are quite different. The last 2 are 
entirely in-memory both, yet the last case is quite a bit faster still (no 
decoding of pages/rows/varints needed).

I confess to not studying the code to try to answer that myself though...
--DD
___
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

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

[Hick Gunter]

This cannot be determined programatically.

The query generator stores an OP_Variable opcode when the SQL program needs to 
access the contents of an SQL parameter, and keeps track of the highest 
parameter number used, which determines the size of the parameter array.

Even if you were to examine the generated bytecode to determine which variables 
are referenced and check if the referenced variable has a value, this would 
still not answer your question. And you would be excluding any query that 
requires NULL to be bound to an SQL parameter.

Mixing numbered and named parameters is a very bad idea. Either you care about 
the assigned indexes or you don't.

SELECT ?, ?10, ?2, :AAA;
SELECT ?, :AAA, ?10, ?2;

The above queries are different in much more than the order of the values 
returned.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von test user
Gesendet: Montag, 22. Juli 2019 14:36
An: SQLite mailing list 
Betreff: 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


___
 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://