modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/session/sessionG.test (about)

     1  # 2016 March 30
     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  # This file implements regression tests for the sessions module.
    13  # Specifically, it tests that UNIQUE constraints are dealt with correctly.
    14  #
    15  
    16  
    17  
    18  if {![info exists testdir]} {
    19    set testdir [file join [file dirname [info script]] .. .. test]
    20  } 
    21  source [file join [file dirname [info script]] session_common.tcl]
    22  source $testdir/tester.tcl
    23  ifcapable !session {finish_test; return}
    24  set testprefix sessionG
    25  
    26  
    27  forcedelete test.db2
    28  sqlite3 db2 test.db2
    29  
    30  do_test 1.0 {
    31    do_common_sql {
    32      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
    33      INSERT INTO t1 VALUES(1, 'one');
    34      INSERT INTO t1 VALUES(2, 'two');
    35      INSERT INTO t1 VALUES(3, 'three');
    36    }
    37    do_then_apply_sql {
    38      DELETE FROM t1 WHERE a=1;
    39      INSERT INTO t1 VALUES(4, 'one');
    40    }
    41    compare_db db db2
    42  } {}
    43  
    44  do_test 1.1 {
    45    do_then_apply_sql {
    46      DELETE FROM t1 WHERE a=4;
    47      INSERT INTO t1 VALUES(1, 'one');
    48    }
    49    compare_db db db2
    50  } {}
    51  
    52  do_test 1.2 {
    53    execsql { INSERT INTO t1 VALUES(5, 'five') } db2
    54    do_then_apply_sql {
    55      INSERT INTO t1 VALUES(11, 'eleven');
    56      INSERT INTO t1 VALUES(12, 'five');
    57    }
    58    execsql { SELECT * FROM t1 } db2
    59  } {2 two 3 three 1 one 5 five 11 eleven}
    60  
    61  do_test 1.3 {
    62    execsql { SELECT * FROM t1 }
    63  } {2 two 3 three 1 one 11 eleven 12 five}
    64  
    65  #-------------------------------------------------------------------------
    66  #
    67  reset_db
    68  db2 close
    69  forcedelete test.db2
    70  sqlite3 db2 test.db2
    71  
    72  do_test 2.1 {
    73    do_common_sql {
    74      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE);
    75      INSERT INTO t1 VALUES(1, 1, 1);
    76      INSERT INTO t1 VALUES(2, 2, 2);
    77      INSERT INTO t1 VALUES(3, 3, 3);
    78    }
    79  } {}
    80  
    81  do_test 2.2.1 {
    82    # It is not possible to apply the changeset generated by the following
    83    # SQL, as none of the three updated rows may be updated as part of the
    84    # first pass.
    85    do_then_apply_sql {
    86      UPDATE t1 SET b=0 WHERE a=1;
    87      UPDATE t1 SET b=1 WHERE a=2;
    88      UPDATE t1 SET b=2 WHERE a=3;
    89      UPDATE t1 SET b=3 WHERE a=1;
    90    }
    91    db2 eval { SELECT a, b FROM t1 }
    92  } {1 1 2 2 3 3}
    93  do_test 2.2.2 { db eval { SELECT a, b FROM t1 } } {1 3 2 1 3 2}
    94  
    95  #-------------------------------------------------------------------------
    96  #
    97  reset_db
    98  db2 close
    99  forcedelete test.db2
   100  sqlite3 db2 test.db2
   101  
   102  do_test 3.1 {
   103    do_common_sql {
   104      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE);
   105      INSERT INTO t1 VALUES(1, 1, 1);
   106      INSERT INTO t1 VALUES(2, 2, 2);
   107      INSERT INTO t1 VALUES(3, 3, 3);
   108    }
   109  } {}
   110  
   111  do_test 3.3 {
   112    do_then_apply_sql {
   113      UPDATE t1 SET b=4 WHERE a=3;
   114      UPDATE t1 SET b=3 WHERE a=2;
   115      UPDATE t1 SET b=2 WHERE a=1;
   116    }
   117    compare_db db db2
   118  } {}
   119  
   120  do_test 3.4 {
   121    do_then_apply_sql {
   122      UPDATE t1 SET b=1 WHERE a=1;
   123      UPDATE t1 SET b=2 WHERE a=2;
   124      UPDATE t1 SET b=3 WHERE a=3;
   125    }
   126    compare_db db db2
   127  } {}
   128  
   129  #-------------------------------------------------------------------------
   130  #
   131  reset_db
   132  db2 close
   133  forcedelete test.db2
   134  sqlite3 db2 test.db2
   135  
   136  do_test 4.1 {
   137    do_common_sql {
   138      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
   139      INSERT INTO t1 VALUES(1, 1);
   140      INSERT INTO t1 VALUES(2, 2);
   141      INSERT INTO t1 VALUES(3, 3);
   142  
   143      CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
   144      INSERT INTO t2 VALUES(1, 1);
   145      INSERT INTO t2 VALUES(2, 2);
   146      INSERT INTO t2 VALUES(3, 3);
   147    }
   148  } {}
   149  
   150  do_test 4.2 {
   151    do_then_apply_sql {
   152      UPDATE t1 SET b=4 WHERE a=3;
   153      UPDATE t1 SET b=3 WHERE a=2;
   154      UPDATE t1 SET b=2 WHERE a=1;
   155  
   156      UPDATE t2 SET b=0 WHERE a=1;
   157      UPDATE t2 SET b=1 WHERE a=2;
   158      UPDATE t2 SET b=2 WHERE a=3;
   159    }
   160    compare_db db db2
   161  } {}
   162  
   163  do_test 4.3 {
   164    do_then_apply_sql {
   165      UPDATE t1 SET b=1 WHERE a=1;
   166      UPDATE t1 SET b=2 WHERE a=2;
   167      UPDATE t1 SET b=3 WHERE a=3;
   168  
   169      UPDATE t2 SET b=3 WHERE a=3;
   170      UPDATE t2 SET b=2 WHERE a=2;
   171      UPDATE t2 SET b=1 WHERE a=1;
   172    }
   173    compare_db db db2
   174  } {}
   175  
   176  finish_test
   177