xref: /sqlite-3.40.0/test/temptable.test (revision f57b3399) 
1# 2001 October 7
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 temporary tables and indices.
14#
15# $Id: temptable.test,v 1.1 2001/10/08 13:22:33 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Create an alternative connection to the database
21#
22do_test temptable-1.0 {
23  sqlite db2 ./test.db
24} {}
25
26# Create a permanent table.
27#
28do_test temptable-1.1 {
29  execsql {CREATE TABLE t1(a,b,c);}
30  execsql {INSERT INTO t1 VALUES(1,2,3);}
31  execsql {SELECT * FROM t1}
32} {1 2 3}
33do_test temptable-1.2 {
34  catch {db2 eval {SELECT * FROM sqlite_master}}
35  db2 eval {SELECT * FROM t1}
36} {1 2 3}
37do_test testtable-1.3 {
38  execsql {SELECT name FROM sqlite_master}
39} {t1}
40do_test testtable-1.4 {
41  db2 eval {SELECT name FROM sqlite_master}
42} {t1}
43
44# Create a temporary table.  Verify that only one of the two
45# processes can see it.
46#
47do_test testtable-1.5 {
48  db2 eval {
49    CREATE TEMP TABLE t2(x,y,z);
50    INSERT INTO t2 VALUES(4,5,6);
51  }
52  db2 eval {SELECT * FROM t2}
53} {4 5 6}
54do_test testtable-1.6 {
55  catch {execsql {SELECT * FROM sqlite_master}}
56  catchsql {SELECT * FROM t2}
57} {1 {no such table: t2}}
58do_test testtable-1.7 {
59  catchsql {INSERT INTO t2 VALUES(8,9,0);}
60} {1 {no such table: t2}}
61do_test testtable-1.8 {
62  db2 eval {INSERT INTO t2 VALUES(8,9,0);}
63  db2 eval {SELECT * FROM t2 ORDER BY x}
64} {4 5 6 8 9 0}
65do_test testtable-1.9 {
66  db2 eval {DELETE FROM t2 WHERE x==8}
67  db2 eval {SELECT * FROM t2 ORDER BY x}
68} {4 5 6}
69do_test testtable-1.10 {
70  db2 eval {DELETE FROM t2}
71  db2 eval {SELECT * FROM t2}
72} {}
73do_test testtable-1.11 {
74  db2 eval {
75     INSERT INTO t2 VALUES(7,6,5);
76     INSERT INTO t2 VALUES(4,3,2);
77     SELECT * FROM t2 ORDER BY x;
78  }
79} {4 3 2 7 6 5}
80do_test testtable-1.12 {
81  db2 eval {DROP TABLE t2;}
82  set r [catch {db2 eval {SELECT * FROM t2}} msg]
83  lappend r $msg
84} {1 {no such table: t2}}
85
86# Make sure temporary tables work with transactions
87#
88do_test testtable-2.1 {
89  execsql {
90    BEGIN TRANSACTION;
91    CREATE TEMPORARY TABLE t2(x,y);
92    INSERT INTO t2 VALUES(1,2);
93    SELECT * FROM t2;
94  }
95} {1 2}
96do_test testtable-2.2 {
97  execsql {ROLLBACK}
98  catchsql {SELECT * FROM t2}
99} {1 {no such table: t2}}
100do_test testtable-2.3 {
101  execsql {
102    BEGIN TRANSACTION;
103    CREATE TEMPORARY TABLE t2(x,y);
104    INSERT INTO t2 VALUES(1,2);
105    SELECT * FROM t2;
106  }
107} {1 2}
108do_test testtable-2.4 {
109  execsql {COMMIT}
110  catchsql {SELECT * FROM t2}
111} {0 {1 2}}
112do_test testtable-2.5 {
113  set r [catch {db2 eval {SELECT * FROM t2}} msg]
114  lappend r $msg
115} {1 {no such table: t2}}
116
117
118# Check for correct name collision processing. A name collision can
119# occur when process A creates a temporary table T then process B
120# creates a permanent table also named T.  The temp table in process A
121# hides the existance of the permanent table.
122#
123
124finish_test
125