github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/fts2r.test (about)

     1  # 2008 July 29
     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  # These tests exercise the various types of fts2 cursors.
    12  #
    13  # $Id: fts2r.test,v 1.1 2008/07/29 20:38:18 shess Exp $
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # If SQLITE_ENABLE_FTS2 is not defined, omit this file.
    20  ifcapable !fts2 {
    21    finish_test
    22    return
    23  }
    24  
    25  #*************************************************************************
    26  # Test table scan (QUERY_GENERIC).  This kind of query happens for
    27  # queries with no WHERE clause, or for WHERE clauses which cannot be
    28  # satisfied by an index.
    29  db eval {
    30    DROP TABLE IF EXISTS t1;
    31    CREATE VIRTUAL TABLE t1 USING fts2(c);
    32    INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
    33    INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
    34    INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
    35  }
    36  
    37  do_test fts2e-1.1 {
    38    execsql {
    39      SELECT rowid FROM t1 ORDER BY rowid;
    40    }
    41  } {1 2 3}
    42  
    43  do_test fts2e-1.2 {
    44    execsql {
    45      SELECT rowid FROM t1 WHERE c LIKE '%test' ORDER BY rowid;
    46    }
    47  } {1 2 3}
    48  
    49  do_test fts2e-1.3 {
    50    execsql {
    51      SELECT rowid FROM t1 WHERE c LIKE 'That%' ORDER BY rowid;
    52    }
    53  } {2}
    54  
    55  #*************************************************************************
    56  # Test lookup by rowid (QUERY_ROWID).  This kind of query happens for
    57  # queries which select by the rowid implicit index.
    58  db eval {
    59    DROP TABLE IF EXISTS t1;
    60    DROP TABLE IF EXISTS t2;
    61    CREATE VIRTUAL TABLE t1 USING fts2(c);
    62    CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
    63    INSERT INTO t2 VALUES (null, 10);
    64    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
    65    INSERT INTO t2 VALUES (null, 5);
    66    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
    67    INSERT INTO t2 VALUES (null, 20);
    68    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
    69  }
    70  
    71  # TODO(shess): This actually is doing QUERY_GENERIC?  I'd have
    72  # expected QUERY_ROWID in this case, as for a very large table the
    73  # full scan is less efficient.
    74  do_test fts2e-2.1 {
    75    execsql {
    76      SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);
    77    }
    78  } {1 2}
    79  
    80  do_test fts2e-2.2 {
    81    execsql {
    82      SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;
    83    }
    84  } {2 5 1 10 3 20}
    85  
    86  do_test fts2e-2.3 {
    87    execsql {
    88      SELECT t1.rowid, weight FROM t1, t2
    89             WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;
    90    }
    91  } {1 10 3 20}
    92  
    93  #*************************************************************************
    94  # Test lookup by MATCH (QUERY_FULLTEXT).  This is the fulltext index.
    95  db eval {
    96    DROP TABLE IF EXISTS t1;
    97    DROP TABLE IF EXISTS t2;
    98    CREATE VIRTUAL TABLE t1 USING fts2(c);
    99    CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
   100    INSERT INTO t2 VALUES (null, 10);
   101    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
   102    INSERT INTO t2 VALUES (null, 5);
   103    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
   104    INSERT INTO t2 VALUES (null, 20);
   105    INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
   106  }
   107  
   108  do_test fts2e-3.1 {
   109    execsql {
   110      SELECT rowid FROM t1 WHERE t1 MATCH 'this' ORDER BY rowid;
   111    }
   112  } {1 3}
   113  
   114  do_test fts2e-3.2 {
   115    execsql {
   116      SELECT t1.rowid, weight FROM t1, t2
   117       WHERE t1 MATCH 'this' AND t1.rowid = t2.id ORDER BY weight;
   118    }
   119  } {1 10 3 20}
   120  
   121  finish_test