/ Artifact Content
Login

Artifact 6b2ebbe3a8fc3222ba773c7f884900367ef528cd90be361468fb15eb97dfcc51:


# 2018 August 24
#
# 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 altertab

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

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, CHECK(t1.a != t1.b));

  CREATE TABLE t2(a, b);
  CREATE INDEX t2expr ON t2(a) WHERE t2.b>0;
}

do_execsql_test 1.1 {
  SELECT sql FROM sqlite_master
} {
  {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
  {CREATE TABLE t2(a, b)}
  {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
}

do_execsql_test 1.2 {
  ALTER TABLE t1 RENAME TO t1new;
}

do_execsql_test 1.3 {
  CREATE TABLE t3(c, d);
  ALTER TABLE t3 RENAME TO t3new;
  DROP TABLE t3new;
}

do_execsql_test 1.4 {
  SELECT sql FROM sqlite_master
} {
  {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))}
  {CREATE TABLE t2(a, b)}
  {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
}


do_execsql_test 1.3 {
  ALTER TABLE t2 RENAME TO t2new;
}
do_execsql_test 1.4 {
  SELECT sql FROM sqlite_master
} {
  {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))}
  {CREATE TABLE "t2new"(a, b)}
  {CREATE INDEX t2expr ON "t2new"(a) WHERE "t2new".b>0}
}


#-------------------------------------------------------------------------
reset_db
register_echo_module db

do_execsql_test 2.0 {
  CREATE TABLE abc(a, b, c);
  INSERT INTO abc VALUES(1, 2, 3);
  CREATE VIRTUAL TABLE eee USING echo('abc');
  SELECT * FROM eee;
} {1 2 3}

do_execsql_test 2.1 {
  ALTER TABLE eee RENAME TO fff;
  SELECT * FROM fff;
} {1 2 3}

db close
sqlite3 db test.db

do_catchsql_test 2.2 {
  ALTER TABLE fff RENAME TO ggg;
} {1 {no such module: echo}}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 3.0 {
  CREATE TABLE txx(a, b, c);
  INSERT INTO txx VALUES(1, 2, 3);
  CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx;
  CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one;
  CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx;
}

do_execsql_test 3.1.1 {
  SELECT * FROM vvv;
} {1 2 3}
do_execsql_test 3.1.2 {
  ALTER TABLE txx RENAME TO "t xx";
  SELECT * FROM vvv;
} {1 2 3}
do_execsql_test 3.1.3 {
  SELECT sql FROM sqlite_master WHERE name='vvv';
} {{CREATE VIEW vvv AS SELECT main."t xx".a, "t xx".b, c FROM "t xx"}}


do_execsql_test 3.2.1 {
  SELECT * FROM uuu;
} {1 2 3}
do_execsql_test 3.2.2 {
  SELECT sql FROM sqlite_master WHERE name='uuu';;
} {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM "t xx" AS one}}

do_execsql_test 3.3.1 {
  SELECT * FROM ttt;
} {1 2 2 1}
do_execsql_test 3.3.2 {
  SELECT sql FROM sqlite_temp_master WHERE name='ttt';
} {{CREATE VIEW ttt AS SELECT main."t xx".a, "t xx".b, one.b, main.one.a FROM "t xx" AS one, "t xx"}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE table t1(x, y);
  CREATE table t2(a, b);

  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    SELECT t1.x, * FROM t1, t2;
    INSERT INTO t2 VALUES(new.x, new.y);
  END;
}

do_execsql_test 4.1 {
  INSERT INTO t1 VALUES(1, 1);
  ALTER TABLE t1 RENAME TO t11;
  INSERT INTO t11 VALUES(2, 2);
  ALTER TABLE t2 RENAME TO t22;
  INSERT INTO t11 VALUES(3, 3);
}

proc squish {a} {
  string trim [regsub -all {[[:space:]][[:space:]]*} $a { }]
}
db func squish squish
do_test 4.2 {
  execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' }
} [list [squish {
  CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN
    SELECT "t11".x, * FROM "t11", "t22";
    INSERT INTO "t22" VALUES(new.x, new.y);
  END
}]]


finish_test