Return to fts3e.test CVS log

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

Annotation of embedaddon/sqlite3/test/fts3e.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 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