SQLite

Check-in [02870ed21d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Backport http://www.sqlite.org/cvstrac/chngview?cn=5489 from fts3. Re-used prepared statement from fts2 cursor. (CVS 5499)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 02870ed21dae2601a656b2f30c3ca0041e9cb60f
User & Date: shess 2008-07-29 20:38:18.000
Context
2008-07-30
13:14
Remove unused sqlite3VdbeMemDynamicify() function from the VDBE. (CVS 5500) (check-in: aee6330831 user: drh tags: trunk)
2008-07-29
20:38
Backport http://www.sqlite.org/cvstrac/chngview?cn=5489 from fts3. Re-used prepared statement from fts2 cursor. (CVS 5499) (check-in: 02870ed21d user: shess tags: trunk)
20:24
Add some simple tests to make sure that the different fulltextFilter query paths are being exercised. (CVS 5498) (check-in: ae96d960e6 user: shess tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts2/fts2.c.
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783


















3784
3785
3786
3787
3788
3789




3790
3791
3792
3793
3794
3795
3796
3797
3798
  sqlite3_vtab_cursor *pCursor,     /* The cursor used for this query */
  int idxNum, const char *idxStr,   /* Which indexing scheme to use */
  int argc, sqlite3_value **argv    /* Arguments for the indexing scheme */
){
  fulltext_cursor *c = (fulltext_cursor *) pCursor;
  fulltext_vtab *v = cursor_vtab(c);
  int rc;
  char *zSql;

  TRACE(("FTS2 Filter %p\n",pCursor));



















  zSql = sqlite3_mprintf("select rowid, * from %%_content %s",
                          idxNum==QUERY_GENERIC ? "" : "where rowid=?");
  sqlite3_finalize(c->pStmt);
  rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql);
  sqlite3_free(zSql);
  if( rc!=SQLITE_OK ) return rc;





  c->iCursorType = idxNum;
  switch( idxNum ){
    case QUERY_GENERIC:
      break;

    case QUERY_ROWID:
      rc = sqlite3_bind_int64(c->pStmt, 1, sqlite3_value_int64(argv[0]));
      if( rc!=SQLITE_OK ) return rc;







<



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
<
|
|
|
>
>
>
>
|
|







3773
3774
3775
3776
3777
3778
3779

3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802

3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
  sqlite3_vtab_cursor *pCursor,     /* The cursor used for this query */
  int idxNum, const char *idxStr,   /* Which indexing scheme to use */
  int argc, sqlite3_value **argv    /* Arguments for the indexing scheme */
){
  fulltext_cursor *c = (fulltext_cursor *) pCursor;
  fulltext_vtab *v = cursor_vtab(c);
  int rc;


  TRACE(("FTS2 Filter %p\n",pCursor));

  /* If the cursor has a statement that was not prepared according to
  ** idxNum, clear it.  I believe all calls to fulltextFilter with a
  ** given cursor will have the same idxNum , but in this case it's
  ** easy to be safe.
  */
  if( c->pStmt && c->iCursorType!=idxNum ){
    sqlite3_finalize(c->pStmt);
    c->pStmt = NULL;
  }

  /* Get a fresh statement appropriate to idxNum. */
  /* TODO(shess): Add a prepared-statement cache in the vt structure.
  ** The cache must handle multiple open cursors.  Easier to cache the
  ** statement variants at the vt to reduce malloc/realloc/free here.
  ** Or we could have a StringBuffer variant which allowed stack
  ** construction for small values.
  */
  if( !c->pStmt ){
    char *zSql = sqlite3_mprintf("select rowid, * from %%_content %s",
                                 idxNum==QUERY_GENERIC ? "" : "where rowid=?");

    rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql);
    sqlite3_free(zSql);
    if( rc!=SQLITE_OK ) return rc;
    c->iCursorType = idxNum;
  }else{
    sqlite3_reset(c->pStmt);
    assert( c->iCursorType==idxNum );
  }

  switch( idxNum ){
    case QUERY_GENERIC:
      break;

    case QUERY_ROWID:
      rc = sqlite3_bind_int64(c->pStmt, 1, sqlite3_value_int64(argv[0]));
      if( rc!=SQLITE_OK ) return rc;
Added test/fts2r.test.


















































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
# 2008 July 29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# These tests exercise the various types of fts2 cursors.
#
# $Id: fts2r.test,v 1.1 2008/07/29 20:38:18 shess Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_ENABLE_FTS2 is not defined, omit this file.
ifcapable !fts2 {
  finish_test
  return
}

#*************************************************************************
# Test table scan (QUERY_GENERIC).  This kind of query happens for
# queries with no WHERE clause, or for WHERE clauses which cannot be
# satisfied by an index.
db eval {
  DROP TABLE IF EXISTS t1;
  CREATE VIRTUAL TABLE t1 USING fts2(c);
  INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
  INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
  INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
}

do_test fts2e-1.1 {
  execsql {
    SELECT rowid FROM t1 ORDER BY rowid;
  }
} {1 2 3}

do_test fts2e-1.2 {
  execsql {
    SELECT rowid FROM t1 WHERE c LIKE '%test' ORDER BY rowid;
  }
} {1 2 3}

do_test fts2e-1.3 {
  execsql {
    SELECT rowid FROM t1 WHERE c LIKE 'That%' ORDER BY rowid;
  }
} {2}

#*************************************************************************
# Test lookup by rowid (QUERY_ROWID).  This kind of query happens for
# queries which select by the rowid implicit index.
db eval {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE VIRTUAL TABLE t1 USING fts2(c);
  CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
  INSERT INTO t2 VALUES (null, 10);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
  INSERT INTO t2 VALUES (null, 5);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
  INSERT INTO t2 VALUES (null, 20);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
}

# TODO(shess): This actually is doing QUERY_GENERIC?  I'd have
# expected QUERY_ROWID in this case, as for a very large table the
# full scan is less efficient.
do_test fts2e-2.1 {
  execsql {
    SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);
  }
} {1 2}

do_test fts2e-2.2 {
  execsql {
    SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;
  }
} {2 5 1 10 3 20}

do_test fts2e-2.3 {
  execsql {
    SELECT t1.rowid, weight FROM t1, t2
           WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;
  }
} {1 10 3 20}

#*************************************************************************
# Test lookup by MATCH (QUERY_FULLTEXT).  This is the fulltext index.
db eval {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE VIRTUAL TABLE t1 USING fts2(c);
  CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
  INSERT INTO t2 VALUES (null, 10);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
  INSERT INTO t2 VALUES (null, 5);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
  INSERT INTO t2 VALUES (null, 20);
  INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
}

do_test fts2e-3.1 {
  execsql {
    SELECT rowid FROM t1 WHERE t1 MATCH 'this' ORDER BY rowid;
  }
} {1 3}

do_test fts2e-3.2 {
  execsql {
    SELECT t1.rowid, weight FROM t1, t2
     WHERE t1 MATCH 'this' AND t1.rowid = t2.id ORDER BY weight;
  }
} {1 10 3 20}

finish_test