| # 2001 September 15 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. The |
| # focus of this file is the ability to specify table and column names |
| # as quoted strings. |
| # |
| # $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $ |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set testprefix quote |
| |
| # Create a table with a strange name and with strange column names. |
| # |
| do_test quote-1.0 { |
| catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );} |
| } {0 {}} |
| |
| # Insert, update and query the table. |
| # |
| do_test quote-1.1 { |
| catchsql {INSERT INTO '@abc' VALUES(5,'hello')} |
| } {0 {}} |
| do_test quote-1.2.1 { |
| catchsql {SELECT * FROM '@abc'} |
| } {0 {5 hello}} |
| do_test quote-1.2.2 { |
| catchsql {SELECT * FROM [@abc]} ;# SqlServer compatibility |
| } {0 {5 hello}} |
| do_test quote-1.2.3 { |
| catchsql {SELECT * FROM `@abc`} ;# MySQL compatibility |
| } {0 {5 hello}} |
| do_test quote-1.3 { |
| catchsql { |
| SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc' |
| } |
| } {0 {hello 10}} |
| do_test quote-1.3.1 { |
| catchsql { |
| SELECT '!pqr', '#xyz'+5 FROM '@abc' |
| } |
| } {0 {!pqr 5}} |
| do_test quote-1.3.2 { |
| catchsql { |
| SELECT "!pqr", "#xyz"+5 FROM '@abc' |
| } |
| } {0 {hello 10}} |
| do_test quote-1.3.3 { |
| catchsql { |
| SELECT [!pqr], `#xyz`+5 FROM '@abc' |
| } |
| } {0 {hello 10}} |
| do_test quote-1.3.4 { |
| set r [catch { |
| execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'} |
| } msg ] |
| lappend r $msg |
| } {0 {hello 10}} |
| do_test quote-1.4 { |
| set r [catch { |
| execsql {UPDATE '@abc' SET '#xyz'=11} |
| } msg ] |
| lappend r $msg |
| } {0 {}} |
| do_test quote-1.5 { |
| set r [catch { |
| execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'} |
| } msg ] |
| lappend r $msg |
| } {0 {hello 16}} |
| |
| # Drop the table with the strange name. |
| # |
| do_test quote-1.6 { |
| set r [catch { |
| execsql {DROP TABLE '@abc'} |
| } msg ] |
| lappend r $msg |
| } {0 {}} |
| |
| #------------------------------------------------------------------------- |
| # Check that it is not possible to use double-quotes for a string |
| # constant in a CHECK constraint or CREATE INDEX statement. However, |
| # SQLite can load such a schema from disk. |
| # |
| reset_db |
| sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 0 |
| sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 |
| do_execsql_test 2.0 { |
| CREATE TABLE t1(x, y, z); |
| } |
| foreach {tn sql errname} { |
| 1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null |
| 2 { CREATE INDEX i2 ON t1(x, y, z||"abc") } abc |
| 3 { CREATE INDEX i3 ON t1("w") } w |
| 4 { CREATE INDEX i4 ON t1(x) WHERE z="w" } w |
| } { |
| do_catchsql_test 2.1.$tn $sql [list 1 "no such column: $errname"] |
| } |
| |
| do_execsql_test 2.2 { |
| PRAGMA writable_schema = 1; |
| CREATE TABLE xyz(a, b, c CHECK (c!="null") ); |
| CREATE INDEX i2 ON t1(x, y, z||"abc"); |
| CREATE INDEX i3 ON t1("w"||""); |
| CREATE INDEX i4 ON t1(x) WHERE z="w"; |
| } |
| |
| db close |
| sqlite3 db test.db |
| |
| do_execsql_test 2.3.1 { |
| INSERT INTO xyz VALUES(1, 2, 3); |
| } |
| do_catchsql_test 2.3.2 { |
| INSERT INTO xyz VALUES(1, 2, 'null'); |
| } {1 {CHECK constraint failed: c!="null"}} |
| |
| do_execsql_test 2.4 { |
| INSERT INTO t1 VALUES(1, 2, 3); |
| INSERT INTO t1 VALUES(4, 5, 'w'); |
| SELECT * FROM t1 WHERE z='w'; |
| } {4 5 w} |
| do_execsql_test 2.5 { |
| SELECT sql FROM sqlite_master; |
| } { |
| {CREATE TABLE t1(x, y, z)} |
| {CREATE TABLE xyz(a, b, c CHECK (c!="null") )} |
| {CREATE INDEX i2 ON t1(x, y, z||"abc")} |
| {CREATE INDEX i3 ON t1("w"||"")} |
| {CREATE INDEX i4 ON t1(x) WHERE z="w"} |
| } |
| |
| # 2021-03-13 |
| # ticket 1c24a659e6d7f3a1 |
| ifcapable altertable { |
| reset_db |
| do_catchsql_test 3.0 { |
| CREATE TABLE t1(a,b); |
| CREATE INDEX x1 on t1("b"); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {1 {error in index x1 after drop column: no such column: b}} |
| do_catchsql_test 3.1 { |
| DROP TABLE t1; |
| CREATE TABLE t1(a,"b"); |
| CREATE INDEX x1 on t1("b"); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {1 {error in index x1 after drop column: no such column: b}} |
| do_catchsql_test 3.2 { |
| DROP TABLE t1; |
| CREATE TABLE t1(a,'b'); |
| CREATE INDEX x1 on t1("b"); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {1 {error in index x1 after drop column: no such column: b}} |
| do_catchsql_test 3.3 { |
| DROP TABLE t1; |
| CREATE TABLE t1(a,"b"); |
| CREATE INDEX x1 on t1('b'); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {1 {error in index x1 after drop column: no such column: b}} |
| do_catchsql_test 3.4 { |
| DROP TABLE t1; |
| CREATE TABLE t1(a, b, c); |
| CREATE INDEX x1 ON t1("a"||"b"); |
| INSERT INTO t1 VALUES(1,2,3),(1,4,5); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {1 {error in index x1 after drop column: no such column: b}} |
| sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1 |
| do_catchsql_test 3.5 { |
| DROP TABLE t1; |
| CREATE TABLE t1(a, b, c); |
| CREATE INDEX x1 ON t1("a"||"x"); |
| INSERT INTO t1 VALUES(1,2,3),(1,4,5); |
| ALTER TABLE t1 DROP COLUMN b; |
| } {0 {}} |
| } |
| |
| finish_test |