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

[Nelson, Erik - 2]

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

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

[Nelson, Erik - 2]

Keith Medcalf Sent Saturday, January 04, 2020 3:29 PM

>So I conclude that SERIALIZED/MULTITHREAD makes very little difference
>and that MEMSTATUS ON/OFF makes a huge difference.  Since the

That's a potentially very useful observation!  Is any of this available to JDBC 
users?

Erik

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

Re: [sqlite] Performance vs. memory trade-off question

[Nelson, Erik - 2]

Dominique Devienne wrote on Monday, December 16, 2019 7:46 AM
>On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp  wrote:

>> QUESTION:  Should this feature be default-on or default-off?
>>
>> What's more important to you?  0.25% fewer CPU cycles or about 72KB
>> less heap space used per database connection?

>Backward compatibility. I.e. if I change nothing in my build, and upgrade
>the amalgamation,
default-off at compile-time.

For my own clarification... if this is default-on, and I upgrade the 
amalgamation, the performance will be improved *and* the connection heap space 
will be reduced.  Is that correct?

Erik
 

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

Re: [sqlite] Safe saving of in-memory db to disk file

[Nelson, Erik - 2]

Dominique Devienne wrote on Monday, July 15, 2019 2:41 AM

>That's when you reach for virtual tables (and their "virtual indices").

>I.e. you keep your data in native data-structures (Boost.MultiIndex in my 
>case),
>and just provide a SQLite view of it. Much faster than "pure-in-Memory" with
> SQLite-managed pager-backed B-tree tables. Steep learning curve, especially
>for the indexing part, but the results are well worth it IMHO.

> Which can be freely mixed with "real" tables having "real" indexes (in the 
> in-memory DB).

That sounds really intriguing- does it significantly speed up queries coming in 
through the SQLite engine?  Or the speed bump is only if accessing from the 
C++-native side?

Is there any literature out there or tips you can share that can flatten the 
learning curve?

Thanks

Erik

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

Re: [sqlite] Bug in table_info pragma

[Nelson, Erik - 2]

Please disregard, apologies for the noise.

From: "Nelson, Erik - 2" 
Sent: May 17, 2019 8:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [External email from sqlite-users-boun...@mailinglists.sqlite.org] Re: 
[sqlite] Bug in table_info pragma

We need to get these into the hive database, if that's helpful

From: Simon Slavin 
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King  wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
> "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate 
CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store 
that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 
'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, 
and store that string in the schema.

Neither of those are the result that programmers would normally want. I 
withdraw my suggestion.  Thanks for the heads-up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw=

--
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
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=PJUZ-wgC3TvfU5DSVDpHM4-k2nn_x52ElGT8LAh017k=E1KU-A0zRaYOwwt9DyisbuiP0m-f_SaufevUhT-XhNo=

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

Re: [sqlite] Bug in table_info pragma

[Nelson, Erik - 2]

We need to get these into the hive database, if that's helpful

From: Simon Slavin 
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King  wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
> "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate 
CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store 
that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 
'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, 
and store that string in the schema.

Neither of those are the result that programmers would normally want. I 
withdraw my suggestion.  Thanks for the heads-up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw=

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

Re: [sqlite] SETting a value to a field with multiple conditions

[Nelson, Erik - 2]

I maintained an in-house sqlite patch that did this for a number of years... 
just made the buffer a little bigger, printed something like 

" set tt.<==HERE"

It was quite useful for helping application users self-serve their own query 
problems.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jose Isaias Cabrera
Sent: Wednesday, May 15, 2019 10:57 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SETting a value to a field with multiple conditions


David Raymond, on Wednesday, May 15, 2019 10:44 AM, wrote...
>When in doubt tinker. In this case it looks like it's the dot in "set tt.b = 
>'z'"
> that it's complaining about. Its thinking is probably "if you're updating a 
> table
> you can't set a field from a different table, so no qualified field names 
> there"
Darn it.  I hate it when you guys make me look so innocent. :-)  Thanks.  It 
would be nice if SQLite would provide a little bit more info something like,

Error: ".", near "set tt.b = ": syntax error

Since it knows that the problem is the ".", than just take 10 characters to the 
left and 10 characters to the right, and display that as the "near" part. I 
would have probably take the tt. out I would have seen that it had worked. :-) 
Thanks again for the teaching moment.

josé

>sqlite> UPDATE t as tt set b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 
>'p004') AND tt.idate = (SELECT max(idate) from t where a = tt.a);
>changes:   4   total_changes: 23
>
>sqlite> select * from t order by a, idate;
>n   a   b   c   d   e   idate
>--  --  --  --  --  --  
>--
>1   p001a   1   n   4   
>2019-02-11
>6   p001a   4   n   4   
>2019-02-12
>11  p001z   3   n   4   
>2019-02-13
>2   p002a   1   n   4   
>2019-02-11
>7   p002a   5   n   4   
>2019-02-12
>12  p002z   4   n   4   
>2019-02-13
>3   p003a   2   n   4   
>2019-02-11
>8   p003a   6   n   4   
>2019-02-12
>13  p003z   5   n   4   
>2019-02-13
>4   p004a   2   y   4   
>2019-02-11
>9   p004a   7   y   4   
>2019-02-12
>14  p004z   6   y   4   
>2019-02-13
>5   p005a   3   y   4   
>2019-02-11
>10  p005a   8   y   4   
>2019-02-12
>15  p005a   7   y   4   
>2019-02-13
>
>sqlite>
>
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, May 15, 2019 10:23 AM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] SETting a value to a field with multiple conditions
>
>
>Hi.  I know this has been probably asked before by someone, but imagine the 
>following scenario:
>
>create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, 
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, 
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, 
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, 
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, 
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, 
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, 
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, 
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, 
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, 
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, 
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, 
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, 
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, 
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, 
>'2019-02-13');
>
>select * from t;
>
>I would like to change field b to 'z' for all records in ('p001', 'p002', 
>'p003', 'p004') but to the latest idate.  I know I can do multiple single 
>statements, ie,
>
>UPDATE t set b = 'z' 

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

That could be a useful approach, and automatable.  It might get complicated in 
the presence of table aliases, like

sqlite> select a.test from b as a;
Error: no such table: b

sqlite> create table b(hello);

sqlite> select a.test from b as a;
Error: no such column: a.test

In the end, the query engine needs to resolve to 'real' tables?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 3:01 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

If you want the bare minimum of what will satisfy a statement I suppose there's 
always the iterative approach of:
Start with empty database
try your statement -> fix the error message -> repeat until it doesn't give an 
error


SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl1

sqlite> create table tbl1 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl2

sqlite> create table tbl2 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field1

sqlite> alter table tbl1 add column field1;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field3

sqlite> alter table tbl2 add column field3;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field4

sqlite> alter table tbl1 add column field4;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field2

