test/temptable.test

   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.21 2009/06/16 17:49:36 drh 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 existence 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 } {0 {9 8 7}}
 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   if {[permutation]=="prepare"} { db2 cache flush }
 296   execsql {
 297     CREATE INDEX mask ON t2(x);
 298     SELECT * FROM t2;
 299   }
 300 } {3 4}
 301 #do_test temptable-5.2 {
 302 #  catchsql {
 303 #    SELECT * FROM t2;
 304 #  } db2
 305 #} {1 {database schema has changed}}
 306 do_test temptable-5.3 {
 307   catchsql {
 308     SELECT * FROM t2;
 309   } db2
 310 } {0 {3 4}}
 311 do_test temptable-5.4 {
 312   execsql {
 313     SELECT y FROM t2 WHERE x=3
 314   }
 315 } {4}
 316 do_test temptable-5.5 {
 317   execsql {
 318     SELECT y FROM t2 WHERE x=3
 319   } db2
 320 } {4}
 321 do_test temptable-5.6 {
 322   execsql {
 323     INSERT INTO t2 VALUES(1,2);
 324     SELECT y FROM t2 WHERE x=1;
 325   } db2
 326 } {2}
 327 do_test temptable-5.7 {
 328   execsql {
 329     SELECT y FROM t2 WHERE x=3
 330   } db2
 331 } {4}
 332 do_test temptable-5.8 {
 333   execsql {
 334     SELECT y FROM t2 WHERE x=1;
 335   }
 336 } {2}
 337 do_test temptable-5.9 {
 338   execsql {
 339     SELECT y FROM t2 WHERE x=3
 340   }
 341 } {4}
 342
 343 db2 close
 344
 345 # Test for correct operation of read-only databases
 346 #
 347 do_test temptable-6.1 {
 348   execsql {
 349     CREATE TABLE t8(x);
 350     INSERT INTO t8 VALUES('xyzzy');
 351     SELECT * FROM t8;
 352   }
 353 } {xyzzy}
 354 do_test temptable-6.2 {
 355   db close
 356   catch {file attributes test.db -permissions 0444}
 357   catch {file attributes test.db -readonly 1}
 358   sqlite3 db test.db
 359   if {[file writable test.db]} {
 360     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
 361   }
 362   execsql {
 363     SELECT * FROM t8;
 364   }
 365 } {xyzzy}
 366 do_test temptable-6.3 {
 367   if {[file writable test.db]} {
 368     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
 369   }
 370   catchsql {
 371     CREATE TABLE t9(x,y);
 372   }
 373 } {1 {attempt to write a readonly database}}
 374 do_test temptable-6.4 {
 375   catchsql {
 376     CREATE TEMP TABLE t9(x,y);
 377   }
 378 } {0 {}}
 379 do_test temptable-6.5 {
 380   catchsql {
 381     INSERT INTO t9 VALUES(1,2);
 382     SELECT * FROM t9;
 383   }
 384 } {0 {1 2}}
 385 do_test temptable-6.6 {
 386   if {[file writable test.db]} {
 387     error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
 388   }
 389   catchsql {
 390     INSERT INTO t8 VALUES('hello');
 391     SELECT * FROM t8;
 392   }
 393 } {1 {attempt to write a readonly database}}
 394 do_test temptable-6.7 {
 395   catchsql {
 396     SELECT * FROM t8,t9;
 397   }
 398 } {0 {xyzzy 1 2}}
 399 do_test temptable-6.8 {
 400   db close
 401   sqlite3 db test.db
 402   catchsql {
 403     SELECT * FROM t8,t9;
 404   }
 405 } {1 {no such table: t9}}
 406
 407 forcedelete test2.db test2.db-journal
 408 ifcapable attach {
 409   do_test temptable-7.1 {
 410     catchsql {
 411       ATTACH 'test2.db' AS two;
 412       CREATE TEMP TABLE two.abc(x,y);
 413     }
 414   } {1 {temporary table name must be unqualified}}
 415 }
 416
 417 # Need to do the following for tcl 8.5 on mac. On that configuration, the
 418 # -readonly flag is taken so seriously that a subsequent [forcedelete]
 419 # (required before the next test file can be executed) will fail.
 420 #
 421 catch {file attributes test.db -readonly 0}
 422
 423 do_test temptable-8.0 {
 424   db close
 425   catch {forcedelete test.db}
 426   sqlite3 db test.db
 427 } {}
 428 do_test temptable-8.1 {
 429   execsql { CREATE TEMP TABLE tbl2(a, b); }
 430   execsql {
 431     CREATE TABLE tbl(a, b);
 432     INSERT INTO tbl VALUES(1, 2);
 433   }
 434   execsql {SELECT * FROM tbl}
 435 } {1 2}
 436 do_test temptable-8.2 {
 437   execsql { CREATE TEMP TABLE tbl(a, b); }
 438   execsql {SELECT * FROM tbl}
 439 } {}
 440
 441 finish_test