Re: [sqlite] Logging sqlite executed/bound SQL statements

[Marek Sebera]

What I did the shim for, was Linux system (x86/x86-64) and libsqlite3.so.0 
version 3.26/3.27

Not logging API calls, but logging executed SQL statements (something 
mysql/maria/postgre/oracle/... provide in form of query-log)

My main interest was to inspect SQL statements executed by 3rd party 
application, in environment, where I cannot modify system-provided 
libsqlite3.so.0, closely related to reverse-engineering.

I kind-of expected something as ENV configured DEBUG/LOGGING settings, that 
would apply to the library, but I found nothing like that.

sqlite3_trace_v2 looks promising, I could probably modify the shim to configure 
the trace and use the callback to log API calls where it'd be appropriate.

Still injecting sqlite3_trace_v2 configuration through LD_PRELOAD seems as a 
lot of work, for something, that one could expect from existing library 
implementation.
 


On 2/13/19 11:00 PM, Warren Young wrote:

On Feb 13, 2019, at 2:10 PM, Marek Sebera  wrote:


re-compiling system provided library is not an option


What version and system are we talking about?  It matters greatly.  If we’re 
talking about the epochal 3.7.x series, the answers are very different than if 
we’re talking about a version shipped last year.


to log sqlite commands


Every single API call, or only *certain* calls?  And if the latter, which ones?

For example, if you only want to log SQL errors, you can do it with

sqlite3_config(SQLITE_CONFIG_LOG, …);

If you’re on SQLite v3.14 or higher, you can use the tracing mechanism:

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

That plus sqlite3_expanded_sql() lets you “decompile” the parsed SQL for 
logging purposes.

There are antecedents to those mechanisms, but they don’t work as well.  In old 
versions of SQLite, it’s basically impossible to recover the SQL post hoc, and 
the precompiled SQL is often parameterized in C programs based on SQLite, so 
preaching didn’t work very well, either.

If none of those satisfy, then there are often platform-specific API tracing 
mechanisms for dynamic libraries and such, but that gets you back to my first 
question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users





signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[J. King]

On February 13, 2019 5:47:09 PM EST, Thomas Kurz  wrote:

>I don't think so because this works (shortened here; it also works with
>REFERENCES...):
>
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

You're correct; I must have been confusing the prohibition on CURRENT_TIMESTAMP 
with a general one. 

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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Keith Medcalf]

You should probably read the documentation.

https://sqlite.org/lang_altertable.html


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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 13 February, 2019 15:32
>To: SQLite mailing list
>Subject: [sqlite] inconsistent behavior when creating INTEGER NOT
>NULL column without DEFAULT?
>
>I just stumbled upon the following issue (tested with 3.27.1):
>
>I can do this:
>CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id)
>ON UPDATE CASCADE ON DELETE CASCADE);
>
>But this fails:
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES
>mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;
>
>--> Error: Cannot add a NOT NULL column with default value NULL
>
>I think both variants should behave consistently. But I don't know
>which behavior is the correct one according to the SQL standard.
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Graham Holden]

On Wednesday, February 13, 2019, 11:06:07 PM, Thomas Kurz
 wrote:

>> I guess a missing DEFAULT automatically implies DEFAULT NULL, so
>> the behavior of ALTER should be correct whilst CREATE seems to
>> forget to reject the statement.