sqlite> alter table tbl2 add column field2;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
QUERY PLAN
|--SCAN TABLE tbl1
`--SEARCH TABLE tbl2 USING AUTOMATIC COVERING INDEX (field2=?)

sqlite> .schema
CREATE TABLE tbl1 (rowid integer primary key, field1, field4);
CREATE TABLE tbl2 (rowid integer primary key, field3, field2);

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 2:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

For

>Could you expound on that a bit more? If the tables don't exist then it 
>becomes a moot point.

The question I'm hoping to answer is "what tables do I need to make in order to 
fulfil this query?".

For

>select * from foo;

I was wondering if I could get a call for 'foo' on the authorizer API, even 
though this query may not be able to be compiled to completion.

Regarding the other complexities you mentioned, point taken.  At the end of the 
day, though, the query results be based on zero or more actual tables- I was 
hoping there might be some robust way to detect what those are.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 2:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

For

>Could you expound on that a bit more? If the tables don't exist then it 
>becomes a moot point.

The question I'm hoping to answer is "what tables do I need to make in order to 
fulfil this query?".

For

>select * from foo;

I was wondering if I could get a call for 'foo' on the authorizer API, even 
though this query may not be able to be compiled to completion.

Regarding the other complexities you mentioned, point taken.  At the end of the 
day, though, the query results be based on zero or more actual tables- I was 
hoping there might be some robust way to detect what those are.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 2:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

--
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
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I=
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I=

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

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

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

[sqlite] sqlite as 'streaming mode' query engine?

[Nelson, Erik - 2]

Sqlite aggregation functions receive one call for each row in the underlying 
results set.  Has anyone on the list done a virtual table that's a based on a 
stream?

Something conceptually along the lines of

Insert row a
Insert row b
begin aggregation on rows a, b
insert row c
insert row d
update aggregation with rows c, d
...
finish aggregation when the stream of rows ends


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

Re: [sqlite] Is it possible to conditionally insert a record?

[Nelson, Erik - 2]

Can you use something along the lines of this?

insert into max_value select value as "value" from source_table order by value 
desc limit 1;

Shane Dev Sent: Wednesday, January 10, 2018 11:49 AM
To: SQLite mailing list 
Subject: [sqlite] Is it possible to conditionally insert a record?

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)

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

Re: [sqlite] C++ compiler

[Nelson, Erik - 2]

Eli Sent: Saturday, December 30, 2017 6:36 AM

>It would be awesome if SQLite could compile as a part of bigger C++ project.
>Right now there is a bunch of pointer casting errors, that can be fixed in
>a matter of hour IMHO.

I don't have any trouble using it as part of a larger C++ project.

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

[sqlite] Committing and memory usage

[Nelson, Erik - 2]

Does committing reduce memory usage on a memory database with pragma 
journal_mode=off?

I see advice to do periodic commits in order to reduce memory usage, but I'm 
wondering if that also applies to memory databases with journaling off.

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

Re: [sqlite] Move to Github!!?

[Nelson, Erik - 2]

Fredrik Gustafsson Sent: Wednesday, December 27, 2017 8:52 AM

>Ask yourself if you want a system that is easy to use or easy to learn.

That feels like a false choice- why can't it be both easy to learn and easy to 
use?  That's a hallmark of good engineering.

>Also when you're forced to use a third party ticket system, fossil i missing 
>one of its big advantages.

I'm no Fossil expert, but it does seem to have a ticketing system.  Is there 
something in particular functionality missing?

Erik

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

[sqlite] journal mode and transactions

[Nelson, Erik - 2]

The transactions documentation (https://sqlite.org/lang_transaction.html) states

   >If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal 
file) then the behavior of the ROLLBACK command is undefined.

Does that correspondingly mean there's no performance benefit to wrapping 
operations in a transaction?  Is that true for in-memory databases as well?

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

[sqlite] generic advice for insert performance in-memory database optimization

[Nelson, Erik - 2]

I've got an in-memory database with a single table that I need to fill with 
~500 million rows.  There are no indexes and the table definitions is

create table data(id int, path int, month int, val1 double, val2 double, val3 
double... val20 double)

I'm running on linux with the OS page size configured to 4096 and ~380 GB of 
ram (much more than required for the table so I think I'm not swapping) and 
haven't altered the sqlite page size.  I am using sqlite version 3.13.0 and 
these pragmas immediately after database creation.

pragma temp_store = MEMORY
pragma journal_mode = off

With these settings I'm seeing nonlinear (in a bad way) times for the insert. 
Is that expected?  I've fiddled about with various performance-related settings 
like described on

https://blog.devart.com/increasing-sqlite-performance.html

with varying results but haven't managed to arrive at fairly-linear insert 
behavior.  It's a single-threaded insert on a prepared query with bound 
arguments in a tight loop.

Is linear-ish insert time a reasonable goal for an in-memory database?

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

Re: [sqlite] Kind of function out of common table expression

[Nelson, Erik - 2]

Yannick Duchêne Sent: Wednesday, December 20, 2017 5:23 PM

>I wonder is there is a way with SQLite3, to reuse a often used and
>moderately long common table expression without the need to copy/paste it in
>every query text it is used in.

I use a C preprocessor for this and pass the queries through that on their way 
to the engine so a user might write something like

#define LONG_EXPRESSION  some very long expression

select LONG_EXPRESSION from somewhere;

select LONG_EXPRESSION from somewhere_else;


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

Re: [sqlite] reate index implies analyze?

[Nelson, Erik - 2]

Great explanation, thanks

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Wednesday, December 20, 2017 10:41 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] create index implies analyze?

CREATE INDEX will populate the index with references to all of the rows in the 
table. Bulk loads may run considerably faster if no indices are present at load 
time (not yet created or dropped beforehand), but created right after the data 
has been inserted. If you run INSERT or UPDATE statements later, they will also 
update the available indices too.

ANALYZE does NOT "populate" an index. It attempts to deduce the "shape" of your 
data, so that a faster query plan may be selected for queries that require 
ordering and/or joining several tables.

Running ANALYZE is only necessary if the "shape" of your data has changed, e.g. 
you have loaded a large dataset that is different from what you had before. 
Note that it is also possible to extract the results of ANALYZE from a 
"production sized" database and insert them into an empty database (with 
identical schema, of course), so that running ANALYZE is not required at all 
even after loading production data.

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

Re: [sqlite] [EXTERNAL] performance impact of index creation order

[Nelson, Erik - 2]

Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM

>You are not showing the definition of data. Some table constraints (e.g. 
>UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous 
>index that duplicates an automatically created index only serves to waste 
>space in the file and time to maintain.


The table definition doesn't have any constraints/primary keys, simply (note 
that 'id' is not unique)

Create table path_data(id int, path int, month int)

>It is possible that creating the unique index first may speed up creation of 
>the second index containing subset of the fields similar to when a covering 
>index is used for a query. You will just have to try this out for yourself. 
>You may find the .timer command of the SQLite shell useful. Be careful to 
>exclude the effects of caching, as these may swamp any differences in speed.

Right, I was hoping for general comments on the index creation order, hoping 
not to pessimize the application.  It's not a big thing either way... the 
database and temp store is in memory with journal mode off if that makes a 
difference, and it takes a day or more to populate the table so this would not 
be a first order performance issue in any event.  I was just curious if anyone 
had insight into the relative merits of varying the index creation order.


-Ursprüngliche Nachricht-

Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nelson, Erik - 2

Gesendet: Mittwoch, 20. Dezember 2017 16:38

An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>

Betreff: [EXTERNAL] [sqlite] performance impact of index creation order

Assuming that table 'data' is completely constructed, does index creation order 
have any performance ramifications?

For example, would it be reasonable to assume that the order of these two 
statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date) 

create index data_index on data(path, date)


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

[sqlite] performance impact of index creation order

[Nelson, Erik - 2]

Assuming that table 'data' is completely constructed, does index creation order 
have any performance ramifications?

For example, would it be reasonable to assume that the order of these two 
statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date)
create index data_index on data(path, date)


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

[sqlite] create index implies analyze?

[Nelson, Erik - 2]

If I am finished making inserts into a table, does the act of creating an index 
also populate the index?  That is, do I need to separately run 'analyze'?

My impression was that there was no need to call analyze unless rows had been 
inserted after index creation.

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Mark Sent: Tuesday, December 19, 2017 1:32 AM

On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote:
> Nomad Sent: Sunday, December 17, 2017 4:11 PM
> >On Sat Dec 16, 2017 at 07:53:22PM +0000, Nelson, Erik - 2 wrote:
>  
> >> Select 1 as value from (insert into table1 values(a, b, c)) I've
> >> tried a number of options but haven't been able to get anything
> ...snip...
> >> to work.  Is it possible?
> 
>> >How about a user defined function that does the insert in the
>> >background?
>> 
>> >SELECT my_insert(table_name, val1, val2);
>> 
>> That's a good suggestion- the only input control I have is sql
>> statements that must return at least one row.  AFAIK there's no way
>> to make UDFs within that constraint?

>That is quite a constraint. Unfortunately then the UDF option is not
>available to you.

Alas, yes.  Thanks to all for the excellent ideas, we can consider my question 
closed.

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf  Sent: Monday, December 18, 2017 2:31 PM

...snipped a lot...

>>Right, all of the statements are step'd regardless of the result of
>>sqlite3_column_count().  SQLITE_DONE is returned from the first
>>step() for insert queries.

>>In pseudocode, it's

>>prepare("insert...")  //okay
>>int num_col = sqlite3_column_count()  //okay
>>step() until sqlite_done  //okay

>>assert(num_col  > 0)  // blows up here, even though the query was successful

>So if the insert statement was step'd, then how is it that it has not been 
>executed?  Is the code more like:

It *has* been executed successfully.  All of the statements are step'd 
regardless of the result of sqlite3_column_count().  The assertion still fails.

...snip... 

>Because if the INSERT is stepped until done, then UNLESS each statement (or 
>group of statements) is taking place within an explicit transaction that is 
>then rolled back, the INSERT must have run to completion and been committed, 
>and the data MUST have been inserted ... notwithstanding the assert failure.  
>As far as I can tell, the only way this would not be true is if the statement 
>were run inside an explicit transaction which was "aborted" by de-rugging 
>before it was committed.


*The insert did run to correct completion*.  There aren't any transactions, 
only a rogue num_col  > 0 post-condition that throws an error.  That's the 
thing I'm trying to get around; I'm trying to get sqlite3_column_count() > 0 
from a SQL statement that effects an insert.

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf  Sent: Monday, December 18, 2017 1:07 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Odd question

>>I investigated a further while exploring some of the list
>>suggestions.  The app halts with an error unless
>>sqlite3_column_count() > 0.  That means I misspoke when I mentioned
>>that the sql statement needed to return at least one row.  I’m not
>>sure if that makes a difference, though.


> That makes a *huge* difference.  sqlite3_column_count() is available after 
> the prepare and before the first step.

Yes, the sqlite3_column_count() call happens after prepare() and before step(). 
 

>This means that the statement can be cancelled BEFORE it is executed 
>(step'ed).  "returns no rows" can only be determined by running (step'ing) the 
>statement and requires the statement to be both prepared and run (and that it 
>returns SQLITE_DONE with no SQLITE_ROW).

Right, all of the statements are step'd regardless of the result of 
sqlite3_column_count().  SQLITE_DONE is returned from the first step() for 
insert queries.

>In the former case all statements which are not select/pragma statements 
>returning data do not have to be run.

All the statements are run, the check for column count happens further 
downstream

>In the latter case, all statements will be run and you will get the "no data 
>was returned" if no data was returned.

Yes, all statements are run but the error comes from the fact that 
sqlite3_column_count() == 0

In pseudocode, it's

prepare("insert...")  //okay
int num_col = sqlite3_column_count()  //okay
step() until sqlite_done  //okay

assert(num_col  > 0)  // blows up here, even though the query was successful

So effectively I need sqlite3_column_count() > 0 in order to bypass the faulty 
assertion.

Thanks for your help in pushing me to think about it and describe it more 
clearly- even if we don't find a solution it's a helpful conversation.







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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Kees Nuyt Sent: Monday, December 18, 2017 12:51 PM

On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2" 
<erik.l.nel...@bankofamerica.com> wrote:

>> I'm using an application that I can't change. 
>> I can give it multiple queries to run but the
>> application assumes that each query will produce
>> at least one row and causes an error if that's
>> not the case.  I want to cause some rows to be
>> inserted into a table but plain insert queries
>> don't work because they violate the application's
>> assumption that a result will be returned.

>Something like: https://sqlite.org/pragma.html#pragma_count_changes
 (deprecated, but might still work)

Great suggestion!  The classical form of the pragma doesn't return any columns, 
and I don't think this pragma is available in the pragma functions (and 
wouldn't help much anyway, the app is built with sqlite 3.13.0)

These are all good suggestions, thanks to everyone who has responded!

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf Sent: Sunday, December 17, 2017 4:30 PM

>How do you know the insert is not working?  Have you verified that the data is 
>not being inserted or are you simply relying on a popup provided by the 
>application?  If the later, you should be aware that the application can 
>pop-up whatever it wants at any time.

The insert() does work when step() is called.

>For example, it could claim the statement failed because the moon is not made 
>of blue stilton.  While the moon is indeed not made of blue stilton, that is 
>not (and cannot possibly be) the reason that the statement failed.  It does 
>not mean that the statement in fact failed at all, only that the application 
>popped up a pop saying it failed bue to the moon not being made of blue 
>stilton.

Also correct- the application effectively does  "if (sqlite3_column_count() == 
0) throw("the moon is made of blue stilton")

>By the time the "application" knows that no results were returned it is 
>already too late, the statement has already been run to completion.  The 
>application is likely just "spitting out a message" based on their being no 
>results.

Exactly.  The statement is run, and it runs correctly.  Just the application 
halts the run because sqlite3_column_count() is zero.

>Unless of course the application has been deliberately designed to preclude 
>insert/update statements by wrapping each statement execution in a 
>transaction, which it then does a rollback on. 

That's not the case, it's just a poor assumption on the part of the application 
designers that sqlite3_column_count() > 0

>Or perhaps by making sure the first token of the statement is the word 
>"SELECT" ...  If this is the case then perhaps you can get a version of the 
>application that does not have these, rather intentionally created, 
>restrictions.

Right- hence the 'For unfortunate reasons' clause.  It's not possible to change 
the application at this time, so I was hoping for some workaround using the 
things I can control (the sql statements being fed to the application)


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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Peter Da Silva Sent: Monday, December 18, 2017 12:24 PM


>What I don’t understand is this app that’s making SQLite calls, so it’s using 
>the SQLite library, and it’s expecting a result from updates and inserts?

Yes, exactly.  The app designers assumed that rows are returned from every 
statement.  That's the 'unfortunate reason'.

>That seems like a bug or design flaw in the application.



I agree with either of those characterizations!



>Possibly it’s looking for the number of rows effected result and not finding 
>it for some reason?



I investigated a further while exploring some of the list suggestions.  The app 
halts with an error unless sqlite3_column_count() > 0.  That means I misspoke 
when I mentioned that the sql statement needed to return at least one row.  I’m 
not sure if that makes a difference, though.



https://sqlite.org/c3ref/column_count.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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Nomad Sent: Sunday, December 17, 2017 4:11 PM
>On Sat Dec 16, 2017 at 07:53:22PM +0000, Nelson, Erik - 2 wrote:
>> For unfortunate reasons, I need a query that does an insert and also
>> returns at least one row... for example, something along the lines of
 
>> Select 1 as value from (insert into table1 values(a, b, c))
>> I've tried a number of options but haven't been able to get anything
...snip...
>> to work.  Is it possible?

>How about a user defined function that does the insert in the
>background?

>SELECT my_insert(table_name, val1, val2);

That's a good suggestion- the only input control I have is sql statements that 
must return at least one row.  AFAIK there's no way to make UDFs within that 
constraint?

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Nelson, Erik - 2 Sent: Saturday, December 16, 2017 5:27 PM

>Petern wrote on Saturday, December 16, 2017 4:53 PM
>>Re: Nelson "odd". This will make the desired (?) side effect happen:

>>.load eval.so
>>SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

>>If INSERT references columns from an outer scope then use printf() inside the 
>>eval().

>>That one is eval(X,Y) where Y is optional column separator.  If you need
>>the improved eval(X,Y,Z) where Z is an optional line separator, let me know
>>and I'll post that upgrade.

>That's a great suggestion... I'm not using the sqlite shell and I don't have 
>control of the application but there's a reasonable chance that eval() is 
>statically compiled into the app.  I'll investigate that further.

I tracked this down, 'eval' isn't available.  :( My only input possibility is 
sql statements/pragmas... anything that will pass through a prepare()/step() 
and produce at least one row of results.

Thanks for the suggestion, though... I never would have thought of trying that!

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Simon Slavin wrote on Saturday, December 16, 2017 5:15 PM
>>On 16 Dec 2017, at 9:43pm, Nelson, Erik - 2 <erik.l.nel...@bankofamerica.com> 
>>wrote:

>> I'm using an application that I can't change.  I can give it multiple 
>> queries to run but the application assumes that each query will produce at 
>> least one row and causes an error if that's not the case.

>Will the application recognise two statements separated by a semicolon ?  This 
>happens automatically if it is using sqlite3_exec().  If it does, then you can 
>do

>INSERT into table1 values(1, 2, 3); SELECT 1

>As if it’s a single statement and it’ll return the result of SELECT 1

This might work- I've never tried passing two statements in as a single 'query' 
but afaik the semicolon doesn't have any particular significance to the 
application.  I believe the application is using the prepare()/step() interface 
but it's worth a try.  Thanks for the suggestion!

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Petern wrote on Saturday, December 16, 2017 4:53 PM
Re: Nelson "odd". This will make the desired (?) side effect happen:

>.load eval.so
>SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

>If INSERT references columns from an outer scope then use printf() inside the 
>eval().

...snip...

>That one is eval(X,Y) where Y is optional column separator.  If you need
>the improved eval(X,Y,Z) where Z is an optional line separator, let me know
>and I'll post that upgrade.

That's a great suggestion... I'm not using the sqlite shell and I don't have 
control of the application but there's a reasonable chance that eval() is 
statically compiled into the app.  I'll investigate that further.

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

Re: [sqlite] Odd question

[Nelson, Erik - 2]

Keith Medcalf wrote on Sent: Saturday, December 16, 2017 4:04 PM

>Your method of achieving a solution to whatever problem you are trying to 
>solve will not work.

>Perhaps you can state the problem you are trying to solve without making an 
>assumption as to how to solve that problem, someone may be able to solve the 
>problem with a workable solution rather than an unworkable solution.

I'm using an application that I can't change.  I can give it multiple queries 
to run but the application assumes that each query will produce at least one 
row and causes an error if that's not the case.  I want to cause some rows to 
be inserted into a table but plain insert queries don't work because they 
violate the application's assumption that a result will be returned.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2
>Sent: Saturday, 16 December, 2017 12:53
>To: SQLite mailing list
>Subject: [sqlite] Odd question
>
>For unfortunate reasons, I need a query that does an insert and also
>returns at least one row... for example, something along the lines of
>
>Select 1 as value from (insert into table1 values(a, b, c))
>
>Or
>
>Select coalesce((insert into table1 values(a, b, c)), 1) as value
>
>I've tried a number of options but haven't been able to get anything
>to work.  Is it possible?
>
= 

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

[sqlite] Odd question

[Nelson, Erik - 2]

For unfortunate reasons, I need a query that does an insert and also returns at 
least one row... for example, something along the lines of

Select 1 as value from (insert into table1 values(a, b, c))

Or

Select coalesce((insert into table1 values(a, b, c)), 1) as value

I've tried a number of options but haven't been able to get anything to work.  
Is it possible?


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

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

[Nelson, Erik - 2]

Simon Slavin wrote on  Monday, November 27, 2017 12:39 PM
> Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)
> 
> On 27 Nov 2017, at 4:51pm, Joe Mistachkin  wrote:
> 
> > Judging from the description so far, I think that both SQLite and
> > System.Data.SQLite are working correctly.
> >
> > It is true that System.Data.SQLite has an internal retry mechanism
> > that will wait 150ms between retries.   The PrepareRetries property
> > for the SQLiteConnection object is the maximum number of retries.
> > By default, it is 3, which means the total wait should be around
> > 450ms by default.

> But the OP may also have installed another AV system.  And there are lots of
> those and one or more may think they should be making chances inside a
> SQLite database.

It's inconceivable to me that any non-malicious application (including AV 
system) would randomly change the contents of files it didn't own.  I usually 
find the source of these kinds of things to be errors in my own program.

(granted that the word may not mean what I think it means)

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Nelson, Erik - 2]

Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM

>but this does not prevent reading and modifying the file from other 
>processes while they are open by SQLite in our process.

Does antivirus or Windows randomly modify other applications' files?  It seems 
like no program could reliably execute under such circumstances.


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

[sqlite] SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

[Nelson, Erik - 2]

If I'm redefining a user-defined function as SQLITE_DETERMINISTIC before each 
query, will the function still get called for subsequent queries?  For example, 
if I had

void user_func(sqlite3_context *context, int argc, sqlite3_value **argv){ 
return random() }

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
NULL, _func, NULL, NULL))

> select user()
.54

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
NULL, _func, NULL, NULL))

> select user()
.54  <-- should this result be the same as the previous call to user()?  Or 
would it get evaluated once after each sqlite3_create_function() call?

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

[sqlite] extension to query/set environment variables?

[Nelson, Erik - 2]

Hello,

I could imagine a virtual table that held all the environment variables, or a 
user-defined function to get or set environment variables.   Does anyone know 
of anything in the wild along these lines?  I know how I would implement it, 
wanted to see if someone else already had.

Erik

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

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

[Nelson, Erik - 2]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Clemens Ladisch
> Sent: Wednesday, May 17, 2017 2:36 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite3_create_function xFinal argument called when
> there's an error?
> 
> Nelson, Erik - 2 wrote:
> > for aggregate functions, is xFinal called if there's an error?
> 
> Yes; it's always called when SQLite cleans up the context.
> 
> 
>Clemens wrote:

Thanks!  In that case, should any result be set in xFinal?  Like 
sqlite3_result_error?  Or the previously-returned xStep error is sufficient?

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

[sqlite] sqlite3_create_function xFinal argument called when there's an error?

[Nelson, Erik - 2]

All- for aggregate functions, is xFinal called if there's an error?  For 
example, in the percentile.c add-on function, percentStep has code like

sqlite3_result_error(pCtx, "2nd argument to percentile() is not "
 "a number between 0.0 and 100.0", -1);

If that error happens, does percentFinal still get called to clean up the 
memory?  Or does it leak?

Thanks

Erik

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

Re: [sqlite] sqlite3 feature or regression

[Nelson, Erik - 2]

Thanks for taking the time to send in the report and thanks to all who 
investigated it.  The robust discussion demonstrates the passion of the sqlite 
community, and I enjoy thinking about the various points as they are made.

From: [Vermes Mátyás ]
Sent: Mar 10, 2017 7:37 AM
To: [SQLite mailing list ]
Subject: [Re: [sqlite] sqlite3 feature or regression]

Thanks. Naturally I had experimented with several versions of the program, and 
saw that any ordering makes the  new feature/error disappear. You can see it if 
you read the comment at the bottom of my original script. But my purpose was 
the opposite: demonstrate the regression. I am not interested in this thing. I 
do not have applications based on SQLite. Simply I think that this 
feature/error cannot be left in its current state, because this is in 
contradiction with the axiom, that the result of a query must not depend from 
the existence of an index. I wanted to help you with this bug report, but I 
cannot do more for that.

> As Dan already observed, the problem results because you are modifying
> an index in the middle of a scan of that index, thereby messing up the
> scan.  Don't do that.  The safest approach is to run the query to
> completion, then go back and start the loop over UPDATEs.
>
> If you add "ORDER BY +rowid" to the query, that forces the query to
> run to completion first and then go through the sorter, before you get
> any results back, which solves the problem.

--
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Nelson, Erik - 2]

Richard Hipp wrote on Tuesday, September 06, 2016 7:26 AM

>There is nothing on the Download page that is useful to a mobile device.
>So links to that page are omitted on mobile, to save precious pixels.

What percentage of sqlite.org hits are mobile browsers?  I agree there's 
nothing of use to a mobile device on the download page, and it seems like 
mobile devices (at least ones with small screens) are not well suited to 
reading technical documentation, no matter how 'responsive'.

Erik

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

[sqlite] error messages from SQLite

[Nelson, Erik - 2]

I've seen a small patch along the lines of this used.  I'm sure it could be 
improved, but it seems like it often would give a bit more context.

  Token msg;
  msg.z = pParse->zTail;
  msg.n = pParse->sLastToken.z - pParse->zTail + pParse->sLastToken.n;
  if(msg.n > 36)
  {
 msg.z = msg.z + msg.n - 36;
 msg.n = 36;
  }
  assert( msg.z[0] );
  sqlite3ErrorMsg(pParse, "near \"%T<--here\": syntax error", );

  UNUSED_PARAMETER(yymajor);  /* Silence some compiler warnings */
  assert( TOKEN.z[0] );  /* The tokenizer always gives us a token */

  /*sqlite3ErrorMsg(pParse, "near \"%T\": syntax error", );*/



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul 
Sanderson
Sent: Saturday, March 19, 2016 6:27 AM
To: General Discussion of SQLite Database
Subject: [sqlite] error messages from SQLite

When executing a sql query I often find I have made a typo or been a bit dull 
and I'll get an error message back along the lines of

Error: near "text": syntax error

Is there a way of expanding on this, for instance adding more of the following 
text or a character offset. The last time this happened I had a create table... 
command with about 30 text columns and identifying which "text" string the 
error referred to took a little while.

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

--
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] Problem with accumulating decimal values

[Nelson, Erik - 2]

Frank Millman Friday, December 11, 2015 9:21 AM
> 
> I am having a problem accumulating decimal values.
> 

> sqlite> UPDATE fmtemp SET balance = balance + 123.45; SELECT bal FROM
> sqlite> fmtemp;
> 6049.049
> 
> Can anyone explain what is going on, and is there a way to avoid it?
> 

It's because of the way that floating point math and display happens.

Simply put, there's no way to avoid it if you really want to use floating point 
numbers.

Bernardo's suggestion about using integer math may be a fairly easy workaround, 
especially if you're just adding numbers.

--
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] "Shipping a New Mindset" - SQLite in Windows 10

[Nelson, Erik - 2]

Richard Hipp Sent: Tuesday, November 10, 2015 2:41 PM
> 
> http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/

Congratulations!

--
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] field name in UDF

[Nelson, Erik - 2]

Stephan Beal Sent: Saturday, November 07, 2015 3:54 AM
> On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 <
> erik.l.nelson at bankofamerica.com> wrote:
> 
> > I have a user-defined function something like
> >
> > void quarter_sqlite3(sqlite3_context *context, int argc,
> sqlite3_value
> > **argv);
> >
> > for each sqlite3_value being passed in, it would sometimes be helpful
> > to have the associated field (if any) that the value is associated
> with.
> >
> > Is there any way to retrieve that?
> 
> An idea came to mind, but i'm not sure if it's usable for your case
> (it's only potentially useful if the queries are static, not entered by
> end
> users): UDFs are variadic, so you could pass an optional 2nd parameter
> with any information as a string to the final parameter, e.g.:
> 
>   select quarter(t.a, 't.a'), quarter(4,'four') from t;

That's not a bad idea, but in my case it's a user query so it doesn't work so 
well, as you mention.

--
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] field name in UDF

[Nelson, Erik - 2]

Stephan Beal wrote on Friday, November 06, 2015 1:00 PM
> On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 wrote:
> 
> > I have a user-defined function something like
> >
> > void quarter_sqlite3(sqlite3_context *context, int argc,
> sqlite3_value
> > **argv);
> >
> > for each sqlite3_value being passed in, it would sometimes be helpful
> > to have the associated field (if any) that the value is associated
> with.
> >
> > Is there any way to retrieve that?
> >
> 
> UDFs receive expanded/evaluated values, not fields:
> 
>   select quarter(t1.a+t2.a+t3.a+3.0) from t1, t2, t3...;
> 
> what field would you expect to get there?

Hence the "(if any)".  For

select quarter(t1.a) from t1  ;

I might hope to get 'a' or 't1.a'.  Any ideas?

--
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] field name in UDF

[Nelson, Erik - 2]

I have a user-defined function something like

void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value **argv);

for each sqlite3_value being passed in, it would sometimes be helpful to have 
the associated field (if any) that the value is associated with.

Is there any way to retrieve that?

--
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] accessing database from Lua and C

[Nelson, Erik - 2]

Simon Slavin wrote on Sunday, October 25, 2015 6:59 AM
> On 25 Oct 2015, at 10:05am, Lev  wrote:
> 
> > Untill now my architecture does all the SQLite work in C, and pass
> > simple variables on Lua's stack.
> >
> > Today morning I've got an idea to push only the database connection
> object.
> > The database is opened in the C code, however I only have a single
> > thread, so no other entity is accessing the database at the same time.
> 
> Can you use LuaSQLite3 or Lua-Sqlite3 to open a connection initially ?
> 
> 
> 
> 
> > Other solution would be to pass the filename of the database, but
> then
> > I have to close the connection. Must I?
> 
> You can have a C connection and a Lua connection at the same time, as
> long as you don't need to perform operations in both at the same time.
> 

I've had good luck compiling in lsqlite3, then registering the sqlite db handle 
into the Lua context using code like this

LuaIntf::LuaContext lua_context;
lua_context.importLibs();
sqlite3* db_handle = open_db_somehow();

if(1 == luaopen_lsqlite3(lua_context.state()))
   LuaIntf::Lua::popToGlobal(lua_context.state(), "sqlite3");
else
   throw runtime_error("error adding lsqlite3 to Lua context");

if(1 == lsqlite_do_open(lua_context.state(), NULL, db_handle))
   LuaIntf::Lua::popToGlobal(lua_context.state(), "db_name");
else
   throw runtime_error("error adding database to Lua context");


This example is using the excellent lua-intf library by Steve K. Chiu

https://github.com/SteveKChiu/lua-intf


--
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] UPDATE silently failing

[Nelson, Erik - 2]

Hugues Bruant wrote on Wednesday, September 23, 2015 2:06 AM
> > > in some cases the SIndex captured inside the first lambda (UPDATE
> > > statement) appeared to be null even though it wasn't null in the
> > > enclosing scope (setVersion_)
> >
> > Interesting (and disturbing) result. Is this with Oracle's java
> compiler?
> >
> Compiler is Oracle JDK 8u40-b25 on OSX
> Runtime is OpenJDK 8u60-b04 built from source on OS X, Windows and
> Linux
> 
> It's pretty rare but damaging enough that I'm going to avoid capturing
> lambdas in critical code paths from now on.

Perhaps everyone is aware already, but it seems like at least sometimes lambdas 
expose underlying thread synchronization problems.

--
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] UPDATE silently failing

[Nelson, Erik - 2]

R.Smith wrote on Tuesday, September 22, 2015 10:08 AM
> 
> On 2015-09-22 03:58 PM, Hugues Bruant wrote:
> > SIndex sidx is just a boxed immutable integer. Its value is bound to
> > the UPDATE and the trace shows it to be 0 But the log line printed
> > when the number of rows updated is zero clearly shows the value to be
> > 1 and this is further confirmed by the fact that when bound to the
> > INSERT, the value turns out to be 1 (or there would be no constraint
> > violation).
> >
> > This leaves essentially two places for the error to arise:
> >   - sqlite3_bind_int
> >   - somewhere in the JVM
> >
> That does look a bit like magic - and as is 99.9% the case when
> inexplicable magic happens, memory is getting corrupt or overwritten.
> You are obviously prudent about this, but is there no clue when running
> under Valgrind or similar for OSX? Nothing out of the ordinary or
> warnings?

I find that most of the time when I see magic it's because I've made an 
incorrect assumption, overlooked something, or have an incorrect mental model 
about what's actually going on.

--
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] Feedback request: JSON support in SQLite

[Nelson, Erik - 2]

Richard Hipp wrote on Friday, September 11, 2015 11:59 AM
> 
> Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
> 
> https://www.sqlite.org/draft/json1.html

This looks really good!

With the understanding that json_extract() already has the functionality, I've 
found that the json_array_* functions can be complemented by a 
json_array_index() function that returns the value at a given index into the 
array.

Erik

--
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] Lua inside SQLite

[Nelson, Erik - 2]

Abilio Marques wrote on Sunday, August 23, 2015 4:42 PM
> 
> 3. Lua can return arrays. Also, Lua can return multiple values. Tried
> to take advantage of those facts, but while reading the SQLite API
> documentation, found no function where I could map multiple values as
> the return of a function. Am I wrong? For example:
> 
> select * from table t where fieldA in (lua('return 1,2,3'));
> 
> Could be a useful feat in real scripts with real code.

Something that I've done that's similar-but-different is to use a C 
preprocessor to preprocess the query, evaluating the Lua prior to the query 
being prepared in sqlite.

In that case (slightly adapted from your example, assuming a lua function named 
'csv')

select * from table t where fieldA in (lua('return csv(1,2,3)'));

resolves to 

select * from table t where fieldA in (1,2,3);

that's still a single return value, but the ability to programmatically 
manipulate the query prior to execution has been useful to me, particularly 
being able to write things like

select * from lua('most_recent_table()') t where fieldA in (1,2,3);

or

attach database lua('get_right_dbfile()') as "db1";

assuming that LuaSqlite3 is available, the Lua functions can inspect the 
database in order to implement their logic.  LuaFileSystem can be useful as 
well.

--
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] Determine query type

[Nelson, Erik - 2]

> users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Hess wrote on 
> Thursday, August 06, 2015 1:11 PM
> 
> Also consider https://www.sqlite.org/c3ref/stmt_readonly.html
> 
> On Thu, Aug 6, 2015 at 10:08 AM, Stephan Beal 
> wrote:
> 
> > On Thu, Aug 6, 2015 at 6:57 PM, Ben Newberg 
> wrote:
> >
> > > Excellent. This is exactly what I was looking for.
> > >
> >
> > Great :). Now that i have some code in front of me i can say, more
> > concretely, that sqlite3_column_count() is what you want.
> >
> > https://www.sqlite.org/c3ref/column_count.html
> >

And https://www.sqlite.org/c3ref/c_alter_table.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] User-defined types -- in Andl

[Nelson, Erik - 2]

david at andl.org wrote on Monday, June 08, 2015 9:23 AM
> 
> Ultimately, I don't think it will really matter, because the role of
> Andl is to be platform independent. Do you care what your SQL product
> is written in?
> 
Absolutely.  I wouldn't be using SQLite if it wasn't C/C++, and I suspect that 
I'm not the only one.  It wouldn't even make sense for me to spend time looking 
at Andl, no matter how good it is.

Implementation technology is critical to anyone that embeds SQLite.  I'd guess 
that the SQLite developers' choice to use C was not accidental.

Many people are perfectly productive using C/C++.

Erik

--
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] Getting a crash on 32-bit Linux

[Nelson, Erik - 2]

Ron Aaron wrote on Tuesday, April 14, 2015 1:51 PM

>Something is very wrong, then.  The file I downloaded from the sqlite.org 
>site, and unzipped, has these:
>-rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c

Can you confirm that this file came from the .zip file, not the .gz file?

>> On 4/14/15 18:13, Dan Kennedy wrote:
>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
 Just updated with the version from sqlite.org and have the same problem:
>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>> website. Are you running [make sqlite3.c] yourself or downloading
>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>> sqlite-amalgamation-3080900.zip
> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
> size, 154880 lines long, and have an MD5 hash of
> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
> using the correct sqlite3.c source file.

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

Re: [sqlite] Shell tool improvement request

[Nelson, Erik - 2]

RSmith wrote on Friday, January 16, 2015 1:08 PM
> 
> On 2015/01/16 18:33, Simon Slavin wrote:
> >
> > Error: mytextfile.txt line 13588392: expected 2 columns of data but
> > found 1
> >
> > Naturally I spent some time looking near the end of the file to
> figure out what was wrong where the actual error was in line 2, and
> should have read something like "still inside quote when file ends".
> 
> Firstly - thanks, this made me chuckle.  Secondly - I think the parser
> does not realize there is an error until it is at the end of the file
> nor counts any imports as successful or indeed imported at all, since
> that first line will very much not be imported after the transaction
> was rolled back (I hope), so to report the successful imports number is
> probably not feasible. Stating that the error was specifically due to
> this or that might also be a stretch if one examines the number of
> possible things that can go wrong.
> 
> I do however believe it would be easy to note the line at which the
> import failed as being the line where parsing started for the current
> record (i.e line 2 in Simon's case), but then it may well be the error
> actually occurs on line 13588392 (such as an invalid UNICODE character),
> reporting line 2 in this case will be diabolical.
> 
> Last I would offer an idea to simply specifically check for "Unclosed
> Quote" error. I mean I can't imagine another import like the one above
> existing in real life, but certainly an actual unclosed quote might be
> a common mistake in some new export system (which maybe one is
> designing for oneself or as an application extension) or such.
> 

Why not just report both where it started and where it was discovered?

Erik

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

Re: [sqlite] sqlite3_bind_text and strings that look like numbers

[Nelson, Erik - 2]

Simon Slavin wrote on Tuesday, January 06, 2015 3:00 PM
> 
> On 6 Jan 2015, at 7:49pm, Nelson, Erik - 2
> <erik.l.nel...@bankofamerica.com> wrote:
> 
> > Is there any way to force the bind_text() to store the input text
> verbatim?
> 
> Another possibility is that everything is being stored fine, and that
> something is changing the type of the result just before you print it.
> To check this do something like
> 
> SELECT id,length(id) FROM tester
> 
> and see whether the length is what you'd expect it to be.

It was a source data problem.  Someone apparently opened the source CSV file in 
Excel, which changed the numbers to scientific notation, then they saved it.

My bad, I should have checked that first.  Many thanks again to you and Dr. 
Hipp.

Erik

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

Re: [sqlite] sqlite3_bind_text and strings that look like numbers

[Nelson, Erik - 2]

Richard Hipp wrote Tuesday, January 06, 2015 3:04 PM
> On 1/6/15, Nelson, Erik - 2 <erik.l.nel...@bankofamerica.com> wrote:
> >
> > converts the value to a textual scientific notation representation,
> > like
> 
> Nope.  SQLite is not doing this.  Something else is going on.
> 
> sqlite3_bind_text() does not do conversions when inserting into a

Thanks to you and Simon- I'll dig deeper, I see where the conversion is 
happening.

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

[sqlite] sqlite3_bind_text and strings that look like numbers

[Nelson, Erik - 2]

This is part of a larger application, but what I *think* is happening is using 
a table definition like

create table tester(id text collate nocase)

and a prepared statement like

insert into tester values(?)

then calling bind something like this pseudocode

sqlite3_bind_text(stmt, column, "1", len, 
SQLITE_TRANSIENT)

converts the value to a textual scientific notation representation, like

select typeof(id) from tester --> text
select * from tester --> 1.0e+24

so it looks like the column affinity is text, but the original string has been 
destroyed.

I know that the datatype page mentions that numerical data is converted to text 
form before being stored, so I'm guessing that the text is being identified as 
a number.

>From poking about on the net, it seems like the viable options are to

1.  use bind_blob
2.  use bind_text but with some hack to the text so it's not identified as a 
number

Is there any way to force the bind_text() to store the input text verbatim?
Looking through the amalgamation, it looks like the coercion might be happening 
around sqlite3_value_numeric_type and applyNumericAffinity - might it a 
practical to make the numeric conversion fail if it doesn't round-trip to the 
original text?  Or is that going to cause other problems?







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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Nelson, Erik - 2]

Simon Slavin wrote on  Monday, January 05, 2015 10:40 AM
> 
> On 5 Jan 2015, at 2:43pm, Nelson, Erik - 2
> <erik.l.nel...@bankofamerica.com> wrote:
> 
> > RSmith wrote on Monday, January 05, 2015 7:43 AM
> >
> >> I haven't done this, but I seem to remember there was a way to tell
> >> SQLite where to make temp files, or override the system default at
> >> any rate - which may help.
> >
> > I ran into this a while ago- used pragma temp_store_directory
> 
> That's what I'm trying now.  Unfortunately the directory I'm trying to
> use has spaces in and is several folders down a hierarchy.  I'm just
> hoping that the string I've used to set the path doesn't need spaces or
> slashes escaped.
> 
> Simon.

If you're using the command line, you could try a relative reference like "."

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

Re: [sqlite] VACUUM requires 6.7 times space ?

[Nelson, Erik - 2]

RSmith wrote on Monday, January 05, 2015 7:43 AM
> 
> On 2015/01/05 13:32, Dan Kennedy wrote:
> > On 01/05/2015 06:22 PM, Simon Slavin wrote:
> >> I have a database file which is 120GB in size.  It consists of two
> huge tables and an index. //...
> > Probably running out of space wherever temp files are created.
> 
> I haven't done this, but I seem to remember there was a way to tell
> SQLite where to make temp files, or override the system default at any
> rate - which may help.
> 

I ran into this a while ago- used pragma temp_store_directory

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

Re: [sqlite] Whish List for 2015

[Nelson, Erik - 2]

Tony Papadimitriou wrote on Tuesday, December 23, 2014 10:48 AM
> 
> Can you explain a bit more?
> 
> * Who is 'we'?  Is this an open source project somewhere?
> * How is the combined Lua & SQLite3 executable created?  Do you have
> some makefile (for Windows and Linux) or some instructions for manual
> compilation?

'We' is my dev team.  There's no open source project, it's just a part of our 
internal application.

I don't have a makefile or instructions- it may not be a good fit for your 
situation.

Erik

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

Re: [sqlite] Whish List for 2015

[Nelson, Erik - 2]

Tony Papadimitriou Sent on Tuesday, December 23, 2014 10:26 AM

>Problem 1: Currently, any scripts have to be stored outside the database
> in separate files meaning there one-file-holds-everything deal is lost
> (and organizing these according to the database they refer also becomes
> a bit of an issue), and

> Problem 2: Dynamically passing parameters to those scripts is not very
> easy without using some kind of external scripting (like Lua, Python,
> etc.) which is not likely to exist on someone else's computer, so
> transferring just the database file is not enough, as the recipient
> also need to install other components besides SQLite3.

FWIW, we just compile the lua interpreter together with sqlite3.

Installing the resulting binary should take care of both of your requirements.

Erik

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

Re: [sqlite] Counting rows

[Nelson, Erik - 2]

Simon Slavin wrote on Thursday, December 11, 2014 10:19 AM
> I know that the internal structure of a table means that this number
> isn't simple to produce.  But is there really no faster way ?  This
> table is going to have about six times that amount soon.  I really
> can't count the rows in less than a few hours ?
> 

Might it be possible to write a trigger that keeps track of 
insertions/deletions and updates a counter in another table?

As another data point, I tested an 8 GB database on a SAN that has about 14 
million rows, count(*) takes about 7 seconds.

Erik

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

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

[Nelson, Erik - 2]

RSmith wrote on Friday, November 21, 2014 4:46 AM
> 
> On 2014/11/21 08:09, Thane Michael wrote:
> > Many of the answers I came across online implied that it wouldn't be
> > as straightforward as serializing ints and strings.
> 
> Of course a byte-stream serializer is most speed-and-size happy and can
> easily be saved as a BLOB in any RDBMS (including SQLite) but the

I serialize into binary BLOBs using the Boost Serialization library.  It's 
probably not worth adding Boost to your project just for this, though.

Erik

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

Re: [sqlite] query REAL without trailing decimal and zero?

[Nelson, Erik - 2]

Richard Hipp wrote on Monday, September 15, 2014 3:16 PM
> On Mon, Sep 15, 2014 at 3:02 PM, Nelson, Erik - 2 <
> erik.l.nel...@bankofamerica.com> wrote:
> 
> > When I query a field defined with type 'real', I get '.0' appended to
> > the results for whole numbers.  For example if the value in the field
> > is 1, it appears as 1.0 in the query results.
> >
> > Is there some way for me to change this?
> >
> 
> Instead of "SELECT x FROM..." you could enter "SELECT printf('%g',x)
> FROM ...".

Yes, understood.  I was hoping to affect the default behavior and don't mind 
maintaining the patch.

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

[sqlite] query REAL without trailing decimal and zero?

[Nelson, Erik - 2]

When I query a field defined with type 'real', I get '.0' appended to the 
results for whole numbers.  For example if the value in the field is 1, it 
appears as 1.0 in the query results.

Is there some way for me to change this?  I poked around in the code and it 
appeared that the format string being used was something like

sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r);

I didn't find any documentation on what the "!" character does, but the "g" 
format specifier seems to indicate that the decimal point and trailing zero 
ought not to be included.

http://en.wikipedia.org/wiki/Printf_format_string#Type

Any tips would be appreciated!

Erik



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

Re: [sqlite] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)

[Nelson, Erik - 2]

> Roger Binns wrote on Sunday, September 07, 2014 2:30 PM
> On 07/09/14 11:19, Richard Hipp wrote:
> > Please use a cast to silence the compiler warnings.
> "(int)sizeof(...)"
> > instead of just "sizeof(...)".
> 
> That isn't safe for correctly written 64 bit apps.  For example they
> could end up with data items that are bigger than 2GB correctly using
> (s)size_t.
> The silencing cast above would then truncate it to a negative 32 bit
> number or truncated 31 bit number, which has differing meanings in the
> SQLite 3 APIs, and certainly never matches the callers intention.
> 
> The only safe thing for a correctly written 64 bit app to do is ensure
> that all size values are less than 2GB, and then the warning can be
> silenced in a cast.
> 

If the Boost C++ libraries are available, there's a numeric_cast that does 
casting and checks for overflow in the process.

It's not worth adding all of Boost for just this little bit, but if you have it 
around, may be worth taking a look.

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

Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup

[Nelson, Erik - 2]

Bob Moran wrote on: Friday, September 05, 2014 12:07 AM
> On Wed, Sep 3, 2014 at 1:29 PM, Nelson, Erik - 2 <
> erik.l.nel...@bankofamerica.com> wrote:
> 
> > Bob Moran wrote on Wednesday, September 03, 2014 12:45 PM
> > >
> > > One added note: My GUI is using QT4, where in this case the SQL
> text
> > > goes something like:
> > >
> > >   QString qstr = "select id, step, temp from protocols where id
> > > = %1";
> > >   qstr = qstr.arg(id);
> > >   char * str = (char*)qPrintable(qstr);
> > >   sqlite3_prepare_v2( db, str, qstr.Length+1, , NULL)
> > >
> >
> > That's pretty different than the pseudocode you presented earlier.
> >
> > From the docs for qPrintable
> > http://qt-project.org/doc/qt-4.8/qtglobal.html#qPrintable
> >
> > **
> > Returns str as a const char *. This is equivalent to
> > str.toLocal8Bit().constData().
> >
> > The char pointer will be invalid after the statement in which
> > qPrintable() is used. This is because the array returned by
> > toLocal8Bit() will fall out of scope.
> > 
> >
> > Your str is likely pointing to memory that has been freed?
> >
> Are you implying that I must copy the text returned by qPrintable(sql)
> to a safe location before calling a method that uses it? I thought that
> the string would be valid until I return from the routine.

Based on my reading of the documentation, yes.  

The char pointer will be invalid after the *statement* in which qPrintable() is 
used.

You've already provided additional evidence that your string is pointing to 
unallocated memory... you previously wrote

>Bob Moran wrote on 03. September 2014 06:25
>>I discovered that a malloc call for 500+ bytes was returning a pointer 8 
>>bytes below my SQL string

On the face of it, I'd suggest that indicates that the memory pointed by your 
char* has been reallocated since it was returned to the free store.

Also, as Alessandro mentioned, the length you're using is likely unreliable.

Erik

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

Re: [sqlite] Problems uploading CSV into sqlite3 DB

[Nelson, Erik - 2]

Carlos A. Gorricho (HGSAS) wrote on Friday, September 05, 2014 4:17 AM

>So, any ideas on how to solve this issue will be more than welcome. I have 
>tried several shortcuts...none works.
>Latest was to install an Ubuntu 14.04 Virtual Machine on my Mac, via Parallels 
>software.
>

Maybe line endings...

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

Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup

[Nelson, Erik - 2]

Bob Moran wrote on Wednesday, September 03, 2014 12:45 PM
> 
> One added note: My GUI is using QT4, where in this case the SQL text
> goes something like:
> 
>   QString qstr = "select id, step, temp from protocols where id
> = %1";
>   qstr = qstr.arg(id);
>   char * str = (char*)qPrintable(qstr);
>   sqlite3_prepare_v2( db, str, qstr.Length+1, , NULL)
> 

That's pretty different than the pseudocode you presented earlier.

>From the docs for qPrintable
http://qt-project.org/doc/qt-4.8/qtglobal.html#qPrintable

**
Returns str as a const char *. This is equivalent to 
str.toLocal8Bit().constData().

The char pointer will be invalid after the statement in which qPrintable() is 
used. This is because the array returned by toLocal8Bit() will fall out of 
scope.


Your str is likely pointing to memory that has been freed?

Erik

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

Re: [sqlite] Window functions?

[Nelson, Erik - 2]

Stephan Beal wrote on Monday, August 25, 2014 3:26 PM
> 
> For the small percentage of users who need it (or would even know how
> to apply it). i've been following this list since 2006 or 2007 and i
> recall this topic having come up only a small handful of times, which
> implies that only a small minority of users feels the need for it.
> 

Not necessarily... we use SQLite extensively in our application, and when we 
wanted to use window functions we looked in the docs and found it wasn't 
supported, we just accepted that as the way things are.  I suspect many users 
might do the same.


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

Re: [sqlite] Importing ~1000 CSV files faster

[Nelson, Erik - 2]

Joe Fisher wrote on Tuesday, August 19, 2014 5:11 PM
> I use the temp table because every CSV files has a header with the
> column names.

Can you just import the files, header row and all, into your destination table 
and just delete the 2000 header rows at the end?

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

Re: [sqlite] Vacuum command fails

[Nelson, Erik - 2]

veeresh kumar wrote:

 
> Now I am running the the application on D: Drive (it has 841 GB free
>  space) and C: drive has space (333 GB free). It looks like VACUUM uses
> C:drive space ??? Also command seems to be working fine...
> 

Maybe I'm missing something, but I felt like this was pretty clearly answered 
before.  

PRAGMA temp_store_directory returns and controls the directory that is used for 
temp files.  You can query it, and it will  probably resolve to your C: drive, 
which is probably where your TEMP or TMP environment variables point.

If your temp store directory doesn't have enough space, the VACUUM will fail.

Erik

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

Re: [sqlite] Vacuum command fails

[Nelson, Erik - 2]

Luuk wrote:

>so, 0 means temp is written to disk.

>Back to the the question.

>How much free disk space is there?

>I think you need more than 14Gb of free space if your database is 14Gb in size.

>http://lmgtfy.com/?q=sqlite+vacuum+how+much+disk+space+is+needed=1

>"This means that when VACUUMing a database, as much as twice the size of the 
>original database file is required in free disk space. "

>So, you will need 28Gb of free disk space..


Sometimes *where* the free space is makes a difference... for example, if the 
temporary file is generated in /tmp, then /tmp needs to have sufficient free 
space.  In my experience, /tmp is frequently too small to vacuum large 
databases.  The tmp location can be controlled by a (deprecated, IIRC, and 
hopefully never removed) pragma.

Erik

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

Re: [sqlite] Is 32bit SQLite limited to 1900mb RAM in windows?

[Nelson, Erik - 2]

Simon Slavin wrote on Monday, June 30, 2014 12:21 PM
> 
> SQLite isn't a thing, it's an API.  There's no SQLite server.  There's
> no particular installation of SQLite on a computer that Windows expects
> to be in a particular place.  You can have many copies of many
> different versions of SQLite in different folders on your computer.
> You can have twenty different apps on your computer with twenty
> different versions of SQLite compiled into them, some 32-bit, some 64-
> bit, and they'll all work fine.
> 

I'd say it's a 'thing'... it's something you can find somewhere on your hard 
drive, maybe twenty different things, in the case.

I think what the OP is trying to find out is if a 64-bit compiled version of 
SQLite properly accesses the 64-bit (ish) memory space, or is it still 
effectively limited to 32-bit addressing.

In my experience, 64-bit SQLite is cleanly addressing the larger space.

Erik

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

Re: [sqlite] detect database/table/field use

[Nelson, Erik - 2]

Roger Binns wrote on Thursday, June 26, 2014 5:38 PM
> >On 26/06/14 12:58, Nelson, Erik - 2 wrote:
> > I'd like to record which databases/tables/fields are accessed.  Is
> > there any not-too-difficult way of doing this?
> 
> The authorizer interface will address your issue.  You can just record
> what it tells you, or prevent/replace with null certain columns.
> 
>   https://sqlite.org/c3ref/set_authorizer.html
> 
> That page doesn't give any examples of what you see.  The doc for my
> python SQLite wrapper shows three examples:
> 
>   http://rogerbinns.github.io/apsw/example.html#authorizer-example
> 

Thanks!

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

[sqlite] detect database/table/field use

[Nelson, Erik - 2]

I work with a C++ application that embeds the SQLite source and executes 
user-supplied queries against SQLite database(s).

I'd like to record which databases/tables/fields are accessed.  Is there any 
not-too-difficult way of doing this?

Poking around in the source, it seems like a fundamental hook might be either 
in the lookupName() function or the internals of the Parse structure.

Anyway suggestions would be appreciated.

Erik




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

[sqlite] Google Stuff

[Nelson, Erik - 2]

I'm not sure where to file this, but it seems like a problem... if I google 
SQLite, like

https://www.google.com/search?q=sqlite

The SQLite Home Page comes up at the top of the results.  The URL shown is an 
HTTPS one, like

https://sqlite.org

which doesn't work (at least not for me, at my office).  Changing 'https' to 
'http' works.

I'm not sure if this is new, just the first time I've noticed it.

Erik



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

[sqlite] More context on parse error

[Nelson, Erik - 2]

I've mentioned before on the list that a bit more context in parse error 
messages would be helpful in our application.  I hacked something quick that 
seems to work for us, posting it in case anyone else finds it useful or would 
like to improve on it.


/*
** The following code executes when a syntax error first occurs.
*/
static void yy_syntax_error(
  yyParser *yypParser,   /* The parser */
  int yymajor,   /* The major type of the error token */
  YYMINORTYPE yyminor/* The minor type of the error token */
){
  sqlite3ParserARG_FETCH;
#define TOKEN (yyminor.yy0)

  Token msg;
  msg.z = pParse->zTail;
  msg.n = pParse->sLastToken.z - pParse->zTail + pParse->sLastToken.n;
  if(msg.n > 36)
  {
 msg.z = msg.z + msg.n - 36;
 msg.n = 36;
  }
  assert( msg.z[0] );
  sqlite3ErrorMsg(pParse, "near \"%T<--here\": syntax error", );

  UNUSED_PARAMETER(yymajor);  /* Silence some compiler warnings */
  assert( TOKEN.z[0] );  /* The tokenizer always gives us a token */
  
  /*sqlite3ErrorMsg(pParse, "near \"%T\": syntax error", ); */
  sqlite3ParserARG_STORE; /* Suppress warning about unused %extra_argument 
variable */
}

*

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

Re: [sqlite] Send Mail from sqlite

[Nelson, Erik - 2]

Simon Slavin wrote on Friday, February 07, 2014 5:06 AM
>> On 7 Feb 2014, at 9:59am, Vairamuthu  wrote:
>> Thanks for your response, it will be great help if you can get me
>> some sample code or algorithms, on that.
> 
> That would depend on what programming language you are using and what
> access it has to any method of sending mail.  However you do it, it
> won't be done inside SQLite so it won't be anything to do with this
> mailing list.


If SQLite is embedded in the app, might it be fairly simple to create a user 
defined function and call that from a trigger?

Erik

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

Re: [sqlite] locked database?

[Nelson, Erik - 2]

Richard Hipp wrote:
> The first thing I would do is use the sqlite3_next_stmt() interface (
> http://www.sqlite.org/c3ref/next_stmt.html) to double-check that there
> were no unreset and unfinalized prepared statements.
> 

That's perfect, exactly what I needed to find the offending statement!  Thanks 
much.

Erik

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

[sqlite] locked database?

[Nelson, Erik - 2]

In my app that embeds sqlite version 3.7.14, I'm running into a database 
locking problem that happens when detaching a database.  If I execute

attach database 'file:dbtest.db?mode=ro' as prism

the attachment happens okay, but when I execute

detach database prism

I get the error 'database prism is locked'

I assumed that this is a duplicate of the question at

http://goo.gl/k3raLi
http://stackoverflow.com/questions/15531498/sqlite-why-cant-i-detach-a-database-when-the-main-db-has-an-open-statement

but I'm having trouble finding the statement in question... I'm tracking all my 
calls to sqlite3_prepare_v2() and sqlite3_finalize() and it seems like the all 
prepared statements are being finalized.  I'm not using any explicit 
transactions or sqlite3_blobs, and the app is single threaded.

I looked through the transaction page at 
http://www.sqlite.org/lang_transaction.html and didn't see anything obvious 
that I'm tripping on.

Is there some way to find out what is causing the lock?  I've looked through 
the C interface documentation and didn't notice anything promising.  If I could 
find the statement that's causing the lock that would be most helpful, if 
that's the problem.  Or maybe there's something else it could be?  Any 
suggestions would be welcome.

Thanks

Erik

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

[sqlite] getting more context on SQL parse errors

[Nelson, Erik - 2]

When a SQL parsing error happens, the message returned by sqlite3_errmsg() is 
pretty terse... is there some way to retrieve more context, so that the user 
has more than one token to help locate the problem?

For example, having the previous several tokens that were successfully parsed 
would give a better idea of where to look for the SQL error.

Thanks

Erik

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

Re: [sqlite] attaching databases programmatically

[Nelson, Erik - 2]

Marc L. Allen wrote on Monday, July 22, 2013 10:55 AM
> 
> I see.  Sorry about that!
> 
> I guess the real problem is the in-memory ones.  The other ones must
> have a database file associated with them, right?
> 

Probably, but that doesn't mean that the file is accessible through a filename 
that can be understood by the "ATTACH" SQL statement.

For example, if the file handle is procured by sqlite and then the file is 
subsequently deleted, it's no longer available through a filename.  Or as 
Richard Hipp mentioned, the filename may refer to a space that the application 
can't currently get to, like a chroot jail.

Erik

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

Re: [sqlite] attaching databases programmatically

[Nelson, Erik - 2]

Richard Hipp wrote on  Monday, July 22, 2013 10:28 AM
> 
> On Mon, Jul 22, 2013 at 10:19 AM, Nelson, Erik wrote:
> 
> > I've got an application that allows the user to create an arbitrary
> > number of databases, either in memory or not.  In my C++ program, I
> > have the handles and I'd like to attach them all together so the user
> > can execute queries against them.  However, the only way that I've
> > found to do that is to use the "ATTACH" sql.  The problem is that the
> > sqlite3* exist, but the databases aren't in any namespace accessible
> >to the "ATTACH" query.
> >
> > Is there some way to programmatically attach databases when all you
> > have are the sqlite3 handles?
> >
> 
> No.  The only way to ATTACH a database is via the ATTACH command, which
> will invoke the xOpen method of the VFS to open the file.  If the file
> is no longer accessible, then it cannot be attached.  There is no way
> to transfer an open file from one database connection to another.
> 

Understood.  Thanks for the speedy response, and extra thanks for the 
outstanding sqlite3 project.

Erik

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

[sqlite] attaching databases programmatically

[Nelson, Erik - 2]

I've got an application that allows the user to create an arbitrary number of 
databases, either in memory or not.  In my C++ program, I have the handles and 
I'd like to attach them all together so the user can execute queries against 
them.  However, the only way that I've found to do that is to use the "ATTACH" 
sql.  The problem is that the sqlite3* exist, but the databases aren't in any 
namespace accessible to the "ATTACH" query.

Is there some way to programmatically attach databases when all you have are 
the sqlite3 handles?

I've read the list history, most of the conversations about this are a few 
years back.

Thanks

Erik

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

Re: [sqlite] attaching databases programmatically

[Nelson, Erik - 2]

Marc L. Allen wrote on Monday, July 22, 2013 10:47 AM
> Nelson, Erik wrote:
> > I've got an application that allows the user to create an arbitrary
> > number of databases, either in memory or not.  In my C++ program, I
> > have the handles and I'd like to attach them all together so the user
> > can execute queries against them.  However, the only way that I've
> > found to do that is to use the "ATTACH" sql.  The problem is that the
> > sqlite3* exist, but the databases aren't in any namespace accessible
> to the "ATTACH" query.
> >
> > Is there some way to programmatically attach databases when all you
> > have are the sqlite3 handles?

> Perhaps I misunderstood the question.  It sounds like he has the
> sqlite* objects for the databases, but wants to be able to determine
> the database/filename associated with them so he can construct an
> ATTACH statement in another query.
> 
> So.. the question is.. given an sqlite*, can you determine the
> underlying database/filename?

Yes, you misunderstood it the databases are programmatically generated- 
there is no file name.


Erik

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