Return to fts2r.test CVS log

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

Annotation of embedaddon/sqlite3/test/fts2r.test, revision 1.1.1.1

1.1       misho       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