1# 2020 February 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. The 12# focus of this script is testing UPDATE statements with FROM clauses 13# against FTS4 tables. 14# 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set testprefix fts4upfrom 20 21# If SQLITE_ENABLE_FTS3 is defined, omit this file. 22ifcapable !fts3 { 23 finish_test 24 return 25} 26 27foreach {tn create_table} { 28 0 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c) } 29 1 { CREATE VIRTUAL TABLE ft USING fts3(a, b, c) } 30 2 { CREATE TABLE ft(a, b, c) } 31 3 { 32 CREATE TABLE real(a, b, c); 33 CREATE INDEX i1 ON real(a); 34 CREATE VIEW ft AS SELECT rowid, a, b, c FROM real; 35 CREATE TRIGGER tr1 INSTEAD OF INSERT ON ft BEGIN 36 INSERT INTO real(rowid, a, b, c) VALUES(new.rowid, new.a, new.b, new.c); 37 END; 38 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON ft BEGIN 39 UPDATE real SET rowid=new.rowid, a=new.a, b=new.b, c=new.c 40 WHERE rowid=old.rowid; 41 END; 42 } 43} { 44 if {$tn==0} { ifcapable !fts5 { continue } } 45 catchsql { DROP VIEW IF EXISTS changes } 46 catchsql { DROP TABLE IF EXISTS ft } 47 catchsql { DROP VIEW IF EXISTS ft } 48 execsql $create_table 49 50 do_execsql_test 1.$tn.0 { 51 INSERT INTO ft(a, b, c) VALUES('a', NULL, 'apple'); 52 INSERT INTO ft(a, b, c) VALUES('b', NULL, 'banana'); 53 INSERT INTO ft(a, b, c) VALUES('c', NULL, 'cherry'); 54 INSERT INTO ft(a, b, c) VALUES('d', NULL, 'damson plum'); 55 } 56 57 do_execsql_test 1.$tn.1 { 58 SELECT a, b, c FROM ft ORDER BY rowid; 59 } { 60 a {} apple 61 b {} banana 62 c {} cherry 63 d {} {damson plum} 64 } 65 66 do_execsql_test 1.$tn.2 { 67 UPDATE ft SET b=o.c FROM ft AS o WHERE (ft.a == char(unicode(o.a)+1)) 68 } 69 70 do_execsql_test 1.$tn.3 { 71 SELECT a, b, c FROM ft ORDER BY rowid; 72 } { 73 a {} apple 74 b apple banana 75 c banana cherry 76 d cherry {damson plum} 77 } 78 79 do_catchsql_test 1.$tn.4 { 80 UPDATE ft SET c=v FROM changes WHERE a=k; 81 } {1 {no such table: changes}} 82 83 do_execsql_test 1.$tn.5 { 84 create view changes(k, v) AS 85 VALUES( 'd', 'dewberry' ) UNION ALL 86 VALUES( 'c', 'clementine' ) UNION ALL 87 VALUES( 'b', 'blueberry' ) UNION ALL 88 VALUES( 'a', 'apricot' ) 89 ; 90 } 91 92 do_execsql_test 1.$tn.6 { 93 UPDATE ft SET c=v FROM changes WHERE a=k; 94 } 95 96 do_execsql_test 1.$tn.7 { 97 SELECT rowid, a, b, c FROM ft ORDER BY rowid; 98 } { 99 1 a {} apricot 100 2 b apple blueberry 101 3 c banana clementine 102 4 d cherry dewberry 103 } 104 105 do_execsql_test 1.$tn.8 " 106 WITH x1(o, n) AS ( 107 VALUES(1, 11) UNION ALL 108 VALUES(2, 12) UNION ALL 109 VALUES(3, 13) UNION ALL 110 VALUES(4, 14) 111 ) 112 SELECT ft.rowid, a, b, c, o, n FROM ft, x1 WHERE ft.rowid = o; 113 " { 114 1 a {} apricot 1 11 115 2 b apple blueberry 2 12 116 3 c banana clementine 3 13 117 4 d cherry dewberry 4 14 118 } 119 120 set ROWID rowid 121 if {$tn==1} { set ROWID docid } 122 do_execsql_test 1.$tn.9 " 123 WITH x1(o, n) AS ( 124 VALUES(1, 11) UNION ALL 125 VALUES(2, 12) UNION ALL 126 VALUES(3, 13) UNION ALL 127 VALUES(4, 14) 128 ) 129 UPDATE ft SET $ROWID = n FROM x1 WHERE ft.rowid = o; 130 SELECT rowid, a, b, c FROM ft ORDER BY rowid; 131 " { 132 11 a {} apricot 133 12 b apple blueberry 134 13 c banana clementine 135 14 d cherry dewberry 136 } 137} 138 139finish_test 140