# 2014-08-24 # # 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 script is testing details of the SQL language parser. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_catchsql_test parser1-1.1 { CREATE TABLE t1( a TEXT PRIMARY KEY, b TEXT, FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC) ); } {1 {syntax error after column name "b"}} # Verify that a legacy schema in the sqlite_master file is allowed to have # COLLATE, ASC, and DESC keywords on the id list of a FK constraint, and that # those keywords are silently ignored. # do_execsql_test parser1-1.2 { CREATE TABLE t1( a TEXT PRIMARY KEY, b TEXT, FOREIGN KEY(b) REFERENCES t1(a) ); INSERT INTO t1 VALUES('abc',NULL),('xyz','abc'); PRAGMA writable_schema=on; UPDATE sqlite_master SET sql='CREATE TABLE t1( a TEXT PRIMARY KEY, b TEXT, FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a) )' WHERE name='t1'; SELECT name FROM sqlite_master WHERE sql LIKE '%collate%'; } {t1} sqlite3 db2 test.db do_test parser1-1.3 { sqlite3 db2 test.db db2 eval {SELECT * FROM t1 ORDER BY 1} } {abc {} xyz abc} db2 close do_execsql_test parser1-1.4 { UPDATE sqlite_master SET sql='CREATE TABLE t1( a TEXT PRIMARY KEY, b TEXT, FOREIGN KEY(b ASC) REFERENCES t1(a) )' WHERE name='t1'; SELECT name FROM sqlite_master WHERE sql LIKE '%ASC%'; } {t1} sqlite3 db2 test.db do_test parser1-1.5 { sqlite3 db2 test.db db2 eval {SELECT * FROM t1 ORDER BY 1} } {abc {} xyz abc} db2 close do_catchsql_test parser1-2.1 { WITH RECURSIVE c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) SELECT x FROM c; } {1 {syntax error after column name "x"}} do_catchsql_test parser1-2.2 { WITH RECURSIVE c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) SELECT x FROM c; } {1 {syntax error after column name "x"}} finish_test