I suspect the difference is you can CREATE a NOT NULL column with an
(implied) DEFAULT NULL because there are no rows yet, and so long as
you always supply a NOT NULL value when INSERTing, nothing is
violated. However, when trying to add a NOT NULL (implied) DEFAULT
NULL column, any _existing_ rows would violate the condition (since
their values for the new column would be NULL) so it cannot be
allowed. In theory, I think it _could_ be allowed if the table is
empty (but I don't know whether SQLite checks this).



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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

> I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior 
> of ALTER should be correct whilst CREATE seems to forget to reject the 
> statement.

Sorry, I was wrong about this. The CREATE shows the correct behavior whereas 
ALTER incorrecty rejects the statement. According to 
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html default values 
for columns without explicit DEFAULT are chosen by type (section "Handling of 
Implicit Defaults").

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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

> This is a limitation of SQLite's current ALTER TABLE implementation. Columns 
> can only be added with a default value of NULL, therefore NOT NULL columns 
> are forbidden. 

I don't think so because this works (shortened here; it also works with 
REFERENCES...):

ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior 
of ALTER should be correct whilst CREATE seems to forget to reject the 
statement.

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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[J. King]

On February 13, 2019 5:32:09 PM EST, Thomas Kurz  wrote:
>I just stumbled upon the following issue (tested with 3.27.1):
>
>I can do this:
>CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON
>UPDATE CASCADE ON DELETE CASCADE);
>
>But this fails:
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES
>mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;
>
>--> Error: Cannot add a NOT NULL column with default value NULL
>
>I think both variants should behave consistently. But I don't know
>which behavior is the correct one according to the SQL standard.
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

This is a limitation of SQLite's current ALTER TABLE implementation. Columns 
can only be added with a default value of NULL, therefore NOT NULL columns are 
forbidden. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

I just stumbled upon the following issue (tested with 3.27.1):

I can do this:
CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON UPDATE 
CASCADE ON DELETE CASCADE);

But this fails:
ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES mygroup (id) 
ON UPDATE CASCADE ON DELETE CASCADE;

--> Error: Cannot add a NOT NULL column with default value NULL

I think both variants should behave consistently. But I don't know which 
behavior is the correct one according to the SQL standard.


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

Re: [sqlite] Logging sqlite executed/bound SQL statements

[Warren Young]

On Feb 13, 2019, at 2:10 PM, Marek Sebera  wrote:
> 
> re-compiling system provided library is not an option

What version and system are we talking about?  It matters greatly.  If we’re 
talking about the epochal 3.7.x series, the answers are very different than if 
we’re talking about a version shipped last year.

> to log sqlite commands

Every single API call, or only *certain* calls?  And if the latter, which ones?

For example, if you only want to log SQL errors, you can do it with

   sqlite3_config(SQLITE_CONFIG_LOG, …);

If you’re on SQLite v3.14 or higher, you can use the tracing mechanism:

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

That plus sqlite3_expanded_sql() lets you “decompile” the parsed SQL for 
logging purposes.

There are antecedents to those mechanisms, but they don’t work as well.  In old 
versions of SQLite, it’s basically impossible to recover the SQL post hoc, and 
the precompiled SQL is often parameterized in C programs based on SQLite, so 
preaching didn’t work very well, either.

If none of those satisfy, then there are often platform-specific API tracing 
mechanisms for dynamic libraries and such, but that gets you back to my first 
question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Logging sqlite executed/bound SQL statements

[Marek Sebera]

Hello,

is there any better way, if re-compiling system provided library is not an 
option, to log sqlite commands, than using something similar to this LD_PRELOAD 
shim?
https://github.com/smarek/sqlite3-preload

I've read https://www.sqlite.org/debugging.html
Apart from compile-time debug options, this page mentions option to use GDB, 
but nothing quite similar to what the shim does, which is (for now) logging 
prepared statement (orig_sqlite3_prepare_v2) and bound text 
(orig_sqlite3_bind_text), with full STMT logging using orig_sqlite3_expanded_sql

With different DB systems, one can use built-in logging, TCP sniffing, etc.

Is there any better way to do this, with SQLite ? Should there be?

Thank you
Marek Sebera



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with clause "where X in (...)"

[Jose Isaias Cabrera]

Click on the link at the bottom of this email...


From: sqlite-users  on behalf of 
Don Walsh 
Sent: Wednesday, February 13, 2019 03:09 PM
To: SQLite mailing list
Subject: Re: [sqlite] Problem with clause "where X in (...)"

Get me of this list

