xref: /sqlite-3.40.0/test/quote.test (revision 0d92571d) 
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
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set testprefix quote
20
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 compatibility
37} {0 {5 hello}}
38do_test quote-1.2.3 {
39  catchsql {SELECT * FROM `@abc`}  ;# MySQL compatibility
40} {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 $msg
66} {0 {hello 10}}
67do_test quote-1.4 {
68  set r [catch {
69    execsql {UPDATE '@abc' SET '#xyz'=11}
70  } msg ]
71  lappend r $msg
72} {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 $msg
78} {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 $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#
94reset_db
95do_execsql_test 2.0 {
96  CREATE TABLE t1(x, y, z);
97}
98foreach {tn sql errname} {
99  1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null
100  2 { CREATE INDEX i2 ON t1(x, y, z||"abc") }        abc
101  3 { CREATE INDEX i3 ON t1("w") }                   w
102  4 { CREATE INDEX i4 ON t1(x) WHERE z="w" }         w
103} {
104  do_catchsql_test 2.1.$tn $sql [list 1 "no such column: $errname"]
105}
106
107do_execsql_test 2.2 {
108  PRAGMA writable_schema = 1;
109  CREATE TABLE xyz(a, b, c CHECK (c!="null") );
110  CREATE INDEX i2 ON t1(x, y, z||"abc");
111  CREATE INDEX i3 ON t1("w");
112  CREATE INDEX i4 ON t1(x) WHERE z="w";
113}
114
115db close
116sqlite3 db test.db
117
118do_execsql_test 2.3.1 {
119  INSERT INTO xyz VALUES(1, 2, 3);
120}
121do_catchsql_test 2.3.2 {
122  INSERT INTO xyz VALUES(1, 2, 'null');
123} {1 {CHECK constraint failed: xyz}}
124
125do_execsql_test 2.4 {
126  INSERT INTO t1 VALUES(1, 2, 3);
127  INSERT INTO t1 VALUES(4, 5, 'w');
128  SELECT * FROM t1 WHERE z='w';
129} {4 5 w}
130do_execsql_test 2.5 {
131  SELECT sql FROM sqlite_master;
132} {
133  {CREATE TABLE t1(x, y, z)}
134  {CREATE TABLE xyz(a, b, c CHECK (c!="null") )}
135  {CREATE INDEX i2 ON t1(x, y, z||"abc")}
136  {CREATE INDEX i3 ON t1("w")}
137  {CREATE INDEX i4 ON t1(x) WHERE z="w"}
138}
139
140
141
142finish_test
143