000001  # 2010 November 30
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_naming.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  set ::testprefix e_resolve
000019  
000020  # An example database schema for testing name resolution:
000021  #
000022  set schema {
000023    ATTACH 'test.db2' AS at1;
000024    ATTACH 'test.db3' AS at2;
000025  
000026    CREATE TABLE   temp.n1(x, y); INSERT INTO temp.n1 VALUES('temp', 'n1');
000027    CREATE TRIGGER temp.n3 AFTER INSERT ON n1 BEGIN SELECT 1; END;
000028    CREATE INDEX   temp.n4 ON n1(x, y);
000029  
000030    CREATE TABLE   main.n1(x, y); INSERT INTO main.n1 VALUES('main', 'n1');
000031    CREATE TABLE   main.n2(x, y); INSERT INTO main.n2 VALUES('main', 'n2');
000032    CREATE INDEX   main.n3 ON n2(y, x);
000033    CREATE TRIGGER main.n4 BEFORE INSERT ON n2 BEGIN SELECT 1; END;
000034  
000035    CREATE TABLE   at1.n1(x, y);  INSERT INTO at1.n1 VALUES('at1', 'n1');
000036    CREATE TABLE   at1.n2(x, y);  INSERT INTO at1.n2 VALUES('at1', 'n2');
000037    CREATE TABLE   at1.n3(x, y);  INSERT INTO at1.n3 VALUES('at1', 'n3');
000038  
000039    CREATE TABLE   at2.n1(x, y);  INSERT INTO at2.n1 VALUES('at2', 'n1');
000040    CREATE TABLE   at2.n2(x, y);  INSERT INTO at2.n2 VALUES('at2', 'n2');
000041    CREATE TABLE   at2.n3(x, y);  INSERT INTO at2.n3 VALUES('at2', 'n3');
000042    CREATE TABLE   at2.n4(x, y);  INSERT INTO at2.n4 VALUES('at2', 'n4');
000043    CREATE TRIGGER at2.n4 BEFORE INSERT ON n4 BEGIN SELECT 1; END;
000044  }
000045  
000046  proc resolve_reopen_db {} {
000047    db close
000048    forcedelete test.db test.db2 test.db3
000049    sqlite3 db test.db
000050    db eval $::schema
000051  }
000052  
000053  
000054  
000055  # EVIDENCE-OF: R-33528-20612 If no database is specified as part of the
000056  # object reference, then SQLite searches the main, temp and all attached
000057  # databases for an object with a matching name. The temp database is
000058  # searched first, followed by the main database, followed all attached
000059  # databases in the order that they were attached. The reference resolves
000060  # to the first match found.
000061  #
000062  resolve_reopen_db
000063  do_execsql_test 1.1 { SELECT * FROM n1 } {temp n1}
000064  do_execsql_test 1.2 { SELECT * FROM n2 } {main n2}
000065  do_execsql_test 1.3 { SELECT * FROM n3 } {at1  n3}
000066  do_execsql_test 1.4 { SELECT * FROM n4 } {at2  n4}
000067  
000068  # EVIDENCE-OF: R-00634-08585 If a schema name is specified as part of an
000069  # object reference, it must be either "main", or "temp" or the
000070  # schema-name of an attached database.
000071  #
000072  #   Or else it is a "no such table: xxx" error.
000073  #
000074  resolve_reopen_db
000075  do_execsql_test 2.1.1 { SELECT * FROM main.n1 } {main n1}
000076  do_execsql_test 2.1.2 { SELECT * FROM temp.n1 } {temp n1}
000077  do_execsql_test 2.1.3 { SELECT * FROM at1.n1 } {at1 n1}
000078  do_execsql_test 2.1.4 { SELECT * FROM at2.n1 } {at2 n1}
000079  
000080  do_catchsql_test 2.2 { SELECT * FROM xxx.n1 } {1 {no such table: xxx.n1}}
000081  
000082  # EVIDENCE-OF: R-17446-42210 Like other SQL identifiers, schema names
000083  # are case-insensitive.
000084  #
000085  resolve_reopen_db
000086  do_execsql_test 3.1 { SELECT * FROM MAIN.n1 } {main n1}
000087  do_execsql_test 3.2 { SELECT * FROM tEmP.n1 } {temp n1}
000088  do_execsql_test 3.3 { SELECT * FROM aT1.n1 } {at1 n1}
000089  do_execsql_test 3.4 { SELECT * FROM At2.n1 } {at2 n1}
000090  
000091  # EVIDENCE-OF: R-14755-58619 If a schema name is specified, then only
000092  # that one schema is searched for the named object.
000093  #
000094  do_catchsql_test 4.1 { SELECT * FROM temp.n2 } {1 {no such table: temp.n2}}
000095  do_catchsql_test 4.2 { SELECT * FROM main.n2 } {0 {main n2}}
000096  do_catchsql_test 4.3 { SELECT * FROM at1.n2 }  {0 {at1 n2}}
000097  do_catchsql_test 4.4 { SELECT * FROM at2.n2 }  {0 {at2 n2}}
000098  
000099  # EVIDENCE-OF: R-08951-19801 When searching database schemas for a named
000100  # object, objects of types that cannot be used in the context of the
000101  # reference are always ignored.
000102  #
000103  #   In this case, "types that cannot be used" are triggers and indexes.
000104  #   The temp and main databases both contain triggers and indexes named
000105  #   "n3" and "n4". Database "at2" contains a trigger called "n4". And yet:
000106  #
000107  do_execsql_test 5.1 { SELECT * FROM n3 } {at1  n3}
000108  do_execsql_test 5.2 { SELECT * FROM n4 } {at2  n4}
000109  
000110  #-------------------------------------------------------------------------
000111  # EVIDENCE-OF: R-37286-42536 
000112  #
000113  db close
000114  forcedelete test.db file.db
000115  sqlite3 db test.db
000116  do_execsql_test 6.1 {
000117    ATTACH 'file.db' AS aux;
000118    CREATE TABLE t1(x, y);
000119    CREATE TEMP TABLE t1(x, y);
000120    CREATE TABLE aux.t1(x, y);
000121  }
000122  
000123  do_execsql_test  6.2.0 { DROP TABLE t1 }
000124  do_catchsql_test 6.2.1 { SELECT * FROM temp.t1 } {1 {no such table: temp.t1}}
000125  do_catchsql_test 6.2.2 { SELECT * FROM main.t1 } {0 {}}
000126  do_catchsql_test 6.2.3 { SELECT * FROM aux.t1  } {0 {}}
000127  
000128  do_execsql_test  6.3.0 { DROP TABLE t1 }
000129  do_catchsql_test 6.3.1 { SELECT * FROM main.t1 } {1 {no such table: main.t1}}
000130  do_catchsql_test 6.3.3 { SELECT * FROM aux.t1  } {0 {}}
000131  
000132  do_execsql_test  6.4.0 { DROP TABLE t1 }
000133  do_catchsql_test 6.4.1 { SELECT * FROM aux.t1 } {1 {no such table: aux.t1}}
000134  
000135  finish_test