test/upfrom3.test

   1 # 2020 July 14
   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
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix upfrom3
  16
  17 # Test plan:
  18 #
  19 #   1.*: Test UPDATE ... FROM statements that modify IPK fields. And that
  20 #        modify "INTEGER PRIMARY KEY" fields on WITHOUT ROWID tables.
  21 #
  22 #   2.*: Test UPDATE ... FROM statements that modify PK fields of WITHOUT
  23 #        ROWID tables.
  24 #
  25 #   3.*: Test that UPDATE ... FROM statements are not confused if there
  26 #        are multiple tables of the same name in attached databases.
  27 #
  28 #   4.*: Tests for UPDATE ... FROM statements and foreign keys.
  29 #
  30
  31 foreach {tn wo} {
  32   1 ""
  33   2 "WITHOUT ROWID"
  34 } {
  35   reset_db
  36   eval [string map [list %WO% $wo %TN% $tn] {
  37
  38   do_execsql_test 1.%TN%.0 {
  39     CREATE TABLE log(t TEXT);
  40     CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z UNIQUE) %WO%;
  41     CREATE INDEX t1y ON t1(y);
  42
  43     INSERT INTO t1 VALUES(1, 'i',   'one');
  44     INSERT INTO t1 VALUES(2, 'ii',  'two');
  45     INSERT INTO t1 VALUES(3, 'iii', 'three');
  46     INSERT INTO t1 VALUES(4, 'iv',  'four');
  47   }
  48
  49   do_execsql_test 1.%TN%.1 {
  50     CREATE TABLE x1(o, n);
  51     INSERT INTO x1 VALUES(1, 11);
  52     INSERT INTO x1 VALUES(2, 12);
  53     INSERT INTO x1 VALUES(3, 13);
  54     INSERT INTO x1 VALUES(4, 14);
  55     UPDATE t1 SET x=n FROM x1 WHERE x=o;
  56     SELECT x, y, z FROM t1 ORDER BY 1;
  57   } {
  58     11 i one
  59     12 ii two
  60     13 iii three
  61     14 iv four
  62   }
  63
  64   do_test 1.%TN%.2 { db changes } 4
  65
  66   do_execsql_test 1.%TN%.3 {
  67     INSERT INTO x1 VALUES(11, 21);
  68     INSERT INTO x1 VALUES(12, 22);
  69     INSERT INTO x1 VALUES(13, 23);
  70     INSERT INTO x1 VALUES(14, 24);
  71
  72     INSERT INTO x1 VALUES(21, 31);
  73     INSERT INTO x1 VALUES(22, 32);
  74     INSERT INTO x1 VALUES(23, 33);
  75     INSERT INTO x1 VALUES(24, 34);
  76     UPDATE t1 SET x=n FROM x1 WHERE x=o;
  77     SELECT x, y, z FROM t1 ORDER BY 1;
  78   } {
  79     21 i one
  80     22 ii two
  81     23 iii three
  82     24 iv four
  83   }
  84
  85   do_execsql_test 1.%TN%.4 {
  86     UPDATE t1 SET x=n FROM x1 WHERE x=o;
  87     SELECT x, y, z FROM t1 ORDER BY 1;
  88   } {
  89     31 i one
  90     32 ii two
  91     33 iii three
  92     34 iv four
  93   }
  94
  95   do_execsql_test 1.%TN%.5 {
  96     INSERT INTO x1 VALUES(31, 32);
  97     INSERT INTO x1 VALUES(33, 34);
  98     UPDATE OR REPLACE t1 SET x=n FROM x1 WHERE x=o;
  99     SELECT x, y, z FROM t1 ORDER BY 1;
 100   } {
 101     32 i one
 102     34 iii three
 103   }
 104
 105   do_execsql_test 1.%TN%.6 {
 106     INSERT INTO t1 VALUES(33, 'ii', 'two');
 107     INSERT INTO t1 VALUES(35, 'iv', 'four');
 108   }
 109
 110   do_execsql_test 1.%TN%.7 {
 111     CREATE TABLE x2(o, n, zz);
 112     INSERT INTO x2 VALUES(32, 41, 'four');
 113     INSERT INTO x2 VALUES(33, 42, 'three');
 114     UPDATE OR IGNORE t1 SET x=n, z=zz FROM x2 WHERE x=o;
 115     SELECT x, y, z FROM t1 ORDER BY 1;
 116   } {
 117     32 i one
 118     33 ii two
 119     34 iii three
 120     35 iv four
 121   }
 122
 123   do_execsql_test 1.%TN%.8 {
 124     UPDATE OR REPLACE t1 SET x=n, z=zz FROM x2 WHERE x=o;
 125     SELECT x, y, z FROM t1 ORDER BY 1;
 126   } {
 127     41 i four
 128     42 ii three
 129   }
 130
 131   }]
 132 }
 133
 134 do_execsql_test 2.1.1 {
 135   CREATE TABLE u1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
 136   INSERT INTO u1 VALUES(0, 0, 0);
 137   INSERT INTO u1 VALUES(1, 0, 1);
 138   INSERT INTO u1 VALUES(2, 1, 0);
 139   INSERT INTO u1 VALUES(3, 1, 1);
 140 }
 141
 142 do_execsql_test 2.1.2 {
 143   CREATE TABLE map(f, t);
 144   INSERT INTO map VALUES(0, 10);
 145   INSERT INTO map VALUES(1, 11);
 146   UPDATE u1 SET c=t FROM map WHERE c=f;
 147   SELECT * FROM u1 ORDER BY a;
 148 } {
 149   0 0 10
 150   1 0 11
 151   2 1 10
 152   3 1 11
 153 }
 154
 155 do_execsql_test 2.1.3 {
 156   UPDATE u1 SET b=t FROM map WHERE b=f;
 157   SELECT * FROM u1 ORDER BY a;
 158 } {
 159   0 10 10
 160   1 10 11
 161   2 11 10
 162   3 11 11
 163 }
 164
 165 do_execsql_test 2.1.4 {
 166   CREATE TABLE map2(o1, o2, n1, n2);
 167   INSERT INTO map2 VALUES
 168     (10, 10, 50, 50), (10, 11, 50, 60),
 169     (11, 10, 60, 50), (11, 11, 60, 60);
 170   UPDATE u1 SET b=n1, c=n2 FROM map2 WHERE b=o1 AND c=o2;
 171   SELECT * FROM u1 ORDER BY a;
 172 } {
 173   0 50 50
 174   1 50 60
 175   2 60 50
 176   3 60 60
 177 }
 178
 179 #-------------------------------------------------------------------------
 180 foreach {tn wo} {
 181   1 ""
 182   2 "WITHOUT ROWID"
 183 } {
 184   reset_db
 185   forcedelete test.db2
 186   eval [string map [list %WO% $wo %TN% $tn] {
 187     do_execsql_test 3.$tn.1 {
 188       CREATE TABLE g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
 189       INSERT INTO g1 VALUES(1, 1, 1);
 190
 191       ATTACH 'test.db2' AS aux;
 192       CREATE TABLE aux.g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
 193       INSERT INTO aux.g1 VALUES(10, 1, 10);
 194       INSERT INTO aux.g1 VALUES(20, 2, 20);
 195       INSERT INTO aux.g1 VALUES(30, 3, 30);
 196     }
 197
 198     do_execsql_test 3.$tn.2 {
 199       UPDATE aux.g1 SET c=101 FROM main.g1;
 200     }
 201     do_execsql_test 3.$tn.3 {
 202       SELECT * FROM aux.g1;
 203     } {10 1 101  20 2 101  30 3 101}
 204
 205     do_execsql_test 3.$tn.4 {
 206       UPDATE g1 SET c=101 FROM g1 AS g2;
 207     }
 208     do_execsql_test 3.$tn.5 {
 209       SELECT * FROM g1;
 210     } {1 1 101}
 211   }]
 212 }
 213
 214 #-------------------------------------------------------------------------
 215 reset_db
 216 foreach {tn wo} {
 217   1 ""
 218   2 "WITHOUT ROWID"
 219 } {
 220   reset_db
 221   forcedelete test.db2
 222   eval [string map [list %WO% $wo %TN% $tn] {
 223
 224     do_execsql_test 4.$tn.1 {
 225       CREATE TABLE p1(a INTEGER PRIMARY KEY, b) %WO%;
 226       CREATE TABLE c1(x PRIMARY KEY, y REFERENCES p1 ON UPDATE CASCADE) %WO%;
 227       PRAGMA foreign_keys = 1;
 228
 229       INSERT INTO p1 VALUES(1, 'one');
 230       INSERT INTO p1 VALUES(11, 'eleven');
 231       INSERT INTO p1 VALUES(111, 'eleventyone');
 232
 233       INSERT INTO c1 VALUES('a', 1);
 234       INSERT INTO c1 VALUES('b', 11);
 235       INSERT INTO c1 VALUES('c', 111);
 236     }
 237
 238     do_execsql_test 4.$tn.2 {
 239       CREATE TABLE map(f, t);
 240       INSERT INTO map VALUES('a', 111);
 241       INSERT INTO map VALUES('c', 112);
 242     }
 243
 244     do_catchsql_test 4.$tn.3 {
 245       UPDATE c1 SET y=t FROM map WHERE x=f;
 246     } {1 {FOREIGN KEY constraint failed}}
 247
 248     do_execsql_test 4.$tn.4 {
 249       INSERT INTO map VALUES('eleven', 12);
 250       INSERT INTO map VALUES('eleventyone', 112);
 251       UPDATE p1 SET a=t FROM map WHERE b=f;
 252     }
 253
 254     do_execsql_test 4.$tn.5 {
 255       SELECT * FROM c1
 256     } {a 1  b 12  c 112}
 257
 258   }]
 259 }
 260
 261 finish_test