# 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.11 2008/03/19 00:21:31 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

 }

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

   return [hexio_get_int [hexio_read $fname 44 4]]

 }

 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;

   }

 } {}

 ifcapable attach {

   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