On Wed, Feb 13, 2019, 8:11 AM David Raymond  Here's the page with the limits for what you can give SQLite
> https://www.sqlite.org/limits.html
>
> Depending on what your statement ended up looking like that could be the
> "Maximum Length of an SQL Statement" of 1,000,000 or maybe Maximum Number
> of Columns of 2,000?
>
> Check to see if you're violating one of the things in there, and if not
> then give us some more info on just how many items are in the list, and how
> large the statement text ended up as.
>
> And if it exits without any error code, why do you say it's broken?
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Mohd Radzi Ibrahim
> Sent: Wednesday, February 13, 2019 8:35 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Problem with clause "where X in (...)"
>
> Hi,
> I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
> contains too many items the code breaks here:
>
> The sqlite3_step just exit without returning any error code.
>
> best regards,
> Radzi
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with clause "where X in (...)"

[J. King]

On February 13, 2019 3:09:31 PM EST, Don Walsh  wrote:
>Get me of this list
>
>On Wed, Feb 13, 2019, 8:11 AM David Raymond wrote:
>
>> Here's the page with the limits for what you can give SQLite
>> https://www.sqlite.org/limits.html
>>
>> Depending on what your statement ended up looking like that could be
>the
>> "Maximum Length of an SQL Statement" of 1,000,000 or maybe Maximum
>Number
>> of Columns of 2,000?
>>
>> Check to see if you're violating one of the things in there, and if
>not
>> then give us some more info on just how many items are in the list,
>and how
>> large the statement text ended up as.
>>
>> And if it exits without any error code, why do you say it's broken?
>>
>> -Original Message-
>> From: sqlite-users
>[mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Mohd Radzi Ibrahim
>> Sent: Wednesday, February 13, 2019 8:35 AM
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: [sqlite] Problem with clause "where X in (...)"
>>
>> Hi,
>> I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my
>in-list
>> contains too many items the code breaks here:
>>
>> The sqlite3_step just exit without returning any error code.
>>
>> best regards,
>> Radzi
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

You can remove yourself by following the URL at the bottom of every message. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with clause "where X in (...)"

[Don Walsh]

Get me of this list

On Wed, Feb 13, 2019, 8:11 AM David Raymond  Here's the page with the limits for what you can give SQLite
> https://www.sqlite.org/limits.html
>
> Depending on what your statement ended up looking like that could be the
> "Maximum Length of an SQL Statement" of 1,000,000 or maybe Maximum Number
> of Columns of 2,000?
>
> Check to see if you're violating one of the things in there, and if not
> then give us some more info on just how many items are in the list, and how
> large the statement text ended up as.
>
> And if it exits without any error code, why do you say it's broken?
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Mohd Radzi Ibrahim
> Sent: Wednesday, February 13, 2019 8:35 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Problem with clause "where X in (...)"
>
> Hi,
> I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
> contains too many items the code breaks here:
>
> The sqlite3_step just exit without returning any error code.
>
> best regards,
> Radzi
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Scott]

Andy, David, Tim!!!
Thanks for the help and advice... I think I will name mine "theKraken"... ;) 
LOL!
Scott ValleryEcclesiastes 4:9-10 

On Wednesday, February 13, 2019, 10:34:51 AM EST, Tim Streater 
 wrote:  
 
 On 13 Feb 2019, at 15:23, David Raymond  wrote:

> On a humor tangent, am I the only person who has ever named a savepoint
> "theHounds" for the sole purpose of being able to run the statement:
>
> release theHounds;

welease Bwian;



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

Re: [sqlite] Checking differences in tables

[Don Walsh]

Remove me


