1# 2005 August 18 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#************************************************************************* 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing corner cases of the DEFAULT syntax 13# on table definitions. 14# 15# $Id: default.test,v 1.3 2009/02/19 14:39:25 danielk1977 Exp $ 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21ifcapable bloblit { 22 do_test default-1.1 { 23 execsql { 24 CREATE TABLE t1( 25 a INTEGER, 26 b BLOB DEFAULT x'6869' 27 ); 28 INSERT INTO t1(a) VALUES(1); 29 SELECT * from t1; 30 } 31 } {1 hi} 32} 33do_test default-1.2 { 34 execsql { 35 CREATE TABLE t2( 36 x INTEGER, 37 y INTEGER DEFAULT NULL 38 ); 39 INSERT INTO t2(x) VALUES(1); 40 SELECT * FROM t2; 41 } 42} {1 {}} 43do_test default-1.3 { 44 catchsql { 45 CREATE TABLE t3( 46 x INTEGER, 47 y INTEGER DEFAULT (max(x,5)) 48 ) 49 } 50} {1 {default value of column [y] is not constant}} 51 52ifcapable pragma { 53 do_test default-2.1 { 54 execsql { 55 CREATE TABLE t4(c DEFAULT 'abc'); 56 PRAGMA table_info(t4); 57 } 58 } {0 c {} 0 'abc' 0} 59 do_test default-2.2 { 60 execsql { 61 INSERT INTO t4 DEFAULT VALUES; 62 PRAGMA table_info(t4); 63 } 64 } {0 c {} 0 'abc' 0} 65} 66 67do_execsql_test default-3.1 { 68 CREATE TABLE t3( 69 a INTEGER PRIMARY KEY AUTOINCREMENT, 70 b INT DEFAULT 12345 UNIQUE NOT NULL CHECK( b>=0 AND b<99999 ), 71 c VARCHAR(123,456) DEFAULT 'hello' NOT NULL ON CONFLICT REPLACE, 72 d REAL, 73 e FLOATING POINT(5,10) DEFAULT 4.36, 74 f NATIONAL CHARACTER(15) COLLATE RTRIM, 75 g LONG INTEGER DEFAULT( 3600*12 ) 76 ); 77 INSERT INTO t3 VALUES(null, 5, 'row1', '5.25', 'xyz', 321, '432'); 78 SELECT a, typeof(a), b, typeof(b), c, typeof(c), 79 d, typeof(d), e, typeof(e), f, typeof(f), 80 g, typeof(g) FROM t3; 81} {1 integer 5 integer row1 text 5.25 real xyz text 321 text 432 integer} 82do_execsql_test default-3.2 { 83 DELETE FROM t3; 84 INSERT INTO t3 DEFAULT VALUES; 85 SELECT * FROM t3; 86} {2 12345 hello {} 4.36 {} 43200} 87do_execsql_test default-3.3 { 88 CREATE TABLE t300( 89 a INT DEFAULT 2147483647, 90 b INT DEFAULT 2147483648, 91 c INT DEFAULT +9223372036854775807, 92 d INT DEFAULT -2147483647, 93 e INT DEFAULT -2147483648, 94 f INT DEFAULT -9223372036854775808, 95 g INT DEFAULT (-(-9223372036854775808)), 96 h INT DEFAULT (-(-9223372036854775807)) 97 ); 98 INSERT INTO t300 DEFAULT VALUES; 99 SELECT * FROM t300; 100} {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807} 101 102# Do now allow bound parameters in new DEFAULT values. 103# Silently convert bound parameters to NULL in DEFAULT causes 104# in the sqlite_master table, for backwards compatibility. 105# 106db close 107forcedelete test.db 108sqlite3 db test.db 109do_execsql_test default-4.0 { 110 CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99)); 111 PRAGMA writable_schema=ON; 112 UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))'; 113} {} 114db close 115sqlite3 db test.db 116do_execsql_test default-4.1 { 117 INSERT INTO t1(a) VALUES('xyzzy'); 118 SELECT a, quote(b) FROM t1; 119} {xyzzy NULL} 120do_catchsql_test default-4.2 { 121 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz)); 122} {1 {default value of column [b] is not constant}} 123do_catchsql_test default-4.3 { 124 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz))); 125} {1 {default value of column [b] is not constant}} 126do_catchsql_test default-4.4 { 127 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz))); 128} {1 {default value of column [b] is not constant}} 129 130finish_test 131