/ Check-in [c8d23452]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add an implicit (HIDDEN) docid column. This works as an alias to rowid, similar to how things work in SQLite tables with INTEGER PRIMARY KEY. Add tests to verify operation. (CVS 4426)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c8d2345200f9ece1af712543982097d0b6f348c7
User & Date: shess 2007-09-13 18:14:49
Context
2007-09-13
18:16
Drop the forced error from fts3.c and add forced errors to fts2.c and fts1.c. (CVS 4427) check-in: fec6567a user: shess tags: trunk
18:14
Add an implicit (HIDDEN) docid column. This works as an alias to rowid, similar to how things work in SQLite tables with INTEGER PRIMARY KEY. Add tests to verify operation. (CVS 4426) check-in: c8d23452 user: shess tags: trunk
18:12
Mark the table-named column HIDDEN. Add tests to make sure it's working as expected. (CVS 4425) check-in: ca669eaf user: shess tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

2698
2699
2700
2701
2702
2703
2704
2705



2706
2707
2708
2709
2710
2711
2712
....
2857
2858
2859
2860
2861
2862
2863

2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
....
3759
3760
3761
3762
3763
3764
3765




3766
3767
3768
3769
3770
3771
3772
....
5670
5671
5672
5673
5674
5675
5676

5677
5678
5679
5680
5681



5682
5683
5684
5685
5686
5687
5688
5689
5690

5691

5692














5693

5694
5695
5696
5697
5698
5699
5700
  zSchema = sqlite3_mprintf("CREATE TABLE x");
  for(i=0; i<nColumn; i++){
    zNext = sqlite3_mprintf("%s%s%Q", zSchema, zSep, azColumn[i]);
    sqlite3_free(zSchema);
    zSchema = zNext;
    zSep = ",";
  }
  zNext = sqlite3_mprintf("%s,%Q HIDDEN)", zSchema, zTableName);



  sqlite3_free(zSchema);
  return zNext;
}

/*
** Build a new sqlite3_vtab structure that will describe the
** fulltext index defined by spec.
................................................................................
out:
  clearTableSpec(&spec);
  return rc;
}

/* Decide how to handle an SQL query. */
static int fulltextBestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){

  int i;
  TRACE(("FTS3 BestIndex\n"));

  for(i=0; i<pInfo->nConstraint; ++i){
    const struct sqlite3_index_constraint *pConstraint;
    pConstraint = &pInfo->aConstraint[i];
    if( pConstraint->usable ) {
      if( pConstraint->iColumn==-1 &&
          pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
        pInfo->idxNum = QUERY_DOCID;      /* lookup by docid */
        TRACE(("FTS3 QUERY_DOCID\n"));
      } else if( pConstraint->iColumn>=0 &&
                 pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
        /* full-text search */
        pInfo->idxNum = QUERY_FULLTEXT + pConstraint->iColumn;
        TRACE(("FTS3 QUERY_FULLTEXT %d\n", pConstraint->iColumn));
      } else continue;

      pInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
    sqlite3_value *pVal = sqlite3_column_value(c->pStmt, idxCol+1);
    sqlite3_result_value(pContext, pVal);
  }else if( idxCol==v->nColumn ){
    /* The extra column whose name is the same as the table.
    ** Return a blob which is a pointer to the cursor
    */
    sqlite3_result_blob(pContext, &c, sizeof(c), SQLITE_TRANSIENT);




  }
  return SQLITE_OK;
}

/* This is the xRowid method.  The SQLite core calls this routine to
** retrieve the rowid for the current row of the result set.  fts3
** exposes %_content.docid as the rowid for the virtual table.  The
................................................................................
    rc = index_delete(v, sqlite3_value_int64(ppArg[0]));
  } else if( sqlite3_value_type(ppArg[0]) != SQLITE_NULL ){
    /* An update:
     * ppArg[0] = old rowid
     * ppArg[1] = new rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)

     */
    sqlite_int64 rowid = sqlite3_value_int64(ppArg[0]);
    if( sqlite3_value_type(ppArg[1]) != SQLITE_INTEGER ||
      sqlite3_value_int64(ppArg[1]) != rowid ){
      rc = SQLITE_ERROR;  /* we don't allow changing the rowid */



    } else {
      assert( nArg==2+v->nColumn+1);
      rc = index_update(v, rowid, &ppArg[2]);
    }
  } else {
    /* An insert:
     * ppArg[1] = requested rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)

     */

    assert( nArg==2+v->nColumn+1);














    rc = index_insert(v, ppArg[1], &ppArg[2], pRowid);

  }

  return rc;
}

