/ Check-in [3e5d38f5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More compact implementation of PRAGMA onconflict, and some test cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | pragma-onconflict
Files: files | file ages | folders
SHA1: 3e5d38f5b37da08a3d4704c23a51399624e3ecab
User & Date: drh 2016-02-27 19:03:22
Context
2016-02-27
19:03
More compact implementation of PRAGMA onconflict, and some test cases. Leaf check-in: 3e5d38f5 user: drh tags: pragma-onconflict
17:16
Experimental "PRAGMA onconflict=FAIL" statement to change the default ON CONFLICT algorithm to something other than ABORT. check-in: 3a0c347c user: drh tags: pragma-onconflict
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

  1899   1899     **   PRAGMA onconflict = FAIL
  1900   1900     **   PRAGMA onconflict = ABORT
  1901   1901     **   PRAGMA onconflict = ROLLBACK
  1902   1902     **
  1903   1903     ** Set the default conflict handling algorithm.
  1904   1904     */
  1905   1905     case PragTyp_ONCONFLICT: {
  1906         -    const char *zRes = "ABORT";
  1907         -    if( zRight ){
  1908         -      if( sqlite3StrICmp(zRight,"FAIL")==0 ) db->dfltOnError = OE_Fail;
  1909         -      if( sqlite3StrICmp(zRight,"ABORT")==0 ) db->dfltOnError = OE_Abort;
  1910         -      if( sqlite3StrICmp(zRight,"ROLLBACK")==0 ) db->dfltOnError = OE_Rollback;
  1911         -    }
  1912         -    switch( db->dfltOnError ){
  1913         -      case OE_Fail:     zRes = "FAIL";      break;
  1914         -      case OE_Rollback: zRes = "ROLLBACK";  break;
         1906  +    static const char *azMode[] = { "ABORT",  "FAIL",  "ROLLBACK"  };
         1907  +    static const u8 aeMode[]    = { OE_Abort, OE_Fail, OE_Rollback };
         1908  +    const char *zRes = 0;
         1909  +    int i;
         1910  +    for(i=0; i<ArraySize(azMode); i++){
         1911  +      if( sqlite3_stricmp(azMode[i], zRight)==0 ) db->dfltOnError = aeMode[i];
         1912  +      if( db->dfltOnError==aeMode[i] ) zRes = azMode[i];
  1915   1913       }
  1916   1914       returnSingleText(v, "onconflict", zRes);
  1917   1915       break;
  1918   1916     }
  1919   1917   
  1920   1918   #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST)
  1921   1919     /*

Added test/onconflict1.test.

            1  +# 2016-02-27
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file focuses on the PRAGMA onconflict statement.
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix onconflict1
           18  +
           19  +do_execsql_test 100 {
           20  +  PRAGMA onconflict;
           21  +} {ABORT}
           22  +do_execsql_test 110 {
           23  +  PRAGMA onconflict=Rollback;
           24  +} {ROLLBACK}
           25  +do_execsql_test 120 {
           26  +  PRAGMA onconflict=fail;
           27  +} {FAIL}
           28  +do_execsql_test 130 {
           29  +  PRAGMA onconflict=whatever;
           30  +} {FAIL}
           31  +do_execsql_test 140 {
           32  +  PRAGMA onconflict=abort;
           33  +} {ABORT}
           34  +
           35  +do_catchsql_test 200 {
           36  +  CREATE TABLE t1(a, b UNIQUE);
           37  +  INSERT INTO t1(a,b) VALUES(1,1),(2,2),(3,3),(98,98),(99,99);
           38  +  BEGIN;
           39  +  UPDATE t1 SET a=100 WHERE a=101;
           40  +  UPDATE t1 SET b=10;
           41  +} {1 {UNIQUE constraint failed: t1.b}}
           42  +do_execsql_test 201 {
           43  +  SELECT a, b FROM t1 ORDER BY a;
           44  +} {1 1 2 2 3 3 98 98 99 99}
           45  +
           46  +do_catchsql_test 210 {
           47  +  PRAGMA onconflict=FAIL;
           48  +  UPDATE t1 SET b=10;
           49  +} {1 {UNIQUE constraint failed: t1.b}}
           50  +do_execsql_test 211 {
           51  +  SELECT a, b FROM t1 ORDER BY a;
           52  +} {1 10 2 2 3 3 98 98 99 99}
           53  +
           54  +do_catchsql_test 220 {
           55  +  ROLLBACK;
           56  +  BEGIN;
           57  +  PRAGMA onconflict=ROLLBACK;
           58  +  UPDATE t1 SET b=10;
           59  +} {1 {UNIQUE constraint failed: t1.b}}
           60  +do_execsql_test 221 {
           61  +  SELECT a, b FROM t1 ORDER BY a;
           62  +} {1 1 2 2 3 3 98 98 99 99}
           63  +do_catchsql_test 222 {
           64  +  ROLLBACK
           65  +} {1 {cannot rollback - no transaction is active}}
           66  +
           67  +do_catchsql_test 300 {
           68  +  PRAGMA onconflict=ABORT;
           69  +  BEGIN;
           70  +  INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6);
           71  +} {1 {UNIQUE constraint failed: t1.b}}
           72  +do_execsql_test 301 {
           73  +  SELECT a, b FROM t1 ORDER BY a;
           74  +} {1 1 2 2 3 3 98 98 99 99}
           75  +
           76  +do_catchsql_test 310 {
           77  +  PRAGMA onconflict=ROLLBACK;
           78  +  INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6);
           79  +} {1 {UNIQUE constraint failed: t1.b}}
           80  +do_execsql_test 311 {
           81  +  SELECT a, b FROM t1 ORDER BY a;
           82  +} {1 1 2 2 3 3 98 98 99 99}
           83  +do_catchsql_test 312 {
           84  +  ROLLBACK
           85  +} {1 {cannot rollback - no transaction is active}}
           86  +
           87  +do_catchsql_test 320 {
           88  +  PRAGMA onconflict=FAIL;
           89  +  BEGIN;
           90  +  INSERT INTO t1(a,b) VALUES(4,4),(5,1),(6,6);
           91  +} {1 {UNIQUE constraint failed: t1.b}}
           92  +do_execsql_test 321 {
           93  +  SELECT a, b FROM t1 ORDER BY a;
           94  +} {1 1 2 2 3 3 4 4 98 98 99 99}
           95  +do_catchsql_test 322 {
           96  +  ROLLBACK
           97  +} {0 {}}
           98  +
           99  +finish_test