sl@0: # 2001 October 7
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.
sl@0: #
sl@0: # This file implements tests for temporary tables and indices.
sl@0: #
sl@0: # $Id: temptable.test,v 1.19 2007/10/09 08:29:33 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !tempdb {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: # Create an alternative connection to the database
sl@0: #
sl@0: do_test temptable-1.0 {
sl@0:   sqlite3 db2 ./test.db
sl@0:   set dummy {}
sl@0: } {}
sl@0: 
sl@0: # Create a permanent table.
sl@0: #
sl@0: do_test temptable-1.1 {
sl@0:   execsql {CREATE TABLE t1(a,b,c);}
sl@0:   execsql {INSERT INTO t1 VALUES(1,2,3);}
sl@0:   execsql {SELECT * FROM t1}
sl@0: } {1 2 3}
sl@0: do_test temptable-1.2 {
sl@0:   catch {db2 eval {SELECT * FROM sqlite_master}}
sl@0:   db2 eval {SELECT * FROM t1}
sl@0: } {1 2 3}
sl@0: do_test temptable-1.3 {
sl@0:   execsql {SELECT name FROM sqlite_master}
sl@0: } {t1}
sl@0: do_test temptable-1.4 {
sl@0:   db2 eval {SELECT name FROM sqlite_master}
sl@0: } {t1}
sl@0: 
sl@0: # Create a temporary table.  Verify that only one of the two
sl@0: # processes can see it.
sl@0: #
sl@0: do_test temptable-1.5 {
sl@0:   db2 eval {
sl@0:     CREATE TEMP TABLE t2(x,y,z);
sl@0:     INSERT INTO t2 VALUES(4,5,6);
sl@0:   }
sl@0:   db2 eval {SELECT * FROM t2}
sl@0: } {4 5 6}
sl@0: do_test temptable-1.6 {
sl@0:   catch {execsql {SELECT * FROM sqlite_master}}
sl@0:   catchsql {SELECT * FROM t2}
sl@0: } {1 {no such table: t2}}
sl@0: do_test temptable-1.7 {
sl@0:   catchsql {INSERT INTO t2 VALUES(8,9,0);}
sl@0: } {1 {no such table: t2}}
sl@0: do_test temptable-1.8 {
sl@0:   db2 eval {INSERT INTO t2 VALUES(8,9,0);}
sl@0:   db2 eval {SELECT * FROM t2 ORDER BY x}
sl@0: } {4 5 6 8 9 0}
sl@0: do_test temptable-1.9 {
sl@0:   db2 eval {DELETE FROM t2 WHERE x==8}
sl@0:   db2 eval {SELECT * FROM t2 ORDER BY x}
sl@0: } {4 5 6}
sl@0: do_test temptable-1.10 {
sl@0:   db2 eval {DELETE FROM t2}
sl@0:   db2 eval {SELECT * FROM t2}
sl@0: } {}
sl@0: do_test temptable-1.11 {
sl@0:   db2 eval {
sl@0:      INSERT INTO t2 VALUES(7,6,5);
sl@0:      INSERT INTO t2 VALUES(4,3,2);
sl@0:      SELECT * FROM t2 ORDER BY x;
sl@0:   }
sl@0: } {4 3 2 7 6 5}
sl@0: do_test temptable-1.12 {
sl@0:   db2 eval {DROP TABLE t2;}
sl@0:   set r [catch {db2 eval {SELECT * FROM t2}} msg]
sl@0:   lappend r $msg
sl@0: } {1 {no such table: t2}}
sl@0: 
sl@0: # Make sure temporary tables work with transactions
sl@0: #
sl@0: do_test temptable-2.1 {
sl@0:   execsql {
sl@0:     BEGIN TRANSACTION;
sl@0:     CREATE TEMPORARY TABLE t2(x,y);
sl@0:     INSERT INTO t2 VALUES(1,2);
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {1 2}
sl@0: do_test temptable-2.2 {
sl@0:   execsql {ROLLBACK}
sl@0:   catchsql {SELECT * FROM t2}
sl@0: } {1 {no such table: t2}}
sl@0: do_test temptable-2.3 {
sl@0:   execsql {
sl@0:     BEGIN TRANSACTION;
sl@0:     CREATE TEMPORARY TABLE t2(x,y);
sl@0:     INSERT INTO t2 VALUES(1,2);
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {1 2}
sl@0: do_test temptable-2.4 {
sl@0:   execsql {COMMIT}
sl@0:   catchsql {SELECT * FROM t2}
sl@0: } {0 {1 2}}
sl@0: do_test temptable-2.5 {
sl@0:   set r [catch {db2 eval {SELECT * FROM t2}} msg]
sl@0:   lappend r $msg
sl@0: } {1 {no such table: t2}}
sl@0: 
sl@0: # Make sure indices on temporary tables are also temporary.
sl@0: #
sl@0: do_test temptable-3.1 {
sl@0:   execsql {
sl@0:     CREATE INDEX i2 ON t2(x);
sl@0:     SELECT name FROM sqlite_master WHERE type='index';
sl@0:   }
sl@0: } {}
sl@0: do_test temptable-3.2 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=1;
sl@0:   }
sl@0: } {2}
sl@0: do_test temptable-3.3 {
sl@0:   execsql {
sl@0:     DROP INDEX i2;
sl@0:     SELECT y FROM t2 WHERE x=1;
sl@0:   }
sl@0: } {2}
sl@0: do_test temptable-3.4 {
sl@0:   execsql {
sl@0:     CREATE INDEX i2 ON t2(x);
sl@0:     DROP TABLE t2;
sl@0:   }
sl@0:   catchsql {DROP INDEX i2}
sl@0: } {1 {no such index: i2}}
sl@0: 
sl@0: # Check for correct name collision processing. A name collision can
sl@0: # occur when process A creates a temporary table T then process B
sl@0: # creates a permanent table also named T.  The temp table in process A
sl@0: # hides the existance of the permanent table.
sl@0: #
sl@0: do_test temptable-4.1 {
sl@0:   execsql {
sl@0:     CREATE TEMP TABLE t2(x,y);
sl@0:     INSERT INTO t2 VALUES(10,20);
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {10 20}
sl@0: do_test temptable-4.2 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2(x,y,z);
sl@0:     INSERT INTO t2 VALUES(9,8,7);
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {9 8 7}
sl@0: do_test temptable-4.3 {
sl@0:   catchsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {0 {10 20}}
sl@0: do_test temptable-4.4.1 {
sl@0:   catchsql {
sl@0:     SELECT * FROM temp.t2;
sl@0:   } db2
sl@0: } {0 {10 20}}
sl@0: do_test temptable-4.4.2 {
sl@0:   catchsql {
sl@0:     SELECT * FROM main.t2;
sl@0:   } db2
sl@0: } {1 {no such table: main.t2}}
sl@0: #do_test temptable-4.4.3 {
sl@0: #  catchsql {
sl@0: #    SELECT name FROM main.sqlite_master WHERE type='table';
sl@0: #  } db2
sl@0: #} {1 {database schema has changed}}
sl@0: do_test temptable-4.4.4 {
sl@0:   catchsql {
sl@0:     SELECT name FROM main.sqlite_master WHERE type='table';
sl@0:   } db2
sl@0: } {0 {t1 t2}}
sl@0: do_test temptable-4.4.5 {
sl@0:   catchsql {
sl@0:     SELECT * FROM main.t2;
sl@0:   } db2
sl@0: } {0 {9 8 7}}
sl@0: do_test temptable-4.4.6 {
sl@0:   # TEMP takes precedence over MAIN
sl@0:   catchsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {0 {10 20}}
sl@0: do_test temptable-4.5 {
sl@0:   catchsql {
sl@0:     DROP TABLE t2;     -- should drop TEMP
sl@0:     SELECT * FROM t2;  -- data should be from MAIN
sl@0:   } db2
sl@0: } {0 {9 8 7}}
sl@0: do_test temptable-4.6 {
sl@0:   db2 close
sl@0:   sqlite3 db2 ./test.db
sl@0:   catchsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {0 {9 8 7}}
sl@0: do_test temptable-4.7 {
sl@0:   catchsql {
sl@0:     DROP TABLE t2;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {1 {no such table: t2}}
sl@0: do_test temptable-4.8 {
sl@0:   db2 close
sl@0:   sqlite3 db2 ./test.db
sl@0:   execsql {
sl@0:     CREATE TEMP TABLE t2(x unique,y);
sl@0:     INSERT INTO t2 VALUES(1,2);
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {1 2}
sl@0: do_test temptable-4.9 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2(x unique, y);
sl@0:     INSERT INTO t2 VALUES(3,4);
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {3 4}
sl@0: do_test temptable-4.10.1 {
sl@0:   catchsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {0 {1 2}}
sl@0: # Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c
sl@0: #         handles it and retries the query anyway.
sl@0: # do_test temptable-4.10.2 {
sl@0: #   catchsql {
sl@0: #     SELECT name FROM sqlite_master WHERE type='table'
sl@0: #   } db2
sl@0: # } {1 {database schema has changed}}
sl@0: do_test temptable-4.10.3 {
sl@0:   catchsql {
sl@0:     SELECT name FROM sqlite_master WHERE type='table'
sl@0:   } db2
sl@0: } {0 {t1 t2}}
sl@0: do_test temptable-4.11 {
sl@0:   execsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {1 2}
sl@0: do_test temptable-4.12 {
sl@0:   execsql {
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {3 4}
sl@0: do_test temptable-4.13 {
sl@0:   catchsql {
sl@0:     DROP TABLE t2;     -- drops TEMP.T2
sl@0:     SELECT * FROM t2;  -- uses MAIN.T2
sl@0:   } db2
sl@0: } {0 {3 4}}
sl@0: do_test temptable-4.14 {
sl@0:   execsql {
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {3 4}
sl@0: do_test temptable-4.15 {
sl@0:   db2 close
sl@0:   sqlite3 db2 ./test.db
sl@0:   execsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {3 4}
sl@0: 
sl@0: # Now create a temporary table in db2 and a permanent index in db.  The
sl@0: # temporary table in db2 should mask the name of the permanent index,
sl@0: # but the permanent index should still be accessible and should still
sl@0: # be updated when its corresponding table changes.
sl@0: #
sl@0: do_test temptable-5.1 {
sl@0:   execsql {
sl@0:     CREATE TEMP TABLE mask(a,b,c)
sl@0:   } db2
sl@0:   execsql {
sl@0:     CREATE INDEX mask ON t2(x);
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {3 4}
sl@0: #do_test temptable-5.2 {
sl@0: #  catchsql {
sl@0: #    SELECT * FROM t2;
sl@0: #  } db2
sl@0: #} {1 {database schema has changed}}
sl@0: do_test temptable-5.3 {
sl@0:   catchsql {
sl@0:     SELECT * FROM t2;
sl@0:   } db2
sl@0: } {0 {3 4}}
sl@0: do_test temptable-5.4 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=3
sl@0:   }
sl@0: } {4}
sl@0: do_test temptable-5.5 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=3
sl@0:   } db2
sl@0: } {4}
sl@0: do_test temptable-5.6 {
sl@0:   execsql {
sl@0:     INSERT INTO t2 VALUES(1,2);
sl@0:     SELECT y FROM t2 WHERE x=1;
sl@0:   } db2
sl@0: } {2}
sl@0: do_test temptable-5.7 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=3
sl@0:   } db2
sl@0: } {4}
sl@0: do_test temptable-5.8 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=1;
sl@0:   }
sl@0: } {2}
sl@0: do_test temptable-5.9 {
sl@0:   execsql {
sl@0:     SELECT y FROM t2 WHERE x=3
sl@0:   }
sl@0: } {4}
sl@0: 
sl@0: db2 close
sl@0: 
sl@0: # Test for correct operation of read-only databases
sl@0: #
sl@0: do_test temptable-6.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t8(x);
sl@0:     INSERT INTO t8 VALUES('xyzzy');
sl@0:     SELECT * FROM t8;
sl@0:   }
sl@0: } {xyzzy}
sl@0: do_test temptable-6.2 {
sl@0:   db close
sl@0:   catch {file attributes test.db -permissions 0444}
sl@0:   catch {file attributes test.db -readonly 1}
sl@0:   sqlite3 db test.db
sl@0:   if {[file writable test.db]} {
sl@0:     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT * FROM t8;
sl@0:   }
sl@0: } {xyzzy}
sl@0: do_test temptable-6.3 {
sl@0:   if {[file writable test.db]} {
sl@0:     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
sl@0:   }
sl@0:   catchsql {
sl@0:     CREATE TABLE t9(x,y);
sl@0:   }
sl@0: } {1 {attempt to write a readonly database}}
sl@0: do_test temptable-6.4 {
sl@0:   catchsql {
sl@0:     CREATE TEMP TABLE t9(x,y);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test temptable-6.5 {
sl@0:   catchsql {
sl@0:     INSERT INTO t9 VALUES(1,2);
sl@0:     SELECT * FROM t9;
sl@0:   }
sl@0: } {0 {1 2}}
sl@0: do_test temptable-6.6 {
sl@0:   if {[file writable test.db]} {
sl@0:     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
sl@0:   }
sl@0:   catchsql {
sl@0:     INSERT INTO t8 VALUES('hello');
sl@0:     SELECT * FROM t8;
sl@0:   }
sl@0: } {1 {attempt to write a readonly database}}
sl@0: do_test temptable-6.7 {
sl@0:   catchsql {
sl@0:     SELECT * FROM t8,t9;
sl@0:   }
sl@0: } {0 {xyzzy 1 2}}
sl@0: do_test temptable-6.8 {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   catchsql {
sl@0:     SELECT * FROM t8,t9;
sl@0:   }
sl@0: } {1 {no such table: t9}}
sl@0: 
sl@0: file delete -force test2.db test2.db-journal
sl@0: ifcapable attach {
sl@0:   do_test temptable-7.1 {
sl@0:     catchsql {
sl@0:       ATTACH 'test2.db' AS two;
sl@0:       CREATE TEMP TABLE two.abc(x,y);
sl@0:     }
sl@0:   } {1 {temporary table name must be unqualified}}
sl@0: }
sl@0: 
sl@0: # Need to do the following for tcl 8.5 on mac. On that configuration, the
sl@0: # -readonly flag is taken so seriously that a subsequent [file delete -force]
sl@0: # (required before the next test file can be executed) will fail.
sl@0: #
sl@0: catch {file attributes test.db -readonly 0}
sl@0: 
sl@0: finish_test