# 2005 February 19## 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 that SQLite can handle a subtle # file format change that may be used in the future to implement# "ALTER TABLE ... ADD COLUMN".## $Id: alter3.test,v 1.9 2006/01/17 09:35:02 danielk1977 Exp $#set testdir [file dirname $argv0]source $testdir/tester.tcl# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.ifcapable !altertable {  finish_test  return}# Determine if there is a codec available on this test.#if {[catch {sqlite3 -has_codec} r] || $r} {  set has_codec 1} else {  set has_codec 0}# Test Organisation:# ------------------## alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.# alter3-2.*: Test error messages.# alter3-3.*: Test adding columns with default value NULL.# alter3-4.*: Test adding columns with default values other than NULL.# alter3-5.*: Test adding columns to tables in ATTACHed databases.# alter3-6.*: Test that temp triggers are not accidentally dropped.# alter3-7.*: Test that VACUUM resets the file-format.## This procedure returns the value of the file-format in file 'test.db'.# proc get_file_format {{fname test.db}} {  set bt [btree_open $fname 10 0]  set meta [btree_get_meta $bt]  btree_close $bt  lindex $meta 2}do_test alter3-1.1 {  execsql {    CREATE TABLE abc(a, b, c);    SELECT sql FROM sqlite_master;  }} {{CREATE TABLE abc(a, b, c)}}do_test alter3-1.2 {  execsql {ALTER TABLE abc ADD d INTEGER;}  execsql {    SELECT sql FROM sqlite_master;  }} {{CREATE TABLE abc(a, b, c, d INTEGER)}}do_test alter3-1.3 {  execsql {ALTER TABLE abc ADD e}  execsql {    SELECT sql FROM sqlite_master;  }} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}do_test alter3-1.4 {  execsql {    CREATE TABLE main.t1(a, b);    ALTER TABLE t1 ADD c;    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';  }} {{CREATE TABLE t1(a, b, c)}}do_test alter3-1.5 {  execsql {    ALTER TABLE t1 ADD d CHECK (a>d);    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';  }} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}ifcapable foreignkey {  do_test alter3-1.6 {    execsql {      CREATE TABLE t2(a, b, UNIQUE(a, b));      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;      SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';    }  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}}do_test alter3-1.7 {  execsql {    CREATE TABLE t3(a, b, UNIQUE(a, b));    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);    SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';  }} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}do_test alter3-1.99 {  catchsql {    # May not exist if foriegn-keys are omitted at compile time.    DROP TABLE t2;   }  execsql {    DROP TABLE abc;     DROP TABLE t1;     DROP TABLE t3;   }} {}do_test alter3-2.1 {  execsql {    CREATE TABLE t1(a, b);  }  catchsql {    ALTER TABLE t1 ADD c PRIMARY KEY;  }} {1 {Cannot add a PRIMARY KEY column}}do_test alter3-2.2 {  catchsql {    ALTER TABLE t1 ADD c UNIQUE  }} {1 {Cannot add a UNIQUE column}}do_test alter3-2.3 {  catchsql {    ALTER TABLE t1 ADD b VARCHAR(10)  }} {1 {duplicate column name: b}}do_test alter3-2.3 {  catchsql {    ALTER TABLE t1 ADD c NOT NULL;  }} {1 {Cannot add a NOT NULL column with default value NULL}}do_test alter3-2.4 {  catchsql {    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;  }} {0 {}}ifcapable view {  do_test alter3-2.5 {    execsql {      CREATE VIEW v1 AS SELECT * FROM t1;    }    catchsql {      alter table v1 add column d;    }  } {1 {Cannot add a column to a view}}}do_test alter3-2.6 {  catchsql {    alter table t1 add column d DEFAULT CURRENT_TIME;  }} {1 {Cannot add a column with non-constant default}}do_test alter3-2.99 {  execsql {    DROP TABLE t1;  }} {}do_test alter3-3.1 {  execsql {    CREATE TABLE t1(a, b);    INSERT INTO t1 VALUES(1, 100);    INSERT INTO t1 VALUES(2, 300);    SELECT * FROM t1;  }} {1 100 2 300}do_test alter3-3.1 {  execsql {    PRAGMA schema_version = 10;  }} {}do_test alter3-3.2 {  execsql {    ALTER TABLE t1 ADD c;    SELECT * FROM t1;  }} {1 100 {} 2 300 {}}if {!$has_codec} {  do_test alter3-3.3 {    get_file_format  } {3}}ifcapable schema_version {  do_test alter3-3.4 {    execsql {      PRAGMA schema_version;    }  } {11}}do_test alter3-4.1 {  db close  file delete -force test.db  set ::DB [sqlite3 db test.db]  execsql {    CREATE TABLE t1(a, b);    INSERT INTO t1 VALUES(1, 100);    INSERT INTO t1 VALUES(2, 300);    SELECT * FROM t1;  }} {1 100 2 300}do_test alter3-4.1 {  execsql {    PRAGMA schema_version = 20;  }} {}do_test alter3-4.2 {  execsql {    ALTER TABLE t1 ADD c DEFAULT 'hello world';    SELECT * FROM t1;  }} {1 100 {hello world} 2 300 {hello world}}if {!$has_codec} {  do_test alter3-4.3 {    get_file_format  } {3}}ifcapable schema_version {  do_test alter3-4.4 {    execsql {      PRAGMA schema_version;    }  } {21}}do_test alter3-4.99 {  execsql {    DROP TABLE t1;  }} {}do_test alter3-5.1 {  file delete -force test2.db  file delete -force test2.db-journal  execsql {    CREATE TABLE t1(a, b);    INSERT INTO t1 VALUES(1, 'one');    INSERT INTO t1 VALUES(2, 'two');    ATTACH 'test2.db' AS aux;    CREATE TABLE aux.t1 AS SELECT * FROM t1;    PRAGMA aux.schema_version = 30;    SELECT sql FROM aux.sqlite_master;  } } {{CREATE TABLE t1(a,b)}}do_test alter3-5.2 {  execsql {    ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);    SELECT sql FROM aux.sqlite_master;  }} {{CREATE TABLE t1(a,b, c VARCHAR(128))}}do_test alter3-5.3 {  execsql {    SELECT * FROM aux.t1;  }} {1 one {} 2 two {}}ifcapable schema_version {  do_test alter3-5.4 {    execsql {      PRAGMA aux.schema_version;    }  } {31}}if {!$has_codec} {  do_test alter3-5.5 {    list [get_file_format test2.db] [get_file_format]  } {2 3}}do_test alter3-5.6 {  execsql {    ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;    SELECT sql FROM aux.sqlite_master;  }} {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}do_test alter3-5.7 {  execsql {    SELECT * FROM aux.t1;  }} {1 one {} 1000 2 two {} 1000}ifcapable schema_version {  do_test alter3-5.8 {    execsql {      PRAGMA aux.schema_version;    }  } {32}}do_test alter3-5.9 {  execsql {    SELECT * FROM t1;  }} {1 one 2 two}do_test alter3-5.99 {  execsql {    DROP TABLE aux.t1;    DROP TABLE t1;  }} {}#----------------------------------------------------------------# Test that the table schema is correctly reloaded when a column# is added to a table.#ifcapable trigger&&tempdb {  do_test alter3-6.1 {    execsql {      CREATE TABLE t1(a, b);      CREATE TABLE log(trig, a, b);      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN        INSERT INTO log VALUES('a', new.a, new.b);      END;      CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN        INSERT INTO log VALUES('b', new.a, new.b);      END;        INSERT INTO t1 VALUES(1, 2);      SELECT * FROM log;    }  } {b 1 2 a 1 2}  do_test alter3-6.2 {    execsql {      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';      INSERT INTO t1(a, b) VALUES(3, 4);      SELECT * FROM log;    }  } {b 1 2 a 1 2 b 3 4 a 3 4}}if {!$has_codec} {  ifcapable vacuum {    do_test alter3-7.1 {      execsql {        VACUUM;      }      get_file_format    } {1}    do_test alter3-7.2 {      execsql {        CREATE TABLE abc(a, b, c);        ALTER TABLE abc ADD d DEFAULT NULL;      }      get_file_format    } {2}    do_test alter3-7.3 {      execsql {        ALTER TABLE abc ADD e DEFAULT 10;      }      get_file_format    } {3}    do_test alter3-7.4 {      execsql {        ALTER TABLE abc ADD f DEFAULT NULL;      }      get_file_format    } {3}    do_test alter3-7.5 {      execsql {        VACUUM;      }      get_file_format    } {1}  }}# Ticket #1183 - Make sure adding columns to large tables does not cause# memory corruption (as was the case before this bug was fixed).do_test alter3-8.1 {  execsql {    CREATE TABLE t4(c1);  }} {}set ::sql ""do_test alter3-8.2 {  set cols c1  for {set i 2} {$i < 100} {incr i} {    execsql "      ALTER TABLE t4 ADD c$i    "    lappend cols c$i  }  set ::sql "CREATE TABLE t4([join $cols {, }])"  list } {}do_test alter3-8.2 {  execsql {    SELECT sql FROM sqlite_master WHERE name = 't4';  }} [list $::sql]finish_test