static int fulltextSync(sqlite3_vtab *pVtab){
  TRACE(("FTS3 xSync()\n"));







|
>
>
>







 







>







|



|







 







>
>
>
>







 







>



|

>
>
>
|
|







>

>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>







2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
....
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
....
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
....
5678
5679
5680
5681
5682
5683
5684
5685
5686
5687
5688
5689
5690
5691
5692
5693
5694
5695
5696
5697
5698
5699
5700
5701
5702
5703
5704
5705
5706
5707
5708
5709
5710
5711
5712
5713
5714
5715
5716
5717
5718
5719
5720
5721
5722
5723
5724
5725
5726
5727
5728
5729
  zSchema = sqlite3_mprintf("CREATE TABLE x");
  for(i=0; i<nColumn; i++){
    zNext = sqlite3_mprintf("%s%s%Q", zSchema, zSep, azColumn[i]);
    sqlite3_free(zSchema);
    zSchema = zNext;
    zSep = ",";
  }
  zNext = sqlite3_mprintf("%s,%Q HIDDEN", zSchema, zTableName);
  sqlite3_free(zSchema);
  zSchema = zNext;
  zNext = sqlite3_mprintf("%s,docid HIDDEN)", zSchema);
  sqlite3_free(zSchema);
  return zNext;
}

/*
** Build a new sqlite3_vtab structure that will describe the
** fulltext index defined by spec.
................................................................................
out:
  clearTableSpec(&spec);
  return rc;
}

/* Decide how to handle an SQL query. */
static int fulltextBestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
  fulltext_vtab *v = (fulltext_vtab *)pVTab;
  int i;
  TRACE(("FTS3 BestIndex\n"));

  for(i=0; i<pInfo->nConstraint; ++i){
    const struct sqlite3_index_constraint *pConstraint;
    pConstraint = &pInfo->aConstraint[i];
    if( pConstraint->usable ) {
      if( (pConstraint->iColumn==-1 || pConstraint->iColumn==v->nColumn+1) &&
          pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
        pInfo->idxNum = QUERY_DOCID;      /* lookup by docid */
        TRACE(("FTS3 QUERY_DOCID\n"));
      } else if( pConstraint->iColumn>=0 && pConstraint->iColumn<=v->nColumn &&
                 pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
        /* full-text search */
        pInfo->idxNum = QUERY_FULLTEXT + pConstraint->iColumn;
        TRACE(("FTS3 QUERY_FULLTEXT %d\n", pConstraint->iColumn));
      } else continue;

      pInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
    sqlite3_value *pVal = sqlite3_column_value(c->pStmt, idxCol+1);
    sqlite3_result_value(pContext, pVal);
  }else if( idxCol==v->nColumn ){
    /* The extra column whose name is the same as the table.
    ** Return a blob which is a pointer to the cursor
    */
    sqlite3_result_blob(pContext, &c, sizeof(c), SQLITE_TRANSIENT);
  }else if( idxCol==v->nColumn+1 ){
    /* The docid column, which is an alias for rowid. */
    sqlite3_value *pVal = sqlite3_column_value(c->pStmt, 0);
    sqlite3_result_value(pContext, pVal);
  }
  return SQLITE_OK;
}

/* This is the xRowid method.  The SQLite core calls this routine to
** retrieve the rowid for the current row of the result set.  fts3
** exposes %_content.docid as the rowid for the virtual table.  The
................................................................................
    rc = index_delete(v, sqlite3_value_int64(ppArg[0]));
  } else if( sqlite3_value_type(ppArg[0]) != SQLITE_NULL ){
    /* An update:
     * ppArg[0] = old rowid
     * ppArg[1] = new rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)
     * ppArg[2+v->nColumn+1] = value for docid
     */
    sqlite_int64 rowid = sqlite3_value_int64(ppArg[0]);
    if( sqlite3_value_type(ppArg[1]) != SQLITE_INTEGER ||
        sqlite3_value_int64(ppArg[1]) != rowid ){
      rc = SQLITE_ERROR;  /* we don't allow changing the rowid */
    }else if( sqlite3_value_type(ppArg[2+v->nColumn+1]) != SQLITE_INTEGER ||
              sqlite3_value_int64(ppArg[2+v->nColumn+1]) != rowid ){
      rc = SQLITE_ERROR;  /* we don't allow changing the docid */
    }else{
      assert( nArg==2+v->nColumn+2);
      rc = index_update(v, rowid, &ppArg[2]);
    }
  } else {
    /* An insert:
     * ppArg[1] = requested rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)
     * ppArg[2+v->nColumn+1] = value for docid
     */
    sqlite3_value *pRequestDocid = ppArg[2+v->nColumn+1];
    assert( nArg==2+v->nColumn+2);
    if( SQLITE_NULL != sqlite3_value_type(pRequestDocid) &&
        SQLITE_NULL != sqlite3_value_type(ppArg[1]) ){
      /* TODO(shess) Consider allowing this to work if the values are
      ** identical.  I'm inclined to discourage that usage, though,
      ** given that both rowid and docid are special columns.  Better
      ** would be to define one or the other as the default winner,
      ** but should it be fts3-centric (docid) or SQLite-centric
      ** (rowid)?
      */
      rc = SQLITE_ERROR;
    }else{
      if( SQLITE_NULL == sqlite3_value_type(pRequestDocid) ){
        pRequestDocid = ppArg[1];
      }
      rc = index_insert(v, pRequestDocid, &ppArg[2], pRowid);
    }
  }

  return rc;
}

