Re: [sqlite] select within transaction

2019-06-15 Thread Roman Fleysher 
Thank you,  Adrian. I think this is reason changes() exist.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Adrian Ho 
Date: 6/15/19 12:25 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API 
(https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Ftclsqlite.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3Dreserved=0),
 then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select within transaction

2019-06-14 Thread Adrian Ho 
On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API (https://sqlite.org/tclsqlite.html), then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


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

Re: [sqlite] select within transaction

2019-06-14 Thread Adrian Ho 
On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...
>
>> Yes, and no.  From what I understand, and have been using it, if
>> something was written to the DB, it will give you a 1.  Otherwise
>> a 0.  But, it is not the amount of fields, just a write. ie.
> This is wrong information.  It does give you the amount of fields updated. Ie.

changes() returns the number of *rows* modified, not fields. See
https://sqlite.org/c3ref/changes.html for the base API function
documentation, which also reveals important details on how it counts
changes in various environments (e.g. triggers, multithreaded updates).


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

Re: [sqlite] select within transaction

2019-06-14 Thread Roman Fleysher 
Thank you! I did not know (or forgot) about ".bail on"

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Friday, June 14, 2019 3:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] select within transaction

How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue 
processing lines. So if you get rid of the "or rollback" then you'll get the 
error message and won't have to worry about it continuing on to the next lines 
in the input file despite there having been an error. And since you explicitly 
started a transaction it will leave the transaction open, and then when the CLI 
closes it will rollback the uncommitted transaction.


-Original Message-
From: sqlite-users  On Behalf Of 
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

Roman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select within transaction

2019-06-14 Thread Jose Isaias Cabrera 

Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...

> Yes, and no.  From what I understand, and have been using it, if
> something was written to the DB, it will give you a 1.  Otherwise
> a 0.  But, it is not the amount of fields, just a write. ie.

This is wrong information.  It does give you the amount of fields updated. Ie.

sqlite> create table a (a, b, c);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes();
1
sqlite> select total_changes();
3
sqlite> update a set a=4 where a = 1 or a = 2 or a = 3;
sqlite> select changes(); -- all changes made on the table
3
sqlite> select total_changes();
6
sqlite>

Sorry for the bad data.

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

Re: [sqlite] select within transaction

2019-06-14 Thread David Raymond 
How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue 
processing lines. So if you get rid of the "or rollback" then you'll get the 
error message and won't have to worry about it continuing on to the next lines 
in the input file despite there having been an error. And since you explicitly 
started a transaction it will leave the transaction open, and then when the CLI 
closes it will rollback the uncommitted transaction.


-Original Message-
From: sqlite-users  On Behalf Of 
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

Roman
___
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] select within transaction

2019-06-14 Thread Jose Isaias Cabrera 

Roman Fleysher, on Friday, June 14, 2019 02:22 PM, wrote...
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?
Yes, and no.  From what I understand, and have been using it, if something was 
written to the DB, it will give you a 1.  Otherwise a 0.  But, it is not the 
amount of fields, just a write. ie.

sqlite> create table a (a, b, c);
sqlite> create table b (a, d, e);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes();  -- this is for the last write
1
sqlite> select total_changes(); -- this is for the total amount of writes
3
sqlite> insert into a values (4, 5, 6);
sqlite> select changes();
1
sqlite> select total_changes();
4

I hope this helps.

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

[sqlite] select within transaction

2019-06-14 Thread Roman Fleysher 
Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

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