# 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