File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / fts2r.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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.1.1 2012/02/21 17:04:16 misho 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

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>