# 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".
#
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 {
sqlite3_db_config db LEGACY_FILE_FORMAT 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);
INSERT INTO t1 VALUES(1,2);
}
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
forcedelete test.db
set ::DB [sqlite3 db test.db]
sqlite3_db_config db LEGACY_FILE_FORMAT 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-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 {
forcedelete test2.db
forcedelete 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]
} {3 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
} {3}
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]
# 2021-07-20: Add support for detecting CHECK and NOT NULL constraint
# violations in ALTER TABLE ADD COLUMN
#
reset_db
do_execsql_test alter3-9.1 {
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1, 2), ('null!',NULL), (3,4);
} {}
do_catchsql_test alter3-9.2 {
ALTER TABLE t1 ADD COLUMN c CHECK(a!=1);
} {1 {CHECK constraint failed}}
do_catchsql_test alter3-9.3 {
ALTER TABLE t1 ADD COLUMN c CHECK(a!=3);
} {1 {CHECK constraint failed}}
do_catchsql_test alter3-9.4 {
ALTER TABLE t1 ADD COLUMN c CHECK(a!=2);
} {0 {}}
do_catchsql_test alter3-9.5 {
ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL;
} {1 {NOT NULL constraint failed}}
do_catchsql_test alter3-9.6 {
ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=1);
} {1 {CHECK constraint failed}}
do_catchsql_test alter3-9.7 {
ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=3);
} {1 {NOT NULL constraint failed}}
do_execsql_test alter3-9.10 {
CREATE TEMP TABLE t0(m,n);
INSERT INTO t0 VALUES(1, 2), ('null!',NULL), (3,4);
ATTACH ':memory:' AS aux1;
CREATE TABLE aux1.t2(x,y);
INSERT INTO t2 VALUES(1, 2), ('null!',NULL), (3,4);
} {}
do_catchsql_test alter3-9.11 {
ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=1);
} {1 {CHECK constraint failed}}
do_catchsql_test alter3-9.12 {
ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=3);
} {1 {NOT NULL constraint failed}}
do_catchsql_test alter3-9.13 {
ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=1);
} {1 {CHECK constraint failed}}
do_catchsql_test alter3-9.14 {
ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=3);
} {1 {NOT NULL constraint failed}}
finish_test