Artifact 814369c72a7ed777ab2acf3f17fcff5ecb724816eb7c6659f40ef87b09521c99:

• File test/altertab2.test — part of check-in [f44bc7a8] at 2018-12-05 13:49:04 on branch trunk — Ensure that ALTER TABLE modifies table and column names embedded in WITH clauses that are part of views and triggers. (user: dan size: 3846) [more...]

# 2018 September 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix altertab2

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}

ifcapable fts5 {
  do_execsql_test 1.0 {
    CREATE TABLE rr(a, b);
    CREATE VIRTUAL TABLE ff USING fts5(a, b);
    CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
      INSERT INTO ff VALUES(new.a, new.b);
    END;
    INSERT INTO rr VALUES('hello', 'world');
    SELECT * FROM ff;
  } {hello world}

  do_execsql_test 1.1 {
    ALTER TABLE ff RENAME TO ffff;
  }

  do_execsql_test 1.2 {
    INSERT INTO rr VALUES('in', 'tcl');
    SELECT * FROM ffff;
  } {hello world in tcl}
}

#-------------------------------------------------------------------------
# Check that table names that appear in REFERENCES clauses are updated
# when a table is renamed unless:
#
#   a) "PRAGMA legacy_alter_table" is true, and
#   b) "PRAGMA foreign_keys" is false.
#
do_execsql_test 2.0 {
  CREATE TABLE p1(a PRIMARY KEY, b);
  CREATE TABLE c1(x REFERENCES p1);
  CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);
  CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));
}

do_execsql_test 2.1 {
  ALTER TABLE p1 RENAME TO p2;
  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
  {CREATE TABLE c1(x REFERENCES "p2")}
  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
}

do_execsql_test 2.2 {
  PRAGMA legacy_alter_table = 1;
  ALTER TABLE p2 RENAME TO p3;
  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
  {CREATE TABLE c1(x REFERENCES "p2")}
  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
}

do_execsql_test 2.3 {
  ALTER TABLE p3 RENAME TO p2;
  PRAGMA foreign_keys = 1;
  ALTER TABLE p2 RENAME TO p3;
  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
  {CREATE TABLE c1(x REFERENCES "p3")}
  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}
  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}
}

#-------------------------------------------------------------------------
# Table name in WITH clauses that are part of views or triggers.
#
foreach {tn schema} {
  1 {
    CREATE TABLE log_entry(col1, y);
    CREATE INDEX i1 ON log_entry(col1);
  }

  2 {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(x);
    CREATE TABLE log_entry(col1);
    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
      INSERT INTO t2 SELECT col1 FROM log_entry;
    END;
  }

  3 {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(x);
    CREATE TABLE log_entry(col1);
    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
      INSERT INTO t2
        WITH xyz(x) AS (SELECT col1 FROM log_entry)
        SELECT x FROM xyz;
    END;
  }

  4 {
    CREATE TABLE log_entry(col1);
    CREATE VIEW ttt AS
        WITH xyz(x) AS (SELECT col1 FROM log_entry)
        SELECT x FROM xyz;
  }
} {
  reset_db
  do_execsql_test 3.$tn.1 $schema
  set expect [db eval "SELECT sql FROM sqlite_master"]
  set expect [string map {log_entry {"newname"}} $expect]

  do_execsql_test 3.$tn.2 {
    ALTER TABLE log_entry RENAME TO newname;
    SELECT sql FROM sqlite_master;
  } $expect

  reset_db
  do_execsql_test 3.$tn.3 $schema
  set expect [db eval "SELECT sql FROM sqlite_master"]
  set expect [string map {col1 newname} $expect]

  do_execsql_test 3.$tn.4 {
    ALTER TABLE log_entry RENAME col1 TO newname;
    SELECT sql FROM sqlite_master;
  } $expect
}

finish_test