Return to fts3e.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

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 fts3 cursors.
   12: #
   13: # $Id: fts3e.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_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