On Wed, Feb 13, 2019, 12:54 PM Jose Isaias Cabrera 
> James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
> >On Tue, 12 Feb 2019 15:05:29 +
> >Jose Isaias Cabrera  wrote:
> >
> >> >SELECT * From t WHERE datestamp = "20190208"
> >>
> >> Ok, Simon, I'll bite; :-) Imagine this table:
> >>
> >> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
> >
> >That's better.
> >
> >> how would I find the differences in the fields based on the different
> >> idate?
> >
> >select ...
> >from t as now join t as then
> >on now.idate = '20190208'
> >and then.idate = '20190207' -- note single quotes
> >and ... -- other things that match
> >where ... --- things that don't match
> >
> >Can't be more specific than that when the question is "find the
> >differences".
>
> This is the last fix on this,
>
> select new.a,old.b,new.b, 'difference in column b' as info from t as new
>   LEFT JOIN t as old ON
> new.idate = '2019-02-12'
> AND old.idate = '2019-02-11'
> AND new.a = old.a
> WHERE
>   new.b != old.b
> UNION ALL
> select new.a,old.c,new.c, 'difference in column c' as info from t as new
>   LEFT JOIN t as old ON
> new.idate = '2019-02-12'
> AND old.idate = '2019-02-11'
> AND new.a = old.a
> WHERE
>   new.c != old.c
> UNION ALL
> select new.d,old.d,new.d, 'difference in column d' as info from t as new
>   LEFT JOIN t as old ON
> new.idate = '2019-02-12'
> AND old.idate = '2019-02-11'
> AND new.a = old.a
> WHERE
>   new.d != old.d
> UNION ALL
> select new.a,old.e,new.e, 'difference in column e' as info from t as new
>   LEFT JOIN t as old ON
> new.idate = '2019-02-12'
> AND old.idate = '2019-02-11'
> AND new.a = old.a
> WHERE
>   new.e != old.e
> ORDER by new.a;
>
> Result,
>
> p001|2|4|difference in column c
> p001|4|3|difference in column e
> p002|2|4|difference in column c
> p003|2|4|difference in column c
> p004|2|4|difference in column c
> p005|5|3|difference in column b
> p005|2|3|difference in column c
>
> This is the best way of doing this, correct?  Thanks.
>
> josé
>
> PS:  I've learn a few things in this last 7 days or so.  Thanks so much
> you Gurus.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>On Tue, 12 Feb 2019 15:05:29 +
>Jose Isaias Cabrera  wrote:
>
>> >SELECT * From t WHERE datestamp = "20190208"
>>
>> Ok, Simon, I'll bite; :-) Imagine this table:
>>
>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>
>That's better.
>
>> how would I find the differences in the fields based on the different
>> idate?
>
>select ...
>from t as now join t as then
>on now.idate = '20190208'
>and then.idate = '20190207' -- note single quotes
>and ... -- other things that match
>where ... --- things that don't match
>
>Can't be more specific than that when the question is "find the
>differences".

This is the last fix on this,

select new.a,old.b,new.b, 'difference in column b' as info from t as new
  LEFT JOIN t as old ON
new.idate = '2019-02-12'
AND old.idate = '2019-02-11'
AND new.a = old.a
WHERE
  new.b != old.b
UNION ALL
select new.a,old.c,new.c, 'difference in column c' as info from t as new
  LEFT JOIN t as old ON
new.idate = '2019-02-12'
AND old.idate = '2019-02-11'
AND new.a = old.a
WHERE
  new.c != old.c
UNION ALL
select new.d,old.d,new.d, 'difference in column d' as info from t as new
  LEFT JOIN t as old ON
new.idate = '2019-02-12'
AND old.idate = '2019-02-11'
AND new.a = old.a
WHERE
  new.d != old.d
UNION ALL
select new.a,old.e,new.e, 'difference in column e' as info from t as new
  LEFT JOIN t as old ON
new.idate = '2019-02-12'
AND old.idate = '2019-02-11'
AND new.a = old.a
WHERE
  new.e != old.e
ORDER by new.a;

Result,

p001|2|4|difference in column c
p001|4|3|difference in column e
p002|2|4|difference in column c
p003|2|4|difference in column c
p004|2|4|difference in column c
p005|5|3|difference in column b
p005|2|3|difference in column c

This is the best way of doing this, correct?  Thanks.

josé

PS:  I've learn a few things in this last 7 days or so.  Thanks so much you 
Gurus.


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

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Tim Streater]

On 13 Feb 2019, at 15:23, David Raymond  wrote:

> On a humor tangent, am I the only person who has ever named a savepoint
> "theHounds" for the sole purpose of being able to run the statement:
>
> release theHounds;

welease Bwian;



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

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[David Raymond]

When you "rollback to something;" any work since that savepoint was created is 
un-done, but that named savepoint still exists, yes.

That caught me out once where my code was like

