gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fts4upfrom.test (about)

     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  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  set testprefix fts4upfrom
    20  
    21  # If SQLITE_ENABLE_FTS3 is defined, omit this file.
    22  ifcapable !fts3 {
    23    finish_test
    24    return
    25  }
    26  
    27  foreach {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  
   139  finish_test