modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/fts5/test/fts5alter.test (about)

     1  # 2015 Jun 10
     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  #
    12  # The tests in this file focus on renaming FTS5 tables using the
    13  # "ALTER TABLE ... RENAME TO ..." command
    14  #
    15  
    16  source [file join [file dirname [info script]] fts5_common.tcl]
    17  set testprefix fts5alter
    18  
    19  # If SQLITE_ENABLE_FTS5 is defined, omit this file.
    20  ifcapable !fts5 {
    21    finish_test
    22    return
    23  }
    24  
    25  #-------------------------------------------------------------------------
    26  # Test renaming regular, contentless and columnsize=0 FTS5 tables.
    27  #
    28  do_execsql_test 1.1.0 {
    29    CREATE VIRTUAL TABLE "a x" USING fts5(a, x);
    30    INSERT INTO "a x" VALUES('a a a', 'x x x');
    31    ALTER TABLE "a x" RENAME TO "x y";
    32  }
    33  do_execsql_test 1.1.1 {
    34    SELECT * FROM "x y";
    35    SELECT rowid FROM "x y" WHERE "x y" MATCH 'a'
    36  } {{a a a} {x x x} 1}
    37  
    38  do_execsql_test 1.2.0 {
    39    CREATE VIRTUAL TABLE "one/two" USING fts5(one, columnsize=0);
    40    INSERT INTO "one/two"(rowid, one) VALUES(456, 'd d d');
    41    ALTER TABLE "one/two" RENAME TO "three/four";
    42  }
    43  do_execsql_test 1.2.1 {
    44    SELECT * FROM "three/four";
    45    SELECT rowid FROM "three/four" WHERE "three/four" MATCH 'd'
    46  } {{d d d} 456}
    47  
    48  do_execsql_test 1.3.0 {
    49    CREATE VIRTUAL TABLE t1 USING fts5(val, content='');
    50    INSERT INTO t1(rowid, val) VALUES(-1, 'drop table');
    51    INSERT INTO t1(rowid, val) VALUES(-2, 'drop view');
    52    ALTER TABLE t1 RENAME TO t2;
    53  }
    54  do_execsql_test 1.3.1 {
    55    SELECT rowid, * FROM t2;
    56    SELECT rowid FROM t2 WHERE t2 MATCH 'table'
    57  } {-2 {} -1 {} -1}
    58  
    59  #-------------------------------------------------------------------------
    60  # Test renaming an FTS5 table within a transaction.
    61  #
    62  do_execsql_test 2.1 {
    63    CREATE VIRTUAL TABLE zz USING fts5(a);
    64    INSERT INTO zz(rowid, a) VALUES(-56, 'a b c');
    65    BEGIN;
    66      INSERT INTO zz(rowid, a) VALUES(-22, 'a b c');
    67      ALTER TABLE zz RENAME TO yy;
    68      SELECT rowid FROM yy WHERE yy MATCH 'a + b + c';
    69    COMMIT;
    70  } {-56 -22}
    71  
    72  do_execsql_test 2.2 {
    73    BEGIN;
    74      ALTER TABLE yy RENAME TO ww;
    75      INSERT INTO ww(rowid, a) VALUES(-11, 'a b c');
    76      SELECT rowid FROM ww WHERE ww MATCH 'a + b + c';
    77  } {-56 -22 -11}
    78  
    79  do_execsql_test 2.3 {
    80    ROLLBACK;
    81    SELECT rowid FROM yy WHERE yy MATCH 'a + b + c';
    82  } {-56 -22}
    83  
    84  #-------------------------------------------------------------------------
    85  
    86  do_execsql_test 3.1 {
    87    CREATE VIRTUAL TABLE abc USING fts5(a);
    88    INSERT INTO abc(rowid, a) VALUES(1, 'a');
    89    BEGIN;
    90      INSERT INTO abc(rowid, a) VALUES(2, 'a');
    91  }
    92  do_execsql_test 3.2 {
    93      SELECT rowid FROM abc WHERE abc MATCH 'a';
    94  } {1 2}
    95  
    96  do_execsql_test 3.3 {
    97    COMMIT;
    98    SELECT rowid FROM abc WHERE abc MATCH 'a';
    99  } {1 2}
   100  
   101  finish_test