gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/quote.test (about) 1 # 2001 September 15 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. The 12 # focus of this file is the ability to specify table and column names 13 # as quoted strings. 14 # 15 # $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set testprefix quote 20 21 # Create a table with a strange name and with strange column names. 22 # 23 do_test quote-1.0 { 24 catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );} 25 } {0 {}} 26 27 # Insert, update and query the table. 28 # 29 do_test quote-1.1 { 30 catchsql {INSERT INTO '@abc' VALUES(5,'hello')} 31 } {0 {}} 32 do_test quote-1.2.1 { 33 catchsql {SELECT * FROM '@abc'} 34 } {0 {5 hello}} 35 do_test quote-1.2.2 { 36 catchsql {SELECT * FROM [@abc]} ;# SqlServer compatibility 37 } {0 {5 hello}} 38 do_test quote-1.2.3 { 39 catchsql {SELECT * FROM `@abc`} ;# MySQL compatibility 40 } {0 {5 hello}} 41 do_test quote-1.3 { 42 catchsql { 43 SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc' 44 } 45 } {0 {hello 10}} 46 do_test quote-1.3.1 { 47 catchsql { 48 SELECT '!pqr', '#xyz'+5 FROM '@abc' 49 } 50 } {0 {!pqr 5}} 51 do_test quote-1.3.2 { 52 catchsql { 53 SELECT "!pqr", "#xyz"+5 FROM '@abc' 54 } 55 } {0 {hello 10}} 56 do_test quote-1.3.3 { 57 catchsql { 58 SELECT [!pqr], `#xyz`+5 FROM '@abc' 59 } 60 } {0 {hello 10}} 61 do_test quote-1.3.4 { 62 set r [catch { 63 execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'} 64 } msg ] 65 lappend r $msg 66 } {0 {hello 10}} 67 do_test quote-1.4 { 68 set r [catch { 69 execsql {UPDATE '@abc' SET '#xyz'=11} 70 } msg ] 71 lappend r $msg 72 } {0 {}} 73 do_test quote-1.5 { 74 set r [catch { 75 execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'} 76 } msg ] 77 lappend r $msg 78 } {0 {hello 16}} 79 80 # Drop the table with the strange name. 81 # 82 do_test quote-1.6 { 83 set r [catch { 84 execsql {DROP TABLE '@abc'} 85 } msg ] 86 lappend r $msg 87 } {0 {}} 88 89 #------------------------------------------------------------------------- 90 # Check that it is not possible to use double-quotes for a string 91 # constant in a CHECK constraint or CREATE INDEX statement. However, 92 # SQLite can load such a schema from disk. 93 # 94 reset_db 95 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 0 96 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 97 do_execsql_test 2.0 { 98 CREATE TABLE t1(x, y, z); 99 } 100 foreach {tn sql errname} { 101 1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null 102 2 { CREATE INDEX i2 ON t1(x, y, z||"abc") } abc 103 3 { CREATE INDEX i3 ON t1("w") } w 104 4 { CREATE INDEX i4 ON t1(x) WHERE z="w" } w 105 } { 106 do_catchsql_test 2.1.$tn $sql [list 1 "no such column: $errname"] 107 } 108 109 do_execsql_test 2.2 { 110 PRAGMA writable_schema = 1; 111 CREATE TABLE xyz(a, b, c CHECK (c!="null") ); 112 CREATE INDEX i2 ON t1(x, y, z||"abc"); 113 CREATE INDEX i3 ON t1("w"||""); 114 CREATE INDEX i4 ON t1(x) WHERE z="w"; 115 } 116 117 db close 118 sqlite3 db test.db 119 120 do_execsql_test 2.3.1 { 121 INSERT INTO xyz VALUES(1, 2, 3); 122 } 123 do_catchsql_test 2.3.2 { 124 INSERT INTO xyz VALUES(1, 2, 'null'); 125 } {1 {CHECK constraint failed: c!="null"}} 126 127 do_execsql_test 2.4 { 128 INSERT INTO t1 VALUES(1, 2, 3); 129 INSERT INTO t1 VALUES(4, 5, 'w'); 130 SELECT * FROM t1 WHERE z='w'; 131 } {4 5 w} 132 do_execsql_test 2.5 { 133 SELECT sql FROM sqlite_master; 134 } { 135 {CREATE TABLE t1(x, y, z)} 136 {CREATE TABLE xyz(a, b, c CHECK (c!="null") )} 137 {CREATE INDEX i2 ON t1(x, y, z||"abc")} 138 {CREATE INDEX i3 ON t1("w"||"")} 139 {CREATE INDEX i4 ON t1(x) WHERE z="w"} 140 } 141 142 # 2021-03-13 143 # ticket 1c24a659e6d7f3a1 144 ifcapable altertable { 145 reset_db 146 do_catchsql_test 3.0 { 147 CREATE TABLE t1(a,b); 148 CREATE INDEX x1 on t1("b"); 149 ALTER TABLE t1 DROP COLUMN b; 150 } {1 {error in index x1 after drop column: no such column: b}} 151 do_catchsql_test 3.1 { 152 DROP TABLE t1; 153 CREATE TABLE t1(a,"b"); 154 CREATE INDEX x1 on t1("b"); 155 ALTER TABLE t1 DROP COLUMN b; 156 } {1 {error in index x1 after drop column: no such column: b}} 157 do_catchsql_test 3.2 { 158 DROP TABLE t1; 159 CREATE TABLE t1(a,'b'); 160 CREATE INDEX x1 on t1("b"); 161 ALTER TABLE t1 DROP COLUMN b; 162 } {1 {error in index x1 after drop column: no such column: b}} 163 do_catchsql_test 3.3 { 164 DROP TABLE t1; 165 CREATE TABLE t1(a,"b"); 166 CREATE INDEX x1 on t1('b'); 167 ALTER TABLE t1 DROP COLUMN b; 168 } {1 {error in index x1 after drop column: no such column: b}} 169 do_catchsql_test 3.4 { 170 DROP TABLE t1; 171 CREATE TABLE t1(a, b, c); 172 CREATE INDEX x1 ON t1("a"||"b"); 173 INSERT INTO t1 VALUES(1,2,3),(1,4,5); 174 ALTER TABLE t1 DROP COLUMN b; 175 } {1 {error in index x1 after drop column: no such column: b}} 176 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1 177 do_catchsql_test 3.5 { 178 DROP TABLE t1; 179 CREATE TABLE t1(a, b, c); 180 CREATE INDEX x1 ON t1("a"||"x"); 181 INSERT INTO t1 VALUES(1,2,3),(1,4,5); 182 ALTER TABLE t1 DROP COLUMN b; 183 } {0 {}} 184 } 185 186 finish_test