Re: [sqlite] What happens when a call contains two SQL statement

[Simon Slavin]

On 9 Jul 2018, at 4:13pm, Simon Slavin  wrote:

> You can find (especially if you're willing to pay) standards for SQL since 
> the 1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so 
> large and complicated that no implementation of SQL implemented as much of 
> the later standards as was done for the 1992 version.

I wanted to emphasise a point that Thomas Kurz made.  The 1992 standard was 
made publicly available for free.  Therefore many implementors got hold of it 
and followed it, and discussion of behaviour could refer back to the standard 
to clarify what implementations should and should not be doing.  It's fully in 
the spirit of Open Standard.

The 2008 version of the standard is not bad.  But you're meant to pay the ISO 
for a copy, and there are many limitations on posting quotes in public.  As you 
can imagine, this greatly hampers discussion of behaviour and identification of 
bugs.

Part 1 of the current standard, ISO/IEC 9075-1:2016, for example, is 78 pages 
long and costs CHF178 (= 150 Euro = US$180).  I didn't look up all the prices, 
but part 14 (sic) is 444 (sic) pages long and costs CHF198 (= 170 Euro = 
US$200).  I would encounter some difficulty in writing software to conform to a 
444 page document if I couldn't discuss that document with people outside my 
organisation.

Far from an Open Standard.  Which is why you will rarely see it quoted. 

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

Re: [sqlite] What happens when a call contains two SQL statement

[Simon Slavin]

On 9 Jul 2018, at 1:57pm, Aaron Elkins  wrote:

> Thank you for the interesting explanations for SQL specific cases, and I am 
> also interested in the SQL standard document after reading your email, can 
> you point me to the right place? 

SQL-86 was the first widely-adopted SQL standard.  However later standards 
added much-needed features to the language, giving it its dominant position 
throughout database programming.  The 1992 version of SQL was the first and 
last document for what has become standard SQL:



You will note, for example, that there are still no DATE, TIME or DATETIME 
types.  If you look at a later standard and don't know why it's weird, go back 
to this 1992 standard and see how the things in this standard guided what 
changes could be made without breaking compatibility.

You can find (especially if you're willing to pay) standards for SQL since the 
1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so large 
and complicated that no implementation of SQL implemented as much of the later 
standards as was done for the 1992 version.

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

Re: [sqlite] What happens when a call contains two SQL statement

[Thomas Kurz]

As far as I know you have to purchase the documents.

- Original Message - 
From: Aaron Elkins 
To: SQLite mailing list 
Sent: Monday, July 9, 2018, 14:57:07
Subject: [sqlite] What happens when a call contains two SQL statement

Hi Ryan, 

Thank you for the interesting explanations for SQL specific cases, and I am 
also interested in the SQL standard document after reading your email, can you 
point me to the right place? 

Sorry, I did not trust myself to find the official SQL standard by googling.

- Aaron



> On Jul 9, 2018, at 8:35 PM, R Smith  wrote:

> I see Gunter already answered this for the specific case, I'll just add some 
> simple rules of thumb for transactions in general:

> 1. SQLite automagically starts a transaction ONLY when faced with a single 
> statement. The second statement in your query/list/script is in its own 
> transaction and not the same one as the first statement.
> 2. If at any point you need more than one statement to be run together in a 
> single transaction, you need to start with BEGIN TRANSACTION, add all 
> statements and end it with either END, COMMIT or ROLLBACK.
> 3. Point 2 is True for ALL SQL Engines.
> 4. Typically once you've started a transaction, you control when it is 
> committed or rolled back. This is typically done in your code, but you can of 
> course set SQL conflict handling to have a vote in what happens.

> In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
> TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same as 
> just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.

> END is also equivalent to COMMIT - it saves the changes and busts out of the 
> current transaction (Important note: It does this EVEN if there were errors. 
> Unless specifically instructed to roll back, a transaction is perfectly happy 
> to complete and commit the successful bits - this is true for most (if not 
> all) SQL Engines).

> In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
> clause which specifically instructs how to handle the current statement and 
> transaction flow.

> The options are:

> - ON CONFLICT ABORT (the default if nothing is specified) - which tells the 
> engine to Stop and Undo this current command's updates, but do not stop the 
> transaction, keep changes by previous statements and keep going with the next 
> statements. [Kill me, but not the others]

> - ON CONFLICT FAIL - which tells the engine the current update failure should 
> STOP the current statement in its tracks, but not undo any changes up to now 
> caused by the current statement, and still continue with any next statements. 
> [Don't let me continue, but don't kill me and don't kill the others]

> - ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and it 
> may BOTH continue with updates for this current statement AND soldier on with 
> the next statements in the transaction. [No harm done, Forgive and Forget, 
> Peace and Love, let's move on...]

> - ON CONFLICT ROLLBACK  - which undoes the entire set of changes since the 
> BEGIN. [This is SPART! Kill everything!]

> - ON CONFLICT REPLACE - this one is more complicated but essentially it tries 
> to remove the old data (where needed) and shove the new data in there, but in 
> all cases it doesn't stop the TRANSACTION. [Please don't kill us... I'll do 
> anything you want!]


> Note also that a statement "failing" here means it ran into a constraint 
> conflict and so cannot fulfill its correctly-understood obligation.  If the 
> statement fails programmatically because it is a nonsense statement that 
> cannot be correctly understood by the parser/query planner, then in my 
> experience the transaction is rolled back automatically (much the same as if 
> there was a power-failure, an IO error, etc.).  I'm not sure this is 100% 
> always the case?


> Some things to watch out for:

> - Unlike some other SQL engines, in SQLite transactions do not nest. [There 
> can be only one!]. Nesting behaviour is however possible using SAVEPOINT 
> instructions. (See the docs).

> - SQLite wrappers in your favourite programming environment will often 
> automatically start transactions or automatically group statements in 
> transactions unbeknown to the programmer - check your wrapper's documentation 
> and config.

> - SQLite CONFLICT clauses can be set upon table creation for constraints, for 
> example the table schema you are writing a transaction for may have a 
> declaration like:
> CREATE TABLE t1 (
>   ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
>   Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
>   ...
>   etc.
> );
> Now you can override this in the tr

Re: [sqlite] What happens when a call contains two SQL statement

[Aaron Elkins]

Hi Ryan, 

Thank you for the interesting explanations for SQL specific cases, and I am 
also interested in the SQL standard document after reading your email, can you 
point me to the right place? 

Sorry, I did not trust myself to find the official SQL standard by googling.

- Aaron



> On Jul 9, 2018, at 8:35 PM, R Smith  wrote:
> 
> I see Gunter already answered this for the specific case, I'll just add some 
> simple rules of thumb for transactions in general:
> 
> 1. SQLite automagically starts a transaction ONLY when faced with a single 
> statement. The second statement in your query/list/script is in its own 
> transaction and not the same one as the first statement.
> 2. If at any point you need more than one statement to be run together in a 
> single transaction, you need to start with BEGIN TRANSACTION, add all 
> statements and end it with either END, COMMIT or ROLLBACK.
> 3. Point 2 is True for ALL SQL Engines.
> 4. Typically once you've started a transaction, you control when it is 
> committed or rolled back. This is typically done in your code, but you can of 
> course set SQL conflict handling to have a vote in what happens.
> 
> In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
> TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same as 
> just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.
> 
> END is also equivalent to COMMIT - it saves the changes and busts out of the 
> current transaction (Important note: It does this EVEN if there were errors. 
> Unless specifically instructed to roll back, a transaction is perfectly happy 
> to complete and commit the successful bits - this is true for most (if not 
> all) SQL Engines).
> 
> In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
> clause which specifically instructs how to handle the current statement and 
> transaction flow.
> 
> The options are:
> 
> - ON CONFLICT ABORT (the default if nothing is specified) - which tells the 
> engine to Stop and Undo this current command's updates, but do not stop the 
> transaction, keep changes by previous statements and keep going with the next 
> statements. [Kill me, but not the others]
> 
> - ON CONFLICT FAIL - which tells the engine the current update failure should 
> STOP the current statement in its tracks, but not undo any changes up to now 
> caused by the current statement, and still continue with any next statements. 
> [Don't let me continue, but don't kill me and don't kill the others]
> 
> - ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and it 
> may BOTH continue with updates for this current statement AND soldier on with 
> the next statements in the transaction. [No harm done, Forgive and Forget, 
> Peace and Love, let's move on...]
> 
> - ON CONFLICT ROLLBACK  - which undoes the entire set of changes since the 
> BEGIN. [This is SPART! Kill everything!]
> 
> - ON CONFLICT REPLACE - this one is more complicated but essentially it tries 
> to remove the old data (where needed) and shove the new data in there, but in 
> all cases it doesn't stop the TRANSACTION. [Please don't kill us... I'll do 
> anything you want!]
> 
> 
> Note also that a statement "failing" here means it ran into a constraint 
> conflict and so cannot fulfill its correctly-understood obligation.  If the 
> statement fails programmatically because it is a nonsense statement that 
> cannot be correctly understood by the parser/query planner, then in my 
> experience the transaction is rolled back automatically (much the same as if 
> there was a power-failure, an IO error, etc.).  I'm not sure this is 100% 
> always the case?
> 
> 
> Some things to watch out for:
> 
> - Unlike some other SQL engines, in SQLite transactions do not nest. [There 
> can be only one!]. Nesting behaviour is however possible using SAVEPOINT 
> instructions. (See the docs).
> 
> - SQLite wrappers in your favourite programming environment will often 
> automatically start transactions or automatically group statements in 
> transactions unbeknown to the programmer - check your wrapper's documentation 
> and config.
> 
> - SQLite CONFLICT clauses can be set upon table creation for constraints, for 
> example the table schema you are writing a transaction for may have a 
> declaration like:
> CREATE TABLE t1 (
>   ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
>   Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
>   ...
>   etc.
> );
> Now you can override this in the transaction by specifying your own ON 
> CONFLICT clause when doing an INSERT, for example, but it's worth noting that 
> in case you don't have a conflict clause, and as such are expecting the 
> default behaviour of "ON CONFLICT ABORT", you might be surprised by an entire 
> transaction roll-back when INSERTing a duplicate ID, or the current statement 
> stopping when one empty Name value occurs.
> 
> 
> That's basically the short version of what you need to know about 
> transactions 

Re: [sqlite] What happens when a call contains two SQL statement

[R Smith]

I see Gunter already answered this for the specific case, I'll just add 
some simple rules of thumb for transactions in general:


1. SQLite automagically starts a transaction ONLY when faced with a 
single statement. The second statement in your query/list/script is in 
its own transaction and not the same one as the first statement.
2. If at any point you need more than one statement to be run together 
in a single transaction, you need to start with BEGIN TRANSACTION, add 
all statements and end it with either END, COMMIT or ROLLBACK.

3. Point 2 is True for ALL SQL Engines.
4. Typically once you've started a transaction, you control when it is 
committed or rolled back. This is typically done in your code, but you 
can of course set SQL conflict handling to have a vote in what happens.


In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same 
as just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.


END is also equivalent to COMMIT - it saves the changes and busts out of 
the current transaction (Important note: It does this EVEN if there were 
errors. Unless specifically instructed to roll back, a transaction is 
perfectly happy to complete and commit the successful bits - this is 
true for most (if not all) SQL Engines).


In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
clause which specifically instructs how to handle the current statement 
and transaction flow.


The options are:

- ON CONFLICT ABORT (the default if nothing is specified) - which tells 
the engine to Stop and Undo this current command's updates, but do not 
stop the transaction, keep changes by previous statements and keep going 
with the next statements. [Kill me, but not the others]


- ON CONFLICT FAIL - which tells the engine the current update failure 
should STOP the current statement in its tracks, but not undo any 
changes up to now caused by the current statement, and still continue 
with any next statements. [Don't let me continue, but don't kill me and 
don't kill the others]


- ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and 
it may BOTH continue with updates for this current statement AND soldier 
on with the next statements in the transaction. [No harm done, Forgive 
and Forget, Peace and Love, let's move on...]


- ON CONFLICT ROLLBACK  - which undoes the entire set of changes since 
the BEGIN. [This is SPART! Kill everything!]


- ON CONFLICT REPLACE - this one is more complicated but essentially it 
tries to remove the old data (where needed) and shove the new data in 
there, but in all cases it doesn't stop the TRANSACTION. [Please don't 
kill us... I'll do anything you want!]



Note also that a statement "failing" here means it ran into a constraint 
conflict and so cannot fulfill its correctly-understood obligation.  If 
the statement fails programmatically because it is a nonsense statement 
that cannot be correctly understood by the parser/query planner, then in 
my experience the transaction is rolled back automatically (much the 
same as if there was a power-failure, an IO error, etc.).  I'm not sure 
this is 100% always the case?



Some things to watch out for:

- Unlike some other SQL engines, in SQLite transactions do not nest. 
[There can be only one!]. Nesting behaviour is however possible using 
SAVEPOINT instructions. (See the docs).


- SQLite wrappers in your favourite programming environment will often 
automatically start transactions or automatically group statements in 
transactions unbeknown to the programmer - check your wrapper's 
documentation and config.


- SQLite CONFLICT clauses can be set upon table creation for 
constraints, for example the table schema you are writing a transaction 
for may have a declaration like:

CREATE TABLE t1 (
  ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
  Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
  ...
  etc.
);
Now you can override this in the transaction by specifying your own ON 
CONFLICT clause when doing an INSERT, for example, but it's worth noting 
that in case you don't have a conflict clause, and as such are expecting 
the default behaviour of "ON CONFLICT ABORT", you might be surprised by 
an entire transaction roll-back when INSERTing a duplicate ID, or the 
current statement stopping when one empty Name value occurs.



That's basically the short version of what you need to know about 
transactions in SQLite.

Cheers!
Ryan


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

[sqlite] What happens when a call contains two SQL statement

[Cecil Westerhof]

I am working with Tcl. The best is of-course a general answer, but if it is
depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
set SQLCmd "
DELETE FROM testing
WHERE  key = 12
;
INSERT INTO testing
(key, value)
VALUES
(12, 'Just some text')
"
db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database
independent.

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