static int fulltextSync(sqlite3_vtab *pVtab){
  TRACE(("FTS3 xSync()\n"));

Changes to test/fts3b.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
131
132
133
134
135
136
137
138















































































139
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  This
# script tests for the fts2 rowid-versus-vacuum problem (ticket #2566).
#
# $Id: fts3b.test,v 1.2 2007/09/13 18:12:10 shess Exp $
#

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

# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
ifcapable !fts3 {
................................................................................
# Test that the column doesn't conflict with inserts that don't name
# columns.
do_test fts3b-3.3 {
  execsql {
    INSERT INTO t3 VALUES ('another test');
  }
} {}
















































































finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  This
# script tests for the fts2 rowid-versus-vacuum problem (ticket #2566).
#
# $Id: fts3b.test,v 1.3 2007/09/13 18:14:49 shess Exp $
#

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

# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
ifcapable !fts3 {
................................................................................
# Test that the column doesn't conflict with inserts that don't name
# columns.
do_test fts3b-3.3 {
  execsql {
    INSERT INTO t3 VALUES ('another test');
  }
} {}

# fts3 adds a new implicit column, docid, which acts as an alias for
# rowid.

db eval {
  CREATE VIRTUAL TABLE t4 USING fts3(c);
  INSERT INTO t4 (c) VALUES('this is a test');
  INSERT INTO t4 (c) VALUES('that was a test');
  INSERT INTO t4 (c) VALUES('this is fun');
  DELETE FROM t4 WHERE c = 'that was a test';
}

# Test that docid is present and identical to rowid.
do_test fts3b-4.1 {
  execsql {
    SELECT rowid FROM t4 WHERE rowid <> docid;
  }
} {}

# Test that docid is hidden.
do_test fts3b-4.2 {
  execsql {
    SELECT * FROM t4 WHERE rowid = 1;
  }
} {{this is a test}}

# Test that docid can be selected.
do_test fts3b-4.3 {
  execsql {
    SELECT docid, * FROM t4 WHERE rowid = 1;
  }
} {1 {this is a test}}

# Test that docid can be used in WHERE.
do_test fts3b-4.4 {
  execsql {
    SELECT docid, * FROM t4 WHERE docid = 1;
  }
} {1 {this is a test}}

# Test that the column doesn't conflict with inserts that don't name
# columns.  [Yes, this is the same as fts3b-3.3, here just in case the
# goals of that test change.]
do_test fts3b-4.5 {
  execsql {
    INSERT INTO t4 VALUES ('another test');
  }
} {}

# Test that the docid can be forced on insert.
do_test fts3b-4.6 {
  execsql {
    INSERT INTO t4 (docid, c) VALUES (10, 'yet another test');
    SELECT * FROM t4 WHERE docid = 10;
  }
} {{yet another test}}

# Test that rowid can also be forced.
do_test fts3b-4.7 {
  execsql {
    INSERT INTO t4 (docid, c) VALUES (12, 'still testing');
    SELECT * FROM t4 WHERE docid = 12;
  }
} {{still testing}}

# If an insert tries to set both docid and rowid, require an error.
do_test fts3b-4.8 {
  catchsql {
    INSERT INTO t4 (rowid, docid, c) VALUES (14, 15, 'bad test');
    SELECT * FROM t4 WHERE docid = 14;
  }
} {1 {SQL logic error or missing database}}

# Don't allow update of docid, to match rowid behaviour.
do_test fts3b-4.9 {
  catchsql {
    UPDATE t4 SET docid = 14 WHERE docid = 12;
  }
} {1 {SQL logic error or missing database}}

finish_test