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