gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/parser1.test (about) 1 # 2014-08-24 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. 12 # The focus of this script is testing details of the SQL language parser. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 do_catchsql_test parser1-1.1 { 19 CREATE TABLE t1( 20 a TEXT PRIMARY KEY, 21 b TEXT, 22 FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC) 23 ); 24 } {1 {syntax error after column name "b"}} 25 26 27 # Verify that a legacy schema in the sqlite_master file is allowed to have 28 # COLLATE, ASC, and DESC keywords on the id list of a FK constraint, and that 29 # those keywords are silently ignored. 30 # 31 sqlite3_db_config db DEFENSIVE 0 32 do_execsql_test parser1-1.2 { 33 CREATE TABLE t1( 34 a TEXT PRIMARY KEY, 35 b TEXT, 36 FOREIGN KEY(b) REFERENCES t1(a) 37 ); 38 INSERT INTO t1 VALUES('abc',NULL),('xyz','abc'); 39 PRAGMA writable_schema=on; 40 UPDATE sqlite_master SET sql='CREATE TABLE t1( 41 a TEXT PRIMARY KEY, 42 b TEXT, 43 FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a) 44 )' WHERE name='t1'; 45 SELECT name FROM sqlite_master WHERE sql LIKE '%collate%'; 46 } {t1} 47 sqlite3 db2 test.db 48 do_test parser1-1.3 { 49 sqlite3 db2 test.db 50 db2 eval {SELECT * FROM t1 ORDER BY 1} 51 } {abc {} xyz abc} 52 db2 close 53 54 do_execsql_test parser1-1.4 { 55 UPDATE sqlite_master SET sql='CREATE TABLE t1( 56 a TEXT PRIMARY KEY, 57 b TEXT, 58 FOREIGN KEY(b ASC) REFERENCES t1(a) 59 )' WHERE name='t1'; 60 SELECT name FROM sqlite_master WHERE sql LIKE '%ASC%'; 61 } {t1} 62 sqlite3 db2 test.db 63 do_test parser1-1.5 { 64 sqlite3 db2 test.db 65 db2 eval {SELECT * FROM t1 ORDER BY 1} 66 } {abc {} xyz abc} 67 db2 close 68 69 do_catchsql_test parser1-2.1 { 70 WITH RECURSIVE 71 c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) 72 SELECT x FROM c; 73 } {1 {syntax error after column name "x"}} 74 do_catchsql_test parser1-2.2 { 75 WITH RECURSIVE 76 c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) 77 SELECT x FROM c; 78 } {1 {syntax error after column name "x"}} 79 80 # Verify that the comma between multiple table constraints is 81 # optional. 82 # 83 # The missing comma is technically a syntax error. But we have to support 84 # it because there might be legacy databases that omit the commas in their 85 # sqlite_master tables. 86 # 87 do_execsql_test parser1-3.1 { 88 CREATE TABLE t300(id INTEGER PRIMARY KEY); 89 CREATE TABLE t301( 90 id INTEGER PRIMARY KEY, 91 c1 INTEGER NOT NULL, 92 c2 INTEGER NOT NULL, 93 c3 BOOLEAN NOT NULL DEFAULT 0, 94 FOREIGN KEY(c1) REFERENCES t300(id) ON DELETE CASCADE ON UPDATE RESTRICT 95 /* no comma */ 96 FOREIGN KEY(c2) REFERENCES t300(id) ON DELETE CASCADE ON UPDATE RESTRICT 97 /* no comma */ 98 UNIQUE(c1, c2) 99 ); 100 PRAGMA foreign_key_list(t301); 101 } {0 0 t300 c2 id RESTRICT CASCADE NONE 1 0 t300 c1 id RESTRICT CASCADE NONE} 102 103 finish_test