gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fts3e.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 fts3 cursors.
    12  #
    13  # $Id: fts3e.test,v 1.1 2008/07/29 20:24:46 shess Exp $
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # If SQLITE_ENABLE_FTS3 is not defined, omit this file.
    20  ifcapable !fts3 {
    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 fts3(c);
    32    INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
    33    INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');
    34    INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');
    35  }
    36  
    37  do_test fts3e-1.1 {
    38    execsql {
    39      SELECT docid FROM t1 ORDER BY docid;
    40    }
    41  } {1 2 3}
    42  
    43  do_test fts3e-1.2 {
    44    execsql {
    45      SELECT docid FROM t1 WHERE c LIKE '%test' ORDER BY docid;
    46    }
    47  } {1 2 3}
    48  
    49  do_test fts3e-1.3 {
    50    execsql {
    51      SELECT docid FROM t1 WHERE c LIKE 'That%' ORDER BY docid;
    52    }
    53  } {2}
    54  
    55  #*************************************************************************
    56  # Test lookup by docid (QUERY_DOCID).  This kind of query happens for
    57  # queries which select by the docid/rowid implicit index.
    58  db eval {
    59    DROP TABLE IF EXISTS t1;
    60    DROP TABLE IF EXISTS t2;
    61    CREATE VIRTUAL TABLE t1 USING fts3(c);
    62    CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
    63    INSERT INTO t2 VALUES (null, 10);
    64    INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
    65    INSERT INTO t2 VALUES (null, 5);
    66    INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');
    67    INSERT INTO t2 VALUES (null, 20);
    68    INSERT INTO t1 (docid, 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_DOCID in this case, as for a very large table the
    73  # full scan is less efficient.
    74  do_test fts3e-2.1 {
    75    execsql {
    76      SELECT docid FROM t1 WHERE docid in (1, 2, 10);
    77      SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);
    78    }
    79  } {1 2 1 2}
    80  
    81  do_test fts3e-2.2 {
    82    execsql {
    83      SELECT docid, weight FROM t1, t2 WHERE t2.id = t1.docid ORDER BY weight;
    84      SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;
    85    }
    86  } {2 5 1 10 3 20 2 5 1 10 3 20}
    87  
    88  do_test fts3e-2.3 {
    89    execsql {
    90      SELECT docid, weight FROM t1, t2
    91             WHERE t2.weight>5 AND t2.id = t1.docid ORDER BY weight;
    92      SELECT t1.rowid, weight FROM t1, t2
    93             WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;
    94    }
    95  } {1 10 3 20 1 10 3 20}
    96  
    97  #*************************************************************************
    98  # Test lookup by MATCH (QUERY_FULLTEXT).  This is the fulltext index.
    99  db eval {
   100    DROP TABLE IF EXISTS t1;
   101    DROP TABLE IF EXISTS t2;
   102    CREATE VIRTUAL TABLE t1 USING fts3(c);
   103    CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
   104    INSERT INTO t2 VALUES (null, 10);
   105    INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
   106    INSERT INTO t2 VALUES (null, 5);
   107    INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');
   108    INSERT INTO t2 VALUES (null, 20);
   109    INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
   110  }
   111  
   112  do_test fts3e-3.1 {
   113    execsql {
   114      SELECT docid FROM t1 WHERE t1 MATCH 'this' ORDER BY docid;
   115    }
   116  } {1 3}
   117  
   118  do_test fts3e-3.2 {
   119    execsql {
   120      SELECT docid, weight FROM t1, t2
   121       WHERE t1 MATCH 'this' AND t1.docid = t2.id ORDER BY weight;
   122    }
   123  } {1 10 3 20}
   124  
   125  finish_test