# 2004 November 10## 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.##*************************************************************************# This file implements regression tests for SQLite library.  The# focus of this script is testing the ALTER TABLE statement.## $Id: alter.test,v 1.17 2006/02/09 02:56:03 drh Exp $#set testdir [file dirname $argv0]source $testdir/tester.tcl# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.ifcapable !altertable {  finish_test  return}#----------------------------------------------------------------------# Test organization:## alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables#     with implicit and explicit indices. These tests came from an earlier#     fork of SQLite that also supported ALTER TABLE.# alter-1.8.*: Tests for ALTER TABLE when the table resides in an #     attached database.# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the#     table name and left parenthesis token. i.e: #     "CREATE TABLE abc       (a, b, c);"# alter-2.*: Test error conditions and messages.# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.## Create some tables to rename.  Be sure to include some TEMP tables# and some tables with odd names.#do_test alter-1.1 {  ifcapable tempdb {    set ::temp TEMP  } else {    set ::temp {}  }  execsql [subst -nocommands {    CREATE TABLE t1(a,b);    INSERT INTO t1 VALUES(1,2);    CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);    INSERT INTO [t1'x1] VALUES(3,4);    CREATE INDEX t1i1 ON T1(B);    CREATE INDEX t1i2 ON t1(a,b);    CREATE INDEX i3 ON [t1'x1](b,c);    CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);    CREATE INDEX i2 ON [temp table](f);    INSERT INTO [temp table] VALUES(5,6,7);  }]  execsql {    SELECT 't1', * FROM t1;    SELECT 't1''x1', * FROM "t1'x1";    SELECT * FROM [temp table];  }} {t1 1 2 t1'x1 3 4 5 6 7}do_test alter-1.2 {  execsql [subst {    CREATE $::temp TABLE objlist(type, name, tbl_name);    INSERT INTO objlist SELECT type, name, tbl_name         FROM sqlite_master WHERE NAME!='objlist';  }]  ifcapable tempdb {    execsql {      INSERT INTO objlist SELECT type, name, tbl_name           FROM sqlite_temp_master WHERE NAME!='objlist';    }  }  execsql {    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;  }} [list \     table t1                              t1             \     index t1i1                            t1             \     index t1i2                            t1             \     table t1'x1                           t1'x1          \     index i3                              t1'x1          \     index {sqlite_autoindex_t1'x1_1}      t1'x1          \     index {sqlite_autoindex_t1'x1_2}      t1'x1          \     table {temp table}                    {temp table}   \     index i2                              {temp table}   \     index {sqlite_autoindex_temp table_1} {temp table}   \  ]# Make some changes#do_test alter-1.3 {  execsql {    ALTER TABLE [T1] RENAME to [-t1-];    ALTER TABLE "t1'x1" RENAME TO T2;    ALTER TABLE [temp table] RENAME to TempTab;  }} {}integrity_check alter-1.3.1do_test alter-1.4 {  execsql {    SELECT 't1', * FROM [-t1-];    SELECT 't2', * FROM t2;    SELECT * FROM temptab;  }} {t1 1 2 t2 3 4 5 6 7}do_test alter-1.5 {  execsql {    DELETE FROM objlist;    INSERT INTO objlist SELECT type, name, tbl_name        FROM sqlite_master WHERE NAME!='objlist';  }  catchsql {    INSERT INTO objlist SELECT type, name, tbl_name         FROM sqlite_temp_master WHERE NAME!='objlist';  }  execsql {    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;  }} [list \     table -t1-                         -t1-        \     index t1i1                         -t1-        \     index t1i2                         -t1-        \     table T2                           T2          \     index i3                           T2          \     index {sqlite_autoindex_T2_1}      T2          \     index {sqlite_autoindex_T2_2}      T2          \     table {TempTab}                    {TempTab}   \     index i2                           {TempTab}   \     index {sqlite_autoindex_TempTab_1} {TempTab}   \  ]# Make sure the changes persist after restarting the database.# (The TEMP table will not persist, of course.)#ifcapable tempdb {  do_test alter-1.6 {    db close    sqlite3 db test.db    set DB [sqlite3_connection_pointer db]    execsql {      CREATE TEMP TABLE objlist(type, name, tbl_name);      INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;      INSERT INTO objlist           SELECT type, name, tbl_name FROM sqlite_temp_master           WHERE NAME!='objlist';      SELECT type, name, tbl_name FROM objlist           ORDER BY tbl_name, type desc, name;    }  } [list \       table -t1-                         -t1-           \       index t1i1                         -t1-           \       index t1i2                         -t1-           \       table T2                           T2          \       index i3                           T2          \       index {sqlite_autoindex_T2_1}      T2          \       index {sqlite_autoindex_T2_2}      T2          \    ]} else {  execsql {    DROP TABLE TempTab;  }}# Make sure the ALTER TABLE statements work with the# non-callback API#do_test alter-1.7 {  stepsql $DB {    ALTER TABLE [-t1-] RENAME to [*t1*];    ALTER TABLE T2 RENAME TO [<t2>];  }  execsql {    DELETE FROM objlist;    INSERT INTO objlist SELECT type, name, tbl_name        FROM sqlite_master WHERE NAME!='objlist';  }  catchsql {    INSERT INTO objlist SELECT type, name, tbl_name         FROM sqlite_temp_master WHERE NAME!='objlist';  }  execsql {    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;  }} [list \     table *t1*                         *t1*           \     index t1i1                         *t1*           \     index t1i2                         *t1*           \     table <t2>                         <t2>          \     index i3                           <t2>          \     index {sqlite_autoindex_<t2>_1}    <t2>          \     index {sqlite_autoindex_<t2>_2}    <t2>          \  ]# Check that ALTER TABLE works on attached databases.#do_test alter-1.8.1 {  file delete -force test2.db  file delete -force test2.db-journal  execsql {    ATTACH 'test2.db' AS aux;  }} {}do_test alter-1.8.2 {  execsql {    CREATE TABLE t4(a PRIMARY KEY, b, c);    CREATE TABLE aux.t4(a PRIMARY KEY, b, c);    CREATE INDEX i4 ON t4(b);    CREATE INDEX aux.i4 ON t4(b);  }} {}do_test alter-1.8.3 {  execsql {    INSERT INTO t4 VALUES('main', 'main', 'main');    INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');    SELECT * FROM t4 WHERE a = 'main';  }} {main main main}do_test alter-1.8.4 {  execsql {    ALTER TABLE t4 RENAME TO t5;    SELECT * FROM t4 WHERE a = 'aux';  }} {aux aux aux}do_test alter-1.8.5 {  execsql {    SELECT * FROM t5;  }} {main main main}do_test alter-1.8.6 {  execsql {    SELECT * FROM t5 WHERE b = 'main';  }} {main main main}do_test alter-1.8.7 {  execsql {    ALTER TABLE aux.t4 RENAME TO t5;    SELECT * FROM aux.t5 WHERE b = 'aux';  }} {aux aux aux}do_test alter-1.9.1 {  execsql {    CREATE TABLE tbl1   (a, b, c);    INSERT INTO tbl1 VALUES(1, 2, 3);  }} {}do_test alter-1.9.2 {  execsql {    SELECT * FROM tbl1;  }} {1 2 3}do_test alter-1.9.3 {  execsql {    ALTER TABLE tbl1 RENAME TO tbl2;    SELECT * FROM tbl2;  }} {1 2 3}do_test alter-1.9.4 {  execsql {    DROP TABLE tbl2;  }} {}# Test error messages#do_test alter-2.1 {  catchsql {    ALTER TABLE none RENAME TO hi;  }} {1 {no such table: none}}do_test alter-2.2 {  execsql {    CREATE TABLE t3(p,q,r);  }  catchsql {    ALTER TABLE [<t2>] RENAME TO t3;  }} {1 {there is already another table or index with this name: t3}}do_test alter-2.3 {  catchsql {    ALTER TABLE [<t2>] RENAME TO i3;  }} {1 {there is already another table or index with this name: i3}}do_test alter-2.4 {  catchsql {    ALTER TABLE SqLiTe_master RENAME TO master;  }} {1 {table sqlite_master may not be altered}}do_test alter-2.5 {  catchsql {    ALTER TABLE t3 RENAME TO sqlite_t3;  }} {1 {object name reserved for internal use: sqlite_t3}}# If this compilation does not include triggers, omit the alter-3.* tests.ifcapable trigger {#-----------------------------------------------------------------------# Tests alter-3.* test ALTER TABLE on tables that have triggers.## alter-3.1.*: ALTER TABLE with triggers.# alter-3.2.*: Test that the ON keyword cannot be used as a database,#     table or column name unquoted. This is done because part of the#     ALTER TABLE code (specifically the implementation of SQL function#     "sqlite_alter_trigger") will break in this case.# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).## An SQL user-function for triggers to fire, so that we know they