# 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 # 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 {PRIMARY KEY must be unique}} # 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} } finish_test