[sqlite] How to make correct transaction use only SQL?

[Alexander Batyrshin]

For example i have this tabe:

CREATE TABLE t1 (
  id int unique ON CONFLICT ROLLBACK,
  val char
);

And I have to execute this sql file:

BEGIN TRANSACTION;
INSERT INTO t1 (id, val) VALUES(1, 'val1');
INSERT INTO t1 (id, val) VALUES(2, 'val2');
INSERT INTO t1 (id, val) VALUES(3, 'val3');
INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT
INSERT INTO t1 (id, val) VALUES(4, 'val5');
COMMIT;

If we execute this sql file, only INSERT before CONFLICT case will be
rollback-ed, but last one still will be executed and remains in
database...

cat test.sql | sqlite3 test.db
SQL error near line 11: column id is not unique
SQL error near line 13: cannot commit - no transaction is active
$ sqlite3 test.db
SQLite version 3.4.0
sqlite> select * from t1;
4|val5


I what that on conflict _whole_ transaction will ROLLBACK and state of
database will be exactly like at moment of execution "BEGIN
TRANSACTION". How it is possible using only SQL?

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------