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.19 2007/10/09 08:29:33 danielk1977 Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 ifcapable !tempdb {

    21   finish_test

    22   return

    23 }

    24 

    25 # Create an alternative connection to the database

    26 #

    27 do_test temptable-1.0 {

    28   sqlite3 db2 ./test.db

    29   set dummy {}

    30 } {}

    31 

    32 # Create a permanent table.

    33 #

    34 do_test temptable-1.1 {

    35   execsql {CREATE TABLE t1(a,b,c);}

    36   execsql {INSERT INTO t1 VALUES(1,2,3);}

    37   execsql {SELECT * FROM t1}

    38 } {1 2 3}

    39 do_test temptable-1.2 {

    40   catch {db2 eval {SELECT * FROM sqlite_master}}

    41   db2 eval {SELECT * FROM t1}

    42 } {1 2 3}

    43 do_test temptable-1.3 {

    44   execsql {SELECT name FROM sqlite_master}

    45 } {t1}

    46 do_test temptable-1.4 {

    47   db2 eval {SELECT name FROM sqlite_master}

    48 } {t1}

    49 

    50 # Create a temporary table.  Verify that only one of the two

    51 # processes can see it.

    52 #

    53 do_test temptable-1.5 {

    54   db2 eval {

    55     CREATE TEMP TABLE t2(x,y,z);

    56     INSERT INTO t2 VALUES(4,5,6);

    57   }

    58   db2 eval {SELECT * FROM t2}

    59 } {4 5 6}

    60 do_test temptable-1.6 {

    61   catch {execsql {SELECT * FROM sqlite_master}}

    62   catchsql {SELECT * FROM t2}

    63 } {1 {no such table: t2}}

    64 do_test temptable-1.7 {

    65   catchsql {INSERT INTO t2 VALUES(8,9,0);}

    66 } {1 {no such table: t2}}

    67 do_test temptable-1.8 {

    68   db2 eval {INSERT INTO t2 VALUES(8,9,0);}

    69   db2 eval {SELECT * FROM t2 ORDER BY x}

    70 } {4 5 6 8 9 0}

    71 do_test temptable-1.9 {

    72   db2 eval {DELETE FROM t2 WHERE x==8}

    73   db2 eval {SELECT * FROM t2 ORDER BY x}

    74 } {4 5 6}

    75 do_test temptable-1.10 {

    76   db2 eval {DELETE FROM t2}

    77   db2 eval {SELECT * FROM t2}

    78 } {}

    79 do_test temptable-1.11 {

    80   db2 eval {

    81      INSERT INTO t2 VALUES(7,6,5);

    82      INSERT INTO t2 VALUES(4,3,2);

    83      SELECT * FROM t2 ORDER BY x;

    84   }

    85 } {4 3 2 7 6 5}

    86 do_test temptable-1.12 {

    87   db2 eval {DROP TABLE t2;}

    88   set r [catch {db2 eval {SELECT * FROM t2}} msg]

    89   lappend r $msg

    90 } {1 {no such table: t2}}

    91 

    92 # Make sure temporary tables work with transactions

    93 #

    94 do_test temptable-2.1 {

    95   execsql {

    96     BEGIN TRANSACTION;

    97     CREATE TEMPORARY TABLE t2(x,y);

    98     INSERT INTO t2 VALUES(1,2);

    99     SELECT * FROM t2;

   100   }

   101 } {1 2}

   102 do_test temptable-2.2 {

   103   execsql {ROLLBACK}

   104   catchsql {SELECT * FROM t2}

   105 } {1 {no such table: t2}}

   106 do_test temptable-2.3 {

   107   execsql {

   108     BEGIN TRANSACTION;

   109     CREATE TEMPORARY TABLE t2(x,y);

   110     INSERT INTO t2 VALUES(1,2);

   111     SELECT * FROM t2;

   112   }

   113 } {1 2}

   114 do_test temptable-2.4 {

   115   execsql {COMMIT}

   116   catchsql {SELECT * FROM t2}

   117 } {0 {1 2}}

   118 do_test temptable-2.5 {

   119   set r [catch {db2 eval {SELECT * FROM t2}} msg]

   120   lappend r $msg

   121 } {1 {no such table: t2}}

   122 

   123 # Make sure indices on temporary tables are also temporary.

   124 #

   125 do_test temptable-3.1 {

   126   execsql {

   127     CREATE INDEX i2 ON t2(x);

   128     SELECT name FROM sqlite_master WHERE type='index';

   129   }

   130 } {}

   131 do_test temptable-3.2 {

   132   execsql {

   133     SELECT y FROM t2 WHERE x=1;

   134   }

   135 } {2}

   136 do_test temptable-3.3 {

   137   execsql {

   138     DROP INDEX i2;

   139     SELECT y FROM t2 WHERE x=1;

   140   }

   141 } {2}

   142 do_test temptable-3.4 {

   143   execsql {

   144     CREATE INDEX i2 ON t2(x);

   145     DROP TABLE t2;

   146   }

   147   catchsql {DROP INDEX i2}

   148 } {1 {no such index: i2}}

   149 

   150 # Check for correct name collision processing. A name collision can

   151 # occur when process A creates a temporary table T then process B

   152 # creates a permanent table also named T.  The temp table in process A

   153 # hides the existance of the permanent table.

   154 #

   155 do_test temptable-4.1 {

   156   execsql {

   157     CREATE TEMP TABLE t2(x,y);

   158     INSERT INTO t2 VALUES(10,20);

   159     SELECT * FROM t2;

   160   } db2

   161 } {10 20}

   162 do_test temptable-4.2 {

   163   execsql {

   164     CREATE TABLE t2(x,y,z);

   165     INSERT INTO t2 VALUES(9,8,7);

   166     SELECT * FROM t2;

   167   }

   168 } {9 8 7}

   169 do_test temptable-4.3 {

   170   catchsql {

   171     SELECT * FROM t2;

   172   } db2

   173 } {0 {10 20}}

   174 do_test temptable-4.4.1 {

   175   catchsql {

   176     SELECT * FROM temp.t2;

   177   } db2

   178 } {0 {10 20}}

   179 do_test temptable-4.4.2 {

   180   catchsql {

   181     SELECT * FROM main.t2;

   182   } db2

   183 } {1 {no such table: main.t2}}

   184 #do_test temptable-4.4.3 {

   185 #  catchsql {

   186 #    SELECT name FROM main.sqlite_master WHERE type='table';

   187 #  } db2

   188 #} {1 {database schema has changed}}

   189 do_test temptable-4.4.4 {

   190   catchsql {

   191     SELECT name FROM main.sqlite_master WHERE type='table';

   192   } db2

   193 } {0 {t1 t2}}

   194 do_test temptable-4.4.5 {

   195   catchsql {

   196     SELECT * FROM main.t2;

   197   } db2

   198 } {0 {9 8 7}}

   199 do_test temptable-4.4.6 {

   200   # TEMP takes precedence over MAIN

   201   catchsql {

   202     SELECT * FROM t2;

   203   } db2

   204 } {0 {10 20}}

   205 do_test temptable-4.5 {

   206   catchsql {

   207     DROP TABLE t2;     -- should drop TEMP

   208     SELECT * FROM t2;  -- data should be from MAIN

   209   } db2

   210 } {0 {9 8 7}}

   211 do_test temptable-4.6 {

   212   db2 close

   213   sqlite3 db2 ./test.db

   214   catchsql {

   215     SELECT * FROM t2;

   216   } db2

   217 } {0 {9 8 7}}

   218 do_test temptable-4.7 {

   219   catchsql {

   220     DROP TABLE t2;

   221     SELECT * FROM t2;

   222   }

   223 } {1 {no such table: t2}}

   224 do_test temptable-4.8 {

   225   db2 close

   226   sqlite3 db2 ./test.db

   227   execsql {

   228     CREATE TEMP TABLE t2(x unique,y);

   229     INSERT INTO t2 VALUES(1,2);

   230     SELECT * FROM t2;

   231   } db2

   232 } {1 2}

   233 do_test temptable-4.9 {

   234   execsql {

   235     CREATE TABLE t2(x unique, y);

   236     INSERT INTO t2 VALUES(3,4);

   237     SELECT * FROM t2;

   238   }

   239 } {3 4}

   240 do_test temptable-4.10.1 {

   241   catchsql {

   242     SELECT * FROM t2;

   243   } db2

   244 } {0 {1 2}}

   245 # Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c

   246 #         handles it and retries the query anyway.

   247 # do_test temptable-4.10.2 {

   248 #   catchsql {

   249 #     SELECT name FROM sqlite_master WHERE type='table'

   250 #   } db2

   251 # } {1 {database schema has changed}}

   252 do_test temptable-4.10.3 {

   253   catchsql {

   254     SELECT name FROM sqlite_master WHERE type='table'

   255   } db2

   256 } {0 {t1 t2}}

   257 do_test temptable-4.11 {

   258   execsql {

   259     SELECT * FROM t2;

   260   } db2

   261 } {1 2}

   262 do_test temptable-4.12 {

   263   execsql {

   264     SELECT * FROM t2;

   265   }

   266 } {3 4}

   267 do_test temptable-4.13 {

   268   catchsql {

   269     DROP TABLE t2;     -- drops TEMP.T2

   270     SELECT * FROM t2;  -- uses MAIN.T2

   271   } db2

   272 } {0 {3 4}}

   273 do_test temptable-4.14 {

   274   execsql {

   275     SELECT * FROM t2;

   276   }

   277 } {3 4}

   278 do_test temptable-4.15 {

   279   db2 close

   280   sqlite3 db2 ./test.db

   281   execsql {

   282     SELECT * FROM t2;

   283   } db2

   284 } {3 4}

   285 

   286 # Now create a temporary table in db2 and a permanent index in db.  The

   287 # temporary table in db2 should mask the name of the permanent index,

   288 # but the permanent index should still be accessible and should still

   289 # be updated when its corresponding table changes.

   290 #

   291 do_test temptable-5.1 {

   292   execsql {

   293     CREATE TEMP TABLE mask(a,b,c)

   294   } db2

   295   execsql {

   296     CREATE INDEX mask ON t2(x);

   297     SELECT * FROM t2;

   298   }

   299 } {3 4}

   300 #do_test temptable-5.2 {

   301 #  catchsql {

   302 #    SELECT * FROM t2;

   303 #  } db2

   304 #} {1 {database schema has changed}}

   305 do_test temptable-5.3 {

   306   catchsql {

   307     SELECT * FROM t2;

   308   } db2

   309 } {0 {3 4}}

   310 do_test temptable-5.4 {

   311   execsql {

   312     SELECT y FROM t2 WHERE x=3

   313   }

   314 } {4}

   315 do_test temptable-5.5 {

   316   execsql {

   317     SELECT y FROM t2 WHERE x=3

   318   } db2

   319 } {4}

   320 do_test temptable-5.6 {

   321   execsql {

   322     INSERT INTO t2 VALUES(1,2);

   323     SELECT y FROM t2 WHERE x=1;

   324   } db2

   325 } {2}

   326 do_test temptable-5.7 {

   327   execsql {

   328     SELECT y FROM t2 WHERE x=3

   329   } db2

   330 } {4}

   331 do_test temptable-5.8 {

   332   execsql {

   333     SELECT y FROM t2 WHERE x=1;

   334   }

   335 } {2}

   336 do_test temptable-5.9 {

   337   execsql {

   338     SELECT y FROM t2 WHERE x=3

   339   }

   340 } {4}

   341 

   342 db2 close

   343 

   344 # Test for correct operation of read-only databases

   345 #

   346 do_test temptable-6.1 {

   347   execsql {

   348     CREATE TABLE t8(x);

   349     INSERT INTO t8 VALUES('xyzzy');

   350     SELECT * FROM t8;

   351   }

   352 } {xyzzy}

   353 do_test temptable-6.2 {

   354   db close

   355   catch {file attributes test.db -permissions 0444}

   356   catch {file attributes test.db -readonly 1}

   357   sqlite3 db test.db

   358   if {[file writable test.db]} {

   359     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"

   360   }

   361   execsql {

   362     SELECT * FROM t8;

   363   }

   364 } {xyzzy}

   365 do_test temptable-6.3 {

   366   if {[file writable test.db]} {

   367     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"

   368   }

   369   catchsql {

   370     CREATE TABLE t9(x,y);

   371   }

   372 } {1 {attempt to write a readonly database}}

   373 do_test temptable-6.4 {

   374   catchsql {

   375     CREATE TEMP TABLE t9(x,y);

   376   }

   377 } {0 {}}

   378 do_test temptable-6.5 {

   379   catchsql {

   380     INSERT INTO t9 VALUES(1,2);

   381     SELECT * FROM t9;

   382   }

   383 } {0 {1 2}}

   384 do_test temptable-6.6 {

   385   if {[file writable test.db]} {

   386     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"

   387   }

   388   catchsql {

   389     INSERT INTO t8 VALUES('hello');

   390     SELECT * FROM t8;

   391   }

   392 } {1 {attempt to write a readonly database}}

   393 do_test temptable-6.7 {

   394   catchsql {

   395     SELECT * FROM t8,t9;

   396   }

   397 } {0 {xyzzy 1 2}}

   398 do_test temptable-6.8 {

   399   db close

   400   sqlite3 db test.db

   401   catchsql {

   402     SELECT * FROM t8,t9;

   403   }

   404 } {1 {no such table: t9}}

   405 

   406 file delete -force test2.db test2.db-journal

   407 ifcapable attach {

   408   do_test temptable-7.1 {

   409     catchsql {

   410       ATTACH 'test2.db' AS two;

   411       CREATE TEMP TABLE two.abc(x,y);

   412     }

   413   } {1 {temporary table name must be unqualified}}

   414 }

   415 

   416 # Need to do the following for tcl 8.5 on mac. On that configuration, the

   417 # -readonly flag is taken so seriously that a subsequent [file delete -force]

   418 # (required before the next test file can be executed) will fail.

   419 #

   420 catch {file attributes test.db -readonly 0}

   421 

   422 finish_test