for each line in the file:
savepoint foo
do several inserts/updates
if they all went ok:
release foo
if not:
rollback to foo
stuff involved with error

I wound up slowly accumulating a ton of savepoints with the same name as each 
time there was an error it left another savepoint on the stack. It should have 
been more like

for each line in the file:
savepoint foo
do several inserts/updates
if something went wrong:
rollback to foo
stuff involved with error
release foo whether it went right or wrong



On a humor tangent, am I the only person who has ever named a savepoint 
"theHounds" for the sole purpose of being able to run the statement:

release theHounds;

?  :)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Andy Bennett
Sent: Wednesday, February 13, 2019 9:58 AM
To: SQLite mailing list
Subject: Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT 
correctly?

Hi,

> For example, would I do this:
> Connect to the DB
> [ Pass the command to save: ]SAVEPOINT 'current'
> [ User choices are processed: SELECT and UPDATE statements ]
> [ if error or user changes their mind ]    ROLLBACK TRANSACTION TO
> SAVEPOINT 'current'
> 
> [ otherwise... upon success ]    RELEASE SAVEPOINT 'current'

That looks pretty good. I think you also need to RELEASE the SAVEPOINT 
after you've ROLLed BACK to it (if you no longer want it) otherwise it 
stays on the stack so that you can ROLLBACK to it again.



I have also recently implemented something using SAVEPOINT TRANSACTIONS 
and I'm unclear (having read the docs) what state one can expect 
statements to be in after a ROLLBACK. One of the mental models says that 
it's as if the transaction was restarted from the beginning, up until 
the SAVEPOINT. However, I assume that SELECT statements stay put, 
returning the next row after the one that was last fetched rather the 
one that was fetched immediately after the first time that the SAVEPOINT 
was passed.

Does anyone know what the guarantees are?




andy...@ashurst.eu.org
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Andy Bennett]

Hi,


For example, would I do this:
Connect to the DB
[ Pass the command to save: ]SAVEPOINT 'current'
[ User choices are processed: SELECT and UPDATE statements ]
[ if error or user changes their mind ]    ROLLBACK TRANSACTION TO
SAVEPOINT 'current'

[ otherwise... upon success ]    RELEASE SAVEPOINT 'current'


That looks pretty good. I think you also need to RELEASE the SAVEPOINT 
after you've ROLLed BACK to it (if you no longer want it) otherwise it 
stays on the stack so that you can ROLLBACK to it again.




I have also recently implemented something using SAVEPOINT TRANSACTIONS 
and I'm unclear (having read the docs) what state one can expect 
statements to be in after a ROLLBACK. One of the mental models says that 
it's as if the transaction was restarted from the beginning, up until 
the SAVEPOINT. However, I assume that SELECT statements stay put, 
returning the next row after the one that was last fetched rather the 
one that was fetched immediately after the first time that the SAVEPOINT 
was passed.


Does anyone know what the guarantees are?




andy...@ashurst.eu.org
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with clause "where X in (...)"

[David Raymond]

Here's the page with the limits for what you can give SQLite
https://www.sqlite.org/limits.html

Depending on what your statement ended up looking like that could be the 
"Maximum Length of an SQL Statement" of 1,000,000 or maybe Maximum Number of 
Columns of 2,000?

Check to see if you're violating one of the things in there, and if not then 
give us some more info on just how many items are in the list, and how large 
the statement text ended up as.

And if it exits without any error code, why do you say it's broken?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mohd Radzi Ibrahim
Sent: Wednesday, February 13, 2019 8:35 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Problem with clause "where X in (...)"

Hi,
I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
contains too many items the code breaks here:

The sqlite3_step just exit without returning any error code.

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

Re: [sqlite] Problem with clause "where X in (...)"

[Richard Hipp]

Your message is incomplete.  Do you have a test case that we can see?

On 2/13/19, Mohd Radzi Ibrahim  wrote:
> Hi,
> I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
> contains too many items the code breaks here:
>
> The sqlite3_step just exit without returning any error code.
>
> best regards,
> Radzi
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Problem with clause "where X in (...)"

