summaryrefslogtreecommitdiffstats
path: root/test/fts4upfrom.test
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--test/fts4upfrom.test139
1 files changed, 139 insertions, 0 deletions
diff --git a/test/fts4upfrom.test b/test/fts4upfrom.test
new file mode 100644
index 0000000..889b64a
--- /dev/null
+++ b/test/fts4upfrom.test
@@ -0,0 +1,139 @@
+# 2020 February 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 UPDATE statements with FROM clauses
+# against FTS4 tables.
+#
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix fts4upfrom
+
+# If SQLITE_ENABLE_FTS3 is defined, omit this file.
+ifcapable !fts3 {
+ finish_test
+ return
+}
+
+foreach {tn create_table} {
+ 0 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c) }
+ 1 { CREATE VIRTUAL TABLE ft USING fts3(a, b, c) }
+ 2 { CREATE TABLE ft(a, b, c) }
+ 3 {
+ CREATE TABLE real(a, b, c);
+ CREATE INDEX i1 ON real(a);
+ CREATE VIEW ft AS SELECT rowid, a, b, c FROM real;
+ CREATE TRIGGER tr1 INSTEAD OF INSERT ON ft BEGIN
+ INSERT INTO real(rowid, a, b, c) VALUES(new.rowid, new.a, new.b, new.c);
+ END;
+ CREATE TRIGGER tr2 INSTEAD OF UPDATE ON ft BEGIN
+ UPDATE real SET rowid=new.rowid, a=new.a, b=new.b, c=new.c
+ WHERE rowid=old.rowid;
+ END;
+ }
+} {
+ if {$tn==0} { ifcapable !fts5 { continue } }
+ catchsql { DROP VIEW IF EXISTS changes }
+ catchsql { DROP TABLE IF EXISTS ft }
+ catchsql { DROP VIEW IF EXISTS ft }
+ execsql $create_table
+
+ do_execsql_test 1.$tn.0 {
+ INSERT INTO ft(a, b, c) VALUES('a', NULL, 'apple');
+ INSERT INTO ft(a, b, c) VALUES('b', NULL, 'banana');
+ INSERT INTO ft(a, b, c) VALUES('c', NULL, 'cherry');
+ INSERT INTO ft(a, b, c) VALUES('d', NULL, 'damson plum');
+ }
+
+ do_execsql_test 1.$tn.1 {
+ SELECT a, b, c FROM ft ORDER BY rowid;
+ } {
+ a {} apple
+ b {} banana
+ c {} cherry
+ d {} {damson plum}
+ }
+
+ do_execsql_test 1.$tn.2 {
+ UPDATE ft SET b=o.c FROM ft AS o WHERE (ft.a == char(unicode(o.a)+1))
+ }
+
+ do_execsql_test 1.$tn.3 {
+ SELECT a, b, c FROM ft ORDER BY rowid;
+ } {
+ a {} apple
+ b apple banana
+ c banana cherry
+ d cherry {damson plum}
+ }
+
+ do_catchsql_test 1.$tn.4 {
+ UPDATE ft SET c=v FROM changes WHERE a=k;
+ } {1 {no such table: changes}}
+
+ do_execsql_test 1.$tn.5 {
+ create view changes(k, v) AS
+ VALUES( 'd', 'dewberry' ) UNION ALL
+ VALUES( 'c', 'clementine' ) UNION ALL
+ VALUES( 'b', 'blueberry' ) UNION ALL
+ VALUES( 'a', 'apricot' )
+ ;
+ }
+
+ do_execsql_test 1.$tn.6 {
+ UPDATE ft SET c=v FROM changes WHERE a=k;
+ }
+
+ do_execsql_test 1.$tn.7 {
+ SELECT rowid, a, b, c FROM ft ORDER BY rowid;
+ } {
+ 1 a {} apricot
+ 2 b apple blueberry
+ 3 c banana clementine
+ 4 d cherry dewberry
+ }
+
+ do_execsql_test 1.$tn.8 "
+ WITH x1(o, n) AS (
+ VALUES(1, 11) UNION ALL
+ VALUES(2, 12) UNION ALL
+ VALUES(3, 13) UNION ALL
+ VALUES(4, 14)
+ )
+ SELECT ft.rowid, a, b, c, o, n FROM ft, x1 WHERE ft.rowid = o;
+ " {
+ 1 a {} apricot 1 11
+ 2 b apple blueberry 2 12
+ 3 c banana clementine 3 13
+ 4 d cherry dewberry 4 14
+ }
+
+ set ROWID rowid
+ if {$tn==1} { set ROWID docid }
+ do_execsql_test 1.$tn.9 "
+ WITH x1(o, n) AS (
+ VALUES(1, 11) UNION ALL
+ VALUES(2, 12) UNION ALL
+ VALUES(3, 13) UNION ALL
+ VALUES(4, 14)
+ )
+ UPDATE ft SET $ROWID = n FROM x1 WHERE ft.rowid = o;
+ SELECT rowid, a, b, c FROM ft ORDER BY rowid;
+ " {
+ 11 a {} apricot
+ 12 b apple blueberry
+ 13 c banana clementine
+ 14 d cherry dewberry
+ }
+}
+
+finish_test