| # 2005 August 18 |
| # |
| # 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 file is testing corner cases of the DEFAULT syntax |
| # on table definitions. |
| # |
| # $Id: default.test,v 1.3 2009/02/19 14:39:25 danielk1977 Exp $ |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| ifcapable bloblit { |
| do_test default-1.1 { |
| execsql { |
| CREATE TABLE t1( |
| a INTEGER, |
| b BLOB DEFAULT x'6869' |
| ); |
| INSERT INTO t1(a) VALUES(1); |
| SELECT * from t1; |
| } |
| } {1 hi} |
| } |
| do_test default-1.2 { |
| execsql { |
| CREATE TABLE t2( |
| x INTEGER, |
| y INTEGER DEFAULT NULL |
| ); |
| INSERT INTO t2(x) VALUES(1); |
| SELECT * FROM t2; |
| } |
| } {1 {}} |
| do_test default-1.3 { |
| catchsql { |
| CREATE TABLE t3( |
| x INTEGER, |
| y INTEGER DEFAULT (max(x,5)) |
| ) |
| } |
| } {1 {default value of column [y] is not constant}} |
| |
| ifcapable pragma { |
| do_test default-2.1 { |
| execsql { |
| CREATE TABLE t4(c DEFAULT 'abc'); |
| PRAGMA table_info(t4); |
| } |
| } {0 c {} 0 'abc' 0} |
| do_test default-2.2 { |
| execsql { |
| INSERT INTO t4 DEFAULT VALUES; |
| PRAGMA table_info(t4); |
| } |
| } {0 c {} 0 'abc' 0} |
| } |
| |
| do_execsql_test default-3.1 { |
| CREATE TABLE t3( |
| a INTEGER PRIMARY KEY AUTOINCREMENT, |
| b INT DEFAULT 12345 UNIQUE NOT NULL CHECK( b>=0 AND b<99999 ), |
| c VARCHAR(123,456) DEFAULT 'hello' NOT NULL ON CONFLICT REPLACE, |
| d REAL, |
| e FLOATING POINT(5,10) DEFAULT 4.36, |
| f NATIONAL CHARACTER(15) COLLATE RTRIM, |
| g LONG INTEGER DEFAULT( 3600*12 ) |
| ); |
| INSERT INTO t3 VALUES(null, 5, 'row1', '5.25', 'xyz', 321, '432'); |
| SELECT a, typeof(a), b, typeof(b), c, typeof(c), |
| d, typeof(d), e, typeof(e), f, typeof(f), |
| g, typeof(g) FROM t3; |
| } {1 integer 5 integer row1 text 5.25 real xyz text 321 text 432 integer} |
| do_execsql_test default-3.2 { |
| DELETE FROM t3; |
| INSERT INTO t3 DEFAULT VALUES; |
| SELECT * FROM t3; |
| } {2 12345 hello {} 4.36 {} 43200} |
| do_execsql_test default-3.3 { |
| CREATE TABLE t300( |
| a INT DEFAULT 2147483647, |
| b INT DEFAULT 2147483648, |
| c INT DEFAULT +9223372036854775807, |
| d INT DEFAULT -2147483647, |
| e INT DEFAULT -2147483648, |
| f INT DEFAULT -9223372036854775808, |
| g INT DEFAULT (-(-9223372036854775808)), |
| h INT DEFAULT (-(-9223372036854775807)) |
| ); |
| INSERT INTO t300 DEFAULT VALUES; |
| SELECT * FROM t300; |
| } {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807} |
| |
| # Do now allow bound parameters in new DEFAULT values. |
| # Silently convert bound parameters to NULL in DEFAULT causes |
| # in the sqlite_master table, for backwards compatibility. |
| # |
| db close |
| forcedelete test.db |
| sqlite3 db test.db |
| sqlite3_db_config db DEFENSIVE 0 |
| do_execsql_test default-4.0 { |
| CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99)); |
| PRAGMA writable_schema=ON; |
| UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))'; |
| } {} |
| db close |
| sqlite3 db test.db |
| do_execsql_test default-4.1 { |
| INSERT INTO t1(a) VALUES('xyzzy'); |
| SELECT a, quote(b) FROM t1; |
| } {xyzzy NULL} |
| do_catchsql_test default-4.2 { |
| CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz)); |
| } {1 {default value of column [b] is not constant}} |
| do_catchsql_test default-4.3 { |
| CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz))); |
| } {1 {default value of column [b] is not constant}} |
| do_catchsql_test default-4.4 { |
| CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz))); |
| } {1 {default value of column [b] is not constant}} |
| |
| finish_test |