[Mohd Radzi Ibrahim]

Hi,
I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
contains too many items the code breaks here:

The sqlite3_step just exit without returning any error code.

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

[sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Scott]

Hi All!
 I've been reading about SAVEPOINT and ROLLBACK in the SQLite documentation. My 
project has an embedded SQLite DB with multiple tables (relational) that may or 
may not be read or updated because of choices the user makes.  Am I 
understanding correctly that I can use these commands to rollback to the 
previous savepoint if there is an error, or the user changes their mind? This 
is all through Java code, so I wasn't sure if the PreparedStatement "Execute()" 
or "ExecuteQuery" would still allow a rollback if I'm understanding correctly. 
For example, would I do this:
Connect to the DB
[ Pass the command to save: ]SAVEPOINT 'current'
[ User choices are processed: SELECT and UPDATE statements ]
[ if error or user changes their mind ]    ROLLBACK TRANSACTION TO SAVEPOINT 
'current'

[ otherwise... upon success ]    RELEASE SAVEPOINT 'current'

Close DB connection
Thanks for your time!
Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tips for index creation

[Thomas Kurz]

Ok, thank you very much for everybody's help.


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Wednesday, February 13, 2019, 13:58:19
Subject: [sqlite] Tips for index creation

Some useful reading:

https://use-the-index-luke.com/

https://www.sqlite.org/queryplanner.html (and the pages it links to)

There's also the .expert command in the sqlite shell:

sqlite> .expert
sqlite> SELECT ... FROM ...;

will suggest indexes that will benefit a particular query.


On Wed, Feb 13, 2019, 4:39 AM Thomas Kurz  Hello,

> I apologize right at the beginning, because this is a real noob question.
> But I don't have much experience with performance optimization and indexes,
> so I'm hoping for some useful hints what indexes to create.

> I have queries like this:

> SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3
> IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

> Do I have to create three indexes, one for each column id1, id2, id3? Or
> is it better or even necessary to create only one index covering all three
> id columns?

> Do I need indexes for the parameter and value columns as well, and under
> which circumstances? (Only if I want to use a SELECT ... WHERE parameter =
> xy?)

> Kind regards,
> Thomas

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

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

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

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Man, you guys are so smart... Thanks, Keith.

From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Wednesday, February 13, 2019 02:31 AM
To: SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables


insert into t (a, b, c, d, e, idate)
 values ('p001', 1, 2,
  (SELECT d FROM t
WHERE a = 'p001'
 ORDER BY idate desc
limit 1
  ),
  4, '2019-02-12');


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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>
>>
>>
>>
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using
>a query as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT
>d FROM t WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>>  (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
>  (SELECT d FROM t
>WHERE a = 'p001'
>  AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
>  ),
>  4, '2019-02-12');
>
>--
>Regards,
>Kees Nuyt
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Tips for index creation

[Shawn Wagner]

Some useful reading:

https://use-the-index-luke.com/

https://www.sqlite.org/queryplanner.html (and the pages it links to)

There's also the .expert command in the sqlite shell:

sqlite> .expert
sqlite> SELECT ... FROM ...;

will suggest indexes that will benefit a particular query.


On Wed, Feb 13, 2019, 4:39 AM Thomas Kurz  Hello,
>
> I apologize right at the beginning, because this is a real noob question.
> But I don't have much experience with performance optimization and indexes,
> so I'm hoping for some useful hints what indexes to create.
>
> I have queries like this:
>
> SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3
> IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c
>
> Do I have to create three indexes, one for each column id1, id2, id3? Or
> is it better or even necessary to create only one index covering all three
> id columns?
>
> Do I need indexes for the parameter and value columns as well, and under
> which circumstances? (Only if I want to use a SELECT ... WHERE parameter =
> xy?)
>
> Kind regards,
> Thomas
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tips for index creation

[R Smith]

On 2019/02/13 2:39 PM, Thomas Kurz wrote:

Hello,

I apologize right at the beginning, because this is a real noob question. But I 
don't have much experience with performance optimization and indexes, so I'm 
hoping for some useful hints what indexes to create.

