Annotation of embedaddon/sqlite3/test/e_resolve.test, revision 1.1
1.1 ! misho 1: # 2010 November 30
! 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: #
! 12: # This file implements tests to verify that the "testable statements" in
! 13: # the lang_naming.html document are correct.
! 14: #
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18: set ::testprefix e_resolve
! 19:
! 20: # An example database schema for testing name resolution:
! 21: #
! 22: set schema {
! 23: ATTACH 'test.db2' AS at1;
! 24: ATTACH 'test.db3' AS at2;
! 25:
! 26: CREATE TABLE temp.n1(x, y); INSERT INTO temp.n1 VALUES('temp', 'n1');
! 27: CREATE TRIGGER temp.n3 AFTER INSERT ON n1 BEGIN SELECT 1; END;
! 28: CREATE INDEX temp.n4 ON n1(x, y);
! 29:
! 30: CREATE TABLE main.n1(x, y); INSERT INTO main.n1 VALUES('main', 'n1');
! 31: CREATE TABLE main.n2(x, y); INSERT INTO main.n2 VALUES('main', 'n2');
! 32: CREATE INDEX main.n3 ON n2(y, x);
! 33: CREATE TRIGGER main.n4 BEFORE INSERT ON n2 BEGIN SELECT 1; END;
! 34:
! 35: CREATE TABLE at1.n1(x, y); INSERT INTO at1.n1 VALUES('at1', 'n1');
! 36: CREATE TABLE at1.n2(x, y); INSERT INTO at1.n2 VALUES('at1', 'n2');
! 37: CREATE TABLE at1.n3(x, y); INSERT INTO at1.n3 VALUES('at1', 'n3');
! 38:
! 39: CREATE TABLE at2.n1(x, y); INSERT INTO at2.n1 VALUES('at2', 'n1');
! 40: CREATE TABLE at2.n2(x, y); INSERT INTO at2.n2 VALUES('at2', 'n2');
! 41: CREATE TABLE at2.n3(x, y); INSERT INTO at2.n3 VALUES('at2', 'n3');
! 42: CREATE TABLE at2.n4(x, y); INSERT INTO at2.n4 VALUES('at2', 'n4');
! 43: CREATE TRIGGER at2.n4 BEFORE INSERT ON n4 BEGIN SELECT 1; END;
! 44: }
! 45:
! 46: proc resolve_reopen_db {} {
! 47: db close
! 48: forcedelete test.db test.db2 test.db3
! 49: sqlite3 db test.db
! 50: db eval $::schema
! 51: }
! 52:
! 53:
! 54:
! 55: # EVIDENCE-OF: R-33528-20612 If no database is specified as part of the
! 56: # object reference, then SQLite searches the main, temp and all attached
! 57: # databases for an object with a matching name. The temp database is
! 58: # searched first, followed by the main database, followed all attached
! 59: # databases in the order that they were attached. The reference resolves
! 60: # to the first match found.
! 61: #
! 62: resolve_reopen_db
! 63: do_execsql_test 1.1 { SELECT * FROM n1 } {temp n1}
! 64: do_execsql_test 1.2 { SELECT * FROM n2 } {main n2}
! 65: do_execsql_test 1.3 { SELECT * FROM n3 } {at1 n3}
! 66: do_execsql_test 1.4 { SELECT * FROM n4 } {at2 n4}
! 67:
! 68: # EVIDENCE-OF: R-54577-28142 If a database name is specified as part of
! 69: # an object reference, it must be either "main", or "temp" or the name
! 70: # of an attached database.
! 71: #
! 72: # Or else it is a "no such table: xxx" error.
! 73: #
! 74: resolve_reopen_db
! 75: do_execsql_test 2.1.1 { SELECT * FROM main.n1 } {main n1}
! 76: do_execsql_test 2.1.2 { SELECT * FROM temp.n1 } {temp n1}
! 77: do_execsql_test 2.1.3 { SELECT * FROM at1.n1 } {at1 n1}
! 78: do_execsql_test 2.1.4 { SELECT * FROM at2.n1 } {at2 n1}
! 79:
! 80: do_catchsql_test 2.2 { SELECT * FROM xxx.n1 } {1 {no such table: xxx.n1}}
! 81:
! 82: # EVIDENCE-OF: R-26223-47623 Like other SQL identifiers, database names
! 83: # are case-insensitive.
! 84: #
! 85: resolve_reopen_db
! 86: do_execsql_test 3.1 { SELECT * FROM MAIN.n1 } {main n1}
! 87: do_execsql_test 3.2 { SELECT * FROM tEmP.n1 } {temp n1}
! 88: do_execsql_test 3.3 { SELECT * FROM aT1.n1 } {at1 n1}
! 89: do_execsql_test 3.4 { SELECT * FROM At2.n1 } {at2 n1}
! 90:
! 91: # EVIDENCE-OF: R-15639-28392 If a database name is specified, then only
! 92: # the named database is searched for the named object.
! 93: #
! 94: do_catchsql_test 4.1 { SELECT * FROM temp.n2 } {1 {no such table: temp.n2}}
! 95: do_catchsql_test 4.2 { SELECT * FROM main.n2 } {0 {main n2}}
! 96: do_catchsql_test 4.3 { SELECT * FROM at1.n2 } {0 {at1 n2}}
! 97: do_catchsql_test 4.4 { SELECT * FROM at2.n2 } {0 {at2 n2}}
! 98:
! 99: # EVIDENCE-OF: R-08951-19801 When searching database schemas for a named
! 100: # object, objects of types that cannot be used in the context of the
! 101: # reference are always ignored.
! 102: #
! 103: # In this case, "types that cannot be used" are triggers and indexes.
! 104: # The temp and main databases both contain triggers and indexes named
! 105: # "n3" and "n4". Database "at2" contains a trigger called "n4". And yet:
! 106: #
! 107: do_execsql_test 5.1 { SELECT * FROM n3 } {at1 n3}
! 108: do_execsql_test 5.2 { SELECT * FROM n4 } {at2 n4}
! 109:
! 110: #-------------------------------------------------------------------------
! 111: # EVIDENCE-OF: R-37286-42536
! 112: #
! 113: db close
! 114: forcedelete test.db file.db
! 115: sqlite3 db test.db
! 116: do_execsql_test 6.1 {
! 117: ATTACH 'file.db' AS aux;
! 118: CREATE TABLE t1(x, y);
! 119: CREATE TEMP TABLE t1(x, y);
! 120: CREATE TABLE aux.t1(x, y);
! 121: }
! 122:
! 123: do_execsql_test 6.2.0 { DROP TABLE t1 }
! 124: do_catchsql_test 6.2.1 { SELECT * FROM temp.t1 } {1 {no such table: temp.t1}}
! 125: do_catchsql_test 6.2.2 { SELECT * FROM main.t1 } {0 {}}
! 126: do_catchsql_test 6.2.3 { SELECT * FROM aux.t1 } {0 {}}
! 127:
! 128: do_execsql_test 6.3.0 { DROP TABLE t1 }
! 129: do_catchsql_test 6.3.1 { SELECT * FROM main.t1 } {1 {no such table: main.t1}}
! 130: do_catchsql_test 6.3.3 { SELECT * FROM aux.t1 } {0 {}}
! 131:
! 132: do_execsql_test 6.4.0 { DROP TABLE t1 }
! 133: do_catchsql_test 6.4.1 { SELECT * FROM aux.t1 } {1 {no such table: aux.t1}}
! 134:
! 135: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_resolve.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test