/ Check-in [b78005b6]
Login

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

Overview
Comment:A few more tests for upsert.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b78005b6d41640203c163ffde4faf9336f11f47f42e8b7fe10b95415bbaed028
User & Date: dan 2018-04-21 14:11:18
Context
2018-04-21
20:24
Enhance LEMON to track which symbols actually carry semantic content. Output the list of symbols that do not carry content at the end of the report, but do not (yet) do anything else with the information. check-in: dcf2bafc user: drh tags: trunk
14:11
A few more tests for upsert. check-in: b78005b6 user: dan tags: trunk
13:51
Add the %extra_context directive to lemon, as an alternative to %extra_argument. Use this to improve the performance of the parser. check-in: be47a6f5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/upsert4.test.

   327    327         DO UPDATE SET w = w||tbl.w;
   328    328       SELECT * FROM t1;
   329    329     } {c 1 1 1 bbbbbbbb 2 2 2}
   330    330   }
   331    331   
   332    332   foreach {tn sql} {
   333    333     1 {
   334         -    CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b'));
          334  +    CREATE TABLE excluded(w, x INTEGER, 'a b', z, PRIMARY KEY(x, 'a b'));
   335    335       CREATE UNIQUE INDEX zz ON excluded(z);
          336  +    CREATE INDEX zz2 ON excluded(z);
   336    337     }
   337    338     2 {
   338    339       CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b')) WITHOUT ROWID;
   339    340       CREATE UNIQUE INDEX zz ON excluded(z);
          341  +    CREATE INDEX zz2 ON excluded(z);
   340    342     }
   341    343   } {
   342    344     reset_db
   343    345     execsql $sql
   344    346     do_execsql_test 8.$tn.0 {
   345    347       INSERT INTO excluded VALUES('a', 1, 1, 1);
   346    348       INSERT INTO excluded VALUES('b', 2, 2, 2);
................................................................................
   355    357     } {a 1 1 1 b 2 2 2}
   356    358   
   357    359     do_execsql_test 8.$tn.2 {
   358    360       INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
   359    361         DO UPDATE SET w=excluded.w;
   360    362       SELECT * FROM excluded;
   361    363     } {hello 1 1 1 b 2 2 2}
          364  +
          365  +  do_execsql_test 8.$tn.3 {
          366  +    INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
          367  +      DO UPDATE SET w=w||w WHERE excluded.w!='hello';
          368  +    SELECT * FROM excluded;
          369  +  } {hello 1 1 1 b 2 2 2}
          370  +
          371  +  do_execsql_test 8.$tn.4 {
          372  +    INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
          373  +      DO UPDATE SET w=w||w WHERE excluded.x=1;
          374  +    SELECT * FROM excluded;
          375  +  } {hellohello 1 1 1 b 2 2 2}
          376  +
          377  +  do_catchsql_test 8.$tn.5 {
          378  +    INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) 
          379  +      ON CONFLICT(x, [a b]) WHERE y=1
          380  +      DO UPDATE SET w=w||w WHERE excluded.x=1;
          381  +  } {1 {no such column: y}}
          382  +}
          383  +
          384  +#--------------------------------------------------------------------------
          385  +#
          386  +do_execsql_test 9.0 {
          387  +  CREATE TABLE v(x INTEGER);
          388  +  CREATE TABLE hist(x INTEGER PRIMARY KEY, cnt INTEGER);
          389  +  CREATE TRIGGER vt AFTER INSERT ON v BEGIN
          390  +    INSERT INTO hist VALUES(new.x, 1) ON CONFLICT(x) DO
          391  +      UPDATE SET cnt=cnt+1;
          392  +  END;
          393  +}
          394  +
          395  +do_execsql_test 9.1 {
          396  +  INSERT INTO v VALUES(1), (4), (1), (5), (5), (8), (9), (1);
          397  +  SELECT * FROM hist;
          398  +} {
          399  +  1 3
          400  +  4 1
          401  +  5 2
          402  +  8 1
          403  +  9 1
   362    404   }
   363    405   
   364    406   
   365    407   finish_test

Added test/upsertfault.test.

            1  +# 2018-04-17
            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  +#
           12  +# Test cases for UPSERT
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix upsertfault
           17  +
           18  +do_execsql_test 1.0 {
           19  +  CREATE TABLE t1(a PRIMARY KEY, b, c, d, UNIQUE(b, c));
           20  +  INSERT INTO t1 VALUES(1, 1, 1, 1);
           21  +  INSERT INTO t1 VALUES(2, 2, 2, 2);
           22  +}
           23  +faultsim_save_and_close
           24  +
           25  +do_faultsim_test 1 -faults oom* -prep {
           26  +  faultsim_restore_and_reopen
           27  +  db eval { SELECT * FROM sqlite_master } 
           28  +} -body {
           29  +  execsql {
           30  +     INSERT INTO t1 VALUES(3, 2, 2, NULL) ON CONFLICT(b, c) DO
           31  +       UPDATE SET d=d+1;
           32  +  }
           33  +} -test {
           34  +  faultsim_test_result {0 {}}
           35  +}
           36  +
           37  +
           38  +finish_test