# 2002 February 26 # # 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 VIEW statements. # # $Id: view.test,v 1.3 2002/03/03 23:06:02 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test view-1.0 { execsql { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(4,5,6); INSERT INTO t1 VALUES(7,8,9); SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9} do_test view-1.1 { execsql { BEGIN; CREATE VIEW v1 AS SELECT a,b FROM t1; SELECT * FROM v1 ORDER BY a; } } {1 2 4 5 7 8} do_test view-1.2 { catchsql { ROLLBACK; SELECT * FROM v1 ORDER BY a; } } {1 {no such table: v1}} do_test view-1.3 { execsql { CREATE VIEW v1 AS SELECT a,b FROM t1; SELECT * FROM v1 ORDER BY a; } } {1 2 4 5 7 8} do_test view-1.3.1 { db close sqlite db test.db execsql { SELECT * FROM v1 ORDER BY a; } } {1 2 4 5 7 8} do_test view-1.4 { catchsql { DROP VIEW v1; SELECT * FROM v1 ORDER BY a; } } {1 {no such table: v1}} do_test view-1.5 { execsql { CREATE VIEW v1 AS SELECT a,b FROM t1; SELECT * FROM v1 ORDER BY a; } } {1 2 4 5 7 8} do_test view-1.6 { catchsql { DROP TABLE t1; SELECT * FROM v1 ORDER BY a; } } {1 {no such table: t1}} do_test view-1.7 { execsql { CREATE TABLE t1(x,a,b,c); INSERT INTO t1 VALUES(1,2,3,4); INSERT INTO t1 VALUES(4,5,6,7); INSERT INTO t1 VALUES(7,8,9,10); SELECT * FROM v1 ORDER BY a; } } {2 3 5 6 8 9} do_test view-1.8 { db close sqlite db test.db execsql { SELECT * FROM v1 ORDER BY a; } } {2 3 5 6 8 9} do_test view-2.1 { execsql { CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5 }; # No semicolon execsql2 { SELECT * FROM v2; } } {x 7 a 8 b 9 c 10} do_test view-2.2 { catchsql { INSERT INTO v2 VALUES(1,2,3,4); } } {1 {view v2 may not be modified}} do_test view-2.3 { catchsql { UPDATE v2 SET a=10 WHERE a=5; } } {1 {view v2 may not be modified}} do_test view-2.4 { catchsql { DELETE FROM v2; } } {1 {view v2 may not be modified}} do_test view-2.5 { execsql { INSERT INTO t1 VALUES(11,12,13,14); SELECT * FROM v2 ORDER BY x; } } {7 8 9 10 11 12 13 14} do_test view-2.6 { execsql { SELECT x FROM v2 WHERE a>10 } } {11} finish_test