I have queries like this:

SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 IS 
NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

Do I have to create three indexes, one for each column id1, id2, id3? Or is it 
better or even necessary to create only one index covering all three id columns?


The quickest will be a covering Index on a, b and c (in that specific 
order).




Do I need indexes for the parameter and value columns as well, and under which 
circumstances? (Only if I want to use a SELECT ... WHERE parameter = xy?)


Depends, the covering index on a, b, c, parameter, value will be very 
fast although somewhat larger (more space needed), but if the table only 
really contains those columns, the speed advantage will be negligible. 
However, if the table contains many columns, let's say 40+ columns, or 
perhaps other columns of which some of the values are rather wide (long 
strings, blobs, etc.) then singling out the 5 mentioned columns into a 
covering Index will definitely have a significant advantage (especially 
when row-count grows large).


A covering Index is essentially a copy of the table singling out the 
pertinent data, using up extra space but gaining faster access as a 
result.  The decision is always weighed on space cost vs. speed gain, 
and sometimes it's hard to find a definite answer without testing on a 
DB with enough real-world data.


So I guess the answer is: Try it, if it works better, keep it!

Cheers,
Ryan

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

Re: [sqlite] Tips for index creation

[Richard Hipp]

On 2/13/19, Thomas Kurz  wrote:
> Hello,
>
> I apologize right at the beginning, because this is a real noob question.
> But I don't have much experience with performance optimization and indexes,
> so I'm hoping for some useful hints what indexes to create.
>
> I have queries like this:
>
> SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 IS
> NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c
>
> Do I have to create three indexes, one for each column id1, id2, id3? Or is
> it better or even necessary to create only one index covering all three id
> columns?

The preferred index here is:

  CREATE INDEX x1 ON metadata(id1,id2,id3);

>
> Do I need indexes for the parameter and value columns as well, and under
> which circumstances? (Only if I want to use a SELECT ... WHERE parameter =
> xy?)

If you add "parameter" and "value" to the index, like this:

  CREATE INDEX x1 ON metadata(id1,id2,id3,parameter,value);

that will make the index into a "covering index" for the queries you
show above, and a covering index does usually run faster.  However, it
will also make your index take up more space on disk.  So you need to
decide which is more important for you.

The cool think about SQL is that you can change this on-the-fly (by
dropping and rebuilding the index) to try it out, without having to
make any modifications to your application - indeed without having to
recompile your application, or even to restart your application.  You
can run experiments with various index configurations to see which one
works best for you.

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

[sqlite] Tips for index creation

[Thomas Kurz]

Hello,

I apologize right at the beginning, because this is a real noob question. But I 
don't have much experience with performance optimization and indexes, so I'm 
hoping for some useful hints what indexes to create.

I have queries like this:

SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 IS 
NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

Do I have to create three indexes, one for each column id1, id2, id3? Or is it 
better or even necessary to create only one index covering all three id columns?

Do I need indexes for the parameter and value columns as well, and under which 
circumstances? (Only if I want to use a SELECT ... WHERE parameter = xy?)

Kind regards,
Thomas

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

Re: [sqlite] Building Amalgamation

[D Burgess]

Maybe not.
See https://www.sqlite.org/compile.html#_options_to_omit_features
to quote "Because of this, these options may only be used when the library
is built from canonical source, not from the amalgamation
"

So the lists in the two places of the documentation are different.

On Wed, Feb 13, 2019 at 7:01 PM Simon Slavin  wrote:

> On 13 Feb 2019, at 6:09am, D Burgess  wrote:
>
> > 3.6.20 is a loong time ago. Which led me to think that maybe that list
> is no longer be accurate.
> > Is there an easy way to extract the available options for the latest
> release?
>
> This is not something I normally worry about, but is the list here any
> good ?
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building Amalgamation

[Simon Slavin]

On 13 Feb 2019, at 6:09am, D Burgess  wrote:

> 3.6.20 is a loong time ago. Which led me to think that maybe that list is no 
> longer be accurate.
> Is there an easy way to extract the available options for the latest release?

This is not something I normally worry about, but is the list here any good ?



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