AryaWu/sqlite

1# 2022 May 272#3# The author disclaims copyright to this source code.  In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix altertrig16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19  finish_test20  return21}22 23proc collapse_whitespace {in} {24  regsub -all {[ \t\n]+} [string trim $in] { }25}26 27proc do_whitespace_sql_test {tn sql res} {28  set got [execsql $sql]29  set wgot [list]30  set wres [list]31  foreach g $got { lappend wgot [collapse_whitespace $g] }32  foreach r $res { lappend wres [collapse_whitespace $r] }33 34  uplevel [list do_test $tn [list set {} $wgot] $wres]35}36 37do_execsql_test 1.0 {38  CREATE TABLE t1(x);39  CREATE TABLE t2(y);40  CREATE TABLE t3(z);41  CREATE TABLE t4(a);42 43  CREATE TRIGGER r1 INSERT ON t1 BEGIN 44    UPDATE t1 SET d='xyz' FROM t2, t3;45  END;46}47 48do_whitespace_sql_test 1.1 {49  ALTER TABLE t3 RENAME TO t5;50  SELECT sql FROM sqlite_schema WHERE type='trigger';51} {{52  CREATE TRIGGER r1 INSERT ON t1 BEGIN 53    UPDATE t1 SET d='xyz' FROM t2, "t5"; 54  END55}}56 57do_execsql_test 1.2 {58  DROP TRIGGER r1;59  CREATE TRIGGER r1 INSERT ON t1 BEGIN 60    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t5); 61  END;62}63 64do_whitespace_sql_test 1.3 {65  ALTER TABLE t5 RENAME TO t3;66  SELECT sql FROM sqlite_schema WHERE type='trigger';67} {{68  CREATE TRIGGER r1 INSERT ON t1 BEGIN 69    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t3"); 70  END71}}72 73foreach {tn alter update final} {74  1 {75    ALTER TABLE t3 RENAME TO t1076  } {77    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t3)78  } {79    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t10")80  }81 82  2 {83    ALTER TABLE t3 RENAME TO t1084  } {85    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))86  } {87    UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10"))88  }89 90  3 {91    ALTER TABLE t3 RENAME e TO abc92  } {93    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))94  } {95    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3))96  }97 98  4 {99    ALTER TABLE t2 RENAME c TO abc100  } {101    UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c)102  } {103    UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE abc)104  }105 106  5 {107    ALTER TABLE t2 RENAME c TO abc108  } {109    UPDATE t1 SET a=t2.c FROM t2110  } {111    UPDATE t1 SET a=t2.abc FROM t2112  }113 114  6 {115    ALTER TABLE t2 RENAME c TO abc116  } {117    UPDATE t1 SET a=t2.c FROM t2, t3118  } {119    UPDATE t1 SET a=t2.abc FROM t2, t3120  }121 122  7 {123    ALTER TABLE t4 RENAME e TO abc124  } {125    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a126  } {127    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.abc=a128  }129 130  8 {131    ALTER TABLE t4 RENAME TO abc132  } {133    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a134  } {135    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN "abc" WHERE "abc".e=a136  }137 138} {139  reset_db140  do_execsql_test 2.$tn.1 {141    CREATE TABLE t1(a,b);142    CREATE TABLE t2(c,d);143    CREATE TABLE t3(e,f);144    CREATE TABLE t4(e,f);145  }146  do_execsql_test 2.$tn.2 "147    CREATE TRIGGER r1 INSERT ON t1 BEGIN 148      $update;149    END150  "151  do_execsql_test 2.$tn.3 $alter152 153  do_whitespace_sql_test 2.$tn.4 {154    SELECT sqL FROM sqlite_schema WHERE type='trigger'155  } "{156    CREATE TRIGGER r1 INSERT ON t1 BEGIN 157      $final;158    END159  }"160}161 162finish_test163