1# 2004 Jun 27 2# 3# The author disclaims copyright to this source code. In place of 4# 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# This file implements regression tests for SQLite library. 12# 13# This file implements tests for miscellanous features that were 14# left out of other test files. 15# 16# $Id: misc4.test,v 1.12 2005/01/20 02:17:02 danielk1977 Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Prepare a statement that will create a temporary table. Then do 22# a rollback. Then try to execute the prepared statement. 23# 24do_test misc4-1.1 { 25 db close 26 set DB [sqlite3 db test.db] 27 execsql { 28 CREATE TABLE t1(x); 29 INSERT INTO t1 VALUES(1); 30 } 31} {} 32do_test misc4-1.2 { 33 set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1} 34 set stmt [sqlite3_prepare $DB $sql -1 TAIL] 35 execsql { 36 BEGIN; 37 CREATE TABLE t3(a,b,c); 38 INSERT INTO t1 SELECT * FROM t1; 39 ROLLBACK; 40 } 41} {} 42do_test misc4-1.3 { 43 sqlite3_step $stmt 44} SQLITE_DONE 45do_test misc4-1.4 { 46 execsql { 47 SELECT * FROM temp.t2; 48 } 49} {1} 50 51# Drop the temporary table, then rerun the prepared statement to 52# recreate it again. This recreates ticket #807. 53# 54do_test misc4-1.5 { 55 execsql {DROP TABLE t2} 56 sqlite3_reset $stmt 57 sqlite3_step $stmt 58} {SQLITE_ERROR} 59do_test misc4-1.6 { 60 sqlite3_finalize $stmt 61} {SQLITE_SCHEMA} 62 63# Prepare but do not execute various CREATE statements. Then before 64# those statements are executed, try to use the tables, indices, views, 65# are triggers that were created. 66# 67do_test misc4-2.1 { 68 set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL] 69 catchsql { 70 INSERT INTO t3 VALUES(1); 71 } 72} {1 {no such table: t3}} 73do_test misc4-2.2 { 74 sqlite3_step $stmt 75} SQLITE_DONE 76do_test misc4-2.3 { 77 sqlite3_finalize $stmt 78} SQLITE_OK 79do_test misc4-2.4 { 80 catchsql { 81 INSERT INTO t3 VALUES(1); 82 } 83} {0 {}} 84 85# Ticket #966 86# 87ifcapable compound { 88do_test misc4-3.1 { 89 execsql { 90 CREATE TABLE Table1(ID integer primary key, Value TEXT); 91 INSERT INTO Table1 VALUES(1, 'x'); 92 CREATE TABLE Table2(ID integer NOT NULL, Value TEXT); 93 INSERT INTO Table2 VALUES(1, 'z'); 94 INSERT INTO Table2 VALUES (1, 'a'); 95 SELECT ID, Value FROM Table1 96 UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1,2 97 ORDER BY 1, 2; 98 } 99} {{} {} 1 x 1 z} 100} ;# ifcapable compound 101 102# Ticket #1047. Make sure column types are preserved in subqueries. 103# 104do_test misc4-4.1 { 105 execsql { 106 create table a(key varchar, data varchar); 107 create table b(key varchar, period integer); 108 insert into a values('01','data01'); 109 insert into a values('+1','data+1'); 110 111 insert into b values ('01',1); 112 insert into b values ('01',2); 113 insert into b values ('+1',3); 114 insert into b values ('+1',4); 115 116 select a.*, x.* 117 from a, (select key,sum(period) from b group by key) as x 118 where a.key=x.key; 119 } 120} {01 data01 01 3.0 +1 data+1 +1 7.0} 121 122# Ticket #1036. When creating tables from a SELECT on a view, use the 123# short names of columns. 124# 125ifcapable view { 126 do_test misc4-5.1 { 127 execsql { 128 create table t4(a,b); 129 create table t5(a,c); 130 insert into t4 values (1,2); 131 insert into t5 values (1,3); 132 create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a; 133 create table problem as select * from myview; 134 } 135 execsql2 { 136 select * FROM problem; 137 } 138 } {a 1} 139 do_test misc4-5.2 { 140 execsql2 { 141 create table t6 as select * from t4, t5; 142 select * from t6; 143 } 144 } {a 1 b 2 a:1 1 c 3} 145} 146 147finish_test 148