# 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.30 2008/05/09 14:17:52 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.

# ...

# alter-12.*: Test ALTER TABLE on views.

#

# 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

#

integrity_check alter-1.3.0

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.1

do_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.

#

ifcapable attach {

  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}}

do_test alter-2.6 {

  catchsql {

    ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);

  }

} {1 {near "(": syntax error}}

# 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

# are working.

proc trigfunc {args} {

  set ::TRIGGER $args

}

db func trigfunc trigfunc

do_test alter-3.1.0 {

  execsql {

    CREATE TABLE t6(a, b, c);

    CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN

      SELECT trigfunc('trig1', new.a, new.b, new.c);

    END;

  }

} {}

do_test alter-3.1.1 {

  execsql {

    INSERT INTO t6 VALUES(1, 2, 3);

  }

  set ::TRIGGER

} {trig1 1 2 3}

do_test alter-3.1.2 {

  execsql {

    ALTER TABLE t6 RENAME TO t7;

    INSERT INTO t7 VALUES(4, 5, 6);

  }

  set ::TRIGGER

} {trig1 4 5 6}

do_test alter-3.1.3 {

  execsql {

    DROP TRIGGER trig1;

  }

} {}

do_test alter-3.1.4 {

  execsql {

    CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN

      SELECT trigfunc('trig2', new.a, new.b, new.c);

    END;

    INSERT INTO t7 VALUES(1, 2, 3);

  }

  set ::TRIGGER

} {trig2 1 2 3}

do_test alter-3.1.5 {

  execsql {

    ALTER TABLE t7 RENAME TO t8;

    INSERT INTO t8 VALUES(4, 5, 6);

  }

  set ::TRIGGER

} {trig2 4 5 6}

do_test alter-3.1.6 {

  execsql {

    DROP TRIGGER trig2;

  }

} {}

do_test alter-3.1.7 {

  execsql {

    CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN

      SELECT trigfunc('trig3', new.a, new.b, new.c);

    END;

    INSERT INTO t8 VALUES(1, 2, 3);

  }

  set ::TRIGGER

} {trig3 1 2 3}

do_test alter-3.1.8 {

  execsql {

    ALTER TABLE t8 RENAME TO t9;

    INSERT INTO t9 VALUES(4, 5, 6);

  }

  set ::TRIGGER

} {trig3 4 5 6}

# Make sure "ON" cannot be used as a database, table or column name without

# quoting. Otherwise the sqlite_alter_trigger() function might not work.

file delete -force test3.db

file delete -force test3.db-journal

ifcapable attach {

  do_test alter-3.2.1 {

    catchsql {

      ATTACH 'test3.db' AS ON;

    }

  } {1 {near "ON": syntax error}}

  do_test alter-3.2.2 {

    catchsql {

      ATTACH 'test3.db' AS 'ON';

    }

  } {0 {}}

  do_test alter-3.2.3 {

    catchsql {

      CREATE TABLE ON.t1(a, b, c); 

    }

  } {1 {near "ON": syntax error}}

  do_test alter-3.2.4 {

    catchsql {

      CREATE TABLE 'ON'.t1(a, b, c); 

    }

  } {0 {}}

  do_test alter-3.2.4 {

    catchsql {

      CREATE TABLE 'ON'.ON(a, b, c); 

    }

  } {1 {near "ON": syntax error}}

  do_test alter-3.2.5 {

    catchsql {

      CREATE TABLE 'ON'.'ON'(a, b, c); 

    }

  } {0 {}}

}

do_test alter-3.2.6 {

  catchsql {

    CREATE TABLE t10(a, ON, c);

  }

} {1 {near "ON": syntax error}}

do_test alter-3.2.7 {

  catchsql {

    CREATE TABLE t10(a, 'ON', c);

  }

} {0 {}}

do_test alter-3.2.8 {

  catchsql {

    CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;

  }

} {1 {near "ON": syntax error}}

ifcapable attach {

  do_test alter-3.2.9 {

    catchsql {

      CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;

    }

  } {0 {}}

}

do_test alter-3.2.10 {

  execsql {

    DROP TABLE t10;

  }

} {}

do_test alter-3.3.1 {

  execsql [subst {

    CREATE TABLE tbl1(a, b, c);

    CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN

      SELECT trigfunc('trig1', new.a, new.b, new.c);

    END;

  }]

} {}

do_test alter-3.3.2 {

  execsql {

    INSERT INTO tbl1 VALUES('a', 'b', 'c');

  }

  set ::TRIGGER

} {trig1 a b c}

do_test alter-3.3.3 {

  execsql {

    ALTER TABLE tbl1 RENAME TO tbl2;

    INSERT INTO tbl2 VALUES('d', 'e', 'f');

  } 

  set ::TRIGGER

} {trig1 d e f}

do_test alter-3.3.4 {

  execsql [subst {

    CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN

      SELECT trigfunc('trig2', new.a, new.b, new.c);

    END;

  }] 

} {}

do_test alter-3.3.5 {

  execsql {

    ALTER TABLE tbl2 RENAME TO tbl3;

    INSERT INTO tbl3 VALUES('g', 'h', 'i');

  } 

  set ::TRIGGER

} {trig1 g h i}

do_test alter-3.3.6 {

  execsql {

    UPDATE tbl3 SET a = 'G' where a = 'g';

  } 

  set ::TRIGGER

} {trig2 G h i}

do_test alter-3.3.7 {

  execsql {

    DROP TABLE tbl3;

  }

} {}

ifcapable tempdb {

  do_test alter-3.3.8 {

    execsql {

      SELECT * FROM sqlite_temp_master WHERE type = 'trigger';

    }

  } {}

}

} ;# ifcapable trigger

# If the build does not include AUTOINCREMENT fields, omit alter-4.*.

ifcapable autoinc {

do_test alter-4.1 {

  execsql {

    CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);

    INSERT INTO tbl1 VALUES(10);

  }

} {}

do_test alter-4.2 {