# 2013-10-30 # # 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 WITHOUT ROWID tables. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix without_rowid1 # Create and query a WITHOUT ROWID table. # do_execsql_test without_rowid1-1.0 { CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID; CREATE INDEX t1bd ON t1(b, d); INSERT INTO t1 VALUES('journal','sherman','ammonia','helena'); INSERT INTO t1 VALUES('dynamic','juliet','flipper','command'); INSERT INTO t1 VALUES('journal','sherman','gamma','patriot'); INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena'); SELECT *, '|' FROM t1 ORDER BY c, a; } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} integrity_check without_rowid1-1.0ic do_execsql_test without_rowid1-1.1 { SELECT *, '|' FROM t1 ORDER BY +c, a; } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} do_execsql_test without_rowid1-1.2 { SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC; } {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |} do_execsql_test without_rowid1-1.11 { SELECT *, '|' FROM t1 ORDER BY b, d; } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} do_execsql_test without_rowid1-1.12 { SELECT *, '|' FROM t1 ORDER BY +b, d; } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} # Trying to insert a duplicate PRIMARY KEY fails. # do_test without_rowid1-1.21 { catchsql { INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard'); } } {1 {UNIQUE constraint failed: t1.c, t1.a}} # REPLACE INTO works, however. # do_execsql_test without_rowid1-1.22 { REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard'); SELECT *, '|' FROM t1 ORDER BY c, a; } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |} do_execsql_test without_rowid1-1.23 { SELECT *, '|' FROM t1 ORDER BY b, d; } {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} # UPDATE statements. # do_execsql_test without_rowid1-1.31 { UPDATE t1 SET d=3.1415926 WHERE a='journal'; SELECT *, '|' FROM t1 ORDER BY c, a; } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |} do_execsql_test without_rowid1-1.32 { SELECT *, '|' FROM t1 ORDER BY b, d; } {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} do_execsql_test without_rowid1-1.35 { UPDATE t1 SET a=1250 WHERE b='phone'; SELECT *, '|' FROM t1 ORDER BY c, a; } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |} integrity_check without_rowid1-1.36 do_execsql_test without_rowid1-1.37 { SELECT *, '|' FROM t1 ORDER BY b, d; } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} do_execsql_test without_rowid1-1.40 { VACUUM; SELECT *, '|' FROM t1 ORDER BY b, d; } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} integrity_check without_rowid1-1.41 # Verify that ANALYZE works # do_execsql_test without_rowid1-1.50 { ANALYZE; SELECT * FROM sqlite_stat1 ORDER BY idx; } {t1 t1 {4 2 1} t1 t1bd {4 2 2}} ifcapable stat3 { do_execsql_test without_rowid1-1.51 { SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx; } {t1 t1 t1 t1bd} } ifcapable stat4 { do_execsql_test without_rowid1-1.52 { SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx; } {t1 t1 t1 t1bd} } #---------- do_execsql_test 2.1.1 { CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID; INSERT INTO t4 VALUES('abc', 'def'); SELECT * FROM t4; } {abc def} do_execsql_test 2.1.2 { UPDATE t4 SET a = 'ABC'; SELECT * FROM t4; } {ABC def} do_execsql_test 2.2.1 { DROP TABLE t4; CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID; INSERT INTO t4(a, b) VALUES('abc', 'def'); SELECT * FROM t4; } {def abc} do_execsql_test 2.2.2 { UPDATE t4 SET a = 'ABC', b = 'xyz'; SELECT * FROM t4; } {xyz ABC} do_execsql_test 2.3.1 { CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID; INSERT INTO t5(a, b) VALUES('abc', 'def'); UPDATE t5 SET a='abc', b='def'; } {} do_execsql_test 2.4.1 { CREATE TABLE t6 ( a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a) ) WITHOUT ROWID; INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi'); UPDATE t6 SET a='ABC', c='ghi'; } {} do_execsql_test 2.4.2 { SELECT * FROM t6 ORDER BY b, a; SELECT * FROM t6 ORDER BY c; } {ABC def ghi ABC def ghi} #------------------------------------------------------------------------- # Unless the destination table is completely empty, the xfer optimization # is disabled for WITHOUT ROWID tables. The following tests check for # some problems that might occur if this were not the case. # reset_db do_execsql_test 3.1.1 { CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; CREATE UNIQUE INDEX i1 ON t1(b); CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID; CREATE UNIQUE INDEX i2 ON t2(b); INSERT INTO t1 VALUES('one', 'two'); INSERT INTO t2 VALUES('three', 'two'); } do_execsql_test 3.1.2 { INSERT OR REPLACE INTO t1 SELECT * FROM t2; SELECT * FROM t1; } {three two} do_execsql_test 3.1.3 { DELETE FROM t1; INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } {three two} do_catchsql_test 3.1.4 { INSERT INTO t2 VALUES('four', 'four'); INSERT INTO t2 VALUES('six', 'two'); INSERT INTO t1 SELECT * FROM t2; } {1 {UNIQUE constraint failed: t2.b}} do_execsql_test 3.1.5 { CREATE TABLE t3(a PRIMARY KEY); CREATE TABLE t4(a PRIMARY KEY); INSERT INTO t4 VALUES('i'); INSERT INTO t4 VALUES('ii'); INSERT INTO t4 VALUES('iii'); INSERT INTO t3 SELECT * FROM t4; SELECT * FROM t3; } {i ii iii} ############################################################################ # Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc] # Name resolution issue with WITHOUT ROWID # do_execsql_test 4.1 { CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID; INSERT INTO t41 VALUES('abc'); CREATE TABLE t42(x); INSERT INTO t42 VALUES('xyz'); SELECT t42.rowid FROM t41, t42; } {1} do_execsql_test 4.2 { SELECT t42.rowid FROM t42, t41; } {1} finish_test