AryaWu/sqlite

1# 2001 September 152#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this file is the ability to specify table and column names13# as quoted strings.14#15# $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix quote20 21# Create a table with a strange name and with strange column names.22#23do_test quote-1.0 {24  catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );}25} {0 {}}26 27# Insert, update and query the table.28#29do_test quote-1.1 {30  catchsql {INSERT INTO '@abc' VALUES(5,'hello')}31} {0 {}}32do_test quote-1.2.1 {33  catchsql {SELECT * FROM '@abc'}34} {0 {5 hello}}35do_test quote-1.2.2 {36  catchsql {SELECT * FROM [@abc]}  ;# SqlServer compatibility37} {0 {5 hello}}38do_test quote-1.2.3 {39  catchsql {SELECT * FROM `@abc`}  ;# MySQL compatibility40} {0 {5 hello}}41do_test quote-1.3 {42  catchsql {43    SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'44  }45} {0 {hello 10}}46do_test quote-1.3.1 {47  catchsql {48    SELECT '!pqr', '#xyz'+5 FROM '@abc'49  }50} {0 {!pqr 5}}51do_test quote-1.3.2 {52  catchsql {53    SELECT "!pqr", "#xyz"+5 FROM '@abc'54  }55} {0 {hello 10}}56do_test quote-1.3.3 {57  catchsql {58    SELECT [!pqr], `#xyz`+5 FROM '@abc'59  }60} {0 {hello 10}}61do_test quote-1.3.4 {62  set r [catch {63    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}64  } msg ]65  lappend r $msg66} {0 {hello 10}}67do_test quote-1.4 {68  set r [catch {69    execsql {UPDATE '@abc' SET '#xyz'=11}70  } msg ]71  lappend r $msg72} {0 {}}73do_test quote-1.5 {74  set r [catch {75    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}76  } msg ]77  lappend r $msg78} {0 {hello 16}}79 80# Drop the table with the strange name.81#82do_test quote-1.6 {83  set r [catch {84    execsql {DROP TABLE '@abc'}85  } msg ]86  lappend r $msg87} {0 {}}88 89#-------------------------------------------------------------------------90# Check that it is not possible to use double-quotes for a string91# constant in a CHECK constraint or CREATE INDEX statement. However, 92# SQLite can load such a schema from disk.93#94reset_db 95sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 096sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 197do_execsql_test 2.0 {98  CREATE TABLE t1(x, y, z);99}100foreach {tn sql errname} {101  1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null102  2 { CREATE INDEX i2 ON t1(x, y, z||"abc") }        abc103  3 { CREATE INDEX i3 ON t1("w") }                   w104  4 { CREATE INDEX i4 ON t1(x) WHERE z="w" }         w105} {106  do_catchsql_test 2.1.$tn $sql [list 1 "no such column: \"$errname\" - should this be a string literal in single-quotes?"]107}108 109do_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 117db close118sqlite3 db test.db119 120do_execsql_test 2.3.1 {121  INSERT INTO xyz VALUES(1, 2, 3);122}123do_catchsql_test 2.3.2 {124  INSERT INTO xyz VALUES(1, 2, 'null');125} {1 {CHECK constraint failed: c!="null"}}126 127do_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}132do_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-13143# ticket 1c24a659e6d7f3a1144ifcapable altertable {145  reset_db146    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" - should this be a string literal in single-quotes?}}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" - should this be a string literal in single-quotes?}}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" - should this be a string literal in single-quotes?}}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" - should this be a string literal in single-quotes?}}176  sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1177  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 186finish_test187