AryaWu/sqlite

1# 2020 February 242#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is testing UPDATE statements with FROM clauses13# against FTS4 tables.14#15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix fts4upfrom20 21# If SQLITE_ENABLE_FTS3 is defined, omit this file.22ifcapable !fts3 {23  finish_test24  return25}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 BEGIN36      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 BEGIN39      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_table49 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 {} apple61    b {} banana62    c {} cherry63    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 {} apple74    b apple banana75    c banana cherry76    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 ALL86      VALUES( 'c', 'clementine' ) UNION ALL87      VALUES( 'b', 'blueberry' ) UNION ALL88      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 {} apricot100    2 b apple blueberry101    3 c banana clementine102    4 d cherry dewberry103  }104 105  do_execsql_test 1.$tn.8 "106    WITH x1(o, n) AS (107        VALUES(1, 11) UNION ALL108        VALUES(2, 12) UNION ALL109        VALUES(3, 13) UNION ALL110        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 11115    2 b apple blueberry 2 12116    3 c banana clementine 3 13117    4 d cherry dewberry 4 14118  }119 120  set ROWID rowid121  if {$tn==1} { set ROWID docid }122  do_execsql_test 1.$tn.9 "123    WITH x1(o, n) AS (124        VALUES(1, 11) UNION ALL125        VALUES(2, 12) UNION ALL126        VALUES(3, 13) UNION ALL127        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 {} apricot133    12 b apple blueberry134    13 c banana clementine135    14 d cherry dewberry136  }137}138 139finish_test140