AryaWu/sqlite

1# 2021-08-192#3# The author disclaims copyright to this source code.  In place of4# 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#12# This file implements regression tests for SQLite library.  The13# focus of this file is testing STRICT tables.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix strict219 20# PRAGMA integrity_check on a STRICT table should verify that21# all of the values are of the correct type.22#23do_execsql_test strict2-1.1 {24  CREATE TABLE t1(25    a INT,26    b INTEGER,27    c TEXT,28    d REAL,29    e BLOB30  ) STRICT;31  CREATE TABLE t1nn(32    a INT NOT NULL,33    b INTEGER NOT NULL,34    c TEXT NOT NULL,35    d REAL NOT NULL,36    e BLOB NOT NULL37  ) STRICT;38  CREATE TABLE t2(a,b,c,d,e);39  INSERT INTO t1(a,b,c,d,e) VALUES(1,1,'one',1.0,x'b1'),(2,2,'two',2.25,x'b2b2b2');40  PRAGMA writable_schema=on;41  UPDATE sqlite_schema SET rootpage=(SELECT rootpage FROM sqlite_schema WHERE name='t1');42} {}43db close44sqlite3 db test.db45do_execsql_test strict2-1.2 {46  PRAGMA quick_check('t1');47} {ok}48do_execsql_test strict2-1.3 {49  UPDATE t2 SET a=2.5 WHERE b=2;50  PRAGMA quick_check('t1');51} {{non-INT value in t1.a}}52do_execsql_test strict2-1.4 {53  UPDATE t2 SET a='xyz' WHERE b=2;54  PRAGMA quick_check('t1');55} {{non-INT value in t1.a}}56do_execsql_test strict2-1.5 {57  UPDATE t2 SET a=x'445566' WHERE b=2;58  PRAGMA quick_check('t1');59} {{non-INT value in t1.a}}60do_execsql_test strict2-1.6 {61  UPDATE t2 SET a=2.5 WHERE b=2;62  PRAGMA quick_check('t1nn');63} {{non-INT value in t1nn.a}}64do_execsql_test strict2-1.7 {65  UPDATE t2 SET a='xyz' WHERE b=2;66  PRAGMA quick_check('t1nn');67} {{non-INT value in t1nn.a}}68do_execsql_test strict2-1.8 {69  UPDATE t2 SET a=x'445566' WHERE b=2;70  PRAGMA quick_check('t1nn');71} {{non-INT value in t1nn.a}}72 73do_execsql_test strict2-1.13 {74  UPDATE t2 SET a=2 WHERE b=2;75  UPDATE t2 SET b=2.5 WHERE a=2;76  PRAGMA quick_check('t1');77} {{non-INTEGER value in t1.b}}78do_execsql_test strict2-1.14 {79  UPDATE t2 SET b='two' WHERE a=2;80  PRAGMA quick_check('t1');81} {{non-INTEGER value in t1.b}}82do_execsql_test strict2-1.15 {83  UPDATE t2 SET b=x'b0b1b2b3b4' WHERE a=2;84  PRAGMA quick_check('t1');85} {{non-INTEGER value in t1.b}}86do_execsql_test strict2-1.16 {87  UPDATE t2 SET b=NULL WHERE a=2;88  PRAGMA quick_check('t1');89} {ok}90do_execsql_test strict2-1.17 {91  UPDATE t2 SET b=2.5 WHERE a=2;92  PRAGMA quick_check('t1nn');93} {{non-INTEGER value in t1nn.b}}94do_execsql_test strict2-1.18 {95  UPDATE t2 SET b=NULL WHERE a=2;96  PRAGMA quick_check('t1nn');97} {{NULL value in t1nn.b}}98 99do_execsql_test strict2-1.23 {100  UPDATE t2 SET b=2 WHERE a=2;101  UPDATE t2 SET c=9 WHERE a=2;102  PRAGMA quick_check('t1');103} {{non-TEXT value in t1.c}}104do_execsql_test strict2-1.24 {105  UPDATE t2 SET c=9.5 WHERE a=2;106  PRAGMA quick_check('t1');107} {{non-TEXT value in t1.c}}108do_execsql_test strict2-1.25 {109  UPDATE t2 SET c=x'b0b1b2b3b4' WHERE a=2;110  PRAGMA quick_check('t1');111} {{non-TEXT value in t1.c}}112 113do_execsql_test strict2-1.33 {114  UPDATE t2 SET c='two' WHERE a=2;115  UPDATE t2 SET d=9 WHERE a=2;116  PRAGMA quick_check('t1');117} {ok}118do_execsql_test strict2-1.34 {119  UPDATE t2 SET d='nine' WHERE a=2;120  PRAGMA quick_check('t1');121} {{non-REAL value in t1.d}}122do_execsql_test strict2-1.35 {123  UPDATE t2 SET d=x'b0b1b2b3b4' WHERE a=2;124  PRAGMA quick_check('t1');125} {{non-REAL value in t1.d}}126 127do_execsql_test strict2-1.43 {128  UPDATE t2 SET d=2.5 WHERE a=2;129  UPDATE t2 SET e=9 WHERE a=2;130  PRAGMA quick_check('t1');131} {{non-BLOB value in t1.e}}132do_execsql_test strict2-1.44 {133  UPDATE t2 SET e=9.5 WHERE a=2;134  PRAGMA quick_check('t1');135} {{non-BLOB value in t1.e}}136do_execsql_test strict2-1.45 {137  UPDATE t2 SET e='hello' WHERE a=2;138  PRAGMA quick_check('t1');139} {{non-BLOB value in t1.e}}140 141do_execsql_test strict2-2.0 {142  DROP TABLE IF EXISTS t2;143  CREATE TABLE t2(a INT, b ANY) STRICT;144  INSERT INTO t2(a,b) VALUES(1,2),(3,4.5),(5,'six'),(7,x'8888'),(9,NULL);145  PRAGMA integrity_check(t2);146} {ok}147 148do_execsql_test strict2-3.0 {149  DROP TABLE IF EXISTS t1;150  CREATE TABLE t1(id ANY PRIMARY KEY, x TEXT);151  INSERT INTO t1 VALUES(1,2),('three','four'),(x'5555','six'),(NULL,'eight');152  PRAGMA writable_schema=ON;153  UPDATE sqlite_schema SET sql=(sql||'STRICT') WHERE name='t1';154  PRAGMA writable_schema=RESET;155  PRAGMA integrity_check(t1);156} {{NULL value in t1.id}}157 158finish_test159